linux db2备份,DB2学习过程--备份与恢复（一）

最新推荐文章于 2021-09-13 08:51:49 发布

yoreua

最新推荐文章于 2021-09-13 08:51:49 发布

阅读量861

点赞数

文章标签： linux db2备份

===========================================================

【备份与恢复】

===========================================================

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"

-----------

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"

-----------

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"

-----------

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"

-----------

2500

1 record(s) selected.

[db2inst1@fedora incbackup]$

yoreua

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
linux db2备份,DB2学习过程--备份与恢复（一）

===========================================================【备份与恢复】===========================================================DB2 日志处理：DB2日志是以文件的形式存放在文件系统中，分为两种模式：循环日志和归档日志。当创建新数据库时，日志的缺省模式是循环日志。在这种模式下...
复制链接

扫一扫