oracle 11g xe sid변경

[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.

1. Configure the SPFILE (you can remove the old file if you want)
   1. copy [XE_HOME]\dbs\spfileXE.ora [XE_HOME]\dbs\spfileNEW_SID_NAME.ora
   2. copy [XE_HOME]\database\initXE.ora [XE_HOME]\database\initNEW_SID_NAME.ora
   3. 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'
2. Shutdown and replace the old service with a new:
   1. sqlplus / as sysdba and execute shutdown
   2. lsnrctl stop
   3. oradim -new -sid NEW_SID_NAME -startmode auto -pfile [XE_HOME]\database\initNEW_SID_NAME.ora
   4. oradim -delete -sid XE
   5. lsnrctl start
3. Update the ORACLE_SID environment property (System Settings > Advanced > Environment)
4. Force Oracle to register with listener
   • sqlplus / as sysdba and execute alter system register;

You can verify that the SID was changed by executing the following query: select instance_name from v$instance;

 

위에서 3번째 단계를 처리하지 않으면 아래 에러가 발생

ora-12560 tns protocol adapter error 11g

해결책

SET oracle_sid=NEW_SID_NAME

 

 

출처: https://stackoverflow.com/questions/410951/how-can-i-change-the-sid-of-an-oracle-xe-instance