Oracle数据库丢失控制文件的恢复四则

日期：2020-06-02 栏目：程序人生浏览：次

下文介绍了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&tbsname1 ;

old 1: select count(*) from &tbsname1

new 1: select count(*) from tbs1

COUNT(*)

----------

72780

sys@BKT> select count(*) from&tbsname2 ;

old 1: select count(*) from &tbsname2

new 1: select count(*) from tbs2

COUNT(*)

----------

72781

sys@BKT> alter tablespace &tbsname1read only ;

old 1: alter tablespace &tbsname1 read only

new 1: alter tablespace tbs1 read only

Tablespace altered.

sys@BKT>

3. switch logfile

alter system switch logfile ;

output :

sys@BKT> alter system switchlogfile ;

alter system switch logfile ;

System altered.

sys@BKT>

System altered.

sys@BKT>

System altered.

sys@BKT>

System altered.

4. remove controlfile

rm /u02/oradat/bkt/control01.ctl

rm/u02/flash_recovery_area/bkt/control02.ctl

output :

[oracle@master ~]$ rm/u02/oradat/bkt/control01.ctl

[oracle@master ~]$ rm/u02/flash_recovery_area/bkt/control02.ctl

--from alert .

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6172.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

5. shutdown database

shutdown abort

output :

sys@BKT> shutdown abort ;

ORACLE instance shut down.

6. using backup controlfile

--copy backup controlfile to thecontrolfile path which defined in the parameter .

cp /tmp/control01.ctl/u02/oradat/bkt/control01.ctl

cp /tmp/control01.ctl/u02/flash_recovery_area/bkt/control02.ctl

recover

output :

cp /tmp/control01.ctl /u02/oradat/bkt/control01.ctl

cp /tmp/control01.ctl/u02/flash_recovery_area/bkt/control02.ctl

sys@BKT> startup

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2228944 bytes

Variable Size 318770480 bytes

Database Buffers 88080384 bytes

Redo Buffers 8466432 bytes

Database mounted.

ORA-01122: database file 1 failedverification check

ORA-01110: data file 1:'/u02/oradat/bkt/system01.dbf'

ORA-01207: file is more recent than controlfile - old control file

--from alert

ALTER DATABASE OPEN

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_ora_6327.trc:

ORA-01122: database file 1 failedverification check

ORA-01110: data file 1:'/u02/oradat/bkt/system01.dbf'

<<<ORA-01207: file is more recentthan control file - old control file>>>

ORA-1122 signalled during: ALTER DATABASEOPEN...

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 1 of thread 1 is more recentthan control file

ORA-00312: online log 1 thread 1:'/u02/oradat/bkt/redo01.log'

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 1 of thread 1 is more recentthan control file

ORA-00312: online log 1 thread 1:'/u02/oradat/bkt/redo01.log'

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 2 of thread 1 is more recentthan control file

ORA-00312: online log 2 thread 1:'/u02/oradat/bkt/redo02.log'

Errors in file /u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 2 of thread 1 is more recentthan control file

ORA-00312: online log 2 thread 1:'/u02/oradat/bkt/redo02.log'

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 3 of thread 1 is more recentthan control file

ORA-00312: online log 3 thread 1:'/u02/oradat/bkt/redo03.log'

Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:

ORA-00338: log 3 of thread 1 is more recentthan control file

ORA-00312: online log 3 thread 1:'/u02/oradat/bkt/redo03.log'

Checker run found 1 new persistent datafailures

[oracle@master ~]$ oerr ora 01207

01207, 00000, "file is more recentthan control file - old control file"

// *Cause: The control file change sequence number in the data file is

// greater than the number in the control file. This implies that

// the wrong control file is being used. Note that repeatedly causing

// this error can make it stop happening without correcting the real

// problem. Every attempt to open the database will advance the

// control file change sequence number until it is great enough.

// *Action: Use the current control file ordo backup control file recovery to

// make the control file current. Be sure to follow all restrictions

// on doing a backup control file recovery.

--显然没有我们最近创建的两个表空间

sys@BKT> select name from v$datafile ;

NAME

----------------------------------------------------------------------------------------------------

/u02/oradat/bkt/system01.dbf

/u02/oradat/bkt/sysaux01.dbf

/u02/oradat/bkt/undotbs01.dbf

/u02/oradat/bkt/users01.dbf

/u02/oradat/bkt/example01.dbf

/u02/oradat/bkt/test1_01.dbf

6 rows selected.

--controlfile_change# low rba ,checkpoint_change#

