Oracle Listeners, JDBC Connect Strings, ORACLE SID and Delphix Connections (KBA1352)
Applicable Delphix Engine Versions
- 4.0.x
- 4.1.x
- 4.2.x
- 4.3.x
- 5.0.x
- 5.1.x
Issue
Delphix relies on Oracle databases registering their services within Node and SCAN listeners successfully. The names of the instances and services that registered in Oracle listeners are used to build the connect strings that Delphix uses to establish connections from Delphix to both dSources and VDB's to perform operations like capturing snaphots and checking the health of these database resources.
The aim of this article is to explore how the parameters set within an Oracle Database impact what is registered in Oracle listeners, what is registered in Delphix through environment discovery and environment refresh operations and connectivity between Delphix and the Oracle RDBMS.
This article covers Single Instance Oracle Databases.
Should changes be made to database parameters impacting the service and instance names registered within a listener these changes can cause connectivity problems from the Delphix side if Delphix is not made aware of these changes through an environment refresh or via manual adjustment to the JDBC connect strings held by Delphix.
Troubleshooting
The first place to start troubleshooting a connection issue is to look at what the database is doing from a listener service registration point of view. This involves investigating the database parameters associated with the name of the service(s) and those controlling listener registration.
Oracle Single Instance Configuration
The database in the example has a DB name of cdb12w, db_unique_name of cdb12w and instance_name
Database parameters impacting service registration
o The databases configuration
SQL> show parameter uniq NAME TYPE VALUE ----------------------------------------------------------------------------- db_unique_name string cdb12w SQL> show parameter domain NAME TYPE VALUE ----------------------------------------------------------------------------- db_domain string SQL> show parameter service NAME TYPE VALUE ----------------------------------------------------------------------------- service_names string cdb12w SQL> show parameter local_listener NAME TYPE VALUE ----------------------------------------------------------------------------- local_listener string (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oelvbc1n1.plb.internal)(PORT=1541)))
o The node listener where this database is registering its services needs to be examined to ensure that the services presented by the database are being registered by the PMON background process successfully against the target listener.
[oracle@oelvbc1n1 ~]$ lsnrctl status listener_12c LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-DEC-2016 11:13:43 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oelvbc1n1.plb.internal)(PORT=1541))) STATUS of the LISTENER ------------------------ Alias listener_12c Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 07-DEC-2016 11:10:22 Uptime 0 days 0 hr. 3 min. 21 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oelvbc1n1/listener_12c/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oelvbc1n1.plb.internal)(PORT=1541))) Services Summary... Service "cdb12w" has 1 instance(s). Instance "cdb12w", status READY, has 1 handler(s) for this service... Service "cdb12wXDB" has 1 instance(s). Instance "cdb12w", status READY, has 1 handler(s) for this service... Service "pdb1w" has 1 instance(s). Instance "cdb12w", status READY, has 1 handler(s) for this service... The command completed successfully
Under the Services Summary section of the listener status command each Service presented to the listener is registered along with the Instance that can be used to attach to the database running that specific service.
o On the Delphix side environment discovery will build the configuration and connect string to look as follows
Note: As this is a single instance environment the jdbc connect string built using the SID (":ORACLE_SID") format rather than a service name ("/SERVICE_NAMES") as it would in a RAC environment.
Resolution
In order for Delphix to successfully establish a connection in a single instance environment as the SID format is set in the connect string, the ORACLE_SID the Instance Name registered in the listener must exist .