How Change ORACLE_SID & DB_NAME

Overview

This month, I will describes how to find and change the "db_name" for a database, or the ORACLE_SID for an instance, without recreating the database.

Query the data dictionary to find the current DB_NAME and ORACLE_SID

Query the views v$database and v$thread.

• V$DATABASE gives DB_NAME
• V$THREAD gives ORACLE_SID

ORACLE_SID = DB_SID and db_name = DBNAME: To find the current value of ORACLE_SID:

SVRMGR> select instance from v$thread;

     INSTANCE
     ----------------
     DB_SID

To find the current value of DB_NAME:

SVRMGR> select name from v$database;

     NAME
     ---------
     DBNAME

How to modify the database to run under a new ORACLE_SID

1.  Shutdown the instance  
 
2.  Backup all control, redo, and data files.  
 
3.  Go through the .profile, .cshrc, .login, oratab, tnsnames.ora,  
    (for SQL*Net version 2), and redefine the ORACLE_SID environment 
    variable to a new value.  
 
    For example, search through disks and do a grep ORACLE_SID *  
 
4.  Change locations to the "dbs" directory 
 
       % cd $ORACLE_HOME/dbs   
 
    and rename the following files:  
 
    o   init.ora  (or use pfile to point to the init file.)  
    o   control file(s). This is optional if you do not rename any 
        of the controlfiles, and the control_files parameter is used.  
        The "control_files" parameter is set in the "init.ora" file 
        or in a file it references with the ifile parameter.  Make  
        sure that the control_files parameter does not point to old 
        file names, if you have renamed them.  
    o   "crdb.sql" & "crdb2.sql",  This is optional.  These are  
        only used at database creation.  
 
5.  Change locations to the "rdbms/admin" directory 
 
       % cd $ORACLE_HOME/rdbms/admin    
    
    and rename the file:  
 
    o   startup.sql.  This is optional.  On some platforms,  
        this file may be in the "$ORACLE_HOME/rdbms/install" directory. 
        Make sure that the contents of this file do not reference old  
        init.ora files that have been renamed.  This file  
        simplifies the "startup exclusive" process to start your database.  
 
6.  To rename the database files and redo log files, you would follow the  
    instructions in [NOTE:9560.1]. 
 
7.  Change the ORACLE_SID environment variable to the new value. 
 
8.  Check in the "$ORACLE_HOME/dbs" directory to see if the password  
    file has been enabled.  If enabled, the file "orapw" will 
    exist and a new password file for the new SID must be created  
    (renaming the old file will not work).  If "orapw" does not 
    exist, skip to step 9.  To create a new password file, issue 
    the following command as oracle owner: 
  
       orapwd file=orapw password=?? entries= 
 
9.  Start up the database and verify that it works.  Once you have done  
    this, shutdown the database and take a final backup of all control,  
    redo, and data files.  
 
10. When the instance is started, the control file is updated with the  
    current ORACLE_SID.

How to the "db_name" for a Database

1.  Login to Server Manager 
 
        % svrmgrl 
        SVRMGR> connect internal  

2.  Type

        SVRMGR> alter system switch logfile;

    to force a checkpoint.
  
3.  Type 
 
        SVRMGR> alter database backup controlfile to trace resetlogs;  
      
    This will create a trace file containing the "CREATE CONTROLFILE" 
    command to recreate the controlfile in its current form.  
 
4.  Shutdown the database and exit SVRMGR 
 
        SVRMGR> shutdown

        SVRMGR> exit 

    The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.
    It must not be shutdown abnormally using SHUTDOWN ABORT.
 
5.  Change locations to the directory where your trace files are located.  
    They are usually in the "$ORACLE_HOME/rdbms/log" directory.  If  
    "user_dump_dest" is set in the "init.ora" file, then go to the  
    directory listed in the "user_dump_dest" variable.  The trace file will 
    have the form "ora_NNNN.trc with NNNN being a number.   

6.  Copy the contents of the trace file starting from the line with
    STARTUP NOMOUNT down to the end of the trace file and put it in
    a new file called something like "ccf.sql".

7.  Edit the "ccf.sql" file   
 
    FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS ...  
      TO: CREATE CONTROLFILE set DATABASE "newdbname"  RESETLOGS ...  

    Change the word 'REUSE' to 'set' and the 'olddbname' to 'newdbname'.

    It is possible to recreate the controlfile using the syntax:
          CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS ...

    But this syntax will allow the existing controlfiles to be overwritten
    without giving an error.

    FROM:
    # 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
    TO:
    # 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

    The last command in ccf.sql should be:
    alter database open resetlogs

8.  Save and exit the "ccf.sql" file 
  
9.  Rename the old control files for backup purposes and so that they do
    not exist when creating the new ones.  
 
10. Edit the "init.ora" file so that db_name="newdb_name" .  
 
11. Login to Server Manager 
 
        % svrmgrl
        SVRMGR> connect internal
 
12. Run the "ccf.sql" script 
 
        SVRMGR> @ccf  

    This will issue a startup nomount, and then recreate the controlfile.

    If, at this point, you receive the error that a file needs media recovery, 
    the database was not shutdown normally as specified in step 4. You can
    try recovering the database using the redo in the current logfile, by
    issuing:

    SVRMGRL>  recover database using backup controlfile until cancel;

    This will prompt for an archived redologfile. It may be possible to 
    open the database after applying the current logfile. BUT this is not 
    guaranteed. 

    To apply the necessary redo, you need to check the online logfiles 
    and apply the one with the same sequence number as reported in the
    message. This usually is the logfile with status=CURRENT. If not apply,
    the logfiles in turn until the logfile with status=CURRENT has been 
    applied.  If, after applying the current logfile, the database will 
    not open then it is highly likely that the operation must be restarted 
    having shutdown the database normally.
 
    To find a list of the online logfiles:

    SVRMGR> select group#, seq#, status from v$log;
    GROUP#     SEQUENCE#   STATUS
    ---------- ---------   ----------------
             1 123         CURRENT     <== this redo needs to be applied
             2 124         INACTIVE
             3 125         INACTIVE
             4 126         INACTIVE
             5 127         INACTIVE
             6 128         INACTIVE
             7 129         INACTIVE

    7 rows selected.
 
    SVRMGR> select member
              from v$logfile
             where GROUP# = 1;

    Member
    ------------------------------------
    /u02/oradata/V815/redoV81501.log
   
    After applying the current online log file the following prompt should
    be displayed:

    Log Applied
    Media Recovery Complete

    At this point the database can be opened with:
 
    SVRMGR> alter database open resetlogs;  
 
13. You may also need to change the global database name:

    alter database rename global_name to .

14. Make sure the database is working.   

15. Shutdown and backup the database.

by Jeff Hunter, Sr. Database Administrator
Source: http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_7.shtml