11g R2 – Managing Control Files

Hi,

My name is Mark Tiger, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly.  I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks; that typically need a lot of research, before you can do them.  I will try to present the information in an easy to understand way.  My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA.  The illustrations are primarily meant for Linux, since this is a Platform; that enjoys preference from Oracle.  However they are easily adaptable for versions of UNIX/AIX and windows etc.

11g R2 – Managing Control Files

What is a Control File

Every Oracle Database has at least one control file.  A control file is a small binary file that records the physical structure of an Oracle Database.  Included in this binary file is the following information:

·         The database name

·         All the names and locations of the data files, and the redo log files

·         The timestamp of the database creation

·         The current log sequence number

·         Checkpoint information

Whenever the database is open, the control file has to be available to be written to.  Without the control file or files, the database cannot be mounted.  Recovering without a control file is difficult.

The control file of the database is created during database creation.  Typically there will be two copies created.  Best practice is to have three copies of the control file. 

The fully qualified names of the control files are specified in the CONTROL_FILES initialization parameter.  The instance starts up and writes to all the listed control files during startup and database operation.

If you do not specify values for the CONTROL_FILES initialization parameter before database creation:

·         If you are not using Oracle managed files, then Oracle Database creates an operating specific default control file, with a default name and OS location.

·         If you are using Oracle Managed files, then the initialization parameters used to enable Oracle Managed Files; determines the names and location of the control files.

·         If you are using ASM(Automatic Storage Management), then you can create incomplete Oracle ASM filenames in the  DB_CREATE_FILE_DEST and the DB_RECOVERY_FILE_DEST initialization parameters.  Oracle ASM will then create the control files in the appropriate places.

Multiplexed control files on different disks

Every database should have at least 2 control files, and preferably 3.  Each copy of the control file, should be stored on a separate disk.  If one of the control files is lost or damaged, due to disk failure or media failure; then the associated instance must be shut down.  Once the disk is restored, then an intact copy of the control file, can be made from the still good copy.  Then the database can be started normally again, with no recovery required in the database.

Multiplexed control files behave like this:

·         The Oracle Database writes to all copies of the control file listed in the CONTROL_FILES initialization parameter

·         The Oracle Database reads only the first control file listed in the CONTROL_FILES initialization parameter during database operation

·         If any of the control files can’t be accessed during database operation, then the instance becomes in-operational and must be shutdown or aborted

It is always a good idea to store your control files with the duplexed copies of the redo logs, provided that the duplexed copies of the redo logs are stored on different disk drives.

Backup of control files

Backups of the control files should be done initially and every time that there is physical change in the database, like:

·         Adding, dropping or renaming datafiles

·         Adding or dropping a tablespace, or changing the read/write state of the tablespace

·         Adding or dropping redo logs files or log groups

By default a copy of the control file is included in a full backup of the database using RMAN.  It is also a good idea to create a text copy of the control file, for just in case you need to rebuild everything from scratch.

Size of control files

The eventual size of the control file is controlled to a large extent by the values of certain initialization parameters.

·         MAXDATAFILES

·         MAXLOGFILES

·         MAXLOGMEMBERS

·         MAXLOGHISTORY

·         MAXINSTANCES

These parameters were defined in the CREATE DATABASE statement.  The larger the values for these parameters, the larger the control file will potentially grow to.

Initial Control File

The initial Oracle Database Control Files are created when you issue the CREATE DATABASE statement.  The names of the control files are specified in the CONTROL_FILES initialization parameter.  The filenames in the CONTROL_FILES parameter should be fully qualified, and are operating system specific.   Here is an example of the CONTROL_FILES parameter:

CONTROL_FILES = (/u01/app/test/control01.ctl, /u01/app/test/control01.ctl, /u01/app/test/control01.ctl,)

If files of the specified name currently exist at the time of database creation, then you must use the CONTROLFILE REUSE clause in the CREATE DATABASE statement, in order to overwrite them.  Make sure that they are no part of another instance before you use this clause.  If the size of the old control file, differs from the SIZE parameter of the new control file, then you cannot use the REUSE clause.

Once your database is created, you can change the value of the CONTROL_FILES parameter to add more control files, or to rename the control files, or delete a control file.  You should have at least 2 control file at all times.

Changing the number of copies, renaming and relocating control files

