Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Log Miner Question

RE: Log Miner Question

From: Khemmanivanh, Somckit <>
Date: Fri, 20 May 2005 10:11:59 -0700
Message-ID: <65C0D8935651CB4D96E97CEFAC5A12B90A5C33@wafedixm10.corp.weyer.pri>

Hmm, have you tried the last command? It's format is:

Userid terminal IP Date

Where IP is IP address of the user's workstation or hostname..


-----Original Message-----
From: Sam Bootsma []=20 Sent: Friday, May 20, 2005 10:04 AM
To: Khemmanivanh, Somckit; Subject: RE: Log Miner Question


Only users in IT have direct access to the server to run SqlPlus. But I suspect it is one of the IT users that dropped the package so your suggestion is relevant. Does "lastcomm" show the workstation of the user? When I did a man of the command I saw it shows a terminal device, but I didn't see workstation.


-----Original Message-----
From: Khemmanivanh, Somckit
[]=20 Sent: May 20, 2005 12:25 PM
To: Sam Bootsma;
Subject: RE: Log Miner Question

Since you said "I requested a log of user logins" I'm going to assume the users log directly into the server to access sqlplus...

Some ways to approach this from the O.S. side:

Does your SA have accounting enabled? The lastcomm command will tell which users where using SQLPLUS.
This info can also be derived from looking at the shell history file of users logged into the server during the time period in question.

There maybe other various auditing tools specific to AIX...


-----Original Message-----
[] On Behalf Of Sam Bootsma Sent: Friday, May 20, 2005 6:37 AM
Subject: Log Miner Question


Last week we had a problem in our production environment due to a dropped Oracle package that invalidated a trigger. Our Development team was able to research the problem, then recreate the dropped package and compile the trigger. Nobody admitted to dropping the Oracle package so I used Log Miner to confirm that the package had been dropped, what day and time it was dropped, the Oracle user logged in, and the OS user logged in. The details immediately below come from v$logmnr_contents (select username, session#, serial#, to_char(timestamp,'yy-mon-dd hh:mi:ss') "time", session_info, sql_redo .....)


SYSTEM                                222       2036 05-may-16 03:04:12

login_username=3DSYSTEM client_info=3D OS_username=3Dbanner Machine_name=3DTCC6C402 OS_t

erminal=3D OS_process_id=3D82044 OS_program name=3Dsqlplus_at_TCC6C402 (TNS V1-V3) =20

drop package BANINST1.WSKSTST; =20


Note that the OS process id is included in session_info column of this virtual table. The problem is, I am not able to trace the OS process_id back to a user workstation. I requested a log of user logins from our AIX Administrator and he returned with a report that shows the Unix user id, terminal (eg. pts/2), the name or IP address connecting to the Unix box, and the time period of the connection. However, the log did not include Unix Process ID so I am not able to link back to the information provided by Log Miner. The System Administrator is currently following up with the vendor on how Unix Process ID can be obtained. Can anybody tell me if there is another way to trace the drop command back to a workstation? For example, does Log Miner store the terminal type in any of its tables?


We are running Oracle on AIX Unix 5. =20


Our other Oracle DBA (who was dropping packages an hour earlier as part of cleanup tasks) is telling everybody this is a security breach. But I think it is much more likely to be human error.




Sam Bootsma

George Brown College <>=20

416-415-5000 x4933



Received on Fri May 20 2005 - 13:18:42 CDT

Original text of this message