资料文本oracle11g r2log讲解

1、誉天教育官网：oracle 日志分析工具 LogMiner 使用1.设置日期格式alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile; select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual ;2.添加补充日志Redo log files are generally used for instance recovery and media recovery. The data needed for such opera

2、tions is automatically recorded in the redo log files. However, a redo-based application may require that additional columns be logged in the redo log files. The process of logging these additional columns is called supplemental logging.By default, Oracle Database does not provide any supplemental l

3、ogging, which means that by default LogMiner is not usable. Therefore, you must enable at least minimal supplemental logging before generating log files which will be analyzed by LogMiner.如果数据库需要使用 logminer，就应该添加,只有添加这个日志之后的才能捕获 DML了mink，Oracle 在文档Doc ID: Note:291574.1 中对这个问题进行了详细说明，如果希望 LOGMNR 可以得到

4、，应该设置 SUPPLEMENTAL LOG DATA PRIMARYKEY 和 UNIQUE INDEX，这样 Oracle 才能确保 LOGMNR 可以获取 SQL 语句：SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UIFROM V$DATABASE;SUP SUP- - NO NOSQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUEINDEX) COLUMNS;数据库已更改。SQL> SELECT SUPPLEMEN

5、TAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UIFROM V$DATABASE;SUP SUP- - YES YES誉天IT 认证站：誉天教育官网：3.开启归档 ,如果没有归档，只能看 online log 的信息检查是否归档SQL> archive log list ; Database log mode Automatic archival Archive destinationOldest online log sequence Current log sequence开启归档模式No Archive Mode DisabledUSE_DB_REC

6、OVERY_FILE_DEST 46shutdown immediate startup mountalter database archivelog;alter database open;在次检查SQL> archive log list ; Database log mode Automatic archival Archive destinationOldest online log sequence Next log sequence to archiveCurrent log sequenceArchive Mode EnabledUSE_DB_RECOVERY_FILE_D

7、EST 4664.配置 LogMiner 工具要安装 LogMiner 工具，必须首先要运行下面这样两个必须均以 SYS 用分析日志文件。第，这两个户运行。其中第一个用来创建 DBMS_LOGMNR 包，该二个用来创建 DBMS_LOGMNR_D 包，该desc DBMS_LOGMNR desc DBMS_LOGMNR_D创建数据字典文件。注意： 没有就需要跑下面的创建包！$ORACLE_HOME/rdbms/admin/dbmslmd.sql$ORACLE_HOME/rdbms/admin/dbmslm.sql誉天IT 认证站：誉天教育官网：$ORACLE_HOME/rdbms/admin/

8、dbmslm.sq $ORACLE_HOME/rdbms/admin/dbmslmd.sql程序包已创建。5.使用 LogMiner 工具5.1、设置参数 UTL_FILE_DIR数据字典文件是一个文本文件，使用包 DBMS_LOGMNR_D 来创建。如果我们要分析的数据库中的表有变化，影响到库的数据字典也发生变化，这时就需要重新创建该字典文件。另 外一种情况是在分析另外一个数据库文件的重作日志时，也必须要重新生成一遍被分析数据库的数据字典文件。在 ORACLE8I 的时候，首先在 init.ora 初始化参数文件中，指定数据字典文件的位置，也就是添加一个参数 UTL_FILE_DIR，该参数

9、值为服务器中放置数据字典文件的目录。如：UTL_FILE_DIR = (/tmp)ORACLE9I 后，推荐使用 SPFILE 启动，可以动态调整参数；SQL> show parameter spfile;NAMETYPEVALUE- - -+DATA/orcl/spfileorcl.ora- - - - - - - - - - - - - - - -spfilestringSQL> alter system set utl_file_dir='/tmp' scope=spfile;System altered系统已更改。重启生效Shutdown immediate

