We help IT Professionals succeed at work.

Connecting to an Oracle DB on RedHat Enterprise Linux 7 in the Azure cloud

cyimxtck
cyimxtck asked
on
1,821 Views
Last Modified: 2017-02-07
We have the database up, running functional and can hit it locally when we login to the Azure/RHEL instance.  Everything works fine on the Linux box but connecting from a computer outside the Azure world we cannot get to work.

Dozens and dozens of articles later we are dead in the water and out of ideas.

Connecting with sqlplus, TOAD or SQL Developer doesn't work so we are perplexed and displeased with the lack of documentation around this.  The articles that are out there are plentiful for "select" scenarios but nothing realistic as to troubleshooting.

https://oracle-base.com/articles/vm/installation-of-an-oracle-database-on-azure

Here is one of the dozens of articles we are talking about.  We need help determining the issue and not another install (3 so far that are not successful)

Oracle works.   It is running.  We need to troubleshoot connectivity to it.

Any help would be greatly appreciated
Comment
Watch Question

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi cyimxtck,

A few questions to jump start things.  :)

1 - What version of Oracle have you installed?
2 - Are you using the Oracle client (on the "desktop") or something else?
3 - If the Oracle Client, what is the version of the Oracle client?
4 - What is the result of running TNSPING against the new database?
Julian ParkerSenior Systems Administrator
CERTIFIED EXPERT

Commented:
Did you follow the section on endpoints for the listener service in the docs?
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
And of course, the biggest question is what is the error you are getting.

Author

Commented:
Let me see if I can answer all of the questions in one go:
kdo:
1 - What version of Oracle have you installed? Oracle 12c Standard Edition 2
2 - Are you using the Oracle client (on the "desktop") or something else?  To connect we are using TOAD, SQL*PLUS, SQL Developer all which time out
3 - If the Oracle Client, what is the version of the Oracle client?  Oracle 12 c client install
4 - What is the result of running TNSPING against the new database?   TNS-03505: Failed to resolve name

jools: Endpoints: We have installed the endpoint on Azure but much to no avail

johnsone: timeout errors

Additional info: We have installed the software once (db_1) and it failed.  Second time it worked (db_2) so we have to change the home manually when on Linux: ORACLE_HOME=/...product/12/db_2
then can connect:

-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 30 09:08:30 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             675283048 bytes
Database Buffers          390070272 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 30 09:13:22 2017

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


Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> select
  2    *
  3  from
  4   dual;

D
-
X

SQL>

it is up as you can see

tnsping is failing with this: TNS-03505: Failed to resolve name

Author

Commented:
sorry, TNSPING is TNS-12541: TNS:no listener (typed in the wrong thing)
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
It looks like a configuration issue on the client machine.  TNSPING doesn't know how to connect to the database so it's unlikely that anything else will either.

Are you using EZCONNECT?  If not, have you configured the SQLNET.ORA and TNSNAMES.ORA files?
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Ahha.  That's different.  :)

You'll need to configure the listener.  

Let's start with the basics.  Run LSNRCTL and check the status.

LSNRCTL
>STATUS

Author

Commented:
We have configured the tnsnames.ora, sqlnet.ora and listener.ora.  

sqlnet.ora:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tnsnames.ora:
NAME_ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1234))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1234))
      (ADDRESS = (PROTOCOL = IPC)(KEY = IPCEXT1234))
    )
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /.../product/12/db_2)
      (SID_NAME = ORCL)
    )
  )

Author

Commented:
-bash-4.2$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2017 09:25:56

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


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1234)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=IPCEXT1234)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
No listener and timeout are two totally different things.

No listener would indicate that the listener is not started and/or configured.

Timeout would typically be a firewall issue.  But, if you don't have a listener running, I don't see how you are getting that.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Port 1234 is an unusual port for Oracle to be using.  Did you change the default port to 1234?

Connect to the database as SYSDBA and check the value of the parameter "local_listener".

