In order to ensure the Oracle Data Dictionary is available in the redo logs, two changes are needed. First a copy of the dictionary has to be periodically dumped to the log and second the Capture configuration must be modified to use the redo log copy of the dictionary.
This section describes the process that must be implemented to extracted (dump) the current dictionary and add it the redo log. It involves the periodic execution of the following SQL to ensure that a fairly recent copy of the dictionary is available in the archive logs:
BEGIN
SYS.DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
END;
/
exitHow to display where the dictionaries are in your archive logs
1. Prepare a shell script similar to the following:
#!/bin/bash
export ORAENV_ASK=NO
export ORACLE_BASE=/srv/oracle/
export ORACLE_SID=orcl11g
. oraenv
echo "START"
date +'DATE %D TIME:%H:%M:%S.%N'
sqlplus <oracle_id>/<oracle_password> @DispDictLogs
date +'DATE %D TIME:%H:%M:%S.%N'
echo "STOP"2. Create the file named DispDictLogs.sql below to display which logs contain a data dictionary dump:
SET num 20
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD-HH24.MI.SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD-HH24.MI.SS.FF6';
select SEQUENCE#, BLOCKS, CREATOR, FIRST_CHANGE#, NEXT_CHANGE#, STATUS, DICTIONARY_BEGIN, DICTIONARY_END, COMPLETION_TIME from v$archived_log where DICTIONARY_BEGIN = 'YES' OR DICTIONARY_END = 'YES';
exit;Note: When using the LogMiner "redo log option", the Capture must be able to restart at the last dictionary dump before the required mining restart point, which is the start of the oldest unit of work not fully processed by the Apply Engine. The --dict-from-redo-logs is specified as in the capture configuration file, see Create Oracle Capture CAB File below.
How to find the most recent dictionary in the archived logs
- Prepare a shell script similar to the following:
#!/bin/bash export ORAENV_ASK=NO export ORACLE_BASE=/srv/oracle/ export ORACLE_SID=orcl11g . oraenv echo "START" date +'DATE %D TIME:%H:%M:%S.%N' sqlplus <oracle_id>/<oracle_password> @DispDictLag date +'DATE %D TIME:%H:%M:%S.%N' echo "STOP" - Create the file named DispDictLag.sql below to display which logs contains the last data dictionary dumped to the redo log:
set serveroutput on DECLARE xscnd NUMBER(18) := 100; xseqd NUMBER(18) := 100; xscnc NUMBER(18) := 100; xseqc NUMBER(18) := 100; xcur NUMBER(18) := 100; BEGIN SELECT CURRENT_SCN INTO xcur FROM V$DATABASE; SELECT SEQUENCE#, FIRST_CHANGE# into xseqd, xscnd from (SELECT SEQUENCE#, FIRST_CHANGE# from v$archived_log where DICTIONARY_BEGIN = 'YES' ORDER BY SEQUENCE# DESC) where rownum = 1; SELECT SEQUENCE#, FIRST_CHANGE# into xseqc, xscnc from (SELECT SEQUENCE#, FIRST_CHANGE# from v$archived_log ORDER BY SEQUENCE# DESC) where rownum = 1; -- dbms_output.put_line('xscnc '||TO_CHAR(xscnc)); -- dbms_output.put_line('xscnd '||TO_CHAR(xscnd)); -- dbms_output.put_line('xseqc '||TO_CHAR(xseqc)); -- dbms_output.put_line('xseqd '||TO_CHAR(xseqd)); -- dbms_output.put_line('xcur '||TO_CHAR(xcur)); dbms_output.new_line; dbms_output.put_line('Dictionary restart needs '||TO_CHAR(xseqc - xseqd)||' logs'); dbms_output.put_line(' and '||TO_CHAR(xcur - xscnd)||' scns '); dbms_output.new_line; END; / exit;
Note: This is not necessarily the dictionary needed for restart if the safe restart point is before the most recent dictionary dump.