·         Shut down the database

·         With OS commands: copy the control file to a new location, or rename all the control files, or change the location of the control files

·         Edit the CONTROL_FILES parameter in the database initialization parameter file (pfile), to reflect the changes that were made on the operating system level

·         Start the database up with PFILE=<fully qualified pfile name>

·         SQL> create spfile from pfile, and restart the database

Creating new control files

The following scenarios would require you to create a new control file

·         All the control files in the database have been permanently damaged, and you don’t have a backup of the control files

·         You want to change the database name.  You are recommended to use the DBNEWID utility to change the database name and database identified(DBID)

·         The COMPATIBLE=10.1.0, then you need to recreate the control file in order to change certain parameters like: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXINSTANCES.  From COMPATIBLE=10.2.0  onwards you don’t have to recreate the control to make such changes.  When you make such a change the control file will automatically expand to accommodate the additional information

For example: if you created a database with COMPATIBLE=10.1.0, ALTER SYSTEM SET MAXLOGFILES=4 will return an error, and you will need to create a new control file, in order to change this setting.  If COMPATIBLE is set to 10.2.0, then you will not get an error, and the new setting will take effect.

CREATE CONTROLFILE

Create controlfile

Set database testa

Logfile group 1 (‘/u01/app/test/redo01_01.log’,

                             ‘/u01/app/test/redo01_02.log’),

             group 2 (‘/u01/app/test/redo02_01.log’,

                             ‘/u01/app/test/redo02_02.log’),   

             group 3 (‘/u01/app/test/redo03_01.log’,

                             ‘/u01/app/test/redo03_02.log’)

Resetlogs

Datafile ‘/u01/app/test/datafiles/system01.dbf’ size 500m,

               ‘/u01/app/test/datafiles/systaux01.dbf’ size 500m,

              ‘/u01/app/test/datafiles/temp01.dbf’ size 100m,

              ‘/u01/app/test/datafiles/users01.dbf’ size 50m

Maxlogfiles 50

Maxlogmembers 4

Maxloghistory 5000

Maxdatafiles     1000

Maxinstances  5

Archivelog;

The CREATE CONTROLFILE statement, can potentially damage datafiles and redo logfiles.  If you leave a file out it can cause loss of access to the data in that file or loss of access to the entire database.

If the database ha force logging enabled before creating the new control file, it will not be enabled after recreating the control file, unless you specify the FORCE LOGGING clause in the CREATE CONTROLFILE statement.

Creating a new control file step by step

1.)    Make a list of all the datafiles and redo log files in the database. 

You may have such a list from the “backup controlfile to trace” statement.

Otherwise you can create one by executing these statements:

SQL> select member from v$logfile;

SQL> select name from v$datafile;

SQL> select value from v$parameter where name = ‘control_files’;

If your control files are already damaged, and you cannot open the database then:

·         From the OS side, try to locate all the datafiles, and redo logs that are part of the database

·         Any files missed  this step will not be recoverable later on

·         If you miss any of the files for the SYSTEM tablespace, then you might not be able to recover the database

2.)    Shut down the database

Use SHUTDOWN NORMAL if possible, otherwise you can use SHUTDOWN IMMEDIATE.  Only use SHUTDOWN ABORT in an emergency

3.)    Back up all the datafiles and redo log files of the database.  Use Operating system commands to do this, preferably copying them to an offline area

4.)    SQL> STARTUP NOMOUNT

5.)    Issue the CREATE CONTROLFILE statement

Specify the RESETLOGS clause if you have lost any redo logs in addition to the control files.  You must also specify the RESETLOGS clause if you have renamed the database.  Otherwise you the NORESETLOGS clause or leave it out

6.)    Make a backup of the new control file.  Preferably on an offline storage device

7.)    Edit the CONTROL_FILES parameter in your initialization parameter file, to indicate the multiplexed copies of the control files that are now part of your database.  If you are changing the name of the database, then also edit the DB_NAME parameter in the initialization parameter file

8.)    If necessary recover the database.  If the new control file was created using the NORESETLOGS clause then, you can recover the database with complete closed database recovery.

If the new control file was created using the RESETLOGS clause then, you must specify the USING BACKUP CONTROL FILE clause .

9.)    Open the database:

·         If you did not perform recovery, or you performed complete closed database recovery, then you can open the database normally.

