備份與恢復--重建控制檔案後資料檔案損壞的恢復
恢復辦法知用於：
控制檔案重建後的資料檔案損壞
前提條件：
歸檔模式，有完整的歸檔日誌和重建控制檔案前的資料檔案
方法：
將以前備份的檔案複製過來，做一次recover就行了
重建控制檔案之前的歸檔日誌。
SQL> SELECT RECID, NAME, FIRST_TIME FROM V$ARCHIVED_LOG;
RECID NAME FIRST_TIM
----- ------------------------------------------------------------------------------------------ ---------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_4_4poo2f69_.arc 09-JAN-09
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_5_4poo2hrt_.arc 10-JAN-09
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_6_4poo3yk0_.arc 12-JAN-09
4 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_7_4pqk7z7q_.arc 12-JAN-09
5 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqk8lmn_.arc 13-JAN-09
6 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqk9l4t_.arc 13-JAN-09
6 rows selected.
做一個冷備份。
SQL> shutdown immediate;
[oracle@rhel131 orcl]$ ls
control01.bak control02.ctl example01.dbf redo01.log sysaux01.dbf test01.bak users01.dbf
control01.ctl control03.bak jglu01.dbf redo02.log system01.dbf test02.dbf
control02.bak control03.ctl jglu02.dbf redo03.log temp01.dbf undotbs01.dbf
[oracle@rhel131 orcl]$ cp *.* /u01/backup
SQL> startup nomount;
ORACLE instance started.
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/orcl/system01.dbf',
14 '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
15 '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
16 '/u01/app/oracle/oradata/orcl/users01.dbf',
17 '/u01/app/oracle/oradata/orcl/example01.dbf',
18 '/u01/app/oracle/oradata/orcl/jglu01.dbf',
19 '/u01/app/oracle/oradata/orcl/jglu02.dbf',
20 '/u01/app/oracle/oradata/orcl/test02.dbf'
21 CHARACTER SET WE8ISO8859P1
22 ;
Control file created.
SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
SQL> column recid format 99
SQL> column name format a90
SSQL> SELECT RECID, NAME,to_char(first_time,'yyyy-mm-dd:hh24:mi:ss') from V$ARCHIVED_LOG;
RECID NAME TO_CHAR
(FIRST_TIME,
----- ------------------------------------------------------------------------------------------ -------------
------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqkt8d6_.arc 2009-01-
13:06:32:31
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqkt8g7_.arc 2009-01-
13:06:32:50
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqktcw0_.arc 2009-01-
13:06:33:22
重建控制檔案後，會丟失所有的歸檔重做日誌的歷史，這三個歸檔日誌是重建控制檔案後新產生的。
SQL> alter tablespace users offline;
Tablespace altered.
SQL> host rm /u01/app/oracle/oradata/orcl/users01.dbf
SQL> host cp /u01/backup/users01.dbf /u01/app/oracle/oradata/orcl
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------
---
4 OFFLINE OFFLINE OFFLINE NORMAL 0
SQL> recover tablespace users
Media recovery complete.
SQL> alter tablespace users online;
Tablespace altered.
可以完全恢復過來,看看日誌檔案，恢復時只用到了online redo,沒有用到archived log
Tue Jan 13 06:52:33 2009
ALTER DATABASE RECOVER tablespace users
Tue Jan 13 06:52:33 2009
Media Recovery Start
Tue Jan 13 06:52:33 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 10 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo03.log
Tue Jan 13 06:52:33 2009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 11 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo01.log
Tue Jan 13 06:52:33 2009
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER tablespace users
下面再來做一些測試性的操作和switch log,以使recover時用到歸檔日誌後再重建一次control file看看情況。
SQL> create table jglu.a(
2 id number)
3 tablespace users;
Table created.
SQL> insert into jglu.a values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into jglu.a values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into jglu.a values(3);
1 row created.
SQL> commit;
SQL> alter system switch logfile;
System altered.
SQL> SQL> SELECT RECID, NAME,to_char(first_time,'yyyy-mm-dd:hh24:mi:ss') from V$ARCHIVED_LOG;
RECID NAME TO_CHAR
(FIRST_TIME,
----- ------------------------------------------------------------------------------------------ -------------
------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqkt8d6_.arc 2009-01-
13:06:32:31
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqkt8g7_.arc 2009-01-
13:06:32:50
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqktcw0_.arc 2009-01-
13:06:33:22
4 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_4pqlzz7c_.arc 2009-01-
13:06:42:19
5 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm18gj_.arc 2009-01-
13:07:02:23
6 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm1mpq_.arc 2009-01-
13:07:03:04
6 rows selected.
下面重建controlfile
SQL> startup nomount;
ORACLE instance started.
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/orcl/system01.dbf',
14 '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
15 '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
16 '/u01/app/oracle/oradata/orcl/users01.dbf',
17 '/u01/app/oracle/oradata/orcl/example01.dbf',
18 '/u01/app/oracle/oradata/orcl/jglu01.dbf',
19 '/u01/app/oracle/oradata/orcl/jglu02.dbf',
20 '/u01/app/oracle/oradata/orcl/test02.dbf'
21 CHARACTER SET WE8ISO8859P1
22 ;
Control file created.
SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
SQL> SELECT RECID, NAME,to_char(first_time,'yyyy-mm-dd:hh24:mi:ss') from V$ARCHIVED_LOG;
RECID NAME TO_CHAR
(FIRST_TIME,
----- ------------------------------------------------------------------------------------------ -------------
------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm516c_.arc 2009-01-
13:07:02:23
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm5185_.arc 2009-01-
13:07:03:04
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_14_4pqm54v0_.arc 2009-01-
13:07:03:15
刪除users表空間
SQL> alter tablespace users offline;
Tablespace altered.
SQL> host rm /u01/app/oracle/oradata/orcl/users01.dbf
SQL> host cp /u01/backup/users01.dbf /u01/app/oracle/oradata/orcl
恢復users表空間
SQL> recover tablespace users;
ORA-00279: change 673659 generated at 01/13/2009 06:34:07 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_%u_.ar
c
ORA-00280: change 673659 for thread 1 is in sequence #10
Specify log: {
AUTO
ORA-00279: change 674058 generated at 01/13/2009 06:42:19 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_%u_.ar
c
ORA-00280: change 674058 for thread 1 is in sequence #11
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqkt
cw0_.arc' no longer needed for this recovery
ORA-00279: change 675373 generated at 01/13/2009 07:02:23 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_%u_.ar
c
ORA-00280: change 675373 for thread 1 is in sequence #12
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_4pqlz
z7c_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
日誌檔案如下資訊：
Tue Jan 13 07:09:08 2009
ALTER DATABASE RECOVER tablespace users
Tue Jan 13 07:09:08 2009
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER tablespace users ...
Tue Jan 13 07:09:22 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Jan 13 07:09:23 2009
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqktcw0_.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Jan 13 07:09:23 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Jan 13 07:09:23 2009
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_4pqlzz7c_.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Jan 13 07:09:23 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Jan 13 07:09:23 2009
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm18gj_.arc
Tue Jan 13 07:09:23 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 13 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo03.log
Tue Jan 13 07:09:23 2009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 14 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo01.log
Tue Jan 13 07:09:23 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 15 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo02.log
Tue Jan 13 07:09:23 2009
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
SQL> alter tablespace users online;
Tablespace altered.
SQL> select * from jglu.a;
ID
----------
1
2
3
綜合以上，說明資料檔案損壞時，只要有以前的備份，哪怕控制檔案重新建立後，用歸檔日誌是可以完全恢復的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-1016124/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 備份與恢復--重建控制檔案
- 備份＆恢復之十三：損壞全部控制檔案
- 【備份與恢復】恢復受損的複用控制檔案
- 備份與恢復--資料檔案損壞或丟失
- 備份與恢復--利用備份的控制檔案恢復
- 【備份與恢復】恢復受損的口令檔案
- 備份＆恢復之十二：損壞單個控制檔案
- 損壞控制檔案的恢復方法
- 備份與恢復系列 十一 控制檔案的備份與恢復
- 非系統資料檔案損壞，rman備份恢復
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- 單個控制檔案損壞的恢復
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 【恢復】使用控制檔案的trace備份恢復因異常斷電導致所有控制檔案損壞的資料庫故障資料庫
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- 【備份恢復】所有控制檔案丟失後 利用trace中的控制檔案備份執行恢復
- 恢復之單個控制檔案損壞
- 備份與恢復--重新控制檔案資料字典和控制檔案不一致的恢復
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 資料檔案丟失損壞的恢復--
- 備份與恢復（Parameter 檔案恢復篇）
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）
- RMAN備份恢復之控制檔案的恢復（一）
- 某個控制檔案損壞的恢復案例
- 一次控制檔案損壞的恢復
- 利用備份的控制檔案恢復
- 控制檔案的備份和恢復
- 使用備份的控制檔案恢復資料庫資料庫
- 【rman 備份與恢復】恢復丟失所有的控制檔案
- 一次控制檔案損壞後的恢復經歷
- 磁碟損壞造成RMAN備份檔案有壞塊的恢復案例
- 恢復之重建資料檔案
- 歸檔模式有備份丟失控制檔案和資料檔案後恢復模式
- 控制檔案重建後的不完全恢復
- rman 恢復---歸檔丟失and資料檔案損壞
- 【備份恢復】 控制檔案多路徑
- rman備份-（1） 利用備份級恢復資料檔案和控制檔案