11G DATAGUARD: FLASHBACK STANDBY AFTER RESETLOGS ON PRIMARY

   If primary database is opened with resetlogs option following an incomplete recovery, redo apply service might halt on standby database when it encounters open resetlogs command in redo. But if physical standby database’s SCN is far enough behind the primary database’s SCN, then redo apply service can interpret open resetlogs command without stopping.
    Hence , after primary has been opened with resetlogs option, if we flashback standby database to an SCN which is earlier than reset logs SCN# of primary, we won’t have to recreate the standby database.
Let’s implement :
OVERVIEW:
   — Backup primary database
   — Create restore point before_update
   — Perform update
   — Restore and recover primary database to restore point before_update
   — Check configuration – standby database has diverged from primary
   — Flashback standby database to SCN = primary (resetlogs_scn) – 1
   — check that primary is back into configuration
IMPLEMENTATION:
–backup database
RMAN>backup database;
– Create restore point before_update
pri> create restore point before_update;
-- check initial salary –
PRI>select salary from hr.employees where employee_id=100;
    SALARY
———-
      2000
– update salary
PRI> update hr.employees set salary = 1000 where employee_id = 100;
       commit;
       alter system switch logfile;
       /
        /
-- On primary Restore from backup and recover till before update
PRI>shutdown immediate;
         startup mount;
$rman target sys/sys@orcl catalog rcowner/rcowner@rcat
RMAN> restore database;
               recover database until restore point before_update;
              alter database open resetlogs;
 — check that recovery was successful i.e. 
     salary on primary database is the same as before update
PRI>select salary from hr.employees where employee_id=100;
   SALARY
———-
      2000
– check configuration  –
    Note that there is error as standby database has diverged from primary
DGMGRL>show configuration;
    orcl – Primary database
    sby  – Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the database
DGMGRL> show database sby;
Database – sby
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    sby
  Database Error(s):
    ORA-16700: the standby database has diverged from the primary database
    ORA-16766: Redo Apply is stopped
– check resetlogs scn from primary database
PRI>select RESETLOGS_CHANGE# from v$database;
RESETLOGS_CHANGE#
—————–
          1414101
– check current scn# from standby database
– note that standby database is ahead of reset logs change# and hence needs
   to be flashed back
SBY>select current_scn from v$database;
CURRENT_SCN
———–
    1414993
–Flashback standby database to 2 SCN earlier than resetlogs_scn–
   i.e. 1414101 -2 = 1414099
SBY>shu immediate;
         startup mount;
         flashback database to scn 1414099 ;
         alter database open;
– check that standby database is ready to receive and apply logs from primary;
DGMGRL> show configuration;
Configuration – dgconfig
  Protection Mode: MaxPerformance
  Databases:
    orcl – Primary database
    sby  – Physical standby database
DGMGRL> show database sby;
Database – sby
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    sby
Database Status:
SUCCESS
– check that salary on standby database is the same as before update
SBY>select salary from hr.employees where employee_id=100;
    SALARY
———-
      2000
  Thanks for your time !!!
   Keep visiting the blog….
                                                   ———————————
Related links:

11g DataGuard : Automatic Client Failover

Flashback Through Role Transition For Physical Standby

                                                      ———————-

3 thoughts on “11G DATAGUARD: FLASHBACK STANDBY AFTER RESETLOGS ON PRIMARY

  1. Hi..
    HI,
    The update after the restore point is lost ? If the recover is done only till the restore point , then how abt the transactions after the restore point?

    PRI> update hr.employees set salary = 1000 where employee_id = 100;
    commit

    ??? How can we restore these updates after checkpoint in primary?

  2. Hi,

    Good question. But to demostrate the scenario he took that example. If the recover is necessary for the primary then, in that case we can use this for avoding standby halt.

    Corrert me if I am wrong..

    I have one more question. if we open standby database, aren’t we report errors in DGMGRL> show database sby; command like managed recovery process has been stopped or etc..

    Thanks,
    Adi.

    1. HI ADI,
      You will get errors showing as multiple errors reported when you fire show configuration from dgmgrl . When you will fire show database verbose standby it will clearly mention ORA-16700 the standby database has diverged from Primary Database and ORA-16766 Redo Applied is Stopped.

      Thanks ,
      Neeraj Gulia

Your comments and suggestions are welcome!