12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard

By Franck Pachot

.
You can accept to do NOLOGGING operations on bulk loads or index build according that you do a backup just after, and that your recovery plan mentions how to load the data again in case of media recovery. With a standby database, we usually force logging because we want redo to be generated for all operations in order to ship it and apply it on standby database. 12.2 brings a new solution: do nologging operations, without generating redo, and then ship the blocks to the standby. This is done on the standby by RMAN.

On primary ORCLA

I create the demo table

SQL> create table DEMO tablespace users pctfree 99 as select rownum n from xmltable('1 to 1000');
Table created.

put it in NOLOGGING

SQL> alter table DEMO nologging;
Table altered.

The database is not in force logging:

SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO

Here is a direct-path insert

SQL> insert /*+ append */ into DEMO select rownum n from xmltable('1 to 100000');
100000 rows created.
 
SQL> commit;
Commit complete.

My rows are here:

SQL> select count(*) from DEMO;
  COUNT(*)
----------
    200000

This is a nologging operation. Media recovery is not possible. The datafile needs backup:

RMAN> report unrecoverable;
using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
7    full or incremental     /u01/oradata/ORCLA/users01.dbf

On ADG standby ORCLB

In Active Data Guard, I can query the table, but:

SQL> select count(*) from DEMO
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 16966)
ORA-01110: data file 7: '/u01/oradata/ORCLB/datafile/o1_mf_users_dbvmwdqc_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

The blocks were not replicated because redo was not generated by the primary and then not shipped and applied on the standby.

Note that this is not identifed by RMAN on the standby:

RMAN> report unrecoverable;
using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
 
RMAN>

recover nonlogged blocks

If I try some recovery here, I can’t because I’m still is apply mode, here is the message I get if I try:

ORA-01153: an incompatible media recovery is active

Let’s stop the apply:

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

In 12.1 I can recover the datafile from the primary with ‘recover from service’ but in 12.2 there is no need to ship the whole datafile. The non-logged block list has been shipped to the standby, recorded in the standby controlfile, and we can list them from v$nonlogged_block.

And we can recover them with a simple command: RECOVER DATABASE NONLOGGED BLOCK

RMAN> recover database nonlogged block;
Starting recover at 25-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
 
starting recovery of nonlogged blocks
List of Datafiles
=================
File Status Nonlogged Blocks Blocks Examined Blocks Skipped
---- ------ ---------------- --------------- --------------
1    OK     0                0               104959
2    OK     0                0               18783
3    OK     0                0               62719
4    OK     0                0               8959
7    OK     0                16731           18948
 
Details of nonlogged blocks can be queried from v$nonlogged_block view
 
recovery of nonlogged blocks complete, elapsed time: 00:00:03

Here it is, I can query the table now

SQL> select count(*) from DEMO;
  COUNT(*)
----------
    200000

I re-enable real-time apply

DGMGRL> edit database orclb set state=apply-on;
Succeeded.

Switchover

Now, what would happen if I do a switchover of failover between the nologging operation and the nonlogged recovery?
I did the same on primary and then:

DGMGRL> switchover to orclb;
Performing switchover NOW, please wait...
Operation requires a connection to database "orclb"
Connecting ...
Connected to "ORCLB"
Connected as SYSDBA.
New primary database "orclb" is opening...
Operation requires start up of instance "ORCLA" on database "orcla"
Starting instance "ORCLA"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "ORCLA"
Switchover succeeded, new primary is "orclb"

I can query the list of nonlogged blocks that was shipped to standby:

SQL> select * from v$nonlogged_block;
 
     FILE#     BLOCK#     BLOCKS NONLOGGED_START_CHANGE# NONLOGGED
---------- ---------- ---------- ----------------------- ---------
NONLOGGED_END_CHANGE# NONLOGGED RESETLOGS_CHANGE# RESETLOGS
--------------------- --------- ----------------- ---------
OBJECT#                                  REASON      CON_ID
---------------------------------------- ------- ----------
         7        307      16826                 2197748
              2197825                     1396169 22-FEB-17
74006                                    UNKNOWN          0

But I cannot recover because the database (the old standby that became primary) is opened:

ORA-01126: database must be mounted in this instance and not open in any instance

So what?

This new feature is acceptable if you recover the nonlogged blocks on the standby just after the nologging operation on the primary. This can be used automatically for datawarehouse load, but also manually when doing a reorganization or an application release that touches the data. Just don’t forget the recover on the standby to avoid surprises later. It will not reduce the amount of data that is shipped to the standby, because shipping the blocks is roughly the same as shipping the redo for the direct-path writes. But one the primary you have the performance benefit of nologging operations.