前言：Logminer有时候能完成Flashback由于undo retention等问题不能完成的任务，比如，查询比较长时间（超过undo rentention）以前某张表被谁改动过。本篇分三个主题，简单测试总结了一下Logminer在不同情景下的使用。
Steps in a Typical LogMiner Session:
1. Enable Supplemental Logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; //Never forget this step!!!
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
2. Extract a LogMiner Dictionary (unless you plan to use the online catalog)
3. Specify Redo Log Files for Analysis
4. Start LogMiner
5. Query V$LOGMNR_CONTENTS
6. End the LogMiner Session
//********************************************************************************************
//* Examples of Mining by Explicitly Specifying the Redo Log Files of Interest
//********************************************************************************************
//==============================================================
//= Example 1: Finding All Modifications in the Last Archived Redo Log File
//==============================================================
Step 1 Determine which redo log file was most recently archived.
SQL> select name from v$archived_log where FIRST_TIME = (select max(FIRST_TIME) from v$archived_log);
NAME
------------------------------------------------------------------------------------------------------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_76_41BVKFPB_.ARC
Step 2 Specify the list of redo log files to be analyzed.
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => 'D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_76_41BVKFPB_.ARC', -
OPTIONS => DBMS_LOGMNR.NEW);
Step 3 Start LogMiner.
EXECUTE DBMS_LOGMNR.START_LOGMNR( PTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); //DICT_FROM_ONLINE_CATALOG指定使用online catalog作为LogMiner Dictionary
Step 4 Query the V$LOGMNR_CONTENTS view.
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('JOHN');
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('JOHN');
USR XID SQL_REDO SQL_UNDO
--------------- --------------- -------------------------------------------------- --------------------------------------------------
JOHN 9.5.381 set transaction read write;
JOHN 9.5.381 delete from "JOHN"."TESTTAB" where "ID" = '4' and insert into "JOHN"."TESTTAB"("ID","NAME","ADDR") v
"NAME" = 'Bin' and "ADDR" = 'Hangzhou' and ROWID = alues ('4','Bin','Hangzhou');
'AAADCCAAEAAAAA3AAD';
JOHN 9.5.381
JOHN 9.5.381
JOHN 9.5.381 commit;
JOHN 8.9.382 set transaction read write;
JOHN 8.9.382 insert into "JOHN"."NAMELIST"("NAME") values ('Log delete from "JOHN"."NAMELIST" where "NAME" = 'LogL
Log'); og' and ROWID = 'AAAC3sAAEAAAAAQAAA';
USR XID SQL_REDO SQL_UNDO
--------------- --------------- -------------------------------------------------- --------------------------------------------------
JOHN 8.9.382 commit;
8 rows selected.
Step 5 End the LogMiner session.
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();
//==============================================================
//= Example 2: Grouping DML Statements into Committed Transactions
//==============================================================
Step 1 Determine which redo log file was most recently archived by the database.
SQL> select name from v$archived_log where FIRST_TIME = (select max(FIRST_TIME) from v$archived_log);
NAME
------------------------------------------------------------------------------------------------------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_76_41BVKFPB_.ARC
Step 2 Specify the list of redo log files to be analyzed.
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => 'D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_76_41BVKFPB_.ARC', -
OPTIONS => DBMS_LOGMNR.NEW);
Step 3 Start LogMiner.
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY); //Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY option.
Step 4 Query the V$LOGMNR_CONTENTS view.
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('JOHN');
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('JOHN');
USR XID SQL_REDO SQL_UNDO
--------------- --------------- -------------------------------------------------- --------------------------------------------------
JOHN 9.5.381 set transaction read write;
JOHN 9.5.381 delete from "JOHN"."TESTTAB" where "ID" = '4' and insert into "JOHN"."TESTTAB"("ID","NAME","ADDR") v
"NAME" = 'Bin' and "ADDR" = 'Hangzhou' and ROWID = alues ('4','Bin','Hangzhou');
'AAADCCAAEAAAAA3AAD';
JOHN 9.5.381 commit;
JOHN 8.9.382 set transaction read write;
JOHN 8.9.382 insert into "JOHN"."NAMELIST"("NAME") values ('Log delete from "JOHN"."NAMELIST" where "NAME" = 'LogL
Log'); og' and ROWID = 'AAAC3sAAEAAAAAQAAA';
JOHN 8.9.382 commit;
6 rows selected.
Step 5 End the LogMiner session.
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();
//==============================================================
//= Example 3: Formatting the Reconstructed SQL
//==============================================================
Step 1 and Step 2 are same as preceding.
Step 3 Start LogMiner.
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);
Step 4 Query the V$LOGMNR_CONTENTS view for SQL_REDO statements.
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO FROM V$LOGMNR_CONTENTS where username='JOHN';
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO FROM V$LOGMNR_CONTENTS where username='JOHN';
USR XID SQL_REDO
------------------------------ --------------- --------------------------------------------------
JOHN 9.5.381 set transaction read write;
JOHN 9.5.381 delete from "JOHN"."TESTTAB"
where
"ID" = 4 and
"NAME" = 'Bin' and
"ADDR" = 'Hangzhou' and
ROWID = 'AAADCCAAEAAAAA3AAD';
JOHN 9.5.381 commit;
JOHN 8.9.382 set transaction read write;
JOHN 8.9.382 insert into "JOHN"."NAMELIST"
USR XID SQL_REDO
------------------------------ --------------- --------------------------------------------------
values
"NAME" = 'LogLog';
JOHN 8.9.382 commit;
6 rows selected.
Step 5 Query the V$LOGMNR_CONTENTS view for reconstructed SQL_UNDO statements.
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_UNDO FROM V$LOGMNR_CONTENTS where username='JOHN';
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_UNDO FROM V$LOGMNR_CONTENTS where username='JOHN';
USR XID SQL_UNDO
------------------------------ --------------- --------------------------------------------------
JOHN 9.5.381
JOHN 9.5.381 insert into "JOHN"."TESTTAB"
values
"ID" = 4,
"NAME" = 'Bin',
"ADDR" = 'Hangzhou';
JOHN 9.5.381
JOHN 8.9.382
JOHN 8.9.382 delete from "JOHN"."NAMELIST"
where
USR XID SQL_UNDO
------------------------------ --------------- --------------------------------------------------
"NAME" = 'LogLog' and
ROWID = 'AAAC3sAAEAAAAAQAAA';
JOHN 8.9.382
6 rows selected.
Step 6 End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();
//==============================================================
//= Example 4: Using the LogMiner Dictionary in the Redo Log Files
//==============================================================
//Using the dictionary contained in the redo log files enables you to mine redo log files in a different database.
//=============== EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
Step 1 Determine which redo log file was most recently archived by the database.
SQL> SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME SEQUENCE#
------------------------------------------------------------------------------------------------------------------------ ----------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC 80
Step 2 Find the redo log files containing the dictionary.
2.1 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# <= 80);
SQL> SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end
2 FROM V$ARCHIVED_LOG
3 WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG
4 WHERE DICTIONARY_END = 'YES' and SEQUENCE# <= 80);
NAME SEQUENCE# D_B D_E
------------------------------------------------------------------------------------------------------------------------ ---------- --- ---
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC 78 YES YES
2.2 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:
//Because the dictionary may be contained in more than one redo log file, you need to
determine which redo log files contain the start and end of the data dictionary(2.1 and 2.2).
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# <= 78);
SQL> SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end
2 FROM V$ARCHIVED_LOG
3 WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG
4 WHERE DICTIONARY_BEGIN = 'YES' and SEQUENCE# <= 78);
NAME SEQUENCE# D_B D_E
------------------------------------------------------------------------------------------------------------------------ ---------- --- ---
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC 78 YES YES
2.3 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 => 'D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC', -
OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME => 'D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC');
2.4 Query the V$LOGMNR_LOGS view to display the list of redo log files to be analyzed, including their timestamps.
SQL> SELECT FILENAME AS name, LOW_TIME, HIGH_TIME FROM V$LOGMNR_LOGS;
NAME LOW_TIME HIGH_TIME
---------------------------------------------------------------------------------------------------- ------------------- -------------------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC 2008-04-28 15:18:36 2008-04-28 15:18:39
Missing log file(s) for thread 1, sequence(s) 79 to 79 2008-04-28 15:18:39 2008-04-28 15:19:25
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC 2008-04-28 15:19:25 2008-04-28 15:22:40
Step 3 Start LogMiner.
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + - //DICT_FROM_REDO_LOGS指定用redo logs作为LogMiner Dictionary
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);
Step 4 Query the V$LOGMNR_CONTENTS view.
SELECT USERNAME AS usr, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM') AND
TIMESTAMP > '2008-04-28 15:29:53';
....
Step 5 Issue additional queries, if desired.
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE XIDUSN = 1 and XIDSLT = 2 and XIDSQN = 1594;
Step 6 End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();
//==============================================================
//= Example 5: Tracking DDL Statements in the Internal Dictionary
//==============================================================
Step 1 Determine which redo log file was most recently archived by the database.
SQL> SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME SEQUENCE#
---------------------------------------------------------------------------------------------------- ----------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC 80
Step 2 Find the dictionary in the redo log files.
SQL> SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end
2 FROM V$ARCHIVED_LOG
3 WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG
4 WHERE DICTIONARY_END = 'YES' and SEQUENCE# <= 80);
NAME SEQUENCE# D_B D_E
---------------------------------------------------------------------------------------------------- ---------- --- ---
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC 78 YES YES
Step 3 Make sure you have a complete list of redo log files.
SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE SEQUENCE# >= 78 AND SEQUENCE# <= 80
3 ORDER BY SEQUENCE# ASC;
NAME
----------------------------------------------------------------------------------------------------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_79_41BYMXCN_.ARC
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC
Step 4 Specify the list of the redo log files of interest.
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME => 'D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC', -
PTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME => 'D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_79_41BYMXCN_.ARC');
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME => 'D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC');
Step 5 Start LogMiner.
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + -
DBMS_LOGMNR.DDL_DICT_TRACKING + - //DDL_DICT_TRACKING here
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);
Step 6 Query the V$LOGMNR_CONTENTS view.
SQL> SELECT USERNAME AS usr,(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) as XID, SQL_REDO FROM
2 V$LOGMNR_CONTENTS
3 WHERE USERNAME = 'JOHN' AND
4 TIMESTAMP > '2008-04-28 15:09:53';
USR XID SQL_REDO
------------------------------ --------------- --------------------------------------------------
JOHN 9.17.383 set transaction read write;
JOHN 9.17.383 delete from "JOHN"."NAMELIST"
where
"NAME" = 'IFULEU' and
ROWID = 'AAAC3sAAEAAAAAMAAK';
JOHN 9.17.383 commit;
JOHN 3.32.386 set transaction read write;
JOHN 3.32.386 insert into "JOHN"."NAMELIST"
values
"NAME" = 'DictInLog';
USR XID SQL_REDO
------------------------------ --------------- --------------------------------------------------
JOHN 3.32.386 commit;
JOHN 10.8.271 set transaction read write;
JOHN 10.8.271 insert into "JOHN"."TESTTAB"
values
"ID" = 4,
"NAME" = 'Bin',
"ADDR" = 'Hangzhou';
JOHN 10.8.271 commit;
JOHN 8.13.384 set transaction read write;
USR XID SQL_REDO
------------------------------ --------------- --------------------------------------------------
JOHN 8.13.384 insert into "JOHN"."TESTTAB"
values
"ID" = 6,
"NAME" = 'Boss',
"ADDR" = 'Shanghai';
JOHN 8.13.384 commit;
12 rows selected.
Step 7 End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();
//==============================================================
//= Example 6: Filtering Output by Time Range
//==============================================================
Step 1 Create a list of redo log files to mine.
--
-- my_add_logfiles
-- Add all archived logs generated after a specified start_time.
--
CREATE OR REPLACE PROCEDURE my_add_logfiles (in_start_time IN DATE) AS
CURSOR c_log IS
SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME >= in_start_time;
count pls_integer := 0;
my_option pls_integer := DBMS_LOGMNR.NEW;
BEGIN
FOR c_log_rec IN c_log
LOOP
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => c_log_rec.name,
OPTIONS => my_option);
my_option := DBMS_LOGMNR.ADDFILE;
DBMS_OUTPUT.PUT_LINE('Added logfile ' || c_log_rec.name);
END LOOP;
END;
/
EXECUTE my_add_logfiles(in_start_time => '2008-04-28 14:49:53');
Step 2 Query the V$LOGMNR_LOGS to see the list of redo log files.
SQL> SELECT FILENAME name, LOW_TIME start_time, FILESIZE bytes FROM V$LOGMNR_LOGS;
NAME START_TIME BYTES
---------------------------------------------------------------------------------------------------- ------------------- ----------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC 2008-04-28 15:18:36 5139456
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_79_41BYMXCN_.ARC 2008-04-28 15:18:39 12288
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC 2008-04-28 15:19:25 36864
Step 3 Adjust the list of redo log files.
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME => 'D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC', -
PTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME => 'D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_79_41BYMXCN_.ARC');
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME => 'D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC');
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
STARTTIME => '2008-04-28 15:18:53', - // 注意，此处时间必须大于Step 2中最小的时间！！！
ENDTIME => '2008-04-28 15:59:53', -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);
Step 4 Query the V$LOGMNR_CONTENTS view.
SQL> SELECT TIMESTAMP, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'JOHN';
TIMESTAMP XID SQL_REDO
------------------- --------------- --------------------------------------------------
2008-04-28 15:20:15 9.17.383 delete from "JOHN"."NAMELIST"
where
"NAME" = 'IFULEU' and
ROWID = 'AAAC3sAAEAAAAAMAAK';
2008-04-28 15:20:46 3.32.386 insert into "JOHN"."NAMELIST"
values
"NAME" = 'DictInLog';
2008-04-28 15:21:19 10.8.271 insert into "JOHN"."TESTTAB"
values
TIMESTAMP XID SQL_REDO
------------------- --------------- --------------------------------------------------
"ID" = 4,
"NAME" = 'Bin',
"ADDR" = 'Hangzhou';
2008-04-28 15:21:37 8.13.384 insert into "JOHN"."TESTTAB"
values
"ID" = 6,
"NAME" = 'Boss',
"ADDR" = 'Shanghai';
Step 5 End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();
//********************************************************************************************
//* Examples of Mining Without Specifying the List of Redo Log Files Explicitly
//********************************************************************************************
//==============================================================
//= Example 1: Mining Redo Log Files in a Given Time Range
//==============================================================
//This example assumes that you want to use the data dictionary extracted to the redo log files.
Step 1 Determine the timestamp of the redo log file that contains the start of the data dictionary.
SQL> SELECT NAME, FIRST_TIME FROM V$ARCHIVED_LOG
2 WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
3 WHERE DICTIONARY_BEGIN = 'YES');
NAME FIRST_TIME
---------------------------------------------------------------------------------------------------- -------------------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC 2008-04-28 15:18:36
Step 2 Display all the redo log files that have been generated so far.
SQL> SELECT FILENAME name FROM V$LOGMNR_LOGS WHERE LOW_TIME > '2008-04-28 15:51:34';
NAME
----------------------------------------------------------------------------------------------------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_79_41BYMXCN_.ARC
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC
Step 3 Start LogMiner.
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
STARTTIME => '2008-04-28 15:18:53', -
ENDTIME => SYSDATE, -
OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL + -
DBMS_LOGMNR.CONTINUOUS_MINE);
Step 4 Query the V$LOGMNR_LOGS view. //(Compare the output in this step to the output in Step 2.)
SQL> SELECT FILENAME name FROM V$LOGMNR_LOGS;
NAME
----------------------------------------------------------------------------------------------------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_79_41BYMXCN_.ARC
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_81_41C0Z05F_.ARC
Step 5 Query the V$LOGMNR_CONTENTS view.
SQL> SELECT USERNAME AS usr,(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) as XID,
2 SQL_REDO FROM V$LOGMNR_CONTENTS
3 WHERE SEG_OWNER = 'JOHN' AND
4 TIMESTAMP > '2008-04-28 15:38:53';
no rows selected //这一个查询跑了我20几分钟，谁能告诉我为什么？？？？
Step 6 End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();
//==============================================================
//= Example 2: Mining the Redo Log Files in a Given SCN Range
//==============================================================
Step 1 Determine the SCN of the last checkpoint taken.
SQL> SELECT CHECKPOINT_CHANGE#, CURRENT_SCN FROM V$DATABASE;
CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
294517000 294526772
Step 2 Start LogMiner and specify the CONTINUOUS_MINE option.
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
STARTSCN => 294517000, -
ENDSCN => 294526772, -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + - //DICT_FROM_ONLINE_CATALOG指定使用online catalog作为LogMiner Dictionary
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL + -
DBMS_LOGMNR.CONTINUOUS_MINE);
Step 3 Display the list of archived redo log files added by LogMiner.
SQL> SELECT FILENAME name, LOW_SCN, NEXT_SCN FROM V$LOGMNR_LOGS;
NAME LOW_SCN NEXT_SCN
---------------------------------------------------------------------------------------------------- ---------- ----------
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_77_41BYLF07_.ARC 294463303 294465496
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_78_41BYLHHC_.ARC 294465496 294466268
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_79_41BYMXCN_.ARC 294466268 294466294
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_80_41BYT0G6_.ARC 294466294 294466384
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_81_41C0Z05F_.ARC 294466384 294477796
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_82_41C2JHJP_.ARC 294477796 294488551
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_83_41C34GGB_.ARC 294488551 294498095
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_84_41C3FG2M_.ARC 294498095 294505957
D:\OPT\FLASH_RECOVERY_AREA\ORA10GBR\ARCHIVELOG\2008_04_28\O1_MF_1_85_41C4C9CQ_.ARC 294505957 294517000
9 rows selected.
SELECT NAME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);
Step 4 Query the V$LOGMNR_CONTENTS view for changes made to the user tables.
SELECT SCN, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) as XID, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'JOHN';
Step 5 End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();
[To be continued...]
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9765498/viewspace-259948/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9765498/viewspace-259948/
352
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
