[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）。