CONTROL FILES

How to manage Control Files??

We have discussed briefly the role of control file in an Oracle Database.
The control file of an Oracle database is created at the same time as the database.
A control file is a small binary file that records the physical structure of the database and
includes:

The database name
Names and locations of associated data files and online redo log files.
If RMAN is configured additional records as required for RMAN are also stored (such as archive log records and various backup records).
The timestamp of the database creation
The current log sequence number
Checkpoint information

The maximum control file size is operating system specific. See your operating system-specific Oracle documentation for more information and try to manage the control file size always within those limits.

The size of the control file can be limited by setting the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME.

The default value is 7. Set it to 0.

CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed.

Note:
This parameter applies only to records in the control file that are circularly
reusable (such as archive log records and various backup records). It does not
apply to records such as datafile, tablespace, and redo thread records, which are
never reused unless the corresponding object is dropped from the tablespace.

Check v$controlfile_record_section for your knowledge

Remember that the control file is to be always available to Oracle for an OPEN Database.

You specify control file names using the CONTROL_FILES initialization parameter in the database's initialization parameter file. The instance startup procedure recognizes and opens all the listed files. The instance writes to and maintains all listed control files during database operation.

If you do not specify files for CONTROL_FILES before database creation, and you are not using the Oracle Managed Files (OMF is to be discussed at a later point) feature, Oracle creates a control file and uses a default filename. The default name is operating system specific.

Every Oracle database should have at least two control files, each stored on a different disk. If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired, the damaged control file can be restored using the intact copy of the control file from the other disk and the instance can be restarted. In this case, no media recovery is required.

Never ever forget to multiplex the control files.

Recreating Control files

Only recreate the control file under very special circumstances:

(1) All current copies of the control file have been lost or are corrupted.
(2) You need to change a "hard" database parameter that was set when the database was first created, such as MAXDATAFILES, MAXLOGFILES, MAXLOGHISTORY, etc.
(3) You are restoring a backup in which the control file is corrupted or missing.
(4) Oracle Customer Support advises you to do so.
(5) If you are moving your database to another machine, which is running the same operating system, but the location of the data files, log files is not the same. This is also understood as cloning database.

Note: Never attempt to clone the database
(1) If the OS is different. Ex source on Sun, HP, AIX and you want to clone on different OS. This kind of problem is not seen on Windows flavors if the version of Oracle is certified for that OS version of Windows
(2) If oracle data block size is to be different than the source database.

What are the default values of MAXDATAFILES??

The default and the range of values of maxdatafiles and db_files are
operating system specific. Please refer to your operating system specific
Oracle manuals.

Why is there a limit on MAXDATAFILES?

Each platform uses a port-specific number of bits to store the ORACLE file numbers. Thus, this number limits MAXDATAFILES.

What are the limits on MAXDATAFILES?

Why would one set MAXDATAFILES to anything less than the port-specific maximum?

Increasing the value of MAXDATAFILES increases the size of the CONTROL FILE

Why would one set DB_FILES to anything less than MAXDATAFILES?

Increasing the value of DB_FILES increases the size of the PGA, or Program Global Area, which is allocated for every user process connected to ORACLE.
How can I determine my machine's maximum limit on MAXDATAFILES?

Check your ORACLE Installation and User's Guide. The index should point to a port-specific limit.

How can I determine where my CONTROL FILE(s) are?

In SVRMGR (if you are using Oracle 8.1.7 or lesser version) or SQL*PLUS depending on the version (hope you know that SVRMGR is not available in Oracle 9 or higher versions),

type:

show parameter control_files;

If you have multiple control files, you may find that some of them may be cut off in the output from the show parameter. In this case, you can query from V$CONTROLFILE;

What common error messages I may get if I exceed the limits???

ORA-01118: cannot add any more database files: limit of 32 exceeded
ORA-01165: MAXDATAFILES may not exceed when attempting to add data files to the database.

Then, how can I resolve them??

There are various options. Metalink Doc ID 119507.1 is best suggested for close reading. A discussion on this is relevant when we discuss backup and recovery.