Oracle DBA: October 2012

Tuesday, October 16, 2012

How to Recreate a Controlfile [ID 735106.1]

How to Recreate a Controlfile [ID 735106.1]

In this Document
Goal
Solution
References

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.2.0.2 - Release: 9.0.1 to 11.2
Information in this document applies to any platform.

Goal

This article describes how you can recreate your controlfile.

Solution

Warning

You should only recreate your control file under the following circumstances:

All current copies of the control file have been lost or are corrupted.
You are restoring a backup in which the control file is corrupted or missing.
You need to change a hard limit database parameter in the controlfile.
If you are moving your database to another server and files are located in a different location.
Oracle Customer Support advises you to.

Creating a new Controlfile from an existing database that is mounted or open.

First you must generate an ascii dump of the controlfile.

Whilst the database is mounted or open issue:

SQL> alter database backup controlfile to trace;

A trace file will be generated in the user_dump_destination directory.

SQL> show parameter user_dump_dest

NAME TYPE VALUE
-------------- ------ ------------------------------------------------
user_dump_dest string /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace

After navigating to the directory locate the latest trace file by date/time by issuing: ls -ltr.

% cd /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace

% ls -ltr

Once you locate the file it will appear as an ordinary trace file:

Trace file /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace/V11_ora_31225.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.1.0/db_1

You are interested in the section that contains the create controlfile script.

Modify the trace file and use it as a script to create the control.

CREATE CONTROLFILE REUSE DATABASE "V11" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata/V11/redo01.log' SIZE 50M,
GROUP 2 '/oradata/V11/redo02.log' SIZE 50M,
GROUP 3 '/oradata/V11/redo03.log' SIZE 50M
DATAFILE
'/oradata/V11/system01.dbf',
'/oradata/V11/sysaux01.dbf',
'/oradata/V11/undotbs01.dbf'
'/oradata/V11/user01.dbf'
CHARACTER SET WE8MSWIN1252
;

ALTER TABLESPACE TEMP_TEST ADD TEMPFILE '˜/oradata/V11/temp01.dbf'™ reuse;

It is important to delete everything above the "CREATE CONTROLFILE" and
everything after the CHARACTER SET. Ensure you leave the semi colon. ";".
In the above example we are choosing the NORESETLOGS option and running the
database in archivelog mode. After successfully saving the script you are now
able to recreate the controlfile. When shutting down the database ensure that
you shutdown with the immediate option.

SQL> shutdown immediate;

SQL> startup nomount;

SQL>@control.sql

Note: After recreating the controlfile ensure you add the existing TEMP files:

Example:

alter tablespace temp_ts add tempfile '˜/oradata/V11/temp01.dbf'™ reuse;

Once the controlfile is successfully created the database is automatically
mounted. If you have opened the database with a resetlogs it is important to
take a backup asap.

Creating a new controlfile from a database that is not able to mount.

Under the rare occasion that you do not have a controlfile to either:

1. Restore

2. or have a script from a "backup controlfile to trace script"

you must create a script from the beginning.

CREATE CONTROLFILE REUSE DATABASE "DBNAME" NORESETLOGS ARCHIVELOG

Follow the format listing:

- Location of redo logs.

- Location of datafiles

- Specifying the characterset.

Once you have listed all files correctly you are ready to recreate your controlfile

SQL> startup nomount;

SQL>@control.sql

Recovering From A Lost Control File [ID 1014504.6]

Recovering From A Lost Control File [ID 1014504.6]

Problem Description:

===============

This is the recovery scenario in which a control file has been lost or damaged to a point that Oracle cannot recognize it anymore. Trying to start up the database will result in an ORA-00205 ("error in identifying control file '%s'") and an operating system level Oracle error such as ORA-07360 ("sfifi: stat error, unable to obtain information about file.") in Oracle7 or ORA-27037 (unable to obtain file status) for Oracle8.

This entry contains the solution for this problem.

Solution Description:

===============

The solution depends on whether you have lost one of the copies of the control file in a mirrored configuration or all copies of the current control file (regardless of mirroring).

I. ONE OF THE MIRRORED COPIES OF THE CONTROL FILE IS LOST

----------------------------------------------------------------------------------------------
In this case, one of the other copies of the control file should be used to bring the database up.

1. If the instance is still up, do a shutdown abort.

2. Use a good copy of the control file to copy onto the location of the lost one. If you are having hardware problems with the current location of the lost control file, such as disk or controller failures, choose another location for copying the file and then edit the "init.ora" file for this instance (or "config.ora" file in an ifile configuration) ccordingly. Edit the CONTROL_FILES parameter to reflect the current location of the control files after you have done the copy.

3. Start up the database.

II. ALL CURRENT COPIES OF THE CONTROL FILE ARE LOST

-----------------------------------------------------------------------------------
An important factor in this scenario is the existence of a backup trace of the control file generated with the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command:

- If you do have such a trace file and it reflects the current structure of the database in terms of datafiles and log files, you can edit it and run it as a SQL script to recreate the control file(s), as described in the Reference to Note:1012929.6.

- If you do not have the controlfile and are using RMAN review Note.372996.1 Using RMAN to Restore and Recover a Database When the Repository and Spfile/Init.ora Files Are Also Lost

- If you have neither the trace file nor a backup of the control file, you will have to issue the CREATE CONTROLFILE statement manually, as described in the Oracle Server SQL Reference Manual. You may have to resort to operating system facilities (such as the "find" command in Unix) to make sure that your CREATE CONTROLFILE statement does include ALL the existing datafiles and redo log files in the database. Here are the steps:

1. Take a full backup of the database, including all datafiles and redo log files.

2. Connect / as sysdba or as sys user and do a STARTUP NOMOUNT.

3. Issue the CREATE CONTROLFILE statement.

Example:

CREATE CONTROLFILE REUSE DATABASE "P716" NORESETLOGS NOARCHIVELOG
               MAXLOGFILES 50
               MAXLOGMEMBERS 3
               MAXDATAFILES 300
               MAXINSTANCES 8
               MAXLOGHISTORY 500
               LOGFILE
                               GROUP 1 '/u01/oracle/7.1.6/dbs/log1p716.dbf' SIZE 1M,
                               GROUP 2 '/u01/oracle/7.1.6/dbs/log2p716.dbf' SIZE 1M,
                               GROUP 3 '/u01/oracle/7.1.6/dbs/log3p716.dbf' SIZE 1M
               DATAFILE
                               '/u01/oracle/7.1.6/dbs/systp716.dbf' SIZE 40M,
                               '/u01/oracle/7.1.6/dbs/tempp716.dbf' SIZE 1M,
                               '/u01/oracle/7.1.6/dbs/toolp716.dbf' SIZE 15M ;

4. Perform media recovery on the database.

RECOVER DATABASE

5. Open the database.

ALTER DATABASE OPEN;

6. At the first opportunity, shut the database down (normal or immediate) and take a full cold backup.

References:

===========
Note:735106.1 HOW TO RECREATE THE CONTROL FILE
Note.403883.1 How To Restore Controlfile From A Backupset Without A Catalog Or Autobackup

Search Words:

=============

ORA-205 ORA-7360 ORA-27037