OCP学习笔记OMF添加控制文件（注意事项）----------OMF创建控制和OFA文件准则文件
网上有篇关于创建控制文件OMF的文章，经过测试出现了一些问题。现总结如下
原文如下
DBCA选择OMF管理方式，但没有添加多路复用。即
首先查看日志文件的路径，
使用db_create_oline_log_dest_n 指定多路复用日志文件和控制文件
SYS@orcl/29-MAR-11>@log_file.sql
FILE GROUP# SEQUENCE# SIZE MEMBERS ARCHIVED STATUS
------------------------------------------------------------ ---------- --------- ------ ------- ---------- ----------
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf4fn_.log 1 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1xs_.log 1 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1kw_.log 1 0 512 4 YES UNUSED
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf75t_.log 1 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0swpph_.log 4 124 512 3 YES INACTIVE
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0swpb3_.log 4 124 512 3 YES INACTIVE
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0sws7z_.log 4 124 512 3 YES INACTIVE
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxp3v_.log 5 125 512 3 YES INACTIVE
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxmk8_.log 5 125 512 3 YES INACTIVE
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxm53_.log 5 125 512 3 YES INACTIVE
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy2js_.log 6 126 512 3 NO CURRENT
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy23s_.log 6 126 512 3 NO CURRENT
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy536_.log 6 126 512 3 NO CURRENT
13 rows selected.
Elapsed: 00:00:00.01
SYS@orcl/29-MAR-11>alter database drop logfile group 4;
Database altered.
Elapsed: 00:00:00.56
SYS@orcl/29-MAR-11>alter database drop logfile group 5;
Database altered.
Elapsed: 00:00:00.57
SYS@orcl/29-MAR-11>alter database add logfile group 2;
Database altered.
Elapsed: 00:00:09.91
SYS@orcl/29-MAR-11>alter database add logfile group 3;
Database altered.
Elapsed: 00:00:10.93
SYS@orcl/29-MAR-11>alter database add logfile group 4;
Database altered.
Elapsed: 00:00:10.33
SYS@orcl/29-MAR-11>alter system switch logfile;
System altered.
Elapsed: 00:00:00.04
SYS@orcl/29-MAR-11>@log_file.sql
FILE GROUP# SEQUENCE# SIZE MEMBERS ARCHIVED STATUS
------------------------------------------------------------ ---------- --------- ------ ------- ---------- ----------
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf75t_.log 1 127 512 4 NO CURRENT
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1kw_.log 1 127 512 4 NO CURRENT
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1xs_.log 1 127 512 4 NO CURRENT
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf4fn_.log 1 127 512 4 NO CURRENT
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgmwy_.log 2 0 512 4 YES UNUSED
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgkb0_.log 2 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggfn_.log 2 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggt4_.log 2 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgwm8_.log 3 0 512 4 YES UNUSED
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgz44_.log 3 0 512 4 YES UNUSED
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rh2gp_.log 3 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgw7m_.log 3 0 512 4 YES UNUSED
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhm7p_.log 4 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfst_.log 4 0 512 4 YES UNUSED
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhjdw_.log 4 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfdv_.log 4 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy23s_.log 6 126 512 3 YES ACTIVE
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy2js_.log 6 126 512 3 YES ACTIVE
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy536_.log 6 126 512 3 YES ACTIVE
19 rows selected.
SYS@orcl/29-MAR-11>alter database drop logfile group 6;
Database altered.
Elapsed: 00:00:00.57
SYS@orcl/29-MAR-11>@log_file.sql
FILE GROUP# SEQUENCE# SIZE MEMBERS ARCHIVED STATUS
------------------------------------------------------------ ---------- --------- ------ ------- ---------- ----------
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1kw_.log 1 127 512 4 YES INACTIVE
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1xs_.log 1 127 512 4 YES INACTIVE
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf4fn_.log 1 127 512 4 YES INACTIVE
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf75t_.log 1 127 512 4 YES INACTIVE
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgmwy_.log 2 128 512 4 YES INACTIVE
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgkb0_.log 2 128 512 4 YES INACTIVE
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggfn_.log 2 128 512 4 YES INACTIVE
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggt4_.log 2 128 512 4 YES INACTIVE
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgw7m_.log 3 129 512 4 NO CURRENT
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgz44_.log 3 129 512 4 NO CURRENT
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rh2gp_.log 3 129 512 4 NO CURRENT
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgwm8_.log 3 129 512 4 NO CURRENT
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhm7p_.log 4 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfdv_.log 4 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfst_.log 4 0 512 4 YES UNUSED
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhjdw_.log 4 0 512 4 YES UNUSED
16 rows selected.
总结：上述的作用是在新的路径创建联机日志的多路复用，以及将日志文件组的序号由1 4 5 6 修改为1 2 3 4并且设置了四个日志路径，
保证不同磁盘的多路复用，防止磁盘损坏导致的日志丢失
上述的附加功能是可以实现日志路径的OFA准则，方便管理
下面主要说明OMF控制文件的创建和管理
SYS@orcl/29-MAR-11>startup force
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
Database mounted.
Database opened.
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /boot/oracle/redo_log/ORCL/con
trolfile/o1_mf_6s137xvp_.ctl
SYS@orcl/29-MAR-11>alter database backup controlfile to trace;
Database altered.
Elapsed: 00:00:00.04
SYS@orcl/29-MAR-11>@trace_name.sql
trace_name
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_23785.trc
1 row selected.
Elapsed: 00:00:00.08
SYS@orcl/29-MAR-11>
SYS@orcl/29-MAR-11>show parameter db_create_online_log
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SYS@orcl/29-MAR-11>alter system set db_create_online_log_dest_1='/u02/oradata/orcl';
System altered.
Elapsed: 00:00:00.04
SYS@orcl/29-MAR-11>alter system set db_create_online_log_dest_2='/u03/oradata/orcl';
System altered.
Elapsed: 00:00:00.01
SYS@orcl/29-MAR-11>alter system set db_create_online_log_dest_3='/u04/oradata/orcl';
System altered.
Elapsed: 00:00:00.01
SYS@orcl/29-MAR-11>alter system set db_create_online_log_dest_4='/u05/oradata/orcl';
System altered.
Elapsed: 00:00:00.04
SYS@orcl/29-MAR-11>alter system set control_files='';
alter system set control_files=''
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
Elapsed: 00:00:00.07
SYS@orcl/29-MAR-11>alter system set control_files=''scope=spfile;
System altered.
Elapsed: 00:00:00.01
SYS@orcl/29-MAR-11>create pfile='/u01/mypfile.ora' from memory;
File created.
Elapsed: 00:00:00.02
SYS@orcl/29-MAR-11>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl/29-MAR-11>startup nomount pfile='/u01/mypfile.ora';
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 4 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0swpb3_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0swpph_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0sws7z_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 5 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxm53_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxmk8_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxp3v_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 6 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy23s_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy2js_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy536_.log'
) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/boot/oracle/datafile/system01.dbf',
'/boot/oracle/datafile/sysaux.dbf',
'/boot/oracle/datafile/undotbs01.dbf',
'/boot/oracle/datafile/user01.dbf',
'/boot/oracle/datafile/test_ts01.dbf',
'/boot/oracle/datafile/manager01.dbf',
'/boot/oracle/datafile/statspack01.dbf',
'/boot/oracle/datafile/user02.dbf',
'/boot/oracle/system02.dbf',
'/boot/oracle/redo_log/ORCL/datafile/o1_mf_test_6rd02mts_.dbf'
CHARACTER SET ZHS16GBK
36 ;
Control file created.
Elapsed: 00:00:01.25
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2qt12s_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2qt19q_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2qt1ls_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2qt1v4_.ctl
SYS@orcl/29-MAR-11>create spfile from memory;
File created.
Elapsed: 00:00:00.02
SYS@orcl/29-MAR-11>recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SYS@orcl/29-MAR-11>alter system archive log all;
System altered.
Elapsed: 00:00:00.04
SYS@orcl/29-MAR-11>alter database open;
Database altered.
Elapsed: 00:00:02.99
SYS@orcl/29-MAR-11>startup force
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
Database mounted.
Database opened.
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2qt12s_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2qt19q_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2qt1ls_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2qt1v4_.ctl
解释：注意点就是使用pfile加载参数文件到nomount，使用trace重建控制文件，重建过程中会自动加载到mount模式，并且在control_files(如果存在的话，因为是测试OMF特性，所以肯定不存在了)，db_create_online_log_dest_n位置创建多份控制文件，并且修改pfile在内存中的控制文件路径，需要使用create spfile from memory重建spfile 在这个新建的spfile中会显示OMF创建的控制文件，如果使用这个时候加载的pfile创建spfile的话，是会创建控制文件的，不过参数文件并不知道控制文件的路径，使用新建的spfile加载的时候会提示找不到控制文件，如下所示
SYS@orcl/28-MAR-11>startup force
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
ORA-00205: error in identifying control file, check alert log for more info
SYS@orcl/28-MAR-11>show parameter control_file
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time integer 60
control_files string /u01/app/oracle/product/11.2.0
/db_1/dbs/cntrlorcl.dbf
SYS@orcl/28-MAR-11>
并且还会由于没有指定control_files路径，自动指向$ORACLE_HOME/dbs/cntrl[sid].dbf文件，不存在的话就报错
这个主要原因就是因为pfile不保存用户对参数的修改
当你使用pfile启动实例的时候，再重建控制文件，虽然会db_create_online_log_dest_n位置创建多份控制文件，不过pfile并没有修改相应的pfile中的值，事实上他也无法保存，只有spfile才能动态修改参数值，所以那个时候应该使用内存中的参数值创建spfile，并使用spfile启动实例就可以了，
当然有心的人还可以换个方法，那就是开始不用创建pfile，
测试过程如下：
SYS@orcl/29-MAR-11>startup
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
Database mounted.
Database opened.
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2qt12s_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2qt19q_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2qt1ls_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2qt1v4_.ctl
SYS@orcl/29-MAR-11>show parameter db_create_online_log_dest_1
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_online_log_dest_1 string /u02/oradata/orcl
SYS@orcl/29-MAR-11>show parameter db_create_online_log_dest_2
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_online_log_dest_2 string /u03/oradata/orcl
SYS@orcl/29-MAR-11>show parameter db_create_online_log_dest_3
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_online_log_dest_3 string /u04/oradata/orcl
SYS@orcl/29-MAR-11>show parameter db_create_online_log_dest_4
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_online_log_dest_4 string /u05/oradata/orcl
SYS@orcl/29-MAR-11>create pfile from spfile;
File created.
Elapsed: 00:00:00.03
pfile文件内容如下
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=60
*.control_files='/u02/oradata/orcl/ORCL/controlfile/o1_mf_6s2qt12s_.ctl','/u03/oradata/orcl/ORCL/controlfile/o1_mf_6s2qt19q_.ctl','/u04/oradata/orcl/ORCL/controlfile/o1_mf_6s2qt1ls_.ctl','/u05/oradata/orcl/ORCL/controlfile/o1_mf_6s2qt1v4_.ctl'# Oracle managed file
*.core_dump_dest='/u01/app/oracle/diag/rdbms/orcl/orcl/cdump'
*.db_block_size=8192
*.db_cache_size=4M
*.db_create_file_dest='/boot/oracle/redo_log'
*.db_create_online_log_dest_1='/u02/oradata/orcl'
*.db_create_online_log_dest_2='/u03/oradata/orcl'
*.db_create_online_log_dest_3='/u04/oradata/orcl'
*.db_create_online_log_dest_4='/u05/oradata/orcl'
*.db_domain='localdomain'
*.db_file_multiblock_read_count=10
*.db_name='orcl'
*.db_recovery_file_dest='/boot/oracle/recovery_area/'
*.db_recovery_file_dest_size=8G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
SYS@orcl/29-MAR-11>alter database backup controlfile to trace;
Database altered.
Elapsed: 00:00:00.01
SYS@orcl/29-MAR-11>@trace_name.sql
trace_name
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4425.trc
1 row selected.
Elapsed: 00:00:00.13
SYS@orcl/29-MAR-11>host cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4425.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4425.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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: localhost.localdomain
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2qt12s_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2qt19q_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2qt1ls_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2qt1v4_.ctl
SYS@orcl/29-MAR-11>alter system set control_files='' scope=spfile;
System altered.
Elapsed: 00:00:00.02
SYS@orcl/29-MAR-11>startup force;
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
ORA-03113: end-of-file on communication channel
Process ID: 4895
Session ID: 125 Serial number: 3
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1kw_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1xs_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf4fn_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf75t_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggfn_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggt4_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgkb0_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgmwy_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgw7m_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgwm8_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgz44_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rh2gp_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 4 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfdv_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfst_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhjdw_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhm7p_.log'
) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/boot/oracle/datafile/system01.dbf',
'/boot/oracle/datafile/sysaux.dbf',
'/boot/oracle/datafile/undotbs01.dbf',
'/boot/oracle/datafile/user01.dbf',
'/boot/oracle/datafile/test_ts01.dbf',
'/boot/oracle/datafile/manager01.dbf',
'/boot/oracle/datafile/statspack01.dbf',
'/boot/oracle/datafile/user02.dbf',
'/boot/oracle/system02.dbf',
'/boot/oracle/redo_log/ORCL/datafile/o1_mf_test_6rd02mts_.dbf'
CHARACTER SET ZHS16GBK
;
ERROR:
ORA-03114: not connected to ORACLE
SYS@orcl/29-MAR-11>startup mount;
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
ORA-03113: end-of-file on communication channel
Process ID: 4993
Session ID: 125 Serial number: 3
SYS@orcl/29-MAR-11>startup nomount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SYS@orcl/29-MAR-11>shutdown immediate;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
杀掉oracle进程后
SYS@orcl/29-MAR-11>startup nomount
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1kw_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1xs_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf4fn_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf75t_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggfn_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggt4_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgkb0_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgmwy_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgw7m_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgwm8_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgz44_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rh2gp_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 4 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfdv_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfst_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhjdw_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhm7p_.log'
) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/boot/oracle/datafile/system01.dbf',
'/boot/oracle/datafile/sysaux.dbf',
'/boot/oracle/datafile/undotbs01.dbf',
'/boot/oracle/datafile/user01.dbf',
'/boot/oracle/datafile/test_ts01.dbf',
'/boot/oracle/datafile/manager01.dbf',
'/boot/oracle/datafile/statspack01.dbf',
'/boot/oracle/datafile/user02.dbf',
'/boot/oracle/system02.dbf',
'/boot/oracle/redo_log/ORCL/datafile/o1_mf_test_6rd02mts_.dbf'
CHARACTER SET ZHS16GBK
45 ;
Control file created.
Elapsed: 00:00:01.47
SYS@orcl/2011-03-29 14:30:19>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2z3tcg_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2z3tln_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2z3tvs_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2z3v5q_.ctl
SYS@orcl/2011-03-29 14:30:34>recover database;
Media recovery complete.
SYS@orcl/2011-03-29 14:31:47>alter system archive log all;
System altered.
Elapsed: 00:00:00.51
SYS@orcl/2011-03-29 14:32:08>alter database open;
Database altered.
SYS@orcl/2011-03-29 14:37:00>create pfile='/u01/test.ora' from spfile;
File created.
Elapsed: 00:00:00.00
SYS@orcl/2011-03-29 14:38:15>create pfile='/u01/test1.ora' from memory;
File created.
test1.ora内容（memory创建）
Elapsed: 00:00:03.51
control_file_record_keep_time=60
control_files='/u02/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tcg_.ctl'
control_files='/u03/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tln_.ctl'
control_files='/u04/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tvs_.ctl'
control_files='/u05/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3v5q_.ctl' # Oracle managed file
core_dump_dest='/u01/app/oracle/diag/rdbms/orcl/orcl/cdump'
db_block_size=8192
db_cache_size=4M
db_create_file_dest='/boot/oracle/redo_log'
db_create_online_log_dest_1='/u02/oradata/orcl'
db_create_online_log_dest_2='/u03/oradata/orcl'
db_create_online_log_dest_3='/u04/oradata/orcl'
db_create_online_log_dest_4='/u05/oradata/orcl'
db_domain='localdomain'
db_file_multiblock_read_count=10
db_name='orcl'
test.ora文件内容（spfile创建）
*.control_file_record_keep_time=60
*.control_files='/u02/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tcg_.ctl','/u03/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tln_.ctl','/u04/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tvs_.ctl','/u05/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3v5q_.ctl'#Oracle managed file
*.core_dump_dest='/u01/app/oracle/diag/rdbms/orcl/orcl/cdump'
*.db_block_size=8192
*.db_cache_size=4M
*.db_create_file_dest='/boot/oracle/redo_log'
*.db_create_online_log_dest_1='/u02/oradata/orcl'
*.db_create_online_log_dest_2='/u03/oradata/orcl'
*.db_create_online_log_dest_3='/u04/oradata/orcl'
*.db_create_online_log_dest_4='/u05/oradata/orcl'
*.db_domain='localdomain'
*.db_file_multiblock_read_count=10
*.db_name='orcl'
SYS@orcl/2011-03-29 14:44:58>startup force
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
Database mounted.
Database opened.
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2z3tcg_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2z3tln_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2z3tvs_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2z3v5q_.ctl
SYS@orcl/29-MAR-11>
圆满成功 大功告成！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
原文如下
OCP学习笔记OMF添加控制文件
DBCA选择OMF管理方式，但没有添加多路复用。即
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string d:“oracle“product“10.2.0“orada
ta
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
只有db_create_file_dest有值，那么ORACLE会在db_create_file_dest目录创建一份(only one)控制文件:
SQL> select name from v$controlfile
2 /
NAME
------------------------------------------------------------------------
D:“ORACLE“PRODUCT“10.2.0“ORADATA“STUDY“CONTROLFILE“O1_MF_625GL8JB_.CTL
Oracle一般建议生成三份控制文件. 下面我们添加控制文件目录:
SQL> alter system set db_create_online_log_dest_1=‘d:“oracle“product“10.2.0“logs‘
SQL> alter system set db_create_online_log_dest_2=‘d:“oracle“product“10.2.0“logs2‘
SQL> alter system set db_create_online_log_dest_3=‘d:“oracle“product“10.2.0“logs3‘
以上添加三个复用目录.
然后生成控制文件脚本:
SQL> alter database backup controlfile to trace;
再就是把control_files参数去除。本来想用reset清掉control_files。很不巧总报错。
只能用生PFILE文件，手工更改的方式:
SQL> create pfile=‘C:“anran.ora‘ from spfile.
打开anran.ora，remove control_files参数行。
SQL> STARTUP NOMOUNT pfile=‘C:“anran.ora‘
剩下就是按trace文件脚本重建控制文件就可以了，这里有一点，TRACE文件里有两处脚本，noresetlogs/resetlog.我们最好选用前者。
本人测试过程如下首先查看日志文件的路径，
使用db_create_oline_log_dest_n 指定多路复用日志文件和控制文件
SYS@orcl/29-MAR-11>@log_file.sql
FILE GROUP# SEQUENCE# SIZE MEMBERS ARCHIVED STATUS
------------------------------------------------------------ ---------- --------- ------ ------- ---------- ----------
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf4fn_.log 1 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1xs_.log 1 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1kw_.log 1 0 512 4 YES UNUSED
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf75t_.log 1 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0swpph_.log 4 124 512 3 YES INACTIVE
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0swpb3_.log 4 124 512 3 YES INACTIVE
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0sws7z_.log 4 124 512 3 YES INACTIVE
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxp3v_.log 5 125 512 3 YES INACTIVE
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxmk8_.log 5 125 512 3 YES INACTIVE
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxm53_.log 5 125 512 3 YES INACTIVE
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy2js_.log 6 126 512 3 NO CURRENT
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy23s_.log 6 126 512 3 NO CURRENT
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy536_.log 6 126 512 3 NO CURRENT
13 rows selected.
Elapsed: 00:00:00.01
SYS@orcl/29-MAR-11>alter database drop logfile group 4;
Database altered.
Elapsed: 00:00:00.56
SYS@orcl/29-MAR-11>alter database drop logfile group 5;
Database altered.
Elapsed: 00:00:00.57
SYS@orcl/29-MAR-11>alter database add logfile group 2;
Database altered.
Elapsed: 00:00:09.91
SYS@orcl/29-MAR-11>alter database add logfile group 3;
Database altered.
Elapsed: 00:00:10.93
SYS@orcl/29-MAR-11>alter database add logfile group 4;
Database altered.
Elapsed: 00:00:10.33
SYS@orcl/29-MAR-11>alter system switch logfile;
System altered.
Elapsed: 00:00:00.04
SYS@orcl/29-MAR-11>@log_file.sql
FILE GROUP# SEQUENCE# SIZE MEMBERS ARCHIVED STATUS
------------------------------------------------------------ ---------- --------- ------ ------- ---------- ----------
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf75t_.log 1 127 512 4 NO CURRENT
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1kw_.log 1 127 512 4 NO CURRENT
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1xs_.log 1 127 512 4 NO CURRENT
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf4fn_.log 1 127 512 4 NO CURRENT
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgmwy_.log 2 0 512 4 YES UNUSED
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgkb0_.log 2 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggfn_.log 2 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggt4_.log 2 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgwm8_.log 3 0 512 4 YES UNUSED
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgz44_.log 3 0 512 4 YES UNUSED
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rh2gp_.log 3 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgw7m_.log 3 0 512 4 YES UNUSED
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhm7p_.log 4 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfst_.log 4 0 512 4 YES UNUSED
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhjdw_.log 4 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfdv_.log 4 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy23s_.log 6 126 512 3 YES ACTIVE
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy2js_.log 6 126 512 3 YES ACTIVE
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy536_.log 6 126 512 3 YES ACTIVE
19 rows selected.
SYS@orcl/29-MAR-11>alter database drop logfile group 6;
Database altered.
Elapsed: 00:00:00.57
SYS@orcl/29-MAR-11>@log_file.sql
FILE GROUP# SEQUENCE# SIZE MEMBERS ARCHIVED STATUS
------------------------------------------------------------ ---------- --------- ------ ------- ---------- ----------
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1kw_.log 1 127 512 4 YES INACTIVE
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1xs_.log 1 127 512 4 YES INACTIVE
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf4fn_.log 1 127 512 4 YES INACTIVE
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf75t_.log 1 127 512 4 YES INACTIVE
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgmwy_.log 2 128 512 4 YES INACTIVE
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgkb0_.log 2 128 512 4 YES INACTIVE
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggfn_.log 2 128 512 4 YES INACTIVE
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggt4_.log 2 128 512 4 YES INACTIVE
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgw7m_.log 3 129 512 4 NO CURRENT
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgz44_.log 3 129 512 4 NO CURRENT
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rh2gp_.log 3 129 512 4 NO CURRENT
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgwm8_.log 3 129 512 4 NO CURRENT
/u05/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhm7p_.log 4 0 512 4 YES UNUSED
/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfdv_.log 4 0 512 4 YES UNUSED
/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfst_.log 4 0 512 4 YES UNUSED
/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhjdw_.log 4 0 512 4 YES UNUSED
16 rows selected.
总结：上述的作用是在新的路径创建联机日志的多路复用，以及将日志文件组的序号由1 4 5 6 修改为1 2 3 4并且设置了四个日志路径，
保证不同磁盘的多路复用，防止磁盘损坏导致的日志丢失
上述的附加功能是可以实现日志路径的OFA准则，方便管理
下面主要说明OMF控制文件的创建和管理
SYS@orcl/29-MAR-11>startup force
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
Database mounted.
Database opened.
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /boot/oracle/redo_log/ORCL/con
trolfile/o1_mf_6s137xvp_.ctl
SYS@orcl/29-MAR-11>alter database backup controlfile to trace;
Database altered.
Elapsed: 00:00:00.04
SYS@orcl/29-MAR-11>@trace_name.sql
trace_name
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_23785.trc
1 row selected.
Elapsed: 00:00:00.08
SYS@orcl/29-MAR-11>
SYS@orcl/29-MAR-11>show parameter db_create_online_log
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SYS@orcl/29-MAR-11>alter system set db_create_online_log_dest_1='/u02/oradata/orcl';
System altered.
Elapsed: 00:00:00.04
SYS@orcl/29-MAR-11>alter system set db_create_online_log_dest_2='/u03/oradata/orcl';
System altered.
Elapsed: 00:00:00.01
SYS@orcl/29-MAR-11>alter system set db_create_online_log_dest_3='/u04/oradata/orcl';
System altered.
Elapsed: 00:00:00.01
SYS@orcl/29-MAR-11>alter system set db_create_online_log_dest_4='/u05/oradata/orcl';
System altered.
Elapsed: 00:00:00.04
SYS@orcl/29-MAR-11>alter system set control_files='';
alter system set control_files=''
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
Elapsed: 00:00:00.07
SYS@orcl/29-MAR-11>alter system set control_files=''scope=spfile;
System altered.
Elapsed: 00:00:00.01
SYS@orcl/29-MAR-11>create pfile='/u01/mypfile.ora' from memory;
File created.
Elapsed: 00:00:00.02
SYS@orcl/29-MAR-11>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl/29-MAR-11>startup nomount pfile='/u01/mypfile.ora';
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 4 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0swpb3_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0swpph_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s0sws7z_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 5 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxm53_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxmk8_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_5_6s0sxp3v_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 6 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy23s_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy2js_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_6_6s0sy536_.log'
) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/boot/oracle/datafile/system01.dbf',
'/boot/oracle/datafile/sysaux.dbf',
'/boot/oracle/datafile/undotbs01.dbf',
'/boot/oracle/datafile/user01.dbf',
'/boot/oracle/datafile/test_ts01.dbf',
'/boot/oracle/datafile/manager01.dbf',
'/boot/oracle/datafile/statspack01.dbf',
'/boot/oracle/datafile/user02.dbf',
'/boot/oracle/system02.dbf',
'/boot/oracle/redo_log/ORCL/datafile/o1_mf_test_6rd02mts_.dbf'
CHARACTER SET ZHS16GBK
36 ;
Control file created.
Elapsed: 00:00:01.25
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2qt12s_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2qt19q_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2qt1ls_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2qt1v4_.ctl
SYS@orcl/29-MAR-11>create spfile from memory;
File created.
Elapsed: 00:00:00.02
SYS@orcl/29-MAR-11>recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SYS@orcl/29-MAR-11>alter system archive log all;
System altered.
Elapsed: 00:00:00.04
SYS@orcl/29-MAR-11>alter database open;
Database altered.
Elapsed: 00:00:02.99
SYS@orcl/29-MAR-11>startup force
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
Database mounted.
Database opened.
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2qt12s_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2qt19q_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2qt1ls_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2qt1v4_.ctl
解释：注意点就是使用pfile加载参数文件到nomount，使用trace重建控制文件，重建过程中会自动加载到mount模式，并且在control_files(如果存在的话，因为是测试OMF特性，所以肯定不存在了)，db_create_online_log_dest_n位置创建多份控制文件，并且修改pfile在内存中的控制文件路径，需要使用create spfile from memory重建spfile 在这个新建的spfile中会显示OMF创建的控制文件，如果使用这个时候加载的pfile创建spfile的话，是会创建控制文件的，不过参数文件并不知道控制文件的路径，使用新建的spfile加载的时候会提示找不到控制文件，如下所示
SYS@orcl/28-MAR-11>startup force
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
ORA-00205: error in identifying control file, check alert log for more info
SYS@orcl/28-MAR-11>show parameter control_file
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time integer 60
control_files string /u01/app/oracle/product/11.2.0
/db_1/dbs/cntrlorcl.dbf
SYS@orcl/28-MAR-11>
并且还会由于没有指定control_files路径，自动指向$ORACLE_HOME/dbs/cntrl[sid].dbf文件，不存在的话就报错
这个主要原因就是因为pfile不保存用户对参数的修改
当你使用pfile启动实例的时候，再重建控制文件，虽然会db_create_online_log_dest_n位置创建多份控制文件，不过pfile并没有修改相应的pfile中的值，事实上他也无法保存，只有spfile才能动态修改参数值，所以那个时候应该使用内存中的参数值创建spfile，并使用spfile启动实例就可以了，
当然有心的人还可以换个方法，那就是开始不用创建pfile，
测试过程如下：
SYS@orcl/29-MAR-11>startup
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
Database mounted.
Database opened.
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2qt12s_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2qt19q_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2qt1ls_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2qt1v4_.ctl
SYS@orcl/29-MAR-11>show parameter db_create_online_log_dest_1
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_online_log_dest_1 string /u02/oradata/orcl
SYS@orcl/29-MAR-11>show parameter db_create_online_log_dest_2
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_online_log_dest_2 string /u03/oradata/orcl
SYS@orcl/29-MAR-11>show parameter db_create_online_log_dest_3
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_online_log_dest_3 string /u04/oradata/orcl
SYS@orcl/29-MAR-11>show parameter db_create_online_log_dest_4
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_online_log_dest_4 string /u05/oradata/orcl
SYS@orcl/29-MAR-11>create pfile from spfile;
File created.
Elapsed: 00:00:00.03
pfile文件内容如下
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=60
*.control_files='/u02/oradata/orcl/ORCL/controlfile/o1_mf_6s2qt12s_.ctl','/u03/oradata/orcl/ORCL/controlfile/o1_mf_6s2qt19q_.ctl','/u04/oradata/orcl/ORCL/controlfile/o1_mf_6s2qt1ls_.ctl','/u05/oradata/orcl/ORCL/controlfile/o1_mf_6s2qt1v4_.ctl'# Oracle managed file
*.core_dump_dest='/u01/app/oracle/diag/rdbms/orcl/orcl/cdump'
*.db_block_size=8192
*.db_cache_size=4M
*.db_create_file_dest='/boot/oracle/redo_log'
*.db_create_online_log_dest_1='/u02/oradata/orcl'
*.db_create_online_log_dest_2='/u03/oradata/orcl'
*.db_create_online_log_dest_3='/u04/oradata/orcl'
*.db_create_online_log_dest_4='/u05/oradata/orcl'
*.db_domain='localdomain'
*.db_file_multiblock_read_count=10
*.db_name='orcl'
*.db_recovery_file_dest='/boot/oracle/recovery_area/'
*.db_recovery_file_dest_size=8G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
SYS@orcl/29-MAR-11>alter database backup controlfile to trace;
Database altered.
Elapsed: 00:00:00.01
SYS@orcl/29-MAR-11>@trace_name.sql
trace_name
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4425.trc
1 row selected.
Elapsed: 00:00:00.13
SYS@orcl/29-MAR-11>host cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4425.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4425.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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: localhost.localdomain
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2qt12s_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2qt19q_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2qt1ls_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2qt1v4_.ctl
SYS@orcl/29-MAR-11>alter system set control_files='' scope=spfile;
System altered.
Elapsed: 00:00:00.02
SYS@orcl/29-MAR-11>startup force;
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
ORA-03113: end-of-file on communication channel
Process ID: 4895
Session ID: 125 Serial number: 3
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1kw_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1xs_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf4fn_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf75t_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggfn_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggt4_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgkb0_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgmwy_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgw7m_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgwm8_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgz44_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rh2gp_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 4 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfdv_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfst_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhjdw_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhm7p_.log'
) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/boot/oracle/datafile/system01.dbf',
'/boot/oracle/datafile/sysaux.dbf',
'/boot/oracle/datafile/undotbs01.dbf',
'/boot/oracle/datafile/user01.dbf',
'/boot/oracle/datafile/test_ts01.dbf',
'/boot/oracle/datafile/manager01.dbf',
'/boot/oracle/datafile/statspack01.dbf',
'/boot/oracle/datafile/user02.dbf',
'/boot/oracle/system02.dbf',
'/boot/oracle/redo_log/ORCL/datafile/o1_mf_test_6rd02mts_.dbf'
CHARACTER SET ZHS16GBK
;
ERROR:
ORA-03114: not connected to ORACLE
SYS@orcl/29-MAR-11>startup mount;
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
ORA-03113: end-of-file on communication channel
Process ID: 4993
Session ID: 125 Serial number: 3
SYS@orcl/29-MAR-11>startup nomount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SYS@orcl/29-MAR-11>shutdown immediate;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
杀掉oracle进程后
SYS@orcl/29-MAR-11>startup nomount
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1kw_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf1xs_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf4fn_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_1_6s2rf75t_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggfn_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rggt4_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgkb0_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_2_6s2rgmwy_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgw7m_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgwm8_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rgz44_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_3_6s2rh2gp_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 4 (
'/u02/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfdv_.log',
'/u03/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhfst_.log',
'/u04/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhjdw_.log',
'/u05/oradata/orcl/ORCL/onlinelog/o1_mf_4_6s2rhm7p_.log'
) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/boot/oracle/datafile/system01.dbf',
'/boot/oracle/datafile/sysaux.dbf',
'/boot/oracle/datafile/undotbs01.dbf',
'/boot/oracle/datafile/user01.dbf',
'/boot/oracle/datafile/test_ts01.dbf',
'/boot/oracle/datafile/manager01.dbf',
'/boot/oracle/datafile/statspack01.dbf',
'/boot/oracle/datafile/user02.dbf',
'/boot/oracle/system02.dbf',
'/boot/oracle/redo_log/ORCL/datafile/o1_mf_test_6rd02mts_.dbf'
CHARACTER SET ZHS16GBK
45 ;
Control file created.
Elapsed: 00:00:01.47
SYS@orcl/2011-03-29 14:30:19>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2z3tcg_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2z3tln_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2z3tvs_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2z3v5q_.ctl
SYS@orcl/2011-03-29 14:30:34>recover database;
Media recovery complete.
SYS@orcl/2011-03-29 14:31:47>alter system archive log all;
System altered.
Elapsed: 00:00:00.51
SYS@orcl/2011-03-29 14:32:08>alter database open;
Database altered.
SYS@orcl/2011-03-29 14:37:00>create pfile='/u01/test.ora' from spfile;
File created.
Elapsed: 00:00:00.00
SYS@orcl/2011-03-29 14:38:15>create pfile='/u01/test1.ora' from memory;
File created.
test1.ora内容（memory创建）
Elapsed: 00:00:03.51
control_file_record_keep_time=60
control_files='/u02/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tcg_.ctl'
control_files='/u03/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tln_.ctl'
control_files='/u04/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tvs_.ctl'
control_files='/u05/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3v5q_.ctl' # Oracle managed file
core_dump_dest='/u01/app/oracle/diag/rdbms/orcl/orcl/cdump'
db_block_size=8192
db_cache_size=4M
db_create_file_dest='/boot/oracle/redo_log'
db_create_online_log_dest_1='/u02/oradata/orcl'
db_create_online_log_dest_2='/u03/oradata/orcl'
db_create_online_log_dest_3='/u04/oradata/orcl'
db_create_online_log_dest_4='/u05/oradata/orcl'
db_domain='localdomain'
db_file_multiblock_read_count=10
db_name='orcl'
test.ora文件内容（spfile创建）
*.control_file_record_keep_time=60
*.control_files='/u02/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tcg_.ctl','/u03/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tln_.ctl','/u04/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3tvs_.ctl','/u05/oradata/orcl/ORCL/controlfile/o1_mf_6s2z3v5q_.ctl'#Oracle managed file
*.core_dump_dest='/u01/app/oracle/diag/rdbms/orcl/orcl/cdump'
*.db_block_size=8192
*.db_cache_size=4M
*.db_create_file_dest='/boot/oracle/redo_log'
*.db_create_online_log_dest_1='/u02/oradata/orcl'
*.db_create_online_log_dest_2='/u03/oradata/orcl'
*.db_create_online_log_dest_3='/u04/oradata/orcl'
*.db_create_online_log_dest_4='/u05/oradata/orcl'
*.db_domain='localdomain'
*.db_file_multiblock_read_count=10
*.db_name='orcl'
SYS@orcl/2011-03-29 14:44:58>startup force
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database Buffers 33554432 bytes
Redo Buffers 6381568 bytes
Database mounted.
Database opened.
SYS@orcl/29-MAR-11>show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_files string /u02/oradata/orcl/ORCL/control
file/o1_mf_6s2z3tcg_.ctl, /u03
/oradata/orcl/ORCL/controlfile
/o1_mf_6s2z3tln_.ctl, /u04/ora
data/orcl/ORCL/controlfile/o1_
mf_6s2z3tvs_.ctl, /u05/oradata
/orcl/ORCL/controlfile/o1_mf_6
s2z3v5q_.ctl
SYS@orcl/29-MAR-11>
圆满成功 大功告成！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24890594/viewspace-691198/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
复制数据库
下一篇：
重装linux遇到的一些问题
请登录后发表评论
登录
全部评论
