控制文件丢失恢复

(一)控制文件丢失(丢失其中一个，而不是全部)
  解决办法有两种：
  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.如果控制文件全部丢失该如何恢复?
  未完待续...

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26937943/viewspace-1069771/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26937943/viewspace-1069771/