Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 5 May 2013

Roll forward a Physical Standby DB using RMAN incr. Backup

Scenario: There can be a situation where standby DB logs far behind the primary DB leading to archive gap. It could be due to any of the reason:
Suppose network is down between primary and standby server or standby server is down due to any hardware failure for long time and in between down time, you have remove archive log file from primary server without taking backup of archive log files.
In the above case, we can use RMAN incremental backups to synchronize a physical standby database with the primary database. By using FROM SCN command with RMAN incremental backup command (to take backup from that particular SCN to till), we can create a backup on the primary database that starts at the standby database’s current SCN, which can then be used to roll forward the standby database.
Detect the Log Gap between Primary and Standby Server
SQL> Select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
------- ----------------
1       997
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
------- ----------------
1       714
Thus the standby database is lagging behind of primary database 283 archives.
Note: The gap between standby and primary server due to network failure can be automatically detected by dataguard once the connection is re-established. Check the link for Script: Gap Detection on Standby Database
Step 1: On the standby database, stop the managed recovery process (MRP):
SQL> ALTER DATABASE recover managed standby database CANCEL;
Step 2: Find the SCN On the standby database:
SQL> SELECT current_scn from V$DATABASE;
CURRENT_SCN
-----------
1438
Step 3: On the Primary DB take RMAN incremental backup (from the SCN that is currently recorded) and transfer this to standby site:
RMAN> RMAN TARGET SYS/ORACLE@SADHAN
RMAN> BACKUP incremental from 1438 database format ‘H:\oraback\sad_inc_%U’ tag ‘EMERGENCY_BACKUP’;
Now also take the standby controlfile backup of primary database controlfile
RMAN> BACKUP current controlfile for standby format ‘H:\oraback\sadstby_%U.ctl’;
Transfer these backup on standby server on the same location or directory where you have created on the primary server incase of different location you need to catalog that location for accessing the backup on standby site with RMAN> CATALOG START WITH ‘H:\oraback’;
Step 4: Recover the standby database with incremental backup:
RMAN> RECOVER DATABASE noredo;
Step 5: Startup the Standby DB in NOMOUNT to restore controlfile
RMAN> shutdown immediate;
RMAN> Startup nomount;
RMAN> Restore STANDBY CONTROLFILE from ‘H:\oraback\sadstby_ 14n7p3st_1_1.ctl
Step 6: Mount the database and Recover the Standby DB:
RMAN> Alter database MOUNT;
Step 7: If the primary and standby DB datafile location are identical, skip to step8
If the primary and standby database datafiles directories are different, then you need to catalog the standby datafiles, and switch the standby DB to use cataloged data files. 
RMAN> CATALOG start with ‘+ORADATA/SADHAN’;
RMAN> SWITCH database to COPY;

Step 8: If the primary & standby DB redologs location are identical, skip to step 9.
If the primary and standby database redologs directories are different then use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths such as: LOG_FILE_NAME_CONVERT=’D:\sadhan’,’D:\sadstby’
Step 9: On the standby site, clear all standby redo log groups
SQL> Alter database CLEAR Logfile GROUP 1;
SQL> Alter database CLEAR Logfile GROUP 2;
SQL> Alter database CLEAR Logfile GROUP 3;
Step 10: On the standby site, restart Flashback (Optional):
SQL> Alter database FLASHBACK OFF;
SQL> Alter database FLASHBACK ON;
Step 11: On the standby database, restart MRP:
SQL> Alter database recover MANAGED STANDBY DATABASE disconnect from session;
SQL> select process, status, sequence# from v$managed_standby;
Step12: Check the synchronization between physical and standby server:
SQL> Select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
------- ----------------
1       1001
SQL> Select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
------- ----------------
1       1001
Thus we can see that the maximum archivelog sequence generated on primary database and applied on standby database is same that means the standby db is synchronized with primary database. Now we can generate some archivelog on primary database and we can check that it is shipped and applied on the standby site.
SQL> alter system switch logfile;
SQL> /
System altered.
SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
THREAD# MAX(SEQUENCE#)
------- ----------------
1       1003
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
------- ----------------
1       1003
Check related post:

0 comments:

Post a Comment