How can I change the SID of an Oracle XE instance

Question

I needed to change the SID of an Oracle XE database (not the Service Name) to match a production database.

When I tried searching online, most of the pages were describing changing or adding a service name through tnsnames.ora; that's not what I needed to do.

Answer 1

In version 11g, all of the previous solution didn't work... I always get the following error when trying to do the sqlplus / as sysdba :

ERROR: ORA-12560: TNS:protocol adapter error

Luckily I found a script to do what I wanted to do under [XE_HOME]\config\scripts. The script is named XE.bat and it will instantiate a new database from scratch asking you for the sysPassword along the process. So what I did was :

1. Stop and remove the existing service if any:

oradim -delete -sid XE

2. Stop the listener
3. Configure the SPFILE as explained by Johannes
4. Make a copy of the script XE.bat, you can name it whatever you want

5. Edit the copy of the script as follows :

   1. Change line "set ORACLE_SID=XE" to "set ORACLE_SID=NEW_SID_NAME"
   2. Change wherever you see "-sid XE" to "-sid NEW_SID_NAME"
   3. Update the line where it calls the "orapwd.exe" command to point to a file called PWDNEW_SID_NAME.ora instead of PWDXE.ora
   4. Update the line that echos the spfileXE.ora into the initXE.ora to echo spfileNEW_SID_NAME.ora into initNEW_SID_NAME.ora (this part may render the step 3 useless but I prefer to do it anyway, just in case...)

6. Execute the script... It will prompt you for the SYSTEM password a few times saying

Enter value for 1:

or

Enter value for 2:

That's it, the new database with your NEW_SID_NAME is up and running!!