Re: Lost controlfile

From: Joshua Goodstein <awing_pilot_at_yahoo.com>
Date: 4 Feb 2002 07:37:57 -0800
Message-ID: <924a90f8.0202040737.4d5bf776@posting.google.com>

Alter database backup control file to trace;

go to your udump dir and edit the .trc file cutt the header until the first liune is

startup nomount

save the file and run it as a sql script. this effectively re-creates your DB

you can find more specifics on MetaLink Note:1012929.6

Here it is if you don't have access:

Doc ID: Note:1012929.6
Subject: How to Recreate the Controlfile Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 04-OCT-1995
Last Revision Date: 27-APR-2001

PURPOSE
This article describes how you can recreate your controlfile.

SCOPE & APPLICATION
For DBAs who need to recreate the controlfile.

WARNING:

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.
Oracle Customer Support advises you to do so.
If you are moving your database to another machine which is running the same operating system but the location of the datafiles, logfiles is not the same.

Instructions:

I. CREATING A NEW CONTROL FILE FROM THE EXISTING CONTROL FILE:

If you are running Oracle7 or higher you can get Oracle to generate a script for you that enables you to recreate the controlfile. Run the following command while the database is mounted or open and connected as a user with DBA privileges:

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

Oracle6 does not have this feature and therefore you will need to build

the CREATE CONTROLFILE statement yourself. The syntax is discussed in

detail in the Oracle SQL Reference Guide.

2. The trace file will be stored in the USER_DUMP_DEST destination,

which is set to "$ORACLE_HOME/rdbms/log" by default on Unix platforms.

To find out what USER_DUMP_DEST is set to, follow one of the following:

Look in the parameter file (init<SID>.ora on UNIX and Windows NT, <node>_<ora_sid>_init.ora on VMS) for the parameter:

USER_DUMP_DEST = d:/oradata/orcl/trce/udump

b) Using SQL*PLus you can issue the following command:

      SQL> SELECT   value
        2> FROM     v$parameter
        3> WHERE    name = 'user_dump_dest';

      VALUE
      ------------------------------------------------
      d:/oradata/orcl/trace/udump

c) Using Server Manager you can issue the following command:

       SVRMGR> show parameter <string>
       SVRMGR> 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

In this example, the file "p716_ora_26220.trc" is the trace file produced that contains a script to create the control file.

3. 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 prior to the words STARTUP NOMOUNT, and make any other desired changes, such as increasing MAXDATAFILES,
MAXLOGFILES, etc.

Sample:

<start trace>
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;

<end trace>
4. Shutdown the database (NORMAL, IMMEDIATE, TRANSACTIONAL (Oracle8 only) but not ABORT).

SVRMGR> shutdown immediate

5. Take a full database backup.

6. Rename/move the existing database controlfiles to a backup. You cannot

use the REUSE option for the CREATE CONTROLFILE if you are increasing

the size of the controlfile. The size of the controlfile will be increased

by increasing the value of MAXDATAFILES, MAXLOGMEMBERS, etc.

Example:

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

7. Create the controlfile within Server Manager                    
              
	SVRMGR> connect internal                     
	SVRMGR> @new_control.sql

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

8. At the first opportunity, shut the database down (normal, immediate or

transactional oracle8 only) 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 SQL*DBA or Server Manager and do a STARTUP NOMOUNT.
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.

SVRMGR> recover database;

5. Open the database.

SVRMGR> alter database open;

6. At the first opportunity, shut the database down and take a full cold

backup. Received on Mon Feb 04 2002 - 09:37:57 CST