Skip to Main Content
  • Questions
  • Why client_info in v$session never show IP address even if creating trigger acquiring IP?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Quanwen.

Asked: July 05, 2020 - 10:04 am UTC

Last updated: July 20, 2020 - 3:26 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

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!

and Connor said...

Closing this because you're getting more activity on the forum here.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you, Connor!

Quanwen Zhao, July 11, 2020 - 3:14 am UTC

A couple of days ago, Jonathan Lewis helped me to solve it. There has the hidden parameter "_system_trig_enabled" to be set "FALSE" so my trigger has always never been fired.

It's so weird, after I replaced with a "TRUE" (default value), it's normal.

Best Regards
Quanwen Zhao

P.S: You and Chris should face a huge pressure when so many people submitting lots of questions every day? So AskTOM has to set a window temporarily closed for preferably answering our questions. In contrary ODC ( https://community.oracle.com/welcome ) has the oracle experts all around the world to help us to answer various problems on each oracle space modules.
Connor McDonald
July 11, 2020 - 5:53 am UTC

"_system_trig_enabled" used to be toggled during patching and upgrades before the "startup upgrade" command was introduced.

So there's a very good chance you had that wrongly set for *long* time :-)

Patching and upgrates?

Quanwen Zhao, July 11, 2020 - 10:23 am UTC

Wow, I've never patched and upgrated my oracle db 11.2.0.4.0.

Is there a method specially tracing some information that who, at what time, and for what purpose modified this parameter "_system_trig_enabled"?

Best Regards
Quanwen Zhao
Connor McDonald
July 13, 2020 - 3:11 am UTC

If you've kept your alert log long enough, it would be in there

Good!

Quanwen Zhao, July 13, 2020 - 9:39 am UTC

Thank you so much, Connor! I usually do the ALERT log file cutting with a SHELL script, how should I search approximate what similar KEYWORD in my alert log?

Perhaps I'll look into ALERT log with parameter "_system_trig_enabled" firstly.

By the way when I am writing a new review here I never see your previous comment in this window so I have to open another tab page on my Google browser with my original Question Link - https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9543192700346829856 in order to see your lastest comment, is it a bug or the restriction/limitation of APEX? So inconvenient...

Best Regards
Quanwen Zhao
Connor McDonald
July 14, 2020 - 3:48 am UTC

Every db start dumps out non-default parms to the alert log as well.

I'll take a look at the "write review" screen and make some changes if possible.

Thanks for letting us know.

Finding out the root cause

Quanwen Zhao, July 19, 2020 - 2:08 am UTC

Hello Connor,

Today I eventually find out the root cause why the hidden parameter "_system_trig_enabled" has been changed.

Firstly, I search "_system_trig_enabled" on the current ALERT log file and see the value of that parameter is "FALSE" loading by the SPFILE$ORACLE_SID.ora, like this:

[oracle@xxxx trace]$ vi alert_$ORACLE_SID.log
......
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfile_$ORACLE_SID.ora
System parameters with non-default values:
......
  _system_trig_enabled     = FALSE
......


So I next see the original INIT parameter file and find whose update date is on Apr 23, 2016 and notice that on which the "_system_trig_enabled" has set "FALSE".

[oracle@xxxx dbs]$ ls -lrht
-rw-r--r--  1 oracle oinstall 1.1K Apr 23  2016 init$ORACLE_SID.ora

[oracle@xxxx dbs]$ cat initORACLE_SID.ora | grep _system_trig_enabled
*._system_trig_enabled=FALSE


Very luckily the cutting ALERT log file (recorded in 5 years ago) has still been existing on my oracle database and on which I finally find out the value of parameter once modified by the former DBA on Oct 10, 2015 (by the way I join company on 2016).

[oracle@xxxx trace]$ ls -lrht alert_$ORACLE_SID_20161203013001.log 
-rw-r----- 1 oracle oinstall 308M Dec  3  2016 alert_$ORACLE_SID_20161203013001.log

[oracle@xxxx trace]$ vi alert_$ORACLE_SID_20161203013001.log
...... 
Sat Oct 10 18:54:24 2015
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=BOTH;
......


Best Regards
Quanwen Zhao
Connor McDonald
July 20, 2020 - 1:55 am UTC

wow ... 2016 :-)

Nice investigative work.

People often truncate their alert logs, but this goes to show...some times it is useful to keep a trail of everythning.

It's best if adding an extra parameter annotation/comment

Quanwen Zhao, July 20, 2020 - 2:44 am UTC

Thanks, Connor!

If adding an extra parameter annotation or comment to alter command, such as, "ALTER SESSION/SYSTEM SET parameter_name=value SCOPE=SPFILE/MEMORY/BOTH ANNOTATION/COMMENT=......" it would be perfect to let us know why modifying this parameter in particular whose is a boolean vlaue (true or false).

Although I know anyone has changed it, don't know the reason why modifying it (for what purpose ...). This is at least an improved or enhanced feedback suggestion about future database NEW FEATURES.

Could I give this suggestion to which oracle product manager?

Best Regards
Quanwen Zhao
Connor McDonald
July 20, 2020 - 3:26 am UTC

There has long been requests for that facility.

All I can suggest is to add it here

https://community.oracle.com/community/groundbreakers/database/database-ideas


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library