DB2备份及恢复db2backup专题（基础）

最新推荐文章于 2023-10-12 09:03:29 发布

pianzif

最新推荐文章于 2023-10-12 09:03:29 发布

阅读量1.6w

点赞数 1

分类专栏： DB2@BK 文章标签： db2 db2backup

本文链接：https://blog.csdn.net/fuwencaho/article/details/24539741

版权

DB2@BK 专栏收录该内容

104 篇文章 4 订阅

订阅专栏

1：脱机备份db2backup（也叫版本恢复）

格式:db2 backup db <database name> [ to <dir name> ]

database name:表示数据库
to <dir name>:表示为备份到的目录路径,为可选项,默认在当前目录下

执行备份命令时,如果出现无法执行,则先断开数据库的连接再执行备份命令.
   我使用的免费版本的DB2数据库测试时,从上面可以看出没有断开也可以执行,但执行完,发现数据库
   处于断开状态了
如: db2 force application all
强制关闭实例上的所有应用程序

说明:上面的方法为脱机备份(也称为离线备份或者冷备份),此方法必须断开所有与数据库连接的应用后才能进行,备份时数据库不能提供给用户使用.

1.1: 恢复

格式:db2 restore db <database name> [ from <dir name> ]
database name:表示恢复的数据库名
from <dir name>:表示为从哪个目录路径下恢复,为可选项,默认在当前目录下

注:如果想把恢复的数据库更改为新的数据库名,则格式如下
db2 restore db <database name> [ from <dir name> into <new database name> ]
例:db2 restore db test from /home/db2inst1/Sunrier into testdb
或者db2 restore db test from "/home/db2inst1/Sunrier" into testdb

详见我的博客：http://blog.csdn.net/fuwencaho/article/details/20629567#t35

图示：

注意：脱机备份与恢复会缺少备份时间到故障时间的数据，不完全恢复

2：在线备份与恢复

2.1：备份

(8.4.1) Log retain for recovery enabled (LOGRETAIN) = OFF 状态只能脱机备份
更改参数后可以进行在线全备份

[wzm@fedora ~]$ db2 update db cfg using LOGRETAIN 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.

(8.4.2) 更改后的参数显示

wzm@fedora ~]$ db2 get db cfg | grep -i logretain
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
First log archive method (LOGARCHMETH1) = LOGRETAIN

(8.4.3)在更改完参数后。连不上数据库，必须进行一次脱机全备份才能连上数据库

[wzm@fedora ~]$ db2 connect to wzmdb
SQL1116N A connection to or activation of database "WZMDB" cannot be made
because of BACKUP PENDING. SQLSTATE=57019

(8.4.4)进行一次全备份 12:45

[wzm@fedora ~]$ db2 backup db wzmdb to /home/db2backup/
Backup successful. The timestamp for this backup image is : 20060521124511

(8.4.5)连接数据库成功

[wzm@fedora ~]$ db2 connect to wzmdb
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = WZM
Local database alias = WZMDB

(8.4.6) 进行在线全备份 12:49

[wzm@fedora db2backup]$ db2 backup db wzmdb online to /home/db2backup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060521124905

(8.4.7)备份恢复 13:03

[wzm@fedora db2backup]$ db2 restore db wzmdb taken at 20060521130338 into wzmdb_db logtarget /home/db2backup/logs/ without prompting
SQL2540W Restore is successful, however a warning "2580" was encountered
during Database Restore while processing in No Interrupt mode.

(8.4.8)连接数据库不成功，显示暂挂前滚状态。

[wzm@fedora db2backup]$ db2 connect to wzmdb_db
SQL1117N A connection to or activation of database "WZMDB_DB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019

(8.4.9)日志的恢复

[wzm@fedora db2backup]$ db2 "rollforward db wzmdb_db to end of logs and stop overflow log path (/home/db2backup/logs)"
Rollforward Status
Input database alias = wzmdb_db
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000001.LOG - S0000001.LOG
Last committed transaction = 2006-05-21-05.03.53.000000
DB20000I The ROLLFORWARD command completed successfully.

(8.9.10)数据库的连接成功

[wzm@fedora db2backup]$ db2 connect to wzmdb_db
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = WZM
Local database alias = WZMDB_DB

2.2：增量

(8.5)增量备份(两种)说明，在进行增量备份是第一次要全备份，接着才可以进行增量备份
增量备份需要该的参数(trackmod)

[wzm@fedora logs]$ db2 get db cfg for wzmdb | grep -i trackmod
Track modified pages (TRACKMOD) = OFF
[wzm@fedora logs]$ db2 update db cfg for wzmdb using TRACKMOD ON
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.

(8.5.1)全备份

[wzm@fedora db2backup]$ db2 backup db wzmdb online to /home/db2backup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060521140150

(8.5.2)增量备份

[wzm@fedora db2backup]$ db2 backup db wzmdb online incremental to /home/db2backup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060521140301

(8.5.3)备份的恢复(先指明恢复的时间点)

[wzm@fedora db2backup]$ db2 restore db wzmdb incremental taken at 20060521140301 into wzmddd logtarget /home/db2backup/logs
DB20000I The RESTORE DATABASE command completed successfully.

(8.5.4)进行一次恢复(恢复到全备份的时间点的内容)

[wzm@fedora db2backup]$ db2 restore db wzmdb incremental taken at 20060521140150 into wzmddd logtarget /home/db2backup/logs
DB20000I The RESTORE DATABASE command completed successfully.

(8.5.5)恢复到所指定的时间点的内容

[wzm@fedora db2backup]$ db2 restore db wzmdb incremental taken at 20060521140301 into wzmddd logtarget /home/db2backup/logs
SQL2580W Warning! Restoring logs to a path which contains existing log files. Attempting to overwrite an existing log file during restore will cause the restore operation to fail.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.

(8.5.6)进行前滚恢复

[wzm@fedora db2backup]$ db2 "rollforward db wzmddd to end of logs and stop overflow log path (home/db2backup/logs)"
Rollforward Status
Input database alias = wzmddd
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000006.LOG - S0000006.LOG
Last committed transaction = 2006-05-21-06.03.12.000000
DB20000I The ROLLFORWARD command completed successfully.

(8.5.7)db2连接

[wzm@fedora db2backup]$ db2 connect to wzmddd
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = WZM
Local database alias = WZMDDD

图示：