Testing Oracle Net Services Connectivity

Testing connectivity in Oracle 19c involves using various tools and utilities provided by Oracle to ensure that connections between clients and the database server are properly established. Here are the most common tools used for testing connectivity:
1. SQL*Plus Purpose: A command-line tool used to test and manage database connectivity.
Steps to Test Connectivity:

1. Open a terminal or command prompt.
2. Run the command:

   • sqlplus username/password@connect_identifier
             

   • Example:

     sqlplus hr/password@orclpdb1
             

3. If the connection succeeds, the SQL prompt appears. If there is an issue, an error message (e.g., ORA-12154 or ORA-12541) will indicate the problem.

2. Oracle Net Manager Purpose: A graphical tool to configure and test Oracle Net Services, including TNS configuration files. Here's the HTML structure based on your requirements:

• Features:
  • Configure and test tnsnames.ora and sqlnet.ora files.
  • Test database connectivity by providing connection details.
• Steps to Test Connectivity:
  1. Open Oracle Net Manager.
  2. Select the "Service Naming" option.
  3. Add or test an existing service by clicking the "Test" button.

3. tnsping Utility
Purpose: A command-line utility used to test if the Oracle Net Service Name (defined in `tnsnames.ora`) can resolve to a database server.
Steps to Test Connectivity:

1. Open a terminal or command prompt.
2. Run the command:

   tnsping connect_identifier
       

   Example:

   tnsping orclpdb1
       

3. If successful, a message like "OK (20 msec)" will appear. If not, it indicates an issue with the network configuration or the TNS name.

4. Data Pump Utilities
Purpose: Tools such as `impdp` (Import Data Pump) and `expdp` (Export Data Pump) allow you to test connectivity during data import/export operations.

• Steps to Test Connectivity:
  • Run a test import/export operation with a valid connection string:

    expdp username/password@connect_identifier schemas=HR
            

5. Oracle SQL Developer
Purpose: A graphical tool used to test and manage database connections.
Steps to Test Connectivity:

1. Open SQL Developer.
2. Click on "New Connection."
3. Enter the connection details (username, password, host, port, service name).
4. Click "Test." If successful, a "Success" message will appear.

6. Database Control (DB Console or Enterprise Manager) Purpose: A web-based interface to manage and monitor the database, including testing client connections.
Steps to Test Connectivity:

1. Log into the Enterprise Manager web console.
2. Navigate to the database home page.
3. Use the "Performance" tab to monitor active sessions or test SQL queries.

7. Listener Control Utility (`lsnrctl`) Purpose: Used to check the status of the Oracle Net Listener, which facilitates client connections.
Steps to Test Connectivity:

1. Open a terminal or command prompt.
2. Run:

   lsnrctl status
       

3. Ensure that the listener is running and configured for the required service.

8. UTL_HTTP Package Purpose: For applications using HTTP-based APIs, this PL/SQL package can be used to test connectivity to external systems.
Example:

  BEGIN
    UTL_HTTP.REQUEST('http://example.com');
    DBMS_OUTPUT.PUT_LINE('Connection successful.');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Connection failed.');
  END;
  

9. Oracle Connection Manager (CMAN) Purpose: Used in environments with firewalls or to consolidate multiple database connections through a single gateway.

• Steps to Test Connectivity:
  • Configure CMAN and test by connecting through the CMAN service.

Tool	Purpose	Common Issues Identified
SQL*Plus	Test connectivity and query execution.	Login issues, invalid credentials, or connection timeouts.
Oracle Net Manager	Configure and test Oracle Net Services.	Misconfigured TNS entries or missing files.
tnsping	Validate TNS resolution.	Network or service resolution issues.
SQL Developer	Test connections via a GUI.	Incorrect credentials or service name issues.
lsnrctl	Check listener status and availability.	Listener not running or misconfigured.

Each tool serves specific purposes, allowing comprehensive testing and debugging of connectivity issues in Oracle 19c environments.

The TNSPING utility determines whether the listener for a service on an Oracle Net network can be reached successfully. If you can connect successfully from a client to a server (or a server to another server) using the TNSPING utility, then it displays an estimate of the round trip time (in milliseconds) it takes to reach the Oracle Net service. If it fails, then it displays a message describing the error that occurred.
This enables you to see the network error that is occurring without the overhead of a database connection.
Use the following command to test connectivity:

