创建测试数据如下:
SQL> conn test/test;
已连接。
SQL> select count(*) from test;
COUNT(*)
----------
2783
SQL> drop table test;
表已删除。
SQL> select * from tab;
TNAMETABTYPECLUSTERID
-------------------------------------------------- -------------- ----------------
BIN$9+j2+vwhTf6Qd7OPMl0sXQ==$0TABLE
BIN$b68nCGC8SjuNbhX+BsIjeQ==$0TABLE
BIN$dabdRCoERBm2euSC3bL1ng==$0TABLE
BIN$khbm6ux+RCyW8wU2wePXCg==$0TABLE
BIN$nLEpgLEmRM2bQ6gBGXSDsQ==$0TABLE
具体挖掘数据步骤如下:
1、Set the Init.ora parameter UTL_FILE_DIR, with the name of dictionary where you want to create a dictionary file.
Alter system set utl_file_dir=’ E:\gwrs\logmnr’ scope=both;
SQL> show parameter utl_file_dir
NAMETYPEVALUE
------------------------ ------------------------------------------------
utl_file_dirstringE:\gwrs\logmnr
2.Now generate the dictionary file using following command:
EXECUTE DBMS_LOGMNR_D.BUILD ('DICTIONARY1102.ORA', ‘E:\gwrs\logmnr’, DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
3. Now prepare the list of the archive files which were generated when the unwanted changes done.
SQL> conn / as sysdba
已连接。
SQL> execute dbms_logmnr.add_logfile(logfilename =>'D:\APP\ADM_XINGDO2\ORADATA\O
RCL\REDO01.LOG',options=>DBMS_LOGMNR.NEW);
PL/SQL 过程已成功完成。
SQL> execute dbms_logmnr.add_logfile(logfilename =>'D:\APP\ADM_XINGDO2\ORADATA\O
RCL\REDO02.LOG',options=>DBMS_LOGMNR.ADDFILE);
PL/SQL 过程已成功完成。
SQL> execute dbms_logmnr.add_logfile(logfilename =>'D:\APP\ADM_XINGDO2\ORADATA\O
RCL\REDO03.LOG',options=>DBMS_LOGMNR.ADDFILE);
PL/SQL 过程已成功完成。
SQL> execute dbms_logmnr.add_logfile(logfilename =>'D:\APP\ADM_XINGDO2\ORADATA\O
RCL\REDO04.LOG',options=>DBMS_LOGMNR.ADDFILE);
PL/SQL 过程已成功完成。
4. Now you can start the logMiner as shown below:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>'E:\gwrs\logmnr\dictionary11
02.ORA');
PL/SQL 过程已成功完成。
5.Now you can query v$LOGMNR_CONTENTS details using following queries:
SQL> col username for a14
SQL> col os_username for a14
SQL> col machine_name for a14
SQL> col sql_redo for a40
SQL> select username,os_username,machine_name,sql_redo from v$logmnr_contents wh
ere table_name='TEST';
USERNAMEOS_USERNAMEMACHINE_NAME
-------------- -------------- --------------
SQL_REDO
----------------------------------------
UNKNOWNUNKNOWNUNKNOWN
ALTER TABLE "TEST"."TEST" RENAME CONSTRA
INT "SYS_C0012119" TO "BIN$bv/vfe5nT02Ag
KNrA3Vcvw==$0" ;
UNKNOWNUNKNOWNUNKNOWN
ALTER TABLE "TEST"."TEST" RENAME CONSTRA
INT "SYS_C0012120" TO "BIN$4c3qfrYSSbqsz
vM7+h+tjQ==$0" ;
USERNAMEOS_USERNAMEMACHINE_NAME
-------------- -------------- --------------
SQL_REDO
----------------------------------------
UNKNOWNUNKNOWNUNKNOWN
ALTER TABLE "TEST"."TEST" RENAME TO "BIN
$b68nCGC8SjuNbhX+BsIjeQ==$0" ;
UNKNOWNUNKNOWNUNKNOWN
drop table test AS "BIN$b68nCGC8SjuNbhX+
BsIjeQ==$0" ;
The column of username,os_username,machine_name is unknown ,because we doesn’t set the parameter as following:
Enable Supplemental Logging by issueing command:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
You can use LogMiner in mount mode as well as an open database mode.
6. You can End LogMiner session using:
SQL> EXECUTE DBMS_LOGMNR.end_LOGMNR;
--转自