分类：
2008-09-12 22:08:43
【备份与恢复】
===========================================================
DB2 日志处理：
DB2日志是以文件的形式存放在文件系统中，分为两种模式：循环日志和归档日志。当创建新数据库时，日志的缺省模式是循环日志。在这种模式下，只能实现数据库的脱机备份和恢复。如果要实现联机备份和恢复，必须设为归档日志模式。
目前在综合业务系统中，设置的均是归档日志模式；其它系统（如事后监督、经营决策、中间业务等）一般都设置为循环日志模式。至于采用何种模式，可以通过修改数据库配置参数（LOGRETAIN）来实现：
归档日志模式：db2 update db cfg for using logretain on 注：改为on后，查看数据库配置参数logretain的值时，实际显示的是recovery。改变此参数后，再次连接数据库会显示数据库处于备份暂挂（BACKUP
PENDING）状态。这时，需要做一次对数据库的脱机备份（db2 backup db ），才能使数据库状态变为正常。
循环日志模式：db2 update db cfg for using logretain on
一、修改日志模式
目的：将日志修改成为归档日志，才能继续进行online备份和后面的所有试验。
保证AUTO_DB_BACKUP = ON才能做备份。
日志格式是LOGRETAIN YES
用户出口USEREXIT on
[db2inst1@fedora ~]$ db2 get db cfg for sample
|grep LOG
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Log buffer size (4KB) (LOGBUFSZ) = 8
Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
Changed path to log files (NEWLOGPATH) =
Path to log files =
/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by
transaction(MAX_LOG) = 0
Num. of active log files for 1
active UOW(NUM_LOG_SPAN) = 0
Log retain for recovery
enabled (LOGRETAIN) = OFF
First log archive method (LOGARCHMETH1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Log pages during index build (LOGINDEXBUILD) = OFF
[db2inst1@fedora ~]$
查看到
LOGRETAIN 选项为 OFF
Path to log files 选项为
/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
修改LOGRETAIN 选项为
ON
[db2inst1@fedora ~]$ db2 update db cfg for sample using
DB20000I The UPDATE DATABASE
CONFIGURATION command completed successfully.
[db2inst1@fedora ~]$ db2 connect to sample
SQL1116N A connection to or
activation of database "SAMPLE" cannot be made
because of BACKUP PENDING.
SQLSTATE=57019
唉，咋连上数据库啊，怎么回事，数据库必须备份一次后才能连接。
提示备份挂起状态，必须进行一次离线备份，下面进行离线不备份
【主意】在做在线备份的之前必须做一次离线备份。
[db2inst1@fedora ~]$ db2 backup db sample to /home/db2inst1/onlineback/
Backup successful. The timestamp for this backup image is : 20060528134320
ok，提示备份成功，完成了，认为它没有用可以把它删掉，可以继续进行我们以后的试验了!
[db2inst1@fedora ~]$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = db2inst1
Local database alias = SAMPLE
[db2inst1@fedora ~]$
【小结】
备份需要进行3步：
1、首先修改数据配置，把日志改成循环日志的方式，需要修改2处“LOGRETAIN YES”和“USEREXIT on”
2、重新启动数据库实例
3、进行第一次离线备份，才能连接数据库，以后才能做在线备份。使用目的命令为：
db2 backup db sample to /home/db2inst1/db2backup/
查看历史信息
[db2inst1@fedora ~]$ db2 list history all for
sample
Number of matching file entries = 1
Op Obj Timestamp+Sequence Type
Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- ---
------------ ------------ --------------
B
D 20060528134320001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE
OFFLINE
Start Time: 20060528134320
End Time: 20060528134339
Status: A
----------------------------------------------------------------------------
EID: 1 Location:
/home/db2inst1/onlineback
[db2inst1@fedora ~]$
显示备份的数据库、时间、类型：离线备份。
============================================================================================================
在线备份和恢复
============================================================================================================
步入正题，下面做在线备份
[db2inst1@fedora ~]$ db2 backup db sample online to
/home/db2inst1/onlineback/ include logs without prompting
db2 backup db
testdb to
/home/db2inst1/db2backup/ include logs
Backup successful. The timestamp for this backup image is : 20060528135607
[db2inst1@fedora ~]$ cd onlineback/
[db2inst1@fedora onlineback]$ pwd
/home/db2inst1/onlineback
[db2inst1@fedora onlineback]$ ll
total 34140
drwxr-xr-x 2 db2inst1 db2grp1 4096 May 28
-rw-r----- 1 db2inst1 db2grp1
34914304 May 28
[db2inst1@fedora onlineback]$
在线备份ok了，以系统提示的时间搓建立了一个备份文件。
联机备份时的信息在./sqllib/db2dump/db2diag.log里
[db2inst1@fedora ~]$ more
./sqllib/db2dump/db2diag.log
[db2inst1@fedora onlineback]$ db2 list history all for sample
List History
File for sample
Number of matching file entries = 2
Op Obj Timestamp+Sequence Type Dev
Earliest Log Current Log Backup ID
-- --- ------------------ ---- ---
------------ ------------ --------------
B
D 20060528135607001 N D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20060528135607
End Time: 20060528135635
Status: A
----------------------------------------------------------------------------
EID: 2 Location:
/home/db2inst1/onlineback
[db2inst1@fedora onlineback]$
开始恢复
目的：将备份好的数据文件恢复到testdb中
[db2inst1@fedora onlineback]$ ll
total 34140
drwxr-xr-x 2 db2inst1 db2grp1 4096 May 28
-rw-r----- 1 db2inst1 db2grp1
34914304 May 28
[db2inst1@fedora onlineback]$ db2 restore db sample taken at
20060528135607 into testdb logtarget /home/db2inst1/onlineback/logs/
DB20000I The RESTORE DATABASE
command completed successfully.
ok恢复成功，数据名为testdb，
[db2inst1@fedora onlineback]$ db2 connect to testdb
SQL1117N A connection to or
activation of database "TESTDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
但是提示restore后的数据库处于前滚暂挂状态.所以要进行前滚以解除该状态.
[db2inst1@fedora onlineback]$ cd logs/
回滚过程：
[db2inst1@fedora logs]$ db2 "rollforward db testdb to end of logs and
stop overflow log path(/home/db2inst1/onlineback/logs/)"
Rollforward
Status
Input database alias = testdb
Number of nodes have returned
status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG -
S0000000.LOG
Last committed transaction = 2006-05-28-05.56.24.000000
DB20000I The ROLLFORWARD command
completed successfully.
[db2inst1@fedora logs]$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = db2inst1
Local database alias = TESTDB
[db2inst1@fedora logs]$ db2 list tables
Table/View
Schema Type Creation time
------------------------------- --------------- -----
--------------------------
CL_SCHED
db2inst1 T 2006-05-28-13.27.48.012181
DEPARTMENT
db2inst1 T 2006-05-28-13.27.46.029916
EMPLOYEE
db2inst1 T 2006-05-28-13.27.46.064657
EMP_ACT
db2inst1 T 2006-05-28-13.27.46.275406
EMP_PHOTO
db2inst1 T 2006-05-28-13.27.46.623908
EMP_RESUME
db2inst1 T 2006-05-28-13.27.47.549226
IN_TRAY
db2inst1 T 2006-05-28-13.27.48.074607
ORG db2inst1 T
2006-05-28-13.27.45.505159
PROJECT
db2inst1 T 2006-05-28-13.27.46.522610
SALES
db2inst1 T 2006-05-28-13.27.47.853524
STAFF db2inst1 T
2006-05-28-13.27.45.767747
STAFFG
db2inst1 T 2006-05-28-13.27.45.915258
12 record(s) selected.
[db2inst1@fedora logs]$
ok在线备份，恢复成功！
备份与恢复的检验：
开2个窗口，1个用来使用sql语句向表test1中插入文件，1个用来进行备份，同时再个窗口将插入语句和备份语句写好，先执行备份，然后不停的再另一个窗口执行插入语句。
窗口1：插入记录
[db2inst1@fedora ~]$ db2 -vf sql/insertdb.sql
反复执行n遍，什么后看到备份日志些好了后什么是再停止。
[db2inst1@fedora sql]$ db2 "select count(*) from test1"
1
-----------
1500
1 record(s) selected.
[db2inst1@fedora sql]$
窗口2：备份
[db2inst1@fedora ~]$ db2 backup db testdb online to
/home/db2inst1/onlineback/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060528145104
[db2inst1@fedora ~]$ db2 backup db testdb online to
/home/db2inst1/onlineback/ include logs without prompting
SQL2001N The utility was
interrupted. The output data may be
incomplete.
[db2inst1@fedora ~]$ cd onlineback/
[db2inst1@fedora onlineback]$ ll
total 34140
drwxr-xr-x 2 db2inst1 db2grp1 4096 May 28
-rw-r----- 1 db2inst1 db2grp1
34914304 May 28
[db2inst1@fedora onlineback]$
开始恢复：
[db2inst1@fedora onlineback]$ ll
total 34140
drwxr-xr-x 2 db2inst1 db2grp1 4096 May 28
-rw-r----- 1 db2inst1 db2grp1
34914304 May 28
[db2inst1@fedora onlineback]$ db2 restore db testdb taken at
20060528145104 into testdb2 logtarget /home/db2inst1/onlineback/logs/
DB20000I The RESTORE DATABASE
command completed successfully.
[db2inst1@fedora onlineback]$ db2 connect to testdb2
SQL1117N A connection to or
activation of database "TESTDB2" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@fedora onlineback]$ db2 "rollforward db testdb2 to end of
logs and stop overflow log path(/home/db2inst1/onlineback/logs/)"
Rollforward
Status
Input database alias = testdb2
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-28-06.51.24.000000
DB20000I The ROLLFORWARD command
completed successfully.
[db2inst1@fedora onlineback]$
[db2inst1@fedora onlineback]$ db2 connect testdb2
SQL0104N An unexpected token
"testdb2" was found following "CONNECT".
Expected tokens may include:
"END-OF-STATEMENT".
SQLSTATE=42601
[db2inst1@fedora onlineback]$ db2 connect to testdb2
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = db2inst1
Local database alias = TESTDB2
[db2inst1@fedora onlineback]$ db2 list tables
Table/View
Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED
db2inst1 T 2006-05-28-13.27.48.012181
DEPARTMENT
db2inst1 T 2006-05-28-13.27.46.029916
EMPLOYEE
db2inst1 T 2006-05-28-13.27.46.064657
EMP_ACT
db2inst1 T 2006-05-28-13.27.46.275406
EMP_PHOTO
db2inst1 T 2006-05-28-13.27.46.623908
EMP_RESUME db2inst1 T
2006-05-28-13.27.47.549226
IN_TRAY
db2inst1 T 2006-05-28-13.27.48.074607
ORG
db2inst1 T 2006-05-28-13.27.45.505159
PROJECT db2inst1 T
2006-05-28-13.27.46.522610
SALES
db2inst1 T 2006-05-28-13.27.47.853524
STAFF
db2inst1 T 2006-05-28-13.27.45.767747
STAFFG
db2inst1 T 2006-05-28-13.27.45.915258
TEST1
db2inst1 T 2006-05-28-14.39.55.004536
13 record(s) selected.
[db2inst1@fedora onlineback]$ db2 "select count(*) from test1"
1
-----------
703
1 record(s) selected.
[db2inst1@fedora onlineback]$
======================================================================
增量备份和恢复
======================================================================
1、先做一次完全备份
[db2inst1@fedora ~]$ db2 backup db testdb online to
/home/db2inst1/incbackup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060528151826
[db2inst1@fedora ~]$ db2 backup db testdb online incremental to /home/db2inst1/incbackup/
include logs without prompting
SQL2426N The database has not been
configured to allow the incremental backup
operation. Reason code = "1".
[db2inst1@fedora ~]$ db2 ? SQL2426N
SQL2426N The database has not been configured to allow the
incremental backup
operation. Reason code =
"
Explanation:
Incremental backups are not enabled
for a table space until
after modification tracking has been activated for the database
and a non-incremental backup has been performed on the table
space.
Possible reason codes:
1. The configuration parameter
TRACKMOD has not been set for the
database.
2. The TRACKMOD configuration
parameter has been set but at
least one table space has not
had a non-incremental backup
taken since the TRACKMOD
parameter was set.
User Response:
The action is based on the reason
code as follows:
1. Activate modification tracking
for the database by setting
the TRACKMOD database
configuration parameter to on, then
perform a full database backup.
2. Perform a full backup of the
table space.
[db2inst1@fedora ~]$ db2 get db cfg for testdb |grep TRACKMOD
Track modified pages (TRACKMOD) = OFF
[db2inst1@fedora ~]$ db2 update db cfg for testdb using
DB20000I The UPDATE DATABASE
CONFIGURATION command completed successfully.
[db2inst1@fedora ~]$
[db2inst1@fedora ~]$ db2 backup db testdb online incremental to
/home/db2inst1/incbackup/ include logs without prompting
SQL2426N The database has not been
configured to allow the incremental backup
operation. Reason code = "2".
[db2inst1@fedora ~]$ db2stop force
SQL1064N DB2STOP processing was
successful.
[db2inst1@fedora ~]$ db2start
SQL1063N DB2START processing was
successful.
[db2inst1@fedora ~]$
[db2inst1@fedora ~]$ db2 backup db testdb online incremental to
/home/db2inst1/incbackup/ include logs without prompting
SQL2426N The database has not been
configured to allow the incremental backup
operation. Reason code = "2".
[db2inst1@fedora ~]$ db2 backup db testdb online incremental to
/home/db2inst1/incbackup/ include logs without prompting
[db2inst1@fedora ~]$ db2 disconnect sample
SQL0843N The server name does not
specify an existing connection.
SQLSTATE=08003
[db2inst1@fedora ~]$ db2 backup db testdb to .
Backup successful. The timestamp for this backup image is : 20060528152712
[db2inst1@fedora ~]$ rm
TESTDB.0.db2inst1.NODE0000.CATN0000.20060528152712.001
[db2inst1@fedora incbackup]$ db2 backup db testdb online to
/home/db2inst1/incbackup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060528153110
[db2inst1@fedora incbackup]$ ll
total 39012
drwxr-xr-x 2 db2inst1 db2grp1 4096 May 28
-rw-r----- 1 db2inst1 db2grp1
39899136 May 28
全备份文件生成了。
[db2inst1@fedora incbackup]$ db2 backup db testdb online incremental to
/home/db2inst1/incbackup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060528153237
另一个窗口插入记录后，备份的文件
[db2inst1@fedora incbackup]$ db2 backup db testdb online incremental to
/home/db2inst1/incbackup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060528153318
另一个窗口又插入记录后，备份的文件
[db2inst1@fedora incbackup]$ db2 backup db testdb online incremental to
/home/db2inst1/incbackup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060528154057
另一个窗口又插入记录后，备份的文件
[db2inst1@fedora incbackup]$ ll
total 73200
drwxr-xr-x 2 db2inst1 db2grp1 4096 May 28
-rw-r----- 1 db2inst1 db2grp1
39899136 May 28
-rw-r----- 1 db2inst1 db2grp1 9990144 May 28
-rw-r----- 1 db2inst1 db2grp1 9990144 May 28
-rw-r----- 1 db2inst1 db2grp1
14974976 May 28
[db2inst1@fedora incbackup]$
生成了4个备份文件，其中一个是全备份，另外3个是整理的备份
开始恢复
恢复到20060528153318时间点
[db2inst1@fedora incbackup]$ db2 restore db testdb incremental taken at
20060528153318 into testdb2 logtarget /home/db2inst1/incbackup/logs/ without
prompting
DB20000I The RESTORE DATABASE
command completed successfully.
[db2inst1@fedora incbackup]$ db2 restore db testdb incremental taken at
20060528153110 into testdb2 logtarget /home/db2inst1/incbackup/logs/ without
prompting
DB20000I The RESTORE DATABASE
command completed successfully.
[db2inst1@fedora incbackup]$ db2 restore db testdb incremental taken at
20060528153237 into testdb2 logtarget /home/db2inst1/incbackup/logs/ without
prompting
SQL2540W Restore is successful,
however a warning "2580" was encountered
during Database Restore while processing in No Interrupt mode.
[db2inst1@fedora incbackup]$ db2 restore db testdb incremental taken at
20060528153318 into testdb2 logtarget /home/db2inst1/incbackup/logs/ without
prompting
SQL2540W Restore is successful,
however a warning "2580" was encountered
during Database Restore while processing in No Interrupt mode.
[db2inst1@fedora incbackup]$
[db2inst1@fedora incbackup]$ db2 "rollforward db testdb2 to end of
logs and stop overflow log path(/home/db2inst1/incbackup/logs/)"
Rollforward
Status
Input database alias = testdb2
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 -
S0000007.LOG
Last committed
transaction =
2006-05-28-07.33.28.000000
DB20000I The ROLLFORWARD command
completed successfully.
[db2inst1@fedora incbackup]$ db2 connect to testdb2
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = db2inst1
Local database alias = TESTDB2
[db2inst1@fedora incbackup]$ db2 "select count(*) from test1"
1
-----------
2000
1 record(s) selected.
[db2inst1@fedora incbackup]$
####################################################################################
恢复到20060528154057时间点
[db2inst1@fedora incbackup]$ db2 restore db testdb incremental taken at
20060528154057 into testdb2 logtarget /home/db2inst1/incbackup/logs/ without
prompting
DB20000I The RESTORE DATABASE
command completed successfully.
[db2inst1@fedora incbackup]$ db2 restore db testdb incremental taken at
20060528153110 into testdb2 logtarget /home/db2inst1/incbackup/logs/ without
prompting
DB20000I The RESTORE DATABASE
command completed successfully.
[db2inst1@fedora incbackup]$ db2 restore db testdb incremental taken at
20060528153237 into testdb2 logtarget /home/db2inst1/incbackup/logs/ without
prompting
SQL2540W Restore is successful,
however a warning "2580" was encountered
during Database Restore while processing in No Interrupt mode.
[db2inst1@fedora incbackup]$ db2 restore db testdb incremental taken at
20060528153318 into testdb2 logtarget /home/db2inst1/incbackup/logs/ without
prompting
SQL2540W Restore is successful,
however a warning "2580" was encountered
during Database Restore while processing in No Interrupt mode.
[db2inst1@fedora incbackup]$ db2 restore db testdb incremental taken at
20060528154057 into testdb2 logtarget /home/db2inst1/incbackup/logs/ without
prompting
SQL2540W Restore is successful,
however a warning "2580" was encountered
during Database Restore while processing in No Interrupt mode.
回滚
[db2inst1@fedora incbackup]$ db2 "rollforward db testdb2 to end of
logs and stop overflow log path(/home/db2inst1/incbackup/logs/)"
Rollforward Status
Input database alias = testdb2
Number of nodes have returned
status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000008.LOG -
S0000008.LOG
Last committed transaction = 2006-05-28-07.41.09.000000
DB20000I The ROLLFORWARD command
completed successfully.
[db2inst1@fedora incbackup]$ db2 connect to testdb2
[db2inst1@fedora incbackup]$ db2 "select count(*) from test1"
1
-----------
2500
1 record(s) selected.
[db2inst1@fedora incbackup]$
