The Server is Oracle Database 11g Enterprise Edition Release - 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 ].

  • 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 '11 at 13:32
  • No I work as DBA for the develop and some test Oracle databases. – bernd_k Jan 6 '11 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 – a_horse_with_no_name Nov 14 '11 at 22:46

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

  • 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 | |
  • 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 '11 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. – ik_zelf Apr 2 '12 at 18:12
  • I'd like to point out, the DBNEWID utility (the nid command), is a command line tool. – Nathan Goings May 14 at 18:11

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 | |
  • 5
    Of note for windows users, you will also need to use the "oradim" utility to setup the new service. – REW Jan 6 '11 at 13:35

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 | |
  • +1 But I'm still unsure whether I want to change the DB_NAME too. – bernd_k Jun 29 '11 at 4:50
  • If you want: sure, you can, the other answers explained correctly how to do that. For what reason would you do it? The db_name is very physical. Knowing the db_name is good for a dba but for most others it is irrelevant. In a normal consolidated approach you have a database serving many services. A service - application - should have no need to care about in what database it is running. – ik_zelf Jun 29 '11 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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