Control File Content and the Limits – Keep Learning Keep Living

Hi,

I would like to talk about Control File (CF). One of the most important component of the Oracle Database. It stores many critical data and limits for the database.

Last SCN (System Change Number).
Last Checkpoint Information.
Data file locations.
Redo log file locations.
RMAN Repository (where the backup related files are).
Parameters like MAXLOGFILE MAXLOGMEMBER MAXLOGHISTORY MAXINSTANCES etc.
Database Properties like; Logging mode, DB Name etc.

I wanted to explain how to find contents of a control file. Basically I wanted to reach the parameters (upper limits for the database) like MAXLOGFILE. Most of those values can be found in V$CONTROLFILE_RECORD_SECTION view. this view lists what control file stores and what is the upper limit for almost all control file parameters.

While creating a control file you can specify the parameters, here is an example of control file create script:

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    'C:\APP\MUSTA\ORADATA\ORCL\ONLINELOG\O1_MF_1_HKGQF3CO_.LOG',
    'C:\APP\MUSTA\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_1_HKGQF570_.LOG'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    'C:\APP\MUSTA\ORADATA\ORCL\ONLINELOG\O1_MF_2_HKGQF3CO_.LOG',
    'C:\APP\MUSTA\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_2_HKGQF56Z_.LOG'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    'C:\APP\MUSTA\ORADATA\ORCL\ONLINELOG\O1_MF_3_HKGQF3CO_.LOG',
    'C:\APP\MUSTA\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_HKGQF56X_.LOG'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  'C:\APP\MUSTA\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_HKGQ5T5R_.DBF',
  'C:\APP\MUSTA\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_HKGQ77L8_.DBF',
  'C:\APP\MUSTA\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_HKGQ80X0_.DBF',
  'C:\APP\MUSTA\ORADATA\ORCL\DATAFILE\O1_MF_USERS_HKGQ826M_.DBF'
CHARACTER SET AL32UTF8
;

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 (

'C:\APP\MUSTA\ORADATA\ORCL\ONLINELOG\O1_MF_1_HKGQF3CO_.LOG',

'C:\APP\MUSTA\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_1_HKGQF570_.LOG'

) SIZE 200M BLOCKSIZE 512,

GROUP 2 (

'C:\APP\MUSTA\ORADATA\ORCL\ONLINELOG\O1_MF_2_HKGQF3CO_.LOG',

'C:\APP\MUSTA\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_2_HKGQF56Z_.LOG'

) SIZE 200M BLOCKSIZE 512,

GROUP 3 (

'C:\APP\MUSTA\ORADATA\ORCL\ONLINELOG\O1_MF_3_HKGQF3CO_.LOG',

'C:\APP\MUSTA\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_HKGQF56X_.LOG'

) SIZE 200M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'C:\APP\MUSTA\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_HKGQ5T5R_.DBF',

'C:\APP\MUSTA\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_HKGQ77L8_.DBF',

'C:\APP\MUSTA\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_HKGQ80X0_.DBF',

'C:\APP\MUSTA\ORADATA\ORCL\DATAFILE\O1_MF_USERS_HKGQ826M_.DBF'

CHARACTER SET AL32UTF8

;

LOGFILE clause is for Redo log files. I’ve created this script from a database which is run on Windows OS. So the path names are in windows format. As you see we can define upper limits for database in this script. Let’s talk about them.

MAXLOGFILES

Maximum number of “Redo Log Groups” not the number of redo log files! as you know we are creating redo log groups and every group should (must) have more than one redo log file. So this is the limit of redo groups. default is 16 which means you can have up to 16 redo log groups and those groups can have 3 redo log file members (48 files).

MAXLOGMEMBERS

Maximum number of redo log file members in a redo log group. as I said earlier, one group can contain (must contain) more than one file which are all identical. this parameter limits the max number of redo files in one group. if MAXLOGMEMBERS are 5 then you cannot create a redo group with 6 redo files.

MAXDATAFILES

Maximum number of data files that database can handle! this is very important as the previous ones. those parameters can not be altered while database is running because you must re-create Control File. so if you don’t make a plan carefully then you might have to shutdown your database at a very important moment! default is 100 for this parameter and let’s say you have created 100th file and then you have needed one more! here is the problem. you have to shutdown database re create controlfile and catalog every rman backups you have etc.

MAXINSTANCES

if you are planning to use RAC for this database, this is the upper limit for your number of instances.

MAXLOGHISTORY

This is the number of redo log switch history that Oracle will remember and list in V$LOG_HISTORY view.

So let’s say you wanted to see those parameters’ current values. how to find them? As I said most of them can be found in V$CONTROLFILE_RECORD_SECTION. Also this view has information about how many backup files are stored in control file for example.

Select Decode(type, 'DATAFILE', 'MAXDATAFILES',
                    'REDO LOG','MAXLOGFILES',
                    'REDO THREAD','MAXINSTANCES',
                    'LOG HISTORY','MAXLOGHISTORY') Param, 
      records_total upper_limit, records_used current_value from V$CONTROLFILE_RECORD_SECTION s
Where Type in ('REDO THREAD', 'REDO LOG', 'DATAFILE', 'LOG HISTORY');
PARAM         UPPER_LIMIT CURRENT_VALUE
------------- ----------- -------------
MAXINSTANCES            8             1
MAXLOGFILES            16            10
MAXDATAFILES          100            11
MAXLOGHISTORY         292           193

Select Decode(type, 'DATAFILE', 'MAXDATAFILES',

'REDO LOG','MAXLOGFILES',

'REDO THREAD','MAXINSTANCES',

'LOG HISTORY','MAXLOGHISTORY') Param,

records_total upper_limit, records_used current_value from V$CONTROLFILE_RECORD_SECTION s

Where Type in ('REDO THREAD', 'REDO LOG', 'DATAFILE', 'LOG HISTORY');

PARAM UPPER_LIMIT CURRENT_VALUE

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

MAXINSTANCES 8 1

MAXLOGFILES 16 10

MAXDATAFILES 100 11

MAXLOGHISTORY 292 193

I found some scripts on the net which uses TYPE=’DATABASE’ condition for MAXINSTANCE but this is not correct. you can use REDO THREAD for max number of instances because every instance has it is own redo thread.

Please remember, this view is not a parameter table. it lists about spaces in control file. For example, you can see that in my example MAXDATAFILES parameter is 11 but I have just 4 datafiles. it is because I have occupied 11 spots in control file until now which means I have created 11 data files in my database but I have dropped them. so you can consider this as a “high water mark”.

I also said you “almost” have all information about control file parameters in this view. MAXLOGMEMBERS is not represented in this view because this is just a limitation not a consumed space in control file. As far as I know only this information can be found on X$KCCDI table’s DIMLM column. X$KCCDI is one of the main data of V$DATABASE so maybe MAXLOGMEMBERS data will be on V$DATABASE view after a while.

I hope this helps to understand what is stored in control file and the importance of those parameters.

wish you a healthy days.