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.

Threaded 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!

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