How to change the SID of an Oracle XE instance

When you install oracle database 11g express, by default its SID name is set to XE. If you want to change your existing SID from XE to your SID, follow below steps to get it done.

Take backup of your oracle database before doing all such things. I have tested this on Oracle database 11g express so before implementing on other databases, first practice on dummy database.

Here we will change XE SID to TESTDB SID.

Shutdown your database

Start > Run > cmd

C:\> sqlplus / as sysdba

SQL> show parameter spfile

It will show you details and location of your parameter file.

SQL> shutdown immediate;

SQL> startup mount;

SQL> exit

Now we have to create a SID with the name of TESTDB and press Y when it asks to change database ID and database name from XE to TESTDB?

C:\> nid target=sys/sys@xe dbname= TESTDB

Y

It will show you a message Successfully changed database name and ID”

C:\> set orale_sid=xe

C:\> sqlplus /nolog

SQL> conn sys/sys as sysdba

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter system set db_name = TESTDB scope=spfile;

SQL> shutdown immediate;

SQL> exit

Go to database folder and change PWDXE.ora to PWDXE_old.ora and issue following command.

C:\> orapwd file=c:\oracleexe…\11.2.0\server\database\PWDjodhpur.ora entries=10

sys

C:\> oradim -delete -sid xe

C:\> oradim -new -sid TESTDB -intpwd sys -startmode a

C:\> lsnrctl start

C:\> lsnrctl stop

C:\> lsnrctl start

C:\> sqlplus /nolog

Now copy initxe.ora to inittestdb.ora in database folder then issue following command.

SQL> startup mount;

SQL> alter database open resetlogs;

SQL> show parameter db_name

SQL> select * from v$instance;

Now run tablespace, user creation script and grant privileges.

 

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top
Scroll to Top