10、 ; StartupSQL> show parameter utl_file_dir;NAME- - - -utl_file_dirTYPE- - - - - - - -VALUE- - - - - -string/tmp誉天IT 认证站：誉天教育官网：SQL>5.2 创建数据字典文件SQL> begindbms_logmnr_d.build('testdict.data','/tmp'); end;/PL/SQL procedure successfully completed.host ls -lh /tmp5.3 创建要分析的日志文件列表

11、Oracle 的重作日志分为两种，（online）和离线（offline）归档日志文件，我这里主要分析归档日志，日志原理一样。-（online）SQL> select GROUP# ,SEQUENCE# ,STATUSfrom v$log;GROUP#SEQUENCE# STATUS- - - -1- - -4 INACTIVE25 INACTIVESQL> select member from v$logfile ;MEMBER- - - - - - - - - - - - - - - - - - - -+DATA/orcl/onlinelog/group_2.262.84232

12、5115+FRA/orcl/onlinelog/group 2.258.842325123+DATA/orcl/onlinelog/group_1.261.842325105+FRA/orcl/onlinelog/group_1.257.842325111誉天IT 认证站：+DATA/orcl/onlinelog/group_3.263.842325127+FRA/orcl/onlinelog/group_3.259.84232513336 CURRENT誉天教育官网：现在做任何操作都是的事物日志原来的值到第二个日志组的日志文件比如：SQL> select ename ,sal from

13、 scott.emp ;ENAME- - SMITH ALLEN WARDJONESSAL-800160012502975125028502450MARTIN BLAKECLARKSQL> update scott.emp set sal=0;14 rows updated.SQL> commit;Commit complete.该事物的日志写入了第三日志组A.创建列表SQL> execdbms_logmnr.add_logfile('+DATA/orcl/onlinelog/group_3.263.842325127',dbms_logmnr.);PL/SQ

14、L procedure successfully completed. B.添加另外的日志文件到列表SQL>execute dbms_logmnr.add_logfile(options=>dbms_logmnr,logfilename=>'+DATA/orcl/onlinelog/group_1.261.808534813');SQL> execute dbms_logmnr.add_logfile(options=>dbms_logmnr.a,logfilename=>'+DATA/orcl/onlinelog/group_2.2

15、62.808534823');誉天IT 认证站：ddfile.addfilenew誉天教育官网：/#说明：dbms_logmnr.new -用于建一个日志分析表dbms_logmnr.addfile -用于加，入用于分析的日志文件dbms_logmnr.removefile -用于移出，用于分析的日志文件删除execute dbms_logmnr.add_logfile(options =>dbms_logmnr.removefile,logfilename=>'+DATA/orcl/onlinelog/group_2.262.808534823');查看日

16、志文件列表:select db_name, thread_sqn,filename from v$logmnr_logsSQL> /DB_NAME- - - - ORCLTHREAD_SQN FILENAME- - - - - - - - - - - -6 +DATA/orcl/onlinelog/group_3.263.842325127-离线（offline）归档日志文件 归档日志BEGINdbms_logmnr.add_logfile('+fra/orcl/archivelog/2014_03_16/ thread_1_seq_6.260.842331263', D

17、BMS_LOGMNR.new );END;5.4 启动 LogMiner 进行分析5.4.1条件SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/tmp/testdict.data') ;誉天IT 认证站：誉天教育官网：PL/SQL procedure successfully completed.5.4.2 限制条件BEGINdbms_logmnr.start_logmnr( dictfilename => '/tmp/testdict.data',StartTime => to_da

18、te('2011-02-18 16:40:26','YYYY-MM-DD HH24:MI:SS'), EndTime => to_date('2011-02-18 16:44:41','YYYY-MM-DD HH24:MI:SS '); END;/5.5 观察分析结果（v$logmnr_contents）到现在为止，我们已经分析得到了重作日志文件中的内容。动态性能视图包含 LogMiner 分析得到的所有的信息。SELECT sql_redo FROM v$logmnr_contents;SELECT sql_redo FR

19、OM v$logmnr_contents where seg_name='EMP' SELECT sql_undo FROM v$logmnr_contents where seg_name='EMP'SELECT sql_redo FROM v$logmnr_contents where username='scott'and seg_name='scott.t1' and upper(operation)='delete'SELECT sql_redo FROM v$logmnr_contents where

20、seg_name='t1' andupper(operation)='delete'SELECT sql_redo FROM v$logmnr_contents where username='SYS' andtable_name='T1'6.关闭 LogMiner可以把 v$logmnr_contents 视图的内容创建一个的数据库表将非常有帮助sql> create table logmnr_contents as select * from v$logmnr_contents;当完成了重做日志的检查，运行 dbms_l

21、ogmnr 中的 end_logmnr execute dbms_logmnr.end_logmnr();誉天IT 认证站：誉天教育官网：建议做一次转存(练习)准备工作：开始补全日志SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UIFROM V$DATABASE;SUP SUP- - NO NOSQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUEINDEX) COLUMNS;数据库已更改。SQL> SELECT SUPPLE

