Question Details [ID: 318216852435]
              
             Print 
             Help 

         QuestionsSearch/Archives

        Search  Archives  [ Question Details ]  

        
                    
                    
            Ken -- Thanks for the question regarding "What is a SID, how to 
            change it, how to find out what it is.", version Oracle 8.0

            You Asked

Hi Tom,

A trivia question for you, what is SID and what is it used for?  
Would changing it have any impact on the different instances of 
the database?  And lastly, where can I find out the SID of my 
database?  I don't seem to able to find it in 
technet.oracle.com.

Please explain.

Thanks 
GoJo.


            and we said...

The SID is a site identifier.  It plus the Oracle_home are 
hashed together in Unix to create a unique key name for 
attaching an SGA.  If your Oracle_sid or Oracle_home is not set 
correctly, you'll get "oracle not available" since we cannot 
attach to a shared memory segment that is identified by magic 
key.  On NT, we don't use shared memory but the SID is still 
important.  We can have more then 1 database on the same oracle 
home so we need a way to id them.

Changing it harder then it looks.  I know you are on unix, so 
here are the steps for changing it (or the database name) under 
Unix - they are different on NT.

How to find the sid -- "select instance from v$thread" will do 
that.


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.


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  
 
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_files parameter does not 
point to old file names, if you have renamed them.  
    o   "crdb<sid>.sql" & "crdb2<sid>.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<sid>.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<SID>.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<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 9.  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> 
 
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.  
   

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 your 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.  Get the "CREATE CONTROLFILE" command from 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" 
NORESETLOGS ...  
      TO: CREATE CONTROLFILE set DATABASE "newdbname"  RESETLOGS 
...  

    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

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, 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;

    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. 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 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. 
 
    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

    The last command in ccf.sql should be:
 
    SVRMGR> alter database open resetlogs;  
 
13. You may also need to change the global database name:

    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.
                    
            Was this response helpful to you? Let us know!   Reviews   



            Bookmark this page with the link HERE 
                        Information 


                               This page provides the details of the question 
                              asked. To find another question click on the 
                              search tab. To view the question archives by week 
                              click on the archives tab. 








       
      Copyright  2001 Oracle Corporation, All rights reserved. 


