由于控制文件对于数据库的重要性很高,所以通常在dbca建库以后,都会对控制文件多路复用。如,在oracle10g中,默认控制文件有3份,内容完全一致,通常位于$ORACLE_BASE/oradata/SID/下面,名字为
control01.ctl
,
control02.ctl
,
control03.ctl
,而到了11g,默认只有2个份,一个位于$ORACLE_BASE/oradata/SID/下面,名字为control01.ctl,另一份位于$ORACLE_BASE/fast_recovery_area/SID/下面,名字为control02.ctl。
当然了,我说的都是默认安装的情况下,完全可以通过在pfile中自定义不同的路径。
有时候,可能会碰到某些库只有1个控制文件,没有镜像,即没有对控制文件多路复用,显然这是不太好的,需要为数据库增加控制文件的镜像文件,下面来做个测试
--启动数据库,查看当前控制文件信息
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/zlm11g
/control01.ctl, /u01/app/oracl
e/fast_recovery_area/zlm11g/co
ntrol02.ctl
默认装完库以后,已经有2个控制文件,其中,control02.ctl是镜像
--查看control_file参数是否可以在线修改
SQL> col name for a20
SQL> select name,issys_modifiable from v$parameter where name='control_files';
NAME ISSYS_MOD
-------------------- ---------
control_files FALSE
显然,FALSE表示无法在线修改该参数,那么多路复用控制文件就意味着要重启数据库了
--创建pfile
SQL> create pfile from spfile;
File created.
--关闭数据库并修改pfile,增加控制文件镜像(在control_files参数后面添加镜像的位置、文件名)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
zlm11g.__db_cache_size=322961408
zlm11g.__java_pool_size=4194304
zlm11g.__large_pool_size=4194304
zlm11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
zlm11g.__pga_aggregate_target=335544320
zlm11g.__sga_target=503316480
zlm11g.__shared_io_pool_size=0
zlm11g.__shared_pool_size=163577856
zlm11g.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/zlm11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/zlm11g/control01.ctl','/u01/app/oracle/fast_recovery_area/zlm11g/control02.ctl',
'/u01/control03.ctl' --红色部分为新增的控制文件镜像
*.db_block_size=8192
zlm11g.db_create_file_dest='/u01/app/oracle/oradata/zlm11g/'
*.db_domain=''
*.db_name='zlm11g'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zlm11gXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=838860800
*.memory_target=838860800
*.open_cursors=300
"/u01/app/oracle/product/11.2.0/db_1/dbs/initzlm11g.ora" 27L, 1048C written
--在OS级别复制一份控制文件镜像
[oracle@zlm ~]$ cp $ORACLE_BASE/oradata/zlm11g/control01.ctl /u01/control03.ctl
[oracle@zlm ~]$ cd /u01
[oracle@zlm u01]$ ls
app bak control03.ctl dave.trc expdp rman.log rman.trc
[oracle@zlm u01]$ exit
exit
--用pfile启动数据库
SQL> startup pfile=$ORACLE_HOME/dbs/initzlm11g.ora
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/zlm11g
/control01.ctl, /u01/app/oracl
e/fast_recovery_area/zlm11g/co
ntrol02.ctl, /u01/control03.ct
新增的镜像控制文件已经生效,现在有3个控制文件了,一个原始文件,2路复用
--创建新的spfile
l
SQL> create spfile from pfile;
File created.
--关库并用spfile重新启动
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/zlm11g
/control01.ctl, /u01/app/oracl
e/fast_recovery_area/zlm11g/co
ntrol02.ctl, /u01/control03.ct
至此,已经完成了对控制文件的复用,从2份增加到了3份,注意,是必须停库的。
下面尝试用另一种方式来增加控制文件镜像,采用spfile动态修改+备份控制文件的方式,看是否可行
--修改spfile,添加第4个控制文件
SQL> alter system set control_files='/u01/app/oracle/oradata/zlm11g/control01.ctl,/u01/oracle/fast_recovery_area/zlm11g/control02.ctl,/u01/control03.ctl,/u01/control04.ctl' scope=spfile;
System altered.
--备份当前控制文件并生成镜像文件
SQL> alter database backup controlfile to '/u01/control04.ctl';
Database altered.
SQL> alter database backup controlfile to trace as '/u01/control.bak';
Database altered.
注意这两条命令的区别,前面的语句生成的是binary的控制文件,而后面的语句是生成可读的trace文件(通常用于重建控制文件)
--关闭数据库并重启
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
ORA-00205: error in identifying control file, check alert log for more info
遭遇ora-00205,可能是刚才设置的参数有问题,查看alert log来进一步分析
SQL> !tail -100f /u01/app/oracle/diag/rdbms/zlm11g/zlm11g/trace/alert_zlm11g.log
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Fri Nov 07 01:19:26 2014
Stopping background process VKTM
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Fri Nov 07 01:19:28 2014
Instance shutdown complete
Fri Nov 07 01:19:31 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: zlm
Release: 2.6.32-200.13.1.el5uek
Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine: x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfilezlm11g.ora
System parameters with non-default values:
processes = 150
memory_target = 800M
control_files = "/u01/app/oracle/oradata/zlm11g/control01.ctl,/u01/oracle/fast_recovery_area/zlm11g/control02.ctl,/u01/control03.ctl,/u01/control04.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_format = "%t_%s_%r.dbf"
db_create_file_dest = "/u01/app/oracle/oradata/zlm11g/"
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 4122M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=zlm11gXDB)"
audit_file_dest = "/u01/app/oracle/admin/zlm11g/adump"
audit_trail = "DB"
db_name = "zlm11g"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
Fri Nov 07 01:19:32 2014
PMON started with pid=2, OS id=30640
Fri Nov 07 01:19:32 2014
PSP0 started with pid=3, OS id=30644
Fri Nov 07 01:19:33 2014
VKTM started with pid=4, OS id=30996 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Fri Nov 07 01:19:33 2014
GEN0 started with pid=5, OS id=31003
Fri Nov 07 01:19:33 2014
DIAG started with pid=6, OS id=31007
Fri Nov 07 01:19:33 2014
DBRM started with pid=7, OS id=31011
Fri Nov 07 01:19:33 2014
DIA0 started with pid=8, OS id=31016
Fri Nov 07 01:19:33 2014
MMAN started with pid=9, OS id=31020
Fri Nov 07 01:19:33 2014
DBW0 started with pid=10, OS id=31024
Fri Nov 07 01:19:33 2014
LGWR started with pid=11, OS id=31028
Fri Nov 07 01:19:33 2014
CKPT started with pid=12, OS id=31033
Fri Nov 07 01:19:33 2014
SMON started with pid=13, OS id=31038
Fri Nov 07 01:19:33 2014
RECO started with pid=14, OS id=31042
Fri Nov 07 01:19:33 2014
MMON started with pid=15, OS id=31048
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Nov 07 01:19:33 2014
MMNL started with pid=16, OS id=31052
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Fri Nov 07 01:19:33 2014
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/zlm11g/control01.ctl,/u01/oracle/fast_recovery_area/zlm11g/control02.ctl,/u01/control03.ctl,/u01/control04.ctl'
--这里参数后面的引号写错了,应该每个镜像都要单独用引号
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri Nov 07 01:19:34 2014
Checker run found 1 new persistent data failures
--关闭数据库用pfile启动
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup pfile=$ORACLE_HOME/dbs/initzlm11g.ora
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
--创建spfile
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/zlm11g
/control01.ctl, /u01/app/oracl
e/fast_recovery_area/zlm11g/co
ntrol02.ctl, /u01/control03.ct
l
SQL> select table_name from dict where table_name like '%CONTROL%';
TABLE_NAME
------------------------------
DBA_HIST_WR_CONTROL
DBA_REPRESOL_STATS_CONTROL
USER_REPRESOL_STATS_CONTROL
ALL_REPRESOL_STATS_CONTROL
V$SESSION_FIX_CONTROL
V$SYSTEM_FIX_CONTROL
GV$DLM_TRAFFIC_CONTROLLER
GV$SESSION_FIX_CONTROL
GV$SYSTEM_FIX_CONTROL
V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION
V$DIAG_ADR_CONTROL
GV$CONTROLFILE
GV$CONTROLFILE_RECORD_SECTION
V$DLM_TRAFFIC_CONTROLLER
V$BACKUP_CONTROLFILE_DETAILS
V$BACKUP_CONTROLFILE_SUMMARY
17 rows selected.
SQL> desc v$controlfile
Name Null? Type
----------------------------------------- -------- ----------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBER
SQL> set lin 130
SQL> select status,name,is_recovery_dest_file,block_size,file_size_blks from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------- --- ---------- --------------
/u01/app/oracle/orad NO 16384 632
ata/zlm11g/control01
.ctl
/u01/app/oracle/fast NO 16384 632
_recovery_area/zlm11
g/control02.ctl
/u01/control03.ctl NO 16384 632
可以查看v$controlfile视图来获得当前控制文件信息,包括位置、名称、大小等
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/zlm11g
/control01.ctl, /u01/app/oracl
e/fast_recovery_area/zlm11g/co
ntrol02.ctl, /u01/control03.ct
l
由于刚才参数格式设置错误,此时control04.ctl还没有被正确修改生效,因此看不到
SQL> alter system set control_files='/u01/app/oracle/oradata/zlm11g/control01.ctl','/u01/app/oracle/fast_recovery_area/zlm11g/control02.ctl','/u01/control03.ctl','/u01/control04.ctl' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
ORA-00214: control file '/u01/app/oracle/oradata/zlm11g/control01.ctl' version 9318 inconsistent with file '/u01/control04.ctl'
version 9283
再次启动数据库发现,用指定路径方式备份控制文件与系统原有的控制文件不一致,版本号不同
猜想可能经过多次打开关闭数据库,现在需要重新创建一次第4个控制文件
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup pfile=$ORACLE_HOME/dbs/initzlm11g.ora
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> alter database backup controlfile to /u01/control04.ctl;
alter database backup controlfile to /u01/control04.ctl
*
ERROR at line 1:
ORA-02236: invalid file name
--是因为没有加引号
SQL> alter database backup controlfile to '/u01/control04.ctl';
alter database backup controlfile to '/u01/control04.ctl'
*
ERROR at line 1:
ORA-01580: error creating control backup file /u01/control04.ctl
ORA-27038: created file already exists
--文件已经存在,之前备份的文件并未删除
Additional information: 1
--删除文件后重新用备份创建控制文件镜像
SQL> !rm /u01/control04.ctl
SQL> alter database backup controlfile to '/u01/control04.ctl';
Database altered.
--关闭数据库后用spfile启动(原来是pfile启动的)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
ORA-00214: control file '/u01/app/oracle/oradata/zlm11g/control01.ctl' version 9338 inconsistent with file '/u01/control04.ctl'
version 9332
仍然报新增的控制文件版本号与系统原有的不一致。
此时猜想可能需要在spfile中先设置好参数后,再生成控制文件备份作为镜像
--先恢复正确的spfile
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup pfile=$ORACLE_HOME/dbs/initzlm11g.ora
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
File created.
SQL> shutdown immedaite
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
--设置control_files参数
SQL> alter system set control_files='/u01/app/oracle/oradata/zlm11g/control01.ctl','/u01/app/oracle/fast_recovery_area/zlm11g/control02.ctl','/u01/control03.ctl','/u01/control04.ctl' scope=spfile;
System altered.
--备份控制文件作为镜像
SQL> !rm /u01/control04.ctl
SQL> alter database backup controlfile to '/u01/control04.ctl';
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
ORA-00214: control file '/u01/app/oracle/oradata/zlm11g/control01.ctl' version 9373 inconsistent with file '/u01/control04.ctl'
version 9367
仍然是不一致的状态,看来之前的假设都被推翻了,用backup controlfile方式备份出的控制文件,与系统原有的控制文件,是不同的,无法做到像OS那样直接复制来得直接
总结:
1.修改静态参数需要重启数据库,然后通过pfile来指定新增的控制文件镜像,最后用pfile启动,并生成spfile
2.复制控制文件镜像文件时,停库后尽量用OS级copy方式,如果选择使用备份当前控制文件生成控制文件镜像的话,新生成的备份控制文件与数据库原有控制文件是不一致的