Oracle在丢失控制文件的情况下，如何恢复数据库重建控制文件？

控制文件对于数据库来说是非常重要，在进行数据恢复时通常是必不可少的;丢失控制文件并不是致命的，但是会使恢复变得困难很多。因此，dba除了多路保存控制文件外，还需要备份控制文件。

备份控制文件也有多种办法，例如cp拷贝、rman自动备份等，今天给大家介绍如何获取创建控制文件的脚本，这样数据库一旦出现意外，即使找不到可用的备份控制文件，也能通过重建控制文件来启动数据库。

Oracle提供了如下命令来实现这个功能：

alter database backup controlfile to trace;

下面将介绍在丢失控制文件的情况下如何恢复数据库。

测试环境：Linux6.5+Oracle 11g

1.环境准备

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> show parameter control_files

打开数据库，执行‘alter database backup controlfile to trace;’语句来生成trace文件。

NAME TYPE VALUE

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

control_files string /data/CEBPM/controlfile/o1_mf_

dm1flhj3_.ctl

2.生成trace文件

SQL> alter database backup controlfile to trace;

Database altered.

3.查找trace文件路径

trace文件的路径可以通过一个多表查询来获得，其中涉及v$processv、$sessionv和$mystat等三个动态视图。

SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

TRACEFILE

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

/u01/app/oracle/diag/rdbms/cebpm/cebpm/trace/cebpm_ora_2225.trc

SQL>

4.查看trace文件

通过cat命令查看trace文件，内容如下：

......

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "CEBPM" NORESETLOGS FORCE LOGGING ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 &＃039;/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log&＃039; SIZE 50M BLOCKSIZE 512,

GROUP 2 &＃039;/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log&＃039; SIZE 50M BLOCKSIZE 512,

GROUP 3 &＃039;/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log&＃039; SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

-- GROUP 11 &＃039;/data/CEBPM/onlinelog/redo11_stb01.log&＃039; SIZE 50M BLOCKSIZE 512,

-- GROUP 12 &＃039;/data/CEBPM/onlinelog/redo12_stb01.log&＃039; SIZE 50M BLOCKSIZE 512,

-- GROUP 13 &＃039;/data/CEBPM/onlinelog/redo13_stb01.log&＃039; SIZE 50M BLOCKSIZE 512,

-- GROUP 14 &＃039;/data/CEBPM/onlinelog/redo14_stb01.log&＃039; SIZE 50M BLOCKSIZE 512

DATAFILE

&＃039;/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf&＃039;,

&＃039;/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf&＃039;,

&＃039;/data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf&＃039;,

&＃039;/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf&＃039;

CHARACTER SET AL32UTF8

;

......

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "CEBPM" RESETLOGS FORCE LOGGING ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 &＃039;/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log&＃039; SIZE 50M BLOCKSIZE 512,

GROUP 2 &＃039;/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log&＃039; SIZE 50M BLOCKSIZE 512,

GROUP 3 &＃039;/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log&＃039; SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

&＃039;/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf&＃039;,

&＃039;/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf&＃039;,

&＃039;/data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf&＃039;,

&＃039;/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf&＃039;

CHARACTER SET AL32UTF8

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE &＃039;/data/CEBPM/archivelog/1_1_945593423.arc&＃039;;

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE &＃039;/data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp&＃039;

SIZE 61865984 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions

.....

5.NORESETLOGS VS RESETLOGS

从trace文件可以看出，提供了两个创建控制文件的脚本，分别针对两种不同的应用场景：NORESETLOGS适用于当前redo log可用，而RESETLOGS适用于当前redo log不可用。

NORESETLOGS重建步骤

(1).执行脚本，重建控制文件(CREATE CONTROLFILE REUSE DATABASE "CEBPM NORESETLOGS NOARCHIVELOG......)

(2).执行介质恢复RECOVER DATABASE

(3).打开数据库ALTER DATABASE OPEN;

(4).添加临时表空间

RESETLOGS重建步骤

(1).执行脚本，重建控制文件(CREATE CONTROLFILE REUSE DATABASE "CEBPM" RESETLOGS NOARCHIVELOG......)

(2).执行介质恢复RECOVER DATABASE USING BACKUP CONTROLFILE

(3).打开数据库ALTER DATABASE OPEN RESETLOGS;

(4).添加临时表空间

6.删除控制文件

根据上面查到的控制文件的路径利用rm将其删除

cebpm:[email&＃160;protected]>rm -rf o1_mf_dm1flhj3_.ctl

此时，强制关闭数据库，然后重启数据库，报ORA-00205错误。需要注意的是，此时执行shutdown immediate命令，数据库无法正常关闭，只能关闭到mounted状态;需要使用shutdown abort命令强制关闭数据库。

SQL> shutdown immediate

ORA-00210: cannot open the specified control file

ORA-00202: control file: &＃039;/data/CEBPM/controlfile/o1_mf_dm1flhj3_.ctl&＃039;

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

ORA-00205: error in identifying control file, check alert log for more info

启动到nomount状态

SQL> startup nomount

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

利用刚才产生的trace里的控制文件创建脚本(如果事先没有产生创建控制文件trace文件，那么这一步则需要自己手动构造控制文件脚本)

cebpm:[email&＃160;protected]>vi createcontrofile.sql

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "CEBPM" NORESETLOGS FORCE LOGGING ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 &＃039;/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log&＃039; SIZE 50M,

GROUP 2 &＃039;/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log&＃039; SIZE 50M,

GROUP 3 &＃039;/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log&＃039; SIZE 50M

DATAFILE

&＃039;/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf&＃039;,

&＃039;/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf&＃039;,

&＃039;/data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf&＃039;,

&＃039;/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf&＃039;

CHARACTER SET AL32UTF8

;

cebpm:[email&＃160;protected]>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 19 08:02:56 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> @/u01/createcontrofile.sql

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

Control file created.

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: &＃039;/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf&＃039;

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

SQL> select name from v$datafile;

NAME

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

/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf

/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf

/data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf

/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf

SQL> select member from v$logfile;

MEMBER

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

/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log

/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log

/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log

SQL> select name from v$controlfile;

NAME

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

/data/CEBPM/controlfile/o1_mf_dm1flhj3_.ctl

SQL> select name from v$tempfile;

no rows selected

添加临时表空间

SQL> alter tablespace temp add tempfile &＃039;/data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp&＃039;;

Tablespace altered.

SQL> select name from v$tempfile;

NAME

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

/data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

SQL> alter system archive log current;

System altered.