控制文件管理

一：什么是控制文件
控制文件是一个二进制文件，记录数据库的物理结构，控制文件包括以下内容
1 数据库的名字
2 数据文件和重做日志文件两者的名字以及位置
3 数据库创建的时间戳
4 当前日志的序列号
5 检查点信息等

二：控制文件的备份
1 备份控制文件到一个二进制文件
alter database backup controlfile to '/backup/control.bkp';
2 产生能够重新创建控制文件的语句
alter database backup controlfile to trace;
三：dropping control files
方法一步骤：
1 更改数据库初始化参数中control_files 参数去掉不需要的控制文件。
2 重新启动数据库 
-----------------------------------------------
方法二的步骤：
1 先由spfile 生成pfile，再更改pfile中控制文件部分
2 停止数据库
3 以pfile方式启动数据库
4 由pfile生成spfile
5 重新启动数据库
eg1 以方法一drop 控制文件control03.ctl

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oracle/CRM2/CRM/control01.ctl
                                                 , /oracle/CRM2/CRM/control02.c
                                                 tl, /oracle/CRM2/CRM/control03
                                                 .ctl
SQL> alter system set control_files='/oracle/CRM2/CRM/control01.ctl','/oracle/CRM2/CRM/control02.ctl' scope=spfile;

System altered.

SQL> startup force open;
ORACLE instance started.

Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              96471936 bytes
Database Buffers          218103808 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oracle/CRM2/CRM/control01.ctl
                                                 , /oracle/CRM2/CRM/control02.c
                                                 tl
             
eg2以方法二drop control03.ctl：
SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oracle/CRM2/CRM/control01.ctl
                                                 , /oracle/CRM2/CRM/control02.c
                                                 tl, /oracle/CRM2/CRM/control03
                                                 .ctl
SQL> create pfile from spfile;

File created.
SQL> host;
[oracle@oracle ~]$ cd /oracle/app/db1/dbs/
[oracle@oracle dbs]$ ls
01nkag44_1_1  02norgeq_1_1  04norgo6_1_1  control.sql     hc_zx1.dat  initCRM.ora      initJIUJIANZX.ora  initzx.ora  lkDUMMY      orapwCRM           spfileERP.ora
01nootjp_1_1  03nkagdo_1_1  05npb63q_1_1  hc_CRM.dat      hc_zx2.dat  initdw.ora       init.ora           initZX.ora  lkERP        snapcf_CRM.f       spfilejiujian.ora
01norgeh_1_1  03norgo2_1_1  06npb64k_1_1  hc_ERP.dat      hc_zx.dat   initERP.ora      initzx1.ora        lkCRM       log1CRM.dbf  spfileCRM.ora
02nkag5r_1_1  04nkagdt_1_1  07npb802_1_1  hc_jiujian.dat  hc_ZX.dat   initjiujian.ora  initzx2.ora        lkCRMTEST   log2CRM.dbf  spfileCRM.ora.bak
[oracle@oracle dbs]$ vi initCRM.ora 
*.control_files='/oracle/CRM2/CRM/control01.ctl','/oracle/CRM2/CRM/control02.ctl'
[oracle@oracle dbs]$ grep control_files initCRM.ora 
*.control_files='/oracle/CRM2/CRM/control01.ctl','/oracle/CRM2/CRM/control02.ctl'

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/oracle/app/db1/dbs/initCRM.ora';  
ORACLE instance started.

Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              96471936 bytes
Database Buffers          218103808 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;

File created.
SQL> startup froce open;
SP2-0714: invalid combination of STARTUP options
SQL> startup force open;
ORACLE instance started.

Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              96471936 bytes
Database Buffers          218103808 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oracle/CRM2/CRM/control01.ctl
                                                 , /oracle/CRM2/CRM/control02.c
                                                 tl
SQL> 
四：Creating Additional Copies, Renaming, and Relocating Control Files
步骤：1 shutdown the database
             2 使用操作系统命令拷贝已存在的控制文件到新的位置
             3 更改control_files参数
             4 重新启动数据库
eg ：创建数据文件的另一个拷贝control03.ctl
SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oracle/CRM2/CRM/control01.ctl
                                                 , /oracle/CRM2/CRM/control02.c
                                                 tl
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@oracle ~]$ cp /oracle/CRM2/CRM/control01.ctl  /oracle/CRM2/CRM/control03.ctl 
[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 6 06:44:30 2012

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              96471936 bytes
Database Buffers          218103808 bytes
Redo Buffers                6365184 bytes
SQL> alter system set control_files='/oracle/CRM2/CRM/control01.ctl','/oracle/CRM2/CRM/control02.ctl','/oracle/CRM2/CRM/control03.ctl' scope=spfile;

System altered.

SQL> startup force open;
ORACLE instance started.

Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              96471936 bytes
Database Buffers          218103808 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oracle/CRM2/CRM/control01.ctl
                                                 , /oracle/CRM2/CRM/control02.c
                                                 tl, /oracle/CRM2/CRM/control03
                                                 .ctl
五 创建controlfile
详见文章 http://jiujian.blog.51cto.com/444665/982734