ITPub博客

首页 > Linux操作系统 > Linux操作系统 > OCP学习笔记OMF添加控制文件(注意事项)----------OMF创建控制和OFA文件准则文件

OCP学习笔记OMF添加控制文件(注意事项)----------OMF创建控制和OFA文件准则文件

原创 Linux操作系统 作者:xpj0515 时间:2011-03-29 13:25:39 0 删除 编辑
网上有篇关于创建控制文件OMF的文章,经过测试出现了一些问题。现总结如下
原文如下

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博客 ” ,链接:http://blog.itpub.net/24890594/viewspace-691198/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 复制数据库
请登录后发表评论 登录
全部评论

注册时间:2010-11-08

  • 博文量
    32
  • 访问量
    52390