22、MENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UIFROM V$DATABASE;SUP SUP- - YES YES开启归档模式Archive log list 查看 如果shutdown immediate startup mountalter database archivelog;alter database open;开启归档创建挖掘的数据字典目录SQL> alter system set utl_file_dir='/tmp' scope=spfile;誉天IT 认证站：誉天教育官网：System altered系统已更改

23、。重启生效Shutdown immediate ; StartupSQL> show parameter utl_file_dir;NAME- - - - - - - - - -utl_file_dir SQL>TYPE- - - - - - - -VALUE-string/tmp创建数据字典文件SQL> begindbms_logmnr_d.build('testdict.data','/tmp'); end;/PL/SQL procedure successfully completed.host ls -lh /tmp1 scott 登陆

24、 修改表的SQL> conn scott/oracle Connected.SQL> update scott.emp set sal=0;14 rows updated.SQL> commit;Commit complete.2 查出当前的日志组SQL> set linesize 100 SQL> lselect GROUP#,THREAD#,SEQUENCE#,STATUS ,FIRST_CHANGE#,FIRST_TIMEfrom v$log誉天IT 认证站：誉天教育官网：SQL> /GROUP#THREAD#SEQUENCE# STATUSFIRST

25、_CHANGE# FIRST_TIME-1-17 INACTIVE1045167 2013-02-28 01:33:353当前日志组是 216 INACTIVE1021200 2013-02-28 01:13:09在查 2 号日志组的日志文件名SQL>select MEMBERfrom v$logfile where group#=2;MEMBER+DATA/orcl/onlinelog/group_2.262.808534823+FRA/orcl/onlinelog/group_2.258.808534829添加日志exec dbms_logmnr.add_logfile('+

26、DATA/orcl/onlinelog/group_2.262.808534823',dbms_logmnr.ne w);PL/SQL procedure successfully completed. 你可以检查你对上面日志文件进行挖掘select db_name, thread_sqn,filename from v$logmnr_logs;DB_NAME THREAD_SQN-FILENAME-ORCL8+DATA/orcl/onlinelog/group_2.262.808534823开启挖掘SQL> exec dbms_logmnr.(dictfilename=>

27、'/tmp/testdict.data');PL/SQL procedure successfully completed.查看结果SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTSWHERE SEG_OWNER = 'SCOTT' AND SEG_NAME = 'EMP' ANDUSERNAME = 'SCOTT'誉天IT 认证站：誉天教育官网：OPERATIONSQL_REDOSQL_UNDOUPDATEupdate "SCOTT&q

28、uot;."EMP" set "SAL" = '0' where "EMPNO" = '7369' and "SAL" = '800' and ROWID = 'AAAR3xAAEAAAACXAAA'update "SCOTT"."EMP" set "SAL" ='800' where "EMPNO"= '7369' and "SA

