10g LOGMINER使用方法

10g LOGMINER使用方法

===========================================================

作者: chenzhen232(http://chenzhen232.itpub.net)
发表于:2007.11.02 10:49
分类: 工具应用
出处：http://chenzhen232.itpub.net/post/36312/410647
---------------------------------------------------------------

转载

LOGMINER使用方法(OS WIN2003 DATABASE 10GR2)
------------------------------------------------------------------

前面是安装LOGMINER工具在这里只是简单介绍一下
以SYS用户执行下面两个脚本
A：$ORACLE_HOME/rdbms/admin/dbmslm.sql ，用来创建DBMS_LOGMNR包
SQL> @dbmslm.sql
程序包已创建。
授权成功。
B：$ORACLE_HOME/rdbms/admin/dbmslmd.sql. 用来创建数据字典文件。
SQL> @dbmslmd.sql
过程已创建。
没有错误。
授权成功。
PL/SQL 过程已成功完成。

程序包已创建。
-------------------------------------------------------------------
第一种方法使用联机目录分析归档日志
1、打开数据库的追加日志(这个一定要注意了，否则分析出来的都是ddl）

---10g特性，不然无法查出dml操作的数据
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
如果结果为YES就不需要追加日志反之，
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA；

2、切换日志，然后执行事务删除.
CONN / AS SYSDBA
ALTER SYSTEM SWITCH LOGFILE；
CONNECT TEST/TEST
DELETE FROM TEST1 WHERE ID=1；
COMMIT；
CONN / AS SYSDBA
ALTER SYSTEM SWITCH LOGFILE；
SELECT NAME FROM V$ARCHIVED_LOG;

3、将新生成的日志文件添加到LOGMINER列表中。
EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-
'C:oracleproduct10.2.0flash_recovery_areaTESTARCHIVELOG2007_10_31O1_MF_1_72_3LHVWNOZ_.ARC',-
DBMS_LOGMNR.NEW);

4、为LOGMINER指定将要使用的联机目录。如果源数据库处于打开或者是可用状态，那么它也可用。
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
PTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

5、在V$LOGMNR_CONTENTS中查询有关删除事务的信息。
SELECT USERNAME,SQL_REDO,SQL_UNDO FROM
V$LOGMNR_CONTENTS WHERE USERNAME='TEST'
AND PERATION='DELETE';

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

TEST
delete from "SYS"."CON$" where "OWNER#" = '74' and "NAME" = 'SYS_C005801' and "C

ON#" = '5801' and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and

"SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAAcA

ABAAAN4tAAN';
insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SPARE2","SPARE3","SPAR

E4","SPARE5","SPARE6") values ('74','SYS_C005801','5801',NULL,NULL,NULL,NULL,NUL

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

L,NULL);

TEST
delete from "SYS"."OBJ$" where "OBJ#" = '54375' and "DATAOBJ#" = '54375' and "OW

NER#" = '74' and "NAME" = 'SYS_C005801' and "NAMESPACE" = '4' and "SUBNAME" IS N

ULL and "TYPE#" = '1' and "CTIME" = TO_DATE('21-9月 -07', 'DD-MON-RR') and "MTIM

E" = TO_DATE('21-9月 -07', 'DD-MON-RR') and "STIME" = TO_DATE('21-9月 -07', 'DD-

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

MON-RR') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and

"FLAGS" = '4' and "OID$" IS NULL and "SPARE1" = '0' and "SPARE2" = '65535' and
"SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL
and ROWID = 'AAAAASAABAAAMTvABJ';
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME"

,"TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$

