This discussion is archived
8 Replies Latest reply: Oct 3, 2013 8:17 PM by user10987724 RSS

Can you change XE SID and if so, does any one have specific step for Linux

759625 Newbie
Currently Being Moderated
We really need the SID to be a specific value if at all possible. Is it possible to change it on XE and if so does anyone have the specific steps needed for Linux?

Also, Is there a work around that would have the same effect as changing the SID? Like it would mask it or allow another value perhaps?
  • 1. Re: Can you change XE SID and if so, does any one have specific step for Linux
    jariola Guru
    Currently Being Moderated
    Hi

    This might help
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:963124000346653522

    Br,Jari
  • 2. Re: Can you change XE SID and if so, does any one have specific step for Linux
    759625 Newbie
    Currently Being Moderated
    I started with that particular link, but some of the directories weren't matching up. For example the C:\oraclexe\app\oracle\product\10.2.0\server\database\ directory doesn't exist. I have an init.ora but it just called init.ora not initXE.ora and it is 1 directory down from where he says to go.

    Also there are at least 3 other files with XE in their name within the directory that the init.ora currently exists. Why doesn't he mention these?

    I'm kind of confused.
  • 3. Re: Can you change XE SID and if so, does any one have specific step for Linux
    clcarter Expert
    Currently Being Moderated
    I'm kind of confused
    Yeah that can happen after reading a windows how-to ;) there's no ORADIM command in *nix.

    If you want to change the database service name as well as the ORACLE_SID connect as sysdba and:

    alter system set service_names = 'SOMETHIN' scope = spfile;
    shutdown immediate;

    Update the environment shell scripts for the new ORACLE_SID, its in the ORACLE_HOME/bin directory (under /usr/lib/oracle/xe/app/oracle/product/10.2.0/server) in oracle_env.csh and oracle_env.sh, and source the one appropriate for your environment

    $ cd $ORACLE_HOME/bin
    $ vi oracle_env.sh
    ... # change
    ORACLE_SID=XE
    # to
    ORACLE_SID=SOMETHIN
    ...
    $ . ./oracle_env.sh

    Note there is a space between the first dot and the dotshlashscriptname. And the database startup rc scripts will also need adjustment if its set to start/stop the database at boot/shutdown.

    Update $ORACLE_HOME/network/admin/listener.ora for the updated service name change:
    DEFAULT_SERVICE_LISTENER = (XE)
    -- to
    DEFAULT_SERVICE_LISTENER = (SOMETHIN)

    Copy $ORACLE_HOME/dbs/spfileXE.ora to $ORACLE_HOME/dbs/spfileSOMETHIN.ora

    $ cd $ORACLE_HOME/dbs
    $ cp spfileXE.ora spfileSOMETHIN.ora

    Check to make sure the settings are in place ...

    $ set |grep ORA
    ...
    ORACLE_HOME=/usr/lib/oracle/xe...
    ORACLE_SID=SOMETHIN

    Stop and start the listener to enforce the listener.ora update

    $ lsnrctl stop
    $ lsnrctl start

    And should be all good to go, if you have set the oracle user password (or better yet, if you can sudo):

    $ su oracle # switch user to the oracle user
    $ sqlplus /nolog
    # or just sudo /bin/su oracle -c "sqlplus /nolog"
    SQL> connect /as sysdba;
    SQL> startup;

    Exit, and just a a double check run a `ps ...` and you should see your pmon, smon, et. al. database background processes running with the updated ORACLE_SID, and check the listener status for the service name change as well:

    $ ps -ef |grep ora_
    ... ora_pmon_SOMETHIN
    ... ora_smon_SOMETHIN
    ... ora_ckpt_SOMETHIN
    ... ora_dbw0_SOMETHIN
    ...
    $ lsnrctl status
    ...
    Service "somethin" has 1 instance(s).
    Instance "somethin", status READY, has 1 handler(s)...
    ...

    The $ORACLE_SID setting is critical to sqlplus (and other utilities) for local connections on the host where the database is installed and running. Otherwise, i.e. with sqlplus you're connecting via a tns alias or EZconnect string, and generally you'll have to configure that on the remote host client $ORACLE_HOME/network/admin/tnsnames.ora file.

    Or maybe you have an OID (or 9i or earlier ONAMEs) tns service name "resolver" with tns names. That could use the ORACLE_SID, could have SERVICE_NAME specified, but that setting is in the connect string, not the local environment variable(s) at the remote host.
  • 4. Re: Can you change XE SID and if so, does any one have specific step for Linux
    759625 Newbie
    Currently Being Moderated
    COOL! These instructions look a LOT better. You are awesome!


    What about the other files I am seeing in /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs (see files in bold below)?


    An ls of this directory gives the following:

    hc_XE.dat
    init.ora
    lkXE
    orapwXE
    spfileXE.ora

    Would any of these need to be altered to the new SID naming convention? And are there any others I need to be concerned with?
  • 5. Re: Can you change XE SID and if so, does any one have specific step for Linux
    clcarter Expert
    Currently Being Moderated
    hc_XE.dat
    > lkXE

    Those are instance state and lock files, it will create a new hc_${ORACLE_SID}.dat when the database is started with a new $ORACLE_SID. I think either the server process(es) or maybe smon and/or pmon (maybe all?) use them. The oracle agent also reads the hc_ file for state info.

    > orapwXE

    That is the remote password file, if you need remote sysdba connections this file can be created with the orapwd utility, best to run the command below as the oracle user. I think install creates the orapwXE with the sys password specified at install time but I'm not positive on that.

    $ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID entries=N password=<passwd for remote sysdba>

    I.e. use 10 for N, that is the number of entries the utility sets up for password entries. Use a strong password value for <passwd>. Or the existing file can be copied to orapw$ORACLE_SID as long as you know the value for <passwd>.

    The instance parameter remote_login_passwordfile controls whether or not the password file is used and can have the values NONE, EXCLUSIVE, or SHARED. Although SHARED won't be relevant to XE since it only allows one Oracle instance to run from the $ORACLE_HOME. When sysdba role is granted to a database user they'll have an entry in the file and show up in the V$PWFILE_USERS system catalog view.

    Edited by: clcarter on Mar 15, 2010 1:22 PM fix remote passwd text
  • 6. Re: Can you change XE SID and if so, does any one have specific step for Linux
    759625 Newbie
    Currently Being Moderated
    ok, will give it a whirl and see how things go. Cross Fingers! :D
  • 7. Re: Can you change XE SID and if so, does any one have specific step for Linux
    759625 Newbie
    Currently Being Moderated
    Yeahhh it worked without a hitch!

    You are totally cool! Thanks so much!
  • 8. Re: Can you change XE SID and if so, does any one have specific step for Linux
    user10987724 Newbie
    Currently Being Moderated

    I follow your steps and all things go well. But when I type

    startup;

    then system show error:

    ORA-01078: failure in processing system parameters

    LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/xe/dbs/initSOMETHIN.ora'

    Please help !

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points