ITPub博客

首页 > Linux操作系统 > Linux操作系统 > db2 数据库备份恢复

db2 数据库备份恢复

原创 Linux操作系统 作者:zhuhichn 时间:2012-01-04 21:46:48 0 删除 编辑
db2 数据库备份恢复
1.查询当前日志模式,是归档日志还是循环日志 
[db2inst1@localhost ~]$ db2 get db cfg | grep log
 User exit for logging status                            = NO
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = (MAXAPPLS*5)
 Number of primary log files                (LOGPRIMARY) = 5
 Number of secondary log files               (LOGSECOND) = 3
 Changed path to log files                  (NEWLOGPATH) = 
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) = 
 Mirror log path                         (MIRRORLOGPATH) = 
 First active log file                                   = 
 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) = 100
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) = 
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) = 
 Failover log archive path                (FAILARCHPATH) = 
 Number of log archive retries on error   (NUMARCHRETRY) = 5

更改日志模式为归档日志
[db2inst1@localhost ~]$ db2 update db cfg using LOGRETAIN recovery
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification 
were not changed dynamically. For these configuration parameters, all 
applications must disconnect from this database before the changes become 
effective.


更改变量revovery trackmod ,此参数更改后,可以进行增量备份
[db2inst1@localhost ~]$ db2 update db cfg using logretain recovery trackmod on
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification 
were not changed dynamically. For these configuration parameters, all 
applications must disconnect from this database before the changes become 
effective.



-----在线备份命令
db2 backup db testdb online to 备份路径(全备份)


----备份从上次全备份到现在的增量数据
db2 backup db testdb online incremental to 备份路径(增量备份)

---只备份增量数据,与上一个备份不一样
db2 backup db testdb online incremental delta to 备份路径(delta备份)





-----本次例子中用的是增量备份


------恢复时,先恢复最近的一次增量备份文件,


------------------------------------错误方法--------------------
-----开始恢复的是全备份文件
[db2inst1@localhost db2bak]$ l
总计 198744
-rw------- 1 db2inst1 db2iadm1 134733824 12-05 19:39 SAMPLE.0.db2inst1.NODE0000.CATN0000.20111205193902.001
-rw------- 1 db2inst1 db2iadm1  68558848 12-05 19:43 SAMPLE.0.db2inst1.NODE0000.CATN0000.20111205194339.001
[db2inst1@localhost db2bak]$ db2 restore db sample taken at 20111205193902
DB20000I  The RESTORE DATABASE command completed successfully.

---使用db2ckrst命令查看备份步骤会出现错误,因为查看的时间戳在备份文件时间戳之后
[db2inst1@localhost db2bak]$ db2ckrst -d sample -t 20111205194339

Error: db2ckrst - No image found for timestamp 20111205194339
Return code of -8 seen at line 1317

----此次为验证,输入了一个匹配时间
[db2inst1@localhost db2bak]$ db2ckrst -d sample -t 20111205193902

Suggested restore order of images using timestamp 20111205193902 for
database sample.
====================================================================
 restore db sample incremental taken at 20111205193902
 restore db sample incremental taken at 20111205193902
====================================================================


-----------------------------错误方法---------------------------------



----------发现错误后,删除数据库,重新试验

[db2inst1@localhost db2bak]$ db2 drop db sample
DB20000I  The DROP DATABASE command completed successfully.
-----命令写错了 汗
[db2inst1@localhost db2bak]$ db2 restore db incremental taken at 20111205194339
SQL0104N  An unexpected token "incremental" was found following "DB".  
Expected tokens may include:  "".  SQLSTATE=42601

-----恢复增量文件,最近一次的备份
[db2inst1@localhost db2bak]$ db2 restore db sample incremental taken at 20111205194339
DB20000I  The RESTORE DATABASE command completed successfully.

---使用db2自带命令,查看恢复步骤
[db2inst1@localhost db2bak]$ db2ckrst -d sample -t 20111205194339

Suggested restore order of images using timestamp 20111205194339 for
database sample.
====================================================================
 restore db sample incremental taken at 20111205194339
 restore db sample incremental taken at 20111205193902
 restore db sample incremental taken at 20111205194339
====================================================================
--------根据提示进行恢复
[db2inst1@localhost db2bak]$ db2 restore db sample incremental taken at 20111205194339
SQL2572N  Attempted an incremental restore of an out of order image.  The 
restore of tablespace "SYSCATSPACE" encountered an error because the backup 
image with timestamp "20111205193902" must be restored before the image that 
was just attempted.
[db2inst1@localhost db2bak]$ db2 restore db sample incremental taken at 20111205193902
DB20000I  The RESTORE DATABASE command completed successfully.
[db2inst1@localhost db2bak]$ db2 restore db sample incremental taken at 20111205194339
DB20000I  The RESTORE DATABASE command completed successfully.



-----使用日志回滚  
[db2inst1@localhost db2bak]$ db2 "rollforward db sample to end of logs overflow log path (/home/db2inst1/log)"

                                 Rollforward Status

 Input database alias                   = sample
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000010.LOG
 Log files processed                    = S0000007.LOG - S0000010.LOG
 Last committed transaction             = 2011-12-05-11.46.25.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.


----发现数据库还在回滚状态,原来上面的忘了加stop命令来停止回滚,⊙﹏⊙
[db2inst1@localhost db2bak]$ db2 connect to sample
SQL1117N  A connection to or activation of database "SAMPLE" cannot be made 
because of ROLL-FORWARD PENDING.  SQLSTATE=57019


---重新来
[db2inst1@localhost db2bak]$ db2 "rollforward db sample to end of logs and stop overflow log path (/home/db2inst1/log)"

                                 Rollforward Status

 Input database alias                   = sample
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000007.LOG - S0000010.LOG
 Last committed transaction             = 2011-12-05-11.46.25.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@localhost db2bak]$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUX 9.7.4
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

[db2inst1@localhost db2bak]$ 

----------关于归档日志丢失的问题,以前一直有疑问,如果归档日志完全丢失,我们能恢复到什么时间,一直以为没有日志就不能恢复,
          问了几个老同事,实验了几次,应该能恢复到上次全备,日志只是我们用来回滚的,我们的增量必须用日志回滚来恢复,如果日志
          丢失,我们增量就没有用处了。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25648398/viewspace-714428/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 数据库三范式
下一篇: REDO更改日志大小
请登录后发表评论 登录
全部评论

注册时间:2012-01-04

  • 博文量
    3
  • 访问量
    5880