DB2 备份 以及 各个参数
DB2 备份 以及 各个参数 (一)
DB2日志是以文件的形式存放在文件系统中,分为两种模式:循环日志和归档日志。当创建新数据库时,日志的缺省模式是循环日志。在这种模式下,只能实现数据库的脱机备份和恢复。如果要实现联机备份和恢复,必须设为归档日志模式。
在 DB2 UDB 中,脱机备份也是最简单的备份。脱机备份要求采取完全数据库备份,显然,在备份的过程中,数据库是脱机的。换言之,当执行脱机备份时,用户无法访问数据库。
如果您正在使用循环日志记录,脱机备份是惟一受支持的备份类型。当首先创建一个数据库的时候,这是默认的日志记录方法。对于循环日志记录,log retain for recovery status 和 user exit for logging status 都被设为 NO。LOGRETAIN 和 USEREXIT 两个参数都被设为 OFF。
Log retain for recovery status = NO
User exit for logging status = YES
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
目前在综合业务系统中,设置的均是归档日志模式;其它系统(如事后监督、经营决策、中间业务等)一般都设置为循环日志模式。至于采用何种模式,可以通过修改数据库配置参数(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 act
ive 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
DB2 备份 以及 各个参数 (二)
(2009-08-25 19:16:26)
转载▼
标签:
db2
sample
to
for
log
it
分类: DB2
[db2inst1@fedora ~]$ db2 update db cfg for sample using LOGRETAIN ON
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 ~]$
显示备份的数据库、时间、类型:离线备份。
============================================================================================================
在线备份和恢复
===============================================================
DB2 备份 以及 各个参数(三)
(2009-08-25 19:18:08)
转载▼
标签:
db2
to
sample
log
of
it
分类: DB2
步入正题,下面做在线备份
[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 13:47 logs
-rw-r----- 1 db2inst1 db2grp1 34914304 May 28 13:56 SAMPLE.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
开始恢复
目的:将备份好的数据文件恢复到testdb中
[db2inst1@fedora onlineback]$ ll
total 34140
drwxr-xr-x 2 db2inst1 db2grp1 4096 May 28 14:32 logs
-rw-r----- 1 db2inst1 db2grp1 34914304 May 28 13:56 SAMPLE.0.db2inst1.NODE0000.CATN0000.20060528135607.001
[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
ok在线备份,恢复成功!
DB2 备份 以及 各个参数(四)
[db2inst1@fedora onlineback]$ db2 "select count(*) from test1"
备份和恢复
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:
[db2inst1@fedora ~]$ db2 get db cfg for testdb |grep TRACKMOD
Track modified pages (TRACKMOD) = OFF
[db2inst1@fedora ~]$ db2 update db cfg for testdb using TRACKMOD ON
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
05/28/2006 15:25:08 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@fedora ~]$ db2start
05/28/2006 15:25:22 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[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 cod
e = "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 15:14 logs
-rw-r----- 1 db2inst1 db2grp1 39899136 May 28 15:31 TESTDB.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
另一个窗口插入记录后,备份的文件