capture – Technology Geek

Capture status : Streams Waiting for dictionary first scn

Example :

Capture is waiting on redo log file with SCN 5611274208824
Capture

The First SCN 5611441137818
Start SCN 5611441137818

Applied Scn 5611441373264
Required SCN 5611441137818

You can get the above information from enterprise manager
Maintenance -> Stream -> management -> capture (choose capture name and edit).

Note :
1-Applied Scn + Required SCN : you can’t change them (read only).

2-Set First SCN + Start SCN same as Required SCN .

[Document 313279.1] Master Note for Troubleshooting Streams capture ‘WAITING For REDO’ or INITIALIZING

ACTION Plan #1 :

A) Restore archived redo logs starting with sequence
B) If you can not restore logs, then capture must be droppend and recreated.
Note 471695.1 – Required Steps to Recreate a Capture Process

You should be running dbms_capture_adm.build on a regular basis to allow you to rebuild capture without having to resync.

ACTION Plan #2 :

look at the output from below sql and ensure that ALL logs (by time) are online and available.

++ Registered Log Files for Capture ++
COLUMN CONSUMER_NAME HEADING ‘Capture|Process|Name’ FORMAT A15
COLUMN SOURCE_DATABASE HEADING ‘Source|Database’ FORMAT A10
COLUMN SEQUENCE# HEADING ‘Sequence|Number’ FORMAT 999999
COLUMN NAME HEADING ‘Archived Redo Log|File Name’ format a35
column first_scn HEADING ‘Archived Log|First SCN’
COLUMN FIRST_TIME HEADING ‘Archived Log Begin|Timestamp’
column next_scn HEADING ‘Archived Log|Last SCN’
COLUMN NEXT_TIME HEADING ‘Archived Log Last|Timestamp’
COLUMN MODIFIED_TIME HEADING ‘Archived Log|Registered Time’
COLUMN DICTIONARY_BEGIN HEADING ‘Dictionary|Build|Begin’ format A6
COLUMN DICTIONARY_END HEADING ‘Dictionary|Build|End’ format A6
COLUMN PURGEABLE HEADING ‘Purgeable|Archive|Log’ format a9

SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME,
r.first_scn,
r.FIRST_TIME,
r.next_scn,
r.next_time,
r.MODIFIED_TIME,
r.DICTIONARY_BEGIN,
r.DICTIONARY_END,
r.purgeable
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME
AND r.SEQUENCE# > 48056
ORDER BY source_database, consumer_name, r.first_scn;

After verifying that all those logs are available, then for capture , advance first/start snc
login to strmadmin

exec dbms_capture_adm.alter_capture(‘capture-name’,first_scn=>same as the above);
exec dbms_capture_adm.alter_capture(‘capture-name’,start_scn=>same as the above);

Verify

SELECT CAPTURE_NAME, FIRST_SCN, START_SCN, APPLIED_SCN, REQUIRED_CHECKPOINT_SCN FROM ALL_CAPTURE;

Restart capture.

##To check if SCN changed :

SELECT SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(‘)+1,4) PROCESS_NAME,
c.CAPTURE_NAME,
C.STARTUP_TIME,
c.SID,
c.SERIAL#,
c.STATE,
c.state_changed_time,
FROM gV$STREAMS_CAPTURE c, gV$SESSION s
WHERE c.SID = s.SID AND
c.SERIAL# = s.SERIAL#;

ACTION PLAN #3 :

If the Missing Archivelog found on ASM but Capture can’t see them you have to do the following :

ALTER DATABASE REGISTER LOGICAL LOGFILE ” FOR ”;

ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE ‘PATH’ FOR ‘Capture-name’;

Then Check By :

select name, sequence# from v$archived_log — use gv$archived_log in RAC
where between FIRST_CHANGE# and NEXT_CHANGE#
order by name;

Hope this will be Useful for you
Finally Don’t forget after you resolve the problem ro run this command once everyday to rebuild capture without having to resync

SQL> exec DBMS_CAPTURE_ADM.BUILD;

Check This Link : Stream Capture

Thank you
Osama Mustafa
Oracle Database Consultant

I faces the most strange issue that you will ever seen , Everything was works fine without any problem but suddenly without any error Replication on database stopped Why ?

No one was know , I try everything that you could be imagine , i try to register the archive log manually :

