Maintaining the control file in oracle9i

Mounting a database includes the following tasks

  .asscociating a database with a priviously started instance

  .locating and opening the control files specified in the parameter file

  .reading the control files to obtain the names and status of the datafiles and redo log files.[Howerver,no checks are performed to verify the existence of the data files and online redo log files at this time.]

 

Control File Contents

  .database name and identifier

  .time stamp of database creation

  .tablespace names

  .names and locations of data files and redo log files

  .current redo log sequence number

  .checkpoint information

  .begin and end of undo segments

  .redo log archive information

  .backup information

 

 

 

Multiplexing the Control File Using SPFILE

  1>alter the spfile

   sql>alter system set control_files='$ORACLE_HOME/oradata/u01/control01.ctl',

   '$ORACLE_HOME/oradata/u02/control02.ctl','$ORACLE_HOME/oradata/u03/control03.ctl'    scope=spfile;

 

  2>shutdown normal

   sql>shutdown immediate

 

  3>create new control files

    $ cp $ORACLE_HOME/oradata/u01/control01.ctl    $ORACLE_HOME/oradata/u01/control03.ctl

 

 

  4>start the database

    sql>startup

 

 

 

Multiplexing the Control File Using PFILE

   1>shutdown normal

    sql>shutdown immediate

 

   2>edit the PFILE

    control_files=('$ORACLE_HOME/oradata/u01/control01.ctl','$ORACLE_HOME/oradata/u01/control02.ctl','$ORACLE_HOME/oradata/u01/control03.ctl')

 

   3>create new control files

    $ cp $ORACLE_HOME/oradata/u01/control01.ctl    $ORACLE_HOME/oradata/u01/control03.ctl

 

 

   4>start the database

     sql>startup

 

 

 

Backup Controlfile After Database Structure Changes

  .alter database backup controlfile to 'filename';

 

  .alter database backup controlfile to trace;

    

  location

      $ORACLE_HOME/admin/instance_name/udump/xxx.trc

 

--------------------------------------------------------------------------

#     Set #1. NORESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager 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 "WENHUA01" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 4 (
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO4A.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO4B.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO4C.LOG'
  ) SIZE 1M,
  GROUP 5 (
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO5A.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO5B.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO5C.LOG'
  ) SIZE 1M
-- STANDBY LOGFILE
DATAFILE
  'D:\ORACLE9I\ORADATA\WENHUA01\SYSTEM01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\UNDOTBS01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\EXAMPLE01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\INDX01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\CATALOG01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\OEM01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\IVAN01.DBF'
CHARACTER SET ZHS16GBK

;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE9I\ORADATA\WENHUA01\TEMP01.DBF'
     SIZE 41943040  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
# End of tempfile additions.
#
#     Set #2. RESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# The contents of online logs will be lost and all backups will
# be invalidated. Use this only if online logs are damaged.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "WENHUA01" RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 4 (
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO4A.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO4B.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO4C.LOG'
  ) SIZE 1M,
  GROUP 5 (
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO5A.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO5B.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO5C.LOG'
  ) SIZE 1M
-- STANDBY LOGFILE
DATAFILE
  'D:\ORACLE9I\ORADATA\WENHUA01\SYSTEM01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\UNDOTBS01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\EXAMPLE01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\INDX01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\CATALOG01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\OEM01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\IVAN01.DBF'
CHARACTER SET ZHS16GBK
;

 

# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE9I\ORADATA\WENHUA01\TEMP01.DBF'
     SIZE 41943040  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
# End of tempfile additions.
#
--------------------------------------------------------------------------

 

 

[Metalink]  

---

Subject:	How to Recreate the Controlfile
	:	Note:1012929.6	Type:	BULLETIN
	Last Revision Date:	06-JUN-2006	Status:	PUBLISHED

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:   
--------------------------------------------------------------   
  
1.  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;   
  
   If you are running Oracle9i or higher you need to use sqlplus instead of 
   svrmgrl.
 
   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:

   a) Look in the parameter file (init.ora on UNIX and Windows NT,
      __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 
       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. 

   NOTE:  The trace file is handled a bit differently when issuing this
   command from a connection to the database using shared server.  The
   shared server connection is created by PMON and the connection inherits
   its environment, meaning the trace file will be created in the directory
   referenced by the initialization parameter BACKGROUND_DUMP_DEST 
   instead of the USER_DUMP_DEST.

   Use similar commands as given above to locate the directory 
   referenced in the BACKGROUND_DUMP_DEST.
  
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:  
   --------------------------  -----------------------------  
   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;  
  
   ----------------------  ----------------------------------  
  
 
4. Shutdown the database (NORMAL, IMMEDIATE, TRANSACTIONAL (Oracle8 only)
   but not ABORT).

       SVRMGR> shutdown immediate 

   If you are running Oracle9i or higher you need to use sqlplus instead of 
   svrmgrl.
  
5. Take a full database backup.
  
6. Rename/move the existing database controlfiles to a backup (The REUSE
   option will overwrite the original files). 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. 

   If you are running Oracle9i or higher you need to use sqlplus instead of 
   svrmgrl.
 
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 Oracle 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. 
 
       SVRMGR> recover database; 

   If you are running Oracle9i or higher you need to use sqlplus instead of 
   svrmgrl.
 
5. Open the database. 
 
       SVRMGR> alter database open; 

   If you are running Oracle9i or higher you need to use sqlplus instead of 
   svrmgrl.
 
6. At the first opportunity, shut the database down and take a full cold 
   backup.


Additional Errors:
------------------
ORA-205 ORA-7360 ORA-376 ORA-1110 ORA-1111