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; ...