[20141210]使用logminer看遠端歸檔檔案補充

[20141210]使用logminer看遠端歸檔檔案(補充).txt

--如果要在本機看其他主機的歸檔或者日志檔案,由于dbid不一樣,是無法檢視的.必須要包括字典資訊.

--如果需要檢視别的資料庫的歸檔檔案，必須要有别的資料字典檔案。正常需要使用utl_file_dir，而經常這個參數沒有設定。

--在使用如下指令建立資料字典。

exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle11g/testd/dict_test.ora');

--如果沒有建立參數utl_file_dir：

SYS@test> show parameter utl_file_dir

NAME           TYPE     VALUE

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

utl_file_dir   string

SYS@test> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle11g/testd/dict_test.ora');

BEGIN dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle11g/testd/dict_test.ora'); END;

*

ERROR at line 1:

ORA-01292: no log file has been specified for the current LogMiner session

ORA-06512: at "SYS.DBMS_LOGMNR", line 58

ORA-06512: at line 1

--實際上可以使用EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);前提是建立附加日志。

--就可以把字典放在日志檔案中。把歸檔以及日志在拷貝過了就可以了。自己已經做了測試。參考連結：

<a href="http://blog.itpub.net/267265/viewspace-1360796/">http://blog.itpub.net/267265/viewspace-1360796/</a>

--我當時是先執行，打開附件日志。

SYS@test&gt; alter database add supplemental log data;

Database altered.

SYS@test&gt; archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     13

Next log sequence to archive   15

Current log sequence           15

SYS@test&gt; EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=&gt; DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

PL/SQL procedure successfully completed.

Oldest online log sequence     15

Next log sequence to archive   17

Current log sequence           17

$  ls -l 2014_12_08/

total 18704

-rw-r----- 1 oracle oinstall 9434112 2014-12-08 12:00:16 o1_mf_1_14_b8b8lj2y_.arc

-rw-r----- 1 oracle oinstall   12288 2014-12-08 12:00:55 o1_mf_1_15_b8b8mq3g_.arc

-rw-r----- 1 oracle oinstall 9432064 2014-12-08 12:00:58 o1_mf_1_16_b8b8mt39_.arc

--可以發現發生了兩次arhcive log 切換。我估計執行前先切換1次，執行完成後再切換1次。這樣資料字典在o1_mf_1_16_b8b8mt39_.arc

--中。

--這樣來确定資料字典在那個archive log 檔案中的。實際上還可以通過v$archived_log視圖的dictionary_begin,dictionary_end來确定。

--例子：

SYS@test&gt; select name,sequence#,first_change#,next_change#,blocks,dictionary_begin,dictionary_end from V$ARCHIVED_LOG ;

NAME                                                                                          SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#       BLOCKS DIC DIC

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

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_08/o1_mf_1_10_b8b8df1n_.arc              10        931129       931303          161 NO  NO

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_08/o1_mf_1_11_b8b8fk0f_.arc              11        931303       931316            1 NO  NO

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_08/o1_mf_1_12_b8b8kh27_.arc              12        931316       931554          171 YES NO

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_08/o1_mf_1_13_b8b8lf2o_.arc              13        931554       931761          170 YES NO

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_08/o1_mf_1_14_b8b8lj2y_.arc              14        931761       932735        18425 YES YES

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_08/o1_mf_1_15_b8b8mq3g_.arc              15        932735       932756           23 NO  NO

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_08/o1_mf_1_16_b8b8mt39_.arc              16        932756       933718        18421 YES YES

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_08/o1_mf_1_17_b8b9565v_.arc              17        933718       934020         1953 NO  NO

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_08/o1_mf_1_18_b8b9qgnt_.arc              18        934020       934793          973 NO  NO

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_08/o1_mf_1_19_b8b9qhjd_.arc              19        934793       935755        18420 YES YES

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_09/o1_mf_1_20_b8d0v1dq_.arc              20        935755       966171        88585 NO  NO

/u01/app/oracle/flash_recovery_area/TEST/archivelog/2014_12_09/o1_mf_1_21_b8g3fsbh_.arc              21        966171       998811        88596 NO  NO

12 rows selected.