oracle10g日志详解,Oracle 10g学习笔记（十二） oracle 10g 深入分析日志文件

最新推荐文章于 2021-06-03 10:07:50 发布

weixin_39755712

最新推荐文章于 2021-06-03 10:07:50 发布

阅读量368

点赞数

文章标签： oracle10g日志详解

本文详细介绍了如何使用Oracle的LOGMINER工具分析数据库的归档日志，包括创建DBMS_LOGMNR包和数据字典文件，设置日志分析，以及查询和解析DML操作。两种方法分别演示了利用在线目录和自定义字典文件进行日志分析的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前面是安装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 过程已成功完成。