db2恢复误drop的表
--先备份数据库$ db2 backup db sample to /tmp
Backup successful. The timestamp for this backup image is : 20101117125927
--分别建立t1,t2两个表：
$ d "create table scott.t1(a int,b varchar(10))"
DB20000IThe SQL command completed successfully.
$ d "create table scott.t2(a int)"
DB20000IThe SQL command completed successfully.
$ d "insert into scott.t1 values (1,'aaa')"
DB20000IThe SQL command completed successfully.
$ d "insert into scott.t1 values (1,'aaa')"
DB20000IThe SQL command completed successfully.
$ d "commit"
DB20000IThe SQL command completed successfully.
$ d "insert into scott.t2 values (123)"
DB20000IThe SQL command completed successfully.
$ d "insert into scott.t2 values (111)"
DB20000IThe SQL command completed successfully.
$ d "insert into scott.t2 values (222)"
DB20000IThe SQL command completed successfully.
$ select count
$ d "select * from scott.t2"
A
-----------
123
111
222
3 record(s) selected.
--drop t2表
$ d "drop table scott.t2"
DB20000IThe SQL command completed successfully.
$ d "select * from scott.t2"
SQL0204N"SCOTT.T2" is an undefined name.SQLSTATE=42704
--恢复数据库
db2 => restore db sample from /tmp/ taken at 20101117125927 into sample
SQL2539WWarning!Restoring to an existing database that is the same as the
backup image database.The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000IThe RESTORE DATABASE command completed successfully.
--现在数据库处于ROLL-FORWARD PENDING状态
db2 => connect to sample
SQL1117NA connection to or activation of database "SAMPLE" cannot be made
because of ROLL-FORWARD PENDING.SQLSTATE=57019
--开始恢复
--首先查看drop表的历史
db2 => list history dropped table all for db sample
List History File for sample
Number of matching file entries = 2
Op Obj Timestamp+Sequence Type Dev Earliest Log Current LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
DT20101117134249 000000000000dc2500040004
----------------------------------------------------------------------------
"SCOTT "."T1" resides in 1 tablespace(s):
00001 TS1
----------------------------------------------------------------------------
Comment: DROP TABLE
Start Time: 20101117134249
End Time: 20101117134249
Status: A
----------------------------------------------------------------------------
EID: 26
DDL: CREATE TABLE "SCOTT "."T1" ( "A" INTEGER , "B" VARCHAR(10) )IN "TS1" ;
----------------------------------------------------------------------------
Op Obj Timestamp+Sequence Type Dev Earliest Log Current LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
DT20101117134550 000000000000ff2500040005
----------------------------------------------------------------------------
"SCOTT "."T2" resides in 1 tablespace(s):
00001 TS1
----------------------------------------------------------------------------
Comment: DROP TABLE
Start Time: 20101117134550
End Time: 20101117134550
Status: A
----------------------------------------------------------------------------
EID: 27
DDL: CREATE TABLE "SCOTT "."T2" ( "A" INTEGER )IN "TS1" ;
----------------------------------------------------------------------------
--roll forward数据库
db2 => rollforward db sample to end of logs and stop recover dropped table 000000000000ff2500040005 to /tmp/
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 = S0000000.LOG - S0000001.LOG
Last committed transaction = 2010-11-17-05.45.50.000000 UTC
DB20000IThe ROLLFORWARD command completed successfully.
--看看roll出来的数据
$ cat data
123
111
222
--现在t1已经回来，但是没有t2:
db2 => connect to sample
Database Connection Information
Database server = DB2/LINUX 9.5.2
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
db2 => select * from scott.t1
A B
----------- ----------
1 aaa
1 aaa
2 record(s) selected.
db2 => select * from scott.t2
SQL0204N"SCOTT.T2" is an undefined name.SQLSTATE=42704
--根据list history dropped table里面的ddl重建t2：
db2 => CREATE TABLE "SCOTT "."T2" ( "A" INTEGER )IN "TS1"
DB20000IThe SQL command completed successfully.
--开始倒回表的数据
db2 => import from /tmp/NODE0000/data of del insert into scott.t2
SQL3109NThe utility is beginning to load data from file
"/tmp/NODE0000/data".
SQL3110NThe utility has completed processing."3" rows were read from the
input file.
SQL3221W...Begin COMMIT WORK. Input Record Count = "3".
SQL3222W...COMMIT of any database changes was successful.
SQL3149N"3" rows were processed from the input file."3" rows were
successfully inserted into the table."0" rows were rejected.
Number of rows read = 3
Number of rows skipped = 0
Number of rows inserted = 3
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 3
--检查结果
db2 => select * from scott.t2
A
-----------
123
111
222
3 record(s) selected.
--ok
页:
[1]