How to and when to recreate control files.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to and when to recreate control files.

Hybrid View

  1. #1
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    How to and when to recreate control files.

    Q. When to create control files ?
    A. Create controlfiles when
    1. You have lost all your control files.
    2. When you want to rename a database name (db_name)

    Q. How to create control files.

    You need a create controlfile script for recreating control files.
    Code:
    SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 17:04:00 2003
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Enter user-name: sys as sysdba
    Enter password:
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
    With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.2.0 - Production
    
    SQL> alter database backup controlfile to trace;
    
    Database altered.
    
    SQL>
    This will create a trace file in the udump directory.
    Im my case it was

    E:\oracle\admin\ORCL\udump\orcl_ora_2052.trc

    Edit the file to point the path of the datafiles and redologfiles.

    Code:
    CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
        MAXLOGFILES 5
        MAXLOGMEMBERS 3
        MAXDATAFILES 14
        MAXINSTANCES 1
        MAXLOGHISTORY 226
    LOGFILE
      GROUP 1 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 100M,
      GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 100M,
      GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 100M
    DATAFILE
      'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
      'E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
      'E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
      'E:\ORACLE\ORADATA\ORCL\INDX01.DBF',
      'E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF',
      'E:\ORACLE\ORADATA\ORCL\USERS01.DBF',
      'E:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.DBF',
      'E:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF',
      'E:\ORACLE\ORADATA\ORCL\DRSYS01.DBF',
      'E:\ORACLE\ORADATA\ORCL\ODM01.DBF',
      'E:\ORACLE\ORADATA\ORCL\XDB01.DBF',
      'E:\ORACLE\ORADATA\ORCL\USERS02.DBF',
      'E:\ORACLE\ORADATA\ORCL\USERS03.DBF',
      'E:\ORACLE\ORADATA\ORCL\USERS04.DBF'
    CHARACTER SET WE8MSWIN1252
    ;
    
    ALTER DATABASE OPEN RESETLOGS;
    and rename it to ctl.sql

    Then startup the database in nomount mode and run the ctl.sql file as sys as sysdba user

    Code:
    SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 17:25:14 2003
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Enter user-name: sys as sysdba
    Enter password:
    Connected to an idle instance.
    
    SQL> startup nomount
    ORACLE instance started.
    
    Total System Global Area   80812648 bytes
    Fixed Size                   453224 bytes
    Variable Size              54525952 bytes
    Database Buffers           25165824 bytes
    Redo Buffers                 667648 bytes
    SQL> @E:\oracle\admin\ORCL\udump\ctl.sql
    
    Control file created.
    
    
    Database altered.
    
    SQL> select status from v$instance;
    
    STATUS
    ------------------------------------
    OPEN
    
    SQL> select name from v$controlfile;
    
    NAME
    --------------------------------------------------------
    E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
    E:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
    E:\ORACLE\ORADATA\ORCL\CONTROL03.CTL
    To rename the database change reuse to set in the create control file script as shown below

    Code:
    CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  ARCHIVELOG
        MAXLOGFILES 5
        MAXLOGMEMBERS 3
        MAXDATAFILES 14
        MAXINSTANCES 1
        MAXLOGHISTORY 226
    LOGFILE
      GROUP 1 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 100M,
      GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 100M,
      GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 100M
    DATAFILE
      'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
      'E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
      'E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
      'E:\ORACLE\ORADATA\ORCL\INDX01.DBF',
      'E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF',
      'E:\ORACLE\ORADATA\ORCL\USERS01.DBF',
      'E:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.DBF',
      'E:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF',
      'E:\ORACLE\ORADATA\ORCL\DRSYS01.DBF',
      'E:\ORACLE\ORADATA\ORCL\ODM01.DBF',
      'E:\ORACLE\ORADATA\ORCL\XDB01.DBF',
      'E:\ORACLE\ORADATA\ORCL\USERS02.DBF',
      'E:\ORACLE\ORADATA\ORCL\USERS03.DBF',
      'E:\ORACLE\ORADATA\ORCL\USERS04.DBF'
    CHARACTER SET WE8MSWIN1252
    ;
    
    ALTER DATABASE OPEN RESETLOGS;
    HTH

    Regards
    Last edited by adewri; 02-28-2003 at 07:25 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Good stuff Amar!
    Perhaps you should point out that "alter database backup controlfile to trace;" needs to be done BEFORE you loose the control files!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Feb 2013
    Posts
    1
    Dear ,
    if you have lost all control files then do the following.

    1. create new database using dbca on the same database server version.
    2. create control file script as above.
    3. Move new data files to some other place and copy old data files of your database on the new server.
    4. Modify control file script so that it points to copied data files (Add all data files path).
    5. now create new control file with the modified control file script.
    6. Recover your database.(see database recovery process).

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by DaPi
    Good stuff Amar!
    needs to be done BEFORE you loose the control files!
    Please note that
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width