重庆思庄oracle技术分享-Oracle通过SQL语句查数据库服务器IP信息

文档课题： Oracle 通过 SQL 语句查数据库服务器 IP 信息 . 数据库：oracle 11.2.0.464位 系统：centos 7.964位 1、hosts文件 /etc/hosts文件内容如下： #PublicIP (ens33) 192.168.133.210hisdb1 192.168.133.211hisdb2 #PrivateIP (ens37) 192.168.11.110hisdb1-priv 192.168.11.111hisdb2-priv #VirtualIP (ens33) 192.168.133.212hisdb1-vip 192.168.133.213hisdb2-vip #Scan IP(ens33) 192.168.133.214hisdb-scan 192.168.133.215hisdbdg 2、sql查询 查public ip及主机名. SQL>col PUBLIC_IP for a20 SQL> colhostname for a15 SQL>select utl_inaddr.get_host_address PUBLIC_IP,utl_inaddr.get_host_name HOSTNAMEfrom dual; PUBLIC_IP            HOSTNAME ----------------------------------- 192.168.133.210      hisdb1 dbms包查主机名和ip SQL>set serveroutput on SQL> begin   2 dbms_output.put_line(utl_inaddr.get_host_name);   3 dbms_output.put_line(utl_inaddr.get_host_address);   4  end;   5  / hisdb1 192.168.133.210 PL/SQLprocedure successfully completed. 查rac publicip. SQL>select * from v$configured_interconnects; NAME            IP_ADDRESS       IS_ SOURCE ------------------------------- --- ------------------------------- ens37:1         169.254.207.21   NO ens33           192.168.133.210  YES ens33:1         192.168.133.214  YES ens33:3         192.168.133.212  YES SQL> col"RAC Device" for a15 SQL> selectindx          as "InterfaceIndex",        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; InterfaceIndex RAC Instance Public?    RACDevice      NIC Device      IP Address --------------------------- ---------- --------------- --------------- ----------------               0            1 N          GPnP            ens37:1         169.254.207.21               1            1 Y          GPnP            ens33           192.168.133.210               2            1 Y          GPnP            ens33:1         192.168.133.214               3            1 Y          GPnP            ens33:3         192.168.133.212 注意：只显示本节点公网IP、VIP和ScanIP，无法查到私网 IP. SingleDB查IP SQL>col host for a15 SQL>col ip for a15 SQL>r   1 select sys_context('USERENV', 'SERVER_HOST') as HOST,   2        utl_inaddr.get_host_address(sys_context('USERENV', 'SERVER_HOST')) as IP   3*  from dual HOST            IP ------------------------------ hisdbdg         192.168.133.215 3、shell查询 查实例名. [oracle@hisdb1~]$ ps -ef|grep ora_smon|grep -v grep oracle     4080     1  0 16:45 ?        00:00:00 ora_smon_orcl1 [oracle@hisdb1~]$ ps -ef | grep ora_smon | grep -v grep| awk -F" " '{print $8}'|awk -F"_" '{print $3}' orcl1 查IP. [oracle@hisdb1 ~]$ grep -w $HOSTNAME /etc/hosts| grep -v vip | grep -vpriv| awk -F" " '{print $1}' 192.168.133.210 [oracle@hisdb1 ~]$ ping `hostname` -c 1 PING hisdb1 (192.168.133.210) 56(84) bytes of data. 64 bytes from hisdb1 (192.168.133.210): icmp_seq=1 ttl=64 time=0.038 ms --- hisdb1 ping statistics --- 1 packets transmitted, 1 received, 0% packet loss, time 0ms rtt min/avg/max/mdev = 0.038/0.038/0.038/0.000 ms [oracle@hisdb1 ~]$ ping `hostname` -c 1 | grep PING   PING hisdb1 (192.168.133.210) 56(84) bytes of data. [oracle@hisdb1 ~]$ ping `hostname` -c 1 | grep PING | cut -d '(' -f2 192.168.133.210) 56 [oracle@hisdb1 ~]$ ping `hostname` -c 1 | grep PING | cut -d '(' -f2  | cut -d ')' -f1 192.168.133.210