Saturday, 11 August 2012

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 theDB_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:

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.0onwards
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

Maxdatafiles1000

Maxinstances5

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 missedthis 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 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