DBA Hub

📋Steps in this guide1/3

DBMS_TNS package for tnsping in database Oracle 12.2 DBACLASS

DBMS_TNS package for tnsping in database Oracle 12.2 provides the RESOLVE_TNSNAME function to resolve a TNS name and return the corresponding Oracle Net8 .

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

DBMS_TNS package provides the RESOLVE_TNSNAME function to resolve a TNS name and return the corresponding Oracle Net8 connection string.When you connect locally to the server (no SQL*Net, no listener), the Oracle session inherits the client environment.   It is available on oracle 12.2 Release . The DBMS_TNS package contains one function, RESOLVE_TNSNAME, which returns the resolved connect string from configured sources that have been defined in the names.directory_path parameter in the sqlnet.ora file. you can run it when connected to the database to see how the name is resolved

Code/Command (click line numbers to comment):

1
2
3
4
5
SQL> desc dbms_tns
FUNCTION RESOLVE_TNSNAME RETURNS VARCHAR2
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
TNS_NAME                       VARCHAR2                IN
2

Section 2

When we switch the container inside the CDB then it will show only container tns entry. If we want to check all database links are going

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
[oracle@Primary ~]$ sqlplus system@pdb1

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('pdb1') from dual

DBMS_TNS.RESOLVE_TNSNAME('PDB1')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1.aeetisalat12345.oraclecloud.internal)(CID=
(PROGRAM=oracle)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(USER=oracle))))

[oracle@Primary ~]$ sqlplus / as sysdba

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED                       READ ONLY  NO
3 PDB1                           READ WRITE NO
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('ORCL') from dual

DBMS_TNS.RESOLVE_TNSNAME('ORCL')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL.aeetisalat12345.oraclecloud.internal)(CID=
(PROGRAM=oracle)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(USER=oracle))))

SQL>  alter session set container=pdb1;

Session altered.

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1                           READ WRITE NO

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('ORCL') from dual

DBMS_TNS.RESOLVE_TNSNAME('ORCL')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL.aeetisalat12345.oraclecloud.internal)(CID=
(PROGRAM=oracle)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(USER=oracle))))
3

Section 3

Code/Command (click line numbers to comment):

1
2
3
4
5
6
SELECT DB_LINK, DBMS_TNS.RESOLVE_TNSNAME(HOST) FROM DBA_DB_LINKS;

DB_LINK          DBMS_TNS.RESOLVE_TNSNAME(HOST)
--------------  --------------------------------------------------------------------------------
PRIM            (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL.aeetisalat12345.oraclecloud.internal)(CID=
(PROGRAM=oracle)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(USER=oracle))))

Comments (0)

Please to add comments

No comments yet. Be the first to comment!