Verifying and Troubleshooting Oracle Database SCAN Based Connect Strings Configured in a Delphix Virtualization Engine (KBA1643)

o Gather the cluster nodes that are a member of the cluster.

The Oracle Cluster in this example is a 2 Node cluster as seen from the Oracle Supplied olsnodes command which displays all cluster nodes active within the Cluster. The node list is required during later diagnostics collection and troubleshooting steps.

[oracle@oelc8n1 ~]$ su - grid
Password: 
Last login: Mon Nov 6 09:48:52 AEDT 2017 on pts/0
[grid@oelc8n1 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/grid

[grid@oelc8n1 ~]$ olsnodes
oelc8n1
oelc8n2

o Ensure that the SCAN, Single Client Access Name is being resolved correctly by the DNS. 

A typical SCAN configuration will resolve the name to 3 IP Addresses. It does not have to be three addresses and one, two or three addresses is also acceptable however to take full advantage of SCAN functionality the DNS resolving to 3 addresses is recommended by Oracle.

The DNS resolving to 3 IP addresses means there will be 3 SCAN-VIP's plumbed to nodes that are members of the cluster and 3 SCAN Listeners running across the cluster members. This is described in more detail in the Oracle Documentation.

[oracle@oelc8n1 ~]$ nslookup oelc8scan

Server:     192.168.0.111
Address:    192.168.0.111#53

Name:  oelc8scan.plb.internal
Address: 192.168.0.163
Name:  oelc8scan.plb.internal
Address: 192.168.0.168
Name:  oelc8scan.plb.internal
Address: 192.168.0.169


[oracle@oelc8n1 ~]$ nslookup oelc8scan.plb.internal

Server:     192.168.0.111
Address:    192.168.0.111#53

Name:  oelc8scan.plb.internal
Address: 192.168.0.168
Name:  oelc8scan.plb.internal
Address: 192.168.0.169
Name:  oelc8scan.plb.internal
Address: 192.168.0.163

The DNS lookup here is resolving to 3 IP addresses, each will be linked to one of the cluster node as a SCAN VIP (Virtual IP). The Clusterware itself manages these VIP resources and chooses which node they will be linked to. As there are 3 SCAN VIP's there will be 3 SCAN Listeners running within this cluster.

o Obtain the list of SCAN VIP's present across the cluster nodes.

Logging in as the Oracle Clusterware owner (typically the "grid" user) and setting the environment up for the Clusterware home the configuration of the SCAN can be examined from the Oracle clusterware point of view.

[oracle@oelc8n1 ~]$ su - grid
Password: 
Last login: Mon Nov 6 09:39:10 AEDT 2017 on pts/0

[grid@oelc8n1 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/grid

[grid@oelc8n1 ~]$ srvctl config scan

SCAN name: oelc8scan.plb.internal, Network: 1
Subnet IPv4: 192.168.0.0/255.255.255.0/enp0s3, static
Subnet IPv6: 
SCAN 0 IPv4 VIP: 192.168.0.163
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 1 IPv4 VIP: 192.168.0.168
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 2 IPv4 VIP: 192.168.0.169
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 

o Collect the SCAN Listener configuration and node that each SCAN listener is running from.

Running the srvctl command as the Oracle Clusterware home owner the number of scan listeners, the port they are accepting connections from and the node each is running against can be determined. 

[grid@oelc8n1 ~]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
Registration invited nodes: 
Registration invited subnets: 
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes: 
SCAN Listener is individually disabled on nodes: 
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
Registration invited nodes: 
Registration invited subnets: 
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes: 
SCAN Listener is individually disabled on nodes: 
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
Registration invited nodes: 
Registration invited subnets: 
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes: 
SCAN Listener is individually disabled on nodes: 

[grid@oelc8n1 ~]$ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node oelc8n2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node oelc8n1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node oelc8n1

o Collect the services running against each SCAN Listener.

From node oelc8n1 where the SCAN listeners listener_scan2 and listener_scan3are running execute lsnrctl as the owner of the listener. In an Oracle Cluster this is the Clusterware home owner. Make sure the service that Delphix is attempting to connect through is registered in the SCAN listeners and each instance associated with the RAC database is listed as READY.

[grid@oelc8n1 ~]$ lsnrctl services listener_scan2

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-NOV-2017 09:49:42
Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
Services Summary...
Service "orcl112" has 2 instance(s).
 Instance "orcl1121", status READY, has 1 handler(s) for this service...
  Handler(s):
   "DEDICATED" established:0 refused:0 state:ready
     REMOTE SERVER
     (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.164)(PORT=1521))
 Instance "orcl1122", status READY, has 1 handler(s) for this service...
  Handler(s):
   "DEDICATED" established:0 refused:0 state:ready
     REMOTE SERVER
     (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.165)(PORT=1521))
Service "orcl112XDB" has 2 instance(s).
 Instance "orcl1121", status READY, has 1 handler(s) for this service...
  Handler(s):
   "D000" established:0 refused:0 current:0 max:1022 state:ready
     DISPATCHER <machine: oelc8n1.plb.internal, pid: 4187>
     (ADDRESS=(PROTOCOL=tcp)(HOST=oelc8n1.plb.internal)(PORT=18055))
 Instance "orcl1122", status READY, has 1 handler(s) for this service...
  Handler(s):
   "D000" established:0 refused:0 current:0 max:1022 state:ready
     DISPATCHER <machine: oelc8n2.plb.internal, pid: 4083>
     (ADDRESS=(PROTOCOL=tcp)(HOST=oelc8n2.plb.internal)(PORT=24638))
The command completed successfully

[grid@oelc8n1 ~]$ lsnrctl services listener_scan3

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-NOV-2017 09:49:46
Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
Services Summary...
Service "orcl112" has 2 instance(s).
 Instance "orcl1121", status READY, has 1 handler(s) for this service...
  Handler(s):
   "DEDICATED" established:0 refused:0 state:ready
     REMOTE SERVER
     (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.164)(PORT=1521))
 Instance "orcl1122", status READY, has 1 handler(s) for this service...
  Handler(s):
   "DEDICATED" established:0 refused:0 state:ready
     REMOTE SERVER
     (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.165)(PORT=1521))
Service "orcl112XDB" has 2 instance(s).
 Instance "orcl1121", status READY, has 1 handler(s) for this service...
  Handler(s):
   "D000" established:0 refused:0 current:0 max:1022 state:ready
     DISPATCHER <machine: oelc8n1.plb.internal, pid: 4187>
     (ADDRESS=(PROTOCOL=tcp)(HOST=oelc8n1.plb.internal)(PORT=18055))
 Instance "orcl1122", status READY, has 1 handler(s) for this service...
  Handler(s):
   "D000" established:0 refused:0 current:0 max:1022 state:ready
     DISPATCHER <machine: oelc8n2.plb.internal, pid: 4083>
     (ADDRESS=(PROTOCOL=tcp)(HOST=oelc8n2.plb.internal)(PORT=24638))
The command completed successfully

From node oelc8n2 where the SCAN listeners listener_scan1 is running execute lsnrctl services.

[grid@oelc8n2 ~]$ lsnrctl services listener_scan1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-NOV-2017 09:57:48
Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "orcl112" has 2 instance(s).
 Instance "orcl1121", status READY, has 1 handler(s) for this service...
  Handler(s):
   "DEDICATED" established:0 refused:0 state:ready
     REMOTE SERVER
     (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.164)(PORT=1521))
 Instance "orcl1122", status READY, has 1 handler(s) for this service...
  Handler(s):
   "DEDICATED" established:0 refused:0 state:ready
     REMOTE SERVER
     (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.165)(PORT=1521))
Service "orcl112XDB" has 2 instance(s).
 Instance "orcl1121", status READY, has 1 handler(s) for this service...
  Handler(s):
   "D000" established:0 refused:0 current:0 max:1022 state:ready
     DISPATCHER <machine: oelc8n1.plb.internal, pid: 4187>
     (ADDRESS=(PROTOCOL=tcp)(HOST=oelc8n1.plb.internal)(PORT=18055))
 Instance "orcl1122", status READY, has 1 handler(s) for this service...
  Handler(s):
   "D000" established:0 refused:0 current:0 max:1022 state:ready
     DISPATCHER <machine: oelc8n2.plb.internal, pid: 4083>
     (ADDRESS=(PROTOCOL=tcp)(HOST=oelc8n2.plb.internal)(PORT=24638))
The command completed successfully

Each SCAN listener must have the service for the database registered (in this case orcl112) within it along with the database instances (orcl1121 and orcl1122) that that are registering the service and accepting connections through the service.

o Ensure the service is also registered and present within each node listener.

From the first node oelc8n1 as the owner of the node listener which could be the Oracle Home owner or the Clusterware Home owner run lsnrctl services against the node listener. The assistance of the sites DBA may be required to identify its name if it is not the default name of listener. Ensure the service set in the Delphix connect string is registered in each of the node listeners.

