Synchronize Physical Standby Database Using RMAN Incremental BACKUP

Rolling Forward a Physical Standby Database Using the RECOVER Command

12c Standby Database Recover from Service.

What is a Physical Standby DATABASE

there are 2 types of Standby Database

1> physical
2> Logical

I am only discussing here about Physical Standby Database.

A physical standby database is a transactionally-consistent copy of the production database. The primary purpose (but not limited to) of using a physical standby database is to enable disaster recovery. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch a standby database to the production role, minimizing the downtime of the outage. Moreover, performance of production database can be improved by offloading tasks like backup and reporting to standby systems. Standby database is alwasys synchronized with primary database using redo apply services.

but

A standby database might lag behind the primary for various reasons like:

• Network Issues bezween Primary and standby database
• Corruption / Accidental deletion of  Archive Redo Data on Primary
• sys user password change at primary database and necessary action not taken at standby database etc.

If standby database lags behind the primary database:

• you need to first synchronize the standby before performing the switchover resulting in Switchover will take more time.
• Failover will result in data loss. and so on

Synchronizing the standby and primary databases can be done in many ways

• rebuilding the complete standby (not recomended) wll always take more time depending on DB size and n/w bandwidth
• by copying and applying the archived logs from the primary database, and time consuming process, Some times you may even need to restore the archive from your backup
• Applying the incremental backups of the primary database containing changes since the standby database was last refreshed is a faster alternative which will recover the standby database much faster as it will apply only the required changes at standby. you can use this method, even if you accidently deleted the archivelog from backup that was not even backedup

ok so far so good then whats new with 12c.

Lets first look pre 12c steps of incremental backup to synchronize the Standby

• Create a control file for the standby database on the primary database.
• Take an incremental backup on the primary starting from the SCN# of the standby database. you can find this scn while executing recover standby database command.

SQL> recover standby database;
ORA-00279: change 1882090 generated at 02/08/2017 18:57:22 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_08_03/o1_mf_1_20_%u_.
arc

• Copy the incremental backup to the standby host
• catalog the backup with RMAN.
• Mount the standby database with newly created standby control file.
• Cancel managed recovery of the standby database and apply incremental backup to the standby database.

SQL>  recover managed standby database cancel;
SQL> recover database noredo;

• Start managed recovery of standby database.

SQL> recover managed standby database using current logfile disconnect;


In 12c, this procedure has been really simplified. Now you can use the RECOVER … FROM SERVICE command to synchronize the physical standby database with the primary database.
and the above listed steps will pe automatically taken care by Oracle. You just Need to synchronize the Control file from prod to refresh the SCN# afterwords

DGMGRL> show configuration;
Configuration - Personal_DG
Protection Mode: MaxPerformance
Databases:
   orclp   - Primary database
   orcls - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Simulate loss of archived logs on primary database (orclp)
Let’s stop redo transport from primary (orclp) and switch logs on primary so the standby (orcls)goes out of sync
at standby database (orclp)

SQL> alter system set log_archive_dest_status_2=defer;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;

We can verify that logs are not transfered on standby site (orcls database)

ORCLP>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           98
ORCLS>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           94
you can also check the same from standby alert log
to simulate the loss of archivelog on primary I identified the archivelogs not shipped on standby and moved them to backup Location

ORCLP>select sequence#, name from v$archived_log where sequence# >90;
SEQUENCE# NAME
---------- ----------------------------------------------------------------
91 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_91_bfjgx50j_.arc
92 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_92_bfjgx50j_.arc
93 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_93_bfjgx6tb_.arc
94 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_94_bfjgx7yh_.arc
95 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_95_bfjgx60h_.arc
96 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_96_bfjgx6ph_.arc
97 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_97_bfjgx9yh_.arc
98 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_98_bfjgx3nh_.arc
mv /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_95_bfjgx60h_.arc /u01/backup
96 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_96_bfjgx6ph_.arc /u01/backup
97 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_97_bfjgx9yh_.arc /u01/backup
98 /u01/app/oracle/fast_recovery_area/ORCLP/archivelog/2017_08_03/o1_mf_1_98_bfjgx3nh_.arc /u01/backup

enable the log shipping again

ORCLP> alter system set log_archive_dest_status_2=enable;

DGMGRL> show configuration;
Configuration - Personal_DG
   Protection Mode: MaxPerformance
   Databases:
   ORCLP   - Primary database
      Error: ORA-16724: cannot resolve gap for one or more standby databases
      Fast-Start Failover: DISABLED
Configuration Status:
ERROR

Note:- if you are not using broker you can verify the staus from primary and standby database from v$database, v$managed_standby and v$archive_gap views as well

ORCLP>select current_scn from v$database;
CURRENT_SCN
          -----------
            1932090
ORCLS>select current_scn from v$database;
CURRENT_SCN
          ----------
           1832061

As you can see the standby database SCN is now quite behind the primary
    
Lets synchronize the physical standby using the RECOVER…FROM SERVICE command

ORCLS>recover managed standby database cancel;
ORCLS>shutdown immediate;
ORCLS>startup mount;

$ rman target /
RMAN> recover database from service ORCLP noredo using compressed backupset;
Starting recover at 03-AUG-17
Starting implicit crosscheck backup at 03-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 03-AUG-17
Starting implicit crosscheck copy at 03-AUG-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 03-AUG-17
searching for all files in the recovery area
cataloging files...
...
...
channel ORA_DISK_1: restoring section 9 of 9
channel ORA_DISK_1: restore complete, elapsed time: 00:01:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ORCLP
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORCLS/sysaux01.dbf
channel ORA_DISK_1: restoring section 1 of 10
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ORCLP
...
...
Finished recover at 03-AUG-17

ORCLP>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
          99
ORCLS>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
         99

Isn't it really easy.


now the only thing you have to do is to create the stanby controlfile from prod and bring it to standby and mount the database from this standby and start recovery
or
you can use again the FROM SERVICE command to synchronize the SCN# of standby controlfile I am using here this method only

ORCLS>SHUTDOWN IMMEDIATE;
ORCLS>STARTUP NOMOUNT;
$ rman target /
RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE ORCLP;

Starting restore at 03-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLP
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=/u01/app/oracle/oradata/orcls/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcls/control02.ctl
Finished restore at 03-AUG-17
Mount the standby database.

orcls>ALTER DATABASE MOUNT;
orcls> recover managed standby database using current logfile disconnect

and thats all......you are done!!!!!

Isn't it interesting..............

hope you have enjoyed the post please write your comments/suggestions/feedback in the comment section below..........

Note:- Please ensure to review each command before executing in Prod environment. You are yourself only responsible to execute anything from this post in your prod env.

I wish you guys a good day and happy learning