控制文件的功能和特点
记录数据库当前的物理状态
在mount阶段被读取的一个二进制文件
维护数据的一致性
记录RMAN备份的元数据
查看Control File的位置
以下三种方法均可用来查看控制文件的位置
- select name from v$controlfile;
- show parameter control_file
- select name,value from v$parameter where name like’%control_files%’;
idle>
idle>
idle>select name from v$controlfile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/orcl/control01.ctl
/opt/oracle/flash_recovery_area/orcl/control02.ctl
idle>
idle>
idle>
idle>show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /opt/oracle/oradata/orcl/contr
ol01.ctl, /opt/oracle/flash_re
covery_area/orcl/control02.ctl
idle>
idle>
idle>
idle>select name,value from v$parameter where name like'%control_files%';
NAME VALUE
------------------------------------ ------------------------------
control_files /opt/oracle/oradata/orcl/control01.ctl, /opt/oracle/flash_recovery_area/orcl/control02.ctl
控制文件多元化
配置多个control_files,控制文件最好有三个,是相互镜像的(shutdown下cp命令复制即可,然后修改spfile中的control_files参数),最多可以有8个control_files多路复用。
alter system set control_files=‘controlfile路径1’,‘controlfile路径2’ scope=spfile;
控制文件的重建与备份
-
trace文件备份(热备)
alter database backup controlfile to trace //生成的trace文件在dump的最新trc文件里
alter database backup controlfile to trace as ‘/opt/oracle/oradata/orcl/con.trace’; //存到自定义路径里
缺点:利用trace文件去备份控制文件,这样会生成全新的控制文件,之前的文件就没了 -
二进制文件备份(冷备)
alter database backup controlfile to ‘/opt/oracle/oradata/orcl/con.bak’;
什么情况下需要重建控制文件
1.所有当前控制文件丢失或损坏
2.需要改变只有重建控制文件才能改变的参数
如:MAZDATAFILES,MAXLOGFILES,MAXLOGHISTORY
3.从备份
实验-利用trace文件重建控制文件
我们来设计这样一个场景:
假设你将控制文件通过trace文件备份的方式将控制文件自定义在如下路径
/opt/oracle/oradata/orcl/
而后所有的控制文件全部损坏,或被误删了
开启数据库时,因在nomout阶段,要读取控制文件,而现在读取不到,数据库起不来了,怎么办?
我们可以利用备份好的trace文件来重建控制文件
1.将control file备份为trace文件
(1)执行如下语句进行备份control file,备份后的文件命名为con.trace
idle>alter database backup controlfile to trace as '/opt/oracle/oradata/orcl/con.trace';
Database altered.
(2)确认con.trace是否创建成功
[root@db01 oradata]# cd /opt/oracle/oradata/orcl/
[root@db01 orcl]# ll
total 1626740
-rw-r--r--. 1 oracle oinstall 5787 Jun 26 19:56 con.trace
-rw-r-----. 1 oracle oinstall 9748480 Jun 26 21:13 control01.ctl
-rw-r-----. 1 oracle oinstall 104865792 Jun 26 21:11 example01.dbf
-rw-r-----. 1 oracle oinstall 52429312 Jun 26 21:13 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 Jun 26 21:11 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 Jun 26 21:11 redo03.log
-rw-r-----. 1 oracle oinstall 566239232 Jun 26 21:11 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 723525632 Jun 26 21:11 system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Jun 26 19:13 temp01.dbf
-rw-r-----. 1 oracle oinstall 47194112 Jun 26 21:11 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 38019072 Jun 26 21:11 users01.dbf
[root@db01 orcl]#
(3)vim编辑con.trace文件,截取重建control file的命令语句,以备用
[root@db01 orcl]vim con.trace
注意:vim con.trace后截取如下命令粘贴至剪贴板备用,命令行间不能有空行,要手动修改
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/opt/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/opt/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/opt/oracle/oradata/orcl/system01.dbf',
'/opt/oracle/oradata/orcl/sysaux01.dbf',
'/opt/oracle/oradata/orcl/undotbs01.dbf',
'/opt/oracle/oradata/orcl/users01.dbf',
'/opt/oracle/oradata/orcl/example01.dbf'
CHARACTER SET WE8MSWIN1252
;
修改后如下
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/opt/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/opt/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/opt/oracle/oradata/orcl/system01.dbf',
'/opt/oracle/oradata/orcl/sysaux01.dbf',
'/opt/oracle/oradata/orcl/undotbs01.dbf',
'/opt/oracle/oradata/orcl/users01.dbf',
'/opt/oracle/oradata/orcl/example01.dbf'
CHARACTER SET WE8MSWIN1252
;
2.模拟control file丢失的场景
(1)查看control file路径
如下可知,我有两个control file,分别存于两个不同的路径下
idle>show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /opt/oracle/oradata/orcl/contr
ol01.ctl, /opt/oracle/flash_re
covery_area/orcl/control02.ctl
idle>
(2) cd 到对应的control file路径下更改control file的名字(模拟control file已丢失)
注意!!!:以小编的经历建议大家在作此实验时,不要在数据库startup状态下删除或更改control file
因为在数据库开启的状态下,无法正常shutdown数据库,也就无法进行后面步骤了
如果不小心真的在startup状态下改了control file名字
我是这么解决的:首先我把control file的名字改回去,然后去shutdown数据库
再重新模拟control file丢失的场景,就可以继续往下进行啦
到这里control file已备份好,并且control file已找不到了,接下来我们就来用备份好的trace文件来重建control file
3.重建control file
idle>startup force nomount //重建control file要在nomount状态下进行,因为startup 已经读取不 到control file了,固然起不来
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2213856 bytes
Variable Size 314574880 bytes
Database Buffers 88080384 bytes
Redo Buffers 4325376 bytes
idle>
idle>
idle>
idle>
idle>//执行上面剪贴板中备份好的重建control file的命令语句
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
ORA-01081: cannot start already-running ORACLE - shut it down first
idle> 2 MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/opt/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/opt/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/opt/oracle/oradata/orcl/system01.dbf',
'/opt/oracle/oradata/orcl/sysaux01.dbf',
'/opt/oracle/oradata/orcl/undotbs01.dbf',
'/opt/oracle/oradata/orcl/users01.dbf',
'/opt/oracle/oradata/orcl/example01.dbf'
CHARACTER SET WE8MSWIN1252
19 ;
Control file created. //到这里 control file就重建好了
idle>
idle>
idle>
idle>//到这里就可以进行一些操作了:
idle>
idle>show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /opt/oracle/oradata/orcl/contr
ol01.ctl, /opt/oracle/flash_re
covery_area/orcl/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
idle>
idle>
idle>
idle>
idle>
idle>
idle>
idle>select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1567620
2 1567620
3 1567620
4 1567620
5 1567620
idle>
idle>
idle>
idle>
idle>
idle>
idle> //最后一步,不要忘记把数据库开启
idle>alter database open;
Database altered.
idle>
idle>
idle>
idle>
idle>
idle>select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1567624
2 1567624
3 1567624
4 1567624
5 1567624
idle>
idle>
idle>