【例項】增加控制檔案

實驗：增加控制檔案至三個

[oracle@hong admin]$ echo $ORACLE_SID

CAI

[oracle@hong admin]$

[oracle@hong admin]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 15 01:37:16 2016

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select status from v$instance;

 

STATUS

------------

OPEN

SQL>  show parameter control

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time        integer     7

control_files                        string      /u01/app/oracle/oradata/CAI/control01.ctl, /u01/app/oracle/oradata/CAI/control02.ctl

control_management_pack_access       string      DIAGNOSTIC+TUNING

 

SQL> select name from v$controlfile;

 

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/CAI/control01.ctl

/u01/app/oracle/oradata/CAI/control02.ctl

 

SQL> alter system set control_files='/u01/app/oracle/oradata/CAI/control01.ctl','/u01/app/oracle/oradata/CAI/control02.ctl','/u01/app/oracle/oradata/CAI/control03.ctl';

alter system set control_files='/u01/app/oracle/oradata/CAI/control01.ctl','/u01/app/oracle/oradata/CAI/control02.ctl','/u01/app/oracle/oradata/CAI/control03.ctl'

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

 

 

SQL> alter system set control_files='/u01/app/oracle/oradata/CAI/control01.ctl','/u01/app/oracle/oradata/CAI/control02.ctl','/u01/app/oracle/oradata/CAI/control03.ctl' scope=spfile;

 

System altered.

 

SQL> shutdown immediate;

(此步驟正常關閉資料庫，則觸發CKPT,對資料檔案、控制檔案、日誌檔案檢查保持SCN一致狀態)

Database closed.

Database dismounted.

ORACLE instance shut down.

 

――回到作業系統層增加控制檔案:

[oracle@hong CAI]$ cd /u01/app/oracle/oradata/CAI/

[oracle@hong CAI]$ ls

control01.ctl  redo02.log    system01.dbf   users01.dbf

control02.ctl  redo03.log    temp01.dbf

redo01.log     sysaux01.dbf  undotbs01.dbf

[oracle@hong CAI]$ cp control01.ctl control03.ctl

[oracle@hong CAI]$ ls

control01.ctl  redo01.log  sysaux01.dbf  undotbs01.dbf

control02.ctl  redo02.log  system01.dbf  users01.dbf

control03.ctl  redo03.log  temp01.dbf

[oracle@hong CAI]$

[oracle@hong CAI]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 15 01:53:27 2016

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

(重新讀取spfile,啟動資料庫，靜態引數修改生效)

ORACLE instance started.

 

Total System Global Area  939495424 bytes

Fixed Size                  2258840 bytes

Variable Size             595593320 bytes

Database Buffers          335544320 bytes

Redo Buffers                6098944 bytes

Database mounted.

Database opened.

SQL> show parameter control

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time        integer     7

control_files                        string      /u01/app/oracle/oradata/CAI/control01.ctl, /u01/app/oracle/oradata/CAI/control02.ctl, /u01/app/oracle/oradata/CAI/control03.ctl

control_management_pack_access       string      DIAGNOSTIC+TUNING

 

SQL> select name from v$controlfile;

 

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/CAI/control01.ctl

/u01/app/oracle/oradata/CAI/control02.ctl

/u01/app/oracle/oradata/CAI/control03.ctl

 

完成!!!!!!!!!!!!!!!