何时创建新的控制文件
在以下情况下，您需要创建新的控制文件：
数据库的所有控制文件已被永久损坏，并且没有控制文件备份。
重建控制文件的核心步骤：
备份控制文件到trace
第一种情况：startup mount (如果能打开到mount状态)
alter database backup controlfile to trace as '/home/oracle/control.sql';
Set #1. NORESETLOGS case
The followingcommands will create a new control file and use it to open the database. Dataused by Recovery Manager will be lost.
Additional logsmay be required for media recovery of offline.
Use this only ifthe current versions of all online logs are available.
--使用noresetlogs仅是当前所有的online logs可用时。
Set #2. RESETLOGS case
The followingcommands will create a new control file and use it to open the database. Dataused by Recovery Manager will be lost.
The contents ofonline logs will be lost and all backups will be invalidated. Use this only ifonline logs are damaged.
--使用resetlogs，将导致online logs里的内容丢失，并且所有的备份失效，仅当online logs 随坏的情况下，才使用resetlos模式。
可以直接编辑control.sql中的内容，省去了查找logfile和datafile的麻烦
启动数据库到nomount状态
shutdown abort
startup nomount;
确认重建控制文件的语句
vi control.sql --主要是红色部分，
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ECOLOGY" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/data/ecology/ecology/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/data/ecology/ecology/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/data/ecology/ecology/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/data/ecology/ecology/system01.dbf',
'/data/ecology/ecology/sysaux01.dbf',
'/data/ecology/ecology/undotbs01.dbf',
'/data/ecology/ecology/users01.dbf',
'/data/ecology/ecology/ecology_01.dbf',
'/data1/ecology/ecology_01.dbf'
CHARACTER SET ZHS16GBK
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/data/ecology/ecology/temp01.dbf' REUSE;
附：实际解决过程如下：
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 218106944 bytes
Database Buffers 373293056 bytes
Redo Buffers 6287360 bytes
Database mounted.
SQL> alter database backup controlfile to trace as '/home/oracle/control.sql';
Database altered.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 218106944 bytes
Database Buffers 373293056 bytes
Redo Buffers 6287360 bytes
SQL> @control.sql
Control file created.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
第二种情况：无法启动到mount状态，此时control.sql无法创建，只能手动编辑 (主要区别在于是否手动编辑control.sql)
CREATE CONTROLFILE语句
创建新控制文件的步骤
完成以下步骤来创建一个新的控制文件
Oracle数据库SQL语言参考描述了CREATE CONTROLFILE语句的完整语法
列出数据库的所有数据文件和重做日志文件。
如果按照“备份控制文件”中所述遵循控制文件备份的建议，则您将拥有反映数据库当前结构的数据文件和重做日志文件的列表。但是，如果您没有这样的列表，执行以下语句将生成一个。
SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';
vi control.sql
CREATE CONTROLFILE REUSE DATABASE "ECOLOGY" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/data/ecology/ecology/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/data/ecology/ecology/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/data/ecology/ecology/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/data/ecology/ecology/system01.dbf',
'/data/ecology/ecology/sysaux01.dbf',
'/data/ecology/ecology/undotbs01.dbf',
'/data/ecology/ecology/users01.dbf',
'/data/ecology/ecology/ecology_01.dbf',
'/data1/ecology/ecology_01.dbf'
CHARACTER SET ZHS16GBK
;
启动一个新实例，但不安装或打开数据库：
STARTUP NOMOUNT
SQL> @control.sql
Control file created.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
数据库现在已打开并可供使用。
