分类: Linux操作系统

2014-08-20 14:18:22

         recover命令,是v8.2版本开始新增的一条命令,它综合了restorerollforward命令。但是,它只能针对数据库级别进行的,而在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.

附:

使用相同的时间,用restorerollforward命令,还原出来的效果却不理想:

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)。

阅读(2405) | 评论(0) | 转发(0) |
0

上一篇:没有了

下一篇:关于索引扫描的极速调优实战(第一篇)

给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册