Wednesday, March 17, 2010

Resolving Archive Gaps in Standby Database

Primary : primary (node1 – rac01 , node2 – rac02) [ 2 node RAC with ASM ]
Standby (local ) : standby1 [ ASM ]
Standby (DR site) : standby2 [ASM]
Operating System : Windows 2003

Scenario :


I found that I have archive log gap on my standby1(local standby), but luckily I had the archive log that was missing in standby1 in primary( Primary).


1>Fire this query on the standby database :

SQL> Select Low_Sequence#,High_Sequence# From V$Archive_Gap;

no rows selected

This means there is no archivelog gap in the standby database.

--- Else --

SQL> Select Low_Sequence#,High_Sequence# From V$Archive_Gap;


THEN FOLLOW THE PROCEDURE :

Query v$archived_log on the PRIMARY to find the location of the missing archive log files.

SQL> SELECT name
2 FROM v$archived_log
3 where sequence# BETWEEN 10270 and 10271;

NAME
--------------------------------------------------------------------------------

E:\ARCHIVE\ARC10270_0647314995.002
+ARCH/primary/arc10270_0647314995.002
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=DBDRC)(PORT=1521)))(CONN

ECT_DATA=(SERVICE_NAME=standby2_XPT.san.com)(INSTANCE_NAME=standby2)(SERVER=dedicated)))


E:\ARCHIVE\ARC10271_0647314995.002
+ARCH/primary/arc10271_0647314995.002
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=DBDRC)(PORT=1521)))(CONN

ECT_DATA=(SERVICE_NAME=standby2_XPT.san.com)(INSTANCE_NAME=standby2)(SERVER=dedicated)))


2>Copy the archive logs from PRIMARY Database to STANDBY Database


On the STANDBY :

SQL> alter database register logfile 'E:\archive\ARC10270_0647314995.002';

Database altered.

SQL> alter database register logfile 'E:\archive\ARC10271_0647314995.002';

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> Select Low_Sequence#,High_Sequence# From V$Archive_Gap;

no rows selected





========== WATCH OUT !!! ========


POSSIBLE ERROR MESSAGES
========================

SQL> select message from v$dataguard_status where severity like 'Warning';

MESSAGE
--------------------------------------------------------------------------------

RFS[1]: No standby redo logfiles created
RFS[2]: No standby redo logfiles created
RFS[2]: No standby redo logfiles created
RFS[2]: Successfully opened standby log 11: '+DATA/primary/redo11.log'
RFS[2]: Possible network disconnect with primary database
RFS[1]: Possible network disconnect with primary database
RFS[4]: Successfully opened standby log 11: '+DATA/primary/redo11.log'
RFS[5]: Successfully opened standby log 11: '+DATA/primary/redo11.log'
RFS[6]: Successfully opened standby log 12: '+DATA/primary/redo12.log'
RFS[5]: Successfully opened standby log 11: '+DATA/primary/redo11.log'

--- Alert.log contents----


Wed Nov 18 08:58:06 2009
ARCH: Possible network disconnect with primary database
FAL[server]: Fail to queue the whole FAL gap
GAP - thread 1 sequence 6501-6501
DBID 1279187827 branch 647314995
ARCH: Possible network disconnect with primary database
Wed Nov 18 09:19:46 2009
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 14: '+DATA/primary/redo14'
Wed Nov 18 09:19:49 2009
Fetching gap sequence in thread 1, gap sequence 5726-5822
Wed Nov 18 09:20:53 2009
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 5726-5822
DBID 1279187827 branch 647314995
FAL[client]: All defined FAL servers have been attempted.


--- Alert.log contents------

========== WATCH OUT !!! ========

No comments:

Post a Comment