Location>code7788 >text

oracle query a sql statement client ip address

Popularity：493 ℃/2024-12-13 22:18:13

1. contexts

After a business anomaly, or a certain sql causes the system to stall. The problem needs to be traced back to the client's IP where the sql was initiated.

2. cs architecture

Clients connect directly to the database, it can be very easy to query, using through the sql_id to find the client, process or port, etc., the default mode is no ip address records

select machine,program,port from GV$SESSION where sql_id=
or
select machine,program,port from GV$ACTIVE_SESSION_HISTORY where sql_id=

select machine,program,port from dba_hist_ACTIVE_SESS_HISTORY where sql_id=

再通过数据库监听查询or应用服务器

2.1. Case Notes

select machine,program,port from GV$SESSION where sql_id=

or GV$ACTIVE_SESSION_HISTORY gets the table of the

By getting the fields machine, program, port.

Again, the database listener logs only fetch the

cat |grep 51880|grep Thin

Note: It is found that the hostname of the computer recorded in the monitoring log may not be the same as the one recorded in the session.

3. BS Architecture

The difference between the bs architecture and the cs architecture, bs uses a multi-tier architecture, it is the application service that connects to the database, and the ip that is obtained is also the ip address of the application server.

As an example, a java application server needs to be queried to the client's ip originator.

There is a trick: the application name of the java service are called JDBC Thin Client, so a server has more than one java service can not be distinguished, you can manually differentiate the name to define different java services, such as java applications in the configuration file for the link pool configuration, so that the name of your application has become oracle-monitor

      data-source-properties:
        "[v$]": oracle-monitor

There are several ways

  1. Through nginx, the client connects to nginx and nginx forwards to the application server. Then configure the time and the requested transaction to locate it, and need to record the logs of the application server, the log format is similar to the
log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent $request_time $upstream_response_time "$http_referer" '
                      '$connection $upstream_addr "$http_x_forwarded_for" "$http_cookie" ';

$remote_addr: this is the client's ip

  1. There is no nginx, the application container uses tomcat, you can view the tomcat request logs

  1. There are also application-side logging logs to get

4. Database Login Trigger

When logging in, you can then session record the ip address so that you don't need to go to the monitoring log to query the specific ip address

CREATE OR REPLACE TRIGGER on_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;

SELECT * FROM gv$session WHERE client_info The ip address is reflected in this field.

5. summarize

Trace the ip address of the source

  1. Get the client, app, port to locate the ip address by using the slow sql_id. the app service name in the BS architecture can be set differently in the link pool. You can add a new login trigger to log the ip more easily
  2. The BS architecture needs to locate the client, which needs to be located by request and time, etc., and then by nginx or tomcat access logs
<Previous Next>