生活随笔
收集整理的這篇文章主要介紹了
Logminer实战
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
相信許多DBA同學都曾遇到過這種情況，由于各種各樣的原因，必須對數據庫做不完全恢復；但又不確定應該恢復到哪個時間點或SCN才合適，因而反復的執行不完全恢復，悲劇掉了一地，中槍的有木有？溫馨提示下，在生產環境中做不完全恢復前，可能的話，先對現場做一次備份，有備無患！前面的是第一個問題，當找對了不完全恢復的時間點并成功執行不完全恢復后，后面的更改如何重做？讓客戶重演一遍？想都別想？打個比方，當前時間為上午11:00，不完全恢復的時間為上午8:30，把數據庫狀態恢復到上午8:30后，8:30到11:00這3.5小時的數據庫更改如何重演，這時間拖的越長就越悲劇！
Oracle從8i后提供了一個logminer免費的日志挖掘工具，可以解析歸檔，在線日志中的具體內容，針對前面提出的2個問題，可以很好的解決！下面使用oracle 10g平臺來實戰下…
一：對數據庫執行備份，生產環境中，這種操作都是計劃性的，過期的備份還應該定期歸檔
[oracle@rhel6?~]$?rman?target?/ ? Recovery?Manager:?Release?10.2.0.1.0?-?Production?on?Wed?May?9?19:24:02?2012 ? Copyright?(c)?1982,?2005,?Oracle.??All?rights?reserved. ? connected?to?target?database:?ORCL?(DBID =1289365799 ) ? ? RMAN> ?list?backup?of?database; ? using?target?database?control?file?instead?of?recovery?catalog ? ? RMAN> ?backup?database;? ? RMAN> ?list?backup?of?database?summary; ? ? List?of?Backups ? =============== ? Key?????TY?LV?S?Device?Type?Completion?Time?????#Pieces?#Copies?Compressed?Tag ? -------?--?--?-?-----------?-------------------?-------?-------?----------?--- ? 1???????B??F??A?DISK????????2012-05-09:19:25:20?1???????1???????NO?????????TAG20120509T192436? 二：開啟數據庫的記錄附加日志屬性，詳細作用參考：http://space.itpub.net/7607759/viewspace-462640
[oracle@rhel6?~]$?sqlplus?/nolog ? SQL*Plus:?Release?10.2.0.1.0?-?Production?on?Wed?May?9?19:26:14?2012 ? Copyright?(c)?1982,?2005,?Oracle.??All?rights?reserved. ? SQL> ?conn?/as?sysdba ? Connected. ? SQL> ?select?supplemental_log_data_min?from?v$database; ? ? SUPPLEME ? -------- ? NO ? ? SQL> ?alter?database?add?supplemental?log?data; ? Database?altered. ? ? SQL> ?select?supplemental_log_data_min?from?v$database; ? ? SUPPLEME ? -------- ? YES? 三：創建測試數據
SQL> ?conn?hr/hr ? Connected. ? SQL> ?create?table?t1?as?select?*?from?employees; ? Table?created. ? ? SQL> ?select?count(*)?from?t1; ? ? ??COUNT(*) ? ---------- ? ???????107 ? ? 這里記錄系統時間主要用于后面的對比，真實環境下是得不到這種信息的！ ?SQL> ?select?sysdate?from?dual;? ? ? SYSDATE ? ------------------- ? 2012-05-09:19:28:52 ? ? SQL> ?truncate?table?t1; ? Table?truncated. ? ? 在執行誤操作后，用戶繼續其他的更新操作，同時數據庫進行了日志切換 ?SQL> ?create?table?t2?(id?number); ? Table?created. ? ? SQL> ?insert?into?t2?values?(1); ? 1?row?created. ? ? SQL> ?insert?into?t2?values?(2); ? 1?row?created. ? ? SQL> ?commit; ? Commit?complete. ? ? SQL> ?conn?/as?sysdba? ? Connected. ? SQL> ?archive?log?list; ? Database?log?mode??????????Archive?Mode ? Automatic?archival?????????Enabled ? Archive?destination????????USE_DB_RECOVERY_FILE_DEST ? Oldest?online?log?sequence?????20 ? Next?log?sequence?to?archive???22 ? Current?log?sequence???????????22 ? ? SQL> ?alter?system?switch?logfile; ? System?altered.? 四：問題出現，現在需要將前面truncate的表找回，首先需要確定不完全恢復時間點的問題！下面開始介紹如何使用logminer
SQL> ?show?parameter?utl_file; ? ? NAME?????????????????????TYPE????VALUE ? ------------------------------------?-----------?------------------------------ ? utl_file_dir?????????????????string ? ? SQL> ?alter?system?set?utl_file_dir ='/home/oracle/utl_file_dir' ?scope =spfile ; ? System?altered. ? ? SQL> ?shutdown?immediate ? Database?closed. ? Database?dismounted. ? ORACLE?instance?shut?down. ? SQL> ?startup ? ORACLE?instance?started. ? ? Total?System?Global?Area??213909504?bytes ? Fixed?Size??????????2019640?bytes ? Variable?Size?????????104861384?bytes ? Database?Buffers??????100663296?bytes ? Redo?Buffers????????????6365184?bytes ? Database?mounted. ? Database?opened. ? ? SQL> ?show?parameter?utl_file; ? ? NAME?????????????????????TYPE????VALUE ? ------------------------------------?-----------?------------------------------ ? utl_file_dir?????????????????string??/home/oracle/utl_file_dir ? ? SQL> ?!mkdir?-p?/home/oracle/utl_file_dir? 2：使用sys用戶執行下面的3個sql腳本
SQL> ?show?user; ? USER?is?"SYS" ? SQL> ?@?/rdbms/admin/dbmslms.sql; ? Package?created. ? No?errors. ? Grant?succeeded. ? ? SQL> ?@?/rdbms/admin/dbmslm.sql; ? Package?created. ? Grant?succeeded. ? ? SQL> ?@?/rdbms/admin/dbmslmd.sql; ? Package?created.? 3：創建數據字典文件，該字典文件是可選的，但是如果沒有它，LogMiner解釋出來的語句中關于數據字典中的部分（如表名、列名等）和數值都將是16進制的形式，我們是無法直接理解的
SQL> ?exec?dbms_logmnr_d.build(dictionary_filename ?=> ?'V10201dict.ora',dictionary_location ?=> ?'/home/oracle/utl_file_dir'); ? PL/SQL?procedure?successfully?completed. ? ? SQL> ?!file?/home/oracle/utl_file_dir/V10201dict.ora ? /home/oracle/utl_file_dir/V10201dict.ora:?ASCII?English?text,?with?very?long?lines ? ? SQL> ?!du?-sh?/home/oracle/utl_file_dir/V10201dict.ora ? 23M?/home/oracle/utl_file_dir/V10201dict.ora? 4：添加在線日志，進行日志挖掘；生產環境中，一般都會將源庫的數據字典和日志文件拷貝到測試庫上分析，源庫不繼續以read write方式打開
SQL> ?select?member?from?v$logfile?where?type ='ONLINE' ; ? ? MEMBER ? ---------------------------------------- ? /u01/app/oracle/oradata/orcl/redo03.log ? /u01/app/oracle/oradata/orcl/redo02.log ? /u01/app/oracle/oradata/orcl/redo01.log ? ? SQL> ?exec?dbms_logmnr.add_logfile(logfilename ?=> ?'/u01/app/oracle/oradata/orcl/redo01.log',options ?=> ?dbms_logmnr.new); ? PL/SQL?procedure?successfully?completed. ? ? SQL> ?exec?dbms_logmnr.start_logmnr(dictfilename ?=> ?'/home/oracle/utl_file_dir/V10201dict.ora',options ?=> ?dbms_logmnr.ddl_dict_tracking); ? PL/SQL?procedure?successfully?completed.? 5：查看v$logmnr_contents視圖是否有相關的信息
SQL> ?select?distinct?username?from?v$logmnr_contents; ? ? USERNAME ? ------------------------------ ? SYSMAN ? SYS ? ? SQL> ?select?count(*)?from?v$logmnr_contents; ? ? ??COUNT(*) ? ---------- ? ????189963? 6：在線日志挖掘完后，發現沒有相關的信息，則開始挖掘歸檔日志文件
SQL> ?exec?dbms_logmnr.add_logfile(logfilename ?=> ?'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_05_09/o1_mf_1_22_7tnochcr_.arc',options ?=> ?dbms_logmnr.new); ? PL/SQL?procedure?successfully?completed. ? ? SQL> ?exec?dbms_logmnr.start_logmnr(dictfilename ?=> ?'/home/oracle/utl_file_dir/V10201dict.ora',options ?=> ?dbms_logmnr.ddl_dict_tracking); ? PL/SQL?procedure?successfully?completed. ? ? SQL> ?select?distinct?username?from?v$logmnr_contents; ? ? USERNAME ? ------------------------------ ? HR ? SYSMAN ? SYS? 7：通過查看v$logmnr_contents可以清楚的確定truncate的具體時間和之后的操作
SQL> ?col?sql_redo?format?a60?trunc ? SQL> ?select?sql_redo,timestamp?from?v$logmnr_contents?where?username ='HR' ?and?table_name ='T1' ?and?operation ='DDL' ; ? ? SQL_REDO?????????????????????????????TIMESTAMP ? ------------------------------------------------------------?------------------- ? create?table?t1?as?select?*?from?employees;??????????2012-05-09:19:28:25 ? truncate?table?t1;???????????????????????????????????2012-05-09:19:29:03 ? ? SQL> ?select?username,sql_redo,timestamp?from?v$logmnr_contents?where?timestamp?> ?'2012-05-09:19:29:03'?and?username?not?in?('SYS','SYSMAN'); ? ? USERNAME???SQL_REDO?????????????????TIMESTAMP ? ----------?----------------------------------------?------------------- ? HR?????set?transaction?read?write;??????????2012-05-09:19:29:27 ? HR??????????????????????????2012-05-09:19:29:27 ? HR?????insert?into?"SYS"."OBJ$"("OBJ#","DATAOBJ?2012-05-09:19:29:27 ? HR?????set?transaction?read?write;??????????2012-05-09:19:29:27 ? HR??????????????????????????2012-05-09:19:29:27 ? HR?????set?transaction?read?write;??????????2012-05-09:19:29:27 ? HR??????????????????????????2012-05-09:19:29:27 ? HR?????commit;??????????????????2012-05-09:19:29:27 ? HR?????insert?into?"SYS"."SEG$"("TS#","FILE#","?2012-05-09:19:29:27 ? HR?????update?"SYS"."TSQ$"?set?"GRANTOR#"?=?'0'?2012-05-09:19:29:27 ? HR?????commit;??????????????????2012-05-09:19:29:27 ? ? USERNAME???SQL_REDO?????????????????TIMESTAMP ? ----------?----------------------------------------?------------------- ? HR?????set?transaction?read?write;??????????2012-05-09:19:29:27 ? HR??????????????????????????2012-05-09:19:29:27 ? HR?????commit;??????????????????2012-05-09:19:29:27 ? HR?????insert?into?"SYS"."TAB$"("OBJ#","DATAOBJ?2012-05-09:19:29:27 ? HR?????insert?into?"SYS"."COL$"("OBJ#","COL#","?2012-05-09:19:29:27 ? HR?????create?table?t2?(id?number);?????????2012-05-09:19:29:27 ? HR?????update?"SYS"."SEG$"?set?"TYPE#"?=?'5',?"?2012-05-09:19:29:27 ? HR?????commit;??????????????????2012-05-09:19:29:27 ? HR?????set?transaction?read?write;??????????2012-05-09:19:29:39 ? HR?????insert?into?"HR"."T2"("ID")?values?('1')?2012-05-09:19:29:39 ? HR?????insert?into?"HR"."T2"("ID")?values?('2')?2012-05-09:19:29:43 ? ? USERNAME???SQL_REDO?????????????????TIMESTAMP ? ----------?----------------------------------------?------------------- ? HR?????commit;??????????????????2012-05-09:19:29:43 ? 23?rows?selected.? 五：執行不完全恢復
SQL> ?shutdown?immediate ? Database?closed. ? Database?dismounted. ? ORACLE?instance?shut?down. ? ? SQL> ?startup?mount ? ORACLE?instance?started. ? ? Total?System?Global?Area??213909504?bytes ? Fixed?Size??????????2019640?bytes ? Variable?Size?????????109055688?bytes ? Database?Buffers???????96468992?bytes ? Redo?Buffers????????????6365184?bytes ? Database?mounted. ? ? [oracle@rhel6?~]$?rman?target?/ ? Recovery?Manager:?Release?10.2.0.1.0?-?Production?on?Wed?May?9?20:21:30?2012 ? Copyright?(c)?1982,?2005,?Oracle.??All?rights?reserved. ? connected?to?target?database:?ORCL?(DBID =1289365799 ,?not?open) ? ? RMAN> ?restore?database; ? ? RMAN> ?run?{ ? 2> ?set?until?time?"to_date('2012-05-09:19:29:00','YYYY-MM-DD:HH24:MI:SS')"; ? 3> ?recover?database; ? 4> ?} ? ? RMAN> ?alter?database?open?resetlogs; ? database?opened ? ? RMAN> ?list?incarnation; ? ? List?of?Database?Incarnations ? DB?Key??Inc?Key?DB?Name??DB?ID????????????STATUS??Reset?SCN??Reset?Time ? -------?-------?--------?----------------?---?----------?---------- ? 1???????1???????ORCL?????1289365799???????PARENT??1??????????2005-10-22:21:44:08 ? 2???????2???????ORCL?????1289365799???????PARENT??525876?????2011-09-11:15:24:27 ? 3???????3???????ORCL?????1289365799???????CURRENT?831637?????2012-05-09:20:26:35? 六：剩下的就是根據v$logmnr_contents視圖中的sql_redo補齊之后的數據
[oracle@rhel6?~]$?sqlplus?/nolog ? SQL*Plus:?Release?10.2.0.1.0?-?Production?on?Wed?May?9?20:27:34?2012 ? Copyright?(c)?1982,?2005,?Oracle.??All?rights?reserved. ? ? SQL> ?conn?hr/hr ? Connected. ? SQL> ?select?count(*)?from?t1; ? ? ??COUNT(*) ? ---------- ? ???????107 ? ? SQL> ?select?count(*)?from?t2; ? select?count(*)?from?t2 ? ?????????????????????* ? ERROR?at?line?1: ? ORA-00942:?table?or?view?does?not?exist? 七、使用測試庫挖掘生產庫日志的注意事項
補充：
參考如下文章，感謝作者分享！
《新程序員》：云原生和全面數字化實踐 50位技術專家共同創作，文字、視頻、音頻交互閱讀
總結
以上是生活随笔 為你收集整理的Logminer实战 的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔 網站內容還不錯，歡迎將生活随笔 推薦給好友。
