This example shows how to use the dictionary that has been extracted to the redo log files. When you use the dictionary in the online catalog, you must mine the redo log files in the same database that generated them. Using the dictionary contained in the redo log files enables you to mine redo log files in a different database.
This example assumes that you know you want to mine the redo log file that was most recently archived.
SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG); NAME SEQUENCE# -------------------------------------------- -------------- /usr/oracle/data/db1arch_1_210_482701534.dbf 210
The dictionary may be contained in more than one redo log file. Therefore, you need to determine which redo log files contain the start and end of the dictionary. Query the V$ARCHIVED_LOG view, as follows:
Find a redo log file that contains the end of the dictionary extract. This redo log file must have been created before the redo log file that you want to analyze, but should be as recent as possible.
SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DICTIONARY_END = 'YES' and SEQUENCE# <= 210); NAME SEQUENCE# D_BEG D_END -------------------------------------------- ---------- ----- ------ /usr/oracle/data/db1arch_1_208_482701534.dbf 208 NO YES
Find the redo log file that contains the start of the data dictionary extract that matches the end of the dictionary found in the previous step:
SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN = 'YES' and SEQUENCE# <= 208); NAME SEQUENCE# D_BEG D_END -------------------------------------------- ---------- ----- ------ /usr/oracle/data/db1arch_1_207_482701534.dbf 207 YES NO
Specify the list of the redo log files of interest. Add the redo log files that contain the start and end of the dictionary and the redo log file that you want to analyze. You can add the redo log files in any order.
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME => '/usr/oracle/data/db1arch_1_210_482701534.dbf', -
OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME => '/usr/oracle/data/db1arch_1_208_482701534.dbf');
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME => '/usr/oracle/data/db1arch_1_207_482701534.dbf');
Query the V$LOGMNR_LOGS view to display the list of redo log files to be analyzed, including their timestamps.
In the output, LogMiner flags a missing redo log file. LogMiner lets you proceed with mining, provided that you do not specify an option that requires the missing redo log file for proper functioning.
SQL> SELECT FILENAME AS name, LOW_TIME, HIGH_TIME FROM V$LOGMNR_LOGS; NAME LOW_TIME HIGH_TIME ------------------------------------- -------------------- -------------------- /usr/data/db1arch_1_207_482701534.dbf 10-jan-2012 12:01:34 10-jan-2012 13:32:46 /usr/data/db1arch_1_208_482701534.dbf 10-jan-2012 13:32:46 10-jan-2012 15:57:03 Missing logfile(s) for thread number 1, 10-jan-2012 15:57:03 10-jan-2012 15:59:53 sequence number(s) 209 to 209 /usr/data/db1arch_1_210_482701534.dbf 10-jan-2012 15:59:53 10-jan-2012 16:07:41
Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY and PRINT_PRETTY_SQL options.
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);
To reduce the number of rows returned by the query, exclude from the query all DML statements done in the SYS or SYSTEM schemas. (This query specifies a timestamp to exclude transactions that were involved in the dictionary extraction.)
The output shows three transactions: two DDL transactions and one DML transaction. The DDL transactions, 1.2.1594 and 1.18.1602, create the table oe.product_tracking and create a trigger on table oe.product_information, respectively. In both transactions, the DML statements done to the system tables (tables owned by SYS) are filtered out because of the query predicate.
The DML transaction, 1.9.1598, updates the oe.product_information table. The update operation in this transaction is fully translated. However, the query output also contains some untranslated reconstructed SQL statements. Most likely, these statements were done on the oe.product_tracking table that was created after the data dictionary was extracted to the redo log files.
(The next example shows how to run LogMiner with the DDL_DICT_TRACKING option so that all SQL statements are fully translated; no binary data is returned.)
SELECT USERNAME AS usr, SQL_REDO FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM') AND
TIMESTAMP > '10-jan-2012 15:59:53';
USR XID SQL_REDO
--- -------- -----------------------------------
SYS 1.2.1594 set transaction read write;
SYS 1.2.1594 create table oe.product_tracking (product_id number not null,
modified_time date,
old_list_price number(8,2),
old_warranty_period interval year(2) to month);
SYS 1.2.1594 commit;
SYS 1.18.1602 set transaction read write;
SYS 1.18.1602 create or replace trigger oe.product_tracking_trigger
before update on oe.product_information
for each row
when (new.list_price <> old.list_price or
new.warranty_period <> old.warranty_period)
declare
begin
insert into oe.product_tracking values
(:old.product_id, sysdate,
:old.list_price, :old.warranty_period);
end;
SYS 1.18.1602 commit;
OE 1.9.1598 update "OE"."PRODUCT_INFORMATION"
set
"WARRANTY_PERIOD" = TO_YMINTERVAL('+08-00'),
"LIST_PRICE" = 100
where
"PRODUCT_ID" = 1729 and
"WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') and
"LIST_PRICE" = 80 and
ROWID = 'AAAHTKAABAAAY9yAAA';
OE 1.9.1598 insert into "UNKNOWN"."OBJ# 33415"
values
"COL 1" = HEXTORAW('c2121e'),
"COL 2" = HEXTORAW('7867010d110804'),
"COL 3" = HEXTORAW('c151'),
"COL 4" = HEXTORAW('800000053c');
OE 1.9.1598 update "OE"."PRODUCT_INFORMATION"
set
"WARRANTY_PERIOD" = TO_YMINTERVAL('+08-00'),
"LIST_PRICE" = 92
where
"PRODUCT_ID" = 2340 and
"WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') and
"LIST_PRICE" = 72 and
ROWID = 'AAAHTKAABAAAY9zAAA';
OE 1.9.1598 insert into "UNKNOWN"."OBJ# 33415"
values
"COL 1" = HEXTORAW('c21829'),
"COL 2" = HEXTORAW('7867010d110808'),
"COL 3" = HEXTORAW('c149'),
"COL 4" = HEXTORAW('800000053c');
OE 1.9.1598 commit;
Display all the DML statements that were executed as part of the CREATE TABLE DDL statement. This includes statements executed by users and internally by Oracle.
If you choose to reapply statements displayed by a query such as the one shown here, then reapply DDL statements only. Do not reapply DML statements that were executed internally by Oracle, or you risk corrupting your database. In the following output, the only statement that you should use in a reapply operation is the CREATE TABLE OE.PRODUCT_TRACKING statement.
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
WHERE XIDUSN = 1 and XIDSLT = 2 and XIDSQN = 1594;
SQL_REDO
--------------------------------------------------------------------------------
set transaction read write;
insert into "SYS"."OBJ$"
values
"OBJ#" = 33415,
"DATAOBJ#" = 33415,
"OWNER#" = 37,
"NAME" = 'PRODUCT_TRACKING',
"NAMESPACE" = 1,
"SUBNAME" IS NULL,
"TYPE#" = 2,
"CTIME" = TO_DATE('13-jan-2012 14:01:03', 'dd-mon-yyyy hh24:mi:ss'),
"MTIME" = TO_DATE('13-jan-2012 14:01:03', 'dd-mon-yyyy hh24:mi:ss'),
"STIME" = TO_DATE('13-jan-2012 14:01:03', 'dd-mon-yyyy hh24:mi:ss'),
"STATUS" = 1,
"REMOTEOWNER" IS NULL,
"LINKNAME" IS NULL,
"FLAGS" = 0,
"OID$" IS NULL,
"SPARE1" = 6,
"SPARE2" = 1,
"SPARE3" IS NULL,
"SPARE4" IS NULL,
"SPARE5" IS NULL,
"SPARE6" IS NULL;
insert into "SYS"."TAB$"
values
"OBJ#" = 33415,
"DATAOBJ#" = 33415,
"TS#" = 0,
"FILE#" = 1,
"BLOCK#" = 121034,
"BOBJ#" IS NULL,
"TAB#" IS NULL,
"COLS" = 5,
"CLUCOLS" IS NULL,
"PCTFREE$" = 10,
"PCTUSED$" = 40,
"INITRANS" = 1,
"MAXTRANS" = 255,
"FLAGS" = 1,
"AUDIT$" = '--------------------------------------',
"ROWCNT" IS NULL,
"BLKCNT" IS NULL,
"EMPCNT" IS NULL,
"AVGSPC" IS NULL,
"CHNCNT" IS NULL,
"AVGRLN" IS NULL,
"AVGSPC_FLB" IS NULL,
"FLBCNT" IS NULL,
"ANALYZETIME" IS NULL,
"SAMPLESIZE" IS NULL,
"DEGREE" IS NULL,
"INSTANCES" IS NULL,
"INTCOLS" = 5,
"KERNELCOLS" = 5,
"PROPERTY" = 536870912,
"TRIGFLAG" = 0,
"SPARE1" = 178,
"SPARE2" IS NULL,
"SPARE3" IS NULL,
"SPARE4" IS NULL,
"SPARE5" IS NULL,
"SPARE6" = TO_DATE('13-jan-2012 14:01:05', 'dd-mon-yyyy hh24:mi:ss'),
insert into "SYS"."COL$"
values
"OBJ#" = 33415,
"COL#" = 1,
"SEGCOL#" = 1,
"SEGCOLLENGTH" = 22,
"OFFSET" = 0,
"NAME" = 'PRODUCT_ID',
"TYPE#" = 2,
"LENGTH" = 22,
"FIXEDSTORAGE" = 0,
"PRECISION#" IS NULL,
"SCALE" IS NULL,
"NULL$" = 1,
"DEFLENGTH" IS NULL,
"SPARE6" IS NULL,
"INTCOL#" = 1,
"PROPERTY" = 0,
"CHARSETID" = 0,
"CHARSETFORM" = 0,
"SPARE1" = 0,
"SPARE2" = 0,
"SPARE3" = 0,
"SPARE4" IS NULL,
"SPARE5" IS NULL,
"DEFAULT$" IS NULL;
insert into "SYS"."COL$"
values
"OBJ#" = 33415,
"COL#" = 2,
"SEGCOL#" = 2,
"SEGCOLLENGTH" = 7,
"OFFSET" = 0,
"NAME" = 'MODIFIED_TIME',
"TYPE#" = 12,
"LENGTH" = 7,
"FIXEDSTORAGE" = 0,
"PRECISION#" IS NULL,
"SCALE" IS NULL,
"NULL$" = 0,
"DEFLENGTH" IS NULL,
"SPARE6" IS NULL,
"INTCOL#" = 2,
"PROPERTY" = 0,
"CHARSETID" = 0,
"CHARSETFORM" = 0,
"SPARE1" = 0,
"SPARE2" = 0,
"SPARE3" = 0,
"SPARE4" IS NULL,
"SPARE5" IS NULL,
"DEFAULT$" IS NULL;
insert into "SYS"."COL$"
values
"OBJ#" = 33415,
"COL#" = 3,
"SEGCOL#" = 3,
"SEGCOLLENGTH" = 22,
"OFFSET" = 0,
"NAME" = 'OLD_LIST_PRICE',
"TYPE#" = 2,
"LENGTH" = 22,
"FIXEDSTORAGE" = 0,
"PRECISION#" = 8,
"SCALE" = 2,
"NULL$" = 0,
"DEFLENGTH" IS NULL,
"SPARE6" IS NULL,
"INTCOL#" = 3,
"PROPERTY" = 0,
"CHARSETID" = 0,
"CHARSETFORM" = 0,
"SPARE1" = 0,
"SPARE2" = 0,
"SPARE3" = 0,
"SPARE4" IS NULL,
"SPARE5" IS NULL,
"DEFAULT$" IS NULL;
insert into "SYS"."COL$"
values
"OBJ#" = 33415,
"COL#" = 4,
"SEGCOL#" = 4,
"SEGCOLLENGTH" = 5,
"OFFSET" = 0,
"NAME" = 'OLD_WARRANTY_PERIOD',
"TYPE#" = 182,
"LENGTH" = 5,
"FIXEDSTORAGE" = 0,
"PRECISION#" = 2,
"SCALE" = 0,
"NULL$" = 0,
"DEFLENGTH" IS NULL,
"SPARE6" IS NULL,
"INTCOL#" = 4,
"PROPERTY" = 0,
"CHARSETID" = 0,
"CHARSETFORM" = 0,
"SPARE1" = 0,
"SPARE2" = 2,
"SPARE3" = 0,
"SPARE4" IS NULL,
"SPARE5" IS NULL,
"DEFAULT$" IS NULL;
insert into "SYS"."CCOL$"
values
"OBJ#" = 33415,
"CON#" = 2090,
"COL#" = 1,
"POS#" IS NULL,
"INTCOL#" = 1,
"SPARE1" = 0,
"SPARE2" IS NULL,
"SPARE3" IS NULL,
"SPARE4" IS NULL,
"SPARE5" IS NULL,
"SPARE6" IS NULL;
insert into "SYS"."CDEF$"
values
"OBJ#" = 33415,
"CON#" = 2090,
"COLS" = 1,
"TYPE#" = 7,
"ROBJ#" IS NULL,
"RCON#" IS NULL,
"RRULES" IS NULL,
"MATCH#" IS NULL,
"REFACT" IS NULL,
"ENABLED" = 1,
"CONDLENGTH" = 24,
"SPARE6" IS NULL,
"INTCOLS" = 1,
"MTIME" = TO_DATE('13-jan-2012 14:01:08', 'dd-mon-yyyy hh24:mi:ss'),
"DEFER" = 12,
"SPARE1" = 6,
"SPARE2" IS NULL,
"SPARE3" IS NULL,
"SPARE4" IS NULL,
"SPARE5" IS NULL,
"CONDITION" = '"PRODUCT_ID" IS NOT NULL';
create table oe.product_tracking (product_id number not null,
modified_time date,
old_product_description varchar2(2000),
old_list_price number(8,2),
old_warranty_period interval year(2) to month);
update "SYS"."SEG$"
set
"TYPE#" = 5,
"BLOCKS" = 5,
"EXTENTS" = 1,
"INIEXTS" = 5,
"MINEXTS" = 1,
"MAXEXTS" = 121,
"EXTSIZE" = 5,
"EXTPCT" = 50,
"USER#" = 37,
"LISTS" = 0,
"GROUPS" = 0,
"CACHEHINT" = 0,
"HWMINCR" = 33415,
"SPARE1" = 1024
where
"TS#" = 0 and
"FILE#" = 1 and
"BLOCK#" = 121034 and
"TYPE#" = 3 and
"BLOCKS" = 5 and
"EXTENTS" = 1 and
"INIEXTS" = 5 and
"MINEXTS" = 1 and
"MAXEXTS" = 121 and
"EXTSIZE" = 5 and
"EXTPCT" = 50 and
"USER#" = 37 and
"LISTS" = 0 and
"GROUPS" = 0 and
"BITMAPRANGES" = 0 and
"CACHEHINT" = 0 and
"SCANHINT" = 0 and
"HWMINCR" = 33415 and
"SPARE1" = 1024 and
"SPARE2" IS NULL and
ROWID = 'AAAAAIAABAAAdMOAAB';
insert into "SYS"."CON$"
values
"OWNER#" = 37,
"NAME" = 'SYS_C002090',
"CON#" = 2090,
"SPARE1" IS NULL,
"SPARE2" IS NULL,
"SPARE3" IS NULL,
"SPARE4" IS NULL,
"SPARE5" IS NULL,
"SPARE6" IS NULL;
commit;
EXECUTE DBMS_LOGMNR.END_LOGMNR();