(一) 使用備份時的控制文件進行恢復,如下圖,即使用備份位置的控制文件進行恢復
1) 創建測試表空間ts1及相關測試表(表空間為ts1)
SYS@ORCL>create tablespace ts1 datafile '/u01/app/oracle/oradata/ORCL/ts1.dbf' size 10m;
Tablespace created.
SYS@ORCL>create table scott.t(x int) tablespace ts1;
Table created.
SYS@ORCL>insert into scott.t select rownum from dual connect by rownum<=10;
10 rows created.
SYS@ORCL>commit;
Commit complete.
SYS@ORCL>
2) 通過RMAN備份ts1表空間和控制文件
RMAN> backup tablespace ts1 include current controlfile;
Starting backup at 17-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/u01/app/oracle/oradata/ORCL/ts1.dbf
channel ORA_DISK_1: starting piece 1 at 17-JAN-13
channel ORA_DISK_1: finished piece 1 at 17-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_nnndf_TAG20130117T101646_8hgqrg9h_.bkp tag=TAG20130117T101646 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 17-JAN-13
channel ORA_DISK_1: finished piece 1 at 17-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_ncnnf_TAG20130117T101646_8hgqrhvd_.bkp tag=TAG20130117T101646 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JAN-13
RMAN>
3) 刪除t表部分記錄
SYS@ORCL>delete scott.t where x>=6;
5 rows deleted.
SYS@ORCL>commit;
Commit complete.
SYS@ORCL>
4) 切換幾次日志,讓上面的動作歸檔(對於測試來說,意義不是特別的大)
SYS@ORCL>alter system switch logfile;
System altered.
SYS@ORCL>/
System altered.
SYS@ORCL>/
System altered.
SYS@ORCL>/
System altered.
SYS@ORCL>
5) 在users表空間上創建t1表
SYS@ORCL>create table scott.t1(x int) tablespace users;
Table created.
SYS@ORCL>insert into scott.t1 select rownum from dual connect by rownum<=2;
2 rows created.
SYS@ORCL>commit;
Commit complete.
SYS@ORCL>
6) 將表ts1修改為只讀模式
SYS@ORCL>alter tablespace ts1 read only;
Tablespace altered.
SYS@ORCL>select tablespace_name,status from dba_tablespaces where tablespace_name='TS1';
TABLESPACE_NAME STATUS
------------------------- ---------
TS1 READ ONLY
SYS@ORCL>select file_id,file_name,status,online_status from dba_data_files where tablespace_name='TS1';
FILE_ID FILE_NAME STATUS ONLINE_
---------- --------------------------------------------- --------- -------
6 /u01/app/oracle/oradata/ORCL/ts1.dbf AVAILABLE ONLINE
SYS@ORCL>
7) 刪除ts1表空間數據文件和所有控制文件
SYS@ORCL>!rm /u01/app/oracle/oradata/ORCL/ts1.dbf
SYS@ORCL>!rm /u01/app/oracle/oradata/ORCL/*.ctl
8) 重啟數據庫到nomount狀態
SYS@ORCL>shutdown abort;
ORACLE instance shut down.
SYS@ORCL>
SYS@ORCL>startup nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
SYS@ORCL>
9) 通過RMAN備份的控制文件完成控制文件的恢復,恢復完成,數據庫修改為mount
RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_ncnnf_TAG20130117T101646_8hgqrhvd_.bkp';
Starting restore at 17-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output filename=/u01/app/oracle/oradata/ORCL/control01.ctl
output filename=/u01/app/oracle/oradata/ORCL/control02.ctl
output filename=/u01/app/oracle/oradata/ORCL/control03.ctl
Finished restore at 17-JAN-13
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
10) 恢復ts1表空間
RMAN> restore tablespace ts1;
Starting restore at 17-JAN-13
Starting implicit crosscheck backup at 17-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 17-JAN-13
Starting implicit crosscheck copy at 17-JAN-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-JAN-13
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_ncnnf_TAG20130117T101646_8hgqrhvd_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /u01/app/oracle/oradata/ORCL/ts1.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_nnndf_TAG20130117T101646_8hgqrg9h_.bkp
channel ORA_DISK_1: restored backup piece 1piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_nnndf_TAG20130117T101646_8hgqrg9h_.bkp tag=TAG20130117T101646
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-JAN-13
RMAN>
11) 表空間恢復完畢,嘗試open數據庫
--當然,我們知道一定是無法open數據庫的,因為控制文件恢復的是老版本的
SYS@ORCL>!ls /u01/app/oracle/oradata/ORCL/ts1.dbf
/u01/app/oracle/oradata/ORCL/ts1.dbf
SYS@ORCL>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--提示resetlogs方式打開
SYS@ORCL>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'
--提示需要恢復1號數據文件
SYS@ORCL>recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
--提示using BACKUP CONTROLFILE方式完成恢復
SYS@ORCL>
12) using backup controlfile恢復數據庫
注:如果要恢復到控制文件SCN以後的時間。這時候,就需要用using backup controlfile. 恢復就不會受“當前controlfile所紀錄的SCN”的限制。
這時候的限制就來自於你的語句(until time , until scn),或者可用的archive log(until cancel)
SYS@ORCL>recover database using backup controlfile until cancel;
ORA-00279: change 493760 generated at 01/16/2013 17:10:46 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_16/o1_mf_1_7_%u_.arc
ORA-00280: change 493760 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
--此處回車則是利用下一個歸檔日志文件進行恢復。不過在這裡也可以輸入CANCEL,表示恢復到此結束,不再使用後面的歸檔日志文件
……
--在恢復的過程中又出現了如下警告
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'
SYS@ORCL>
13) 嘗試再次resetlogs打開數據庫
SYS@ORCL>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'
SYS@ORCL>
14) 采用極端辦法
注:此時只能采取極端手段:隱含參數_allow_resetlogs_corruption強制啟動數據庫,設置此參數之後,在數據庫Open過程中,Oracle會跳過某些一致性檢查,從而使數據庫可能跳過不一致狀態,Open打開:
--查看隱藏參數_allow_resetlogs_corruption,默認值為false
SYS@ORCL>SELECT ksppstvl, ksppdesc
2 FROM x$ksppi x, x$ksppcv y
3 WHERE x.indx = y.indx
4 AND ksppinm = '_allow_resetlogs_corruption';
KSPPSTVL KSPPDESC
---------- --------------------------------------------------
FALSE allow resetlogs even if it will cause corruption
SYS@ORCL>
--將隱藏參數設置為true
SYS@ORCL>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SYS@ORCL>
--由於是靜態參數,重啟數據庫使其生效
SYS@ORCL>shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>
--數據庫啟動到mount狀態
SYS@ORCL>startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 83887696 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS@ORCL>
15) 再次嘗試open數據庫,提示RESETLOGS方式open
SYS@ORCL>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@ORCL>
16) 數據庫成功以resetlogs方式open
SYS@ORCL>alter database open resetlogs;
Database altered.
SYS@ORCL>
SYS@ORCL>select count(*) from scott.t;
COUNT(*)
----------
5
SYS@ORCL>select count(*) from scott.t1;
select count(*) from scott.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@ORCL>
17) 恢復參數,重啟數據庫
SYS@ORCL>alter system set "_allow_resetlogs_corruption"=false scope=spfile;
System altered.
SYS@ORCL>startup force;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 88082000 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ORCL>
如果在生產中使用了只讀表空間,那麼切記,在修改為只讀以後,第一件事兒就是備份,這樣會省去不必要的麻煩和損失。