sys@BKT> select checkpoint_change#,CONTROLFILE_SEQUENCE# ,CONTROLFILE_CHANGE# fromv$database ;

CHECKPOINT_CHANGE# CONTROLFILE_SEQUENCE#CONTROLFILE_CHANGE#

------------------ ----------------------------------------

1281710 3287 1293684

sys@BKT> select min(checkpoint_change#)from V$datafile_header ;

MIN(CHECKPOINT_CHANGE#)

-----------------------

1270438

sys@BKT> selectgroup#,first_change#,next_change# from v$log ;

GROUP# FIRST_CHANGE# NEXT_CHANGE#

---------- ------------- ------------

1 1273500 1273646

3 1274338 2.8147E+14

2 1273646 1274338

sys@BKT> recover database;

ORA-00283: recovery session canceled due toerrors

ORA-01122: database file 1 failedverification check

ORA-01110: data file 1:'/u02/oradat/bkt/system01.dbf'

ORA-01207: file is more recent than controlfile - old control file

sys@BKT> recover database using backup controlfile ;

ORA-00279: change 1293684 generated at05/08/2013 14:29:38 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arc

ORA-00280: change 1293684 for thread 1 isin sequence #93

Specify log: {<RET>=suggested |filename | AUTO | CANCEL}

auto

ORA-00283: recovery session canceled due toerrors

ORA-01244: unnamed datafile(s) added tocontrol file by media recovery

ORA-01110: data file 7:'/u02/oradat/bkt/tbs101.dbf'

ORA-01112: media recovery not started

这时候第七号文件已经加回来了。

sys@BKT> recover database using backupcontrolfile ;

ORA-00283: recovery session canceled due toerrors

ORA-01111: name for data file 7 is unknown- rename to correct file

ORA-01110: data file 7:'/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007'

ORA-01157: cannot identify/lock data file 7- see DBWR trace file

ORA-01111: name for data file 7 is unknown- rename to correct file

ORA-01110: data file 7: '/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007'

sys@BKT> select name from v$datafile ;

NAME

----------------------------------------------------------------------------------------------------

/u02/oradat/bkt/system01.dbf

/u02/oradat/bkt/sysaux01.dbf

/u02/oradat/bkt/undotbs01.dbf

/u02/oradat/bkt/users01.dbf

/u02/oradat/bkt/example01.dbf

/u02/oradat/bkt/test1_01.dbf

/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007

7 rows selected.

offline以下再恢复

sys@BKT> alter database datafile 7offline ;

Database altered.

sys@BKT> recover database using backupcontrolfile ;

ORA-00279: change 1293752 generated at05/13/2013 14:42:44 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arc

ORA-00280: change 1293752 for thread 1 isin sequence #93

Specify log: {<RET>=suggested |filename | AUTO | CANCEL}

auto

ORA-00283: recovery session canceled due toerrors

ORA-01244: unnamed datafile(s) added tocontrol file by media recovery

ORA-01110: data file 8: '/u02/oradat/bkt/tbs201.dbf'

ORA-01112: media recovery not started

这时候最后一个数据文件也加回来了

sys@BKT> select name from v$datafile ;

NAME

----------------------------------------------------------------------------------------------------

/u02/oradat/bkt/system01.dbf

/u02/oradat/bkt/sysaux01.dbf

/u02/oradat/bkt/undotbs01.dbf

/u02/oradat/bkt/users01.dbf

/u02/oradat/bkt/example01.dbf

/u02/oradat/bkt/test1_01.dbf

/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007

/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00008

8 rows selected.

因为是表空间是readonly的，数据文件这里需要修改一下名称

sys@BKT> alter database rename file'/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00008' to'/u02/oradat/bkt/tbs201.dbf' ;

Database altered.

sys@BKT> alter database rename file'/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007' to '/u02/oradat/bkt/tbs101.dbf';

Database altered.

sys@BKT> alter database datafile 7online ;

Database altered.

再次恢复

sys@BKT> recover database using backup controlfile ;

ORA-00279: change 1294029 generated at05/13/2013 14:42:58 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arc

ORA-00280: change 1294029 for thread 1 isin sequence #93

Specify log: {<RET>=suggested |filename | AUTO | CANCEL}

auto

ORA-00279: change 1294568 generated at05/13/2013 14:44:22 needed for thread 1

ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_94_8s2qoqjj_.arc

ORA-00280: change 1294568 for thread 1 isin sequence #94

ORA-00278: log file'/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arc'