下文介绍了Oracle数据库中丢失控制文件的几种处理方法。
丢失单个控制文件
报错信息：
2013-05-08 03:00:29.678000 +08:00
Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_5204.trc:
ORA-00210: cannot open the specifiedcontrol file
ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file ordirectory
Additional information: 3
Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m001_5289.trc:
ORA-00210: cannot open the specifiedcontrol file
ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file ordirectory
1、直接手动关闭了，其实数据库会自动的关闭。
shutdown abort ;
2、以下有两种方法
2.1、拷贝控制文件到原来的目录
cp/u02/flash_recovery_area/bkt/control02.ctl /u02/oradat/bkt/control01.ctl
2.2、启动到nomount后设置control_files的位置，将丢失的控制文件路径去掉
alter system setcontrol_files='/u02/flash_recovery_area/bkt/control02.ctl' scope=spfile ;
3.启动数据库即可
startup
下面介绍丢失所有控制文件的时候应该怎么做
使用冷备份的控制文件恢复
以下为详细的示例：
1. backup controlfile
show controlfile
copy
rman target /
backup controlfile current format '' ;
output :
sys@BKT> show parameter control
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u02/oradat/bkt/control01.ctl,
/u02/flash_recovery_area/bkt/
control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
--instance still running ...
[oracle@master ~]$ cp/u02/oradat/bkt/control01.ctl /tmp/control01.ctl
2. create tablespace
conn / as sysdba
define tbsname1='tbs1'
define tbsname2='tbs2'
define dfpath1='/u02/oradat/bkt/tbs101.dbf'
define dfpath2='/u02/oradat/bkt/tbs201.dbf'
create tablespace &tbsname1 datafile'&dfpath1' size 100m ;
create tablespace &tbsname2 datafile'&dfpath2' size 100m ;
create table &tbsname1 tablespace&tbsname1 as select * from all_objects ;
create table &tbsname2 tablespace&tbsname2 as select * from all_objects ;
select count(*) from &tbsname1 ;
select count(*) from &tbsname2 ;
alter tablespace &tbsname1 read only ;
output :
sys@BKT> conn / as sysdba
Connected.
sys@BKT>
sys@BKT> define tbsname1='tbs1'
sys@BKT> define tbsname2='tbs2'
sys@BKT>
sys@BKT> definedfpath1='/u02/oradat/bkt/tbs101.dbf'
sys@BKT> definedfpath2='/u02/oradat/bkt/tbs201.dbf'
sys@BKT> create tablespace &tbsname1datafile '&dfpath1' size 100m ;
old 1: create tablespace &tbsname1 datafile '&dfpath1' size 100m
new 1: create tablespace tbs1 datafile '/u02/oradat/bkt/tbs101.dbf' size100m
Tablespace created.
sys@BKT> create tablespace &tbsname2datafile '&dfpath2' size 100m ;
old 1: create tablespace &tbsname2 datafile '&dfpath2' size 100m
new 1: create tablespace tbs2 datafile '/u02/oradat/bkt/tbs201.dbf' size100m
Tablespace created.
sys@BKT> create table &tbsname1tablespace &tbsname1 as select * from all_objects ;
old 1: create table &tbsname1 tablespace &tbsname1 as select * fromall_objects
new 1: create table tbs1 tablespace tbs1 as select * from all_objects
Table created.
sys@BKT> create table &tbsname2tablespace &tbsname2 as select * from all_objects ;
old 1: create table &tbsname2 tablespace &tbsname2 as select * fromall_objects
new 1: create table tbs2 tablespace tbs2 as select * from all_objects
Table created.
sys@BKT> select count(*) from&tbsnam