本次試驗分為以下步驟
1 刪除表空間
2 檢查恢復
3 還原資料檔案
4 不完全恢復資料庫
5 以重製日誌選項開啟資料庫
6 確認資料庫恢復

step1 刪除表空間

插入時間戳資料作為參考
insert into snow.date_log values(sysdate,'snow1'); commit;

select * from snow.date_log order by create_time;

CREATE_TIME         NAME
------------------- ------------------------------
2014-08-01 10:26:35 --
2014-08-01 10:27:35 --
2014-08-01 10:28:16 snow1 <==資料插入的時間

切換3次以上日誌，強制產生歸檔
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

drop tablespace indx including contents and datafilee;
出錯資訊為表空間中有主鍵約束，無法刪除
ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1ORA-02429: cannot drop index used for enforcement of unique/primary key

執行SQL語句產生一條刪除約束的命令
select 'alter table ' ||owner||'.'||table_name|| ' drop constraint ' ||constraint_name||' ;'
from dba_constraints
where constraint_type in ('U' , 'P')
   and (index_owner, index_name) in
       ( select owner, segment_name
          from dba_segments
         where tablespace_name = 'INDX' );

alter table SNOW.DATE_LOG drop constraint CREATE_DATE_PK ;

drop tablespace indx including contents and datafilee;

此時alert日誌會記錄刪除表空間的資訊
Fri Aug 01 10:29:06 2014
Archived Log entry 24 added for thread 1 sequence 4 ID 0xb581bdfe dest 1:
Fri Aug 01 10:29:18 2014
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 3 seq# 6 mem# 0: /oradata/PRACTICE/redo03.log
Fri Aug 01 10:29:18 2014 <==刪除表空間前最後一次日誌切換時間，不完全恢復時將要引用該時間
Archived Log entry 25 added for thread 1 sequence 5 ID 0xb581bdfe dest 1: <==最後一次切換日誌序列號

step2 檢查恢復
alter system switch logfile;

找出在重做日誌序列號為3的第一個變動序號，需要使用v$log_history
select sequence#,first_change# from v$log_history where sequence#=5;

SEQUENCE# FIRST_CHANGE#
---------- -------------
         5       1077795
         5       1299130

step3 還原資料檔案

還原所有的資料檔案，但是保留現在正使用的控制檔案，本實驗的關鍵點在此

