Skip to main content
Delphix

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 .

  • Was this article helpful?