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/