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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.