oracle11g之LogMiner分析重做日志實踐

來源：懂視網(wǎng) 責編：小采時間：2020-11-09 16:29:50

oracle11g之LogMiner分析重做日志實踐

oracle11g之LogMiner分析重做日志實踐:1.安裝LogMiner 以DBA用戶身份運行下面2個腳本： dbmslm.sql用來創(chuàng)建DBMS_LOGMNR包，該包用來分析日志文件。 SQL @$ORACLE_HOME/rdbms/admin/dbmslm.sql;Package created.Grant succeeded.Synonym created. dbms

推薦度：

點擊下載本文 文檔為doc格式

導讀oracle11g之LogMiner分析重做日志實踐:1.安裝LogMiner 以DBA用戶身份運行下面2個腳本： dbmslm.sql用來創(chuàng)建DBMS_LOGMNR包，該包用來分析日志文件。 SQL @$ORACLE_HOME/rdbms/admin/dbmslm.sql;Package created.Grant succeeded.Synonym created. dbms

dbmslm.sql用來創(chuàng)建DBMS_LOGMNR包，該包用來分析日志文件。

SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql;

Package created.

Grant succeeded.

Synonym created.

dbmslmd.sql用來創(chuàng)建DBMS_LOGMNR_D包，該包用來創(chuàng)建數(shù)據(jù)字典文件。

SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql;

Package created.

Synonym created.

2.設(shè)置參數(shù)UTL_FILE_DIR

添加參數(shù)UTL_FILE_DIR,該參數(shù)值為服務(wù)器中放置數(shù)據(jù)字典文件的目錄，

SQL> CREATE DIRECTORY utlfile AS '/home/oracle/logminer';

Directory created.

SQL> alter system set utl_file_dir='/home/oracle/logminer' scope=spfile;

System altered.

重啟數(shù)據(jù)庫，生效上面的設(shè)置。

SQL> shutdown immediate;

SQL> startup;

SQL> show parameters utl_file_dir;

3.啟動補充日志

補充日志不是LogMiner日志分析必須的步驟，但是如果不啟用補充日志，分析日志的的很多結(jié)果集信息就會顯示為“UNKNOWN”，下面是開啟最小補充日志。

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME

 --------

 YES

4.創(chuàng)建專門的LogMiner用戶

不是必須，實為管理安全方便。

5.創(chuàng)建數(shù)據(jù)字典

SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/logminer');

6.添加需要分析的在線日志或者歸檔日志

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'

/home/oracle/flash_recovery_area/PRIMARY/archivelog/2016_04_25/o1_mf_1_199_ckv04o6w_.arc',OPTIONS => DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'

/home/oracle/flash_recovery_area/PRIMARY/archivelog/2016_04_25/o1_mf_1_201_ckv08jyp_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'

/home/oracle/flash_recovery_area/PRIMARY/archivelog/2016_04_25/o1_mf_1_200_ckv05pmp_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

7. 使用字典分析歸檔日志文件

SQL> EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');

8.在線日志分析實例

插入數(shù)據(jù)

SQL> insert into nn.t1 values(1000,'succ');

1 row created.

SQL> commit;

Commit complete.

查詢當前日志序列號

SQL> SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORD ER BY first_change#;

 GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------------- ------------- ---------

 1 208 INACTIVE 2825768 25-APR-16

 2 209 INACTIVE 2825872 25-APR-16

 3 210 CURRENT 2845771 25-APR-16

加入當前日志組

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/home/oracle/oradata/orcl/redo03.log',OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

使用LogMiner進行分析

SQL> EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');

PL/SQL procedure successfully completed.

查詢分析結(jié)果

SQL> SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='T1';

SQL_REDO SQL_UNDO SEG_OWNER

insert into "NN"."T1"("TID","TNAME") values ('1000','succ');

delete from "NN"."T1" where "TID" = '1000' and "TNAME" = 'succ' and ROWID = 'AAAR7YAAEAAAACrAAD';

NN