DB2在线备份恢复步骤和原理

DB2在线备份恢复步骤和原理。
在在线备份不影响正常的事务，但是Load,trancate table之类的锁表的应用会影响，会有锁等，一直等待到备份结束。
最核心的原理是在备份期间，会把期间产生的Log打包到备份介质中然后前滚。DB2数据库的每一个page页都会记录最新的LSN号，当前滚时，如果发现一个事务的LSN小于page页的LSN，说明在备份期间，这个page页已经更新，就会跳过这个事务，如果一个事务的LSN大于于page页的LSN，则会重做这个Log,也就是redo

在线备份会报错，是因为没有开启归档日志
[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ db2 backup db test online
SQL2413N  Online backup is not allowed because the database is not recoverable 
or a backup pending condition is in effect.
[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ ls
TEST.0.db2inst1.DBPART000.20230823103323.001
[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ cd ..

[db2inst1@t3-dtpoc-dtpoc-web04 db2arclogs]$ db2 get db cfg for test |grep -i first
 First active log file                                   = 
 First log archive method                 (LOGARCHMETH1) = OFF

开启归档日志并重启数据库生效：
[db2inst1@t3-dtpoc-dtpoc-web04 db2arclogs]$ db2 update db cfg for test using LOGARCHMETH1 DISK://home/db2inst1/liys/db2arclogs
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 db2arclogs]$ db2stop 
08/23/2023 10:53:44     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@t3-dtpoc-dtpoc-web04 db2arclogs]$ db2start
08/23/2023 10:53:48     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

在线备份还是报错，是因为开启归档日志后，数据库是backup pending的状态，离线备份一次后即可在线备份
[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ db2 backup db test online
SQL2413N  Online backup is not allowed because the database is not recoverable 
or a backup pending condition is in effect.
[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ db2 backup db test 

Backup successful. The timestamp for this backup image is : 20230823105451

[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ db2 backup db test online

Backup successful. The timestamp for this backup image is : 20230823105457

[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ ls 
TEST.0.db2inst1.DBPART000.20230823103323.001  TEST.0.db2inst1.DBPART000.20230823105451.001  TEST.0.db2inst1.DBPART000.20230823105457.001
[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ 

include logs的作用是备份结束后把期间产生的log都打包到备份介质中
[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ db2 backup db test online include logs

Backup successful. The timestamp for this backup image is : 20230823105907

[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ db2 restore db test from /home/db2inst1/liys/backup_images taken at 20230823105907 on  /testdata/db2inst1 dbpath on /home/db2inst1 logtarget /home/db2inst1/liys/backup_images/logtarget
SQL2539W  The specified name of the backup image to restore is the same as the 
name of the target database.  Restoring to an existing database that is the 
same as the backup image database will cause the current database to be 
overwritten by the backup version.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

连接数据库报错，是因为在线备份必须前滚log
[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ db2 connect to test
SQL1117N  A connection to or activation of database "TEST" cannot be made 
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

这里就用到了resstore命令中的logtarget /home/db2inst1/liys/backup_images/logtarget，是把打包的Log解压到该目录下
[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ db2 "rollforward db test to end of logs and complete overflow log path ('/home/db2inst1/liys/backup_images/logtarget')"

                                 Rollforward Status

 Input database alias                   = test
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000002.LOG - S0000002.LOG
 Last committed transaction             = 2023-08-23-02.59.09.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ 

[db2inst1@t3-dtpoc-dtpoc-web04 backup_images]$ db2 connect to test

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.11
 SQL authorization ID   = DB2INST1
 Local database alias   = TEST