Re: Lost Control Files

From: Beverly Hom <bhom_at_sprintmail.com>
Date: Wed, 03 Jun 1998 13:57:02 -0700
Message-ID: <3575B89D.4D551C22@sprintmail.com>

Hi,

You did not specify what operating system/platform, so I am recording the procedure for Unix.
For Windows NT, the commands are different.

How to Recreate A Controlfile

You should only need to recreate your control file under very special circumstances:

All current copies of the control file have been lost or are corrupted.
You need to change a "hard" database parameter that was set when the database was first created, such as MAXDATAFILES, MAXLOGFILES, MAXLOGHISTORY, etc.
You are restoring a backup in which the control file is corrupted or missing. I. CREATING A NEW CONTROL FILE FROM THE EXISTING CONTROL FILE:
1. Get a backup trace of the existing control file:

        % svrmgrl
        SVRMGRL> connect internal
        SVRMGRL> startup mount
        SVRMGRL> alter database backup controlfile to trace;

This will create a trace file in USER_DUMP_DEST which is set to $ORACLE_HOME/rdbms/log by default in Unix platforms.

To find out what USER_DUMP_DEST is set to:

SVRMGRL> show parameter user_dump_dest;

The easiest way to locate the correct trace is to look at its date. A file will exist with the current date and time. The naming convention for these files is operating system specific.

Example:

        % cd $ORACLE_HOME/rdbms/log
        % ls -l
-rw-r--r--   1 osupport dba 2315 Oct  3 16:39 alert_p733.log
-rw-r--r--   1 osupport dba         1827 Oct3 16:39 p733_ora_26220.trc

2. Modify the trace file and use it as a script to create the control file. Copy the trace file to a script file, such as 'new_control.sql', delete the header information and make any other desired changes, such as increasing MAXDATAFILES, MAXLOGFILES, etc.

Sample:

Dump file /u01/oracle/7.3.3/rdbms/log/p733_ora_26220.trc Oracle7 Server Release 7.3.3.0 - Production Release With the distributed and replication options PL/SQL Release 2.3.3.0 - Production
ORACLE_HOME = /u01/oracle/7.3.3
ORACLE_SID = p733
Oracle process number: 9 Unix process id: 26220 System name: SunOS

Node name:      tcsun2
Release:        5.4
Version:        Generic_101945-27

Machine: sun4m

Tue Oct 3 16:39:13 1995
*** SESSION ID:(6.61)
# The following commands will create a new control file and use it
# to open the database.
# No data other than log history will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "P733" NORESETLOGS NOARCHIVELOG     MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 8
    MAXLOGHISTORY 800
LOGFILE

  GROUP 1 '/u01/oracle/7.3.3/dbs/log1p733.dbf'  SIZE 500K,
  GROUP 2 '/u01/oracle/7.3.3/dbs/log2p733.dbf'  SIZE 500K,
  GROUP 3 '/u01/oracle/7.3.3/dbs/log3p733.dbf'  SIZE 500K

DATAFILE

  '/u01/oracle/7.3.3/dbs/systp733.dbf' SIZE 40M,
  '/u01/oracle/7.3.3/dbs/tempp733.dbf' SIZE 550K,
  '/u01/oracle/7.3.3/dbs/toolp733.dbf' SIZE 15M

;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.

ALTER DATABASE OPEN;

==> NOTE: The header is everything above these comments:

# The following commands will create a new control file and use it
# to open the database.
# No data other than log history will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.

3. Shutdown the database (NORMAL or IMMEDIATE, but not ABORT).

4. Take a full database backup (recommended).

5. Rename/move the existing control files to a backup:

Example:

        % cd $ORACLE_HOME/dbs
        % mv ctrlV733.ctl ctrlV733.bak

6. Create the controlfile within SVRMGRL:

        SVRMGRL> connect internal
        SVRMGRL> @new_control.sql

If you get the "Statement processed" message, the database will be open with a brand new control file.

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

II. CREATING A NEW CONTROL FILE WITHOUT AN EXISTING CONTROL FILE:

CREATE CONTROLFILE SYNTAX:
The following is information on the create control file syntax. This information is fully documented in the Oracle7 SQL Reference Manual.

CREATE CONTROLFILE [REUSE]
   DATABASE name
   [LOGFILE filespec [, filespec] ...]
    RESETLOGS | NORESETLOGS

   [MAXLOGFILES integer]
   [DATAFILE filespec [, filespec] ...]
   [MAXDATAFILES integer]
   [MAXINSTANCES integer]
   [ARCHIVELOG | NOARCHIVELOG]
   [SHARED | EXCLUSIVE]

The complete procedure follows:

Take a full backup of the database, including all datafiles and redo log files.
Go into Server Manager and do a STARTUP NOMOUNT.
Issue the CREATE CONTROLFILE statement.

Example:

        CREATE CONTROLFILE REUSE DATABASE "P733" NORESETLOGS NOARCHIVELOG
        MAXLOGFILES 50
        MAXLOGMEMBERS 3
        MAXDATAFILES 300
        MAXINSTANCES 8
        MAXLOGHISTORY 500
        LOGFILE
                GROUP 1 '/u01/oracle/7.3.3/dbs/log1p733.dbf'  SIZE 1M,
                GROUP 2 '/u01/oracle/7.3.3/dbs/log2p733.dbf'  SIZE 1M,
                GROUP 3 '/u01/oracle/7.3.3/dbs/log3p733.dbf'  SIZE 1M
        DATAFILE
                '/u01/oracle/7.3.3/dbs/systp733.dbf' SIZE 40M,
                '/u01/oracle/7.3.3/dbs/tempp733.dbf' SIZE 1M,
                '/u01/oracle/7.3.3/dbs/toolp733.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 and take a full cold

backup.

Alan Brown wrote:

> Due to a series of errors, some on my part, our Oracle 7.3 database has
> been left without any control files (and no backups) and we obviously
> cannot re-start the instance.
>
> All of the datafiles are intact. Is there any way to re-create the controls
> files  ... please!
>
> Regards
>
> Alan

Received on Wed Jun 03 1998 - 15:57:02 CDT