客戶的一次疏忽，DBA的一次噩夢
作者：小荷
你是否如同作者一樣，有過如下噩夢一般的經歷呢？是否半夜被老板挖起來，只為了客戶的一個小小的誤操作？無論是否在你身上印證過，都可以來看看，作者寫這篇文章之時已經熬了一夜，還能思路如此清晰，值得學習，佩服之~~~
今晚接到老大的電話，泰國的客戶不小心刪除了一些表的數據，現在非常著急，需要恢復數據。其實DBA做的數據庫備份，很大程度是用于數據庫crash掉的時候，恢復數據，而不是三天兩頭的因為客戶誤刪了數據，而去做恢復。
看了客戶的郵件，是有2個表的數據被誤刪除或者誤插入或者誤更新了?？傊?，操作過一大通，希望恢復到當天下午15：30的數據。上數據庫去查了一下，用備份來恢復，似乎時間不夠，嘗試用戶flashback query，發現已經回不去了：
- SQL> SQL> SQL> SELECT count(*) from hr_ttm.TA_ABSDOCS
- 2 AS OF TIMESTAMP TO_TIMESTAMP('2011-06-09 15:29:00','YYYY-MM-DD HH24:MI:SS');
- SELECT count(*) from hr_ttm.TA_ABSDOCS
- *
- ERROR at line 1:
- ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$"
- too small
其他也沒有更快的方法了，于是當下決定用logmnr挖數據，
由于數據庫原來就沒有配置utl_file_dir，因此還需要重啟數據庫使得該參數生效。一路做下來，大致算順利，不過也遇到了不少小插曲。下面就是恢復的步驟：
一、備份原表
- create table hr_ttm.TA_ABSDOCS_20110610_0010 as
- SELECT * from hr_ttm.TA_ABSDOCS;
- create table hr_ttm.TA_ABSDOC_20110610_0010 as
- SELECT * from hr_ttm.TA_ABSDOC;
二，根據客戶要求，建立新用戶，將恢復的數據導入到這2個表中：
- create user hr_ttm2 identified by hr_ttm2 default tablespace MSG_DATA;
- grant connect,resource,dba to hr_ttm2;
三、把原表數據備份到新用戶下，用于做回滾
- create table hr_ttm2.TA_ABSDOCS as
- SELECT * from hr_ttm.TA_ABSDOCS
- create table hr_ttm2.TA_ABSDOC as
- SELECT * from hr_ttm.TA_ABSDOC
四、修改參數，用于挖日志，重啟數據庫
- alter system set utl_file_dir='/prodlog/logmnr' scope=spfile;
五、生成數據字典
- exec dbms_logmnr_d.build('dictionary.ora','/prodlog/logmnr');
做這一步之前注意需要修改LD_LIBRARY_PATH和LIBPATH，使得lib的變量在lib32前面。不然會有報錯ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []
#p#
六、檢查需要回滾的日志，客戶要求回滾到6月9日15：30之前：
- -rw-r----- 1 oracle oinstall 48868352 Jun 09 14:53 ARC0000025854_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 15:08 ARC0000025855_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 15:17 ARC0000025856_0666465023.0001
- -rw-r----- 1 oracle oinstall 48910848 Jun 09 15:42 ARC0000025857_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 16:04 ARC0000025858_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 17:22 ARC0000025859_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 17:27 ARC0000025860_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 17:47 ARC0000025861_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 20:52 ARC0000025862_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 21:17 ARC0000025863_0666465023.0001
- drwxr-xr-x 2 oracle oinstall 256 Jun 09 23:02 logmnr
- -rw-r----- 1 oracle oinstall 48863744 Jun 09 23:07 ARC0000025864_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:28 ARC0000025865_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:29 ARC0000025866_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:29 ARC0000025867_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:29 ARC0000025868_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:29 ARC0000025869_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:30 ARC0000025870_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:30 ARC0000025871_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:30 ARC0000025872_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:31 ARC0000025873_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:31 ARC0000025874_0666465023.0001
- -rw-r----- 1 oracle oinstall 48868864 Jun 09 23:31 ARC0000025875_0666465023.0001
七、添加歸檔日志
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025854_0666465023.0001',Options=>dbms_logmnr.new);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025855_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025856_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025857_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025858_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025859_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025860_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025861_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025862_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025863_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025864_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025865_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025866_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025867_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025868_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025869_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025870_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025871_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025872_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025873_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025874_0666465023.0001',Options=>dbms_logmnr.addfile);
- exec dbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025875_0666465023.0001',Options=>dbms_logmnr.addfile);
八、開始挖日志
- exec dbms_logmnr.start_logmnr(dictfilename=>'/prodlog/logmnr/dictionary.ora');
九、將logmnr的數據暫時保存在一個表里面，免得再次查詢時候不用再次添加歸檔日志
- create table hjm_logmnr nologging as select * from v$logmnr_contents where 1=2;
- insert /*+ append */ into hjm_logmnr select * from v$logmnr_contents;
- /*------做這一步之前注意將nls_date_format改成'yyyy-mm-dd hh24:mi:ss'，不然泰文亂碼，時間會變成問號。------*/
十、導出腳本，用腳本做回滾，注意SQL_UNDO中的delete語句末尾有rowid，不能直接用，需要用正則表達式替換掉。
- spool TA_ABSDOCS_undosql.txt
- select regexp_replace(replace(SQL_UNDO,'"HR_TTM"','"HR_TTM2"'),'and ROWID.+;',';')
- from hjm_logmnr
- WHERE
- SEG_NAME = 'TA_ABSDOCS' AND
- SEG_OWNER = 'HR_TTM'
- order by to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') desc;
- spool off
- spool TA_ABSDOC_undosql.txt
- select regexp_replace(replace(SQL_UNDO,'"HR_TTM"','"HR_TTM2"'),'and ROWID.+;',';')
- from hjm_logmnr
- WHERE
- SEG_NAME = 'TA_ABSDOC' AND
- SEG_OWNER = 'HR_TTM'
- order by to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') desc;
- spool off
去掉腳本的頭部的語句和末尾返回多少多少行的文字，在hr_ttm2下執行這2個腳本，實現數據回滾。
另外，我們來看一下：
- SQL> select to_char(max(TIMESTAMP),'yyyy-mm-dd hh24:mi:ss'),to_char(min(TIMESTAMP),'yyyy-mm-dd hh24:mi:ss')
- from hjm_logmnr
- WHERE
- SEG_NAME = 'TA_ABSDOC' AND
- SEG_OWNER = 'HR_TTM' 2 3 4 5
- 6 /
- TO_CHAR(MAX(TIMESTA TO_CHAR(MIN(TIMESTA
- ------------------- -------------------
- 2011-06-09 21:20:26 2011-06-09 15:31:54
- SQL>
- SQL>
- SQL>
- SQL>
- SQL> l
- 1 select to_char(max(TIMESTAMP),'yyyy-mm-dd hh24:mi:ss'),to_char(min(TIMESTAMP),'yyyy-mm-dd hh24:mi:ss')
- 2 from hjm_logmnr
- 3 WHERE
- 4 SEG_NAME = 'TA_ABSDOC' AND
- 5* SEG_OWNER = 'HR_TTM'
- SQL> l4
- 4* SEG_NAME = 'TA_ABSDOC' AND
- SQL> c/TA_ABSDOC/TA_ABSDOCS
- 4* SEG_NAME = 'TA_ABSDOCS' AND
- SQL>
- TO_CHAR(MAX(TIMESTA TO_CHAR(MIN(TIMESTA
- ------------------- -------------------
- 2011-06-09 21:20:26 2011-06-09 15:37:39
歸檔日志是從14:53開始，而這2個表的變動，是在15：30之后才有的（min(TIMESTAMP是在15：30之后）。
也就是說從14：53分到15：30之前，這2個表一直是沒動過的。因此，可以跟客戶說，2個表的數據恢復到15：00的。
抬頭一看，東方既白。唉，又熬了個通宵。
【小邊碎語】以上是作者在月初的一次噩夢一般的經歷，無論是否在你身上印證過，都可以來看看，作者真不愧是專業級人物，思路超清晰，值得學習和借鑒。
【編輯推薦】
- Oracle SQL：經典查詢練手第一篇
- Oracle SQL：經典查詢練手第二篇
- Oracle SQL：經典查詢練手第三篇
- Oracle SQL：經典查詢練手第四篇
- Oracle SQL：經典查詢練手第五篇
責任編輯：艾婧
來源：
OracleBlog
