控制文件对于数据库来说是非常重要，在进行数据恢复时通常是必不可少的;丢失控制文件并不是致命的，但是会使恢复变得困难很多。因此，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 ‘/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log’ SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE
— GROUP 11 ‘/data/CEBPM/onlinelog/redo11_stb01.log’ SIZE 50M BLOCKSIZE 512,
— GROUP 12 ‘/data/CEBPM/onlinelog/redo12_stb01.log’ SIZE 50M BLOCKSIZE 512,
— GROUP 13 ‘/data/CEBPM/onlinelog/redo13_stb01.log’ SIZE 50M BLOCKSIZE 512,
— GROUP 14 ‘/data/CEBPM/onlinelog/redo14_stb01.log’ SIZE 50M BLOCKSIZE 512
DATAFILE
‘/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’
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 ‘/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log’ SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/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’
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 ‘/data/CEBPM/archivelog/1_1_945593423.arc’;
— 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 ‘/data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp’
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:/data/CEBPM/controlfile@myoracle>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: ‘/data/CEBPM/controlfile/o1_mf_dm1flhj3_.ctl’
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:/u01@myoracle>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 ‘/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log’ SIZE 50M,
GROUP 2 ‘/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log’ SIZE 50M,
GROUP 3 ‘/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log’ SIZE 50M
DATAFILE
‘/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’
CHARACTER SET AL32UTF8
;
cebpm:/u01@myoracle>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: ‘/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf’
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 ‘/data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp’;
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.