Author

Commented:
I don't understand....we can clearly select from dual when we connect sqlplus / as sysdba

So we tried connections from remote machines with the software mentioned above (TOAD, etc.) and they all time out.  So no listener how is that possible if the database is up and running?  We are unsure what the heck is going on here...

Author

Commented:
kdo I altered that port because the default of 1521 was getting a LOT of hacker (44 attempts in 2 hours) so we changed it to something else (not 1234 because I don't want to have the port here either)

The Azure endpoint also has the port we used in it.

SQL> SQL> show parameters local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
SQL>
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
When you run LSNRCTL, the output should include the path to the listener parameter and logs files.

Check the log file to see if there are any hints (rejected connections, etc.)  I doubt it, but it's worth a look.

Has the listener's parameter file (shown in the lsnrctl output) been properly configured?  If so, try stopping and starting the listener to reload the parameters.

Author

Commented:
if we type lsnrctl start it seems to start

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                30-JAN-2017 09:40:28
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /.../product/12/db_2/network/admin/listener.ora
Listener Log File         /.../listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.local)(PORT=1234)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=IPCEXT1234)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Author

Commented:
Sorry didn't see your comment...checking the XML log mentioned above now
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Connecting with / as sysdba on the server does not use the listener.  The listener is only for remote connections.  Using a local connection on the server uses the BEQUEATH protocol, not TCP.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Now that you have the listener running, what is the message you get on the client from tnsping.  Let's start with the "are you at least getting there" step.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
I don't think checking the listener log is useful.  If the listener isn't up you cannot connect to it and it won't log anything.

Author

Commented:
log only has warning:
 <txt>WARNING: Subscription for node down event still pending

or the status:
<msg time='2017-01-30T09:40:28.571-05:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16'

type of unknow...

nothing else really stands out
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Silly question, but is the database mounted and open?

The listener seems to be running, but can't communicate with the database.  Let's check the easy things first.  :)

Author

Commented:
yeah it said it was when I did the startup

Author

Commented:
-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 30 09:51:29 2017

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


Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             675283048 bytes
Database Buffers          390070272 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL>
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Can you post the listener.ora file?

Author

Commented:
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1234))
      (ADDRESS = (PROTOCOL = IPC)(KEY = IPCEXT1234))
    )
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /.../product/12/db_2)
      (SID_NAME = ORCL)
    )
  )
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
The issue above was the listener wasn't running on the box.  It is now.

If the instance is named ORCL, it looks like the instance registered with the linstener:
Service "ORCL" has 1 instance(s).

Since you have now restarted the instance, verify it has re-registered from a shell prompt:
lsnrctl status

If you see ORCL, everything from a server side should be fine.

From a client machine, now what is the error when you try to connect?

Author

Commented:
from remote machines:

SQL Developer: Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection

TOAD: ORA-12170: TNS:Connect timeout occurred
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>ORA-12170: TNS:Connect timeout occurred

99.999% of the time that is a firewall issue blocking the high-level ports.

I hope you aren't trying to connect directly to the server using an exposed/public IP address.

I'm guessing you already have the SSH/Secure connection established between the remote client and the Azure server?
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Running the client tools on the server again, does the output from TNSPING and LSNRCTL STATUS look correct?

Author

Commented:
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
I have also seen an issue similar where routing tables weren't set up correctly (look at iptables, there is a link to it in your original post).  For me, it was an internal server that nobody really cared about so we just deleting all the routing.

Author

Commented:
-bash-4.2$ tnsping ORCL

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2017 10:20:23

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/.../product/12/db_2/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

How is this possible....lsnrctl says it is up?
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
could it be the failed install doing something (db_1) mentioned earlier?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>How is this possible....lsnrctl says it is up?

Was that tnsping from the local client?  Looks like it doesn't have a proper tnsnames.ora file configured.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>could it be the failed install doing something (db_1) mentioned earlier?
It might be.  It seems to be a configuration issue.  If it is related to a previous ORACLE_HOME has yet to be seen.

