===========================================================
【备份与恢复】
===========================================================
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 usingLOGRETAINON
DB20000IThe UPDATE DATABASE
CONFIGURATION command completed successfully.
[db2inst1@fedora ~]$ db2 connect to sample
SQL1116NA 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 LogBackup ID
-- --- ------------------ ---- ---
------------ ------------ --------------
BD20060528134320001FDS0000000.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
testdbto
/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-x2 db2inst1 db2grp14096 May 2813:47logs
-rw-r-----1 db2inst1 db2grp1
34914304 May 2813:56SAMPLE.0.db2inst1.NODE0000.CATN0000.20060528135607.001
[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 LogBackup ID
-- --- ------------------ ---- ---
------------ ------------ --------------
BD20060528135607001NDS0000000.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-x2 db2inst1 db2grp14096 May 2814:32logs
-rw-r-----1 db2inst1 db2grp1
34914304 May 2813:56SAMPLE.0.db2inst1.NODE0000.CATN0000.20060528135607.001
[db2inst1@fedora onlineback]$ db2 restore db sample taken at
20060528135607 into testdb logtarget /home/db2inst1/onlineback/logs/
DB20000IThe RESTORE DATABASE
command completed successfully.
ok恢复成功,数据名为testdb,
[db2inst1@fedora onlineback]$ db2 connect to testdb
SQL1117NA 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
DB20000IThe 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/ViewSchemaTypeCreation time
------------------------------- --------------- -----
--------------------------
CL_SCHEDdb2inst1T2006-05-28-13.27.48.012181
DEPARTMENTdb2inst1T2006-05-28-13.27.46.029916
EMPLOYEEdb2inst1T2006-05-28-13.27.46.064657
EMP_ACTdb2inst1T2006-05-28-13.27.46.275406
EMP_PHOTOdb2inst1T2006-05-28-13.27.46.623908
EMP_RESUMEdb2inst1T2006-05-28-13.27.47.549226
IN_TRAYdb2inst1T2006-05-28-13.27.48.074607
ORGdb2inst1T2006-05-28-13.27.45.505159
PROJECTdb2inst1T2006-05-28-13.27.46.522610
SALESdb2inst1T2006-05-28-13.27.47.853524
STAFFdb2inst1T2006-05-28-13.27.45.767747
STAFFGdb2inst1T2006-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
SQL2001NThe utility was
interrupted.The output data may be
incomplete.
[db2inst1@fedora ~]$ cd onlineback/
[db2inst1@fedora onlineback]$ ll
total 34140
drwxr-xr-x2 db2inst1 db2grp14096 May 2814:47logs
-rw-r-----1 db2inst1 db2grp1
34914304 May 2814:51TESTDB.0.db2inst1.NODE0000.CATN0000.20060528145104.001
[db2inst1@fedora onlineback]$
开始恢复:
[db2inst1@fedora onlineback]$ ll
total 34140
drwxr-xr-x2 db2inst1 db2grp14096 May 2814:47logs
-rw-r-----1 db2inst1 db2grp1
34914304 May 2814:51TESTDB.0.db2inst1.NODE0000.CATN0000.20060528145104.001
[db2inst1@fedora onlineback]$ db2 restore db testdb taken at
20060528145104 into testdb2 logtarget /home/db2inst1/onlineback/logs/
DB20000IThe RESTORE DATABASE
command completed successfully.
[db2inst1@fedora onlineback]$ db2 connect to testdb2
SQL1117NA 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
DB20000IThe ROLLFORWARD command
completed successfully.
[db2inst1@fedora onlineback]$
[db2inst1@fedora onlineback]$ db2 connect testdb2
SQL0104NAn 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/ViewSchemaTypeCreation time
------------------------------- --------------- ----- --------------------------
CL_SCHEDdb2inst1T2006-05-28-13.27.48.012181
DEPARTMENTdb2inst1T2006-05-28-13.27.46.029916
EMPLOYEEdb2inst1T2006-05-28-13.27.46.064657
EMP_ACTdb2inst1T2006-05-28-13.27.46.275406
EMP_PHOTOdb2inst1T2006-05-28-13.27.46.623908
EMP_RESUMEdb2inst1T2006-05-28-13.27.47.549226
IN_TRAYdb2inst1T2006-05-28-13.27.48.074607
ORGdb2inst1T2006-05-28-13.27.45.505159
PROJECTdb2inst1T2006-05-28-13.27.46.522610
SALESdb2inst1T2006-05-28-13.27.47.853524
STAFFdb2inst1T2006-05-28-13.27.45.767747
STAFFGdb2inst1T2006-05-28-13.27.45.915258
TEST1db2inst1T2006-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
SQL2426NThe 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 usingTRACKMODON
DB20000IThe UPDATE DATABASE
CONFIGURATION command completed successfully.
[db2inst1@fedora ~]$
[db2inst1@fedora ~]$ db2 backup db testdb online incremental to
/home/db2inst1/incbackup/ include logs without prompting
SQL2426NThe database has not been
configured to allow the incremental backup
operation. Reason code = "2".
[db2inst1@fedora ~]$ db2stop force
05/28/200615:25:0800SQL1064NDB2STOP processing was successful.
SQL1064NDB2STOP processing was
successful.
[db2inst1@fedora ~]$ db2start
05/28/200615:25:2200SQL1063NDB2START processing was successful.
SQL1063NDB2START processing was
successful.
[db2inst1@fedora ~]$
[db2inst1@fedora ~]$ db2 backup db testdb online incremental to
/home/db2inst1/incbackup/ include logs without prompting
SQL2426NThe 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
SQL0843NThe 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-x2 db2inst1 db2grp14096 May 2815:14logs
-rw-r-----1 db2inst1 db2grp1
39899136 May 2815:31TESTDB.0.db2inst1.NODE0000.CATN0000.20060528153110.001
全备份文件生成了。
[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-x2 db2inst1 db2grp14096 May 2815:14logs
-rw-r-----1 db2inst1 db2grp1
39899136 May 2815:31TESTDB.0.db2inst1.NODE0000.CATN0000.20060528153110.001
-rw-r-----1 db2inst1 db2grp19990144 May 2815:32TESTDB.0.db2inst1.NODE0000.CATN0000.20060528153237.001
-rw-r-----1 db2inst1 db2grp19990144 May 2815:33TESTDB.0.db2inst1.NODE0000.CATN0000.20060528153318.001
-rw-r-----1 db2inst1 db2grp1
14974976 May 2815:41TESTDB.0.db2inst1.NODE0000.CATN0000.20060528154057.001
[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
DB20000IThe 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
DB20000IThe 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
SQL2540WRestore 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
SQL2540WRestore 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
DB20000IThe 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
DB20000IThe 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
DB20000IThe 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
SQL2540WRestore 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
SQL2540WRestore 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
SQL2540WRestore 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
DB20000IThe 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]$