探索ORACLE不完全恢複之--基于時間恢複
作者:吳偉龍 Name:Prodence Woo
QQ:286507175 msn:[email protected]
基于時間(time)恢複
基于時間的恢複将資料庫恢複到備份點與失敗點之間的某個時間點。基于時間的恢複不僅在媒體失敗的時候使用,也可以在資料庫正常運作的時候使用。例如:某個使用者誤删除了某個表的資料,這個時候我們可以通過基于時間的恢複來将删除的資料恢複出來,示例如下:
1、檢視目前使用者下的表,隻有一張WWL001
21:07:31 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -----------------
WWL001 TABLE
Elapsed: 00:00:00.06
我們通過WWL001來建立WWL002-WWL005 共四張表用來測試不完全恢複
21:08:28 SQL> create table wwl002 asselect * from wwl001;
Table created.
Elapsed: 00:00:00.17
21:08:55 SQL> create table wwl003 asselect * from wwl001;
Elapsed: 00:00:00.04
21:09:00 SQL> create table wwl004 asselect * from wwl001;
Elapsed: 00:00:00.03
21:09:06 SQL> create table wwl005 asselect * from wwl001;
Elapsed: 00:00:00.05
如上表已經建立完成
2、我們在21:13:13開始删除表(請注意時間)
21:13:13 SQL> drop table wwl002 purge;
Table dropped.
Elapsed: 00:00:00.16
21:13:28 SQL> drop table wwl003 purge;
Elapsed: 00:00:00.11
21:13:34 SQL> truncate table wwl004;
Table truncated.
Elapsed: 00:00:00.32
21:13:44 SQL> truncate table wwl005;
Elapsed: 00:00:00.07
21:13:47 SQL>
因為我們是要做基于時間的恢複,那麼我們隻有将資料庫恢複到21:13:13之前的這個時間段,才能把我們剛才建立的表找回來。
3、開始執行恢複,先将資料庫啟動到mount狀态
21:14:40 SQL> conn / as sysdba
Connected.
21:14:44 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
21:15:20 SQL> startup mount;
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
21:15:46 SQL> e
4、開始執行restore到備份資料庫的目前狀态:
RMAN> restore database;
Starting restore at 12-JUL-12
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
channel ORA_DISK_1: starting datafilebackupset restore
channel ORA_DISK_1: specifying datafile(s)to restore from backup set
restoring datafile 00001 to/DBBak2/oradata/WWL/system01.dbf
restoring datafile 00002 to/DBBak2/oradata/WWL/undotbs01.dbf
restoring datafile 00003 to/DBBak2/oradata/WWL/sysaux01.dbf
restoring datafile 00004 to/DBBak2/oradata/WWL/users01.dbf
restoring datafile 00005 to/DBBak2/oradata/WWL/wwl01.dbf
restoring datafile 00006 to/DBBak2/oradata/WWL/wwl02.dbf
restoring datafile 00007 to/DBBak2/oradata/WWL/wwl03.dbf
channel ORA_DISK_1: reading from backuppiece /DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1
channel ORA_DISK_1: restored backup piece1
piecehandle=/DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1 tag=TAG20120712T095437
channel ORA_DISK_1: restore complete,elapsed time: 00:01:35
Finished restore at 12-JUL-12
RMAN> exit
5、執行基于時間點的恢複:
21:27:54 SQL> recover database untiltime ‘YYYY-mm-dd hh24:mi:ss’
21:27:54 SQL> recover database untiltime '2012-07-12 21:10:00';
ORA-00279: change 1436429 generated at07/12/2012 09:54:38 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_788372282.dbf
ORA-00280: change 1436429 for thread 1 isin sequence #3
21:30:09 Specify log:{<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1440657 generated at 07/12/201214:00:52 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788450452.dbf
ORA-00280: change 1440657 for thread 1 isin sequence #1
ORA-00279: change 1440855 generated at07/12/2012 15:08:58 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788454538.dbf
ORA-00280: change 1440855 for thread 1 isin sequence #1
ORA-00279: change 1441316 generated at07/12/2012 15:19:50 needed for thread 1
ORA-00289: suggestion : /DBSoft/product/10.2.0/db_1/dbs/arch1_1_788455190.dbf
ORA-00280: change 1441316 for thread 1 isin sequence #1
ORA-00279: change 1442275 generated at07/12/2012 15:52:01 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788457121.dbf
ORA-00280: change 1442275 for thread 1 isin sequence #1
ORA-00279: change 1442953 generated at07/12/2012 16:25:06 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788459106.dbf
ORA-00280: change 1442953 for thread 1 is insequence #1
ORA-00279: change 1462958 generated at07/12/2012 16:28:16 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788459106.dbf
ORA-00280: change 1462958 for thread 1 isin sequence #2
ORA-00278: log file '/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788459106.dbf'no longer needed for this recovery
ORA-00279: change 1462963 generated at07/12/2012 17:17:59 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788462279.dbf
ORA-00280: change 1462963 for thread 1 isin sequence #1
ORA-00279: change 1483784 generated at07/12/2012 17:54:25 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788462279.dbf
ORA-00280: change 1483784 for thread 1 isin sequence #2
ORA-00278: log file'/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788462279.dbf' no longer needed forthis recovery
Log applied.
Media recovery complete.
21:30:29 SQL>
至此已經恢複完成。
6、因為做了不完全恢複,必須要做restlogs打開資料庫。
21:30:29 SQL> alter database open restlogs;
alter database open restlogs
7、資料已經恢複完成,資料都回來了。
21:34:04 SQL> select * from wwl002;
ID NAME
-------------------------------------------------------
1 wwl
2 prodence
3 woo
4 xgx
5 cms
Elapsed: 00:00:00.01
21:34:16 SQL> select * from wwl003;
Elapsed: 00:00:00.00
21:34:18 SQL> select * from wwl004;
21:34:20 SQL> select * from wwl005;
---------- ---------------------------------------------
21:34:22 SQL>