views:

1600

answers:

2

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.

+4  A: 

Asktom has the answer, I had to go through a lot of google-fu to get to it.

Osama ALASSIRY
+1  A: 

The asktom article has the answer, but the formatting and verbosity makes it hard to follow, so here's a summary:

[XE_HOME] means where Oracle XE is installed. Normally this is C:\oraclexe\app\oracle\product\10.2.0\server.

Make sure you have Administrator privileges or the procedure will fail.

  1. Configure the SPFILE (you can remove the old file if you want)
    1. copy [XE_HOME]\dbs\spfileXE.ora [XE_HOME]\dbs\spfileNEW_SID_NAME.ora
    2. copy [XE_HOME]\database\initXE.ora [XE_HOME]\database\initNEW_SID_NAME.ora
    3. Edit [XE_HOME]\database\initNEW_SID_NAME.ora: It should contain a single line like this: SPFILE='[XE_HOME]\server\dbs/spfileNEW_SID_NAME.ora'
  2. Shutdown and replace the old service with a new:
    1. sqlplus / as sysdba and execute shutdown
    2. lsnrctl stop
    3. oradim -new -sid NEW_SID_NAME -startmode auto -pfile [XE_HOME]\database\initNEW_SID_NAME.ora
    4. oradim -delete -sid XE
    5. lsnrctl start
  3. Update the ORACLE_SID environment property (System Settings > Advanced > Environment)
  4. Force Oracle to register with listener
    • sqlplus / as sysdba and execute alter system register;

You can verify that the SID was changed by executing the following query: select instance_name from v$instance;

Johannes Brodwall
Thanks, I didn't like the idea of accepting my own answer. Your answer is much clearer,
Osama ALASSIRY