SQL> alter database open;

·         If you specified the RESETLOGS clause when creating the control file, then you need to use this clause when opening the database for the first time again.

SQL> alter database open resetlogs;

Checking for missing or extra files

After creating a new control file, and using it to open the database; you must check the alert log, to see if Oracle has recorded inconsistencies between the data dictionary and the control file.  For example a data file in the data dictionary, that does not exist in the control file.  In this case the database creates a placeholder entry in the control file ‘MISSINGnnnn’ where ‘nnnn’ is the file number.  This file is flagged in the control file as being offline and requiring media recovery.

If the actual datafile corresponding to ‘MISSINGnnnn’ is read-only or offline normal, then you can make ‘MISSINGnnn’ accessible to the database, by renaming it to the name of the actual datafile in the controlfile.

If ‘MISSINGnnnn’ corresponds to a datafile that was not read-only or offline normal, then you cannot use the rename option to make the data file accessible.  In this case the data file needs media recovery, which is precluded by the results of RESETLOGS.  In this case you need to drop the tablespace containing the data file.

If a data file listed in the control file(CREATE CONTROLFILE) is not present in the data dictionary, then the Oracle Database removes references to it from the new control file.

There will be messages for missing or extra files included in the alert log, explaining what was found.

If the Oracle database ends you an error, when you attempt to mount the database, after creating the new controlfile, you may get one of these errors:

ORA-01173

ORA-01176

ORA-01177

ORA-01215

ORA-01216

More than likely you have missed a data file or included an extra data file in the CREATE CONTROLFILE statement.

In this case you need to do a full restore of the cold backup that you made in step three above.  Open the database in nomount mode, and create the controlfile again, after correcting the problem in the script.

Backing up control files

 One approach is to use the ALTER DATABASE BACKUP CONTROLFILE statement.

·         You can backup the controlfile to a binary file

SQL> alter database backup controlfile to ‘/u01/app/test/backup/control_binary.bkp’

·         You can produce the sql statements, that can be used to recreate the control file

SQL> alter database bup controlfile to trace

You can view the last few entries in the alert log to find the name and location of the trace file, that contains the statements, to recreate your tracefile.  Or you can just look through the few latest trace files to find the correct one.

Recovering from control file corruption using a duplexed copy

This process assumes that the directory holding the file is still accessible, although the control file itself is missing or corrupted.

Also assumed is that one of the files in the CONTROL_FILES initialization parameter is still accessible.

·         Make sure that the instance is shut down

·         Use an Operating System command to overwrite the bad copy of the control file with a good multiplexed copy of the control file

·         Open the database

SQL> startup

In this case there was no recovery required, after replacing the d copy of the control file with a duplexed copy that was still good.

Let’s assume that due to media failure, the directory holding the control file is no longer accessible.

In this case you will copy a multiplexed good copy of the control file to a new location.  Then you will edit the CONTROL_FILES initialization parameter in the pfile, to reflect the new location.  You will then startup like this:

SQL> startup pfile=’...\initORACLE_SID.ora’

SQL> create spfile from pfile;

Then you need restart, so that your database is running from a spfile, and not from a pfile.

Dropping Control Files

You may want to drop a control file, because the current location is not optimal anymore.  The Oracle database needs a minimal of two control files at all times, and st practice dictates that there should be three control files.

·         Shutdown the database

·         Edit the CONTROL_FILES initialization parameter to remove the reference to this control file

·         Startup the database with the pfile

·         Put the Oracle database back into running from spfile mode

·         Lastly don’t forget to clean up the old control file with an Operating System command.

To gather information about the control files, you can access these views:

View	Description
V$DATABASE	Database information from the control file
V$CONTROLFILE	Names of the control files
V$CONTROLFILE_RECORD_SECTION	Details of the control file record sections
V$PARAMETER	Names of the control files, as specified in the CONTROL_FILES parameter
V$BACKUP_CONTROLFILE_DETAILS	Details of the backups that have been taken of the control files
V$BACKUP_CONTROLFILE_SUMMARY	Summary details of the backups that have been taken of the control files

Mark Tiger,

Need a Database Health Check, Remote Monitoring, Support, Maintenance, or a Security Audit?

MarkTiger01@gmail.com

P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam.  Watch this spot for details on the books, as they start becoming available.