控制文件多路复用时要注意的问题

由于控制文件对于数据库的重要性很高，所以通常在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方式，如果选择使用备份当前控制文件生成控制文件镜像的话，新生成的备份控制文件与数据库原有控制文件是不一致的