QUERYING A SERVER’S IP ADDRESS
Environments
REQUEST
I want to gather a hostname and/or IP address of the current host or a remote one.
SOLUTION
A hostname and IP address can be retrieved through UTL_INADDR package
LOCAL HOST
oracle@dbpilot.net: nslookup dbpilot.net
Server: 10.1.1.50
Address: 10.1.1.50#53
Name: dbpilot.net
Address: 10.1.1.10
COL IP FOR A10
SELECT SYS.UTL_INADDR.GET_HOST_ADDRESS IP FROM DUAL;
IP
----------
10.1.1.10
COL HOSTNAME FOR A15
SELECT SYS.UTL_INADDR.GET_HOST_NAME HOSTNAME FROM DUAL;
HOSTNAME
---------------
dbpilot.net
A REMOTE HOST
oracle@dbpilot.net: nslookup another.host
Server: 10.1.1.50
Address: 10.1.1.50#53
Name: another.host
Address: 10.1.1.20
COL IP FOR A10
SELECT SYS.UTL_INADDR.GET_HOST_ADDRESS('another.host') IP FROM DUAL;
IP
----------
10.1.1.20
COL HOSTNAME FOR A15
SELECT SYS.UTL_INADDR.GET_HOST_NAME('10.1.1.20') HOSTNAME FROM DUAL;
HOSTNAME
---------------
another.host
NON EXISTING HOST
oracle@dbpilot.net: nslookup 1.1.1.1
Server: 10.1.1.50
Address: 10.1.1.50#53
** server can't find 1.1.1.1.in-addr.arpa.: NXDOMAIN
oracle@dbpilot.net: nslookup non.existing.host
Server: 10.1.1.50
Address: 10.1.1.50#53
** server can't find non.existing.host: NXDOMAIN
SQL> COL IP FOR A10
SQL> SELECT SYS.UTL_INADDR.GET_HOST_ADDRESS('non.existing.host') IP FROM DUAL;
SELECT SYS.UTL_INADDR.GET_HOST_ADDRESS('non.existing.host') IP FROM DUAL
*
ERROR at line 1:
ORA-29257: host non.existing.host unknown
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1
SQL> COL HOSTNAME FOR A30
SQL> SELECT SYS.UTL_INADDR.GET_HOST_NAME('1.1.1.1') HOSTNAME FROM DUAL;
SELECT SYS.UTL_INADDR.GET_HOST_NAME('1.1.1.1') HOSTNAME FROM DUAL
*
ERROR at line 1:
ORA-29257: host 1.1.1.1 unknown
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1
Version : 16:47 19.01.2018
Database : Oracle database 10g – 12cR2
Database : Oracle database 10g – 12cR2
Tags In
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (69)
- Account (2)
- AWR (4)
- Database Errors (6)
- Database Performance (10)
- Datapump (4)
- Enterprise Manager (25)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (3)
- High IO Load (4)
- Historical Session Information (3)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Tablespaces (1)
- Temporary Tablespace (2)
Tags
/etc/sudoers
Account
ACTIVE SESSION HISTORY
Agent
Agent 12c Deinstall
Agent 13c Deployment
ASH
Automated Maintenance Tasks
AWR
BLOCKING SESSION
Database Auditing
Database Directory
Database Performance
Datapump
DBMS_SYSTEM
Enterprise Manager
Enterprise Manager Metrics
Failed Logins (Historical) Metric
Failed Logins Metric
Gather Statistics Job
GET_ENV
High IO Load
historical session information
Idle Sessions
Initialization Parameters
LOCKED(TIMED)
OEM
OEM 12.1.0.4
OEM 13.1.0.0.0
OMS
Processes
Repository
ROW LOCK
Scheduler
Schema Statistics
Scripts
Sessions
spfile
SQL_ID
STANDARD EDITION
Standard Query
TRANSACTION
UTL_FILE
WAITING SESSION
Yum