8 Replies Latest reply on Jan 25, 2013 4:45 PM by TSharma-Oracle

    Confusion About Renaming a SID

      I've been tasked at my company to currently rename / change the current SID on our single instance 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;
      SQL> SELECT name FROM v$database;
      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
        • 1. Re: Confusion About Renaming a SID
          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
            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
              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

              • 4. Re: Confusion About Renaming a SID
                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)

                • 5. Re: Confusion About Renaming a SID
                  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
                  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
                    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
                      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
                        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