Solved

WAITING FOR DICTIONARY REDO: FILE /u0/archivelog/db1/DB1_675367766.1.ARC

Posted on 2009-07-09
12
1,748 Views
Last Modified: 2013-12-19
Hi

We have setup replication  for table from DB1 to DB2. And see capture status as

WAITING FOR DICTIONARY REDO: FILE /u0/archivelog/make/938_675367766.1.ARC

As it is a DEV box we removed archive logs and now Archive logs are missing and process is waiting for this archive logs.

What steps I need to perform such that it capture changes from current archive logs insted of old.

Thanks
0
Comment
Question by:basirana
  • 5
  • 4
  • 3
12 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 24812763
ALTER DATABASE REGISTER LOGICAL LOGFILE <file spec> FOR <capture name>;
according
http://forums.oracle.com/forums/thread.jspa?threadID=551658

Also consider ALTER CAPTURE procedure in DBMA_ADMIN_CAPTURE package.
0
 

Author Comment

by:basirana
ID: 24813029
Thanks for your reply. I din't understand the link which u provided can you pls help me with steps.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24813047
Almost same situation is described there. Or I am wrong?
0
 

Author Comment

by:basirana
ID: 24813269
I followed the steps but it is not working for some reason.
I will see same message in capture status.

Thanks





SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/u0/archivelog/make/945_675367766.1.ARC' FOR 'STREAMS_CAPTURE';

Database altered.
 

SQL> exec dbms_capture_adm.prepare_table_instantiation('SCOTT.EMP');

PL/SQL procedure successfully completed.
 

I still see capture status as

WAITING FOR DICTIONARY REDO: FILE /u0/archivelog/make/938_675367766.1.ARC

Open in new window

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24813551
The logfile you registered is not the one it is waiting for.

945 vs 938

0
 

Author Comment

by:basirana
ID: 24813557
Is there a way I skip the archive logs which capture process is waiting and start the capture process from the file which is available?

How can I do that?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24813604
Are you saying you deleted archived logs that the capture needs?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24813677
I think you have to start over. I am not a streams expert, but I know that deleting the archive logs before the capture used them is not correct.

The docs say:

WAITING FOR DICTIONARY REDO - Waiting for redo log files containing the dictionary build related to the first SCN to be added to the capture process session. A capture process cannot begin to scan the redo log files until all of the log files containing the dictionary build have been added.


See, it says it cannot begin. You cannot just skip them, I think, since they are dictionary REDO. They are not optional.

The error was deleting archive logs in the first place, so I think reconfiguring the streams setup is the only option. Sorry, thats all I can suggest.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28321/strms_trouble.htm#i1006114

Read the part that is titled "Are Required Redo Log Files Missing"

One suggestion is to make sure not to store archive logs in the FRA so RMAN will not delete them prior to capture.
0
 

Author Comment

by:basirana
ID: 24813681
Actually we donot having the backup of the archivelog which has been deleted.
How can I skip all those archive logs which are not available and start from the one which is available instead of waiting.

Thanks
0
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 24813791
In such cases, stop and drop the capture and apply  process
and manually sync both the databases.


exec DBMS_CAPTURE_ADM.STOP_CAPTURE('<CAPTURE_NAME>');
exec DBMS_APPLY_ADM.STOP_APPLY('<APPLY_NAME>');

exec DBMS_CAPTURE_ADM.DROP_CAPTURE('<CAPTURE_NAME>')
exec DBMS_APPLY_ADM.DROP_APPLY('<APPLY_NAME>');


Manually re-sync both the databases by using exp/imp utility or TTS or RMAN.
Make sure all the replicated objects are in sync.
 Create the capture and apply process.
  Perform instantiation.




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

begin
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION('<Schema_Name>');
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 => '<Schema_Name>',
source_database_name => '<Source_DB_Name>',
recursive => TRUE,
instantiation_scn => &iscn); <== iscn from the Source DB.
END;
/

 

Start the capture and apply process.


exec DBMS_CAPTURE_ADM.START_CAPTURE('<CAPTURE_NAME>');
exec DBMS_APPLY_ADM.START_APPLY('<APPLY_NAME>');

Verify the setup
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24813821
In addition to schwertner's instructions, you might want to read:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28321/strms_capture.htm#i1007010

Read section "Instantiation in an Oracle Streams Environment"

0
 

Author Closing Comment

by:basirana
ID: 31601533
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Bulk insert into global temporary table 2 63
T-SQL Convert to PL/SQL 23 80
Encryption Decryption in Oracle 12 107
Dataware house query tuning 9 65
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now