db2 备份恢复 2

###########备份前准备#################################

************创建存放归档日志和备份的目录****************
[root@yan ~]# su - db2inst1
[db2inst1@yan ~]$ mkdir archivelog
[db2inst1@yan ~]$ mkdir log
[db2inst1@yan ~]$ mkdir backup   

 

************创建测试数据库和测试表***************************
db2 => create db yan    
db2 => connect to yan
db2 => create table test (id int,name varchar(20),password varchar(20))
db2 => insert into test values (1,'zhu','yan')

************修改db2参数**************************************
db2 => update db cfg for yan using mirrorlogpath /home/db2inst1/archivelog--启用mirror log

db2 => update db cfg for yan using userexit on    --启用用户出口

db2 => update db cfg for yan using logretain on    --启用归档日志

db2 => update db cfg for yan using trackmod on    --启用增量备份功能

db2 => backup db yan to /home/db2inst1/backup 这时数据库处于挂起状态，需要对数据库备份一次，否则不能用

Backup successful. The timestamp for this backup image is : 20090807082755

###############开始备份和还原###############################

 

***************冷备份和还原*********************************

db2 => backup db yan to /home/db2inst1/backup

Backup successful. The timestamp for this backup image is : 20090807083306

db2 => insert into test values (2,'hui','hui') 

[db2inst1@yan ~]$ cp archivelog/NODE0000/* log    --将归档日志拷贝走

db2 => drop db yan       --手动删除数据库，模拟灾备

db2 => restore db yan from /home/db2inst1/backup taken at 20090807083306--恢复数据库
DB20000I  The RESTORE DATABASE command completed successfully.

db2 => rollforward db yan to end of logs and complete overflow log path (/home/db2inst1/log)--前滚日志

                                 Rollforward Status

 Input database alias                   = yan
 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             = 2009-08-07-12.34.41.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

db2 => select *from test      --能查出test表中的所有数据证明成功了

 

 

*****************热备份和还原*********************************

db2 => insert into test values (3,'java','db')    --插入测试数据

db2 => backup db yan online to /home/db2inst1/backup   --热备

Backup successful. The timestamp for this backup image is : 20090807084334 

db2 => insert into test values (4,'db2','jsp')    --插入测试数据

[db2inst1@yan ~]$ cp -rf archivelog/NODE0000/* log   --拷贝日志

db2 => drop db yan       --手动删除数据库，模拟灾备
DB20000I  The DROP DATABASE command completed successfully.

db2 => restore db yan from /home/db2inst1/backup taken at 20090807084334--恢复数据库
DB20000I  The RESTORE DATABASE command completed successfully.

db2 => rollforward db yan to end of logs and complete overflow log path (/home/db2inst1/log)--前滚日志

                                 Rollforward Status

 Input database alias                   = yan
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000002.LOG - S0000002.LOG
 Last committed transaction             = 2009-08-07-12.43.35.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

db2 => select *from test      --能查出所有的测试数据证明成功了

 

 

***************增量备份和还原********************************

db2 => insert into test values (5,'oracle','mysql')   --插入测试数据

db2 => backup db yan online incremental to /home/db2inst1/backup --备份数据库

Backup successful. The timestamp for this backup image is : 20090807085501

[db2inst1@yan ~]$ cp -rf archivelog/NODE0000/* log   --拷贝日志

db2 => drop db yan       --手动删除数据库，模拟灾备
DB20000I  The DROP DATABASE command completed successfully.

db2 => restore db yan incremental automatic from /home/db2inst1/backup taken at 20090807085501
DB20000I  The RESTORE DATABASE command completed successfully.  --恢复数据库

db2 => rollforward db yan to end of logs and complete overflow log path (/home/db2inst1/log)

                                 Rollforward Status

 Input database alias                   = yan
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000004.LOG - S0000004.LOG
 Last committed transaction             = 2009-08-07-12.55.01.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

db2 => select *from test      --能查出所有的测试数据证明成功了