This discussion is archived
8 Replies Latest reply: Jan 25, 2013 8:45 AM by TSharma-Oracle RSS

Confusion About Renaming a SID

895327 Newbie
Currently Being Moderated
I've been tasked at my company to currently rename / change the current SID on our single instance 11.2.0.3 11g database server. I found Doc ID 15390.1 and it tells me the first step is to identify what I "assume" my current configuration parameters are:
SQL> select instance from v$thread;

INSTANCE
--------------------------------------------------------------------------------
cqdb


SQL> SELECT name FROM v$database;

NAME
---------
CQDB
So it appears my instance / SID is called "CQDB" which is correct. I need to change it to <TCQDB> on this Oracle database server so I consult the Doc ID above.

Step 1 = shutdown or shutdown immediate (no shutdown abort)
Step 2 = perform an entire full back up of the control file / redo / data files using RMAN utility
Step 3 = change UNIX / Linux environment variables for ORACLE_SID
Step 4 = change directory to $ORACLE_HOME/dbs & rename the following files:

a. init<SID>.ora (or use pfile to point to the init file)

This is where I get stuck. When I go to the specified directory in step # 4 and locate the file it wants me to rename, I only see a generic 'init.ora' file and the parameters inside that file are super generic. It says it's an example config with a SID = ORCL. I'm fairly sure that my Oracle database is using SPFILE as it's auto backed up when I use RMAN and I see it listed in my ls -l command:
[oracle@testcq dbs]$ ls -l
total 10132
-rw-rw---- 1 oracle oinstall     1544 Jan 17 15:42 hc_cqdb.dat
-rw-r--r-- 1 oracle oinstall     2851 Jul 13  2012 init.ora
-rw-r----- 1 oracle oinstall       24 Jul 13  2012 lkCQDB
-rw-r----- 1 oracle oinstall     1536 Jan 22 13:54 orapwcqdb
-rw-r----- 1 oracle oinstall 10338304 Nov  9 09:40 snapcf_cqdb.f
-rw-r----- 1 oracle oinstall     3584 Jan 23 23:44 spfilecqdb.ora
Do I simply just rename the spfilecqdb.ora to spfiletcqdb.ora?
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/u01/app/oracle/product
                                                 /11.2.0/db_1/dbs/spfilecqdb.or
  • 1. Re: Confusion About Renaming a SID
    Fran Guru
    Currently Being Moderated
    why don't do:

    SQL> create pfile=''/.../.../<your_new_pfile>' from spfile;

    modify the pfile

    SQL> startup pfile='/.../.../<your_new_pfile>
    SQL> create spfile from pfile='/.../.../<your_new_pfile>'
  • 2. Re: Confusion About Renaming a SID
    Simon_DBA Journeyer
    Currently Being Moderated
    Don't forget the SID and the DATABASE NAME are two different things. You probably want to change both as having a SID that doesn't match in some logical way to the DB_NAME may not make much sense.

    Changing the SID is simply a matter of creating a new init<SID>.ora or spfile<SID>.ora and updating the parameters within it accordingly. Changing the DB_NAME is a little bit more difficult and can involve rebuilding the control file. Or an easier way might be to simply duplicate your database using RMAN, then get rid of the old one if you're happy with the new database.

    And one last point: the "init.ora" (and "initdw.ora") file is just a template one in $OH/dbs. I always get rid of that immediately after installation. Same with $OH/network/admin/samples. Unless you really need these sample/template files just remove them to avoid confusion.
  • 3. Re: Confusion About Renaming a SID
    TSharma-Oracle Guru
    Currently Being Moderated
    You need to make spfile from pile

    SQL> create pfile='<location of pfile>' from spfile;

    This will create a pfile and han you can modify the instance_name parameter in newly created pfile. You have to make sure that when you restart your instance, you will ave to restart with newly modified pfile.

    This link shows how to change database name and dvid

    http://docs.oracle.com/cd/B14117_01/server.101/b10825/dbnewid.htm
  • 4. Re: Confusion About Renaming a SID
    Krishna-Oracle Pro
    Currently Being Moderated
    Hi,
    You can create pfile from spfile
    Also I request you to check
    Difference between DB name, DB global name, DBID and SID (Doc ID 1277854.1)


    Thanks,
    Krishna
  • 5. Re: Confusion About Renaming a SID
    895327 Newbie
    Currently Being Moderated
    So wait - I'm confused. I need to generate a 'pfile' from my existing 'spfile' that already exist in $ORACLE_HOME/dbs? I don't understand why I can't use my existing SPFILE to simply rename my ORACLE_SID?
    SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
           FROM sys.v_$parameter WHERE name = 'spfile';  2
    
    Init F
    ------
    SPFILE
    So just to be clear, if my servers instance is starting from SPFILE, and I don't have a PFILE at all on my system, I will need to gereate a PFILE as follows to simply rename my database instance name and SID?
    SQL> create pfile from spfile='/oracle/u01/app/oracle/product/11.2.0/db_1/dbs/spfilecqdb.ora';
    Sorry for being so dense but I just want to make sure I can't proceed w/o generating a PFILE...
  • 6. Re: Confusion About Renaming a SID
    TSharma-Oracle Guru
    Currently Being Moderated
    You can do that without generating pfile also.
    Just runn the following on your sql prompt:

    SQL> Alter system set instance_name = TCQDB scope=spfile;

    SQL> shutdown immediate;
    SQL> startup

    You will see your modified instance name.
  • 7. Re: Confusion About Renaming a SID
    TSharma-Oracle Guru
    Currently Being Moderated
    Deleted becaus ethe code was not fomatted. Please see the next post.

    Edited by: TSharma on Jan 25, 2013 11:43 AM
  • 8. Re: Confusion About Renaming a SID
    TSharma-Oracle Guru
    Currently Being Moderated
    In this example I changed my instance name from 'rptdbt' to 'test'
     SQL> show parameter instance
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    instance_name                        string      rptdbt
    
    SQL> alter system set instance_name = test scope=spfile;
    
    System altered.
    
    SQL> shu immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  314572800 bytes
    Fixed Size                  2065440 bytes
    Variable Size             180358112 bytes
    Database Buffers          125829120 bytes
    Redo Buffers                6320128 bytes
    Database mounted.
    Database opened.
    
    SQL> show parameter instance
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    instance_name                        string      TEST

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points