1 2 Previous Next 20 Replies Latest reply: Aug 29, 2012 11:57 PM by Osama_Mustafa RSS

    Can't use xe as sid

    HuaMin Chen
      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
          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
            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
              you run the oracle command as root user :)
              • 4. Re: Can't use xe as sid
                Azar
                [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
                  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.
                    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
                      export your env , export ORACLE_SID , export ORACLE_HOME
                      • 8. Re: Can't use xe as sid
                        HuaMin Chen
                        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
                          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
                            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
                              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
                                Thanks. What is the command to set local_listener parameter on memory scope?
                                • 13. Re: Can't use xe as sid
                                  Osama_Mustafa
                                  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
                                    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