Troubleshooting Oracle Database connectivity issues

Consult this topic to troubleshoot issues that you might encounter while validating connectivity between the Oracle Enterprise Database and PATROL for Oracle Enterprise KM by using the ENV Checker utility. The ENV Checker is a utility used to validate and troubleshoot database connectivity between PATROL for Oracle Enterprise Knowledge Module (KM) and an Oracle Enterprise Database. 

The ENV Checker utility verifies whether the PATROL Oracle Enterprise KM can successfully connect to an Oracle database by checking:

• Network connectivity (host and port)
• Database identification (SID or service)
• Authentication (username and password)
• Authorization (SYSDBA, SYSDG, or NON-SYS privileges)
• Ability to execute required SQL queries

Oracle Enterprise Database KM fails to connect to the Oracle Enterprise Database

Symptom

Oracle Enterprise Database monitoring does not start, fails intermittently, or shows connection-related errors during configuration or runtime.

Scope

This issue can occur because of the following scenarios;

• Connectivity issues exist between the Oracle Enterprise Database KM and the Oracle Database.

• Incorrect Oracle database host, port, instance, or credentials are provided during configuration.

• The database user does not have permission to execute the required queries, even though authentication succeeds.

To run env checker utility

1. Navigate to the PATROL for Oracle Enterprise KM directory under the PATROL installation path.
   For example: <PATROL_HOME>/Patrol3/KOEKM/

2. Open a terminal or command prompt from the <PATROL_HOME>/Patrol3/KOEKM/ directory.

3. Execute the Env checker by running the following command:

   • Windows: envchecker.bat

   • Linux: /envchecker.sh

4. Provide the following inputs when prompted:

   • Host name or IP address
   • Port
   • Instance: pmry 
   • Connection Type (Type 0 for service, 1 for SID):
   • Username (Oracle Database)
   • Password (Oracle Database)
5. When prompted for the Connect as (Type 1 for SYSDBA, 2 for SYSDG, 3 for NON SYS) option, specify one of the following privilege levels for the database connection:
   • Type 1 (SYSDBA):  Administrative privileges 
   • Type 2 (SYSDG):  SYSDG privileges.
   • Type 3 (NON SYS): Standard database user
     Make sure that the user has sufficient permissions to execute required queries.

6. (Optional) When prompted for the Do you want to execute custom query (Type 1 Yes, 2 No) option, perform one of the following actions:

   • Select 1 (Yes) to enter and execute your own SQL query.

   • Select 2 (No) to execute the default query provided in the utility.

7. If the query execution is successful, perform the following actions:

   • Verify that the connection is successful.

   • Verify that the database connectivity and credentials are valid.
     If the database connectivity and credentials are valid, the issue might be related to the Oracle Enterprise Database KM configuration. 

   • If the utility reports errors, perform the following actions:

     1. Review the error message displayed in the terminal.

     2. Verify the host, port, SID/service, and credentials.

     3. Confirm the required privileges for the database user.

8. After resolving the reported issues, run the ENV Checker utility again to validate connectivity.