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.
