R12.2 Apps DBA. Powered by Blogger.

Resolve huge archive gap between PRIMARY and STANDBY

No comments :
A Physical Standby database synchs with Primary by continuous apply of archive logs from a Primary Database.
When the logs are missing on standby difference is huge (say more than 500 logs), you have to rebuild the standby database from scratch.
With out rebuild standby database, As an enhancement from 10g, an incremental backup created with BACKUP INCREMENTAL… FROM SCN can be used to refresh the standby database.
Please use below query to find out archive gap on Standby:
SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
  Thread        Last Sequence Received      Last Sequence Applied      Difference
———-              ———————-                  ———————             ———-  
1                               8254                                      7954                          300
 Find the SCN on the PRIMARY:
SQL> select current_scn from v$database;
 CURRENT_SCN
———–  
242671761
Find the SCN on the STANDBY:
 SQL> select current_scn from v$database;
CURRENT_SCN
———–  
223771173
Clearly you can see there is difference
Stop and shutdown the managed standby apply process:
SQL> alter database recover managed standby database cancel;
Database altered.
Shutdown the standby database
SQL> shut immediate
On the primary, take an incremental backup from the SCN number where the standby current value 223771173:
 RMAN> run { allocate channel c1 type disk format ‘/backup/%U.bkp’;
backup incremental from scn 223771173 database;
 }
On the primary, create a new standby controlfile:
SQL> alter database create standby controlfile as ‘/backup/for_standby.ctl’;
Database altered.
Copy the standby controlfile to STANDBY and bring up the standby instance in nomount status with standby controlfile:
SQL> startup nomount
SQL> alter database mount standby database;
Connect to RMAN on STANDBY, Catalog backup files to RMAN using below commands:
$ rman target=/
RMAN> catalog start with ‘/backup’;
PERFORM RECOVER:
RMAN> recover database;
Start managed recovery process:
SQL> alter database recover managed standby database disconnect from session;
Database altered.

Check the SCN’s in primary and standby it should be close to each other.

No comments :

Post a Comment

Note: only a member of this blog may post a comment.