Changing ORACLE_SID

I recently faced a rather obnoxious CT that demanded that the SID for a certain instance be in uppercase instead of lowercase (as it was created). So I started looking for possibilities (other than recreating the database, that is).

Scenario:
=========
AIX 5.3 ML07
Oracle Database 10g Release 2 Patchset 2 (10.2.0.3)

Database:
=========
There is one database with sid orcl (of course name is uppercase always, so db_name is 'ORCL')

Process Overview:
=================
At this point there are two possible ways to go. First, since the DB_NAME is not going to change, we can simply rename a few files, change the environment variable and tnsnames enty and fire up a new instance.
Another option is DBNEWID utility.

Option 1. Case Change:
======================
First we must shutdown the instance.

1. rename spfile $ORACLE_HOM/dbs/spfileorcl.ora to $ORACLE_HOME/dbs/spfileORCL.ora
2. rename passwordfile $ORACLE_HOME/dbs/orapworcl to $ORACLE_HOME/dbs/orapwORCL
3. create new directory structure $ORACLE_BASE/admin/ORCL/(b|c|u|a)dump and change the proper parameters (USER_DUMP_DEST, BACKGROUND_DUMP_DEST, AUDIT_DUMP_DEST, CORE_DUMP_DEST)

Then change ORACLE_SID environment variable to ORCL (export ORACLE_SID=ORCL) and fire up the instance. Everything should work fine. If you use the instance name as part of the naming standard for db files, you would need to change those as well. I suggest you move everything to the new name and then issue alter database rename file 'orig' to 'new'.

hth
Guillermo Alan Bort
Oracle Certified Associate.