I have recently discovered a new way to easily fix nologging corruption in a standby which version is 12.2 or higher.

The first thing we need to do for this test is to disable the force logging parameter from our Primary Database

db1221 >  select name,force_logging from v$database;

    NAME    FORCE_LOGGING 
________ ________________ 
DB122    YES              

db1221 > alter database no force logging;

Database altered.

db1221 > select name,force_logging from v$database;

    NAME    FORCE_LOGGING 
________ ________________ 
DB122    NO               

db1221 > 

Let’s now create a table with P42 user with nologging option and populate it with some data and using append hint to make it work

You can read more about that in this Ask Tom discussion

db1221 > CREATE TABLE "P42"."ORDERS"
  2  ("ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE,
  3  "ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE,
  4  "ORDER_MODE" VARCHAR2(8),
  5  "CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE,
  6  "ORDER_STATUS" NUMBER(2,0),
  7  "ORDER_TOTAL" NUMBER(8,2),
  8  "SALES_REP_ID" NUMBER(6,0),
  9  "PROMOTION_ID" NUMBER(6,0),
 10  "WAREHOUSE_ID" NUMBER(6,0),
 11  "DELIVERY_TYPE" VARCHAR2(15),
 12  "COST_OF_DELIVERY" NUMBER(6,0),
 13  "WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15),
 14  "DELIVERY_ADDRESS_ID" NUMBER(12,0),
 15  "CUSTOMER_CLASS" VARCHAR2(30),
 16  "CARD_ID" NUMBER(12,0),
 17  "INVOICE_ADDRESS_ID" NUMBER(12,0),
 18  CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID"))
 19  TABLESPACE "P42"
 20  nologging;

Table "P42"."ORDERS" created.

db1221 > 

db1221 > INSERT /*+ append */ INTO P42.ORDERS
  2  select * from SOE.ORDERS where ORDER_ID < 200 order by 1;

198 rows inserted.

db1221 > commit;
Commit complete.

As soon as we commit the changes we got the following error in the Standby side:

2020-07-06T15:38:58.995954+01:00
Recovery of Online Redo Log: Thread 1 Group 112 Seq 234 Reading mem 0
  Mem# 0: +RECO/ST122/ONLINELOG/group_112.398.1043160859
2020-07-06T16:06:36.812409+01:00
Multi instance redo apply has encountered invalidation redo and will stop.
MIRA encountered invalidation redo for AFN 9 block 147 with redo SCN 0x6171878
MRP0: The following warnings/errors are found:
ORA-10892: multi-instance redo apply encountered nonlogged operation
Multi Instance Redo Apply terminaed with error 10892
2020-07-06T16:06:36.828259+01:00
Errors in file /u01/app/oracle/diag/rdbms/st122/st1221/trace/st1221_pr00_7546.trc:
ORA-10892: multi-instance redo apply encountered nonlogged operation
MIRA: Mark controlfile recovery error occurred 
2020-07-06T16:06:36.894655+01:00
Managed Standby Recovery not using Real Time Apply
2020-07-06T16:06:37.253274+01:00
Recovery slave PR02 exited 
2020-07-06T16:06:37.265695+01:00

We can just simple restart MRP to avoid any lag and "ignore" the issue for now (not saying this is a good idea though… 🙂 )

st1221 > alter database recover managed standby database disconnect;
Database altered.

We can also see the errors if we try to select the tables from the standby (which is open as read-only)

st1221 > select count(*)  from P42.ORDERS;

Error starting at line : 1 in command -
select count(*)  from P42.ORDERS
Error report -
ORA-01578: ORACLE data block corrupted (file # 9, block # 147)
ORA-01110: data file 9: '+DATA12/ST122/DATAFILE/p42.336.1045064041'
ORA-26040: Data block was loaded using the NOLOGGING option

st1221 > 

# Alert Log #
2020-07-06T16:18:46.559599+01:00
Errors in file /u01/app/oracle/diag/rdbms/st122/st1221/trace/st1221_ora_13479.trc  (incident=133443):
ORA-01578: ORACLE data block corrupted (file # 9, block # 147)
ORA-01110: data file 9: '+DATA12/ST122/DATAFILE/p42.336.1045064041'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /u01/app/oracle/diag/rdbms/st122/st1221/incident/incdir_133443/st1221_ora_13479_i133443.trc

If we validate now the datafile related to that error, we can see the system will mark 3 blocks as corrupt:

RMAN> validate datafile 9;

Starting validate at 06-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 instance=st1221 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=+DATA12/ST122/DATAFILE/p42.336.1045064041
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9    OK     3              130919       131072          6171894   
  File Name: +DATA12/ST122/DATAFILE/p42.336.1045064041
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              5               
  Index      0              6               
  Other      0              142             

Finished validate at 06-JUL-20

RMAN> 

Back in previous releases, this will need to recover the datafile, probably copying from the primary to the standby or doing a "recover from service" job if you are in 12.1.

Since 12.2 release however, we can just execute a simple rman command which will take care of transport the actual blocks needed to fix the corruption.

We just need to stop the MRP and execute "recover database nonlogged block" in RMAN

DGMGRL> edit database st122 set state=apply-off;    
Succeeded.

[oracle@rac2-node1 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jul 6 16:25:30 2020
connected to target database: DB122 (DBID=955512738)

RMAN> recover database nonlogged block;

Starting recover at 06-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=506 instance=st1221 device type=DISK

starting recovery of nonlogged blocks
List of Datafiles
=================
File Status Nonlogged Blocks Blocks Examined Blocks Skipped
---- ------ ---------------- --------------- --------------
1    OK     0                0               108799        
2    OK     0                0               1556479       
3    OK     0                0               101119        
4    OK     0                0               35839         
5    OK     0                0               38399         
7    OK     0                0               13759         
8    OK     0                0               12799         
9    OK     0                3               131068        

Details of nonlogged blocks can be queried from v$nonlogged_block view

recovery of nonlogged blocks complete, elapsed time: 00:00:03

Finished recover at 06-JUL-20

RMAN> 

As we can see from the above output, the RMAN has reviewed all the datafiles of the system and recovered the 3 nologging blocks.

For more details, we can check the Alertlog where we can see how the system connected to "db122" which is our primary database and replaced those 3 blocks from the original ones in primary.

2020-07-06T16:26:06.933084+01:00
alter database recover datafile list clear
Completed: alter database recover datafile list clear
2020-07-06T16:26:07.879740+01:00
Started Nonlogged Block Replacement recovery on file 1 (ospid 14299 rcvid 10795522858173557238)
2020-07-06T16:26:07.946824+01:00
Finished Nonlogged Block Replacement recovery on file 1. 0 blocks remain
[...]
Started Nonlogged Block Replacement recovery on file 9 (ospid 14299 rcvid 10795522858173557238)
Data Transfer Cache defaulting to 112MB. Trying to get it from Buffer Cache for process 14299.
2020-07-06T16:26:09.738880+01:00
Finished Nonlogged Block Replacement recovery on file 9. 0 blocks remain
  Statistics for replacement block source database (service=db122)
  Blocks requested 3, blocks received 3. 

  Reason replacement blocks accepted or rejected               Blocks Last block
  -------------------------------------------------------- ---------- ----------
  Accept: SCN in range for classic non-logged block                 3        149

We can now see that table from the Standby without any issues

st1221 > select count(*)  from P42.ORDERS;

   COUNT(*) 
___________ 
        198 

st1221 > 

For this kind of new features, is always good to make sure you check your system version and not just relay on your old notes about how to resolve issues 🙂

More information: How to Resolve ORA-1578 /ORA-26040 Nologging operation in 12.2 on Standby database (Doc ID 2378500.1)

Last modified: 17 August 2020

Author

Comments

Write a Reply or Comment

Your email address will not be published.