logminer实践--客户的一次疏忽，DBA的一次噩梦。

it2022-05-05 273

客户的一次疏忽，DBA的一次噩梦。转载出处：http://www.oracleblog.org/working-case/dba-always-bad-luck-with-careless-customer/

今晚接到老大的电话，泰国的客户不小心删除了一些表的数据，现在非常着急，需要恢复数据。其实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], [], [], [], [], [], [], []

六、检查需要回滚的日志，客户要求回滚到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的。

抬头一看，东方既白。唉，又熬了个通宵。

专利

最新回复(0)