Can I change the SID of an Oracle database?

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.

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
  

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.