Change SID on Oracle

david -- Thanks for the question regarding "Change SID on Oracle Express (XE) 10.2.0", version XE 10.2.0

Submitted on 23-Jun-2008 9:17 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 24-Jun-2008 9:36

You Asked

I'm trying to change the SID for an Oracle Express XE 10.2.0 install on Windows.

I keep finding "Check Ask Tom" references, but all I've found on your site is a unix guide and an nt guide for version 8i. There have been a lot of changes since 8i and I'm having trouble using the guide, not all the programs mentioned in it seem to exist anymore.

What do I have to do?

Thanks

and we said...

Ok, here is a way, basically the same as it always was, just oradim is named oradim now, not oradim80. We do not need to rename the database, just want to change the sid from XE to SOMETHIN (8 characters max)

1) shutdown database cleanly

sqlplus / as sysdba
shutdown

2) stop the oracle services using control panel. (oracleserviceXE and the listener - or use lsnrctl stop from command line)

3) rename or copy C:\oraclexe\app\oracle\product\10.2.0\server\dbs\spfileXE.ora to C:\oraclexe\app\oracle\product\10.2.0\server\dbs\spfileSOMETHIN.ora

4) create C:\oraclexe\app\oracle\product\10.2.0\server\database\initSOMETHIN.ora by copying C:\oraclexe\app\oracle\product\10.2.0\server\database\initXE.ora and modify the spfile line to point to the new spfile

5) create the new service to start database and remove the old one

oradim -new -sid SOMETHIN -startmode auto -pfile C:\oraclexe\app\oracle\product\10.2.0\server\database\initSOMETHIN.ora

oradim -delete -sid XE

6) start listener

lsnrctl start

7) fix your environment

set ORACLE_SID=SOMETHIN

8) the database will eventually register with listener, but you can make it happen now:

sqlplus / as sysdba
alter system register;

if you query:

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
somethin

You'll see the new sid....

Reviews