RMAN - Recovery Senario ORA-26040: Data block was loaded using the NOLOGGING option on Operations on RAC 2 node 12.1.0.2.0

Scenario Preview:

• Create Table with no logging on schema soe
• Database got corrupted , Using RMAN recovery preformed
• While recover operation it will throw error (ORA-26040: Data block was loaded using the NOLOGGING option)

Solution Preview:

• stop database using SRVCTL tool
• start database using SRVCTL tool
• restore database using RMAN 
• recover database using RMAN
• Open database 
• Error Found -- ORA-26040: Data block was loaded using the NOLOGGING option
• Solution - drop and recreate again

[oracle@srv1 expdp]$ sqlplus soe/soe@rac

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 19 18:04:21 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> CREATE TABLE RPT_ORDER_MONTH NOLOGGING AS SELECT SUM(ORDER_TOTAL) ORDERS_SUM, TO_CHAR(ORDER_DATE,'YYYY-MM') ORDER_MONTH FROM ORDERS GROUP BY TO_CHAR(ORDER_DATE,'YYYY-MM') ORDER BY TO_CHAR(ORDER_DATE,'YYYY-MM');

Table created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.



[root@srv1 ~]# srvctl stop database -d rac

[root@srv1 ~]# srvctl start database -d rac -startoption mount


[oracle@srv1 expdp]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Aug 19 18:11:55 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RAC (DBID=2604196865, not open)

RMAN> restore database;

Starting restore at 19-AUG-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 instance=rac1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/RAC/DATAFILE/system.258.1016214685
channel ORA_DISK_1: restoring datafile 00002 to +DATA/RAC/DATAFILE/soetbs.284.1016731949
channel ORA_DISK_1: restoring datafile 00003 to +DATA/RAC/DATAFILE/sysaux.257.1016214601
channel ORA_DISK_1: restoring datafile 00004 to +DATA/RAC/DATAFILE/undotbs1.260.1016214793
channel ORA_DISK_1: restoring datafile 00005 to +DATA/RAC/DATAFILE/undotbs2.265.1016215315
channel ORA_DISK_1: restoring datafile 00006 to +DATA/RAC/DATAFILE/users.259.1016214791
channel ORA_DISK_1: reading from backup piece +FRA/RAC/BACKUPSET/2019_08_19/nnndf0_full_db_0.416.1016733531
channel ORA_DISK_1: piece handle=+FRA/RAC/BACKUPSET/2019_08_19/nnndf0_full_db_0.416.1016733531 tag=FULL_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:05
Finished restore at 19-AUG-19

RMAN> recover database;

Starting recover at 19-AUG-19
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 63 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_19/thread_1_seq_63.393.1016733841
archived log for thread 1 with sequence 64 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_19/thread_1_seq_64.415.1016733843
archived log for thread 1 with sequence 65 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_19/thread_1_seq_65.412.1016733945
archived log for thread 1 with sequence 66 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_19/thread_1_seq_66.411.1016733949
archived log for thread 1 with sequence 67 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_19/thread_1_seq_67.409.1016733991
archived log for thread 2 with sequence 49 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_19/thread_2_seq_49.413.1016733845
archived log for thread 2 with sequence 50 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_19/thread_2_seq_50.410.1016733989
archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_19/thread_1_seq_63.393.1016733841 thread=1 sequence=63
archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_19/thread_2_seq_49.413.1016733845 thread=2 sequence=49
archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_19/thread_1_seq_64.415.1016733843 thread=1 sequence=64
archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_19/thread_1_seq_65.412.1016733945 thread=1 sequence=65
media recovery complete, elapsed time: 00:00:04
Finished recover at 19-AUG-19

RMAN> ALTER DATABASE OPEN;

Statement processed
[root@srv1 ~]# srvctl start instance -i rac2 -d rac

[oracle@srv1 expdp]$ ps -ef| grep pmon
grid      7638     1  0 12:23 ?        00:00:01 asm_pmon_+ASM1
oracle   15758     1  0 18:11 ?        00:00:00 ora_pmon_rac1
oracle   21827 31146  0 18:27 pts/0    00:00:00 grep pmon

[oracle@srv1 expdp]$ sqlplus soe/soe@rac

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 19 18:33:24 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Aug 19 2019 18:06:07 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> SELECT * FROM RPT_ORDER_MONTH;
SELECT * FROM RPT_ORDER_MONTH
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 219)
ORA-01110: data file 6: '+DATA/RAC/DATAFILE/users.259.1016214791'
ORA-26040: Data block was loaded using the NOLOGGING option


SQL>

SQL> DROP TABLE RPT_ORDER_MONTH PURGE;

Table dropped.

SQL> CREATE TABLE RPT_ORDER_MONTH NOLOGGING AS SELECT SUM(ORDER_TOTAL) ORDERS_SUM, TO_CHAR(ORDER_DATE,'YYYY-MM') ORDER_MONTH FROM ORDERS GROUP BY TO_CHAR(ORDER_DATE,'YYYY-MM') ORDER BY TO_CHAR(ORDER_DATE,'YYYY-MM');

Table created.

SQL> SELECT * FROM RPT_ORDER_MONTH;

SQL> select count(*) from RPT_ORDER_MONTH;

  COUNT(*)

----------

       109