20

The Server is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit

Is there an easy & quick way to change the SIDs of the test databases on the server?

Dropping & recreating of the database is an option for me. But I'm looking for something requiring less time.

The other option to assign names in the clients tnsnames.ora is prone to errors, because they are not administrated centrally.

Compared with the time to drop & create a database on SQL-Server, the amount of time required to create a new Oracle database is excessively greater. Further on SQL-Server you can rename SQL-Server instances. [ Usually you rename the server where SQL-Server is running and have some problems until you rename the server too ].

Improve this question
3
  • You mention that you can drop and recreate the databases. Do you have access to the production database and it's backups? You could use RMAN and the duplicate command to recreate the database and change SID at the same time.
    – Sumnibot
    Jan 6, 2011 at 13:32
  • No I work as DBA for the develop and some test Oracle databases.
    – bernd_k
    Jan 6, 2011 at 13:36
  • 2
    When you would use a database in SQL Server you usually use a Schema (=user) in Oracle. The same name does not mean the same thing in those two worlds
    – user1822
    Nov 14, 2011 at 22:46

4 Answers 4

Reset to default
14

Since 9i dbnewid utility (nid) can be used to change database name (and DBID if required). If database name being changed only then resetlogs is not required:

  • 1 startup database in mount mode

    shutdown immediate
startup mount

  • 2 run nid to change database name:

    nid target=sys/syspassword@dbtns dbname=newname setname=YES

  • 3 shutdown and start database in mount mode:

    shutdown immediate
startup mount

  • 4 change db_name in spfile (or in pfile editing the file):

    alter system set db_name=newname scope=spfile;

  • 5 recreate password file:

    orapwd file=orapwnewname password=syspassword

  • 6 startup the database

    startup

  • 7 post rename steps:

    change SID in listener.ora
correct tnsnames.ora
remove old trace directories
change /etc/oratab (UNIX) or rename windows service using oradim
Improve this answer
3
  • 1
    I renamed 4 oracle databases on a Windows Server this way. Searching for dbnewid utility I found oracle-base.com/articles/9i/DBNEWID.php which gave some additional explanations.
    – bernd_k
    Jan 13, 2011 at 14:07
  • 1
    this is a good answer when the question was: how to rename a database. Question is: how to change a SID.
    – user953
    Apr 2, 2012 at 18:12
  • I'd like to point out, the DBNEWID utility (the nid command), is a command line tool.
    – Nathan Goings
    May 14, 2020 at 18:11
17

You need to recreate the control file

This post by Kaunain Ahmed describes the necessary steps:

  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.ora and change the init.ora
  5. 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 10.Change the TNS-Configuration accordingly $ORACLE_HOME/network/admin/*.ora Look for SID and GLOBAL_NAME

There are other tools referenced in the thread.

Here's a post by AskTom which references the process in more detail. While it's for 10g, it should still work.

Improve this answer
1
  • 5
    Of note for windows users, you will also need to use the "oradim" utility to setup the new service.
    – REW
    Jan 6, 2011 at 13:35
10

Yes, you can and it is quite easy too.

In Oracle, the ORACLE_SID is just the name for the Oracle Instance and has not very much to do with the DBNAME. A database with the name PROD, can be served using Instances with any valid name. There is no direct connection between the SID and the DBNAME. This connection is made using the parameters.

The parameter file is identified as init${ORACLE_SID}.ora or spfile${ORACLE_SID}.ora In the parameter file is the parameter db_name. This is where the connection between the Oracle Instance and the database is made.

So, you don't need to re-create a controlfile, you don't need to use nid, just make sure that your parameterfile has the right name, bring down the old Oracle Instance and start the new Oracle Instance after having set ORACLE_SID to the new Oracle Instance name. The parameterfile and the password file are both found using the ${ORACLE_SID} as part of their name.

Re-creating the controlfile is only needed when the DBNAME has to change. nid is needed after a clone operation where you need to change the DBID to prevent accidents that could hurt the backups of the source database.

Improve this answer
0
0

Steps to change sid of oracle on linux:

Login in sqlplus as sysdba and perform the following

  1. create pfile from spfile.
  2. Take backup of the pfile and rename it to init.ora
  3. shut down immediate -- shut the database.
  4. lsnrctl stop (listener name).
  5. export ORACLE_SID=(newsid).
  6. sqlplus / as sysdba
  7. create spfile from pfile
  8. startup
  9. lsnrctl start (listener name).

Database is started with new instance name

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.