How do you know in versions earlier than 9i that multiple statements belong to the same transaction? You can check USERNAME (or session_info) and XIDUSN (rollback segment number) and you can see first and last for transaction. XIDSQN identifies the SCN. XIDSLOT can also be used to order the transaction components: operation START, sql_redo set transaction read write, and operation COMMIT, sql_redo commit.
How do you know in versions earlier than 9i that a table was dropped? DROP TABLE will generate DELETE operations on COL$, OBJ$ and TAB$.
select seg_name, operation, scn, count(*) from v$logmnr_contents
where operation != 'INTERNAL'
group by seg_name, operation, scn
order by scn;
SEG_NAME OPERATION SCN COUNT(*)
--------------- -------------------------------- ---------- ----------
COL$ DELETE 5012065 3
OBJ$ DELETE 5012065 1
TAB$ DELETE 5012065 1
START 5012065 1
SEG$ UPDATE 5012065 1
How do you know in versions earlier than 9i to repopulate a table that had chained rows? DML on chained rows are included in "v$logmnr_contents.sql_redo" and "v$logmnr_contents.sql_undo". The SQL redo/undo columns are NULL for INSERT and UPDATE and contain 'Unsupported' for DELETE. Other columns (including data_blk#, data_obj#, row_id) can be used to identify chained rows, but it we cannot determine the SQL redo/undo statement. So, note that v$logmnr_contents.sql_redo CANNOT be used to completely repopulate a table that ever contained chained rows.
Log Miner Procedures Summary
Many people make the dictionary file creation part of the daily backup procedures
Do not run the log analysis on the production database, as it takes a lot of I/O and PGA
Accessing V$LOGMNR_CONTENTS is very slow, a full scan can take 10-20 hours, physically reading the redo log files, using a lot of PGA not SGA. The analyzing database can be only mounted. V$LOGMNR_CONTENTS can contain Millions of rows on a busy production system
Some people recommend using a standby in mount or read-only state to analyze the redos from the primary. This will read directly the redo files, so it will be very slow, as described above.
I would rather recommend using an opened database, even on a workstation, and doing the copy of the view V$LOGMNR_CONTENTS to a regular table. If you need, you can build indexes on it. This will also avoid a number of reported problems and crashes caused by running directly against V$LOGMNR_CONTENTS. V$LOGMNR_CONTENTS.
精品推荐