何时创建新的控制文件

在以下情况下,您需要创建新的控制文件:

    数据库的所有控制文件已被永久损坏,并且没有控制文件备份。

重建控制文件的核心步骤:

备份控制文件到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.


数据库现在已打开并可供使用。