This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jul 19, 2006 11:22 AM by 522411 RSS

How do I rename an XE database on a Windows 2003 Server?

522411 Newbie
Currently Being Moderated
Ok. I'm attempting to rename a freshly installed XE database on a Windows 2003 Server. It looks like there is no getting around the fact that the database gets named "XE" during installation. Because there could potentially be multiple installations of XE in my organization, I thought it'd be a good idea to disambiguate the installations by renaming them to match their function. In this particular case, I'd like to rename the database to "CTXMGMT". It will be a data repository for our Citrix farm administration programs.

Here is what I've performed thus far:

1. Installed Oracle 10g XE on one of our Windows 2003 Server, Standard Edition machines.

2. Verified that the installation was performed correctly by making sure the 'Database Home Page' loads correctly through Internet Explorer, and by performing a few Select statements on the database (i.e. select name from v$database;)

* * I then began following instructions from this document (http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/server.920/a96652/ch14.htm#1004735) to rename the database. Note: I'm only trying to change the name of the database, not the ID.

3. Shutdown the database

4. Opened the database in MOUNT mode

5. Ran the NID utility. Here's the actual input/output to/from the NID utility:

-----------------------------------------------------------------------------------------------------------------

C:\oraclexe\app\oracle\product\10.2.0\server\BIN>nid target=sys/manager@xe DBNAME=CTXMGMT SETNAME=YES

DBNEWID: Release 10.2.0.1.0 - Production on Mon Jul 10 15:55:10 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to database XE (DBID=2476560070)

Connected to server version 10.2.0

Control Files in database:
C:\ORACLEXE\ORADATA\XE\CONTROL.DBF

Change database name of database XE to CTXMGMT? (Y/[N]) => Y

Proceeding with operation
Changing database name from XE to CTXMGMT
Control File C:\ORACLEXE\ORADATA\XE\CONTROL.DBF - modified
Datafile C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF - wrote new name
Datafile C:\ORACLEXE\ORADATA\XE\UNDO.DBF - wrote new name
Datafile C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF - wrote new name
Datafile C:\ORACLEXE\ORADATA\XE\USERS.DBF - wrote new name
Datafile C:\ORACLEXE\ORADATA\XE\TEMP.DBF - wrote new name
Control File C:\ORACLEXE\ORADATA\XE\CONTROL.DBF - wrote new name
Instance shut down

Database name changed to CTXMGMT.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

-------------------------------------------------------------------------------------------------------------

6. After running the NID utility from a separate DOS Window, I attempt to shutdown the newly renamed database from the first DOS Window I used in steps 3 & 4 from above (i.e. to shutdown the database and then start it in MOUNT mode) but I receive this error:

ORA-03113: end-of-file on communication channel

This is somewhat expected since the SQL*Plus connection in this window probably had "the bottom pulled out from under it" when the database was renamed.

However at this point I'm unsure about what state the database is in. Is it down? Is it up? Logic would sort of dictate that the database was left in the MOUNT state, which is the state it was in when the database name was changed. However, the only way I'm able to get back in to the database is by doing the following

a. Opened a new DOS window

b. entered "set ORACLE_HOME=C:\oraclexe\app\oracle\product\10.2.0\server"

c. entered "set ORACLE_SID=XE"

d. entered " sqlplus "/ as sysdba" "

e. at SQL> prompt, entered "startup"... which produces:

-----------------------------------------------------------------------------------------------
SQL> startup;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1287016 bytes
Variable Size 92277912 bytes
Database Buffers 188743680 bytes
Redo Buffers 2904064 bytes
ORA-01103: database name 'CTXMGMT' in control file is not 'XE'


SQL>
--------------------------------------------------------------------------------------------------------

From here I don't know what to do. According to the ORA-01103 error message, it looks like I need to change the database name in the control file to "CTXMGMT", but I'm not sure how I should go about that. The control file is a binary file, so I can't just open it up with Notepad and type in a new database name.

I suspect that even once that is done that the listener.ora (and maybe the tnsnames.ora file?) will have to be edited to include the new "CTXMGMT" name, but I'm not sure how exactly those files should look to incorporate the new database name.

Also, would the names of services have to be changed in Windows as well? (i.e. Would the Windows service named "OracleServiceXE" have to be renamed to "OracleServiceCTXMGMT" ?)

In short, I really just need to know how to rename an XE database on Windows appropriately. The steps described above represent as far as I've gotten. If I'm going about it incorrectly could someone shed some light on the correct steps to follow? The more detailed the better. Thanks.


- Gary
  • 1. Re: How do I rename an XE database on a Windows 2003 Server?
    MaximDemenko Pro
    Currently Being Moderated
    You interpret the error message incorrect. It says only, that in controlfile the database name is CTXMGMT, but expected to be XE. To finalize your rename action you have to modify the database name in the (s)pfile - the parameter db_name should match the database name stored in controlfile.
    However, it seems to me too much work regarding what you want to achieve. The simpliest way in my opinion would be to create different service names in the tnsnames.ora (LDAP, whatever) - so you'll definitely avoid possible side-effects which you maybe get renaming the database ( yes, i tested this scenario and database came up after being renamed, but who knows ;-).

    Best regards

    Maxim
  • 2. Re: How do I rename an XE database on a Windows 2003 Server?
    392393 Newbie
    Currently Being Moderated
    I agree. Just make an alias in the tnsnames.ora so that the connection strings look how you want them to look. That way you don't have to change it and nobody who doesn't know Oracle will know the difference.

    ~Jer
  • 3. Re: How do I rename an XE database on a Windows 2003 Server?
    522411 Newbie
    Currently Being Moderated
    Okay, I understand the reccomendation to just change the tnsnames.ora file on the machines that would be connecting to this database -- such that the "CTXMGMT" actualyl points to the SID "XE" -- and I may actually do this to disambiguate the database. But...


    What if I really wanted to rename the database? Can anyone provide step-by-step instructions from where I left off (see above description) to successfully rename the database?
  • 4. Re: How do I rename an XE database on a Windows 2003 Server?
    392393 Newbie
    Currently Being Moderated
    What if I really wanted to rename the database? Can
    anyone provide step-by-step instructions from where I
    left off (see above description) to successfully
    rename the database?
    I think the best you'll get is to look through the old posts in these forums. I think there have been other people who have done it, but you'll have to check.
  • 5. Re: How do I rename an XE database on a Windows 2003 Server?
    MaximDemenko Pro
    Currently Being Moderated
    I thought, it was clear from my previous post - you do it exactly as you have it done. After renaming the database with nid utility you shutdown it, then edit you pfile and search for parameter "db_name" and set its value to "CTXMGMT". If you don't have a pfile (in the default installation on Windows pfile contains only reference to the spfile) , you should prepare it before renaming. After that you should be able to start your renamed database. If you still have problems/errors - post please them exactly here.

    Best regards

    Maxim
  • 6. Re: How do I rename an XE database on a Windows 2003 Server?
    522411 Newbie
    Currently Being Moderated
    Jer,

    I've searched through this forum looking exactly for how to rename an XE database, and my search didn't anything that provided step-by-step instructions.
    If you find such a posting, please let me know.

    - Gary
  • 7. Re: How do I rename an XE database on a Windows 2003 Server?
    522411 Newbie
    Currently Being Moderated
    Maxim,

    So... since the default installation of XE uses a spfile to set it's initialization parameters (not a pfile) I should

    1) install the XE database
    2) start it up, change the db_name parameter in the spfile (I plan on doing this using Toad)
    3) carry out all the steps I previosuly performed (noted in my first post)

    and Presto!, thats it?

    - Gary
  • 8. Re: How do I rename an XE database on a Windows 2003 Server?
    MaximDemenko Pro
    Currently Being Moderated
    I would change db_name after renaming with the nid.
    You can create pfile from sqlplus command line ( see thread Re: ORA-00064 after changes processes, open_cursors
    sqlplus>create pfile from spfile
    You can previously backup your original initXE.ora.
    Within pfile you can easily edit and change concerned parameter.
    After renaming with nid, i would move the original spfile to spfile.bkp, so instance then gets its parameter from your new initXE.ora.
    If something goes wrong, you can revert to original config files and invoke nid to rename to XE back.

    Best regards

    Maxim
  • 9. Re: How do I rename an XE database on a Windows 2003 Server?
    522411 Newbie
    Currently Being Moderated
    Okay, I've....


    1) Reinstalled XE, to start from scratch
    2) Went in to SQL*Plus, did a "create pfile from spfile;"
    3) Shutdown the database
    4) Started it up in MOUNT mode
    5) Ran NID, to change the database name from 'XE' to 'CTXMGMT'
    6) Shutdown the database again
    7) Edited the pfile to change the database name (i.e. "db_name='CTXMGMT'")
    8) Started up the database

    And that worked! So that's good... but now my question is:

    How do I change the SID for the database? Currently, the instance name for the database is still 'XE'....


    ----------------------------------------------------------------------------------------------------------------
    SQL> select instance_name
    2 from v$instance;

    INSTANCE_NAME
    ----------------
    xe
    -----------------------------------------------------------------------------------------------------------------

    I'd like it so a connection could be made to the database with the tnsnames.ora file looking like this:

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    ...

    CTXMGMT =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(Host = rodin)(Port = 1521))
    )
    (CONNECT_DATA = (SID = CTXMGMT))
    )

    ...

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    Could someone explain how to get the database to this state? Thanks.

    - Gary
  • 10. Re: How do I rename an XE database on a Windows 2003 Server?
    MaximDemenko Pro
    Currently Being Moderated
    Instance name as opposite to database name is not recorded in database . So , theoretically, every database can be opened by any instance ( not at the same time of course ). To do that on unix is enough to create a configuration file ( aka (s)pfile ) with desired instance_name, name this file accordingly ( init$SID.ora), set enironment ( export ORACLE_SID=$MY_NEW_SID ) and you are done - instance can be started. On windows it is basically the same, however slightly differs - you have to check Oracle manuals about oradim utility, so , likelywise you'll need to create new windows service. That applies in general to Oracle RDBMS software such as EE/SE/SE1, however, regarding XE i'm not sure, maybe some hardcoded references to the this certain instance name exists somewhere and after start with instance name other than XE it may not work properly. On the other side - you are spending a lot of time to achieve a goal which i can not understand. Instance name ( as well as database name) are meaningless , in addition you have to troubleshoot your listener configuration, because it may depend on SID. To distinguish the instances/databases respecting their functionality you have a lot of places which are much more appropriated ( as posted before).

    Best regards

    Maxim
  • 11. Re: How do I rename an XE database on a Windows 2003 Server?
    dave sharples Newbie
    Currently Being Moderated
    well there is the instance_name parameter and you will need to-recreate the service
  • 12. Re: How do I rename an XE database on a Windows 2003 Server?
    522411 Newbie
    Currently Being Moderated
    David,

    Ok, I've just attempted to modify the instance name by adding an entry to the pfile....

    ...
    *.instance_name='CTXMGMT'
    ...

    ... and then shutting down and restarting the instance. But the name of the instance remained the same...

    ----------------------------------------------------------------------------------------------------------------

    SQL> select instance_name
    2 from v$instance;

    INSTANCE_NAME
    ----------------
    xe

    -----------------------------------------------------------------------------------------------------------------


    Attempting to change the parameter while logged into the database through SQL*Plus yields....


    ------------------------------------------------------------------------------------------------------------------

    SQL> alter system set instance_name='CTXMGMT';
    alter system set instance_name='CTXMGMT'
    *
    ERROR at line 1:
    ORA-02095: specified initialization parameter cannot be modified

    ------------------------------------------------------------------------------------------------------------------

    Any ideas?

    - Gary
  • 13. Re: How do I rename an XE database on a Windows 2003 Server?
    522411 Newbie
    Currently Being Moderated
    My last question is really open for anyone to answer. Does anyone know what the deal here is? There's got to be a way to change the database name and the instance name in an XE database for Windows. Any Oracle officials have the word on this? Tom Kyte? You out there?

    - Gary
  • 14. Re: How do I rename an XE database on a Windows 2003 Server?
    dave sharples Newbie
    Currently Being Moderated
    well you need to re-create the service as I said - have you done that/
1 2 Previous Next