Hey Connor & Chris :-),
I found a pretty weird situation on my this Oracle DB server (Version:
11.2.0.4.0 for single instance on Linux) - never showing IP on column
"client_info" of view
"V$SESSION" even if creating a
TRIGGER particularly acquiring
IP address.
The following is my trigger code being created on
SYS shcema:
COLUMN text FORMAT a80
SELECT line, text FROM user_source WHERE name = 'ON_LOGON_TRIGGER';
LINE TEXT
---------- --------------------------------------------------------------------------------
1 TRIGGER on_logon_trigger
2 AFTER LOGON ON DATABASE
3 BEGIN
4 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('userenv', 'ip_address'));
5 END;
SET LONG 9999
SET LINESIZE 200
SET PAGESIZE 60
SELECT trigger_body, status FROM user_triggers WHERE trigger_name = 'ON_LOGON_TRIGGER';
TRIGGER_BODY STATUS
-------------------------------------------------------------------------------- --------
BEGIN ENABLED
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('userenv', 'ip_address'));
END;
As you can see from the preceding
second code I've
enabled to that trigger now. In the mean time, using the view "v$session" to check session numbers with column "client_info" and other important columns such as username, machine, and program. Nevertheless column "CLIENT_INFO" never shows the IP address, it's why?
SET LINESIZE 200
SET PAGESIZE 200
COLUMN username FORMAT a25
COLUMN machine FORMAT a20
COLUMN client_info FORMAT a15
COLUMN program FORMAT a16
COLUMN status FORMAT a8
SELECT username
, machine
, client_info
, program
, status
, count(*)
FROM v$session
WHERE username IS NOT NULL
-- AND status = 'ACTIVE'
AND program = 'JDBC Thin Client'
GROUP BY username
, machine
, client_info
, program
, status
ORDER BY count(*) DESC
, status
/
USERNAME MACHINE CLIENT_INFO PROGRAM STATUS COUNT(*)
------------------------- -------------------- --------------- ---------------- -------- ----------
TEST-00010 web10 JDBC Thin Client INACTIVE 20
TEST-00011 web11 JDBC Thin Client INACTIVE 16
TEST-00012 web12 JDBC Thin Client INACTIVE 10
TEST-00013 web13 JDBC Thin Client INACTIVE 10
TEST-00014 web14 JDBC Thin Client INACTIVE 10
TEST-00015 web15 JDBC Thin Client INACTIVE 10
TEST-00016 web16 JDBC Thin Client INACTIVE 10
TEST-00017 web17 JDBC Thin Client INACTIVE 10
TEST-00018 web18 JDBC Thin Client INACTIVE 10
9 rows selected.
Initially I thought that it might take an effection if
rebooting oracle db servere but after which I tried, still
no showing IP address. Is it a bug? (By the way my another oracle db server is
okay).
Could you give me some good suggestion to troubleshoot it? Very appreciate if if any help. Thanks in advance!!!
Best Regards
Quanwen Zhao
P.S.: Finally I must provide other important info to you, so appending to the following place.
Another DB server (hostname is
"yyyy") is also
11.2.0.4.0, the same patch with that DB (hostname is
"xxxx") mentioned previously.
[oracle@xxxx db_1]$ cd OPatch/
[oracle@xxxx OPatch]$ ./opatch apply
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2020-07-04_22-00-50PM_1.log
[oracle@yyyy db_1]$ cd OPatch/
[oracle@yyyy OPatch]$ ./opatch apply
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2020-07-04_22-00-29PM_1.log
As you can see
"yyyy" is able to show IP address as follows,
SET LINESIZE 200
SET PAGESIZE 200
COLUMN username FORMAT a25
COLUMN machine FORMAT a20
COLUMN client_info FORMAT a15
COLUMN program FORMAT a16
COLUMN status FORMAT a8
SELECT username
, machine
, client_info
, program
, status
, count(*)
FROM v$session
WHERE username IS NOT NULL
-- AND status = 'ACTIVE'
AND program = 'JDBC Thin Client'
GROUP BY username
, machine
, client_info
, program
, status
ORDER BY count(*) DESC
, status
/
USERNAME MACHINE CLIENT_INFO PROGRAM STATUS COUNT(*)
------------------------- -------------------- --------------- ---------------- -------- ----------
DEV-0001 web01 10.10.20.64 JDBC Thin Client INACTIVE 10
DEV-0002 web02 10.10.20.43 JDBC Thin Client INACTIVE 10
DEV-0003 web03 10.10.20.70 JDBC Thin Client INACTIVE 10
DEV-0004 web04 10.10.20.63 JDBC Thin Client INACTIVE 10
DEV-0005 web05 10.10.20.45 JDBC Thin Client INACTIVE 10
DEV-0006 web06 10.10.20.67 JDBC Thin Client INACTIVE 10
DEV-0007 web07 10.10.20.19 JDBC Thin Client INACTIVE 10
DEV-0008 web08 10.10.20.73 JDBC Thin Client INACTIVE 10
DEV-0009 web09 10.10.20.78 JDBC Thin Client INACTIVE 10
DEV-0010 web10 10.10.20.54 JDBC Thin Client INACTIVE 10
DEV-0011 web11 10.10.20.52 JDBC Thin Client INACTIVE 10
DEV-0012 web12 10.10.20.15 JDBC Thin Client INACTIVE 10
12 rows selected.
These two oracle db servers have been both connected by the JDBC Driver of Java application. It should be
TCP protocol connect. Never set DBMS_APPLICATION_INFO to
NULL.
Now I simply use a
NETSTAT (OS command) to check my TCP connect. The IP address is showing nicely on the server
"xxxx" (Due to security reason, all of real IP addresses are intentionally replaced).
[oracle@xxxx ~]$ netstat -ntp | grep 1521 | grep oracle
......
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.14:45020 ESTABLISHED 26865/oraclexxxx
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.11:51785 ESTABLISHED 24644/oraclexxxx
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.22:53110 ESTABLISHED 25499/oraclexxxx
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.73:37065 ESTABLISHED 24797/oraclexxxx
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.47:47698 ESTABLISHED 26608/oraclexxxx
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.51:39071 ESTABLISHED 25754/oraclexxxx
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.38:35335 ESTABLISHED 26031/oraclexxxx
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.18:38523 ESTABLISHED 25625/oraclexxxx
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.10:59745 ESTABLISHED 26364/oraclexxxx
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.15:56064 ESTABLISHED 24924/oraclexxxx
tcp 0 0 ::ffff:10.06.0.34:1521 ::ffff:10.10.20.13:38353 ESTABLISHED 24852/oraclexxxx
......
Now I'm still struggling with the CLIENT_INFO (as you can see, all of connecting is TCP type).
The following is more information only using remote SQL*Plus to test my case!!! Due to too many stuff at the same time I've also submitted it to ODC forum. You can read it from
https://community.oracle.com/thread/4337533 Thank you!
Closing this because you're getting more activity on the forum here.