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