Re: You lost conrol file....

From: PaulCinVT <paulcinvt_at_aol.com>
Date: 29 Oct 1999 18:22:04 GMT
Message-ID: <19991029142204.13136.00000051@ngol06.aol.com>

How To Recreate the Control File

Affected Platforms : Generic: not platform specific Affected Products : Oracle7 Server
Affected Components : RDBMS V07.XX

This entry describes how you can recreate your controlfile. WARNING: You should only need to recreate your control file under the following 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. Oracle Customer Support advises you to do so.

I. CREATING A NEW CONTROL FILE FROM THE EXISTING CONTROL FILE 1. Get a backup trace of the existing control file:

% sqldba lmode=y

SQLDBA> connect internal
SQLDBA> startup mount
SQLDBA> 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:

SQLDBA> 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_p716.log
-rw-r--r--   1 osupport dba         1827 Oct3 16:39 p716_ora_26220.trc

p716_ora_26220.trc contains a script to create the control file. 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.1.6/rdbms/log/p716_ora_26220.trc Oracle7 Server Release 7.1.6.2.0 - Production Release With the distributed and replication options PL/SQL Release 2.1.6.2.0 - Production
ORACLE_HOME = /u01/oracle/7.1.6
ORACLE_SID = p716
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 "P716" NORESETLOGS NOARCHIVELOG     MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 8
    MAXLOGHISTORY 800
LOGFILE

  GROUP 1 '/u01/oracle/7.1.6/dbs/log1p716.dbf'  SIZE 500K,
  GROUP 2 '/u01/oracle/7.1.6/dbs/log2p716.dbf'  SIZE 500K,
  GROUP 3 '/u01/oracle/7.1.6/dbs/log3p716.dbf'  SIZE 500K

DATAFILE

  '/u01/oracle/7.1.6/dbs/systp716.dbf' SIZE 40M,
  '/u01/oracle/7.1.6/dbs/tempp716.dbf' SIZE 550K,
  '/u01/oracle/7.1.6/dbs/toolp716.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 ctrlV716.ctl ctrlV716.bak
6. Create the controlfile within SQLDBA:

SQLDBA> connect internal
SQLDBA> @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:
1. Take a full backup of the database, including all datafiles and redo log files.

2. Go into SQL*DBA or Server Manager 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 and take a full cold backup.

Paul in VT Received on Fri Oct 29 1999 - 13:22:04 CDT