如何更改 Oracle XE 实例的 SID

How can I change the SID of an Oracle XE instance

提问by Osama Al-Maadeed

I needed to change the SID of an Oracle XE database (not the Service Name) to match a production database.

我需要更改 Oracle XE 数据库的 SID（不是服务名称）以匹配生产数据库。

When I tried searching online, most of the pages were describing changing or adding a service name through tnsnames.ora; that's not what I needed to do.

当我尝试在线搜索时，大多数页面都在描述通过tnsnames.ora更改或添加服务名称；那不是我需要做的。

回答by Johannes Brodwall

The asktomarticle has the answer, but the formatting and verbosity makes it hard to follow, so here's a summary:

该asktom文章有答案，但格式和详细程度使其难以遵循，所以这里有一个总结：

[XE_HOME] means where Oracle XE is installed. Normally this is C:\oraclexe\app\oracle\product\10.2.0\server.

[XE_HOME] 表示 Oracle XE 的安装位置。通常这是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 sysdbaand 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 sysdbaand execute alter system register;

1. 配置 SPFILE（如果需要，您可以删除旧文件）
   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. 编辑[XE_HOME]\database\initNEW_SID_NAME.ora：它应该包含这样的一行：SPFILE='[XE_HOME]\server\dbs/spfileNEW_SID_NAME.ora'
2. 关闭并用新服务替换旧服务：
   1. sqlplus / as sysdba并执行 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. 更新 ORACLE_SID 环境属性（系统设置 > 高级 > 环境）
4. 强制 Oracle 向侦听器注册
   • sqlplus / as sysdba并执行 alter system register;

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

您可以通过执行以下查询来验证 SID 是否已更改： select instance_name from v$instance;

回答by Osama Al-Maadeed

Asktom has the answer, I had to go through a lot of google-fu to get to it.

Asktom 有答案，我必须通过大量的 google-fu 才能找到答案。

回答by nMoncho

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 sysdbaI got:

我对 Johannes 发布的解决方案有一些问题，所以我不得不做一些额外的步骤。当尝试通过执行sqlplus / as sysdba连接到 oracle（第 4 步）时，我得到：

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:

然后与 / 连接工作正常，但尝试使用系统或 HR 连接到 NEW_SID_NAME 给我带来了另一个问题：

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 statusin 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:

我通过查询select instance_name from v$instance;来检查侦听器将是 NEW_SID_NAME，结果也是如此。但是lsnrctl status在命令行中运行或查询select name from dba_services;并没有将 NEW_SID_NAME 显示为侦听器。这个问题的解决方法是在sqlplus上执行如下语句：

alter system set service_names='NEW_SID_NAME';

Maybe you'll need to execute alter system register;after this also.

也许您还需要alter system register;在此之后执行。

After doing this two steps I can connect to the NEW_SID_NAME with system and HR.

完成这两个步骤后，我可以使用系统和 HR 连接到 NEW_SID_NAME。

Hope it helps

希望能帮助到你

回答by Sébastien Guenette

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:

在版本 11g 中，所有以前的解决方案都不起作用......在尝试执行sqlplus / as sysdba时，我总是收到以下错误 ：

ERROR: ORA-12560: TNS:protocol adapter error

错误：ORA-12560：TNS：协议适配器错误

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 :

幸运的是，我在 [XE_HOME]\config\scripts 下找到了一个脚本来完成我想做的事情。该脚本名为 XE.bat，它将从头开始实例化一个新数据库，并在此过程中要求您提供 sysPassword。所以我所做的是：

1. Stop and remove the existing service if any:

1. 停止并删除现有服务（如果有）：

oradim -delete -sid XE

oradim -delete -sid XE

1. Stop the listener
2. Configure the SPFILE as explained by Johannes
3. Make a copy of the script XE.bat, you can name it whatever you want

4. Edit the copy of the script as follows :

   1. Change line "set ORACLE_SID=XE" to "set ORACLE_SID=NEW_SID_NAME"
   2. Change wherever you see "-sid XE" to "-sid NEW_SID_NAME"
   3. Update the line where it calls the "orapwd.exe" command to point to a file called PWDNEW_SID_NAME.ora instead of PWDXE.ora
   4. 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...)

5. Execute the script... It will prompt you for the SYSTEM password a few times saying

2. 停止监听器
3. 按照 Johannes 的说明配置 SPFILE
4. 复制脚本XE.bat，你可以随意命名

5. 编辑脚本的副本如下：

   1. 将“set ORACLE_SID=XE”行更改为“set ORACLE_SID=NEW_SID_NAME”
   2. 将您看到“-sid XE”的任何地方更改为“-sid NEW_SID_NAME”
   3. 更新它调用“orapwd.exe”命令的行以指向名为 PWDNEW_SID_NAME.ora 而不是 PWDXE.ora 的文件
   4. 更新将 spfileXE.ora 回显到 initXE.ora 中的行，以将 spfileNEW_SID_NAME.ora 回显到 initNEW_SID_NAME.ora（这部分可能会使第 3 步变得无用，但我还是喜欢这样做，以防万一……）

6. 执行脚本...它会提示你输入系统密码几次说

Enter value for 1:

输入 1 的值：

or

或者

Enter value for 2:

输入 2 的值：

That's it, the new database with your NEW_SID_NAME is up and running!!

就是这样，带有您的 NEW_SID_NAME 的新数据库已启动并运行！！