大家都知道，数据库启动有几个阶段：closed → nomount → mount → open。每个阶段都用到了不同的文件，其中：
从closed →nomount 读取的是spfile或pfile参数文件，在此过程中，分配SGA和后台进程。
从nomount → mount 读取的是control file控制文件，在mount状态，可以调整数据库的归档模式，并能做备份和恢复动作。
从mount → open 读取的是联机数据文件和redo 文件。
所以当control file出现问题时，数据库启动会遇到什么问题，接下来做个试验：
一、模拟control file丢失环境
（关闭数据库拷贝控制文件，开启数据库执行DML操作后关闭数据库，备份最新的控制文件，并将旧的控制文件覆盖到新的控制文件中）
1.1 本数据库控制文件指定了两个镜像，现仅覆盖其中一个控制文件：
1.1.1 关闭数据库：
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
1.1.2 备份控制文件为control01.ctl.bak、control02.ctl.bak：
[oracle@localhost ~]$ cd /oracle/app/oracle/oradata/ [oracle@localhost oradata]$ ls mydb [oracle@localhost oradata]$ cd mydb/ [oracle@localhost mydb]$ ls control01.ctl example01.dbf redo01.log redo02.log redo04.log system01.dbf temp02.dbf test1.dbf users01.dbf control02.ctl example02.dbf redo0201.log redo03.log sysaux01.dbf temp01.dbf temp03.dbf undotbs02.dbf users02.dbf [oracle@localhost mydb]$ cp control01.ctl control01.ctl.bak [oracle@localhost mydb]$ cp control02.ctl control02.ctl.bak [oracle@localhost mydb]$ ls control01.ctl control02.ctl example01.dbf redo01.log redo02.log redo04.log system01.dbf temp02.dbf test1.dbf users01.dbf control01.ctl.bak control02.ctl.bak example02.dbf redo0201.log redo03.log sysaux01.dbf temp01.dbf temp03.dbf undotbs02.dbf users02.dbf [oracle@localhost mydb]$ cd ~
1.1.3 打开数据库，进行DML操作：
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 23 18:19:14 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 503319672 bytes Database Buffers 318767104 bytes Redo Buffers 6586368 bytes Database mounted. Database opened. SQL> create table hr.test033002 (test varchar2(10),num int); Table created. SQL> insert into hr.test033002 values('ubin',1); 1 row created. SQL> commit; Commit complete. SQL> update hr.test033002 set test='dftugyoi' where num=1; 1 row updated. SQL> commit; Commit complete.
1.1.4 关闭数据库：
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
1.1.5 备份最新的控制文件为 control01.ctl.bak1、control02.ctl.bak1：
[oracle@localhost ~]$ cd $ORACLE_BASE/oradata/mydb/ [oracle@localhost mydb]$ ls control01.ctl control02.ctl example01.dbf redo01.log redo02.log redo04.log system01.dbf temp02.dbf test1.dbf users01.dbf control01.ctl.bak control02.ctl.bak example02.dbf redo0201.log redo03.log sysaux01.dbf temp01.dbf temp03.dbf undotbs02.dbf users02.dbf [oracle@localhost mydb]$ cp control01.ctl control01.ctl.bak1 [oracle@localhost mydb]$ cp control02.ctl control02.ctl.bak1 [oracle@localhost mydb]$ ls control01.ctl control01.ctl.bak1 control02.ctl.bak example01.dbf redo01.log redo02.log redo04.log system01.dbf temp02.dbf test1.dbf users01.dbf control01.ctl.bak control02.ctl control02.ctl.bak1 example02.dbf redo0201.log redo03.log sysaux01.dbf temp01.dbf temp03.dbf undotbs02.dbf users02.dbf
1.1.6 将第一次备份的控制文件覆盖，仅覆盖control.ctl：
[oracle@localhost mydb]$ rm control01.ctl [oracle@localhost mydb]$ cp control01.ctl.bak control01.ctl [oracle@localhost mydb]$ cd ~
1.1.7 此时，登录sys用户，执行startup,观察:
[oracle@localhost mydb]$ cd ~ [oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 23 18:21:58 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 503319672 bytes Database Buffers 318767104 bytes Redo Buffers 6586368 bytes ORA-00214: control file '/oracle/app/oracle/oradata/mydb/control02.ctl' version 2642138 inconsistent with file '/oracle/app/oracle/oradata/mydb/control01.ctl' version 2642121 SQL> select open_mode from v$database; select open_mode from v$database * ERROR at line 1: ORA-01507: database not mounted
发现在启动数据库时，抛出ora-00214报错，控制文件版本不一致问题，且数据库状态为nomount。
1.2 本数据库控制文件指定了两个镜像，现全部覆盖控制文件：
1.2.1 基于1.1全部操作后续，继续覆盖control02.ctl
SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@localhost ~]$ cd $ORACLE_BASE/oradata/mydb [oracle@localhost mydb]$ ls control01.ctl control01.ctl.bak1 control02.ctl.bak example01.dbf redo01.log redo02.log redo04.log system01.dbf temp02.dbf test1.dbf users01.dbf control01.ctl.bak control02.ctl control02.ctl.bak1 example02.dbf redo0201.log redo03.log sysaux01.dbf temp01.dbf temp03.dbf undotbs02.dbf users02.dbf [oracle@localhost mydb]$ rm control02.ctl [oracle@localhost mydb]$ cp control02.ctl.bak control02.ctl [oracle@localhost mydb]$ ls control01.ctl control01.ctl.bak1 control02.ctl.bak example01.dbf redo01.log redo02.log redo04.log system01.dbf temp02.dbf test1.dbf users01.dbf control01.ctl.bak control02.ctl control02.ctl.bak1 example02.dbf redo0201.log redo03.log sysaux01.dbf temp01.dbf temp03.dbf undotbs02.dbf users02.dbf [oracle@localhost mydb]$ cd ~
1.2.2 登录sys用户，执行startup 观察结果：
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 23 18:23:10 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 503319672 bytes Database Buffers 318767104 bytes Redo Buffers 6586368 bytes Database mounted. ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/oracle/app/oracle/oradata/mydb/system01.dbf' ORA-01207: file is more recent than control file - old control file SQL> select status from v$instance; STATUS ------------------------ MOUNTED
发现在启动数据库时，抛出 ORA-01122，ORA-01110，ORA-01207错误，且此时数据库状态为mount。
二、进行恢复control 控制文件，开启数据库
通过mos文档，可参考文档：
ORA-1122, ORA-1110, ORA-1207 while open the database after crash (文档 ID 283927.1)
How to Recreate a Controlfile (文档 ID 735106.1)
第一步：将数据库打开至mount状态
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 23 18:52:50 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 503319672 bytes Database Buffers 318767104 bytes Redo Buffers 6586368 bytes Database mounted.
第二步：将控制文件的创建语句放在trace里：
SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_7350.trc
第三步：查看trace文件，提取语句
[oracle@localhost ~]$ more /oracle/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_7350.trc Trace file /oracle/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_7350.trc Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1 System name: Linux Node name: localhost.localdomain Release: 2.6.32-504.el6.x86_64 Version: #1 SMP Tue Sep 16 01:56:35 EDT 2014 Machine: x86_64 VM name: VMWare Version: 6 Instance name: mydb Redo thread mounted by this instance: 1 Oracle process number: 19 Unix process pid: 7350, image: oracle@localhost.localdomain (TNS V1-V3) *** 2018-02-23 18:57:48.660 *** SESSION ID:(125.3) 2018-02-23 18:57:48.660 *** CLIENT ID:() 2018-02-23 18:57:48.660 *** SERVICE NAME:() 2018-02-23 18:57:48.660 *** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2018-02-23 18:57:48.660 *** ACTION NAME:() 2018-02-23 18:57:48.660 -- The following are current System-scope REDO Log Archival related -- parameters and can be included in the database initialization file. -- -- LOG_ARCHIVE_DEST='' -- LOG_ARCHIVE_DUPLEX_DEST='' -- -- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf -- -- DB_UNIQUE_NAME="mydb" -- -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' -- LOG_ARCHIVE_MAX_PROCESSES=4 -- STANDBY_FILE_MANAGEMENT=MANUAL -- STANDBY_ARCHIVE_DEST=?/dbs/arch -- FAL_CLIENT='' -- FAL_SERVER='' -- -- LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch' -- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY' -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY' -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' -- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' -- LOG_ARCHIVE_DEST_STATE_1=ENABLE -- -- Below are two sets of SQL statements, each of which creates a new -- control file and uses it to open the database. The first set opens -- the database with the NORESETLOGS option and should be used only if -- the current versions of all online logs are available. The second -- set opens the database with the RESETLOGS option and should be used -- if online logs are unavailable. -- The appropriate set of statements can be copied from the trace into -- a script file, edited as necessary, and executed when there is a -- need to re-create the control file. -- -- Set #1. NORESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- Additional logs may be required for media recovery of offline -- Use this only if the current versions of all online logs are -- available. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "MYDB" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 584 LOGFILE GROUP 1 '/oracle/app/oracle/oradata/mydb/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/oracle/app/oracle/oradata/mydb/redo02.log' SIZE 100M BLOCKSIZE 512, GROUP 3 '/oracle/app/oracle/oradata/mydb/redo03.log' SIZE 50M BLOCKSIZE 512, GROUP 4 '/oracle/app/oracle/oradata/mydb/redo04.log' SIZE 100M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/oracle/app/oracle/oradata/mydb/system01.dbf', '/oracle/app/oracle/oradata/mydb/sysaux01.dbf', '/oracle/app/oracle/oradata/mydb/users02.dbf', '/oracle/app/oracle/oradata/mydb/users01.dbf', '/oracle/app/oracle/oradata/mydb/example01.dbf', '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/bigtbs_f2.dbf', '/oracle/app/oracle/oradata/mydb/example02.dbf', '/oracle/app/oracle/oradata/mydb/test1.dbf', '/oracle/app/oracle/oradata/mydb/undotbs02.dbf' CHARACTER SET WE8MSWIN1252 ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames
第四步：创建生成控制文件的脚本：
[oracle@localhost ~]$ vi control.sql CREATE CONTROLFILE REUSE DATABASE "MYDB" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 584 LOGFILE GROUP 1 '/oracle/app/oracle/oradata/mydb/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/oracle/app/oracle/oradata/mydb/redo02.log' SIZE 100M BLOCKSIZE 512, GROUP 3 '/oracle/app/oracle/oradata/mydb/redo03.log' SIZE 50M BLOCKSIZE 512, GROUP 4 '/oracle/app/oracle/oradata/mydb/redo04.log' SIZE 100M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/oracle/app/oracle/oradata/mydb/system01.dbf', '/oracle/app/oracle/oradata/mydb/sysaux01.dbf', '/oracle/app/oracle/oradata/mydb/users02.dbf', '/oracle/app/oracle/oradata/mydb/users01.dbf', '/oracle/app/oracle/oradata/mydb/example01.dbf', '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/bigtbs_f2.dbf', '/oracle/app/oracle/oradata/mydb/example02.dbf', '/oracle/app/oracle/oradata/mydb/test1.dbf', '/oracle/app/oracle/oradata/mydb/undotbs02.dbf' CHARACTER SET WE8MSWIN1252 ; ~ ~ "control.sql" [New] 24L, 1015C written
第五步：将数据库打开到nomount状态，并执行生成控制文件的脚本：
SQL> startup nomount; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 503319672 bytes Database Buffers 318767104 bytes Redo Buffers 6586368 bytes SQL> @control.sql Control file created
第六步：更改数据库状态为open，并查看数据库状态和表空间状态信息：
SQL> alter database open; Database altered. SQL> select status from v$instance; STATUS ------------------------ OPEN SQL> select open_mode from v$database; OPEN_MODE ---------------------------------------- READ WRITE SQL> col TABLESPACE_NAME for a20 SQL> select TABLESPACE_NAME,STATUS,CONTENTS from dba_tablespaces; TABLESPACE_NAME STATUS CONTENTS -------------------- ------------------ ------------------ SYSTEM ONLINE PERMANENT SYSAUX ONLINE PERMANENT TEMP ONLINE TEMPORARY USERS ONLINE PERMANENT UNDOTBS2 ONLINE UNDO EXAMPLE ONLINE PERMANENT BIGTBS_02 ONLINE PERMANENT TBS_TEMP_02 ONLINE TEMPORARY TEMP_DEMO ONLINE TEMPORARY TEST ONLINE PERMANENT 10 rows selected.
此时，数据库已恢复正常。
其中、mos文档中还指出，需要创建临时表空间
ALTER TABLESPACE TEMP_TEST ADD TEMPFILE '/oradata/V11/temp01.dbf' reuse;
这个应该是因情况不同，本次实验不需要。
暂时记录到这里啦，接下来可以研究下数据文件丢失时如何恢复。
