什么是Control file

每个Oracle database都有控制文件， 是一个很小的二进制文件,存储了数据库的物理结构。

内容包括

• The database name
• datafile, redo log file位置和文件名
• The timestamp of the database creation
• The current log sequence number
• Checkpoint information

SQL>show parameter control

control_files     

string     

/u01/app/oracle/oradata/orcl/control01.ctl,

/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

CONTROL_FILES初始化参数

CONTROL_FILES列出了所有的Oracle  Control file,Oracle数据库启动时打开所有Control Files

Multiplex Control Files on Different Disks就是Oracle必须有至少两个control file, 每个control file放在自己的disk上。

每次修改datafiles, tablespace, redo log files or group都要备份Control files

Control file的文件大小主要由这些初始化参数决定MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES

创建Control files

初次创建由CREATE DATABASE基于初始化参数CONTROL_FILES来创建control files

增加，删除，移动，重命名Control file都是文件操作，操作完成以后都要修改CONTROL_FILES初始化参数

步骤

• shutdown database
• OS copy, move, delete operation
• edit CONTROL_FILES参数
• restart database

手动创建CONTROL_FILE的命令

CREATECONTROLFILE

SETDATABASE prod

LOGFILEGROUP 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

MAXLOGFILES50

MAXLOGMEMBERS3

MAXLOGHISTORY400

MAXDATAFILES200

MAXINSTANCES6

ARCHIVELOG;

具体步骤

1.查找redolog, data file文件的SQL查询

SELECT MEMBER FROMV$LOGFILE;

SELECT NAME FROMV$DATAFILE;

SELECT VALUE FROM V$PARAMETER WHERE NAME =‘control_files‘;

2.shutdown database

3.backup all datafiles and log files

4.startup nomount

5.用上面的CREATECONTROLFILES命令创建control files

6.备份刚创建的Controlfile

7.修改CONTROL_FILES初始化参数文件

8.恢复数据库，如果有需要的话，否则直接下一步

9.ALTER DATABASE OPEN or ALTER DATABASE OPENRESETLOGS;

备份Controlfile

ALTER DATABASE BACKUP CONTROLFILE TO‘/oracle/backup/control.bkp‘;

生成一份CREATE CONTROLFILES语句到TRACElog,用于以后创建control files

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Control Fie相关View

• V$DATABASE
• V$CONTROLFILE
• V$CONTROLFILE_RECORD_SECTION
• V$PARAMETER