DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How to and when to recreate control files.

  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 08: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
    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!

  4. #4
    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).

  5. #5
    Join Date
    Apr 2015
    Posts
    2
    Hi, your post is something new to me and helpful. Anyway, upon a thorough search about the topic , I found out that the first control files of an oracle database are created when you issue the create database statement. On the other side, we are able to manually create a new control file for a database using the create control file statement. This is the reason why control file is recreated. I just hope, I make sense with this

  6. #6
    Join Date
    Aug 2017
    Posts
    2
    What about the db references in the spfile when you want to rename the db? When running the create controlfile command I got this error:
    ERROR at line 1:
    ORA-01503: CREATE CONTROLFILE failed
    ORA-01504: database name 'FOO1' does not match parameter db_name 'foo'

    I created pfile from spfile, changed all db references there, and converted back to spfile.
    Then it worked!

    Somehow offtopic, but also edit name in /etc/oratab to reflect name change for dbca.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    i'm using Adewri's code as an example. Instead of the word reuse, you need to use the word "SET".
    Keep in mind that the Oracle database has changed significantly in the last 14 years. The original
    post was written for 7.3.4, 8.0 or 8i. Few people used RMAN back when this post was written.
    Now, you can restore the control file and use the nid program to change the database name.

    Therefore you don't specifically need to recreate the control files. Is there a specific problem
    that you are trying to solve?

    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;

  8. #8
    Join Date
    Aug 2017
    Posts
    2
    I'm learning about the CloneDB feature using copy on write with Direct NFS introduced in Oracle 11. In a first step you need to create a db clone and creating a cold backup plus creating controlfile is one method often described. I know it might not be the latest preferred method, but I'm still learning to understand what's going on under the hood.

  9. #9
    Join Date
    Jun 2021
    Posts
    1
    Quote Originally Posted by adewri View Post
    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

    All current copies of the control file have been lost or are corrupted.
    You are restoring a backup in which the control file is corrupted or missing.
    You need to change a hard limit database parameter in the controlfile.

  10. #10
    Join Date
    Apr 2011
    Location
    Largo, FL.
    Posts
    44
    Please note - this thread started 02-28-2003, 08:19 AM

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