db2恢复误drop的表


--先备份数据库
[db2inst1@server1 ~]$ db2 backup db sample to /tmp

Backup successful. The timestamp for this backup image is : 20101117125927

--分别建立t1,t2两个表:
[db2inst1@server1 ~]$ d "create table scott.t1(a int,b varchar(10))"
DB20000I The SQL command completed successfully.
[db2inst1@server1 ~]$ d "create table scott.t2(a int)"
DB20000I The SQL command completed successfully.
[db2inst1@server1 ~]$ d "insert into scott.t1 values (1,'aaa')"
DB20000I The SQL command completed successfully.
[db2inst1@server1 ~]$ d "insert into scott.t1 values (1,'aaa')"
DB20000I The SQL command completed successfully.
[db2inst1@server1 ~]$ d "commit"
DB20000I The SQL command completed successfully.

[db2inst1@server1 ~]$ d "insert into scott.t2 values (123)"
DB20000I The SQL command completed successfully.
[db2inst1@server1 ~]$ d "insert into scott.t2 values (111)"
DB20000I The SQL command completed successfully.
[db2inst1@server1 ~]$ d "insert into scott.t2 values (222)"
DB20000I The SQL command completed successfully.
[db2inst1@server1 ~]$ select count
[db2inst1@server1 ~]$ d "select * from scott.t2"

A
-----------
123
111
222

3 record(s) selected.

--drop t2表
[db2inst1@server1 ~]$ d "drop table scott.t2"
DB20000I The SQL command completed successfully.
[db2inst1@server1 ~]$ 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
SQL2539W Warning! 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
DB20000I The RESTORE DATABASE command completed successfully.

--现在数据库处于ROLL-FORWARD PENDING状态
db2 => connect to sample
SQL1117N A 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 Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
D T 20101117134249 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 Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
D T 20101117134550 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

DB20000I The ROLLFORWARD command completed successfully.


--看看roll出来的数据
[db2inst1@server1 /tmp/NODE0000]$ 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"
DB20000I The SQL command completed successfully.

--开始倒回表的数据
db2 => import from /tmp/NODE0000/data of del insert into scott.t2
SQL3109N The utility is beginning to load data from file
"/tmp/NODE0000/data".

SQL3110N The 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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值