When you installed the new client software the PATH should be set to the latest one.  Make sure you are running all the tools from db_2's HOME not db_1.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
The listener says the status is UNKNOWN.  It knows about the database but can't communicate with it.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
My guess is that is tnsping from the server (notice it says for Linux).  If you didn't set up a tnsnames.ora file on the database server, that is your issue.

Author

Commented:
We use the /etc/hosts and are using the name of the Azure instance so:

ip address AzureName.local   Alias

there are entries in /etc/services with the same number on the port

so it reads name      port/tcp               # description

Or was that created FROM Oracle?

Or we need to change our port for Oracle (ora files) and restart?

Author

Commented:
>>My guess is that is tnsping from the server (notice it says for Linux).  If you didn't set up a tnsnames.ora file on the database server, that is your issue.

We have this setup and it is posted above (at the top are all 3 ora files)
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
A listener status of UNKNOWN (as per the documentation here) does not mean that the database cannot communicate with the listener.  It means that it is statically registered with the listener.  As per the listener.ora file that was posted that is correct.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
In the definition in /etc/hosts

  ip address AzureName.local   Alias

is the ip address the localhost address or the network address?

Author

Commented:
It is the Azure server VM slice IP address.

Did you see my comment above?

We use the /etc/hosts and are using the name of the Azure instance so:

ip address AzureName.local   Alias

there are entries in /etc/services with the same number on the port

so it reads name      port/tcp               # description

Or was that created FROM Oracle?

Or we need to change our port for Oracle (ora files) and restart?

Author

Commented:
Not sure if you just moved on as this isn't an issue or this IS the issue and you read that last note from me?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
You are in good hands.  There are too many people helping right now.

If you don't get things resolved and the others run out of ideas, I'll jump back in.

Author

Commented:
-bash-4.2$ netstat -an | grep LISTEN | grep 1234
tcp6       0      0 :::1234                 :::*                    LISTEN
unix  2      [ ACC ]     STREAM     LISTENING     #####    /var/tmp/.name/sIPCEXT1234


Why not tcp4?

Does that matter?
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
>> It is the Azure server VM slice IP address.

>> Did you see my comment above?

I did.  Just trying to follow up.  

There can be a significant difference in how the network traffic is handled when the address is the localhost or the network address.  The localhost traffic routes right back onto the host with minimal interaction with the IP stack processes.  Using the network address can subject the traffic to involvement by more of TCP/IP processes.

What we think we know:

- Oracle and the listener are running.  
- The listener knows of the Oracle instance, but cannot communicate with it.
- SQLNET.ORA, LISTENER.ORA appear to be configured correctly.
- The Oracle parameters appear to match the configuration files.
- The TNSNAMES.ORA file appears correct.

Since Oracle looks to be correct, I'm thinking of communications related issues.  There may be a firewall or port restriction/conflict.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Are you using a port above 1024?

Author

Commented:
yes it is in the thousands
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Can we stop here and take a minute to talk about the listener status that you are seeing?  You should see one of these three scenarios

Scenario 1:
Service "tstora" has 1 instance(s).
  Instance "tstora", status READY, has 1 handler(s) for this service...

Open in new window

This is dynamic instance registration.  Based on the listener.ora file you aren't doing this.  The listener should be started before the database is started in this situation, otherwise it can take some time for the database to register with the listener or it may not register.

Scenario 2:
Service "tstora" has 2 instance(s).
  Instance "tstora", status UNKNOWN, has 1 handler(s) for this service...
  Instance "tstora", status READY, has 1 handler(s) for this service...

Open in new window

This is static instance registration but the listener was running before the database started.  Then UNKNOWN status is the static registartion.  The READY is because it seems to do both dynamic and static if the listener is up before the database.

Scenario 3:
Service "tstora" has 1 instance(s).
  Instance "tstora", status UNKNOWN, has 1 handler(s) for this service...

