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