|
|
一、什么是ControlFile
Control file体积虽小（主要由createdatabase的命令决定），但起到了很重要的作用，它定义了当前物理数据库的状态，并对数据库进行维护和管理。在启动数据库时进入mount状态需要使用control file。一个数据库对应一个control file，如果丢失了可能可以恢复。
[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ strings spfileorcl.ora | more
1
2
3
4
5
6
7
8
| orcl.__db_cache_size=96468992
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
...
*.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/flash_recovery_area/o
rcl/control02.ctl'
# 这里定义了控制文件的位置
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
|
[oracle@localhost dbs]$ cd /oracle/oradata/orcl
[oracle@localhost orcl]$ ls
1
2
3
| control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
# 根据SPFILE定义的路径可以找到control file
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
|
二、数据库启动过程中ControlFile的作用
Nomount状态不需要用到controlfile
Mount状态会根据PFILE或SPFILE中定义的路径打开control file，将其读入内存，然后根据control file获取数据文件的名字、状态和redo log files的相关信息
OPEN状态主要是读取一些体积较大的文件。
[oracle@localhost orcl]$ strings control01.ctl | more
1
2
3
4
| /oracle/oradata/orcl/redo03.log
...
/oracle/oradata/orcl/sysaux01.dbf
/oracle/oradata/orcl/example01.dbf
|
三、ControlFile的复用
关于ControlFile的说明，可以查阅联机文档中concept 和Administrator'sGuide的相关章节。
如果在初始化参数文件中定义了多个controlfiles（最多可以定义8个文件），那么oracle写信息时会同时向多个文件中写入数据，但是读数据时只读第一个文件。如果控制文件中的任何一个损坏了，则整个实例都无法操作了，这点和联机重做日志不一样。
Oracle强烈建议最好准备两个以上的controlfile，并存放在不同的磁盘上。
如何进行ControlFile的多路复用呢：
1. 使用SPFILE复用Control File
[oracle@localhost ~]$ sqlplus /nolog
1
2
3
| SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 923:25:46 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
|
SQL> conn / assysdba
1
2
3
4
5
6
7
8
9
10
| Connected.
SQL> showparameter control
NAME TYPE VALUE
------------------------------------ -------------------------------------------------------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/orcl/control01.ctl,
/oracle/flash_recovery_area/orcl/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
-- 查看当前control file的位置
|
SQL> alter system
1
2
3
4
| setcontrol_files='/oracle/oradata/orcl/control01.ctl','/oracle/flash_recovery_area/orcl/control02.ctl','/oracle/oradata/orcl/control03.ctl'scope=spfile;
-- 修改SPFILE
System altered.
|
SQL> shutdown immediate
1
2
3
4
| Database closed.
Database dismounted.
ORACLE instance shut down.
-- 关闭数据库
|
[oracle@localhost dbs]$ strings spfileorcl.ora | more
1
2
3
4
5
6
7
| orcl.__db_cache_size=96468992
orcl.__java_pool_size=4194304
...
*.compatible='11.2.0.0.0'
*.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/flash_recovery_area/o
rcl/control02.ctl','/oracle/oradata/orcl/control03.ctl'
# 可以看到SPFILE已经被修改了
|
[oracle@localhost dbs]$ cd /oracle/oradata/orcl
[oracle@localhost orcl]$ ls
1
2
| control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
|
[oracle@localhost orcl]$ cp control01.ctl ./control03.ctl
# 复制一份control file
SQL> startup
1
2
3
4
5
6
7
8
9
10
| -- 重新启动数据库
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 318769536 bytes
Database Buffers 96468992 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
|
SQL> showparameter control
1
2
3
4
5
6
7
| NAME TYPE VALUE
------------------------------- ----------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/orcl/control01.ctl , /oracle/flash_recovery_area/orcl /control02.ctl,
/oracle/oradata/orcl/control03.ctl
-- 再次查看，会发现多了一个control file
control_management_pack_access string DIAGNOSTIC+TUNING
|
2. 使用SPFILE复用Control File
使用PFILE和SPFILE 方法类似，只不过启动时指定特定的PFILE即可
SQL> createpfile from spfile;
1
2
| -- 通过PFILE创建SPFILE
File created.
|
SQL> shutdownimmediate
1
2
3
4
| Database closed.
Database dismounted.
ORACLE instance shut down.
-- 关闭数据库
|
[oracle@localhost ~]$ cd /oracle/11g/dbs
[oracle@localhost dbs]$ ls
1
2
3
| crdb01.sql hc_orcl.dat kWLY old peshm_smn_0 wly.env
...
spfileorcl.ora initorcl.ora
|
[oracle@localhost dbs]$ vim initorcl.ora
1
2
3
4
5
6
7
8
| orcl.__db_cache_size=96468992
...
*.compatible='11.2.0.0.0'
*.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/flash_recovery_area/orcl/control02.ctl','/oracle/oradata/orcl/control03.ctl','/oracle/oradata/orcl/control04.ctl'
# 在新建的PFILE中添加control04.ctl
*.db_block_size=8192
...
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
|
[oracle@localhost ~]$ cd /oracle/oradata/orcl
[oracle@localhost orcl]$ ls
1
2
3
4
| control01.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@localhost orcl]$ cp control01.ctl ./control04.ctl
# 复制一份control file，命名为control04.ctl
|
SQL> startuppfile=$ORACLE_HOME/dbs/initorcl.ora
1
2
3
4
5
6
7
8
9
10
| -- 指定PFILE来启动数据库
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 318769536 bytes
Database Buffers 96468992 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
|
SQL> showparameter control
1
2
3
4
5
6
7
8
9
10
| NAME TYPE VALUE
------------------------------------ -----------------------------------------------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/orcl/control01
.ctl, /oracle/flash_recovery_a
rea/orcl/control02.ctl,/oracl
e/oradata/orcl/control03.ctl,
/oracle/oradata/orcl/control04.ctl
-- 可以看到又多了一个control file
control_management_pack_access string DIAGNOSTIC+TUNING
|
3. 使用OMF来复用control file
使用OMF时，无需指定CONTROL_FILES这个参数，但是要指定DB_CREATE_ONLINE_LOG_DEST_n这个参数，启动数据库后，control file会创建在DB_CREATE_ONLINE_LOG_DEST_n这个参数指定的目录下。这样创建出来的control file名字是随机起的，通过查阅alertSID.log可以获取这个名字。目前主要以手动创建数据库为主，故不对OMF的使用详解。
四、管理ControlFile
有关control file的信息可以通过查询动态性能表V$CONTROLFILE,V$PARAMETER和V$CONTROLFILE_RECORD_SECTION获得，也可以使用SHOW PARAMETERCONTROL_FILES命令，建议使用SHOW PARAMETER CONTROL_FILES命令，可以避免输入错误，且支持通配符。
SQL> descv$controlfile;
1
2
3
4
5
6
7
| Name Null? Type
------------------------------------------------- ----------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBER
|
SQL> select *from v$controlfile;
1
2
3
4
5
6
| STATUS NAME IS_BLOCK_SIZE FILE_SIZE_BLKS
--------------------------------------------------------- ---------------------------------------------------------
/oracle/oradata/orcl/control01.ctl NO 16384 594
/oracle/flash_recovery_area/orcl/control02.ctl NO 16384 594
/oracle/oradata/orcl/control03.ctl NO 16384 594
/oracle/oradata/orcl/control04.ctl NO 16384 594
|
SQL> descv$controlfile_record_section;
1
2
3
4
5
6
7
8
9
| Name Null? Type
------------------------------------------------- ----------------------------
TYPE VARCHAR2(28)
RECORD_SIZE NUMBER
RECORDS_TOTAL NUMBER
RECORDS_USED NUMBER
FIRST_INDEX NUMBER
LAST_INDEX NUMBER
LAST_RECID NUMBER
|
SQL> selecttype, records_total, records_used from v$controlfile_record_section;
1
2
3
4
5
6
7
8
9
10
11
12
13
| TYPE RECORDS_TOTALRECORDS_USED
------------------------------------------------------------------------------------------------
DATABASE 1 1
CKPT PROGRESS 11 0
REDO THREAD 8 1
REDO LOG 16 3
DATAFILE 100 5
FILENAME 2298 9
...
ACM OPERATION 64 6
FOREIGN ARCHIVED LOG 1002 0
37 rows selected.
|
上表中列出的type的具体含义，可以通过查询联机文档Reference中的v$controlfile_record_section获得
五、Control File遗失情况演示
=======将controlfile移走========
[oracle@localhost dbs]$ cd /oracle/oradata/orcl
[oracle@localhost orcl]$ ls
1
2
| control01.ctl control04.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
control03.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
|
[oracle@localhost orcl]$ mkdir old
[oracle@localhost orcl]$ mv control0* old/
[oracle@localhost orcl]$ cd /oracle/flash_recovery_area/orcl/
[oracle@localhost orcl]$ ls
[oracle@localhost orcl]$ mkdir backup
[oracle@localhost orcl]$ mv control02.ctl backup/
========启动数据库========
SQL> startup
1
2
3
4
5
6
7
8
9
| ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 318769536 bytes
Database Buffers 96468992 bytes
Redo Buffers 6094848 bytes
ORA-00205: error in identifying control file, check alertlog for more info
-- 报错了，提示无法确认control file
|
=======查看alertSID.log=======
[oracle@localhost ~]$ cd /oracle/diag/rdbms/orcl/orcl/trace
[oracle@localhost trace]$ vim alert_orcl.log
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| Wed Sep 10 00:43:12 2014
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file:'/oracle/oradata/orcl/control03.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file:'/oracle/flash_recovery_area/orcl/control02.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/oradata/orcl/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
|
========关闭数据库=======
SQL> shutdown immediate
1
2
| ORA-01507: database not mounted
ORACLE instance shut down.
|
=========把controlfile 还原========
[oracle@localhost ~]$ cd /oracle/flash_recovery_area/orcl/
[oracle@localhost orcl]$ mv backup/control02.ctl ./
[oracle@localhost orcl]$ cd /oracle/oradata/orcl
[oracle@localhost orcl]$ mv old/control0* ./
===========再次启动数据库=========
SQL> startup
1
2
3
4
5
6
7
8
9
10
| ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 318769536 bytes
Database Buffers 96468992 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
-- 能够成功启动数据库了
|
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2014-9-25 13:41:23
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