DB2备份和恢复的一些练习

DB2备份和恢复的一些练习
##########################################################
# Prepare
##########################################################
1.Create database named 'TESTDB' ,owned by db2inst (instance user).
file : db2_script_00_create_db.sql
2.Create DMS tablespace named 'userspace1' and 'indexspace1'.
file : db2_script_01_create_tbs.sql
3.Create table named 'TEST01' and insert into some records.
file : db2_script_02_init_data.sql
4.Change parameters
file : db2_script_03_change_parameters.sql
5.Offline Full backup
file : db2_script_04_offline_fullbackup.sql
image file name : TESTDB.0.db2inst.NODE0000.CATN0000.20050506130109.001


##########################################################
# TEST 1 : Online restore database or tablespace
##########################################################
1.Insert into new record (time :2005-05-06-13.01.59)
db2 "insert into test01 (rid,username) values (100,'New 01')"
db2 commit

2.Copy log file to new place
cp /opt/db2data/testdb/db2inst_log/NODE0000/*.* /opt/db2data/testdb/db2inst_log_bak

3.Drop database
db2 terminate
db2 "drop database TESTDB"

4.Restore database from image file 'TESTDB.0.db2inst.NODE0000.CATN0000.20050506130109.001'
. ./db2_script_00_create_db.sql
db2 "restore database TESTDB from '/opt/db2data/testdb/' taken at 20050506130109 into TESTDB replace existing"

5.Copy log file for rolling forward
cp /opt/db2data/testdb/db2inst_log_bak/*.* /opt/db2data/testdb/db2inst_log/NODE0000/

6.Rolling forward database
db2 "rollforward db TESTDB to end of logs and stop"
as same as above command
db2 "rollforward db TESTDB to 2005-05-06-13.02.00.0000 using local time and stop"

7.Insert into new record (time :2005-05-06-13.47.00)
db2 "insert into test01 (rid,username) values (200,'New 02')"
db2 commit

8.Backup database increment (The timestamp for this backup image is :20050506135153)
db2 "backup database TESTDB incremental to '/opt/db2data/testdb/' with 2 buffers buffer 1024 parallelism 2"

9.Restore database in incremantal mode
cp /opt/db2data/testdb/db2inst_log/NODE0000/*.* /opt/db2data/testdb/db2inst_log_bak
db2 terminate
db2 "drop database TESTDB"
. ./db2_script_00_create_db.sql
db2 "restore database TESTDB incremental from '/opt/db2data/testdb/' taken at 20050506135153"
db2 "restore database TESTDB incremental from '/opt/db2data/testdb/' taken at 20050506130109"
db2 "restore database TESTDB incremental from '/opt/db2data/testdb/' taken at 20050506135153"
cp /opt/db2data/testdb/db2inst_log_bak/*.* /opt/db2data/testdb/db2inst_log/NODE0000/
db2 "rollforward db TESTDB to end of logs and stop"

10.Insert into new records(time :2005-05-06-14.10.00)
db2 "insert into test01 (rid,username) values (1000,'New 10')"
db2 commit

11.Drop table test01(time :2005-05-06-14.12.00)
db2 "drop table test01"

12.Restore database in order to recovery table test01
cp /opt/db2data/testdb/db2inst_log/NODE0000/*.* /opt/db2data/testdb/db2inst_log_bak
db2 terminate
db2 "restore database TESTDB incremental from '/opt/db2data/testdb/' taken at 20050506135153"
db2 "restore database TESTDB incremental from '/opt/db2data/testdb/' taken at 20050506130109"
db2 "restore database TESTDB incremental from '/opt/db2data/testdb/' taken at 20050506135153"
cp /opt/db2data/testdb/db2inst_log_bak/*.* /opt/db2data/testdb/db2inst_log/NODE0000/
db2 "rollforward db TESTDB to 2005-05-06-14.11.00.0000 using local time and stop"
if rollforward to end of logs , we can not recovery the table test01 .


##########################################################
# TEST 2 : Restore table using 'RECOVER DROPPED TABLE'
##########################################################
1.Status of database as same as the end of step 12 of TEST 1.
2.Restore table TEST01
db2 "drop table test01" (time : 2005-05-15-21.57.00)
db2 "list history dropped table since 20050507000000 for TESTDB"
(Get the droped time : 20050516215739 and DroppedTableID :000000000000fb0000020004)
( DDL: CREATE TABLE "DB2INST "."TEST01" ( "RID" INTEGER NOT NULL , "USERNAME" VARCHAR(10) ) IN "USERSPACE1" INDEX IN "INDEXSPACE1"
db2 "create table TEST02 (rid int ,username varchar(10))"
db2 "insert into TEST02 (rid ,username) values (100,'Tom 01')
cp /opt/db2data/testdb/db2inst_log/NODE0000/*.* /opt/db2data/testdb/db2inst_log_bak
db2 "restore database TESTDB tablespace(userspace1) from '/opt/db2data/testdb/' taken at 20050506130109 into TESTDB replace existing"
db2 "rollforward db TESTDB to end of logs and stop tablespace (userspace1) recover dropped table 000000000000fb0000020004 to '/opt/sharedoc'"
The data of TEST01 were exported to file named data ,which location is /opt/sharedoc/NODE0000/
db2 "CREATE TABLE DB2INST.TEST01 ( RID INTEGER NOT NULL , USERNAME VARCHAR(10) ) IN USERSPACE1 INDEX IN INDEXSPACE1 "
db2 "import from /opt/sharedoc/NODE0000/data of del insert into TEST01"