今晚接到老大的电话，泰国的客户不小心删除了一些表的数据，现在非常着急，需要恢复数据。其实DBA做的数据库备份，很大程度是是用于数据库crash掉的时候，恢复数据，而不是三天两头的因为客户误删了数据，而去做恢复。
看了客户的邮件，是有2个表的数据被误删除或者误插入或者误更新了。总之，操作过一大通，希望恢复到当天下午15：30的数据。上数据库去查了一下，用备份来恢复，似乎时间不够，尝试用户flashback query，发现已经回不去了：
SQL> SQL> SQL> SELECTcount(*)fromhr_ttm.TA_ABSDOCS
2ASOFTIMESTAMPTO_TIMESTAMP('2011-06-09 15:29:00','YYYY-MM-DD HH24:MI:SS');
SELECTcount(*)fromhr_ttm.TA_ABSDOCS
*
ERRORatline1:
ORA-01555: snapshottooold: rollbacksegmentnumber1withname"_SYSSMU1$"
toosmall
2ASOFTIMESTAMPTO_TIMESTAMP('2011-06-09 15:29:00','YYYY-MM-DD HH24:MI:SS');
SELECTcount(*)fromhr_ttm.TA_ABSDOCS
*
ERRORatline1:
ORA-01555: snapshottooold: rollbacksegmentnumber1withname"_SYSSMU1$"
toosmall
其他也没有更快的方法了，于是当下决定用logmnr挖数据，
由于数据库原来就没有配置utl_file_dir，因此还需要重启数据库使得该参数生效。一路做下来，大致算顺利，不过也遇到了不少小插曲。下面就是恢复的步骤：
一、备份原表
createtablehr_ttm.TA_ABSDOCS_20110610_0010as
SELECT * fromhr_ttm.TA_ABSDOCS;
createtablehr_ttm.TA_ABSDOC_20110610_0010as
SELECT * fromhr_ttm.TA_ABSDOC;
SELECT * fromhr_ttm.TA_ABSDOCS;
createtablehr_ttm.TA_ABSDOC_20110610_0010as
SELECT * fromhr_ttm.TA_ABSDOC;
二，根据客户要求，建立新用户，将恢复的数据导入到这2个表中：
createuserhr_ttm2identifiedbyhr_ttm2defaulttablespaceMSG_DATA;
grantconnect,resource,dbatohr_ttm2;
grantconnect,resource,dbatohr_ttm2;
三、把原表数据备份到新用户下，用于做回滚
createtablehr_ttm2.TA_ABSDOCSas
SELECT * fromhr_ttm.TA_ABSDOCS
createtablehr_ttm2.TA_ABSDOCas
SELECT * fromhr_ttm.TA_ABSDOC
SELECT * fromhr_ttm.TA_ABSDOCS
createtablehr_ttm2.TA_ABSDOCas
SELECT * fromhr_ttm.TA_ABSDOC
四、修改参数，用于挖日志，重启数据库
altersystemsetutl_file_dir='/prodlog/logmnr'scope=spfile;
五、生成数据字典
execdbms_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之前：
-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
-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
七、添加归档日志
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025854_0666465023.0001',Options=>dbms_logmnr.new);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025855_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025856_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025857_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025858_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025859_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025860_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025861_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025862_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025863_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025864_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025865_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025866_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025867_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025868_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025869_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025870_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025871_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025872_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025873_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025874_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025875_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025855_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025856_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025857_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025858_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025859_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025860_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025861_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025862_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025863_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025864_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025865_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025866_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025867_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025868_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025869_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025870_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025871_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025872_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025873_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025874_0666465023.0001',Options=>dbms_logmnr.addfile);
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025875_0666465023.0001',Options=>dbms_logmnr.addfile);
八、开始挖日志
execdbms_logmnr.start_logmnr(dictfilename=>'/prodlog/logmnr/dictionary.ora');
九、将logmnr的数据暂时保存在一个表里面，免得再次查询时候不用再次添加归档日志
createtablehjm_logmnrnologgingasselect * fromv$logmnr_contentswhere1=2;
insert/*+ append */intohjm_logmnrselect * fromv$logmnr_contents;
做这一步之前注意将nls_date_format改成'yyyy-mm-dd hh24:mi:ss'，不然泰文乱码，时间会变成问号。
insert/*+ append */intohjm_logmnrselect * fromv$logmnr_contents;
做这一步之前注意将nls_date_format改成'yyyy-mm-dd hh24:mi:ss'，不然泰文乱码，时间会变成问号。
九、导出脚本，用脚本做回滚，注意SQL_UNDO中的delete语句末尾有rowid，不能直接用，需要用正则表达式替换掉。
spoolTA_ABSDOCS_undosql.txt
selectregexp_replace(replace(SQL_UNDO,'"HR_TTM"','"HR_TTM2"'),'and ROWID.+;',';')
fromhjm_logmnr
WHERE
SEG_NAME = 'TA_ABSDOCS'AND
SEG_OWNER = 'HR_TTM'
orderbyto_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')desc;
spooloff
spoolTA_ABSDOC_undosql.txt
selectregexp_replace(replace(SQL_UNDO,'"HR_TTM"','"HR_TTM2"'),'and ROWID.+;',';')
fromhjm_logmnr
WHERE
SEG_NAME = 'TA_ABSDOC'AND
SEG_OWNER = 'HR_TTM'
orderbyto_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')desc;
spooloff
selectregexp_replace(replace(SQL_UNDO,'"HR_TTM"','"HR_TTM2"'),'and ROWID.+;',';')
fromhjm_logmnr
WHERE
SEG_NAME = 'TA_ABSDOCS'AND
SEG_OWNER = 'HR_TTM'
orderbyto_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')desc;
spooloff
spoolTA_ABSDOC_undosql.txt
selectregexp_replace(replace(SQL_UNDO,'"HR_TTM"','"HR_TTM2"'),'and ROWID.+;',';')
fromhjm_logmnr
WHERE
SEG_NAME = 'TA_ABSDOC'AND
SEG_OWNER = 'HR_TTM'
orderbyto_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')desc;
spooloff
去掉脚本的头部的语句和末尾返回多少多少行的文字，在hr_ttm2下执行这2个脚本，实现数据回滚。
另外，我们来看一下：
SQL> selectto_char(max(TIMESTAMP),'yyyy-mm-dd hh24:mi:ss'),to_char(min(TIMESTAMP),'yyyy-mm-dd hh24:mi:ss')
fromhjm_logmnr
WHERE
SEG_NAME = 'TA_ABSDOC'AND
SEG_OWNER = 'HR_TTM'2345
6 /
TO_CHAR(MAX(TIMESTATO_CHAR(MIN(TIMESTA
------------------- -------------------
2011-06-0921:20:262011-06-0915:31:54
SQL>
SQL>
SQL>
SQL>
SQL> l
1selectto_char(max(TIMESTAMP),'yyyy-mm-dd hh24:mi:ss'),to_char(min(TIMESTAMP),'yyyy-mm-dd hh24:mi:ss')
2fromhjm_logmnr
3WHERE
4SEG_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(TIMESTATO_CHAR(MIN(TIMESTA
------------------- -------------------
2011-06-0921:20:262011-06-0915:37:39
fromhjm_logmnr
WHERE
SEG_NAME = 'TA_ABSDOC'AND
SEG_OWNER = 'HR_TTM'2345
6 /
TO_CHAR(MAX(TIMESTATO_CHAR(MIN(TIMESTA
------------------- -------------------
2011-06-0921:20:262011-06-0915:31:54
SQL>
SQL>
SQL>
SQL>
SQL> l
1selectto_char(max(TIMESTAMP),'yyyy-mm-dd hh24:mi:ss'),to_char(min(TIMESTAMP),'yyyy-mm-dd hh24:mi:ss')
2fromhjm_logmnr
3WHERE
4SEG_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(TIMESTATO_CHAR(MIN(TIMESTA
------------------- -------------------
2011-06-0921:20:262011-06-0915:37:39
归档日志是从14:53开始，而这2个表的变动，是在15：30之后才有的（min(TIMESTAMP是在15：30之后）。
也就是说从14：53分到15：30之前，这2个表一直是没动过的。因此，可以跟客户说，2个表的数据恢复到15：00的。
