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

另一个窗口插入记录后,备份的文件

相关主题
相关文档
最新文档