logminer实践--客户的一次疏忽，DBA的一次噩梦。-阿里云开发者社区

客户的一次疏忽，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的。

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

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

热门文章

最新文章

相关课程

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

热门文章

最新文章

相关课程

相关电子书