DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, October 17, 2014


Recovering a standby database from missing archive logs


There would be scenarios where the standby database lags far behind from the primary database leading to Archive Gap. It could be due to archive logs getting missed out on the primary database or the archives getting corrupted and there would be no valid backups.

In such cases where the standby lags far behind from the primary database, incremental backups can be used as one of the  methods to roll forward the physical standby database to have it in sync with the primary database.


Step 1: Take a note of the Current SCN of the Physical Standby Database.

Standby Database:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
991247

Note down the CURRENT_SCN value of the standby database (991247) to proceed further.


Step 2 : Cancel the Managed Recovery Process on the Standby database.

Standby Database:

SQL>alter database recover managed standby database cancel;


Step 3: On the Primary database, take the incremental SCN backup from the SCN that is currently recorded on the standby database (991247)

Connect to the primary database and take the incremental SCN backup.

Primary Database:

[oracle@dev ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 2515:44:45 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SSPM (DBID=1624493265)

RMAN> backup incremental from scn 991247 database format '/u02/bkp/stnd_backp_%U.bak';

Starting backup at 25-MAR-12

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
backup will be obsolete on date 01-APR-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA_NEW/sspm/datafile/system.256.778803539
input datafile file number=00002 name=+DATA_NEW/sspm/datafile/sysaux.257.778803541
input datafile file number=00003 name=+DATA_NEW/sspm/datafile/undotbs1.258.778803541
input datafile file number=00004 name=+DATA_NEW/sspm/datafile/users.259.778803543
channel ORA_DISK_1: starting piece 1 at 25-MAR-12
channel ORA_DISK_1: finished piece 1 at 25-MAR-12
piece handle=/u02/bkp/stnd_backp_10n6p3nl_1_1.bak tag=TAG20120325T154639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

using channel ORA_DISK_1
backup will be obsolete on date 01-APR-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 25-MAR-12
channel ORA_DISK_1: finished piece 1 at 25-MAR-12
piece handle=/u02/bkp/stnd_backp_11n6p3p4_1_1.bak tag=TAG20120325T154639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-MAR-12


Step 4: Take the standby controlfile backup of the Primary database controlfile.

Connect to the Primary database and create the standby controlfile backup.

Primary Database :

RMAN> backup current controlfile for standby format '/u02/stnd_%U.ctl';

Starting backup at 25-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 25-MAR-12
channel ORA_DISK_1: finished piece 1 at 25-MAR-12
piece handle=/u02/stnd_12n6p3qt_1_1.ctl tag=TAG20120325T154845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-MAR-12

Step 5: Transfer the backups from the Primary Server to the Standby Server.


Step 6: On the standby server, connect the Standby Database through RMAN and catalog the copied incremental backups so that the Controlfile of the Standby Database would be aware of these incremental backups.

I had the incremental backuppieces copied to the location ‘/u02/bkp‘ on the standby server.

Standby Database:

[oracle@uat ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 2515:51:02 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SSPM (DBID=1624493265, not open)

RMAN> catalog start with '/u02/bkp';
Starting implicit crosscheck backup at 25-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
Crosschecked 15 objects
Finished implicit crosscheck backup at 25-MAR-12

Starting implicit crosscheck copy at 25-MAR-12
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 25-MAR-12

searching for all files in the recovery area
cataloging files...
File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_200.453.778846881
File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_201.454.778846881
File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_202.455.778846881
File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_203.456.778846881
File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_137.457.778846881
File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_204.458.778846881
File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_205.459.778846883
.
.
.
.

File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_856.947.778861691
File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_858.949.778861709
File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_857.950.778861719

searching for all files that match the pattern /u02/bkp

List of Files Unknown to the Database
=====================================
File Name: /u02/bkp/stnd_backp_10n6p3nl_1_1.bak
File Name: /u02/bkp/stnd_backp_11n6p3p4_1_1.bak

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u02/bkp/stnd_backp_10n6p3nl_1_1.bak
File Name: /u02/bkp/stnd_backp_11n6p3p4_1_1.bak


Step 7: Recover the standby database with the cataloged incremental backup pieces.


RMAN> recover database noredo;

Starting recover at 25-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/sssb/datafile/system.274.778865099
destination for restore of datafile 00002: +DATA/sssb/datafile/sysaux.275.778865193
destination for restore of datafile 00003: +DATA/sssb/datafile/undotbs1.276.778865259
destination for restore of datafile 00004: +DATA/sssb/datafile/users.277.778865273
channel ORA_DISK_1: reading from backup piece /u02/bkp/stnd_backp_10n6p3nl_1_1.bak
channel ORA_DISK_1: piece handle=/u02/bkp/stnd_backp_10n6p3nl_1_1.bak tag=TAG20120325T154639
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 25-MAR-12


Step 8 : Shutdown the physical standby database, start it in nomount stage and restore the standby controlfile backup that we had taken from the primary database.

Standby Database:

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     659730432 bytes

Fixed Size                     2216264 bytes
Variable Size                398462648 bytes
Database Buffers             255852544 bytes
Redo Buffers                   3198976 bytes

RMAN> restore standby controlfile from '/u02/stnd_12n6p3qt_1_1.ctl';

Starting restore at 25-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/sssb/controlfile/current.273.778864875
Finished restore at 25-MAR-12
Step 9: Shutdown the standby database and mount the standby database, so that the standby database would be mounted with the new controlfile that was restored in the previous step.

Standby Database:

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     659730432 bytes

Fixed Size                     2216264 bytes
Variable Size                398462648 bytes
Database Buffers             255852544 bytes
Redo Buffers                   3198976 bytes



Step 11: On the standby database, start the Managed Recovery Process.

Standby Database:


SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE               1010
RFS       IDLE                  0
MRP0      WAIT_FOR_LOG          0

9 rows selected.


Step 12: On the Primary database, check the Maximum Archivelog Sequence generated.

Primary Database:

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- ----------------
1       1009


Step 13: Check the maximum archivelog sequence that is applied on the Physical standby database.


SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- ----------------
1       1009

So, here we can see from Steps 12 and 13 that the maximum archivelog sequence generated on the Primary database is sequence# 1009 and that applied on the Physical Standby Database is also 1009 which means that the Standby database is in sync with the Primary Database. You can check it out by generating an archive sequence on the Primary database and check if its shipped and applied on the standby database.

Primary Database:

SQL> alter system switch logfile;

System altered.

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- ----------------
1       1010

Standby Database:


SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- ----------------
1       1010


Now standby database is in sync with the Primary Database.

Here you go !!

No comments: