Resolve tns alias from the database (DBMS_TNS.RESOLVE_TNSNAME)

When a given tns alias is not working, via SQL*Plus, database link or within data guard, traditionally I troubleshoot connectivity via tnsping utility, at first just to understand the target host, port and service name.

In oracle 12.2 we have a new function called DBMS_TNS.RESOLVE_TNSNAME that enables to query the return string as we receive it from tnsping.

Using oracle utility tnsping from the command line

$ tnsping stndby_db

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 13-NOV-2017 14:45:39

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stndby_host)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stndby_db)))
OK (0 msec)

Using dbms_tns

SQL> select dbms_tns.resolve_tnsname ('stndby_db') as TNS_ALIAS from dual;
TNS_ALIAS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=stndby_host)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stndby_db)(CID=(PROGRAM=oracle)(HOST=prmry_host)(USER=oracle))))

Extracting service name from a tns alias

SQL> col SERVICE for a15
SQL> SELECT REGEXP_REPLACE (
                        dbms_tns.resolve_tnsname ( 'stndby_db' )
,                       '(.*)(SERVICE_NAME|SID_NAME)[[:blank:]]*=[[:blank:]]*([^\)]+*)\).*'
,                       '\3'
,                       1
,                       1
,                       'i'
                       )
           AS SERVICE
 10    FROM DUAL;

SERVICE
---------------
stndby_db

Extracting host name from a tns alias

SQL> col HOST for a15
SELECT REGEXP_REPLACE (
                        dbms_tns.resolve_tnsname ( 'stndby_db' )
,                       '(.*)(HOST)[[:blank:]]*=[[:blank:]]*([^\)]+*)\).*'
,                       '\3'
,                       1
,                       1
,                       'i'
                       )
           AS HOST
 10       FROM DUAL;

HOST
---------------
stndby_host

Extracting port from a tns alias

SQL> col PORT for a10
SELECT REGEXP_REPLACE (
                        dbms_tns.resolve_tnsname ( 'stndby_db' )
,                       '(.*)(PORT)[[:blank:]]*=[[:blank:]]*([^\)]+*)\).*'
,                       '\3'
,                       1
,                       1
,                       'i'
                       )
           AS HOST
 10       FROM DUAL;

PORT
---------------
1521

Extracting target information of a standby

SQL> col TARGET_HOST for a15 heading "TARGET|HOST" 
SQL> col TARGET_PORT for a15 heading "TARGET|PORT"
SQL> col TARGET_SERVICE_NAME for a15 heading "TARGET|SERVICE|NAME"
SQL> SELECT dest_name,db_unique_name
,REGEXP_REPLACE ( dbms_tns.resolve_tnsname ( db_unique_name ), '(.*)(HOST)[[:blank:]]*=[[:blank:]]*([^\)]+*)\).*', '\3', 1, 1, 'i' ) AS TARGET_HOST
,REGEXP_REPLACE ( dbms_tns.resolve_tnsname ( db_unique_name ), '(.*)(PORT)[[:blank:]]*=[[:blank:]]*([^\)]+*)\).*', '\3', 1, 1, 'i' ) AS TARGET_PORT
,REGEXP_REPLACE ( dbms_tns.resolve_tnsname ( db_unique_name ), '(.*)(SERVICE_NAME|SID_NAME)[[:blank:]]*=[[:blank:]]*([^\)]+*)\).*', '\3', 1, 1, 'i' ) AS TARGET_SERVICE_NAME
FROM gv$archive_dest WHERE destination IS NOT NULL and TARGET not in ('LOCAL','PRIMARY');

     DB      TARGET
DEST      UNIQUE  TARGET      TARGET      SERVICE
NAME      NAME    HOST      PORT      NAME
-------------------- ------- --------------- --------------- ---------------
LOG_ARCHIVE_DEST_2   stndby_db prmry_host      1521      stndby_db