oracle控制文件是一個跟蹤數據庫的物理組成的二進制文件，僅與一個數據庫相關聯，每個數據庫有唯一的控制文件，可以維護多個相同的拷貝。控制文件是oracle數據庫用來查找數據庫文件，並從總體上管理漱口狀態的根文件。控制文件包含以下信息：
控制文件的作用
在數據庫使用期間，oracle數據庫不斷讀取和寫入控制文件，並且只要數據庫處於打開狀態，控制文件就必須是可用的，以便可以寫入。例如，恢復數據庫涉及控制文件中讀取數據庫中包含的所有數據文件名稱。其他的操作，如添加數據文件，會更新存儲在控制文件中的信息。
控制文件的結構
與數據庫相關的信息存儲在控制文件中的不同部分中。每個部分是有關數據庫的某個方面的一組記錄。例如，控制文件中有一個部分追蹤數據文件，並包含你一個記錄集合，每個數據文件有一條記錄。每個部分存儲在多個邏輯控制文件塊中。同一部分可以跨越多個塊。
控制文件中包含以下類型的記錄：
這些記錄包含可以被覆蓋的非關鍵信息。當所有可用的記錄槽用完時，數據庫需要擴展控制文件或覆蓋最舊的記錄，以便為新記錄騰出空間。循環重用記錄可以刪除，並且不會影響數據庫運行，如：RMAN備份記錄，歸檔日志歷史信息等信息。
這些記錄包含不經常更改且不能被覆蓋的關鍵信息。包括表空間、數據文件、聯機重做日志文件、重做線程。oracle數據庫絕不會重用這些記錄，除非從表空間中刪除相應的對象。
方法一，直接查看v$controlfile_record_section視圖。
v$controlfile_record_section的數據來源於X$KCCRS內部表，其定義如下：
select inst_id,
decode(indx,0,'DATABASE',1,'CKPT PROGRESS',2,'REDO THREAD',
3,'REDO LOG',4,'DATAFILE',5,'FILENAME',6,'TABLESPACE',
7,'TEMPORARY FILENAME',8,'RMAN CONFIGURATION',9,'LOG HISTORY',
10,'OFFLINE RANGE',11,'ARCHIVED LOG',12,'BACKUP SET',
13,'BACKUP PIECE',14,'BACKUP DATAFILE',15,'BACKUP REDOLOG',
16,'DATAFILE COPY',17,'BACKUP CORRUPTION',18,'COPY CORRUPTION',
19,'DELETED OBJECT',20,'PROXY COPY',21,'BACKUP SPFILE',
23,'DATABASE INCARNATION',24,'FLASHBACK LOG',25,'RECOVERY DESTINATION',
26,'INSTANCE SPACE RESERVATION',27,'REMOVABLE RECOVERY FILES',
28,'RMAN STATUS',29,'THREAD INSTANCE NAME MAPPING',30,'MTTR',
31,'DATAFILE HISTORY',32,'STANDBY DATABASE MATRIX',
33,'GUARANTEED RESTORE POINT',34,'RESTORE POINT',
35,'DATABASE BLOCK CORRUPTION',36,'ACM OPERATION',
37,'FOREIGN ARCHIVED LOG','UNKNOWN') TYPE,
rsrsz,
rsnum,
rsnus,
rsiol,
rsilw,
rsrlw
from x$kccrs
where indx not in (22);
為什麼使用上述定義，可以通過查看$ORACLE_HOME/rdbms/admin/dbmsbkrs.sql得知，該腳本是DBMS_BACKUP_RESTORE包的創建代碼，如果數據庫中不存在DBMS_BACKUP_RESTORE包，可以使用該腳本創建。其中有一段對類型定義常量的部分如下：
--********************************
-- Controlfile Sizing Procedures--
--******************************--
-- These procedures are used to resize a record section of the controlfile
-- and to calculate the size of a controlfile having specified counts of
-- records of each type.
--
-- Valid record types are defined as the following constants:
RTYP_DB_INFO constant binary_integer := 0;
RTYP_CKPTPROG constant binary_integer := 1;
RTYP_THREAD constant binary_integer := 2;
RTYP_LOGFILE constant binary_integer := 3;
RTYP_DATAFILE constant binary_integer := 4;
RTYP_FILENAME constant binary_integer := 5;
RTYP_TABLESPACE constant binary_integer := 6;
RTYP_RESERVED1 constant binary_integer := 7;
RTYP_TEMPFILE constant binary_integer := 7;
RTYP_RMAN_CONFIGURATION constant binary_integer := 8;
RTYP_LOG_HISTORY constant binary_integer := 9;
RTYP_OFFLINE_RANGE constant binary_integer := 10;
RTYP_ARCHIVED_LOG constant binary_integer := 11;
RTYP_BACKUP_SET constant binary_integer := 12;
RTYP_BACKUP_PIECE constant binary_integer := 13;
RTYP_BACKUP_DFILE constant binary_integer := 14;
RTYP_BACKUP_LOG constant binary_integer := 15;
RTYP_DFILE_COPY constant binary_integer := 16;
RTYP_BACKUP_DFILE_CORR constant binary_integer := 17;
RTYP_DFILE_COPY_CORR constant binary_integer := 18;
RTYP_DELETED_OBJECT constant binary_integer := 19;
RTYP_RESERVED3 constant binary_integer := 20;
RTYP_PROXY constant binary_integer := 20;
RTYP_RESERVED4 constant binary_integer := 21;
RTYP_BACKUP_SPFILE constant binary_integer := 21;
RTYP_DB2 constant binary_integer := 22;
RTYP_INCARNATION constant binary_integer := 23;
RTYP_FLASHBACK constant binary_integer := 24;
RTYP_RA_INFO constant binary_integer := 25;
RTYP_INST_RSVT constant binary_integer := 26;
RTYP_AGED_FILES constant binary_integer := 27;
RTYP_RMAN_STATUS constant binary_integer := 28;
RTYP_THREAD_INST constant binary_integer := 29;
RTYP_MTR constant binary_integer := 30;
RTYP_DFH constant binary_integer := 31;
RTYP_SDM constant binary_integer := 32;
RTYP_RSP constant binary_integer := 33;
RTYP_NRR constant binary_integer := 34;
RTYP_BLOCK_CORRUPTION constant binary_integer := 35;
RTYP_ACM_OPERATION constant binary_integer := 36;
RTYP_FOREIGN_ARCHIVED_LOG constant binary_integer := 37;
通過上面代碼與v$controlfile_record_section視圖的創建代碼比較就能明白了。
方法二，通過trace文件查看控制文件的創建腳本
--生成trace文件
alter database backup controlfile to trace;
--查看trace文件位置
SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file
FROM (SELECT VALUE FROM v$parameter WHERE name = 'user_dump_dest') a,
(SELECT SUBSTR(VALUE, -6, 1) symbol FROM v$parameter WHERE name = 'user_dump_dest') b,
(SELECT instance_name FROM v$instance) c,
(SELECT spid FROM v$session s, v$process p, v$mystat m WHERE
s.paddr = p.addr AND s.sid = m.sid AND m.statistic# = 0) d;
其中創建控制文件的部分如下所示：
CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/oracl/system01.dbf',
'/u01/app/oracle/oradata/oracl/sysaux01.dbf',
'/u01/app/oracle/oradata/oracl/undotbs01.dbf',
'/u01/app/oracle/oradata/oracl/users01.dbf'
CHARACTER SET ZHS16GBK;
標紅的參數需要特別注意
MAXDATAFILES
這個參數是保存在控制文件裡的，在DBCA創建實例的時候可以指定該值的大小。
官方解釋：
The MAXDATAFILES option of CREATE DATABASE determines the number of data files a database can have. With Oracle Real Application Clusters, databases tend to have more data files and log files than an exclusive mounted database.
MAXDATAFILES與DB_FILES參數的關系可以參見MOS文檔：MAXDATAFILES and DB_FILES Parameters (文檔 ID 119507.1)
MAXINSTANCES
The MAXINSTANCES optionof CREATE DATABASE limits the number of instances that can access a databaseconcurrently. The default value for this option underz/OS is 15. Set MAXINSTANCES to a value greater than the maximum numberof instances you expect to run concurrently.
MAXLOGFILES和MAXLOGMEMBERS
The MAXLOGFILES option of CREATE DATABASE specifies the maximum number of redo log groups that can be created for the database. The MAXLOGMEMBERS option specifies the maximum number of members or number of copies per group. Set MAXLOGFILES to the maximum number of instances you plan to run concurrently multiplied by the maximum anticipated number of groups per thread.
MAXLOGHISTORY
The MAXLOGHISTORY option of CREATE DATABASE specifies the maximum number of redolog files that can be recorded in the log history of the control file.The log history is used for automatic media recovery of Oracle Real ApplicationClusters.
For Oracle Real Application Clusters, set MAXLOGHISTORY to a large value, such as 100. The control file can then store information about this number of redo log files. When the log history exceeds this limit, the Oracle server overwrites the oldest entries in the log history. The default for MAXLOGHISTORY is 0 (zero), which disables log history.
這裡指清除循環重用記錄。
方法一，通過重建控制文件或設置control_file_record_keep_time=0。
方法二，使用execute sys.dbms_backup_restore.resetCfileSection清理。
清理v$archived_log對應的記錄，執行execute sys.dbms_backup_restore.resetCfileSection(11)
清理v$rman_status對應的記錄，執行execute sys.dbms_backup_restore.resetCfileSection(28);
使用參數請參照上一部分介紹。
注：請勿隨意在生產環境上執行！
