Configuring Oracle Listener to Listen Externally

Oracle listening on changing port for remote connections

This is not programming question, maybe it belongs to serverfault.

However, I would think of following:

Is localhost defined in /etc/hosts ? And, if you want to access the listener from network, you should not be listening on localhost, but on external address.

Is the port 1521 available? Verify by running netstat -tlnp .

Does the Oracle DB listener bind to private ip address only?

You just need to configure Oracle Listener with hostname, in case your dns server resolves ip addresses correctly you should have no problem accessing Oracle with hostname.

As for public ip address access, after configuring Oracle with hostname your network's router should be configured to forward a port to Oracle Listener hostname:port. Then you will be able to access Oracle publicly. Of course firewall configuration should be done accordingly.

Oracle 12c server cannot be accessed from remote computer using the .Net provider

You should not have the http: in the host name, in either place. The SQL*Plus version would be:

sqlplus system/password@//10.111.111.47:1521/Telefon

assuming the service name is the same as the SID, which isn't necessarily the case; from your lsnrctl status it is for you so that part should be OK.

But your listener is only listening on localhost, 127.0.0.1. That means it is not contactable from anywhere else. You'll probably get a 'no listener' error with that connection string. You need to modify your listener configuration so it's listening on the external IP address, 10.111.111.47, which is hopefully static and not assigned by DHCP. It can listen on both addresses if necessary:

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

Assuming AndrasWin7x64v1 resolves to 10.111.111.47, at least within that server; if not you can use the IP address instead:

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.111.111.47)(PORT = 1521))

But again, you should only be using IP addresses if the server's IP is static; if it's dynamic (assigned by DHCP) then you should be using a DNS name that resolves to that address, in the listener.ora and from the client, and you should probably do that anyway even if it is static in case it has to change in the future.

Configuring the connection between client and server Oracle 10g

In a comment you have an extract from lsnrctl status:

Listening Endpoints summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services summary...

Your listener is only listening on 127.0.0.1, so connections can only be made from the server. There is nothing listening on your external address 10.0.2.39, so connections to port 1521 on that address fail.

Your listener.ora presumably has something either a single ADDRESS, or no ADDRESS at all, which will default to localhost:1521. You need to modify it to something like:

LISTENER =
...
    (ADDRESS_LIST =
      ...
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.39)(PORT = 1521))
    )

or your machine's host name if that's resolvable to that address. Ideally this would be done through netca rather than by editing the file by hand.

Oracle listener not running and won't start

1.Check the Environment variables (must be set for System and not for user):

ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server
ORACLE_SID = XE

2.Check if you have the right definition in listener.ora

XE =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

3.Restart the service (Services > OracleServiceXE)

After that you may see a new service called OracleXETNSListenerXE.

There is already an old OracleXETNSListener.

I started both and then I was able to make a successful connection.

Edit:

If everything is running but you still can't connect, check if there is no error: ORA-12557: TNS:protocol adapter not loadable.

To correct the error go back to the Environment variables and this time edit the one called: Path. Be sure that C:\oraclexe\app\oracle\product\11.2.0\server\bin is somewhere at the beginning, definitely before any other path pointing to a different version of the Oracle DB.

Configuring the connection between client and server Oracle 10g

In a comment you have an extract from lsnrctl status:

Listening Endpoints summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services summary...

Your listener is only listening on 127.0.0.1, so connections can only be made from the server. There is nothing listening on your external address 10.0.2.39, so connections to port 1521 on that address fail.

Your listener.ora presumably has something either a single ADDRESS, or no ADDRESS at all, which will default to localhost:1521. You need to modify it to something like:

LISTENER =
...
    (ADDRESS_LIST =
      ...
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.39)(PORT = 1521))
    )

or your machine's host name if that's resolvable to that address. Ideally this would be done through netca rather than by editing the file by hand.

How can I run Oracle XE on localhost only?

There are two parts for this (because there are two 'technologies' serving different ports).

Firstly the listener for database port 1521. You use a SQLNET.ORA setting (tcp.invited_nodes) as a soft firewall, so the listener will ignore other nodes.

Secondly, for the 8080 PL/SQL gateway you need to use DBMS_XDB.SETLISTENERLOCALACCESS as described here

---

Related Topics