Durham Oracle

For Oracle database TESTAPPS

Bit of background :

http://www.orafaq.com/wiki/JDBC

http://www.dba-oracle.com/t_oracle_sid_instance_name_service_name.htm

** ora1, ora2, ora3 and ora4 redacted server names -

Thin driver

Oracle's JDBC Thin driver uses Java sockets to connect directly to Oracle.

It provides its own TCP/IP version of Oracle's SQL*Net protocol.

Because it is 100% Java, this driver is platform independent and can also run from a Web Browser (applets).

Oracle is replacing identifying databases by the SID with the new services approach. There is a third approach, TNSNames, which can be used if configured in the file tnsnames.ora correctly.

The Thin driver offers these kinds of URL formats for all of them:

SID (no longer recommended by Oracle to be used):

jdbc:oracle:thin:[<user>/<password>]@<host>[:<port>]:<SID>

Services:

jdbc:oracle:thin:[<user>/<password>]@//<host>[:<port>]/<service>

TNSNames:

jdbc:oracle:thin:[<user>/<password>]@<TNSName>

Also -

https://stackoverflow.com/questions/31570461/unable-to-connect-to-connect-to-oracle-12c-with-sid-but-service-name-works

12c and service names -

You're in a multitenant environment.

You should always use the SERVICE name when connecting to a 12c instance.

SID in a multitenant will always take you to the container database.

The pluggables running in the container will only be reachable by their service name.

Remember databases use self registration so listener will know about each database regardless.

When connection to ncs - the services works but not the SID as would be expected as on 12c.

(user status.java script throughout this changing the connection details to test the jdbc connection string)

[oracle@ORA01 admin]$ srvctl status database -db umb -v

Instance umbapps1 is running on node ora01 with online services oam,oms,prodcat,umb. Instance status: Open.

Instance umb2 is running on node ora02 with online services cch,cdcs,cmc,cng,cxs,ec,frm,ncs,nis,oms,pal,prodcat,umb2. Instance status: Open.

Instance umbapps3 is running on node ora03 with online services cch,cdcs,cmc,cng,cxs,ec,frm,ncs,nis,oam,pal,umb3. Instance status: Open.

Instance umbapps4 is running on node ora04 with online services cch,cdcs,cmc,cng,cxs,ec,frm,ncs,nis,oam,oms,pal,prodcat,umb4. Instance status: Open.

[oracle@ORA01 test_cub]$ sqlplus GUEST/.......@ora-scan:1521/ncs

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 8 04:43:34 2018

Last Successful login time: Wed Aug 08 2018 04:01:15 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

GUEST@ora-scan:1521/ncs

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Using SID

[oracle@ORA01 test_cub]$ sqlplus GUEST/……………….@ora-scan:1521:ncs

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 8 04:43:44 2018

ERROR:

ORA-12545: Connect failed because target host or object does not exist

To list full details about each service :

--check cardinality

oracle@ORA01 ~]$ crsctl status resource ora.umb.cch.svc -f |grep -i car

CARDINALITY=4
CARDINALITY_ID=0

[oracle@ORA01 test_cub]$ srvctl config service -d umbapps -s ncs

Service name: ncs

Server pool:

Cardinality: 3

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type:

Failover method:

TAF failover retries:

TAF failover delay:

Failover restore: NONE

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Pluggable database name:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Drain timeout:

Stop option:

Session State Consistency: DYNAMIC

GSM Flags: 0

Service is enabled

Preferred instances: umb2,umb3,umb4

Available instances: umb1

CSS critical: no

So the connection using services i.e. ncs works

Test connection using java connection string

[oracle@ORA01 test_cub]$ /home/oracle/test_cub status

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

PL/SQL Release 12.2.0.1.0 - Production

CORE 12.2.0.1.0 Production

TNS for Linux: Version 12.2.0.1.0 - Production

NLSRTL Version 12.2.0.1.0 - Production

Global Name

UMBAPPS

Test JDBC connection

Connection using - jdbc:oracle:thin:@ora-scan:1521/ncs

but not the services jdbc:oracle:thin:@ora-scan:1521:ncs as would be expected.

[oracle@ORA01 test_cub]$ test_cub status

Exception in thread "main" java.sql.SQLException: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:774)

at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)

at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:247)

at status.main(status.java:17)

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:271)

at oracle.net.ns.NSProtocol.connect(NSProtocol.java:317)

at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1438)

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:518)

... 6 more

No tnsnames services for ncs

[oracle@ORA01 admin]$ pwd

/u01/app/oracle/product/12.2.0/dbhome/network/admin

[oracle@ORA01 admin]$ ls -la | grep tns

-rw-------. 1 oracle oinstall 916 Apr 3 23:30 tnsnames_abp.ora

-rw-------. 1 oracle oinstall 916 Apr 3 23:34 tnsnames_cch.ora

-rw-------. 1 oracle oinstall 916 Apr 3 22:19 tnsnames_cmc.ora

.....(cut for brevity)

i.e.

[oracle@ORA01 admin]$ sqlplus GUEST/………..@ncs

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 8 05:01:37 2018

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

For ODS database

srvctl status database -db ods –v

Instance ods1 is running on node ora01 with online services ods_1. Instance status: Open.

Instance ods2 is running on node ora02 with online services ods_2. Instance status: Open.

Instance ods3 is running on node ora03 with online services ods_3. Instance status: Open.

Instance ods4 is running on node ora04 with online services ods_4. Instance status: Open.

The service connection and the tnsnames connection both work.

[oracle@ORA01 test_cub]$ test_cub status

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

PL/SQL Release 12.2.0.1.0 - Production

CORE 12.2.0.1.0 Production

TNS for Linux: Version 12.2.0.1.0 - Production

NLSRTL Version 12.2.0.1.0 - Production

Global Name

ODS

Test JDBC connection

Connection using - jdbc:oracle:thin:@ora-scan:1521:ods

[oracle@ORA01 admin]$ sqlplus edw/……..@ods

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 8 04:56:20 2018

Last Successful login time: Wed Aug 08 2018 04:53:03 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

We could do this to get the SID to work :

http://oracleinaction.com/12c-use_sid_as_service_listener_name/

Durham Oracle

Oracle database connection background info

Getting latest RAC version update