db2dart恢复数据

当数据库发生IO错误，而且没有有效的备份；该怎么办呢，最后的救命稻草就是db2dart这个神奇的工具。接下来我来模拟一个故障并通过db2dart来恢复的案例。

操作步骤：

STEP1模拟一个故障，删除在线活动日志

STEP2登录数据库，检查故障

STEP3 db2dart导出数据

STEP4恢复数据

STEP5启动数据库

详细步骤：

STEP1模拟一个故障，删除在线活动日志

查看数据库在线日志目录

[db2inst1@localhost ~]$ db2 get db cfg for sample |grep -i log

 Log retain for recovery status                          = RECOVERY

 User exit for logging status                            = YES

Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

 Overflow log path                     (OVERFLOWLOGPATH) =

 Mirror log path                         (MIRRORLOGPATH) = /db2/data/db2insta/mirror_log/

 First active log file                                   = S0000005.LOG

 Block log on disk full                (BLK_LOG_DSK_FUL) = NO

 Block non logged operations            (BLOCKNONLOGGED) = NO

 Percent max primary log space by transaction  (MAX_LOG) = 0

 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520

 Log retain for recovery enabled             (LOGRETAIN) = RECOVERY

 User exit for logging enabled                (USEREXIT) = OFF

 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC

 First log archive method                 (LOGARCHMETH1) = DISK:/db2/data/db2insta/archvie_log/

删除归档日志

[db2inst1@localhost ~]$ db2 get db cfg |grep -i mirror

 Mirror log path                         (MIRRORLOGPATH) = /db2/data/db2insta/mirror_log/

db2 update db cfg using MIRRORLOGPATH NULL

db2 update db cfg using LOGARCHMETH1 NULL

db2 update db cfg using LOGRETAIN OFF

rm /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/*

db2stop force

db2start

STEP2登录数据库，检查故障

登录数据库

db2 connect to sample

SQL1036C  An I/O error occurred while accessing the database.  SQLSTATE=58030

尝试restart恢复

db2 restart db sample

SQL1036C  An I/O error occurred while accessing the database.  SQLSTATE=58030

没有备份，没办法了只有看看db2dart能不能管用了

导表清单

db2dart sample

systables,0,0,9999

保存生成table.index

导表数据

#table.index是导出的systables的内容 【脚步转自网友mdkii 】

sed 's/"//g'  tables.index | while read line 

do

  echo $line | awk -F"," '{

     objtype=$3

     gsub(" ","",$2)

     dbschema=$2

     if ( objtype != "T" || dbschema != "'$SCHEMANAME'" )

        continue

     tablename=$1

     tableid=$7

     tbspaceid=$8

     printf("%s,%s,0,99999999nyn%sn",tableid,tbspaceid,tablename)

  }' > inputfile

  if [ -s inputfile ]

  then

#这里是导出到当前目录

    db2dart sample /DDEL /RPT  ./  < inputfile

  fi

done

恢复数据

重建数据库

使用以前备份的DDL脚步，建表

db2 –tvf  test.ddl

导入数据

load导入数据