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:00We 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)
Comments