Avatar of cyimxtck
cyimxtckFlag for United States of America

asked on 

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

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
LinuxAzureOracle Database

Avatar of undefined
Last Comment
cyimxtck
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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?
Avatar of Julian Parker
Julian Parker
Flag of United Kingdom of Great Britain and Northern Ireland image

Did you follow the section on endpoints for the listener service in the docs?
Avatar of johnsone
johnsone
Flag of United States of America image

And of course, the biggest question is what is the error you are getting.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

sorry, TNSPING is TNS-12541: TNS:no listener (typed in the wrong thing)
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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?
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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)
    )
  )
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

-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
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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".
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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...
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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>
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

Sorry didn't see your comment...checking the XML log mentioned above now
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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.  :)
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

yeah it said it was when I did the startup
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

-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>
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Can you post the listener.ora file?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

# 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)
    )
  )
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
>>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?
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Running the client tools on the server again, does the output from TNSPING and LSNRCTL STATUS look correct?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

-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?
SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

could it be the failed install doing something (db_1) mentioned earlier?
>>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.
>>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.
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

The listener says the status is UNKNOWN.  It knows about the database but can't communicate with it.
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

>>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)
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

In the definition in /etc/hosts

  ip address AzureName.local   Alias

is the ip address the localhost address or the network address?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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?
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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

-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?
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

>> 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.
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Are you using a port above 1024?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

yes it is in the thousands
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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>?
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of johnsone
johnsone
Flag of United States of America image

Also, make sure that you can ping the server remotely.  That at least tells you that you have basic connectivity.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

>>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.
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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?
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Howdy...

Have you verified that the user's configuration files match those on the server?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

What does a TNSPING from the client machine show?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

same error

TNS-12541: TNS:no listener
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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?
>>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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

Cannot connect directly EZCONNECT or tns selection...nothing we are aware of would stop this activity (ACL or otherwise)
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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.
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

it does get resolved with nslookup but you cannot ping Azure period.  At all.  By design.  To keep the hacking world at bay.
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?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
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?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

yes....if I nslookup from the client box it resolved that and shows the ip address
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
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

network error: Connection refused
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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

telnet: XXXX:XXXX Name or service not known
XXXX:XXXX: Unknown host
>>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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

It must be a bad port or something ?  Won't allow it through
>>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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

we did connect to it that way and created a test user for that purpose
>>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.
SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
>>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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
>>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?
SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
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?
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
>>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".
ASKER CERTIFIED SOLUTION
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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.
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.
Avatar of johnsone
johnsone
Flag of United States of America image

Fine with me if we do that.

There was the fact that the listener wasn't started, but that wasn't the real issue.
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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!  ;)
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

ASKER

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
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo