After an incomplete recovery of the source database, where the database was opened using “ALTER DATABASE OPEN RESETLOGS”, your Oracle Streams downstream capture process will fail to the following errors, seen in the target database instance alert log:
ORA-01346: LogMiner processed redo beyond specified reset log scn
ORA-01280: Fatal LogMiner Error.
The following procedure can be used to reinstate the downstream capture process:
1. Logon to target database as Streams Admin user and stop the downstream capture and apply processes.
sqlplus streams_admin/streams_admin
SQL> exec dbms_apply_adm.stop_apply(apply_name=>’SRC_SCHEMA_APPLY’)
SQL> exec dbms_capture_adm.stop_capture(capture_name=>’SRC_SCHEMA_CAPTURE’)
2. Logon to source database as Streams Admin user. Generate and implicitly obtain the first SCN on source DB containing the data dictionary.
sqlplus streams_admin/streams_admin
SQL>
SET SERVEROUTPUT ON
DECLARE
scn NUMBER;
BEGIN
DBMS_CAPTURE_ADM.BUILD(
first_scn => scn);
DBMS_OUTPUT.PUT_LINE('First SCN Value = ' || scn);
END;
/
First SCN Value = 1900359521
PL/SQL procedure successfully completed.
3. Logon to target database as Streams Admin user and drop capture process.
sqlplus streams_admin/streams_admin
SQL> exec dbms_capture_adm.drop_capture (‘SRC_SCHEMA_CAPTURE’);
PL/SQL procedure successfully completed.
4. Create capture process on target database. Use SCN obtained in step 2 for both start_scn and first_scn parameters of DBMS_CAPTURE_ADM.CREATE_CAPTURE procedure.
BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'STREAMS_ADMIN.STREAMS_SRC_Q',
capture_name => 'SRC_SCHEMA_CAPTURE',
rule_set_name => NULL,
start_scn => 1900359521,
source_database => '&src_db_name',
use_database_link => TRUE,
first_scn => 1900359521,
logfile_assignment => 'implicit');
END;
/
5. Add capture rules using DBMS_STREAMS_ADM.ADD_SCHEMA_RULES procedure, as in example below:
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'SRC',
streams_type => 'CAPTURE',
streams_name => 'SRC_SCHEMA_CAPTURE',
queue_name => 'STREAMS_ADMIN.STREAMS_SRC_Q',
include_dml => TRUE,
include_ddl => FALSE,
source_database => '&src_db_link',
and_condition => ':dml.get_command_type() != ''DELETE'');
END;
/
6. Add capture performance settings using DBMS_STREAMS_ADM.SET_PARAMETER and DBMS_STREAMS_ADM.ALTER_CAPTURE procedures, as in example below:
BEGIN
dbms_capture_adm.set_parameter( capture_name => 'SRC_SCHEMA_CAPTURE',
parameter => '_CHECKPOINT_FREQUENCY',
VALUE => '1000');
dbms_capture_adm.alter_capture( capture_name => 'SRC_SCHEMA_CAPTURE',
checkpoint_retention_time => 7);
dbms_capture_adm.set_parameter( capture_name => 'SRC_SCHEMA_CAPTURE',
parameter => 'PARALLELISM',
VALUE => '2');
dbms_capture_adm.set_parameter( capture_name => 'SRC_SCHEMA_CAPTURE',
parameter => '_SGA_SIZE',
VALUE => '100');
END;
/
7. Start the combined capture and apply process on the target database.
SQL> exec dbms_apply_adm.start_apply(apply_name=>’SRC_SCHEMA_APPLY’)
SQL> exec dbms_capture_adm.start_capture(capture_name=>’SRC_SCHEMA_CAPTURE’)
8. Log back onto source database and switch log files to initiate the downstream capture process.
sqlplus / as sysdba
SQL> alter system archive log current;
System altered.
9. Logon to target database and check capture process is “capturing changes”.
sqlplus streams_admin/streams_admin
SQL> select CAPTURE_NAME, STATE from v$streams_capture;
CAPTURE_NAME STATE
------------------ -----------------
SRC_SCHEMA_CAPTURE CAPTURING CHANGES
10. If you wish to re- enable downstream real-time mine, this can be done by executing the following procedure after Streams archivelog downstream mine has reinitialised. (downstream real-time mine also requires standby redolog files on the target database). Then re-execute step 8 to make the transition.
BEGIN
dbms_capture_adm.set_parameter( capture_name => 'SRC_SCHEMA_CAPTURE',
parameter => 'downstream_real_time_mine',
VALUE => 'Y');
END;
/
____________________________________________________________
Should the capture process not advance and appears stuck in one of the following states:
select * from v$streams_capture shows :
- INITALIZING / DICTIONARY INITIALIZATION (the state alternates between these states), or
- WAITING FOR DICTIONARY REDO: FIRST SCN <SCN> , or
- WAITING FOR REDO: LAST SCN MINED <SCN> , or
- WAITING FOR DICTIONARY REDO: FILE <filename>
Then perform these additional steps:
11. Repeat step 1 and 2 above
12. Make a note of the System Change Number returned from step 2 and alter the downstream capture process on the target database to change the start SCN to this number.
SQL> exec dbms_capture_adm.alter_capture(capture_name=>’SRC_SCHEMA_CAPTURE’,start_scn=><SCN>)
13. Start the apply and capture processes on the target database.
SQL> exec dbms_apply_adm.start_apply(apply_name=>’SRC_SCHEMA_APPLY’)
SQL> exec dbms_capture_adm.start_capture(capture_name=>’SRC_SCHEMA_CAPTURE’)
14. Check the capture process is “capturing changes”.
SQL> select CAPTURE_NAME, STATE from v$streams_capture;
CAPTURE_NAME STATE
------------------ -----------------
SRC_SCHEMA_CAPTURE CAPTURING CHANGES
