How to determine the IP address of the remote session client in Oracle a few days ago, a tragedy occurred in the environment where Daniel was located for multiple databases: After we changed the passwords of all database hosts and databases, an Oracle database monitoring tool is installed on a windows host that is integrated with the hardware, constantly trying to use the wrong password (incorrect
A few days before how to determine the IP address of the remote session client in Oracle, a tragedy occurred for multiple databases in Daniel's Environment: After we changed the passwords of all database hosts and databases, an Oracle database monitoring tool is installed on a windows host that is integrated with the hardware, constantly trying to use the wrong password (incorrect
How to determine the IP address of the remote session client in Oracle
A few days ago, a tragedy happened to multiple databases in Daniel's Environment: After we changed the passwords of all database hosts and databases,
An Oracle database monitoring tool is installed on a windows host that is integrated with hardware, constantly trying to use the wrong password (incorrect or incorrect)
Connecting to our database causes a large number of library cache lock wait events in multiple databases in the production environment.
It has no impact, but it has brought great trouble to database O & M, and all login attempts fail. More than a dozen people and several vendors checked for more than a day
Finally, I found the windows host and host. This accident gave us a painful lesson, so I had to study how
Obtain the Client IP address of the remote session to quickly locate such problems. The IP address of the client that obtains the session in Oracle
It can be divided into the IP address of the current session and the IP address of the non-current session.
1. How is the IP address of the client of the current session?
You can use SYS_CONTEXT namespace to obtain the Client IP address of the current session.
[Oracle @ prod admin] $ sqlplus system/oracle123 @ prod
SQL * Plus: Release 11.2.0.3.0 Production on Tue Mar 19 20:48:47 2013
Copyright (c) 1982,201 1, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
20:48:51 system @ PROD> col "My IP Address" for a30
20:48:59 system @ PROD> SELECT SYS_CONTEXT ('userenv', 'IP _ address') "My ip address" from dual;
My IP Address
------------------------------
192.168.8.160
Note: To test whether this method can be connected to a database through operating system authentication, TNS must be used because the former is connected through the IPC protocol.
The latter is connected through the TCP/IP protocol.
2. How to obtain the Client IP address of all remote sessions
There is no way to directly obtain the IP address of the remote session client in the Oracle database. However, the current
The IP address of the session client. Create an after logon trigger in the database, and then use
DBMS_APPLICATION_INFO.SET_CLIENT_INFO stored procedure. We can query v $ session to obtain all
The Client IP address of the remote session.
First, create the following trigger:
Create or replace trigger login_on AFTER LOGON DATABASE
DECLARE
Ipinfo VARCHAR2 (30 );
BEGIN
SELECT SYS_CONTEXT ('userenv', 'IP _ address') INTO ipinfo from dual;
DBMS_APPLICATION_INFO.SET_CLIENT_INFO (ipinfo );
END;
/
Connect to the database using any remote user (TNS), and then query the following statement to view the Client IP address of the session:
21:03:35 system @ PROD> conn scott/tiger @ prod
Connected.
21:04:05 scott @ PROD> col sys_context ('userenv', 'sid ') for a40
21:04:18 scott @ PROD> select sys_context ('userenv', 'sid ') from dual;
SYS_CONTEXT ('userenv', 'sid ')
----------------------------------------
125
Use the system user to create a session connection to query the Client IP address of the session created by the scott user in this session:
21:07:47 sys @ PROD> col client_info for a30
21:08:04 sys @ PROD> col CLIENT_IDENTIFIER for a30
21:08:17 sys @ PROD> select username, sid, serial #, client_info, client_identifier from v $ session where sid = 125;
Username sid serial # CLIENT_INFO CLIENT_IDENTIFIER
--------------------------------------------------------------------------------------------------------------
SCOTT 125 35 192.168.8.160
At 21:08:43 sys @ PROD> select username, sid, serial #, client_info, client_identifier from v $ session where sid = 18;
Username sid serial # CLIENT_INFO CLIENT_IDENTIFIER
--------------------------------------------------------------------------------------------------------------
SCOTT 18 17 192.168.8.100
[Oracle @ dss admin] $ sqlplus scott/tiger @ prod
SQL * Plus: Release 11.2.0.3.0 Production on Tue Mar 19 21:20:16 2013
Copyright (c) 1982,201 1, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
21:21:18 scott @ PROD> col sys_context ('userenv', 'sid ') for a30
21:21:24 scott @ PROD> select sys_context ('userenv', 'sid ') from dual;
SYS_CONTEXT ('userenv', 'sid ')
------------------------------
139
21:08:59 sys @ PROD> select username, sid, serial #, client_info, client_identifier from v $ session where sid = 139;
Username sid serial # CLIENT_INFO CLIENT_IDENTIFIER
--------------------------------------------------------------------------------------------------------------
SCOTT 139 163 192.168.8.180
Http://blog.csdn.net/xiangsir/article/details/8693734
