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.

 

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)