控制文件大小的疑问

在一个9i数据库后台看到一条报警日志,说控制文件已满,而这个控制文件是放在一个250M大小的裸设备上,到底这个控制文件有多大? 它会增长到多大? 10g还好计算,9i怎么办?[@more@]

-- v$controlfile_record_section

-- oracle 9i 和 oracle10g 中的v$controlfile_record_section 差异

-- 在9i中有23种类型的记录
select type from v$controlfile_record_section;

TYPE
--------------------
ARCHIVED LOG
BACKUP CORRUPTION
BACKUP DATAFILE
BACKUP PIECE
BACKUP REDOLOG
BACKUP SET
BACKUP SPFILE
CKPT PROGRESS
COPY CORRUPTION
DATABASE
DATABASE INCARNATION
DATAFILE
DATAFILE COPY
DELETED OBJECT
FILENAME
LOG HISTORY
OFFLINE RANGE
PROXY COPY
REDO LOG
REDO THREAD
RMAN CONFIGURATION
TABLESPACE
TEMPORARY FILENAME

-- 在10g中有34种类型
ARCHIVED LOG
BACKUP CORRUPTION
BACKUP DATAFILE
BACKUP PIECE
BACKUP REDOLOG
BACKUP SET
BACKUP SPFILE
CKPT PROGRESS
COPY CORRUPTION
DATABASE
DATABASE INCARNATION
DATAFILE
DATAFILE COPY
DATAFILE HISTORY
DELETED OBJECT
FILENAME
FLASHBACK LOG
GUARANTEED RESTORE POINT
INSTANCE SPACE RESERVATION
LOG HISTORY
MTTR
OFFLINE RANGE
PROXY COPY
RECOVERY DESTINATION
REDO LOG
REDO THREAD
REMOVABLE RECOVERY FILES
RESTORE POINT
RMAN CONFIGURATION
RMAN STATUS
STANDBY DATABASE MATRIX
TABLESPACE
TEMPORARY FILENAME
THREAD INSTANCE NAME MAPPING

------------------
-- 增加的11项内容如下：
DATAFILE HISTORY
FLASHBACK LOG
GUARANTEED RESTORE POINT
INSTANCE SPACE RESERVATION
MTTR
RECOVERY DESTINATION
REMOVABLE RECOVERY FILES
RESTORE POINT
RMAN STATUS
STANDBY DATABASE MATRIX
THREAD INSTANCE NAME MAPPING
------------------
-- v$controlfile_record_section 各列的含义
RECORD_SIZE NUMBER --Record size in bytes
RECORDS_TOTAL NUMBER --Number of records allocated for the section
RECORDS_USED NUMBER --Number of records used in the section
FIRST_INDEX NUMBER --Index (position) of the first record
LAST_INDEX NUMBER --Index of the last record
LAST_RECID NUMBER --Record ID of the last record

------------------
-- 控制文件的大小
SQL> col name for a40
SQL> select * from v$controlfile;

STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------ --- ---------- --------------
/oracle/oradata/mynewdb/ora_control1 NO 16384 380

SQL>
SQL> select 16384*380 from dual;

16384*380
----------
6225920

SQL> host ls -l /oracle/oradata/mynewdb/ora_co*
-rw-r----- 1 oracle dba 6242304 Feb 21 11:45 /oracle/oradata/mynewdb/ora_control1

SQL> select 6242304-6225920 from dual;

6242304-6225920
---------------
16384

SQL>
-- 小结：在10g v$controlfile视图中有blocksize 和 file size blks 两列
-- 通过blocksize*(file size blks + 1) 可以计算出当前控制文件大小。

