This was first published on https://blog.dbi-services.com/oracle-12cr2-plsql-new-feature-tnsping-from-the-database (2016-11-08)
Republishing here for new followers. The content is related to the the versions available at the publication date
Database links are resolved with the server TNS_ADMIN configuration (sqlnet.ora and tnsnames.ora). You can use tnsping to check the resolution, but it supposes that you are on the server and have set the same environment as the one which started the database. In 12.2 you have a new package to check that: DBMS_TNS. It’s the kind of little new features that make our life easier.
The easy way to verify a connection string is to use tnsping. Here is an example with an EZCONNECT resolution:
[oracle@SE122 ~]$ tnsping //10.196.234.38/CDB1.opcoct.oraclecloud.internal TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 08-NOV-2016 17:45:34 Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora Used EZCONNECT adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=CDB1.opcoct.oraclecloud.internal))(ADDRESS=(PROTOCOL=TCP)(HOST=10.196.234.38)(PORT=1521))) OK (0 msec)
The full connection description is displayed here before contacting the listener.
This resolution is valid only with a specific TNS configuration (which is here /u01/app/oracle/product/12.2.0/dbhome_1/network/admin). However, you may have different configurations (using the TNS_ADMIN environment variable) and if it’s not set consistently, you may have different results. Basically:
So here is this new package:
SQL> desc dbms_tns FUNCTION RESOLVE_TNSNAME RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TNS_NAME VARCHAR2 IN
And you can run it when connected to the database to see how the name is resolved:
SQL> select dbms_tns.resolve_tnsname('&_connect_identifier') from dual; old 1: select dbms_tns.resolve_tnsname('&_connect_identifier') from dual new 1: select dbms_tns.resolve_tnsname('//10.196.234.38/CDB1.opcoct.oraclecloud.internal') from dual DBMS_TNS.RESOLVE_TNSNAME('//10.196.234.38/CDB1.OPCOCT.ORACLECLOUD.INTERNAL') ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=CDB1.opcoct.oraclecloud.internal)(CID=(PROGRAM=oracle)(HOST=SE122.compute-opcoct.oraclecloud.internal)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP)(HOST=10.196.234.38)(PORT=1521)))
The resolution is done without attempting to contact the listener. This ip address do not exist on my network:
select dbms_tns.resolve_tnsname('//10.1.1.1/XX') from dual; DBMS_TNS.RESOLVE_TNSNAME('//10.1.1.1/XX') ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=XX)(CID=(PROGRAM=oracle)(HOST=SE122.compute-opcoct.oraclecloud.internal)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521)))As you can see, the client identification is send here (PROGRAM and HOST).
I’ll use this new feature to prove my assumption above about which environment is used when connecting locally or through dynamic or static service.
I create 3 directories with different names for the SERVICE_NAME in order to see which one is used:
mkdir -p /tmp/tns_lsnr ; echo "NAMES.DIRECTORY_PATH=TNSNAMES" > /tmp/tns_lsnr/sqlnet.ora ; echo "XXX=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=from_lsnr))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))" > /tmp/tns_lsnr/tnsnames.ora mkdir -p /tmp/tns_sess ; echo "NAMES.DIRECTORY_PATH=TNSNAMES" > /tmp/tns_sess/sqlnet.ora ; echo "XXX=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=from_sess))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))" > /tmp/tns_sess/tnsnames.ora mkdir -p /tmp/tns_inst; echo "NAMES.DIRECTORY_PATH=TNSNAMES" > /tmp/tns_inst/sqlnet.ora ; echo "XXX=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=from_inst))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))" > /tmp/tns_inst/tnsnames.ora
In addition, I’ll need a listener configuration with a static service, let’s call it STATIC:
cat > /tmp/tns_lsnr/listener.ora <<END LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$HOSTNAME)(PORT=1521)))) SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(ORACLE_HOME=$ORACLE_HOME)(GLOBAL_DBNAME=STATIC)(SID_NAME=CDB1))) END
Here’s a summary of the different configurations:
$ tail /tmp/tns*/* ==> /tmp/tns_inst/sqlnet.ora <== NAMES.DIRECTORY_PATH=TNSNAMES ====> /tmp/tns_inst/tnsnames.ora <== XXX=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=from_inst))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) ====> /tmp/tns_lsnr/listener.ora <== LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SE122.compute-opcoct.oraclecloud.internal)(PORT=1521)))) SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(ORACLE_HOME=/u01/app/oracle/product/122EE)(GLOBAL_DBNAME=STATIC)(SID_NAME=CDB1))) ====> /tmp/tns_lsnr/sqlnet.ora <== NAMES.DIRECTORY_PATH=TNSNAMES ====> /tmp/tns_lsnr/tnsnames.ora <== XXX=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=from_lsnr))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) ====> /tmp/tns_sess/sqlnet.ora <== NAMES.DIRECTORY_PATH=TNSNAMES ====> /tmp/tns_sess/tnsnames.ora <== XXX=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=from_sess))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
I start the listener and the instance with their own environment, and set the session one to another:
export TNS_ADMIN=/tmp/tns_lsnr ; lsnrctl start export TNS_ADMIN=/tmp/tns_inst ; sqlplus / as sysdba <<< startup export TNS_ADMIN=/tmp/tns_sess
Now it’s time to use this new DBMS_TNS when connecting locally, through the dynamic service (CDB1) and through the static service (STATIC):
SQL> connect system/oracle Connected. SQL> select dbms_tns.resolve_tnsname('XXX') from dual; DBMS_TNS.RESOLVE_TNSNAME('XXX') ----------------------------------------------------------------------------------------------------------------------------------------------------------- (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=from_sess)(CID=(PROGRAM=oracle)(HOST=SE122.compute-opcoct.oraclecloud.internal)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))When connected locally the TNS_ADMIN from my shell environment running sqlplus is used.
SQL> connect system/oracle@//localhost/CDB1 Connected. SQL> select dbms_tns.resolve_tnsname('XXX') from dual; DBMS_TNS.RESOLVE_TNSNAME('XXX') ----------------------------------------------------------------------------------------------------------------------------------------------------------- (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=from_inst)(CID=(PROGRAM=oracle)(HOST=SE122.compute-opcoct.oraclecloud.internal)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))When connected to dynamic service, the TNS_ADMIN used to startup the instance is used.
SQL> connect system/oracle@//localhost/STATIC Connected. SQL> select dbms_tns.resolve_tnsname('XXX') from dual; DBMS_TNS.RESOLVE_TNSNAME('XXX') ----------------------------------------------------------------------------------------------------------------------------------------------------------- (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=from_lsnr)(CID=(PROGRAM=oracle)(HOST=SE122.compute-opcoct.oraclecloud.internal)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))When connected to static service, the TNS_ADMIN used to startup the listener is used.
You should use a consistent environment setting in order to be sure that all sessions will use the same name resolution. But if you have a doubt about it, DBMS_TNS can help to troubleshoot. It’s better than DBMS_SYSTEM.GET_ENV as it does the name resolution rather than just showing the environment variables.
Want to know quickly where all database links are going? Here it is:
SQL> select username,dbms_tns.resolve_tnsname(host) from cdb_db_links;