7 Replies Latest reply: Jul 29, 2010 6:07 AM by P.Forstmann RSS

    how to change the SID name in the server

    788095
      hi all,

      i installed oracle 10g R1 on windows 2003 server. i gave an sid and created a database. the database is new and there is no data in the db. now i want to change the SID name of the database. pls help me in doing this.

      regards,
      nagarjuna
        • 1. Re: how to change the SID name in the server
          orawiss
          1. do: alter database backup controlfile to trace;
          2. extract the "create controlfile" command from the
          background-dump-destination tracefile.
          3. shutdown the DB.
          4. Change the DB-Name in your init<SID>.ora and change the init<NEWSID>.ora
          5. for Unix : Change the SID in the /etc/oratab or /var/opt/oracle/oratab
          6. Change the SID in your environment and source it
          7. Startup the database to mount-status
          startup mount
          8. Re-Create the controlfile with the statement from position 2.
          9. Do a alter database rename global_name to <SID>
          10.Change the TNS-Configuration accordingly
          $ORACLE_HOME/network/admin/*.ora Look for SID and GLOBAL_NAME
          • 2. Re: how to change the SID name in the server
            Toni Lazarin
            785092 wrote:
            hi all,
            pls help me
            This link helped me once ,
            i hope it will do the needful for you , Changing ORACLE SID
            cheers
            • 3. Re: how to change the SID name in the server
              rajeysh
              Toni Lazarin wrote:
              785092 wrote:
              hi all,
              pls help me
              This link helped me once ,
              i hope it will do the needful for you , Changing ORACLE SID
              cheers
              OP is a newbie, may be he is an student or learner. encourage forum OP to develop their skills.

              785092      
                   Newbie
                   
              Handle:      785092
              Status Level:      Newbie
              Registered:      Jul 28, 2010
              Total Posts:      2
              Total Questions:      2 (2 unresolved)
              Posters, please mind these common-sense rules when participating here:
              
              - When asking a question, provide all the details that someone would need to answer it. 
              Consulting documentation first is highly recommended.
              - When answering a question, please be courteous and respectful; there are different levels of experience represented here.
               A poorly worded question is better ignored than flamed - or better yet, help the poster ask a better question.
              - It is considered good etiquette to reward answerers with points (as "helpful" - 5 pts - or "correct" - 10pts).
              - See more tips in the FAQ
              
              Thanks for doing your part to make this community as valuable as possible for everyone!
              
              - OTN 
              • 4. Re: how to change the SID name in the server
                user00726
                You can the your backup control file trace and making appropriatechanged on the same and recreating at the nomount instacne:

                eg:
                CREATE CONTROLFILE REUSE SET DATABASE "SID9" RESETLOGS ARCHIVELOG
                MAXLOGFILES 16
                MAXLOGMEMBERS 3
                MAXDATAFILES 100
                MAXINSTANCES 8
                MAXLOGHISTORY 292
                LOGFILE
                GROUP 1 '/oracle/oradata/SID9/redo01.log' SIZE 50M,
                GROUP 2 '/oracle/oradata/SID9/redo02.log' SIZE 50M,
                GROUP 3 '/oracle/oradata/SID9/redo03.log' SIZE 50M
                -- STANDBY LOGFILE
                DATAFILE
                '/oracle/oradata/SID9/system01.dbf',
                '/oracle/oradata/SID9/undotbs01.dbf',
                '/oracle/oradata/SID9/sysaux01.dbf',
                '/oracle/oradata/SID9/users01.dbf',
                '/oracle/oradata/SID9/example01.dbf'
                CHARACTER SET WE8ISO8859P9
                ;

                You have to idenitfy all the ablove datafiles,logfiles,undofiles etc.
                • 5. Re: how to change the SID name in the server
                  788095
                  hi thanks for your replies,
                  i am a newbie in dba, so need a step-by-step approach for the above issue. pls. help me in this regard.
                  • 6. Re: how to change the SID name in the server
                    Martin Kucera
                    Hi,

                    Just a notice: SID and DBNAME are two different things. But the usual practice is to have SID = DBNAME, so let me assume you want to change both SID and DBNAME.


                    Either you can do it manually by recreating the controlfile as orawiss suggests. You may discover some challenges around the way (for instance, for the command CREATE CONTROLFILE SET "new_dbname" to work, you have do delete or rename the old controlfiles first).

                    Or, you can change it easier by using nid utility. The detailed howto and all implications can be found on:
                    http://download.oracle.com/docs/cd/B14117_01/server.101/b10825/dbnewid.htm

                    Basically:
                    SQL> create pfile from spfile;
                    SQL> shutdown immediate;

                    nid TARGET=sys/password@oldname DBNAME=newname

                    copy the initoldname.ora to initnewname.ora and edit the DB_NAME parameter to reflect the new name
                    sqlplus / as sysdba
                    SQL> create spfile from pfile;
                    SQL>startup mount;
                    SQL> alter database open resetlogs;

                    Then don't forget to generate new password file for the new dbname (using orapw) and make changes in tnsnames.ora and listener.ora if needed.
                    Just make sure, the database has been properly shut down before changing the db_name either manualy by recreating controlfile or by the newid utility, as the renamed database has to be opened with resetlogs - making any crash recover of improperly shut down database impossible.

                    Kind regards,
                    Martin
                    • 7. Re: how to change the SID name in the server
                      P.Forstmann
                      Martin Kucera wrote:
                      Just a notice: SID and DBNAME are two different things.
                      Yes instance parameter INSTANCE_NAME has a default value set to DBNAME and you can set INSTANCE_NAME to a different value: this would be easy on Unix but on Windows we should remember that each Oracle instance has a Windows service with SID value hardcoded namely OracleService<SID>. Changing INSTANCE_NAME on Windows must also include removing service and adding a new service. These steps are documented by Tim Hall http://www.oracle-base.com/articles/9i/DBNEWID.php.