Oracle SQL Developer: Test failed: The Network Adapter could not establish the connection?

1
votes

I'm in real need of a help here. I setup a Oracle Database server on a VirtualBox VM and until yesterday everything seemed to work just fine. Today when I initiated the database to keep working, and turned on SQLDeveloper in my local machine (Client machine) it threw me this error:

Erro de ES: The Network Adapter could not establish the connection

My VirtualBox VM network setting is configure to bridged adapter. And I was able to connect just fine and today, out of nothing this error started occurring.

I did some research but none of them worked for me. Tried to follow this guy solution: Oracle SQL Developer: Failure - Test failed: The Network Adapter could not establish the connection? And didn't work as well. I'm gonna share my lsnrctl status and my file here so you can take a look.

First this is how I start my setup:

1. lsnrctl start to start the listener. Resulting in:

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-AUG-2020 10:40:56

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

Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dbserver/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                24-AUG-2020 10:40:56
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbserver/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

2. sqlplus / as sysdba to start sqlplus shell and then startup to start the database instance, which gives:

ORACLE instance started.

Total System Global Area 2550136832 bytes
Fixed Size          8623928 bytes
Variable Size         671090888 bytes
Database Buffers     1862270976 bytes
Redo Buffers            8151040 bytes
Database mounted.
Database opened.

Then my lsnrctl status gives:

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-AUG-2020 10:46:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                24-AUG-2020 10:40:56
Uptime                    0 days 0 hr. 5 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbserver/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=dbserver)(PORT=5500))(Security=(I removed the path for security))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

My listener.ora:

# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

My tnsnames.ora:

  GNU nano 2.9.8                                                                                   tnsnames.ora                                                                                   Modified  

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

When I try to connect in the database through the SQLDeveloper installed inside the Virtual Machine (That is the server side) it works ok, but when I try to connect from the SQLDeveloper on the local machine (Client side) i throws the error. Every post I saw, told me to look on the listener status, but that is not the problem, I think.

I'd appreciate any help guys. And thank you very much, any additional information I`m here to answer. =D

EDIT: forgot to mention that I'm using version 12c of the Oracle Databse

EDIT: Here is how I'm trying to connect to the DB: enter image description here

EDIT: When I ping the host for the DB I get this:

   [oracle@dbserver admin]$ ping dbserver
PING dbserver(dbserver (fe80::f8dd:c03c:920c:5442%enp0s3)) 56 data bytes
64 bytes from dbserver (fe80::f8dd:c03c:920c:5442%enp0s3): icmp_seq=1 ttl=64 time=0.075 ms
64 bytes from dbserver (fe80::f8dd:c03c:920c:5442%enp0s3): icmp_seq=2 ttl=64 time=0.053 ms
64 bytes from dbserver (fe80::f8dd:c03c:920c:5442%enp0s3): icmp_seq=3 ttl=64 time=0.051 ms
64 bytes from dbserver (fe80::f8dd:c03c:920c:5442%enp0s3): icmp_seq=4 ttl=64 time=0.072 ms
64 bytes from dbserver (fe80::f8dd:c03c:920c:5442%enp0s3): icmp_seq=5 ttl=64 time=0.049 ms
^C
--- dbserver ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 131ms
rtt min/avg/max/mdev = 0.049/0.060/0.075/0.011 ms
2
oracleoracle-sqldeveloperoracle12c
What is the hostname in your SQL*Developer connection properties? Can you ping that hostname / IP address? Did you give your virtual machine a static IP address or are you letting it be assigned one via DHCP? I am thinking that you brought down your VM and, when you brought it back up, it came back with a new IP address. - Matthew McPeak
Sure! Here is the result: [oracle@dbserver admin]$ ping dbserver PING dbserver(dbserver (fe80::f8dd:c03c:920c:5442%enp0s3)) 56 data bytes 64 bytes from dbserver (fe80::f8dd:c03c:920c:5442%enp0s3): icmp_seq=1 ttl=64 time=0.075 ms``` --- dbserver ping statistics --- 5 packets transmitted, 5 received, 0% packet loss, time 131ms rtt min/avg/max/mdev = 0.049/0.060/0.075/0.011 ms @MatthewMcPeak I added an Edit to the question so you can get a more readable result to that - Leonardo Guerreiro
the easiest way to get this going is create a port forward from your host to the vm on 1521, then you can simply connect to localhost:1521 - thatjeffsmith
@thatjeffsmith Can you clearify how I could do this please? - Leonardo Guerreiro
sure thing @LeonardoGuerreiro, see my answer - thatjeffsmith

2 Answers

1
votes

I used to fight with VM networking settings such that my host could 'see' my vm, and vice versa.

I stopped worrying about this once i learned about port forwards.

You can simply tell VirtualBox to take traffic from your local port, say 1521, and forward that to the VM, say also 1521.

So when connecting to the VM from my host in SQL Developer, I can go to localhost:1521, and have my connection traffic go to the Oracle Listener on my Linux VBox image, listening on port 1521

enter image description here

Click on Port Forwarding

Add an entry for TCP 1521 and 1521

enter image description here

Then, in SQL Developer you can simply do this to connect to your DB on the VM

enter image description here

0
votes

I'm glad you got it working, but as an alternative, let me say that I'm not a big fan of port forwarding for this issue, nor am I a fan of using the bridged adapter. I've created scores of VMs under VBox, both on Windows and on Ubuntu hosts. In every case I create HOSTONLY adapter, and configure it with a fixed IP address. I then add an entry for the vm in the host's 'hosts' file. See more detail at https://edstevensdba.wordpress.com/2012/12/15/configure-the-virtualbox-network-2/