tnsping net_service_name count

In the preceding command, the following arguments are used:

1. net_service_name must exist in tnsnames.ora file or the name service in use, such as NIS.
2. count determines how many times the program attempts to reach the server. This argument is optional.

If the network service name specified is a database name, then TNSPING attempts to contact the corresponding listener. It does not actually determine whether the database is running. Use SQL*Plus to attempt a connection to the database.
The following are some examples of TNSPING: Invoke TNSPING for the display of the proper interface requirements.
Example 6-7 is an example of checking a listener for a database using a network service name of sales using the TNSPING
Example 6-7: Checking a Listener with TNSPING

TNSPING sales

This produces the following message:

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 21-NOV-2012
Copyright (c) 1997, 2012 Oracle Corporation. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = sales-server)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =
sales.us.example.com)))
OK (10 msec)

What do you do if the tnsnames.ora entry was made manually?
When you add a new TNS entry to your tnsnames.ora file, it is always a good idea to test the connectivity. Actually, you need to perform two tests:

1. Test connectivity to the server.
2. Test connectivity to the Oracle listener.

The following section discusses testing your connection.

Here’s a modernized version of the "Testing your connection to the Database" instructions, adapted for Oracle 11g R2 and current best practices:
The following instructions outline the steps to test database connectivity effectively in an Oracle 11g R2 environment. Step 1: Verify Network Connectivity

1. Open a Command Prompt or Terminal.
2. Ping the IP address of the remote host:

   ping <IP Address>
       

   Example:

   ping 192.168.1.100
       

   • If the ping is successful, the remote host is reachable.
   • If the ping fails, troubleshoot your network connection or contact your network administrator.
3. Test connectivity using the host name:

   ping <Host Name>
       

   Example:

   ping server.example.com
       

   • If successful, this confirms that DNS or the local hosts file is correctly resolving the host name to its IP address.
   • If it fails, verify the DNS configuration or check the hosts file (typically located at C:\Windows\System32\drivers\etc\hosts on Windows).

Step 2: Test Connectivity to the Oracle Listener

1. Use the tnsping utility to verify that the Oracle listener is reachable:

   tnsping <Service Name>
       

   Example:

   tnsping ORCL
       

   • If successful, tnsping will return the time taken to reach the database.
   • If it fails:
     • Verify that the Oracle listener is running on the remote server. You can do this by logging into the server and using the lsnrctl status command.
     • Check the tnsnames.ora configuration file (usually located at C:\app\<username>\product\11.2.0\dbhome_1\network\admin on Windows or $ORACLE_HOME/network/admin/tnsnames.ora on Unix/Linux). Ensure the entry for the database service matches the listener configuration.

Step 3: Test Database Connection Using SQL*Plus

1. Open SQL*Plus from the Command Prompt or Terminal:

   sqlplus
       

2. Connect to the database using a valid user ID, password, and service name:

   connect <username>/<password>@<service_name>
       

   Example:

   connect scott/tiger@ORCL
       

   • If successful, you will see the SQL prompt (SQL>).
   • If the connection fails:
     • Ensure that the username and password are correct.
     • Verify that the service name is correctly configured in the tnsnames.ora file.
     • Check the status of the database instance using the srvctl status database -d <dbname> command (if you have administrative access).

Step 4: Advanced Troubleshooting
If issues persist:

• Confirm that the Oracle listener configuration (listener.ora) on the server is correct.
• Check the database alert logs and listener logs for error messages.
• Test using tools such as Oracle SQL Developer or an equivalent database client for GUI-based connectivity testing.

These steps ensure a more modern and streamlined approach, leveraging features and tools relevant to Oracle 11g R2 while adhering to current practices.

Oracle Corporation introduced interdatabase connectivity with SQL*Net in Oracle Version 5 and simplified its usage considerably with the database links feature in Oracle Version 6, opening up a world of distributed possibilities. Oracle now supplies a variety of techniques that you can use to establish interdatabase connectivity and data sharing. Each technique has its advantages and disadvantages, but in many cases the best solution is not immediately obvious. The next lesson concludes this module.