实战演练
<div class="resizeimg4">88504018喜欢 db2 java的朋友进来了前滚恢复误删除表
1.归档日志的设置
参照实战演练-3
2.对表空间设置删除表恢复
CMD: db2 alter tablespace userspace1 dropped table recovery on
3.备份
CMD: db2 backup database sample online to /mnt/hgfs/F/backup/
Backup successful. The timestamp for this backup image is : 20100627180045
4.表的创建，数据插入和表删除
CMD: db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.7.1
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
CMD: db2 "CREATE TABLE TEST_DROPTABLE (COL1 INTEGER) IN USERSPACE1"
DB20000I The SQL command completed successfully.
CMD: db2 "INSERT INTO TEST_DROPTABLE VALUES(1),(2),(3),(4),(5)"
DB20000I The SQL command completed successfully.
CMD: db2 "SELECT * FROM TEST_DROPTABLE"
COL1
-----------
1
2
3
4
5
5 record(s) selected.
CMD: db2 "DROP TABLE TEST_DROPTABLE"
DB20000I The SQL command completed successfully.
5.从删除表历史里找到表的DDL
CMD: db2 list history dropped table all for sample
List History File for sample
Number of matching file entries = 1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
D T 20100627180224 000000000100477f00020013
----------------------------------------------------------------------------
"DB2INST1"."TEST_DROPTABLE" resides in 1 tablespace(s):
00001 USERSPACE1
----------------------------------------------------------------------------
Comment: DROP TABLE
Start Time: 20100627180224
End Time: 20100627180224
Status: A
----------------------------------------------------------------------------
EID: 31
DDL: CREATE TABLE "DB2INST1"."TEST_DROPTABLE" ( "COL1" INTEGER ) IN "USERSPACE1" ;
----------------------------------------------------------------------------
6.表的恢复
CMD: db2 "restore db sample tablespace(userspace1) from /mnt/hgfs/F/backup/"
DB20000I The RESTORE DATABASE command completed successfully.
7.前滚恢复的同时导出被删除的表数据，注意需要提供表ID（可以在删除表历史记录里获取）
CMD: db2 "rollforward db sample to end of logs and stop tablespace(userspace1) recover dropped table 000000000100477f00020013 to /mnt/hgfs/F/TESTDATA/"
Rollforward Status
Input database alias = sample
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = -
Last committed transaction = 2010-06-27-17.46.44.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
8.利用删除表历史中的DDL重建表。
CMD: db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.7.1
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
CMD: db2 'CREATE TABLE "DB2INST1"."TEST_DROPTABLE" ("COL1" INTEGER) IN "USERSPACE1"'
DB20000I The SQL command completed successfully.
9.IMPORT前滚恢复中导出的数据
CMD: db2 "import from /mnt/hgfs/F/TESTDATA/NODE0000/data of del insert into TEST_DROPTABLE"
SQL3109N The utility is beginning to load data from file
"/mnt/hgfs/F/TESTDATA/NODE0000/data".
SQL3110N The utility has completed processing. "5" rows were read from theinput file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "5".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "5" rows were processed from the input file. "5" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 5
Number of rows skipped = 0
Number of rows inserted = 5
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 5
10.结果确认。误删除表的数据找回来了！
CMD: db2 "select * from TEST_DROPTABLE"
COL1
-----------
1
2
3
4
5
5 record(s) selected.
页:
[1]