shutdown immediate;
cp /backup/closed_backup/*.dbf /oradata/PRACTICE/

step4 不完全恢復資料庫
startup mount;

基於時間的恢復，時間點引用 “Fri Aug 01 10:29:18 2014”
alter database recover automatic until time '2014-08-01 10:29:18';

基於SCN的恢復
RECOVER DATABASE UNTIL CHANGE 1300637;

基於取消的恢復
alter database recover until cancel using backup controlfile;

step5 以重製日誌選項開啟資料庫
alter database open resetlogs;

step6 確認資料庫恢復

檢視INDX表空間是否被恢復
select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                                                                            STATUS
------------------------------------------------------------------------------------------ ----------
SYSTEM                                                                                     ONLINE
SYSAUX                                                                                     ONLINE
UNDOTBS1                                                                                   ONLINE
TEMP                                                                                       ONLINE
USERS                                                                                      ONLINE
EXAMPLE                                                                                    ONLINE
TOOLS                                                                                      ONLINE
INDX                                                                                       ONLINE

檢視日誌檔案是否有報錯
Fri Aug 01 10:42:35 2014
Errors in file /u01/app/oracle/diag/rdbms/practice/PRACTICE/trace/PRACTICE_j001_8206.trc:
ORA-12012: error on auto execute of job 24
ORA-00376: file 7 cannot be read at this time
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007’ <==7號資料檔案有錯
ORA-06512: at "SNOW.CREATE_DATE_LOG_ROW", line 4
ORA-06512: at line 1

col tablespace_name for a10
col file_name for a60

檢視資料庫檔案資料字典
select file_id,tablespace_name,file_name,online_status from dba_data_files;

   FILE_ID TABLESPACE FILE_NAME                                                    ONLINE_STATUS
---------- ---------- ------------------------------------------------------------ ---------------------
         4 USERS      /oradata/PRACTICE/users01.dbf                                ONLINE
         3 UNDOTBS1   /oradata/PRACTICE/undotbs01.dbf                              ONLINE
         2 SYSAUX     /oradata/PRACTICE/sysaux01.dbf                               ONLINE
         1 SYSTEM     /oradata/PRACTICE/system01.dbf                               SYSTEM
         5 EXAMPLE    /oradata/PRACTICE/example01.dbf                              ONLINE
         6 TOOLS      /oradata/PRACTICE/tools01.dbf                                ONLINE
         7 INDX       /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007         RECOVER
         8 USERS      /oradata/PRACTICE/users02.dbf                                ONLINE

由於我們使用的是當前的控制檔案，已經“不認識”被刪除的表空間INDX和所包含的資料檔案datafile7。從冷備複製回來的資料檔案datafile7在恢復過程中無法識別，就被建立到預設的/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007，為了可以正常啟動資料庫。

手工重置資料檔案的路徑，讓控制檔案重新識別
ALTER TABLESPACE indx RENAME DATAFILE '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007' to '/oradata/PRACTICE/indx.dbf';

恢復資料檔案
recover datafile 7;
ORA-00279: change 1298436 generated at 08/01/2014 10:21:00 needed for thread 1
ORA-00289: suggestion : /archive/1_1_854446453.arc
ORA-00280: change 1298436 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1299046 generated at 08/01/2014 10:26:58 needed for thread 1
ORA-00289: suggestion : /archive/1_2_854446453.arc
ORA-00280: change 1299046 for thread 1 is in sequence #2

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1299121 generated at 08/01/2014 10:28:54 needed for thread 1
ORA-00289: suggestion : /archive/1_3_854446453.arc
ORA-00280: change 1299121 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1299127 generated at 08/01/2014 10:29:03 needed for thread 1
ORA-00289: suggestion : /archive/1_4_854446453.arc
ORA-00280: change 1299127 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1299130 generated at 08/01/2014 10:29:06 needed for thread 1
ORA-00289: suggestion : /archive/1_5_854446453.arc
ORA-00280: change 1299130 for thread 1 is in sequence #5

Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

恢復後將資料檔案置為online狀態

alter database datafile 7 online;

select file_id,tablespace_name,file_name,online_status from dba_data_files;

   FILE_ID TABLESPACE FILE_NAME                                                    ONLINE_STATUS
---------- ---------- ------------------------------------------------------------ ---------------------
         4 USERS      /oradata/PRACTICE/users01.dbf                                ONLINE
         3 UNDOTBS1   /oradata/PRACTICE/undotbs01.dbf                              ONLINE
         2 SYSAUX     /oradata/PRACTICE/sysaux01.dbf                               ONLINE
         1 SYSTEM     /oradata/PRACTICE/system01.dbf                               SYSTEM
         5 EXAMPLE    /oradata/PRACTICE/example01.dbf                              ONLINE
         6 TOOLS      /oradata/PRACTICE/tools01.dbf                                ONLINE
         7 INDX       /oradata/PRACTICE/indx.dbf                                   ONLINE
         8 USERS      /oradata/PRACTICE/users02.dbf                                ONLINE

檢視資料恢復是否成功
select * from snow.date_log order by create_time;

CREATE_TIME         NAME
------------------- --------------------
2014-08-01 10:20:20 --
2014-08-01 10:21:35 --
2014-08-01 10:22:35 --
2014-08-01 10:23:35 --
2014-08-01 10:24:35 --
2014-08-01 10:25:35 --
2014-08-01 10:26:35 --
2014-08-01 10:27:35 --
2014-08-01 10:28:16 snow1

檢視被刪除的約束是否恢復成功
col INDEX_NAME for a20
col TABLE_NAME for a20
elect index_name,table_name from user_indexes;

INDEX_NAME           TABLE_NAME
-------------------- --------------------
CREATE_DATE_PK       DATE_LOG

ORA-01110: data file 7: '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007'

相關文章