本文給出基於使用者管理的熱備份對非系統表空間的損壞進行恢復例項。
備份恢復前提：
①資料庫執行在歸檔模式
②具有可用的使用者管理的備份

1.針對表空間TBS_SEC_D進行模擬損壞和恢復
sys@ora10g> col tablespace_name format a30
sys@ora10g> col file_name for a66
sys@ora10g> select tablespace_name,file_name,bytes/1024/1024 MB from dba_data_files where tablespace_name='TBS_SEC_D';

TABLESPACE_NAME FILE_NAME                                           MB
--------------- ------------------------------------------------ -----
TBS_SEC_D       /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf 2048

2.完成使用者管理的熱備份
sys@ora10g> alter tablespace tbs_sec_d begin backup;

Tablespace altered.

sys@ora10g> ! cp /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf /home/oracle

sys@ora10g> alter tablespace tbs_sec_d end backup;

Tablespace altered.

3.驗證備份資訊
1）作業系統上備份檔案資訊
sys@ora10g> ! ls -l /home/oracle/tbs_sec_d_01.dbf
-rw-r----- 1 oracle oinstall 2147491840 Apr 30 22:36 /home/oracle/tbs_sec_d_01.dbf

2）資料庫中查詢備份資訊
sys@ora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

sys@ora10g> col STATUS for a30
sys@ora10g> select * from v$backup;

     FILE# STATUS           CHANGE# TIME
---------- ------------- ---------- -------------------
         1 NOT ACTIVE             0
         2 NOT ACTIVE             0
         3 NOT ACTIVE             0
         4 NOT ACTIVE             0
         5 NOT ACTIVE        925678 2011-04-30 22:34:51

4.在sec使用者下模擬使用者操作
1）連線到sec使用者
sys@ora10g> conn sec/sec
Connected.

2）確定sec使用者下未包含資料庫物件
sec@ora10g> select * from obj;

no rows selected

這裡是為了保證測試使用者的純淨性，使用者後續恢復後的對比。

3）建立測試表T並初始化資料
sec@ora10g> create table t (x varchar2(8));

Table created.

sec@ora10g> insert into t values('secooler');

1 row created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from t;

X
--------
secooler

4）確認表T所屬的表空間是TBS_SEC_D
sec@ora10g> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from user_segments where SEGMENT_NAME='T';

SEGMENT_NAME   SEGMENT_TYPE       TABLESPACE_NAME
-------------- ------------------ --------------------
T              TABLE              TBS_SEC_D

也可以在建立表的過程中指定表空間。

5）切換日誌檔案
sys@ora10g> alter system switch logfile;

System altered.

5.人為損壞表空間TBS_SEC_D
這裡透過損壞TBS_SEC_D對應的資料檔案達到目的。
sec@ora10g> ! ls -l /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf
-rw-r----- 1 oracle oinstall 2147491840 Apr 30 22:37 /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf

sec@ora10g> ! echo > /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf

sec@ora10g> ! ls -l /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf
-rw-r----- 1 oracle oinstall 1 Apr 30 22:44 /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf

OK，此時資料檔案已為空檔案！

6.檢視故障現象
1）注意，此時還是可以查詢到T表中的資料，因為此時T表資料還殘留在記憶體中。
sec@ora10g> select * from t;

X
--------
secooler

2）嘗試完成幾次日誌切換。
sys@ora10g> alter system switch logfile;

System altered.

sys@ora10g> alter system switch logfile;

System altered.

sys@ora10g> alter system switch logfile;

System altered.

3）再次查詢T表資料的報錯資訊
sec@ora10g> select * from t;
select * from t
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf'

此時提示資料檔案已經不可用！

4）嘗試將表空間TBS_SEC_D的online操作
sys@ora10g> alter tablespace tbs_sec_d online;
alter tablespace tbs_sec_d online
*
ERROR at line 1:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf'
ORA-01251: Unknown File Header Version read for file number 5

這裡給出了進一步的報錯資訊。

7.使用備份對錶空間進行恢復
1）是問題資料檔案離線
sys@ora10g> alter database datafile '/oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf' offline;

Database altered.

2）還原備份的資料檔案
sys@ora10g> ! cp /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf /home/oracle/tbs_sec_d_01.dbf

sec@ora10g> ! rm -f /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf

sec@ora10g> ! cp /home/oracle/tbs_sec_d_01.dbf /oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf

3）嘗試對故障表空間online
sys@ora10g> alter tablespace tbs_sec_d online;
alter tablespace tbs_sec_d online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf'

這裡提示需要介質恢復，這個提示資訊是友好的。

4）獲取歸檔檔案的資訊
sys@ora10g> select * from v$recovery_log;
   THREAD# SEQUENCE# TIME      ARCHIVE_NAME
---------- ---------- --------- -------------------------------------------------------------------------------------------
         1         53 30-APR-11 /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_53_6vr7z4q4_.arc
         1         54 30-APR-11 /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_54_6vr87f2n_.arc
         1         55 30-APR-11 /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_55_6vr87l2t_.arc
         1         56 30-APR-11 /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_56_6vr87q7k_.arc

5）對故障資料檔案進行恢復
sys@ora10g> recover datafile 5;
ORA-00279: change 925678 generated at 04/30/2011 22:34:51 needed for thread 1
ORA-00289: suggestion : /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_53_%u_.arc
ORA-00280: change 925678 for thread 1 is in sequence #53

Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_53_6vr7z4q4_.arc
ORA-00279: change 925868 generated at 04/30/2011 22:42:44 needed for thread 1
ORA-00289: suggestion : /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_54_%u_.arc
ORA-00280: change 925868 for thread 1 is in sequence #54
ORA-00278: log file '/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_53_6vr7z4q4_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_54_6vr87f2n_.arc
ORA-00279: change 925961 generated at 04/30/2011 22:47:08 needed for thread 1
ORA-00289: suggestion : /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_55_%u_.arc
ORA-00280: change 925961 for thread 1 is in sequence #55
ORA-00278: log file '/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_54_6vr87f2n_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_55_6vr87l2t_.arc
ORA-00279: change 925964 generated at 04/30/2011 22:47:13 needed for thread 1
ORA-00289: suggestion : /oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_56_%u_.arc
ORA-00280: change 925964 for thread 1 is in sequence #56
ORA-00278: log file '/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_55_6vr87l2t_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/ora10gR2/flash_recovery_area/ORA10G/archivelog/2011_04_30/o1_mf_1_56_6vr87q7k_.arc
Log applied.
Media recovery complete.

這裡選擇手工指定歸檔檔案。

6）嘗試將表空間online
sys@ora10g> alter tablespace tbs_sec_d online;

Tablespace altered.

恢復使命初戰告捷。

7）最後的資料驗證
sec@ora10g> select * from t;

X
--------
secooler

整個恢復過程成功結束。

8.小結
注意整個恢復過程的前提是存在有效的備份！有備份便有了希望!

Good luck.

secooler
11.04.30

-- The End --

【恢復】基於使用者管理的熱備份對非系統表空間的損壞進行恢復

相關文章