Change Your ORACLE_SID

* Warning: Technical Entry


Salam and good day,

I'm sure if you're working on oracle DB, some of you use ORACLE XE as local database to test for your convenience. It's different from what is installed on your server. What we do, we change the SID to meet our need. But how? This tutorial will show you an example to change our SID from XE to ORCL.

Prerequisites:
Windows Vista (This is my setting you might use other OS as well).
Oracle Database 10g Express Edition (Of course its free to develop, deploy and distribute. download here)
Oracle DB was installed on the target machine.

Reminder:
Maximum 8 char for SID

Step-by-step guide
1. Shutdown the database.

sqlplus /as sysdba

shutdown

2. Use control panel to shutdown OracleService (In this case service name are OracleServiceXE and listener OracleXETNSListener) or issue this command in your command prompt:

lsnrctl stop

net stop OracleXETNEListener

net stop OracleServiceXE

3. Rename or copy SPFileXE.ora to SPFileORCL.ora. The file is at

C:\oraclexe\app\oracle\product\10.2.0\server\dbs 

 Depending on your installation setting.

4. Copy and rename C:\oraclexe\app\oracle\product\10.2.0\server\database\initXE.ora to initORCL.ora then modify the spfile line to point to the new spfile.

5. Assign this command in your command prompt to create new service and remove the old one.

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

oradim -delete -sid XE

6. Type this in your command line to start up your listener.

lsnrctl start

 7. We're almost done, Set your oracle SID to ORCL now. Type:

set ORACLE_SID=ORCL

8. Run this query after altering process is finish.

C:\Users\xx>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 22 19:30:14 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> alter system register;

System altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

orcl

SQL>

9. orcl is our new SID.