Monday, August 8, 2011

Howto Recover Archive Gap in Streams Configuration

When i realized that my archive logs are not shipping to the destination database (both the databases are version 10.2.0.4) which is using downstream capture process for streams replication it was too late that i already missed around 20 archived logs. I fixed the problem which was originated from the different service definition in the log_archive_dest_2 system parameter and the TNS alias. But what about the missing archived logs?

--SOURCE DB
SQL> select name, value from v$parameter where name = 'log_archive_dest_2';

NAME               VALUE
----               -----
log_archive_dest_2 SERVICE=ODSD ASYNC NOREGISTER
                   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   
                   DB_UNIQUE_NAME=ODSD


After correcting the entry in the tnsnames.ora file log shipping started from where it paused. I tested it by simply archiving the current redolog.

--SOURCE DB
SQL> alter system archive log current;
System altered.

SH> ls
-rw-r-----    1 oracle   dba      43451392 Aug 05 09:00 1_21759_657122256.dbf
-rw-r-----    1 oracle   dba       8708608 Aug 05 09:57 1_21760_657122256.dbf

--DESTINATION DB
SH> ls
-rw-r-----    1 oracle   dba      15930368 Aug  4 16:38 1_21727_657122256.dbf
-rw-r-----    1 oracle   dba       8708608 Aug  5 09:58 1_21760_657122256.dbf


Now the question how can i recover the gap between the last archivelog and the one created approximately 12 hours ago. First i should define the exact archived logs should be carried from the source to the target. I will check the capture process and see which SCN is it waiting for. As i am using a downstream capture process i should check the capture process in the target database.

--DESTINATION DB
SQL> select capture_name, state from v$streams_capture;

CAPTURE_NAME       STATE
------------       -----
CAPTURE_TABLE_GRP1 WAITING FOR DICTIONARY REDO: SCN 7523421102323


I find the SCN number which is waited in the target. I should find which archived log is this scn in the source database?

--SOURCE DB
SQL> SELECT 
  name, dest_id, sequence#, first_change#, next_change#, completion_time 
FROM V$ARCHIVED_LOG where 7523421102323 between first_change# and next_change#

NAME                               DEST_ID SEQUENCE# FIRST_CHANGE# COMPLETION_TIME
----                               ------- -------- ------------- ---------------
/oracle/.../1_21728_657122256.dbf  1       21728    7523421102321 04/08/11 16:38:52
ODSD,                              2       21728    7523421102321 04/08/11 16:38:54
/oracle/.../1_21729_657122256.dbf  1       21729    7523421102323 04/08/11 16:39:10


It seems that after sequence# 21728 archived logs are not shipped to the destination database. What i will do is to copy these archived logs manually to the destination host from the target by using OS commands.

After copying the archived logs i have to register the archived logs for the streams configuration. The beginning of the register command is similar to the one we already know. And a small oppss!...

--DESTINATION DB
SQL> alter database register logical logfile '/oracle/admin/ODSD/archive/1_21759_657122256.dbf';

ORA-16225: Missing LogMiner session name for Streams

SQL> select name, source_database from DBA_LOGMNR_SESSION;

ID NAME               SOURCE_DATABASE
-- ----               ---------------  
2  CAPTURE_TABLE_GRP1 CORED.CEB.LOCAL

SQL> alter database register logical logfile '/oracle/admin/ODSD/archive/1_21759_657122256.dbf' for 'CAPTURE_TABLE_GRP1';

Database altered.

SQL> select logmnr_session_id, name from DBA_LOGMNR_LOG; --or dba_registered_archived_log

LOGMNR_SESSION_ID NAME
----------------- ----
2                 /oracle/admin/ODSD/archive/1_21759_657122256.dbf
2                 /oracle/admin/ODSD/archive/1_21760_657122256.dbf
2                 /oracle/admin/ODSD/archive/1_21761_657122256.dbf


Lets check the capture process again. And the second oppss!..

--DESTINATION DB
SQL> select capture_name, state from v$streams_capture;

no rows selected.

SQL> select capture_name, status, captured_scn, applied_scn from dba_capture;

CAPTURE_NAME  STATUS CAPTURED_SCN APPLIED_SCN
------------  ------ ------------ -----------
CAPTURE_TABLE_GRP1 ABORTED 7523421102323 7523421102323


It seems the capture process is aborted while we are registering the archived logs. Maybe we need to restart it.


SQL> exec DBMS_CAPTURE_ADM.START_CAPTURE('CAPTURE_TABLE_GRP1');

SQL> select capture_name, state from v$streams_capture;

CAPTURE_NAME       STATE
------------       -----
CAPTURE_TABLE_GRP1 WAITING FOR DICTIONARY REDO: SCN 7523573032011


It seems the problem is solved and the capture process moves on with the next scn and the archived logs.