How To Update Ethos Through Remote Connection

How to troubleshoot Oracle remote database connectedness

Many Oracle DBAs, developers and just end-users often come across an effect when they tin not connect remotely to an Oracle database. There can be different reasons of the connection problems. I give below a short cookbook on resolving those database connection issues. Just kickoff let's explore a bit Oracle connectivity concepts and terminology.

Oracle NET Client Listener tnsnames

Oracle Internet Client Server configuration

An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients. The service name is included in the connect data part of the connect descriptor. To connect to a database service, clients employ a connect descriptor that provides the location of the database and the proper noun of the database service. The accost portion of the connect descriptor is actually the protocol address of the listener. To connect to a database service, clients first contact a listener procedure that typically resides on the database server. The listener receives incoming client connexion requests and easily these requests to the database server. Once the connection is established, the client and database server communicate directly.

Ping database host IP

ping xi.222.333.44

If it works, get to the next stride;
If not, check the server availability

Ping database hostname

ping orcl.dadbm.com

if information technology works, get to the adjacent step;
if non, something wrong with DNS /Agile directory => endeavor using hosts file

Examination if listener port is reachable/opened for your remote connection

For that use telnet utility which is available on Unix and can exist enabled on Windows.

ane. Linux case:
$ telnet orcl1.dadbm.com 1522 Trying 10.126.247.xi... Continued to orcl.dadbm.com (12.222.333.44). Escape grapheme is '^]'. go Connection closed by foreign host.

ii. Windows example

i) Beginning enable telnet on Windows 7 if information technology's non there:

pkgmgr /iu:"TelnetClient"

2) Check the database port

telnet orcl1.dadbm.com 1523 => no output in instance of a port opened; in case of a failure – see below Oracle error: Could not open connection to the host, on port 1523: Connect failed

In instance the database listener port is not reachable you face potentially a firewall effect. There are at least two solutions of this problems.
Both described in one of my presentations on SlideShare and in Demos on DaDBm YouTube channel.

Test Oracle Net connectivity – tnsping

The Oracle Net Listener is the gateway to the Oracle instance for all nonlocal user connections. A single listener can service multiple database instances and thousands of customer connections. tnsping is the Oracle Net equivalent of the TCP/IP ping utility. It offers a quick test to verify that the network path to a destination is proficient. The utility validates that the host name, port, and protocol achieve a listener. Information technology does not actually check whether the listener handles the service proper name or a database is up and running

tnsping orcl.dadbm.com:1521/orclor
tnsping orcl

1) If it works, you will get a following bulletin:

OK (ten msec)

2) In case of issues or errors, verify that the database listener is configured properly and/or troubleshoot the customer side (see the chapter below)

Test database connection

1) With EZCONNECT bypassing tnsnames.ora (yous tin omit default port 1521)
sqlplus user@'//orcl.dadbm.com:1521/orcl' sqlplus user@'//orcl.dadbm.com/orcl'

2) With TNS allonym using tnsnames.ora file:

sqlplus user@orcl

Troubleshoot the client side

Earlier trying to solve a detail Oracle error on client side, ensure the following on client side:

– Your Oracle customer is installed and configured properly
– Identify your Oracle environment
– Identify current ORACLE_HOME
– Identify a location of tnsname.ora file (if used)
– Verify that you have correctly entered the service proper name of the database that you desire to reach
– If you are connecting from a login dialog box, verify that you are not placing an at symbol (@) before your connection service name.

You tin apply Oracle Universal Installer (OUI) and Os commands to reach all in a higher place steps. For example, on Windows following sqlplus commands can be useful in identifying your Oracle environment:

sqlplus /nolog @%ORACLE_HOME% @%TNS_ADMIN%

The following fault codes are related to problems on the client side

ORA-12154: TNS:could not resolve the connect identifier specified

Couse and Action:
Usually this error betoken that a connect identifier / tns alias you lot use in your connection can non be recognized or establish somewhere. Cantankerous check your tnsnames.ora if it exists there.

ORA-12198: "TNS:could non find path to destination" and
ORA-12203: "TNS:unable to connect to destination"

Cause: The customer cannot find the desired database.
Action:
1. Verify that the service name Accost parameters in the connect descriptor of your TNSNAMES.ORA file are correct.
2. Verify that the listener on the remote node has started and is running. If not, start the listener past using the Listener Command utility.

ORA-12533: "TNS:illegal Accost parameters"

Cause: The protocol-specific parameters in the ADDRESS section of the designated
connect descriptor in your tnsnames.ora file are incorrect.
Activity: For more data about protocol-specific keywords, refer to the Oracle
operating organization documentation for your platform.

TNS-12541: TNS:no listener

Cause: The listener on the remote node cannot exist contacted.
Action: Verify that the listener on the remote node has been started. You tin can cheque its status with the STATUS control of the Listener Control utility and get-go it with the START command if necessary. Verify that the database listener is configured properly using the following commands:
tnslsnr condition
tnslsnr status
tnslsnr services
…

tnsnames.ora file instance

ORCL1 = (Clarification = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl1.dadbm.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL1) ) ) ORCL2 = (Clarification = (Address = (PROTOCOL = TCP)(HOST = orcl2.dadbm.com)(PORT = 1522)) (CONNECT_DATA = (SERVICE_NAME = ORCL2)) ) ORCL3 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl3.dadbm.com)(PORT = 1523)) ) (CONNECT_DATA = (SID = ORCL3)) )

More data

Presentation: Live adventure – From my PC to Oracle remote database
Demo videos:
– How to configure / identify Oracle customer on Windows
– Firewall SSH tunneling to Oracle database

Enjoyed this commodity? Please share it with others using the social site of your pick: