9 Replies Latest reply on May 30, 2020 11:13 AM by Nazbit

    XE Previously working - TNS service not known after machine reboot.

    Nazbit

      Hi,

       

      After installing XE 18c on to RHEL 8 and configuring the database I can connect to the instance from both SQL Plus on the local machine and SQL Developer from a remote windows machine. So that's all good.

       

      However after the first reboot I can no longer connect. I suspect that this may be something to do with being on a virtual cloud machine? and IP addresses and host names not persisting post reboot?

       

      I nuked the box and started over just to prove the theory and again everything works great until first reboot.

       

      I would imagine I need to change something in my config to allow for the cloud network that but I'm not sure how to fix it. Any help would be greatly appreciated. I've tried to be as specific as possible and provide all the relevant info (that I know of). Please do let me know if I can provide any further information to aid with diagnosis.

       

      Thanks

       

      Environment / Components:

      • AWS EC2 t3a.small instance
      • Variable ECUs, 2 vCPUs, 2.2 GHz, AMD EPYC 7571, 2 GiB memory, EBS only
      • Red Hat Enterprise Linux release 8.2 (Ootpa)
      • oracle-database-xe-18c-1.0-1.x86_64

       

      ORACLE_HOME and ORACLE_SID are set:

       

      # echo $ORACLE_HOME
      /opt/oracle/product/18c/dbhomeXE
      # echo $ORACLE_SID
      xe
      

       

       

      TNSPING

       

      # tnsping XE
      
      
      TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 27-MAY-2020 13:27:18
      
      
      Copyright (c) 1997, 2018, Oracle.  All rights reserved.
      
      
      Used parameter files:
      /opt/oracle/product/18c/dbhomeXE/network/admin/sqlnet.ora
      
      
      
      
      Usd TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
      OK (10 msec)
      

       

       

      listener.ora

      # listener.ora Network Configuration File: /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora

      # Generated by Oracle configuration tools.

       

       

      DEFAULT_SERVICE_LISTENER = XE

       

       

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

        )

       

      sqlnet.ora

      # sqlnet.ora Network Configuration File: /opt/oracle/product/18c/dbhomeXE/network/admin/sqlnet.ora

      # Generated by Oracle configuration tools.

       

       

      NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

       

      tnsnames.ora

      tnsnames.ora Network Configuration File: /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora

      # Generated by Oracle configuration tools.

       

       

       

       

      XE =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = XE)

          )

        )

       

       

      LISTENER_XE =

        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

       

      SQL Plus - Can't connect as sys to start the database.

      # sqlplus /nolog

       

       

      SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 27 13:23:42 2020

      Version 18.4.0.0.0

       

       

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

       

       

      SQL>

      SQL>

      SQL> conn sys@xe / as sysdba

      Enter password:

      ERROR:

      ORA-12514: TNS:listener does not currently know of service requested in connect

      descriptor

       

       

      SQL> conn / as sysdba

      ERROR:

      ORA-01017: invalid username/password; logon denied

       

      Editing to add listener output

      [root@ip-172-31-11-39 admin]# lsnrctl start

       

       

      LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 27-MAY-2020 13:34:58

       

       

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

       

       

      Starting /opt/oracle/product/18c/dbhomeXE/bin/tnslsnr: please wait...

       

       

      TNSLSNR for Linux: Version 18.0.0.0.0 - Production

      Log messages written to /opt/oracle/product/18c/dbhomeXE/network/log/listener.log

      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-xxxxx-xx-xx.eu-west-1.compute.internal)(PORT=1521)))

       

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

      STATUS of the LISTENER

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

      Alias                     LISTENER

      Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production

      Start Date                27-MAY-2020 13:35:00

      Uptime                    0 days 0 hr. 0 min. 0 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Log File         /opt/oracle/product/18c/dbhomeXE/network/log/listener.log

      Listening Endpoints Summary...

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-xxx-xx-xx-xx.eu-west-1.compute.internal)(PORT=1521)))

      The listener supports no services

      The command completed successfully

       

      listener log

      TNSLSNR for Linux: Version 18.0.0.0.0 - Production on 27-MAY-2020 13:35:00

       

       

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

       

       

       

       

      LISTENER for Linux: Version 18.0.0.0.0 - Production

      Version 18.4.0.0.0

       

       

      Log messages written to /opt/oracle/product/18c/dbhomeXE/network/log/listener.log

      Trace information written to /opt/oracle/product/18c/dbhomeXE/network/trace/listener.trc

      Trace level is currently 0

       

       

      NL-08014: Failed to initialize Diagnosability framework, falling back to old network tracing/logging

      No valid ADR Base to use

       

       

      NL-08013:  Invalid ADR Base directory

      Started with pid=1572

      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-xxx-xx-xx-xx.eu-west-1.compute.internal)(PORT=1521)))

      Listener completed notification to CRS on start

       

       

      TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE

       

       

      27-MAY-2020 13:36:30 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=sqlplus)(HOST=ip-xxx-xx-xx-xx.eu-west-1.compute.internal)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=52734)) * establish * XE * 12514

      TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

       

       

      27-MAY-2020 13:36:53 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=zzz.zzz))) * (ADDRESS=(PROTOCOL=tcp)(HOST=y.yy.yyy.y)(PORT=63276)) * establish * xe * 12505

      TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

       

       

      27-MAY-2020 13:36:53 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=zzz.zzz))(SID=xe)) * (ADDRESS=(PROTOCOL=tcp)(HOST=y.yy.yyy.y)(PORT=63277)) * establish * xe * 12505

      TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

       

       

      27-MAY-2020 13:38:22 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=zzz.zzz))(SERVICE_NAME=xepdb1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=y.yy.yyy.y)(PORT=63812)) * establish * xepdb1 * 12514

      TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

       

      Thanks again!

        • 1. Re: XE Previously working - TNS service not known after machine reboot.
          L. Fernigrini

          This is strange:

           

          Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-xxxxx-xx-xx.eu-west-1.compute.internal)(PORT=1521)))

           

          If listener is configured to use localhost...

          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

           

          Make sure that the listener is really started using localhost, you do not need to use the real (short or full) server name nor the IP address

           

          Try restarting the listener and confirm that is running on "localhost".

          1 person found this helpful
          • 2. Re: XE Previously working - TNS service not known after machine reboot.
            Nazbit

            @L. Fernigrini Thanks for your reply.

             

            Restarted it as below. Still seeing lots of references to the internal AWS host name. Not sure where that is coming from. Is that the problem?

             

            I think that you're saying its correct to have localhost in the listener.ora and tnsnames.ora files rather than using a host or ip address?

             

            Thanks again to everyone who can offer any assistance.

             

            # lsnrctl stop

             

             

            LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 27-MAY-2020 19:17:34

             

             

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

             

             

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

            The command completed successfully

             

            # lsnrctl start

             

             

            LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 27-MAY-2020 19:17:40

             

             

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

             

             

            Starting /opt/oracle/product/18c/dbhomeXE/bin/tnslsnr: please wait...

             

             

            TNSLSNR for Linux: Version 18.0.0.0.0 - Production

            Log messages written to /opt/oracle/product/18c/dbhomeXE/network/log/listener.log

            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-xxx-xx-xx-xx.eu-west-1.compute.internal)(PORT=1521)))

             

             

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

            STATUS of the LISTENER

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

            Alias                     LISTENER

            Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production

            Start Date                27-MAY-2020 19:17:42

            Uptime                    0 days 0 hr. 0 min. 0 sec

            Trace Level               off

            Security                  ON: Local OS Authentication

            SNMP                      OFF

            Listener Log File         /opt/oracle/product/18c/dbhomeXE/network/log/listener.log

            Listening Endpoints Summary...

              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-xxx-xx-xx-xx.eu-west-1.compute.internal)(PORT=1521)))

            The listener supports no services

            The command completed successfully

             

            /opt/oracle/product/18c/dbhomeXE/network/log/listener.log

            Listener completed notification to CRS on stop

            27-MAY-2020 19:17:34 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ip-xxx-xx-xx-xx.eu-west-1.compute.internal)(USER=root))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=301989888)(CRS=ON)) * stop * 0

             

             

            TNSLSNR for Linux: Version 18.0.0.0.0 - Production on 27-MAY-2020 19:17:42

             

             

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

             

             

             

             

            LISTENER for Linux: Version 18.0.0.0.0 - Production

            Version 18.4.0.0.0

             

             

            Log messages written to /opt/oracle/product/18c/dbhomeXE/network/log/listener.log

            Trace information written to /opt/oracle/product/18c/dbhomeXE/network/trace/listener.trc

            Trace level is currently 0

             

             

            NL-08014: Failed to initialize Diagnosability framework, falling back to old network tracing/logging

            No valid ADR Base to use

             

             

            NL-08013:  Invalid ADR Base directory

            Started with pid=2053

            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-xxx-xx-xx-xx.eu-west-1.compute.internal)(PORT=1521)))

            Listener completed notification to CRS on start

             

             

            TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE

            27-MAY-2020 19:17:42 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=xxx-xx-xx-xx.eu-west-1.compute.internal)(USER=root))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=301989888)) * status * 0

            Trace information written to /opt/oracle/product/18c/dbhomeXE/network/trace/listener.trc

            Trace level is currently 0

            Log messages written to /opt/oracle/product/18c/dbhomeXE/network/log/listener.log

            • 3. Re: XE Previously working - TNS service not known after machine reboot.
              Nazbit

              Morning,

               

              Still pretty stuck with this.

               

              Just adding some more info in case it helps...

               

              Host File

              # cat /etc/hosts

              127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

              ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

               

              Host Name

              # cat /etc/hostname

              ip-xxx-xx-xx-xx.eu-west-1.compute.internal

               

              I have tired:

              Changing the hosts in tnsnames.ora and listener.ora from localhost to ip-xxx-xx-xx-xx.eu-west-1.compute.internal and restarting the listener. This made no difference so I've reverted them.

              Added  127.0.0.1   ip-xxx-xx-xx-xx.eu-west-1.compute.internal to the hosts file, again no difference so iIve reverted.

               

              So as it stands all of the config is as per the original post and the listener output is the same.

               

              Thanks for reading.

              • 4. Re: XE Previously working - TNS service not known after machine reboot.
                Dude!

                # cat /etc/hostname

                ip-xxx-xx-xx-xx.eu-west-1.compute.internal

                Is the ip address supposed to be part of your hostname?

                 

                RHEL 6/7/8 are very different systems, hence administration is very different. Starting with RHEL 7, for example, NetworkManager is also deployed during minimal installations and previous methods to configure network settings are deprecated. Users are supposed to change the system hostname through the hostnamectl utility. Also the use of the HOSTNAME variable in the /etc/sysconfig/network file is deprecated.

                 

                For example:

                 

                # hostnamectl set-hostname eu-west-1
                
                # systemctl restart systemd-hostnamed
                

                 

                If you are configuring your Oracle net listener to use any specific hostname, you also need to make sure it can resolve, either using your configured DNS server, or /etc/hosts.

                 

                For example:

                 

                $ cat /etc/hosts
                127.0.0.1  localhost localhost.localdomain localhost4 localhost4.localdomain4
                ::1        localhost localhost.localdomain localhost6 localhost6.localdomain6
                192.168.1.1    eu-west-1.compute.internal
                

                 

                 

                The Oracle Net Listener Parameter documentation outlines the relationship between Listener HOST and computer HOSTNAME. It is a popular misconception that the Oracle Listener cannot use localhost and accept remote connections. However, if the HOST parameter is localhost and the server's hostname is localhost.localdomain, then the Listener will accept connections from any TCP/IP interface.

                 

                If you use a IP address as HOST= in your listener config, then the listener will only listenen on that IP address (and corresponding interface).

                 

                To set ORACLE_HOME, etc. is not enough. You also need to adjust PATH, etc. The easiest way is to use oraenv. It's probably still included in 18c. I haven't tried 18c XE yet. For example:

                 

                $ source oraenv <<< XE
                
                1 person found this helpful
                • 5. Re: XE Previously working - TNS service not known after machine reboot.
                  Nazbit

                  Dude! Hi, thanks for your reply. I will read that documentation.

                   

                   

                  Is the ip address supposed to be part of your hostname?

                  That is the hostname that is automatically generated upon creation of the VM in EC2. On creation is looks like this:

                   

                  Public IP x.xxx.xxx.xxx

                  Public DNS ec2-x-xxx-xxx-xxx.eu-west-1.compute.amazonaws.com

                   

                  Private IP yyy.yy.yy.yy

                  Private DNS ip-yyy-yy-yy-yy.eu-west-1.compute.internal

                   

                  If you are configuring your Oracle net listener to use any specific hostname, you also need to make sure it can resolve, either using your configured DNS server, or /etc/hosts.

                  For example:

                   

                   

                   

                  1. $ cat /etc/hosts 
                  2. 127.0.0.1  localhost localhost.localdomain localhost4 localhost4.localdomain4 
                  3. ::1        localhost localhost.localdomain localhost6 localhost6.localdomain6 
                  4. 192.168.1.1    eu-west-1.compute.internal 

                  I have tried adding 127.0.0.1  ip-yyy-yy-yy-yy.eu-west-1.compute.internal which did not help.

                   

                   

                  The listener still starts as.....

                  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-xxxxx-xx-xx.eu-west-1.compute.internal)(PORT=1521)))

                   

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

                  ... is that definitely incorrect? Or am I going down the wrong avenue here? Are we expecting to see "Listening on Local Host" and "connecting to local host"?

                   

                   

                   

                  To set ORACLE_HOME, etc. is not enough. You also need to adjust PATH, etc.

                  I have the following in my profile:

                   

                  ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE

                  PATH=$ORACLE_HOME/bin:$PATH

                  LD_LIBRARY_PATH=$ORACLE_HOME/lib

                  ORACLE_SID=xe

                  export ORACLE_HOME

                  export LD_LIBRARY_PATH

                  export PATH

                  export ORACLE_SID

                   

                   

                  I have confirmed the same behaviour in RHEL 7 and 8.

                   

                  What I don't understand is why everything works perfectly at the time of database creation and then stops working after a reboot.

                   

                  As far as I can see the DNS entries and IP addresses (both internal and external) are persisting through the reboot. So it doesn't make much sense to me. I'm not sure what is changing during the reboot.

                   

                  I guess I need to create a VM from scratch again and pull the listener log before the reboot to compare with the above. (It is not one off behaviour, I've spun the VM up 4 times now trying different things and always everything works until first reboot)

                  • 6. Re: XE Previously working - TNS service not known after machine reboot.
                    Nazbit

                    Started again with a clean VM.

                     

                    Same situation but just to clarify its not the rebooting of the box like I thought it was that is causing the issue its the listener.

                     

                    So this time once the DB was installed and configured I could access it as normal and then after starting and stopping the listener (where I had previously been restarting the VM) I'm locked out with the same symptoms.

                     

                    So what could be different about the way that the config script (/etc/init.d/oracle-xe-18c configure) starts the listener and the way its starting on subsequent calls.

                    • 7. Re: XE Previously working - TNS service not known after machine reboot.
                      Dude!

                      I have tried adding 127.0.0.1  ip-yyy-yy-yy-yy.eu-west-1.compute.internal which did not help.

                      What is this supposed to accomplish?

                       

                      If your listener uses ip-yyy-yy-yy-yy.eu-west-1.compute.internal then your /etc/hosts file should list that name with a valid IP address, but not the loopback adapter, etc.:

                       

                      /etc/hosts

                      127.0.0.1 localhost.....

                      ::1 ....

                      192.168.0.1 192.168.0.1.eu-west-1.compute.internal

                       

                      If you want to use localhost and have the listener listen on all interfaces than your host in the listener should be "localhost" and your computer's hostname must match, e.g. localhost.localdomain.

                       

                      If you listener host is 192.168.0.1.eu-west-1.compute.internal, then it must resolve, but cannot be localhost.

                      1 person found this helpful
                      • 8. Re: XE Previously working - TNS service not known after machine reboot.
                        Dude!

                        ORACLE_SID=xe

                        Oracle SID is case sensitive in Linux and Unix.

                         

                        What's in your /etc/oratab file, Is there XE upper or lowercase?

                         

                        If you have case missmatch then the following won't work:

                        conn sys@xe / as sysdba

                         

                        Btw, connections as sysdba are always connecting to the SYS schema. You can even try:

                        sqlplus "humptytrumpty/letmein@XE as sysdba" and it will connect to the SYS schema/user.

                        Hence you can also use sqlplus / as sysdba and forget about username and use OS (dba) authentication.

                        1 person found this helpful
                        • 9. Re: XE Previously working - TNS service not known after machine reboot.
                          Nazbit

                          @Dude!

                           

                          If your listener uses ip-yyy-yy-yy-yy.eu-west-1.compute.internal then your /etc/hosts file should list that name with a valid IP address, but not the loopback adapter, etc.:

                           

                          /etc/hosts

                          127.0.0.1 localhost.....

                          ::1 ....

                          192.168.0.1 192.168.0.1.eu-west-1.compute.internal

                          Thanks for taking the time to reply again. This solved the problem. I had misunderstood and incorrectly

                          mapped the host to 127.0.0.1.  It is now mapped incorrectly. Interestingly just correcting /etc/hosts didn't work after database installation and configuration had already taken place.

                           

                          However starting over again (probably on double figures now!!) and mapping the host correctly prior to installing and configuring the database was what finally solved this issue.

                           

                          So that makes me think that the host mapping may have also been copied elsewhere by the installation package or configuration script.

                           

                          Anyway that's sorted so I will mark the question as answered.

                           

                          Thanks again.