控制檔案丟失恢復

(一)控制檔案丟失(丟失其中一個，而不是全部)
  解決辦法有兩種：
  1)複製一份現有的控制檔案進行恢復
  2)修改spfile檔案不指定到丟失的控制檔案
 
模擬這兩種情況
準備工作：
1.檢視當前資料庫控制檔案
SQL> select name,status from v$controlfile;

NAME                                                         STATUS
------------------------------------------------------------ -------
D:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
D:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
D:\ORACLE\ORADATA\ORCL\CONTROL03.CTL
2.檢視歸檔
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     77
Next log sequence to archive   79
Current log sequence           79
3.刪除一個控制檔案（模擬控制檔案丟失）,由於在windows下資料庫在開啟的狀態下不能直接刪除檔案，用360粉碎檔案來刪除

4.檢視資料庫是否還能使用
SQL> select name,status from v$controlfile;
ERROR:
ORA-03114: not connected to ORACLE
5.檢視告警日誌檔案
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_ckpt_388.trc:
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: 'D:\ORACLE\ORADATA\ORCL\CONTROL03.CTL'
ORA-27072: File I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 6) 控制程式碼無效。

Sun Jan 12 16:29:52 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_ckpt_388.trc:
ORA-00221: error on write to control file
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: 'D:\ORACLE\ORADATA\ORCL\CONTROL03.CTL'
ORA-27072: File I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 6) 控制程式碼無效。

Sun Jan 12 16:29:52 2014
CKPT: terminating instance due to error 221
Sun Jan 12 16:29:52 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_q001_5964.trc:
ORA-00221: error on write to control file

Sun Jan 12 16:29:52 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_5152.trc:
ORA-00221: error on write to control file

Sun Jan 12 16:29:52 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_4280.trc:
ORA-00221: 寫入控制檔案時出錯

Sun Jan 12 16:29:52 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_5484.trc:
ORA-00221: error on write to control file

一個控制檔案損壞或丟失，資料庫就會關閉.現在資料庫已不能使用.
開始恢復
第一種方法：複製當前可用的控制檔案
現在資料庫處於關閉狀態
1)啟動到nomout,檢視資料庫控制檔案
SQL> show parameter control

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time        integer                7
control_files                        string                 D:\ORACLE\ORADATA\ORCL\CONTROL
                                                            01.CTL, D:\ORACLE\ORADATA\ORCL
                                                            \CONTROL02.CTL, D:\ORACLE\ORAD
                                                            ATA\ORCL\CONTROL03.CTL
2)使用control02.ctl複製一個CONTROL03.CTL
3)啟動到mount狀態
SQL> alter database mount;

Database altered.
4)開啟資料庫
SQL> alter database open;

Database altered.

恢復完成
第二種方法：使用修改引數檔案不指定到丟失的控制檔案
刪除控制檔案跟第一種方法一樣，不再模擬
1)啟動資料庫到nomount
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  570425344 bytes
Fixed Size                  1250188 bytes
Variable Size             192941172 bytes
Database Buffers          369098752 bytes
Redo Buffers                7135232 bytes
SQL> alter database mount;  --啟動到mount狀態報錯，因為控制檔案已丟失
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
SQL> show parameter control  --檢視控制檔案

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time        integer                7
control_files                        string                 D:\ORACLE\ORADATA\ORCL\CONTROL
                                                            01.CTL, D:\ORACLE\ORADATA\ORCL
                                                            \CONTROL02.CTL, D:\ORACLE\ORAD
                                                            ATA\ORCL\CONTROL03.CTL
2)修改control_files引數指定control01.ctl和control03.ctl
SQL> alter system set control_files='D:\oracle\oradata\orcl\control01.ctl','D:\oracle\oradata\orcl\control03.ctl' scope=spfile;

System altered.

SQL> show parameter control  --檢視控制檔案沒有修改,scope=spfile下次啟動才生效

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time        integer                7
control_files                        string                 D:\ORACLE\ORADATA\ORCL\CONTROL
                                                            01.CTL, D:\ORACLE\ORADATA\ORCL
                                                            \CONTROL02.CTL, D:\ORACLE\ORAD
                                                            ATA\ORCL\CONTROL03.CTL
3)關閉資料庫並啟動到nomount
SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  570425344 bytes
Fixed Size                  1250188 bytes
Variable Size             192941172 bytes
Database Buffers          369098752 bytes
Redo Buffers                7135232 bytes
SQL> show parameter control    --引數已生效

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time        integer                7
control_files                        string                 D:\ORACLE\ORADATA\ORCL\CONTROL
                                                            01.CTL, D:\ORACLE\ORADATA\ORCL
                                                            \CONTROL03.CTL
4)啟動到mount並開啟資料庫
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

總結：
  1.控制檔案損壞或丟失任何一個，資料庫將關閉
    寫三個都要寫，讀只讀其中一個?
  2.控制檔案的保護可以使用複用控制檔案,放在不同磁碟上
  3.如果控制檔案全部丟失該如何恢復?
  未完待續...