今晚接到老大的电话，泰国的客户不小心删除了一些表的数据，现在非常着急，需要恢复数据。其实DBA做的数据库备份，很大程度是是用于数据库crash掉的时候，恢复数据，而不是三天两头的因为客户误删了数据，而去做恢复。
看了客户的邮件，是有2个表的数据被误删除或者误插入或者误更新了。总之，操作过一大通，希望恢复到当天下午15：30的数据。上数据库去查了一下，用备份来恢复，似乎时间不够，尝试用户flashback query，发现已经回不去了：
|
1 2 3 4 5 6 7 |
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，因此还需要重启数据库使得该参数生效。一路做下来，大致算顺利，不过也遇到了不少小插曲。下面就是恢复的步骤：
一、备份原表
|
1 2 3 4 5 |
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个表中：
|
1 2 3 |
create user hr_ttm2 identified by hr_ttm2 default tablespace MSG_DATA; grant connect,resource,dba to hr_ttm2; |
三、把原表数据备份到新用户下，用于做回滚
|
1 2 3 4 5 |
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 |
四、修改参数，用于挖日志，重启数据库
|
1 |
alter system set utl_file_dir='/prodlog/logmnr' scope=spfile; |
五、生成数据字典
|
1 |
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], [], [], [], [], [], [], []
六、检查需要回滚的日志，客户要求回滚到6月9日15：30之前：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-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 |
七、添加归档日志
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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); |
八、开始挖日志
|
1 |
exec dbms_logmnr.start_logmnr(dictfilename=>'/prodlog/logmnr/dictionary.ora'); |
九、将logmnr的数据暂时保存在一个表里面，免得再次查询时候不用再次添加归档日志
|
1 2 3 |
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，不能直接用，需要用正则表达式替换掉。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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个脚本，实现数据回滚。
另外，我们来看一下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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的。
抬头一看，东方既白。唉，又熬了个通宵。
17条评论
思路很清晰啊，很不错的一次经历，但愿我遇不到。哈哈
“在hr_ttm2下执行这2个脚本，实现数据回滚”,运行TA_ABSDOCS_undosql.txt和
TA_ABSDOC_undosql.txt这两个脚本来实现恢复数据，但是
”去掉脚本的头部的语句和末尾返回多少多少行的文字“，如果文件比较大，有啥好方法比较快的去掉？用cat和tail看下文件首尾，然后用sed等工具去掉吗？
另：oracle正则表达式哪里有介绍的文章？多谢
操作过一大通，希望恢复到当天下午15：30的数据
也就是说从14：53分到15：30之前，这2个表一直是没动过的。因此，可以跟客户说，2个表的数据恢复到15：00的。
－－－〉恢复到15：30？
re wangliang:正则表达式，推荐可以看看Advanced SQL Functions in Oracle 10g
re gobird:其实这2个表在15：30之前是没有dml操作的。通过archivelog，我是恢复到了14：53分。 而之前客户要求恢复到15：30分之前的任意时刻，因此如果精确的说，是已经恢复到了14：53分，取个整数的话，大致上可以说恢复到了15：00.
脚本不需要将redo中提取的delete改成insert？
re jyc: 不用，用的是sql_undo, 不是sql_redo
数据库是什么版本？
如果是9i之上 这个参数“utl_file_dir” 不用设置也可以吧
后面的能不能解释下呀，怎么感觉很混乱了
小荷，问下，你的这个环境启动 SUPPLEMENTAL LOG 没？
re Dave，没开启supplemental log：
SQL> select supplemental_log_data_min,
2 supplemental_log_data_pk,
supplemental_log_data_ui,
supplemental_log_data_fk,
supplemental_log_data_all
from v$database; 3 4 5 6
SUPPLEME SUP SUP SUP SUP
——– — — — —
NO NO NO NO NO
SQL>
小荷，根据你的经历我做了一次实验，倒是可以成功恢复，但是spool出来的undo脚本需要格式化整理之后才能运行，
直接运行提示错误，你有好的建议吗？
希望有一天我也能碰到这样一次机会……
已经把分析结果copy到相关表了，利用pl/sql developer等相关工具直接将sql_undo提取到文本中，然后直接执行应该就可以了吧
小荷总 已经将sql的正则表达式玩的溜溜转了 佩服啊 ^_^
想請教版大一個關於oracle 資料還源的問題:
oracle資料庫有某table資料遺失。
想將之前備份所有table的dmp檔中，某個table轉為文字檔?
試過用excel、urtra edit開啟都會有亂碼?
好像是因為二元檔的關係?
用MS visual studio開啟，會說不支援舊傾印檔格式?
只能用imp還原回資料庫的方式嗎?
是否有什麼軟體或方式，可在windows 系統中，將.dmp檔中某個特定table轉為文字檔修改?
想請版大幫忙~
re peter:
dmp文件是二进制文件，不能在excel，utraedit中打开。
常规的方式来说，只能imp到数据库中去读取。另外在unix环境下，用strings命令能看dmp文件里面的内容
小荷，你好。针对这个案例有些疑问。
看你的回复，出事故的数据库连Minimal Supplemental Logging都没有启用，不知道数据库的版本是什么。如果是10g，由于受imu特性的影响，用logmnr做日志挖掘基本是不可能的。即使都能挖掘出来，如果未追加主键的日志(SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS)，也应该必须基于rowid来恢复。
比如
update scott.emp set sal=2453 where empno=7728;
lognmr出来的sql_undo会是
update “SCOTT”.”EMP” set “SAL” = ‘2452’ where “SAL” = ‘2453’ and ROWID = ‘AAAMfMAAEAAAAAcAAG’;
将rowid过滤掉，如何能保证恢复的正确性？难道update的字段都具有唯一性？
dbms_logmnr.NO_ROWID_IN_STMT