ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to recover Streams capture when it is 'WAITING For REDO' [ID 843267.1]

How to recover Streams capture when it is 'WAITING For REDO' [ID 843267.1]

原创 Linux操作系统 作者:lhl1212 时间:2011-12-06 00:27:27 0 删除 编辑

In this Document
  Symptoms
  Changes
  Cause
  Solution
     A) When MISSING archivelogs CAN be restored.
     B) When MISSING archivelogs CANNOT be restored.
  References


 

 

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Symptoms

This article will help in the situation where capture process gets stuck on waiting for some of the archive log which is either missing or are deleted.

Changes

Cause

Archive logs might get deleted by the RMAN job or less value for the retention policy.

Solution

Streams works on the concept of redo data mining. The redo logs can be archived in the form. of archive logs. Streams capture process mine these archives and replicate all the underlying actions/operations.

Oracle maintains the continuity of these archive information in terms of SCN values. If any archive are missing or deleted by manually or by RMAN jobs then the continuity sequence breaks and Streams capture process will not be able to move until the missing archive logs will be available .

Check the capture process status:

SQL> SELECT CAPTURE_NAME, STATE FROM V$STREAMS_CAPTURE;

Capture_Name State
------------------- -------
STREAMS_CAPTURE WAITING FOR REDO: LAST SCN MINED 10404900045757

Note.471713.1 “Different States of Capture & Apply Process.”



The following solution in this note will help to recover Streams in two scenarios

A) When MISSING archivelogs CAN be restored.
B) When MISSING archivelogs CANNOT be restored.

A) When MISSING archivelogs CAN be restored.

       i) Get all the archive logs required for the capture process to get restarted :

Refer :

Note.290143.1 "Minimum Achived Log Necessary to Restart 10g and 11g Streams Capture Process".

Or simply query :

SQL> SELECT THREAD#, NAME, SEQUENCE# FROM V$ARCHIVED_LOG
WHERE 10404900045757 BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#;

Note.735976.1 "All Replication Configuration Views For Streams, AQ, CDC and Advanced Replication"

  • The output of the above query will give you the name of the archivelogs which are required for the catpure process.
  • Make sure all the archives are available to the capture process from this archive sequence number.
  •  So you need to restore them back to the archive destination.

     
      ii)  Copy all the missing archives from the backup to the main archive location
           or in case of RMAN use the following syntax :

Example:

RMAN> run {
set archivelog destination to ‘/tmp’;
restore archivelog from logseq=60 until logseq=70;
}


       iii). After restoring all the archive logs, manually register them for the capture process.
            This will enable the capture process to mine those archive logs.

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE
'Full Path & File_name' FOR '';

Example :
---------
ALTER DATABASE REGISTER LOGICAL LOGFILE
'/oracle/logs_from_dbs1/1_10_456789.dbf' FOR 'strms_capture'; 


In some cases the following Bugs may be applicable:

Bug 5278539
Bug 5602452

Both are fixed in 10.2.0.4 and above.


B) When MISSING archivelogs CANNOT be restored.


        i). In case of restoration of the missing archive logs is not possible due to un-availability
            of the backup of the old archive logs, Then the whole streams need to rebuild.

NOTE: There is no way that the capture process can skip these missing archive logs and jump to the next available archive log. Streams gets stuck when the archve logs are not available.


       ii). In such cases, stop and drop the capture and apply  process and manually sync both
            the databases.

exec DBMS_CAPTURE_ADM.STOP_CAPTURE('');
exec DBMS_APPLY_ADM.STOP_APPLY('');

exec DBMS_CAPTURE_ADM.DROP_CAPTURE('')
exec DBMS_APPLY_ADM.DROP_APPLY('');

       iii). Manually re-sync both the databases by using exp/imp utility or TTS or RMAN.
             Make sure all the replicated objects are in sync.

Refer:
Note.550955.1 "Instantiating Objects in a 10g Streams Using
Original Export/Import and Data Pump Export/Import - Example"
Note.551106.1 "Instantiating Objects in Streams Using
Transportable Tablespace or RMAN"


       iv). Create the capture and apply process.

       v). Perform. instantiation.


On the Source DB
============

begin
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION('');
end;
/

set serveroutput on
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;
/

On the Target DB
===========

BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name => '',
source_database_name => '',
recursive => TRUE,
instantiation_scn => &iscn); <== iscn from the Source DB.
END;
/

Note.429543.1 "Purpose of Instantiation in Streams Environment"


       vii).Start the capture and apply process.

exec DBMS_CAPTURE_ADM.START_CAPTURE('');
exec DBMS_APPLY_ADM.START_APPLY('');


       viii). Verify the setup

Refer
Note.729860.1 "Troubleshooting Queries in Streams"

References

NOTE:273674.1 - Streams Configuration Report and Health Check Script
NOTE:471713.1 - Different States of Capture & Apply Process
NOTE:550955.1 - Instantiating Objects Using Original Export/Import and Data Pump Export/Import - Example
NOTE:551106.1 - Instantiating Objects in Streams Using Transportable Tablespace or RMAN
NOTE:729860.1 - Troubleshooting Queries in Streams
NOTE:735976.1 - All Replication Configuration Views For Streams, AQ, CDC and Advanced Replication


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/611609/viewspace-712771/,如需转载,请注明出处,否则将追究法律责任。

下一篇: AIX日常维护
请登录后发表评论 登录
全部评论

注册时间:2008-12-08

  • 博文量
    252
  • 访问量
    488446