ALTER DATABASE REGISTER LOGICAL LOGFILE
‘Archivelog-name’ FOR ‘Capture-name’;

But nothing works, after that i start trace Capture process maybe it will give me hints with following produce:

1. Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)

2. Enable tracing:

sqlplus /nolog
connect / as sysdba
alter system set events ‘26700 trace name context forever, level 6’;
alter system set events ‘1349 trace name context forever , level 1024’;
exit

sqlplus strmadmin/passwd (streams admin)

exec dbms_capture_adm.set_parameter(‘yourcapturename’,’trace_level’,’127′);
exec dbms_capture_adm.start_capture(‘yourcapturename’);

Wait for about 30 minutes an check whether capture is or not progressing..

3. To turn off capture tracing:

sqlplus strmadmin/passwd (streams admin)

Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)
exec dbms_capture_adm.set_parameter(‘yourcapturename’,’trace_level’,null);
exit

sqlplus /nolog
connect / as sysdba

alter system set events ‘26700 trace name context off’;
alter system set events ‘1349 trace name context off’;
exit

Yes Nothing works , No error , its make me crazy

Tried checkpoint force, stop/start capture.

tracing the capture process:

1. Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)

exec dbms_capture_adm.set_parameter(‘yourcapturename‘,’trace_level’,’127′);

exec dbms_capture_adm.start_capture(‘yourcapturename‘);
“set trace off after 10-15 mins”:

2. To turn off capture tracing:
exec dbms_capture_adm.set_parameter(‘yourcapturename‘,’trace_level’,null);

Yea Nothing Works too

Then I start check if there’s some missing Archivelogs By :

select NAME,SEQUENCE#,STATUS,DELETED from V$ARCHIVED_LOG

Information about archive logs that needed by Oracle Stream :

SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND
r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN
order by 3;

and using this query could help sometimes to give you information TXN about apply process :

select apply_name, xidusn||’.’||xidslt||’.’||xidsqn txn_id, first_scn, first_message_create_time, message_count, spill_creation_time from dba_apply_SPILL_TXN;

Output : (Usually The last one)

APPLY_FROM_MARKA               9.27.637276                         5600212722474 17-OCT-11            25    18-OCT-11
APPLY_FROM_MARKA               4.23.369085                         5600212761449 17-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               56.17.156892                        5600242177417 18-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               4.40.369226                        5600243099471 18-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               24.21.419884                         5600247380679 18-OCT-11            15      19-OCT-11
APPLY_FROM_MARKA               14.20.166840                         5600278453686 19-OCT-11             1       19-OCT-11

After Check The TXN we need to Run the Stream to ignore the required TXN :

1-exec dbms_apply_adm.stop_apply(‘Apply-process-name’);

2-exec dbms_apply_adm.set_parameter(‘Apply-process-name’,’_ignore_transaction’,’TXN-Number’);

3-exec sys.purge_spill_txn(‘Apply-process-name’,’TXN-Number’);

4-exec dbms_apply_adm.start_apply(‘Apply-process-name’);

5-exec dbms_apply_adm.set_parameter(‘Apply-process-name’,’_ignore_transaction’,null);

Capture Site:

1-execute dbms_capture_adm.stop_capture(‘capture-Process-name’);

2-execute dbms_capture_adm.set_parameter(‘capture-Process-name’,’_ignore_transaction’,’TXN-Number’);

3-execute dbms_capture_adm.start_capture(‘capture-Process-name’);

But Nothing works too .

But I remember something Stream got hidden parameter _SGA_Size :

Capture needs to be configured to have more space.

The default amount of space for capture / log miner activity is 10

Steps :

exec dbms_capture_adm.set_parameter(‘Capture-procees-name’,’_SGA_SIZE’,’50’);

exec dbms_capture_adm.start_capture(‘Capture-procees-name’);

The above changes space for named capture process : ‘STRMADMIN_CAPTURE’ from 10M -> 50M

Thank God Its works again .

Note that i write every steps maybe something will be useful for you .

Some metalink note will be useful too :

Troubleshooting Long-Running Transactions in Oracle Streams (Doc ID 783927.1)

LogMiner out-of-memory (Doc ID 336705.1)

Tag: capture

waiting for dictionary redo first scn

Oracle Stream Stop Replication without any error