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

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

Nazbit May 27, 2020 2:33 PM

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 May 27, 2020 5:28 PM (in response to Nazbit)

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
Like Show 0 Likes(0)

Actions
2. Re: XE Previously working - TNS service not known after machine reboot.

Nazbit May 27, 2020 7:41 PM (in response to L. Fernigrini)

@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
Like Show 0 Likes(0)

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

Nazbit May 28, 2020 8:02 AM (in response to 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.
Like Show 0 Likes(0)

Actions
4. Re: XE Previously working - TNS service not known after machine reboot.

Dude! May 28, 2020 10:42 AM (in response to Nazbit)
# 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
Like Show 0 Likes(0)

Actions
5. Re: XE Previously working - TNS service not known after machine reboot.

Nazbit May 28, 2020 1:21 PM (in response to Dude!)
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:

$ 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
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)
Like Show 0 Likes(0)

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

Nazbit May 28, 2020 2:23 PM (in response to 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.
Like Show 0 Likes(0)

Actions
7. Re: XE Previously working - TNS service not known after machine reboot.

Dude! May 28, 2020 3:43 PM (in response to Nazbit)

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
Like Show 1 Likes(1)

Actions
8. Re: XE Previously working - TNS service not known after machine reboot.

Dude! May 28, 2020 3:53 PM (in response to Nazbit)

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
Like Show 0 Likes(0)

Actions
9. Re: XE Previously working - TNS service not known after machine reboot.

Nazbit May 30, 2020 11:13 AM (in response to Dude!)

@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.
Like Show 0 Likes(0)

Actions

Go to original post