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
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
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?
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?
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
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
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
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
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?
Are you using EZCONNECT? If not, have you configured the SQLNET.ORA and TNSNAMES.ORA files?
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
You'll need to configure the listener.
Let's start with the basics. Run LSNRCTL and check the status.
LSNRCTL
>STATUS
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)
)
)
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)
)
)
-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=(PRO
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PRO
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
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=(PRO
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PRO
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
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.
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.
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".
Connect to the database as SYSDBA and check the value of the parameter "local_listener".
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...
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...
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>
The Azure endpoint also has the port we used in it.
SQL> SQL> show parameters local_listener
NAME TYPE VALUE
--------------------------
local_listener string
SQL>
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.
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.
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/netwo
Listener Log File /.../listener/alert/log.xm
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
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/netwo
Listener Log File /.../listener/alert/log.xm
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
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.
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.
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.
log only has warning:
<txt>WARNING: Subscription for node down event still pending
or the status:
<msg time='2017-01-30T09:40:28.
type='UNKNOWN' level='16'
type of unknow...
nothing else really stands out
<txt>WARNING: Subscription for node down event still pending
or the status:
<msg time='2017-01-30T09:40:28.
type='UNKNOWN' level='16'
type of unknow...
nothing else really stands out
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. :)
The listener seems to be running, but can't communicate with the database. Let's check the easy things first. :)
-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>
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>
# 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)
)
)
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)
)
)
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?
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?
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
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?
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?
Running the client tools on the server again, does the output from TNSPING and LSNRCTL STATUS look correct?
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
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.
-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/netwo
TNS-03505: Failed to resolve name
How is this possible....lsnrctl says it is up?
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/netwo
TNS-03505: Failed to resolve name
How is this possible....lsnrctl says it is up?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
>>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.
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.
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.
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 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?
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?
>>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)
We have this setup and it is posted above (at the top are all 3 ora files)
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.
In the definition in /etc/hosts
ip address AzureName.local Alias
is the ip address the localhost address or the network address?
ip address AzureName.local Alias
is the ip address the localhost address or the network address?
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?
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?
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.
If you don't get things resolved and the others run out of ideas, I'll jump back in.
-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?
tcp6 0 0 :::1234 :::* LISTEN
unix 2 [ ACC ] STREAM LISTENING ##### /var/tmp/.name/sIPCEXT1234
Why not tcp4?
Does that matter?
>> 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.
>> 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.
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:
Scenario 2:
Scenario 3:
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.
Scenario 1:
Service "tstora" has 1 instance(s).
Instance "tstora", status READY, has 1 handler(s) for this service...
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...
Scenario 3:
Service "tstora" has 1 instance(s).
Instance "tstora", status UNKNOWN, has 1 handler(s) for this service...
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.
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>?
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>?
If you want to verify that a connection is working, then from the server use the easy connect string.
sqlplus user/password@//host:port/
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.
sqlplus user/password@//host:port/
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.
Also, make sure that you can ping the server remotely. That at least tells you that you have basic connectivity.
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
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
>>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.
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.
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.
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.
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
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
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.
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.
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?
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?
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
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?
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.
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.
Cannot connect directly EZCONNECT or tns selection...nothing we are aware of would stop this activity (ACL or otherwise)
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.
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.
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?
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?
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?
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?
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
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?
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
If the listener is listening on ip address 1.2.3.4 on port 1111:
telnet 1.2.3.4 1111
even from the server: telnet: connect to address XXXX: Connection refused
telnet: XXXX:XXXX Name or service not known
XXXX:XXXX: Unknown host
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.
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.
>>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.
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.
>>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.
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.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
>>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.
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.
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.
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.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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 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?
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?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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
Just nowhere to turn on this really
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.
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.
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
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?
>>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?
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.
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...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".
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".
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.
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.
Fine with me if we do that.
There was the fact that the listener wasn't started, but that wasn't the real issue.
There was the fact that the listener wasn't started, but that wasn't the real issue.
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.