ANBOB™

数据库中的dblink创建时如果使用了tnsnames.ora中的别名，那么在日后梳理数据库中所有的DBLINK 与哪些主机的数据库有连接时？通过DBA_DB_DBLINKS视图可能无法直接取到IP地址， 如修改数据库用户名或迁移数据库主机变更IP时就需要那么做。通常需要登录数据库主机使用tnsping 别名的方式来解析IP，如果一个数据库中的DBLINK很多就需要更多的时间去梳理。在几年前我面临这个需求时自己写过一个shell脚本来简化操作How to list all db links in oracle DB to generate a flat file? (生成dblink列表文件)， 但是如果当时创建dblink时在session级使用了非默认的tnsnames.ora如指定了TNS_ADMIN，查找对应的tnsnames.ora和解析工作变的更佳复杂。所幸在oracle 12.2版本中提供了新的package DBMS_TNS, 可以不登录主机（终端）在数据库内部就可以解析。

SQL> desc DBMS_TNS
FUNCTION RESOLVE_TNSNAME RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TNS_NAME                       VARCHAR2                IN

[oracle@anbob ~]$ tnsping anbob/anbob
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 16-MAR-2017 14:16:27
Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u02/app/oracle/product/12.2.0/db_1/network/admin/sqlnet.ora
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=anbob))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.3)(PORT=1521)))
OK (10 msec)

[oracle@anbob ~]$ tnsping pdbanbob

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 16-MAR-2017 15:06:44
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
/u02/app/oracle/product/12.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = anbob.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdbanbob.com)))
OK (10 msec)

[oracle@anbob ~]$ env|grep TNS
[oracle@anbob ~]$ cd $ORACLE_HOME/network/admin
[oracle@anbob admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u02/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ANBOB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = anbob.com)(PORT = 1521))


ANBOB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = anbob.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = anbob.com)
    )
  )

pdbANBOB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = anbob.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdbanbob.com)
    )
  )
cdb210 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.210)(PORT = 1521))
    (LOAD_BALANCE = NO)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL122)
    )
  )

DBMS_TNS
SQL> select dbms_tns.resolve_tnsname('cdb210') from dual;
DBMS_TNS.RESOLVE_TNSNAME('CDB210')
------------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.31.210)(PORT=1521))(LOAD_BALANCE=NO)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL122)(CID=(PROGRAM=oracle)(HOST=anbob)(USER=oracle))))

SQL> select dbms_tns.resolve_tnsname('anbob/anbob.com') from dual;
DBMS_TNS.RESOLVE_TNSNAME('ANBOB/ANBOB.COM')
----------------------------------------------------------------------------------------------
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=anbob.com)(CID=(PROGRAM=oracle)(HOST=anbob)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.3)(PORT=1521)))

SQL> select dbms_tns.resolve_tnsname('149.0.0.1/anbob') from dual;
DBMS_TNS.RESOLVE_TNSNAME('149.0.0.1/ANBOB')
------------------------------------------------------------------------------
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=anbob)(CID=(PROGRAM=oracle)(HOST=anbob)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP)(HOST=149.0.0.1)(PORT=1521)))

SQL> select dbms_tns.resolve_tnsname('300.0.0.0/anbob') from dual;
DBMS_TNS.RESOLVE_TNSNAME('300.0.0.0/ANBOB')
------------------------------------------------------------------------------------------------

Note:
dbms_tns和tnsping一样可以解析EZCONNECT和TNSNAMES，但是不会验证IP和PORT是否能通信，所以这点不会像tnsping那样与listener通信返回通信结果，就像上面使用的149.0.0.1这样的IP, 但是对于无效的IP如300.0.0.0和不存在的主机名也是无法解析的返回空。

这样一来像开始我提到的，如果取数据库中所有DBLINK的源端主机信息就更加容易。

SQL> select owner,db_link,dbms_tns.resolve_tnsname(host) from dba_db_links;
OWNER                           DB_LINK
-------------------------     ----------------------------------------------------------
DBMS_TNS.RESOLVE_TNSNAME(HOST)
----------------------------------------------------------------
SYS                            SYS_HUB


SYS                           LINK_PROD.COM
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anbob.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdbanbob.com)(CID=(PROGRAM=oracle)(HOST=anbob)(USER=oracle))))

SYS                            LINK_CDB210.COM
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.31.210)(PORT=1521))(LOAD_BALANCE=NO)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL122)(CID=(PROGRAM=oracle)(HOST=anbob)(USER=oracle))))

打赏

微信扫一扫，打赏作者吧～