重庆思庄Oracle、Redhat认证学习论坛

标题: Oracle通过SQL语句查数据库服务器IP信息 [打印本页]

作者: denglj 时间: 2022-11-17 10:59
标题: 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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)