","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('54375','54375'

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

,'74','SYS_C005801','4',NULL,'1',TO_DATE('21-9月 -07', 'DD-MON-RR'),TO_DATE('21-

9月 -07', 'DD-MON-RR'),TO_DATE('21-9月 -07', 'DD-MON-RR'),'1',NULL,NULL,'4',NULL

,'0','65535',NULL,NULL,NULL,NULL);

TEST
delete from "SYS"."OBJ$" where "OBJ#" = '54355' and "DATAOBJ#" = '54355' and "OW

NER#" = '74' and "NAME" = 'DEMO' and "NAMESPACE" = '1' and "SUBNAME" IS NULL and

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

"TYPE#" = '2' and "CTIME" = TO_DATE('21-9月 -07', 'DD-MON-RR') and "MTIME" = TO

_DATE('21-9月 -07', 'DD-MON-RR') and "STIME" = TO_DATE('21-9月 -07', 'DD-MON-RR'

) and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS

" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" IS

NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID =

'AAAAASAABAAAMTvAA0';
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME"

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

,"TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$

","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('54355','54355'

,'74','DEMO','1',NULL,'2',TO_DATE('21-9月 -07', 'DD-MON-RR'),TO_DATE('21-9月 -07

', 'DD-MON-RR'),TO_DATE('21-9月 -07', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1

',NULL,NULL,NULL,NULL);

TEST

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

delete from "TEST"."DEPT_DEMO" where "DEPT_ID" = '1' and "DEPT_NAME" = '技术部'
and ROWID = 'AAAN1uAAEAAABjkAAA';
insert into "TEST"."DEPT_DEMO"("DEPT_ID","DEPT_NAME") values ('1','技术部');

TEST
delete from "TEST"."DEPT_DEMO" where "DEPT_ID" = '2' and "DEPT_NAME" = '财务部'
and ROWID = 'AAAN1uAAEAAABjkAAB';

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

insert into "TEST"."DEPT_DEMO"("DEPT_ID","DEPT_NAME") values ('2','财务部');

TEST
delete from "TEST"."DEPT_DEMO" where "DEPT_ID" = '3' and "DEPT_NAME" = '部市部'
and ROWID = 'AAAN1uAAEAAABjkAAC';
insert into "TEST"."DEPT_DEMO"("DEPT_ID","DEPT_NAME") values ('3','部市部');

已选择6行。

6、结束LOGMNR
execute dbms_logmnr.end_logmnr;

----------------------------------------------------------------------
第二种方法使用LOGMINER字典
1、首先修改参数文件
添加UTL_FILE_DIR=c:log_miner
2、以sys用户运行脚本，创建数据字典文件

SQL> execute dbms_logmnr_d.build('testtrace.ora', 'c:log_miner',dbms_logmnr_d.store_in_flat_file);

PL/SQL 过程已成功完成。
3、建立日志分析表,使用dbms_logmnr.add_logfile()

SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename=>'C:oracleproduct10.2.0flash_recovery_areaTESTARCHIVELOG2007_10_31O1_MF_1_72_3LHVWNOZ_.ARC');

PL/SQL 过程已成功完成。
4、添加用于分析的日志文件

SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename =>'C:oracleproduct10.2.0flash_recovery_areaTESTARCHIVELOG2007_10_31O1_MF_1_73_3LHZO54W_.ARC');

execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename =>'C:oracleproduct10.2.0flash_recovery_areaTESTARCHIVELOG2007_10_31O1_MF_1_71_3LHVOK7B_.ARC');

PL/SQL 过程已成功完成。

5、启动LogMiner进行分析。

SQL> execute dbms_logmnr.start_logmnr(dictfilename =>'c:log_minertesttrace.ora',starttime =>to_date-('20071031 09:00:00','yyyymmdd hh24:mi:ss'),endtime =>to_date('20071031 12:00:00','yyyymmdd-hh24:mi:ss'));
6、查看日志分析的结果,通过查询v$logmnr_contents可以查询到

SELECT USERNAME,SQL_REDO,SQL_UNDO FROM
V$LOGMNR_CONTENTS WHERE USERNAME='TEST'
AND PERATION='DELETE';

输出结果同上一个实验.在这里就不在贴出结果。

7、结束LogMiner的分析。

execute dbms_logmnr.end_logmnr;

PL/SQL 过程已成功完成。

8、可以把这个文件从日志分析表中移除，从而不进行分析。

SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.removefile,logfilename =>'d:oracleora92rdbmsARC00038.001');

PL/SQL 过程已成功完成。

综合比较两种方式，我强烈建议使用第一种方法，因为 最好将LOGMINER视为不需要数据库中断就可以进行数据析取和数据重建操作的配套部件。