How can I change the SID of an Oracle XE instance
In version 11g, all of the previous solution didn't work... I always get the following error when trying to do the sqlplus / as sysdba :
ERROR: ORA-12560: TNS:protocol adapter error
Luckily I found a script to do what I wanted to do under [XE_HOME]\config\scripts. The script is named XE.bat and it will instantiate a new database from scratch asking you for the sysPassword along the process. So what I did was :
- Stop and remove the existing service if any:
oradim -delete -sid XE
- Stop the listener
- Configure the SPFILE as explained by Johannes
- Make a copy of the script XE.bat, you can name it whatever you want
Edit the copy of the script as follows :
- Change line "set ORACLE_SID=XE" to "set ORACLE_SID=NEW_SID_NAME"
- Change wherever you see "-sid XE" to "-sid NEW_SID_NAME"
- Update the line where it calls the "orapwd.exe" command to point to a file called PWDNEW_SID_NAME.ora instead of PWDXE.ora
- Update the line that echos the spfileXE.ora into the initXE.ora to echo spfileNEW_SID_NAME.ora into initNEW_SID_NAME.ora (this part may render the step 3 useless but I prefer to do it anyway, just in case...)
Execute the script... It will prompt you for the SYSTEM password a few times saying
Enter value for 1:
or
Enter value for 2:
That's it, the new database with your NEW_SID_NAME is up and running!!
The asktom article has the answer, but the formatting and verbosity makes it hard to follow, so here's a summary:
[XE_HOME] means where Oracle XE is installed. Normally this is C:\oraclexe\app\oracle\product\10.2.0\server
.
Make sure you have Administrator privileges or the procedure will fail.
- Configure the SPFILE (you can remove the old file if you want)
copy [XE_HOME]\dbs\spfileXE.ora [XE_HOME]\dbs\spfileNEW_SID_NAME.ora
copy [XE_HOME]\database\initXE.ora [XE_HOME]\database\initNEW_SID_NAME.ora
- Edit
[XE_HOME]\database\initNEW_SID_NAME.ora
: It should contain a single line like this:SPFILE='[XE_HOME]\server\dbs/spfileNEW_SID_NAME.ora'
- Shutdown and replace the old service with a new:
sqlplus / as sysdba
and executeshutdown
lsnrctl stop
oradim -new -sid NEW_SID_NAME -startmode auto -pfile [XE_HOME]\database\initNEW_SID_NAME.ora
oradim -delete -sid XE
lsnrctl start
- Update the ORACLE_SID environment property (System Settings > Advanced > Environment)
- Force Oracle to register with listener
sqlplus / as sysdba
and executealter system register;
You can verify that the SID was changed by executing the following query: select instance_name from v$instance;
I had some problems with the solution posted by Johannes, so I had to do some extra steps. When trying to connect to oracle (step 4) by doing sqlplus / as sysdba I got:
ERROR: ORA-12560: TNS:protocol adapter error
The solution for this was executing the following line:
oradim -start -sid NEW_SID_NAME
Then connecting with / worked fine, but trying to connect to NEW_SID_NAME with system or HR got me another problem:
ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
I checked that with the query select instance_name from v$instance;
that the listener would be NEW_SID_NAME, and so did. But running lsnrctl status
in the command line or querying select name from dba_services;
didn't show NEW_SID_NAME as a listener.
The solution of this problem was executing the followind sentence on sqlplus:
alter system set service_names='NEW_SID_NAME';
Maybe you'll need to execute alter system register;
after this also.
After doing this two steps I can connect to the NEW_SID_NAME with system and HR.
Hope it helps