Open in new window

This is static instance registration but the listener was down when the database started.  The UNKNOWN status is from static registration and documented that way in the link provided previously.  Based on the information posted in this thread so far, I believe this is the scenario you have.

Now, in all 3 of these scenarios, you are able to connect to the database.

All that being said, I'm pretty sure we have the listener issue sorted out.

Back to the problem...

Are you still getting timeouts when you are trying to connect to the database?  If so, it is likely a firewall issue.  You can completely disable the firewall on the Oracle Linux side, but I'm not sure if that is what you are looking for.  It also depends on your version of Oracle Linux how you do it.  If you would like some links that show you how to do it, I can point you in the right direction, but just be aware that the links I would post would be to disable the firewall completely.  If you wanted to add a rule to allow the Oracle connections through that would be a different set of instructions.

Author

Commented:
You are totally correct in the above statement and when we have the instance down and start the listener it says as you describe.  The issue that we have is that the file /etc/oratab contains two entries.  One has the old install (corrupt headers in the control files and the other is the "active one"

I can tnsping the active one but not the instance of Oracle that is in the tnsnames.

Should those values match or ...

It seems we tramped on each other at some point in naming this stuff and reinstalling.

But we can connect to the database on Linux and life is roses....just not a remote machine.  We connect to either the SID or the name in the /etc/oratab (where :Y) and it works

Remotely, not so much as it always times out

Any ideas>?
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
If you want to verify that a connection is working, then from the server use the easy connect string.

sqlplus user/password@//host:port/sid

If that connects without a problem on the server side and gets a timeout on the remote side, then your issue is almost certainly a firewall issue.  Only you can say how you want to resolve that.  It would either mean a firewall rule to allow the connections or disable the firewall completely.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Also, make sure that you can ping the server remotely.  That at least tells you that you have basic connectivity.

Author

Commented:
that times out on the server!  we can connect with userid/pwd@sid but not as you have it above (never thought of that actually)

What does that mean in the grand scheme of things?  Like what does that tell us about the below error from the server?

from the Linux server:
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 30 18:20:25 2017

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

ERROR:
ORA-12170: TNS:Connect timeout occurred

Author

Commented:
>>Also, make sure that you can ping the server remotely.  That at least tells you that you have basic connectivity.

You cannot ping anything on Azure by design.  You cannot tnsping anything by design either.

You either get it perfect (SID, UID, PWD, etc.) or it fails completely without an error on the client aside from timeout.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
tnsping should work regardless of platform.  If tnsping will never work, you will never be able to connect to the database.  tnsping does not ping the server by looking something up in the Oracle configuration, it connects to the listener.  If tnsping cannot connect to the listener, then nothing else can either.  All tnsping proves is that the listener is running and you can connect to it, it doesn't prove your database is up though.

ping is shut of by design on a lot of servers, but if it isn't it is a good test of network connectivity.

When you connect on the server with user/pass@sid, can you run this:

ps -fu oracle | grep LOCAL

You should see one connection where LOCAL=NO.  If you are seeing that then your listener is working.  That is all we are really trying to prove there.

If you get the LOCAL=NO connection, then your issue is with your network setup.  Again in all likelihood it is a firewall issue.  Assuming that you followed the directions in your original post to get the endpoints set up and that is working.

Author

Commented:
so a couple things...

I can tnsping the name here (the second one): cat /etc/oratab <-- there are two entries here; the first was the corrupt install and the second is the successful

But not the Oracle SID.  Should those match?  The entry in oratab and the SID.

netstat -an | grep LISTEN | grep port# shows a listener with IPCEXT1234

netstat -ltp shows tnslsnr twice but both on tcp6
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
tnsping doesn't use oratab.  tnsping uses TNS, which in most cases is the tnsnames.ora file.  If tnsping works with a name in the oratab file then you have an entry in your tnsnames.ora file with that name.

Typically in a single instance setup the database name and instance name match but they don't have to.  In a RAC setup, they cannot match, you cannot have multiple instances with the same name.

Not sure why we are running netstat, we know the listener is running.

Author

Commented:
we have completely uninstalled and reinstalled everything.  The connection is now getting the ORA-12541: TNS:no listener error from remote connections but locally we can connect as a test user.

It doesn't time out anymore - has to be hitting the host because it gets the Oracle message.  Just not clear on how to wake it up?

We have been pounding this for weeks now and have made progress but just need that last little bit.  Thanks for everyone's contributions

Any idea?
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Howdy...

Have you verified that the user's configuration files match those on the server?

Author

Commented:
We are doing it by tnsnames.ora and on the server it is localhost and remotely it is the ip address but aside from that they match
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
What does a TNSPING from the client machine show?

Author

Commented:
same error

TNS-12541: TNS:no listener
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Is the Oracle client on the user machine compatible with the version of Oracle on the server?

Are there any ACL's on the server OR the network that could be interfering with the connection(s)?

Can you connect from the desktop using the EZConnect form?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>TNS-12541: TNS:no listener

The listener isn't running on the database server or you it is listening on a different ip address/port or you aren't talking to the server you think you are talking to.

>>It doesn't time out anymore -

You likely will once you get to talk to the listener.

The two problems really aren't related.

Author

Commented:
Cannot connect directly EZCONNECT or tns selection...nothing we are aware of would stop this activity (ACL or otherwise)

Author

Commented:
ok can we talk through this?

The listener isn't running on the database server or you it is listening on a different ip address/port or you aren't talking to the server you think you are talking to.

The tnsping works.  Shows the values we are using on the remote machine.  We can see the port on the entry and have an endpoint on Azure with the same.

Can you tell us how to do "something different" to check this or alter this or test another way or log files or....just anything?  We are totally stumped here.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
As Slightvw notes above, there's still something going on that we're not aware of.

From the client machine, run NSLOOKUP on the same name that appears in the TNSNAMES file (HOST= parameter).  Does it resolve to the server's address?

Can you ping the server from the client machine by name and IP address?

Author

Commented:
it does get resolved with nslookup but you cannot ping Azure period.  At all.  By design.  To keep the hacking world at bay.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
We are getting conflicting info:
What does a TNSPING from the client machine show?
you replied:  same error   TNS-12541: TNS:no listener

Now you post:  
The tnsping works.

When posting errors, can you start including what machine you are on when getting what error?

Author

Commented:
sorry about that...

tnsping works on the server and we have input that information into the tnsnames/listener.ora on the client machine.

On the client box we get the What does a TNSPING from the client machine show?
you replied:  same error   TNS-12541: TNS:no listener
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Is the local address on the server where tnsping works exposed to the outside world?  In other words, when you did the nslookup from the client machine, did it resolve to the same ip address the listener is using?

Author

Commented:
yes....if I nslookup from the client box it resolved that and shows the ip address
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
From the client can you telnet to the listener?

If the listener is listening on ip address 1.2.3.4 on port 1111:
telnet 1.2.3.4 1111

Author

Commented:
network error: Connection refused

Author

Commented:
even from the server: telnet: connect to address XXXX: Connection refused

telnet: XXXX:XXXX Name or service not known
XXXX:XXXX: Unknown host
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>network error: Connection refused

That would explain why tnsping also fails.  It is a similar error:  Your server appears to be blocking remote connections for that port and ip address.

>>even from the server: telnet: connect to address XXXX: Connection refused

Not sure I've ever seen that one.

>>telnet: XXXX:XXXX Name or service not known

You sure you had everything right?  Surely the server knows about the ip address and port.

Author

Commented:
It must be a bad port or something ?  Won't allow it through
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>It must be a bad port or something

There really isn't anything as a bad port in the software world.

Can you make a TNS connection from the server?
sqlplus username/password@TNSALIAS

where TNSALIAS is the entry from the tnsnames.ora file

The "@TNSALIAS" is important.  sqlplus username/password does a BEQUETH connection and doesn't use any of the TNS setup.

Author

Commented:
we did connect to it that way and created a test user for that purpose
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>we did connect to it that way and created a test user for that purpose

Not sure why you need a specific user.  Connecting is the important piece.  That is progress.

As far as Oracle is concerned, it looks like everything on the server is good.

So there is something restricting communication between your client and server on that ip address using that port.

That likely isn't Oracle.  I would work with your system administrators and/or network folks to find what out what is blocking you.

Remember that based on your previous timeout errors, that is 99.999% of the time a firewall issue so I would probably start there.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>then the remote machine was allowed to get to the server and the port.

I just set up a test on my servers.  I changed to port to a non-listener port.  HOST and SERVICE_NAME entries were correct.  Only the port was different.  tnsping returned a TNS-12541: TNS no listener.

I'll give you the ip address.  changing that to a valid IP address for a server with no listener returned the TNS-12535 timeout.

Author

Commented:
So one thing we found is Azure has changed the way things are configured in the endpoints.  You can no longer specify a "Floating IP" which would let you connect (not intuitive; it doesn't mean dynamic).  Now they have new entries in IP configuration which has the ability to "open an ip" and let you ping it and all that good stuff - you can "reach it"

Where do we put this "new ip" we created such that Oracle can see it, Azure can see it and we can see it at the client level?  Meaning, HOSTNAME=new ip or....Any idea on that?

The vm slice has it's ip on Azure = 1.1.1.1 (private ip address- we have Oracle using this in Linux)
there is a virtual ip = 2.2.2.2 (resolved with DNS but cannot ping)
this new ip we added is 3.3.3.3 (can ping this address from client)

How can we configure Oracle to "share" this pingable ip so we can hit that remotely and Oracle can serve up the goods?

Also netca tells us that the port x is already in use when we try to use the listener configuration - we disabled the usage from the previous app  in Linux and created our own udp and tcp entries for it on that port.  No matter what port we use, netca says it is in use.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Several posts above you said the nslookup form the client machine resolved to the ip address Oracle was listening on?

This is true...you can nslookup with it and it gives you back the ip of the host but you cannot ping that ip/host.

I am just totally out of ideas here...how do we expose Oracle "selectively here" and not to the world if we cannot connect ourselves?

It is so frustrating trying to pull this together without any concrete examples online or even on the two Azure books we purchased.

We are open to any advice on this topic right now
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>This is true...you can nslookup with it and it gives you back the ip of the host but you cannot

ip address of the host doesn't matter unless you have NATtting/routing set up properly.

EVERYTHING starts with the IP address and port that the listener is listening for connections on.
Second thing you need is an unobstructed network path between the machine you are trying to connect from and the Listener.  Notice I said the Listener not the server.

Then we get to talk about ports that need to be open AFTER you get the connection.  Oracle claims to have fixed the issue of having ALL of the high ports open but I have yet to actually test it.  We never made it far enough to talk about this topic...

>>It is so frustrating trying to pull this together without any concrete examples

There aren't concrete examples on this because the topic is so broad.  You are trying to set up Oracle in a Cloud environment while maintaining a least privilege security model.  People make careers out of a subset of any one of those single areas.

>>We are open to any advice on this topic right now

We cannot provide that advice because we don't know your system, it's requirements and exactly what you are trying to do.

We know you have a server in the Cloud.  This server has an Oracle database.  Somewhere you have a machine remotely that you want to connect to that database.

We have no way of knowing the security models you have in place or what you actually need.

Can we get your client machine connecting to the database server, I'm confident we can.  Once done, can the entire world also connect to the database?  We cannot say.

Has your company already set up a private network for the Cloud?
What is running on the client machine that needs direct database connectivity?
Are these IP addresses you are using part of the private network?
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
We tried the firewall systemctl start firewalld/systemctl stop firewalld etc.  No dice...that was around 6AM and nothing works still.

Just nowhere to turn on this really
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Like I said since you are talking about a cloud server outside of your control, there is likely more firewalls in the way.  You need to get connectivity through all of them.

You are connected to the server, so you are likely already going through the firewalls to get there.  How did you do that?  You need to do the same thing for the Oracle ports so those can get through as well.

Author

Commented:
We really didn't do a ton there aside from buy an Azure instance.  You add things to it (Like a vm) and it shows that on your control panel.  You can access it by opening a terminal window (in the case of Linux) and hitting it based on the pre-configured parameters they give you.

Securing/hardening the box is something I always do last because of these types of issues so we haven't added in the Barracuda firewall and all the rules on Linux, Oracle, etc. yet.

This is a fairly open instance that is not very cooperative.  

The more troubling part is that Microsoft has adverts for Oracle and Linux and all this that you can purchase.  Ok.  Where is the support for that?  But I digress.

Thanks for all your help
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Back to the question before my warnings about exposure:
>>this new ip we added is 3.3.3.3 (can ping this address from client)

If that IP isn't a public facing one, in other words, if you gave me the real one, could I ping it?

If that IP address is bound to a NIC on the server, then we can probably get you connectivity.

I cannot guarantee security or exposure.

Do you wish to try?

Author

Commented:
Sure we can try....we have nothing now so this is an improvement and I feel like we can harden the box with Oracle and Linux enough to make that work.

Author

Commented:
Sorry forgot: If that IP isn't a public facing one, in other words, if you gave me the real one, could I ping it?

Yes you can ping this IP...hard to tell if it is hooked to the server nic or how it is resolved.  We get the ability to add an ip and make it public
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>Yes you can ping this IP...
Then I won't help get that working.  That would allow me to connect to your database.

>>hard to tell if it is hooked to the server nic or how it is resolved

ifconfig?

>>we have nothing now so this is an improvement and

A wide open database server where I set up a stored procedure that emails me all your data daily isn't an improvement.  Hardening AFTER the fact won't help...

>>I feel like we can harden the box with Oracle and Linux enough to make that work.

In addition to my comment above,  you need a private IP that cannot be seen or addressed by public machines.

Pretend the Cloud is the companies data center that is across the country and you use a VPN to connect from your desktop to a server there.  No real difference:  You are at pointA and you need to access a server at pointB.  The exact location of pointB doesn't matter.

You use the same Internet wiring to get from pointA to pointB but all the traffic between the server and your desktop is encrypted at every step.

Someone should be able to tell you the VPN type IP address for your database server.  Then you need a remote client that has access to the same network.

It doesn't have to be on the same network/subnet but the routing/NATting pieces need to be in place for communication to take place.

Someone you are working with HAS to know the network layout between your Azure machines and the rest of the system.

Someone has to know how the Virtual Switches are set up in the Hyper-V environment.

Until you know all this, you are guessing.

Trust me:  Oracle networking setup isn't that difficult but it also isn't a "run setup.exe, click next and you are done".
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
The use of IP address 0.0.0.0 suggests that the server has multiple network cards/addresses, and your client connection was through the card/address that was not being configured in Oracle.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I'm just glad you got everything up and running!

Yep, I wouldn't have ever gotten to the 0.0.0.0.  We danced all around the other issues though.  ;)


I have no objection if you just accept your post as the solution.  I'm not a fan of participation points.  I'll let the other Experts decide if they agree with that.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Fine with me if we do that.

There was the fact that the listener wasn't started, but that wasn't the real issue.

Author

Commented:
Every contribution that was critical I have given credit for so everyone gets points on this super long winded solution/exercise.  We didn't get all of them together at the same time but in the end we got it!  ;)

Author

Commented:
I am only accepting my solution as a piece of this because I have spoken in depth with two Microsoft tech support folks for hours which has given the correct end result.  I don't want any points though
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.