Friday, 18 November 2016

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes

---- Example error in Standby DB ------

ORA-01578: 
ORA-01110: 
ORA-26040: Data block was loaded using the NOLOGGING option

-----------------------------------------------------------------------------------------

--- Backup Server ---

col FIRST_NONLOGGED_SCN format  999999999999999
SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0 ;

     FILE# FIRST_NONLOGGED_SCN
---------- -------------------
         4       2229616324279
         6       2229616324282
         7       2229616324285
         8       2229486974087
         9       2229486973946
        10       2229486973946
        11       2229486973954
        19       2229616324228
        20       2229616324228
        21       2229616324231
        22       2229486973959
        24       2229486974422
        25       2229616324273
        27       2229486974437
        28       2229486974454
        29       2229616324276



 recovery cancel;
 ALTER DATABASE DATAFILE 4 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 6 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 7 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 8 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 9 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 10 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 11 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 19 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 20 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 21 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 22 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 24 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 25 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 27 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 28 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 29 OFFLINE FOR DROP;








-- PRIMARY --
BACKUP INCREMENTAL FROM SCN 2229616324279 DATAFILE 4 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324282 DATAFILE 6 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324285 DATAFILE 7 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486974087 DATAFILE 8 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486973946 DATAFILE 9 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486973946 DATAFILE 10 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486973954 DATAFILE 11 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324228 DATAFILE 19 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324228 DATAFILE 20 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324231 DATAFILE 21 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486973959 DATAFILE 22 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486974422 DATAFILE 24 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324273 DATAFILE 25 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486974437 DATAFILE 27 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486974454 DATAFILE 28 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324276 DATAFILE 29 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';


SCP the backup from primary to backup;


-- Backup --

rman connect > calatog start with

 ALTER DATABASE DATAFILE 4 online;
 ALTER DATABASE DATAFILE 6 online;
 ALTER DATABASE DATAFILE 7 online;
 ALTER DATABASE DATAFILE 8 online;
 ALTER DATABASE DATAFILE 9 online;
 ALTER DATABASE DATAFILE 10 online;
 ALTER DATABASE DATAFILE 11 online;
 ALTER DATABASE DATAFILE 19 online;
 ALTER DATABASE DATAFILE 20 online;
 ALTER DATABASE DATAFILE 21 online;
 ALTER DATABASE DATAFILE 22 online;
 ALTER DATABASE DATAFILE 24 online;
 ALTER DATABASE DATAFILE 25 online;
 ALTER DATABASE DATAFILE 27 online;
 ALTER DATABASE DATAFILE 28 online;
 ALTER DATABASE DATAFILE 29 online;


- recover DATAFILE 4,6,7,8,9,10,11,19,20,21,22,24,25,27,28,29; # For manual standby 

or

- recover DATAFILE 4,6,7,8,9,10,11,19,20,21,22,24,25,27,28,29 NOREDO;


- Now perform recovery standby db


For more detail Below the reference doc ID.

Reference :

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes [ID 958181.1]

No comments: