How to Check Control File Size

  • by

Check Control File Size

Control file is a file that have several file attributes including size, but when database is stored upon raw devices, your control files are just symbolic links that represent raw devices. You have no way to check the usage of a raw device.

Here are several ways that can check the size of a control file.

V$CONTROLFILE_RECORD_SECTION

Query v$controlfile_record_section.

SQL> SELECT 2* SUM(record_size*records_total)/1024/1024 "SIZE (MB)" FROM v$controlfile_record_section;

You can see I double the size of the sum of all the records size. The result is not accurate, but it's close to the actual size, just a little less than the actual.

RMAN Backup Size

By an indirect approach, to backup current control file in order to check the control file size.

RMAN> BACKUP CURRENT CONTROLFILE FORMAT '/tmp/control_backup.ctl';
...
$ ls -l /tmp/con*
-rw-r----- 1 oracle asmadmin 19955712 Aug 18 10:29 /tmp/control_backup.ctl

V$CONTROLFILE

For 11g, query V$CONTROLFILE/span> directly.

SQL> SELECT (block_size * file_size_blks)/1024/1024 "SIZE (MB)" FROM v$controlfile;

The result is more accurate than query v$controlfile_record_section. But for 9i, 10g, this statement can not be used, because there is no block_size and file_size_blks column in v$controlfile in 9i and 10g.

ASMCMD

For 11g, list file size via ASMCMD with grid owner.

$ asmcmd
ASMCMD> cd +data/dbname/controlfile
ASMCMD> ls -s
Block_Size  Blocks     Bytes     Space  Name
     16384    1215  19906560  78643200  Current.329.798435911

The size is on the column Bytes.

Raw Device

Using command dd to output a raw device to a regular file as following command will not give you the answer, which will dump all blocks of the raw device whether the blocks are used or not.

# dd if=/dev/vg1/rlv01 of=/tmp/control_output.ctl bs=4k

Further reading: How to Write Control File into Raw Device

Leave a Reply

Your email address will not be published. Required fields are marked *