RE: how to rename a database SID

[Cale, Rick T (Richard)]

Title: how to rename a database SID

See Note   Note:15390.1 Subject: How to Determine and Change DB_NAME or ORACLE_SID on MetaLink

 

Here it is below

 

Rick

 

 

 

Purpose
   This entry describes how to find and change the "db_name" for a database, or
   the ORACLE_SID for an instance, without recreating the database. 
 
 SCOPE & APPLICATION
   For DBAs requiring to either find or change the db_name or ORACLE_SID.

 

 RELATED DOCUMENTS
   [NOTE:1018634.102]  AFTER RENAMING THE DATABASE SELECT FROM DUAL RETURNS
                       OLD DATABASE NAME
   [NOTE:9560.1]       ALTER TABLESPACE/DATABASE TO RENAME FILES

 

 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

 

 If 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
  

 

 Modifying a database to run under a new ORACLE_SID: 
 ===================================================
  
 1.  Shutdown the instance 

 

     The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.
     It must not be shutdown abnormally using SHUTDOWN ABORT.
 
 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<sid>.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<SID>.ora" file
         or in a file it references with the ifile parameter.  Make 
         sure that the control_file parameter does not point to old
         file names, if they have been renamed. 
     o   "crdb<sid>.sql" & "crdb2<sid>.sql",  This is optional.  These are 
         only used at database creation. 
 
 5.  To rename the database files and redo log files, follow the 
     instructions in [NOTE:9560.1].
 
 6.  Change the ORACLE_SID environment variable to the new value.
 
 7.  Check in the "$ORACLE_HOME/dbs" directory to see if the password 
     file has been enabled.  If enabled, the file "orapw<OLD_SID>" will
     exist and a new password file for the new SID must be created 
     (renaming the old file will not work).  If "orapw<OLD_SID>" does not
     exist, skip to step 8.  To create a new password file, issue
     the following command as oracle owner:
  
        orapwd file=orapw<NEWSID> password=?? entries=<number of users to be
        granted permission to start the database instance>
 
 8.  Start up the database and verify that it works.  Once this is done, 
     shutdown the database and take a final backup of all control, redo,
     and data files. 

 

     The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.
     It must not be shutdown abnormally using SHUTDOWN ABORT.

 

 9.  When the instance is started, the control file is updated with the 
     current ORACLE_SID. 
   

 

 Changing 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 the trace files are located. 
     They are usually in the "$ORACLE_HOME/rdbms/log" directory.  If 
     "user_dump_dest" is set in the "init<SID>.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<SID>.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, an error stating that a file needs media recovery
     is reported, then the database was not shutdown normally as specified
     in step 4. 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, 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. The global database name may also need to be changed:

 

     alter database rename global_name to <newdb_name>.<domain>

 

     See [NOTE:1018634.102] for further detail.

 

 14. Make sure the database is working.  

 

 15. Shutdown and backup the database.

 

     The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.
     It must not be shutdown abnormally using SHUTDOWN ABORT.

 

 

-----Original Message-----
From: Andrey Bronfin [mailto:[EMAIL PROTECTED]]
Sent: Monday, June 18, 2001 12:56 PM
To: Multiple recipients of list ORACLE-L
Subject: how to rename a database SID

HI !
How can i rename a database on NT / UNIX ?
Thanks a lot in advance !