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"