[oracle@oelc8n1 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-NOV-2017 15:13:56
Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
 Instance "+ASM1", status READY, has 1 handler(s) for this service...
  Handler(s):
   "DEDICATED" established:0 refused:0 state:ready
     LOCAL SERVER
Service "orcl112" has 1 instance(s).
 Instance "orcl1121", status READY, has 1 handler(s) for this service...
  Handler(s):
   "DEDICATED" established:1 refused:0 state:ready
     LOCAL SERVER
Service "orcl112XDB" has 1 instance(s).
 Instance "orcl1121", status READY, has 1 handler(s) for this service...
  Handler(s):
   "D000" established:0 refused:0 current:0 max:1022 state:ready
     DISPATCHER <machine: oelc8n1.plb.internal, pid: 4187>
     (ADDRESS=(PROTOCOL=tcp)(HOST=oelc8n1.plb.internal)(PORT=18055))
The command completed successfully

 From the second cluster node ensure the service in the Delphix connect string is registered in the node listener.

[grid@oelc8n2 ~]$ [grid@oelc8n2 ~]$ lsnrctl services listener

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-NOV-2017 09:57:54
Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "+ASM" has 1 instance(s).
 Instance "+ASM2", status READY, has 1 handler(s) for this service...
  Handler(s):
   "DEDICATED" established:0 refused:0 state:ready
     LOCAL SERVER
Service "orcl112" has 1 instance(s).
 Instance "orcl1122", status READY, has 1 handler(s) for this service...
  Handler(s):
   "DEDICATED" established:0 refused:0 state:ready
     LOCAL SERVER
Service "orcl112XDB" has 1 instance(s).
 Instance "orcl1122", status READY, has 1 handler(s) for this service...
  Handler(s):
   "D000" established:0 refused:0 current:0 max:1022 state:ready
     DISPATCHER <machine: oelc8n2.plb.internal, pid: 4083>
     (ADDRESS=(PROTOCOL=tcp)(HOST=oelc8n2.plb.internal)(PORT=24638))
The command completed successfully

o Using SQLPlus test a connection using the connect string Delphix uses.

Delphix requires connection be established to the target database for a number of its operations including snapshots and SnapSync. After completing the verification of SCAN, SCAN Listeners and node listeners performing a test log in can be performed using the same JDBC connect string as Delphix uses.

The connect string that may be having an issue for the database in Delphix might look like the following barring different services, ports, hostnames etc. The sample connect string here is:

jdbc:oracle:thin:@(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)
(DESCRIPTION=(ENABLE=broken)(ADDRESS=(PROTOCOL=TCP)(HOST=oelc8scan.plb.internal)(PORT=1521))
(CONNECT_DATA=(UR=A)(SERVICE_NAME=orcl112))))

This same connect string can be tested using sqlplus from one of the cluster nodes. For other environments you would need to substitute the Delphix database user "delphix", the SCAN "oelc8scan.plb.internal", the service name "orcl112" and possibly the port number.

sqlplus delphix@'(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)
(DESCRIPTION=(ENABLE=broken)(ADDRESS=(PROTOCOL=TCP)(HOST=oelc8scan.plb.internal)(PORT=1521))
(CONNECT_DATA=(UR=A)(SERVICE_NAME=orcl112))))'

The connection can be tested from any one of the target environments cluster nodes or any node where 11.2 or higher of the SQLPlus client is installed.

[oracle@oelc8n1 ~]$ sqlplus delphix@'(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)
(DESCRIPTION=(ENABLE=broken)(ADDRESS=(PROTOCOL=TCP)(HOST=oelc8scan.plb.internal)(PORT=1521))
(CONNECT_DATA=(UR=A)(SERVICE_NAME=orcl112))))'

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 6 10:03:47 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password: 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select db_unique_name,database_role,open_mode from v$database;

DB_UNIQUE_NAME         DATABASE_ROLE  OPEN_MODE
------------------------------ ---------------- --------------------
orcl112            PRIMARY     READ WRITE

SQL> set lines 132
SQL> set pages 500
SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
orcl1121 oelc8n1.plb.internal

The alternate connect string as presented in the Delphix UI can be tested using the following format via Oracle easyconnect. 

[oracle@oelc8n1 ~]$ sqlplus delphix@\"oelc8scan.plb.internal:1521/orcl112\"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 6 16:14:46 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> show user
USER is "DELPHIX"
SQL> 

If the database service Delphix is attempting to connect through is not registered in the SCAN listeners correctly or not present in the node listeners from which the database instances are running then its likely connections will always fail or fail intermittently depending on which route a connection request takes at an Oracle level.

External Links

Oracle Clusterware SCAN Addresses and Client Connections

SRVCTL Command Reference