DB2备份和恢复的一些练习

huyuhui001

##########################################################
#  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"

zzjijun

顶一下!

johndoe

ding

xnaddd

俺也ding一下，增量备份很麻烦的