Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: how to rename a database SID

RE: how to rename a database SID

From: Cale, Rick T (Richard) <RICHARD.T.CALE_at_saic.com>
Date: Mon, 18 Jun 2001 10:08:15 -0700
Message-ID: <F001.0032D6DB.20010618101106@fatcity.com>

See
Note <FONT color=#0000ff
size=2>  Note:15390.1 Subject: How to Determine and Change DB_NAME or ORACLE_SID on MetaLink
<FONT color=#0000ff
size=2> 
Here it is
below
<FONT color=#0000ff
size=2> 
<FONT color=#0000ff
size=2>Rick
<FONT color=#0000ff
size=2> 
<FONT color=#0000ff
size=2> 
<FONT color=#0000ff
size=2> 
P<SPAN
class=961411117-18062001>urpose   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.
 
 

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Andrey Bronfin   [mailto:Andreyb_at_maxbill.com]Sent: Monday, June 18, 2001 12:56   PMTo: Multiple recipients of list ORACLE-LSubject: how   to rename a database SID
  HI ! How can i
  rename a database on NT / UNIX ? Thanks a   lot in advance ! Received on Mon Jun 18 2001 - 12:08:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US