梦想有多远

控制文件是数据库中很重要的文件，它记录着数据库如下信息

数据库名字

数据文件和重做日志的名字和位置

数据库的创建时间

当前日志序列

检查点信息等

如果数据库没有控制文件，那么数据库将不能被MOUNT和恢复，所以一般需要创建多个副本

SQL> select name from v$controlfile;

NAME
—————————————————

F:\APP\BOSON.SUN\ORADATA\ORCL\CONTROL01.CTL
F:\APP\BOSON.SUN\ORADATA\ORCL\CONTROL02.CTL
F:\APP\BOSON.SUN\ORADATA\ORCL\CONTROL03.CTL

还有必须做好控制文件的备份工作，可以启用RMAN下的 控制文件自动备份功能：

我的机器上的数据库已经做了，但是如果你没有话可以通过如下命令

RMAN> configure controlfile autobackup on;

旧的 RMAN 配置参数:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
新的 RMAN 配置参数:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
已成功存储新的 RMAN 配置参数

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # de
fault

新的 RMAN 配置参数:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’;
已成功存储新的 RMAN 配置参数

然后 可以通过show all查看，

当数据库发生一下问题时，会备份数据库：

添加、删除、重命名数据文件时

添加、删除表空间时，或者更改表空间状态

添加、删除重做日志文件或组的时候

控制文件要特别注意的地方：

You can change the database name and DBID (internal database identifier) using the DBNEWID utility. See Oracle Database Utilities for information about using this utility.

The compatibility level is set to a value that is earlier than 10.2.0, and you must make a change to an area of database configuration that relates to any of the following parameters from the CREATE DATABASE or CREATE CONTROLFILE commands: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES. If compatibility is 10.2.0 or later, you do not have to create new control files when you make such a change; the control files automatically expand, if necessary, to accommodate the new configuration information.

For example, assume that when you created the database or recreated the control files, you set MAXLOGFILES to 3. Suppose that now you want to add a fourth redo log file group to the database with the ALTER DATABASE command. If compatibility is set to 10.2.0 or later, you can do so and the controlfiles automatically expand to accommodate the new logfile information. However, with compatibility set earlier than 10.2.0, your ALTER DATABASE command would generate an error, and you would have to first create new control files.

下面介绍 如何添加、重命名、重定位控制文件

关闭数据库后，copy控制文件，并修改想要的名字 。

然后修改服务器参数文件，修改类似如下的位置：

CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
                 /u02/oracle/prod/control02.ctl,
                 /u03/oracle/prod/control03.ctl)
创建控制文件类似如下命令：

CREATE CONTROLFILE    SET DATABASE prod    LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',                      '/u01/oracle/prod/redo01_02.log'),            GROUP 2 ('/u01/oracle/prod/redo02_01.log',                      '/u01/oracle/prod/redo02_02.log'),            GROUP 3 ('/u01/oracle/prod/redo03_01.log',                      '/u01/oracle/prod/redo03_02.log')     RESETLOGS    DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,             '/u01/oracle/prod/rbs01.dbs' SIZE 5M,             '/u01/oracle/prod/users01.dbs' SIZE 5M,             '/u01/oracle/prod/temp01.dbs' SIZE 5M    MAXLOGFILES 50    MAXLOGMEMBERS 3    MAXLOGHISTORY 400    MAXDATAFILES 200    MAXINSTANCES 6    ARCHIVELOG;

当然了此命令必须时数据库处于NOMOUNT状态：

Steps for Creating New Control Files

Complete the following steps to create a new control file.

1. Make a list of all datafiles and redo log files of the database.If you follow recommendations for control file backups as discussed in “Backing Up Control Files” , you will already have a list of datafiles and redo log files that reflect the current structure of the database. However, if you have no such list, executing the following statements will produce one.

   SELECT MEMBER FROM V$LOGFILE; SELECT NAME FROM V$DATAFILE;  SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';

   If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and redo log files that constitute the database. Any files not specified in step 5 are not recoverable once a new control file has been created. Moreover, if you omit any of the files that make up the SYSTEM tablespace, you might not be able to recover the database.

2. Shut down the database.If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT clauses only as a last resort.
3. Back up all datafiles and redo log files of the database.备份所有的数据文件和重做日志文件
4. Start up a new instance, but do not mount or open the database:

   STARTUP NOMOUNT 启动数据库到NOMOUNT状态

5. Create a new control file for the database using the CREATE CONTROLFILE statement.When creating a new control file, specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.这一步要注意RESETLOGS和NORESETLOGS，因为它决定你是否能进行数据的 完全还是不完全恢复
6. Store a backup of the new control file on an offline storage device. See “Backing Up Control Files” for instructions for creating a backup.
7. Edit the CONTROL_FILES initialization parameter for the database to indicate all of the control files now part of your database as created in step 5 (not including the backup control file). If you are renaming the database, edit the DB_NAME parameter in your instance parameter file to specify the new name.
8. Recover the database if necessary. If you are not recovering the database, skip to step 9.If you are creating the control file as part of recovery, recover the database. If the new control file was created using the NORESETLOGS clause (step 5), you can recover the database with complete, closed database recovery.

   If the new control file was created using the RESETLOGS clause, you must specify USING BACKUP CONTROL FILE. If you have lost online or archived redo logs or datafiles, use the procedures for recovering those files.

   See Also: recover database using backup control file

   Oracle Database Backup and Recovery Basics and Oracle Database Backup and Recovery Advanced User’s Guide for information about recovering your database and methods of recovering a lost control file

9. Open the database using one of the following methods:
   1. If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.

      ALTER DATABASE OPEN;

If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.

ALTER DATABASE OPEN RESETLOGS;

备份控制文件有如下几种方法：

一个是RMAN的自动备份

ALTER DATABASE BACKUP CONTROLFILE( TO '')

ALTER DATABASE BACKUP CONGTROLFILE TO TRACE

控制文件的恢复 ：

如果有备份的话：

restore controlfile to ''from autobackup 同样方式也可以恢复 服务器参数文件SPFILE

如果只是某个控制文件损坏就更简单了，

关闭数据库后COPE一个

% cp /u03/oracle/prod/control03.ctl  /u02/oracle/prod/control02.ctl

如果没有备份，都丢了的话，只能重建了。

这个案例不错

http://www.eygle.com/archives/2005/03/oeoeoeoeiaeoeei.html

以下是关于控制文件的一些视图：

The following views display information about control files:

 


View
Description

This entry was posted on 星期三, 十一月 28th, 2007 at 17:37 and is filed under oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.