Oracle 通过 SQL 语句查看数据库服务器 IP 地址

原创 JiekeXu All China Database Union 2021-11-26

5815

Oracle 通过 SQL 语句查看数据库服务器 IP 地址,有如下几种方法：

数据库 IP 配置如下 host 所示：

#public ip  
192.168.75.128 jiekexu-r1  
192.168.75.129 jiekexu-r2  
#private ip  
10.10.10.128 jiekexu-r1-priv  
10.10.10.129 jiekexu-r2-priv  
#vip  
192.168.75.130 jiekexu-r1-vip  
192.168.75.131 jiekexu-r2-vip  
#scanip  
192.168.75.132 jiekexu-racscan

查看 public IP 及主机名

SQL> col PUBLIC_IP for a30  
SQL> col HOSTNAME for a30  
SQL> select utl_inaddr.get_host_address PUblic_IP,utl_inaddr.get_host_name HOSTNAME from dual;  
  
PUBLIC_IP HOSTNAME  
------------------------------ ------------------------------  
192.168.75.128 jiekexu-r1  
  
  
SQL> SELECT UTL_INADDR.get_host_address PUBLIC_IP from dual;  
  
PUBLIC_IP  
------------------------------  
192.168.75.128

– DBMS 包查看

SET serveroutput on  
BEGIN  
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);       -- get local host name  
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS);    -- get local IP addr  
END;  
/

RAC IP 查看

SQL> select * from v$configured_interconnects;  
  
NAME IP_ADDRESS IS_ SOURCE CON_ID  
--------------- --------------- --- --------------- ----------  
ens33:1 169.254.3.163 NO 0  
ens32 192.168.75.128 YES 0  
ens32:1 192.168.75.132 YES 0  
ens32:2 192.168.75.130 YES 0  

------------------------
select  
indx as "Interface Index",  
inst_id as "RAC Instance",  
pub_ksxpia as "Public?",  
picked_ksxpia as "RAC Device",  
name_ksxpia as "NIC Device",  
ip_ksxpia as "IP Address"  
from x$ksxpia;  
  
Interface Index RAC Instance Public? RAC Device NIC Device IP Address  
--------------- ------------ ---------- ----------------------------------- --------------- ----------------------------------------------  
0 1 N GPnP ens33:1 169.254.3.163  
1 1 Y GPnP ens32 192.168.75.128  
2 1 Y GPnP ens32:1 192.168.75.132  
3 1 Y GPnP ens32:2 192.168.75.130  
4 1 Y GPnP ens32:3 192.168.75.131

通过 PLSQL 查看

图片.png

如果 scanIP 在本节点也可以查到，节点 2 VIP FAILED OVER 也会在节点 1 查到，故会查到公网 IP 以及 VIP 和 scanIP，无法查到私网 IP。

Single DB 查看 IP

select  
sys_context('USERENV','SERVER_HOST') as HOST,  
utl_inaddr.get_host_address(sys_context('USERENV','SERVER_HOST')) as IP  
from dual;
HOST               IP
------------------ --------------------
test-19cogg-87      192.168.75.87

shell 查看 IP 和实例名

通过 shell 命令查看实例名

jiekexu-r1:/home/oracle(JiekeXu1)$ ps -ef | grep ora_smon | grep -v grep  
oracle 10780 1 0 14:32 ? 00:00:00 ora_smon_JiekeXu1  
jiekexu-r1:/home/oracle(JiekeXu1)$ ps -ef | grep ora_smon | grep -v grep| awk -F" " '{print $8}'| awk -F"_" '{print $3}'  
JiekeXu1

通过 shell 命令查看 IP

jiekexu-r1:/home/oracle(JiekeXu1)$ grep -w $HOSTNAME /etc/hosts| grep -v vip | grep -v priv| awk -F" " '{print $1}'  
192.168.75.128  
  
jiekexu-r1:/home/oracle(JiekeXu1)$ ping `hostname` -c 1  
PING jiekexu-r1 (192.168.75.128) 56(84) bytes of data.  
64 bytes from jiekexu-r1 (192.168.75.128): icmp_seq=1 ttl=64 time=0.017 ms  
  
--- jiekexu-r1 ping statistics ---  
1 packets transmitted, 1 received, 0% packet loss, time 0ms  
rtt min/avg/max/mdev = 0.017/0.017/0.017/0.000 ms  
  
jiekexu-r1:/home/oracle(JiekeXu1)$ ping `hostname` -c 1 | grep PING  
PING jiekexu-r1 (192.168.75.128) 56(84) bytes of data.  
  
jiekexu-r1:/home/oracle(JiekeXu1)$ ping `hostname` -c 1 | grep PING | cut -d '(' -f2  
192.168.75.128) 56  
  
jiekexu-r1:/home/oracle(JiekeXu1)$ ping `hostname` -c 1 | grep PING | cut -d '(' -f2  
192.168.75.128) 56 
jiekexu-r1:/home/oracle(JiekeXu1)$ ping `hostname` -c 1 | grep PING | cut -d '(' -f2  | cut -d ')' -f1
192.168.75.128

图片.png