29、L"= '0' and ROWID = 'AAAR3xAAEAAAACXAAA'UPDATEOPERATIONSQL_REDOSQL_UNDOupdate "SCOTT"."EMP" set "SAL" '1600' and ROWID = 'AAAR3xAAEAAAACXAAB'='0' where "EMPNO" ='7499' and "SAL" =update "SCOTT

30、"."EMP" set "SAL" = '1600' where "EMPNO" = '7499' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAB'UPDATE誉天IT 认证站：誉天教育官网：update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = &#

31、39;7521' and "SAL" ='1250' and ROWID = 'AAAR3xAAOPERATIONSQL_REDOSQL_UNDOEAAAACXAAC'update "SCOTT"."EMP" set "SAL" = '1250' where "EMPNO" = '7521' and "SAL"= '0' and ROWID = 'AAAR3xAAEAAAACXAA

32、C'UPDATEupdate "SCOTT"."EMP" set "SAL"'2975' and ROWID = 'AAAR3xAA EAAAACXAAD'= '0' where "EMPNO" = '7566' and "SAL" =OPERATIONSQL_REDOSQL_UNDOupdate "SCOTT"."EMP" set "SAL" = '297

33、5' where "EMPNO" = '7566' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAD'UPDATE誉天IT 认证站：誉天教育官网：update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7654' and "SAL" = '1250' and

34、 ROWID = 'AAAR3xAAEAAAACXAAE'update "SCOTT"."EMP" set "SAL" = '1250' where "EMPNO" = '7654' and "SAL"= '0' and ROWID = 'AAAR3xAAOPERATIONSQL_REDOSQL_UNDOEAAAACXAAE'UPDATEupdate "SCOTT"."EMP"

35、 set "SAL"'2850' and ROWID = 'AAAR3xAA EAAAACXAAF'= '0' where "EMPNO" = '7698' and "SAL" =update "SCOTT"."EMP" set "SAL" = '2850' where "EMPNO" = '7698' and "SAL"= '0&#

36、39; and ROWID='AAAR3xAAEAAAACXAAF'OPERATIONSQL_REDOSQL_UNDOUPDATE誉天IT 认证站：誉天教育官网：update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7782' and "SAL" = '2450' and ROWID = 'AAAR3xAAEAAAACXAAG'update "SCOTT

37、"."EMP" set "SAL" = '2450' where "EMPNO" = '7782' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAG'OPERATIONSQL_REDOSQL_UNDOUPDATEupdate "SCOTT"."EMP" set "SAL"'3000' and ROWID = 'AA

38、AR3xAA EAAAACXAAH'= '0' where "EMPNO" = '7788' and "SAL" =update "SCOTT"."EMP" set "SAL" = '3000' where "EMPNO" = '7788' and "SAL"= '0' and ROWID='AAAR3xAAEAAAACXAAH'UPDATEOPERAT

39、IONSQL_REDOSQL_UNDO誉天IT 认证站：誉天教育官网：update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7839' and "SAL" = '5000' and ROWID = 'AAAR3xAAEAAAACXAAI'update "SCOTT"."EMP" set "SAL" = '5000

40、' where "EMPNO" = '7839' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAI'UPDATEupdate "SCOTT"."EMP" set "SAL" '1500' and ROWID = 'AAAR3xAA= '0' where "EMPNO" = '7844' and "SAL&quo

41、t; =OPERATIONSQL_REDOSQL_UNDOEAAAACXAAJ'update "SCOTT"."EMP" set "SAL" = '1500' where "EMPNO" = '7844' and "SAL"= '0' and ROWID = 'AAAR3xAAEAAAACXAAJ'UPDATEupdate "SCOTT"."EMP" set "SAL&quo

42、t;'1100' and ROWID = 'AAAR3xAA EAAAACXAAK'= '0' where "EMPNO" = '7876' and "SAL" =OPERATIONSQL_REDOSQL_UNDO誉天IT 认证站：誉天教育官网：update "SCOTT"."EMP" set "SAL" = '1100' where "EMPNO" = '7876' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAK'UPDATEupdate "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = &#