How to sync standby database which is lagging behind from primary database
\nPrimary Database cluster: cluster1.rmohan.com
\nStandby Database cluster: cluster2.rmohan.com<\/p>\n
Primary Database: prim
\nStandby database: stand<\/p>\n
Database version:11.2.0.1.0<\/p>\n
Reason:-
\n1. Might be due to the network outage between the primary and the standby database leading to the archive
\ngaps. Data guard would be able to detect the archive gaps automatically and can fetch the missing logs as
\nsoon as the connection is re-established.<\/p>\n
2. It could also be due to archive logs getting missed out on the primary database or the archives getting
\ncorrupted and there would be no valid backups.<\/p>\n
In such cases where the standby lags far behind from the primary database, incremental backups can be used
\nas one of the methods to roll forward the physical standby database to have it in sync with the primary database.<\/p>\n
At primary database:-
\nSQL> select status,instance_name,database_role from v$database,v$instance;<\/p>\n
STATUS INSTANCE_NAME DATABASE_ROLE
\n———— —————- —————-
\nOPEN prim PRIMARY<\/p>\n
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;<\/p>\n
THREAD# MAX(SEQUENCE#)
\n———- ————–
\n1 214<\/p>\n
At standby database:-
\nSQL> select status,instance_name,database_role from v$database,v$instance;<\/p>\n
STATUS INSTANCE_NAME DATABASE_ROLE
\n———— —————- —————-
\nOPEN stand PHYSICAL STANDBY<\/p>\n
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;<\/p>\n
THREAD# MAX(SEQUENCE#)
\n———- ————–
\n1 42
\nSo we can see the standby database is having archive gap of around (214-42) 172 logs.<\/p>\n
Step 1: Take a note of the Current SCN of the Physical Standby Database.
\nSQL> select current_scn from v$database;<\/p>\n
CURRENT_SCN
\n———–
\n1022779<\/p>\n
Step 2 : Cancel the Managed Recovery Process on the Standby database.
\nSQL> alter database recover managed standby database cancel;<\/p>\n
Database altered.<\/p>\n
Step 3: On the Primary database, take the incremental SCN backup from the SCN that is currently recorded on the standby database (1022779)
\nAt primary database:-<\/p>\n
RMAN> backup incremental from scn 1022779 database format ‘\/tmp\/rman_bkp\/stnd_backp_%U.bak’;<\/p>\n
Starting backup at 28-DEC-14<\/p>\n
using channel ORA_DISK_1
\nbackup will be obsolete on date 04-JAN-15
\narchived logs will not be kept or backed up
\nchannel ORA_DISK_1: starting full datafile backup set
\nchannel ORA_DISK_1: specifying datafile(s) in backup set
\ninput datafile file number=00001 name=\/u01\/app\/oracle\/oradata\/prim\/system01.dbf
\ninput datafile file number=00002 name=\/u01\/app\/oracle\/oradata\/prim\/sysaux01.dbf
\ninput datafile file number=00005 name=\/u01\/app\/oracle\/oradata\/prim\/example01.dbf
\ninput datafile file number=00003 name=\/u01\/app\/oracle\/oradata\/prim\/undotbs01.dbf
\ninput datafile file number=00004 name=\/u01\/app\/oracle\/oradata\/prim\/users01.dbf
\nchannel ORA_DISK_1: starting piece 1 at 28-DEC-14
\nchannel ORA_DISK_1: finished piece 1 at 28-DEC-14
\npiece handle=\/tmp\/rman_bkp\/stnd_backp_0cpr8v08_1_1.bak tag=TAG20141228T025048 comment=NONE
\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25<\/p>\n
using channel ORA_DISK_1
\nbackup will be obsolete on date 04-JAN-15
\narchived logs will not be kept or backed up
\nchannel ORA_DISK_1: starting full datafile backup set
\nchannel ORA_DISK_1: specifying datafile(s) in backup set
\nincluding current control file in backup set
\nchannel ORA_DISK_1: starting piece 1 at 28-DEC-14
\nchannel ORA_DISK_1: finished piece 1 at 28-DEC-14
\npiece handle=\/tmp\/rman_bkp\/stnd_backp_0dpr8v12_1_1.bak tag=TAG20141228T025048 comment=NONE
\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
\nFinished backup at 28-DEC-14<\/p>\n
We took the backup inside \/tmp\/rman_bkp directory and ensure that it contains nothing besides the incremental backups of scn.<\/p>\n
Step 4: Take the standby controlfile backup of the Primary database controlfile.<\/p>\n
At primary database:<\/p>\n
RMAN> backup current controlfile for standby format ‘\/tmp\/rman_bkp\/stnd_%U.ctl’;<\/p>\n
Starting backup at 28-DEC-14
\nusing channel ORA_DISK_1
\nchannel ORA_DISK_1: starting full datafile backup set
\nchannel ORA_DISK_1: specifying datafile(s) in backup set
\nincluding standby control file in backup set
\nchannel ORA_DISK_1: starting piece 1 at 28-DEC-14
\nchannel ORA_DISK_1: finished piece 1 at 28-DEC-14
\npiece handle=\/tmp\/rman_bkp\/stnd_0epr8v4e_1_1.ctl tag=TAG20141228T025301 comment=NONE
\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
\nFinished backup at 28-DEC-14<\/p>\n
Starting Control File and SPFILE Autobackup at 28-DEC-14
\npiece handle=\/u01\/app\/oracle\/flash_recovery_area\/PRIM\/autobackup\/2014_12_28\/o1_mf_s_867466384_b9y8sr8k_.bkp comment=NONE
\nFinished Control File and SPFILE Autobackup at 28-DEC-14<\/p>\n
Step 5: Transfer the backups from the Primary cluster to the Standby cluster.
\n[oracle@cluster1 ~]$ cd \/tmp\/rman_bkp\/
\n[oracle@cluster1 rman_bkp]$ ls -ltrh
\ntotal 24M
\n-rw-r—–. 1 oracle oinstall 4.2M Dec 28 02:51 stnd_backp_0cpr8v08_1_1.bak
\n-rw-r—–. 1 oracle oinstall 9.7M Dec 28 02:51 stnd_backp_0dpr8v12_1_1.bak
\n-rw-r—–. 1 oracle oinstall 9.7M Dec 28 02:53 stnd_0epr8v4e_1_1.ctl<\/p>\n
oracle@cluster1 rman_bkp]$ scp *.* oracle@cluster2:\/tmp\/rman_bkp\/
\noracle@cluster2’s password:
\nstnd_0epr8v4e_1_1.ctl 100% 9856KB 9.6MB\/s 00:00
\nstnd_backp_0cpr8v08_1_1.bak 100% 4296KB 4.2MB\/s 00:00
\nstnd_backp_0dpr8v12_1_1.bak 100% 9856KB 9.6MB\/s 00:00<\/p>\n
Step 6: On the standby cluster, connect the Standby Database through RMAN and catalog the copied
\nincremental backups so that the Controlfile of the Standby Database would be aware of these
\nincremental backups.<\/p>\n
At standby database:-<\/p>\n
SQL><\/p>\n
[oracle@cluster2 ~]$ rman target \/
\nRMAN> catalog start with ‘\/tmp\/rman_bkp’;<\/p>\n
using target database control file instead of recovery catalog
\nsearching for all files that match the pattern \/tmp\/rman_bkp<\/p>\n
List of Files Unknown to the Database
\n=====================================
\nFile Name: \/tmp\/rman_bkp\/stnd_0epr8v4e_1_1.ctl
\nFile Name: \/tmp\/rman_bkp\/stnd_backp_0dpr8v12_1_1.bak
\nFile Name: \/tmp\/rman_bkp\/stnd_backp_0cpr8v08_1_1.bak<\/p>\n
Do you really want to catalog the above files (enter YES or NO)? yes
\ncataloging files…
\ncataloging done<\/p>\n
List of Cataloged Files
\n=======================
\nFile Name: \/tmp\/rman_bkp\/stnd_0epr8v4e_1_1.ctl
\nFile Name: \/tmp\/rman_bkp\/stnd_backp_0dpr8v12_1_1.bak
\nFile Name: \/tmp\/rman_bkp\/stnd_backp_0cpr8v08_1_1.bak<\/p>\n
Step 7. Shutdown the database and open it in mount stage for recovery purpose.
\nSQL> shut immediate;
\nSQL> startup mount;<\/p>\n
Step 8.Now recover the database :-
\nRMAN> rman target \/
\nRMAN> recover database noredo;<\/p>\n
Starting recover at 28-DEC-14
\nusing target database control file instead of recovery catalog
\nallocated channel: ORA_DISK_1
\nchannel ORA_DISK_1: SID=25 device type=DISK
\nchannel ORA_DISK_1: starting incremental datafile backup set restore
\nchannel ORA_DISK_1: specifying datafile(s) to restore from backup set
\ndestination for restore of datafile 00001: \/u01\/app\/oracle\/oradata\/stand\/system01.dbf
\ndestination for restore of datafile 00002: \/u01\/app\/oracle\/oradata\/stand\/sysaux01.dbf
\ndestination for restore of datafile 00003: \/u01\/app\/oracle\/oradata\/stand\/undotbs01.dbf
\ndestination for restore of datafile 00004: \/u01\/app\/oracle\/oradata\/stand\/users01.dbf
\ndestination for restore of datafile 00005: \/u01\/app\/oracle\/oradata\/stand\/example01.dbf
\nchannel ORA_DISK_1: reading from backup piece \/tmp\/rman_bkp\/stnd_backp_0cpr8v08_1_1.bak
\nchannel ORA_DISK_1: piece handle=\/tmp\/rman_bkp\/stnd_backp_0cpr8v08_1_1.bak tag=TAG20141228T025048
\nchannel ORA_DISK_1: restored backup piece 1
\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:03<\/p>\n
Finished recover at 28-DEC-14
\nexit.<\/p>\n
Step 9 : Shutdown the physical standby database, start it in nomount stage and restore the standby controlfile
\nbackup that we had taken from the primary database.<\/p>\n
SQL> shut immediate;
\nSQL> startup nomount;<\/p>\n
[oracle@cluster2 rman_bkp]$ rman target \/
\nRMAN> restore standby controlfile from ‘\/tmp\/rman_bkp\/stnd_0epr8v4e_1_1.ctl’;
\necovery Manager: Release 11.2.0.1.0 – Production on Sun Dec 28 03:08:45 2014<\/p>\n
Copyright (c) 1982, 2009, Oracle and\/or its affiliates. All rights reserved.<\/p>\n
connected to target database: PRIM (not mounted)<\/p>\n
RMAN> restore standby controlfile from ‘\/tmp\/rman_bkp\/stnd_0epr8v4e_1_1.ctl’;<\/p>\n
Starting restore at 28-DEC-14
\nusing target database control file instead of recovery catalog
\nallocated channel: ORA_DISK_1
\nchannel ORA_DISK_1: SID=20 device type=DISK<\/p>\n
channel ORA_DISK_1: restoring control file
\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01
\noutput file name=\/u01\/app\/oracle\/oradata\/stand\/stand.ctl
\noutput file name=\/u01\/app\/oracle\/flash_recovery_area\/stand\/stand.ctl
\nFinished restore at 28-DEC-14<\/p>\n
Step 10: Shutdown the standby database and mount the standby database, so that the standby database would
\nbe mounted with the new controlfile that was restored in the previous step.<\/p>\n
SQL> shut immediate;
\nSQL> startup mount;<\/p>\n
At standby database:-
\nSQL> alter database recover managed standby database disconnect from session;<\/p>\n
At primary database:-
\nSQL> select thread#,max(sequence#) from v$archived_log group by thread#;<\/p>\n
THREAD# MAX(SEQUENCE#)
\n———- ————–
\n1 215<\/p>\n
At standby database:-
\nSQL> select thread#,max(sequence#) from v$archived_log group by thread#;<\/p>\n
THREAD# MAX(SEQUENCE#)
\n———- ————–
\n1 215<\/p>\n
Step 11.Now we will cancel the recovery to open the database
\nSQL> alter database recover managed standby database cancel;<\/p>\n
SQL> alter database open;
\nDatabase altered.<\/p>\n
SQL> alter database recover managed standby database using current logfile disconnect from session;
\nDatabase altered.<\/p>\n
SQL> select open_mode from v$database;<\/p>\n
OPEN_MODE
\n——————–
\nREAD ONLY WITH APPLY<\/p>\n
Now standby database is in sync with the Primary Database.<\/p>\n","protected":false},"excerpt":{"rendered":"
How to sync standby database which is lagging behind from primary database Primary Database cluster: cluster1.rmohan.com Standby Database cluster: cluster2.rmohan.com<\/p>\n
Primary Database: prim Standby database: stand<\/p>\n
Database version:11.2.0.1.0<\/p>\n
Reason:- 1. Might be due to the network outage between the primary and the standby database leading to the archive gaps. Data guard would be able to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7299"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7299"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7299\/revisions"}],"predecessor-version":[{"id":7300,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7299\/revisions\/7300"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7299"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7299"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7299"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}