一、控制文件
控制文件包括数据库的状态和数据库的物理结构信息,主要包括数据库名、数据库标识符(DBID)、数据库字符集、数据文件信息、临时文件信息、在线重做日志信息、表空间信息、rman信息、检查点信息、还原点信息。
在数据库mount阶段,数据库会打开控制文件读取数据文件和在线日志的路径,数据库在open状态会打开所有数据文件和在线日志。
在oracle 11g R2版本中,数据库安装完后控制文件默认有两个,如下:
SYS@orcl>show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/
fast_recovery_area/orcl/control02.ctl
所有的控制文件内容是相同的,它们之间互为在线副本或镜像。当进程读取控制文件内容时总是读取第一个控制文件,在上面的例子中是' /u01/app/oracle/oradata/orcl/control01.ctl'。当第一个控制文件损坏时,读写都会出错,当除第一个控制文件以外的其他控制文件出问题时,只有写操作会出错,读操作正常。但是只要其中任何一个出现问题,实例拒绝mount。一般系统都会有三个以上的控制文件,以避免控制文件的损坏,同时所有的控制文件应该跨磁盘、空磁盘控制器、跨存储的保存,避免物理单点故障。增加控制文件有以下4步:
1.设置control_files参数
SYS@orcl>alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control03.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' scope=spfile;
2.关闭数据库实例
SYS@orcl>shutdown immediate;
3.复制控制文件到conttrol_files中指定的新位置
SYS@orcl>host cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control03.ctl
4.启动数据库实例
SYS@orcl>startup;
SYS@orcl>show parameter control_files; ---显示已经增加的控制文件
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control03.ctl, /u
01/app/oracle/fast_recovery_ar
ea/orcl/control02.ctl
二、控制文件备份
控制文件除了上面所说的建立在线副本的方式外,还有自动备份和手动备份。
1、自动备份
自动备份又分为显式自动备份和隐式自动备份
1.1显示自动备份使用rman的configure命令设置'controlfile autobackup'为on即可,默认显示自动备份关闭,强烈推荐打开此功能。oracle会在下面两种时刻备份控制文件:
(1)rman命令执行任何备份操作成功后会自动备份控制文件。
(2)数据库增加表空间或者表空间添加数据文件。
例如:
RMAN> configure controlfile autobackup on; ---设置控制文件自动备份
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> backup datafile 4;
Starting backup at 28-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 28-DEC-12
channel ORA_DISK_1: finished piece 1 at 28-DEC-12
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_12_28/o1_mf_nnndf_TAG20121228T205157_8fv5hgxj_.bkp tag=TAG20121228T205157 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 28-DEC-12
Starting Control File and SPFILE Autobackup at 28-DEC-12 ---显示控制文件正在自动备份
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2012_12_28/o1_mf_s_803249526_8fv5hrok_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-DEC-12
RMAN> list backup of controlfile summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6 B F A DISK 28-DEC-12 1 1 NO TAG20121228T205205
SYS@orcl>create tablespace testt datafile '/u01/app/oracle/oradata/orcl/testt01.dbf' size 10m; ---新建表空间
Tablespace created.
RMAN> list backup of controlfile summary; ---查看控制文件的备份
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6 B F A DISK 28-DEC-12 1 1 NO TAG20121228T205205
7 B F A DISK 28-DEC-12 1 1 NO TAG20121228T210241
在oracle 10g R2中数据库物理结构发生改变会立即备份控制文件,oracle 11g R2中需要等待一段时间才会自动备份控制文件。
1.2隐式自动备份
如果rman中'controlfile autobackup'设置为off,只要使用rman命令备份system表空间中的第一个数据文件,也就是1号数据文件,控制文件就会自动备份。
例如:
RMAN> configure controlfile autobackup off; ---关闭控制文件自动备份
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
RMAN> backup datafile 1;
Starting backup at 28-DEC-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 28-DEC-12
channel ORA_DISK_1: finished piece 1 at 28-DEC-12
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_12_28/o1_mf_nnndf_TAG20121228T212202_8fv77vfy_.bkp tag=TAG20121228T212202 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set ---显示控制文件自动备份
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-DEC-12
channel ORA_DISK_1: finished piece 1 at 28-DEC-12
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_12_28/o1_mf_ncsnf_TAG20121228T212202_8fv791wx_.bkp tag=TAG20121228T212202 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-DEC-12
2、手动备份
2.1通过rman备份
RMAN> backup as backupset current controlfile; ---备份为备份集
RMAN> backup as copy current controlfile; ---备份为镜像备份
2.2通过sqlplus备份
SYS@orcl>alter database backup controlfile to '/home/oracle/control.20121228.bak' ---备份为镜像备份
RMAN> list copy of controlfile; ---上面的镜像备份可以通过list copy命令查看
List of Control File Copies
===========================
Key S Completion Time Ckp SCN Ckp Time
------- - --------------- ---------- ---------------
3 A 28-DEC-12 1380611 28-DEC-12
Name: /home/oracle/control.20121228.bak
Tag: TAG20121228T212923
2 A 28-DEC-12 1380480 28-DEC-12
Name: /u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_TAG20121228T212733_8fv7l5oj_.ctl
Tag: TAG20121228T212733
2.3重建脚本
SYS@orcl>alter database backup controlfile to trace;
在oracle 10g中trace文件保存在$ORACLE_BASE/admin/<sid>/udump/目录中,时间修改时间为最新时间。oracle 11g中通过v$diag_info视图查找trace文件的位置:
SYS@orcl>select value from v$diag_info where name='Default Trace File';
VALUE
------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30766.trc
在trace文件中包含两个重建控制文件的命令(create controfile ……),一个是不重设日志的noresetlogs,一个是重设日志的resetlogs。