-- 9i 中的情况又如何呢？
SQL> select * from v$controlfile_record_section
2 ;

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED
-------------------- ----------- ------------- ------------
DATABASE 316 1 1
CKPT PROGRESS 4084 4 0
REDO THREAD 228 1 1
REDO LOG 72 5 3
DATAFILE 428 100 11
FILENAME 268 116 16
TABLESPACE 68 100 13
TEMPORARY FILENAME 56 100 2
RMAN CONFIGURATION 1108 50 1
LOG HISTORY 36 226 226
OFFLINE RANGE 180 136 0
ARCHIVED LOG 328 24 24
BACKUP SET 40 204 204
BACKUP PIECE 480 204 204
BACKUP DATAFILE 116 211 211
BACKUP REDOLOG 76 107 107
DATAFILE COPY 404 202 0
BACKUP CORRUPTION 44 185 0
COPY CORRUPTION 40 204 0
DELETED OBJECT 20 408 408
PROXY COPY 596 219 0
BACKUP SPFILE 36 226 200
DATABASE INCARNATION 56 145 1

23 rows selected.

-- (RECORD_SIZE * RECORDS_TOTAL )/8192 取整 求和
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED RECORDS_TOTAL2 RECORDS_USED2 tt8k tt8k-int tt4k tt4k-int
-------------------- ----------- ------------- ------------
DATABASE 316 1 1 316 316 0.038574219 1 0.077148438 1
CKPT PROGRESS 4084 4 0 16336 0 1.994140625 2 3.98828125 4
REDO THREAD 228 1 1 228 228 0.027832031 1 0.055664063 1
REDO LOG 72 5 3 360 216 0.043945313 1 0.087890625 1
DATAFILE 428 100 11 42800 4708 5.224609375 6 10.44921875 11
FILENAME 268 116 16 31088 4288 3.794921875 4 7.58984375 8
TABLESPACE 68 100 13 6800 884 0.830078125 1 1.66015625 2
TEMPORARY FILENAME 56 100 2 5600 112 0.68359375 1 1.3671875 2
RMAN CONFIGURATION 1108 50 1 55400 1108 6.762695313 7 13.52539063 14
LOG HISTORY 36 226 226 8136 8136 0.993164063 1 1.986328125 2
OFFLINE RANGE 180 136 0 24480 0 2.98828125 3 5.9765625 6
ARCHIVED LOG 328 24 24 7872 7872 0.9609375 1 1.921875 2
BACKUP SET 40 204 204 8160 8160 0.99609375 1 1.9921875 2
BACKUP PIECE 480 204 204 97920 97920 11.953125 12 23.90625 24
BACKUP DATAFILE 116 211 211 24476 24476 2.987792969 3 5.975585938 6
BACKUP REDOLOG 76 107 107 8132 8132 0.992675781 1 1.985351563 2
DATAFILE COPY 404 202 0 81608 0 9.961914063 10 19.92382813 20
BACKUP CORRUPTION 44 185 0 8140 0 0.993652344 1 1.987304688 2
COPY CORRUPTION 40 204 0 8160 0 0.99609375 1 1.9921875 2
DELETED OBJECT 20 408 408 8160 8160 0.99609375 1 1.9921875 2
PROXY COPY 596 219 0 130524 0 15.93310547 16 31.86621094 32
BACKUP SPFILE 36 226 200 8136 7200 0.993164063 1 1.986328125 2
DATABASE INCARNATION 56 145 1 8120 56 0.991210938 1 1.982421875 2
77 150

-- 控制文件总共要分配77个大小为8K的块
-- 或者
-- 控制文件总共要分配150个大小为4K的块
SQL> host ls -l /oracle/oradata/ptdb/control01.ctl
-rw-r----- 1 oracle dba 1302528 Feb 21 11:36 /oracle/oradata/ptdb/control01.ctl

-- 控制文件总共要分配150个大小为4K的块 到操作系统要分配150个数据块(8k) 还缺少9个8k的块做什么了？
-- 问题： v$controlfile_record_section.RECORDS_TOTAL 与 v$controlfile.FILE_SIZE_BLKS 到底有什么关系呢？

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271063/viewspace-1046293/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271063/viewspace-1046293/