[DB2]使用recover命令找回删除的表

         recover命令，是v8.2版本开始新增的一条命令，它综合了restore和rollforward命令。但是，它只能针对数据库级别进行的，而在oracle中，recover则可以针对表空间进行操作。

         1、首先，连上数据，确认有一张表newtable后，查看下时间戳

db2 => connect to sample

   Database Connection Information

 

 Database server        = DB2/LINUX 9.7.1

 SQL authorization ID   = DB2INST1

 Local database alias   = SAMPLE

db2 => select * from newtable

 

AA        

-----------

        123

        222

        333

        444

        555

 

  5 record(s) selected.

db2 => select current timestamp from sysibm.sysdummy1

1                        

--------------------------

2010-08-04-20.25.31.490261

 

  1 record(s) selected.

         2、执行删除表的操作：

db2 => drop table newtable

DB20000I  The SQL command completed successfully.

         3、使用recover命令来恢复到指定的时间点，执行前需要先commit：

db2 => recover database sample to 2010-08-04-20.25.31.490261 using local time

SQL1350N  The application is not in the correct state to process this request.

Reason code="1".

db2 => commit

DB20000I  The SQL command completed successfully.

db2 => recover database sample to 2010-08-04-20.25.31.490261 using local time

                                 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                    = S0000001.LOG - S0000003.LOG

 Last committed transaction             = 2010-08-04-20.05.33.000000 Local

DB20000I  The RECOVER DATABASE command completed successfully.

小结：可以看到使用recover命令恢复时，该命令会自动得去寻找

         4、最后，查询一下表中的数据，发现已经将删除的表找回来了：

db2 => connect to sample

   Database Connection Information

 Database server        = DB2/LINUX 9.7.1

 SQL authorization ID   = DB2INST1

 Local database alias   = SAMPLE

 

db2 => select * from newtable

 

AA        

-----------

        123

        222

        333

        444

        555

  5 record(s) selected.

附：

使用相同的时间，用restore和rollforward命令，还原出来的效果却不理想：

1、执行commit，紧接着执行restore恢复表空间（之前对表空间有备份，表中有数据存在）：

db2 => commit

DB20000I  The SQL command completed successfully.

db2 => restore database sample tablespace from /home/db2inst1/bak_sample taken at 20100804201914

DB20000I  The RESTORE DATABASE command completed successfully.

         2、将表空间前滚到指定的时间点时，出现错误提示。根据提示，修改命令：

db2 => rollforward db sample to 2010-08-04-20.25.31.490261 using local time and stop TABLESPACE (USERSPACE1)

SQL1275N  The stoptime passed to roll-forward must be greater than or equal to

"2010-08-04-20.25.58.000000 Local", because database "SAMPLE" on node(s) "0"

contains information later than the specified time.

db2 => rollforward db sample to 2010-08-04-20.25.58.000000 using local time and stop TABLESPACE ( USERSPACE1 )

 

                                 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-08-04-19.39.27.000000 Local

 

DB20000I  The ROLLFORWARD command completed successfully.

         3、连接上数据库，却发现被删除的表，依旧没有恢复回来：

db2 => connect to sample

 

   Database Connection Information

 

 Database server        = DB2/LINUX 9.7.1

 SQL authorization ID   = DB2INST1

 Local database alias   = SAMPLE

 

db2 => select * from newtable

SQL0204N  "DB2INST1.NEWTABLE" is an undefined name.  SQLSTATE=42704

 

总结：recover命令，能够将数据库恢复到指定的时间点，是一个非常方便的工具。但是该命令，不支持表空间级的不完全恢复，实在令人觉得有些遗憾。

注：在oracle中rman中的revocer命令，支持表空间级的不完全恢复（TSPITR）。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14600958/viewspace-670311/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14600958/viewspace-670311/