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.***

Saturday, 19 May 2012

How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary?

Suppose we have deleted some archive log files from primary and we do not have backup of the deleted archive files. Normally we should not allow such a situation but such a situation can happen to us. In this case,  we first need to know the current SCN number of Primary and standby databases.
1. check current SCN number with the following query on the Primary.
SQL> select current_scn from v$database;
2. check current SCN number with the following query on the Standby
SQL> select current_scn from v$database;
I have set up a real time apply physical standby and trying to find how Real Time it is. However it seems that it is hard to find the latest SCN# on physical standby database, v$database (current_scn) only gets updated to the last scn which has been archived. Here is what I got:
SQL> select recovery_mode from v$archive_dest_status where dest_id=2;
SQL> select name, value, time_computed from v$dataguard_stats where
name='apply lag';
NAME            VALUE                TIME_COMPUTED
--------------- -------------------- ------------------------------
apply lag       +00 00:00:00         12-JAN-2012 15:26:02
Check time gap between Primary and standby using the function scn_to_timestamp(SCN_NUMBER). The below example is not a real time sinario.
SQL> select scn_to_timestamp(current_scn) from v$database;
12-JAN-12 PM
SQL> select scn_to_timestamp(579664324) from v$database;
11-JAN-12 PM

3. Stop apply process on the Standby database.
SQL> alter database recover managed standby database cancel;
4. Shutdown the Standby database.
SQL> shutdown immediate;
5. Take incremental backup from the latest SCN number of the Standby database on the Primary database. And copy backup to the standby server.
RMAN> backup incremental from scn 2188350932 database;
# Copy this backup from Primary site to standby site using xcopy or any copy command
6. Create new standby control file on the Primary database. And copy this file to standby server.
SQL> alter database create standby controlfile as 'C:\oracle1\orcl3\standby.ctl';
# copy this controlfile on standby server using any standrad copy command.
7. Open the Standby database in NOMOUNT phase
SQL> startup nomount
SQL> show parameter control_files
8. Replace new standby control file with old files.
Use standard move or copy copy command and overwrite new file on the old file.
9. Open the Standby database on MOUNT state.
SQL> alter database mount standby database;
10. Connect to the RMAN and register backup to catalog.
C:\ rman target /
RMAN> catalog start with 'C:\oracle\orcl3\Backup';
It will ask for confirmation. Click "Y".
11. Now you are able to recover the Standby database. Start recovery database.
RMAN> recover database;
When recover of database is finished, it searches the latest archive file. And it gives an ORA-00334 error. In this case, don’t worry about it. Exit from RMAN and start apply process on the standby database.
SQL> alter database recover managed standby database disconnect from session;

We solved the Primary/Standby log gap with RMAN incremental backup . When you face with such a situation don't be panic try to recover it. Initially i faces too many problem but finally i have done it. I hope it will help you!!


  1. Shahid good work.. can you explain method for same in oracle 9i please !