This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Aug 29, 2012 9:57 PM by Osama_Mustafa RSS

Can't use xe as sid

HuaMin Chen Pro
Currently Being Moderated
Hi,
I set up 11g XE in OL5.8. I don't know why I get these and can't connect to it using XE as sid.

SQL> conn system/???@xe as sysdba
ERROR:
ORA-12541: TNS:no listener


SQL> conn system/??? as sysdba
Connected.

while I've put this in tnsnames using the relevant IP of the machine as the host.

XE =
(DESCRIPTION =
# (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ?.?.255.127)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Many Thanks & Best Regards,
HuaMin
  • 1. Re: Can't use xe as sid
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    did you check local_listener parameter ?
    Also Could you please post lsnrctl Status <Listener-name>

    Edited by: Osama_mustafa on Aug 28, 2012 8:02 AM
  • 2. Re: Can't use xe as sid
    HuaMin Chen Pro
    Currently Being Moderated
    Thanks. can you please advise for these?

    [root@localhost bin]# pwd
    /u01/app/oracle/product/11.2.0/xe/bin
    [root@localhost bin]# ls -t lsnrct*
    lsnrctl
    [root@localhost bin]# lsnrctl
    bash: lsnrctl: command not found
    [root@localhost bin]#

    Many Thanks & Best Regards,
    HuaMin
  • 3. Re: Can't use xe as sid
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    you run the oracle command as root user :)
  • 4. Re: Can't use xe as sid
    Azar Pro
    Currently Being Moderated
    [root@cignadev ~]# su - oracle
    [oracle@cignadev ~]$ lsrnctl status


    But If you're not set oracle home and path, you cannot execute directly this command. So first you need to set oracle home path.
  • 5. Re: Can't use xe as sid
    HuaMin Chen Pro
    Currently Being Moderated
    It is the same by other login
    [HuaMin@localhost ~]$ cd /u01/app/oracle/product/11.2.0/xe/bin
    [HuaMin@localhost bin]$ lsnrctl
    bash: lsnrctl: command not found
    [HuaMin@localhost bin]$
  • 6. Re: Can't use xe as sid
    Paul M. Oracle ACE
    Currently Being Moderated
    Try
    $ source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
    $ lsnrctl stat
  • 7. Re: Can't use xe as sid
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    export your env , export ORACLE_SID , export ORACLE_HOME
  • 8. Re: Can't use xe as sid
    HuaMin Chen Pro
    Currently Being Moderated
    Paul M. wrote:
    Try
    $ source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
    $ lsnrctl stat
    Thanks.
    Paul,
    Here is what I've got
    [HuaMin@localhost ~]$ source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
    [HuaMin@localhost ~]$ lsnrctl stat

    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-AUG-2012 17:12:25

    Copyright (c) 1991, 2011, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    Start Date 28-AUG-2012 16:55:14
    Uptime 0 days 0 hr. 17 min. 10 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Default Service XE
    Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
    Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=?.?.255.127)(PORT=1521)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    [HuaMin@localhost ~]$

    Many Thanks & Best Regards,
    HuaMin
  • 9. Re: Can't use xe as sid
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    you database instance in not register with listener , try to use
    alter system register and check again if you can connect
  • 10. Re: Can't use xe as sid
    HuaMin Chen Pro
    Currently Being Moderated
    Thanks. I've done this
    SQL> alter system register;

    System altered.

    Here is what I get
    [HuaMin@localhost ~]$ source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
    [HuaMin@localhost ~]$ lsnrctl stat

    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 29-AUG-2012 10:11:03

    Copyright (c) 1991, 2011, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    Start Date 29-AUG-2012 10:03:36
    Uptime 0 days 0 hr. 7 min. 26 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Default Service XE
    Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
    Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=?.?.254.179)(PORT=1521)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

    why do I still get this?
    SQL> conn system as sysdba
    Enter password:
    Connected.
    SQL> conn system@xe as sysdba
    Enter password:
    ERROR:
    ORA-12170: TNS:Connect timeout occurred


    Warning: You are no longer connected to ORACLE.

    Many Thanks & Best Regards,
    HuaMin
  • 11. Re: Can't use xe as sid
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    set your local_listener parameter first on memory scope to test it then alter system register and try again .
  • 12. Re: Can't use xe as sid
    HuaMin Chen Pro
    Currently Being Moderated
    Thanks. What is the command to set local_listener parameter on memory scope?
  • 13. Re: Can't use xe as sid
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    HuaMin Chen wrote:
    Thanks. What is the command to set local_listener parameter on memory scope?
    alter system set local_listener='<you get this entry from tns>' scope=memory

    example :
    SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521))';
  • 14. Re: Can't use xe as sid
    HuaMin Chen Pro
    Currently Being Moderated
    I've done that but I still get these

    SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=?.?.254.179)(PORT=1521))' scope=memory;

    System altered.

    SQL> alter system register;

    System altered.

    SQL> conn system@xe as sysdba
    Enter password:
    ERROR:
    ORA-12170: TNS:Connect timeout occurred


    Warning: You are no longer connected to ORACLE.

    Many Thanks & Best Regards,
    HuaMin
1 2 Previous Next

Legend

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