Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
v$logmnr_contents with username as unknown
1,032 views
Skip to first unread message
jbe...@gmail.com
Sep 13, 2012, 12:57:21 PM9/13/12
to
I applied logminer to find who deleted a lot of rows in one table. View v$logmnr_contents give me details of that operation. Unfortunately, minimum supplemental logging was not active when archived redo logs were generated, and when I query v$logmnr_contents, I found columns "username" and "session_info" as "unknown".
Would be there any chance to deduce what user did that operation? Maybe using timestamp to determine what users were logged at that time? Or using column Log_ID?
Would be there any chance to deduce what user did that operation? Maybe using timestamp to determine what users were logged at that time? Or using column Log_ID?
joel garry
Sep 13, 2012, 1:55:24 PM9/13/12
to
On Sep 13, 9:57 am, jbet...@gmail.com wrote:
> I applied logminer to find who deleted a lot of rows in one table. View v$logmnr_contents give me details of that operation. Unfortunately, minimum supplemental logging was not active when archived redo logs were generated, and when I query v$logmnr_contents, I found columns "username" and "session_info" as "unknown".
>
> Would be there any chance to deduce what user did that operation? Maybe using timestamp to determine what users were logged at that time? Or using column Log_ID?
Don't underestimate the power of the listener log. Some googling
> I applied logminer to find who deleted a lot of rows in one table. View v$logmnr_contents give me details of that operation. Unfortunately, minimum supplemental logging was not active when archived redo logs were generated, and when I query v$logmnr_contents, I found columns "username" and "session_info" as "unknown".
>
> Would be there any chance to deduce what user did that operation? Maybe using timestamp to determine what users were logged at that time? Or using column Log_ID?
might find how to define that as an external table. Of course if you
have beq access, that won't tell all possibilities.
jg
--
@home.com is bogus.
http://mortgagefraudblog.com/perp-walk/item/19634-11-indicted-on-mortgage-fraud-charges
ddf
Sep 13, 2012, 4:07:15 PM9/13/12
to
On Thursday, September 13, 2012 10:57:21 AM UTC-6, jbe...@gmail.com wrote:
> I applied logminer to find who deleted a lot of rows in one table. View v$logmnr_contents give me details of that operation. Unfortunately, minimum supplemental logging was not active when archived redo logs were generated, and when I query v$logmnr_contents, I found columns "username" and "session_info" as "unknown".
>
>
>
> Would be there any chance to deduce what user did that operation? Maybe using timestamp to determine what users were logged at that time? Or using column Log_ID?
Probably, but I don't know how you started logminer. You can use the data dictionary to map names, etc in logminer:
> I applied logminer to find who deleted a lot of rows in one table. View v$logmnr_contents give me details of that operation. Unfortunately, minimum supplemental logging was not active when archived redo logs were generated, and when I query v$logmnr_contents, I found columns "username" and "session_info" as "unknown".
>
>
>
> Would be there any chance to deduce what user did that operation? Maybe using timestamp to determine what users were logged at that time? Or using column Log_ID?
exec dbms_logmnr.start_logmnr(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
The option listed will map user and object names from the online catalog. You can also read here:
http://dfitzjarrell.wordpress.com/2008/10/31/workin-in-the-mines/
but there are no examples showing username and session_info populated. To rectify that:
SQL> select 'exec dbms_logmnr.add_logfile('''||member||''')'
2 from v$logfile;
'EXECDBMS_LOGMNR.ADD_LOGFILE('''||MEMBER||''')'
--------------------------------------------------------------------------------
exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO01.LOG')
exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO02.LOG')
exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO03.LOG')
exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO04.LOG')
exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO05.LOG')
exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO06.LOG')
6 rows selected.
SQL> exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO01.LOG')
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO02.LOG')
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO03.LOG')
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO04.LOG')
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO05.LOG')
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('C:\ORADB\ORADATA\SMEDLEY\REDO06.LOG')
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
PL/SQL procedure successfully completed.
SQL> select username, session_info
2 from v$logmnr_contents
3 where rownum < 11;
USERNAME
------------------------------
SESSION_INFO
--------------------------------------------------------------------------------
SYS
login_username=SYS client_info= OS_username=NT AUTHORITY\SYSTEM Machine_name=INF
OCROSSING\E642-FITZJARREL OS_terminal=E642-FITZJARREL OS_process_id=4772:4752 OS
_program_name=oradim.exe
SYS
login_username=SYS client_info= OS_username=NT AUTHORITY\SYSTEM Machine_name=INF
OCROSSING\E642-FITZJARREL OS_terminal=E642-FITZJARREL OS_process_id=4772:4752 OS
_program_name=oradim.exe
USERNAME
------------------------------
SESSION_INFO
--------------------------------------------------------------------------------
SYS
login_username=SYS client_info= OS_username=NT AUTHORITY\SYSTEM Machine_name=INF
OCROSSING\E642-FITZJARREL OS_terminal=E642-FITZJARREL OS_process_id=4772:4752 OS
_program_name=oradim.exe
SYS
login_username=SYS client_info= OS_username=NT AUTHORITY\SYSTEM Machine_name=INF
OCROSSING\E642-FITZJARREL OS_terminal=E642-FITZJARREL OS_process_id=4772:4752 OS
USERNAME
------------------------------
SESSION_INFO
--------------------------------------------------------------------------------
_program_name=oradim.exe
SYS
login_username=SYS client_info= OS_username=NT AUTHORITY\SYSTEM Machine_name=INF
OCROSSING\E642-FITZJARREL OS_terminal=E642-FITZJARREL OS_process_id=4772:4752 OS
_program_name=oradim.exe
SYS
login_username=SYS client_info= OS_username=NT AUTHORITY\SYSTEM Machine_name=INF
USERNAME
------------------------------
SESSION_INFO
--------------------------------------------------------------------------------
OCROSSING\E642-FITZJARREL OS_terminal=E642-FITZJARREL OS_process_id=4772:4752 OS
_program_name=oradim.exe
SYS
login_username=SYS client_info= OS_username=NT AUTHORITY\SYSTEM Machine_name=INF
OCROSSING\E642-FITZJARREL OS_terminal=E642-FITZJARREL OS_process_id=4772:4752 OS
_program_name=oradim.exe
SYS
USERNAME
------------------------------
SESSION_INFO
--------------------------------------------------------------------------------
login_username=SYS client_info= OS_username=NT AUTHORITY\SYSTEM Machine_name=INF
OCROSSING\E642-FITZJARREL OS_terminal=E642-FITZJARREL OS_process_id=4772:4752 OS
_program_name=oradim.exe
SYS
login_username=SYS client_info= OS_username=NT AUTHORITY\SYSTEM Machine_name=INF
OCROSSING\E642-FITZJARREL OS_terminal=E642-FITZJARREL OS_process_id=4772:4752 OS
_program_name=oradim.exe
USERNAME
------------------------------
SESSION_INFO
--------------------------------------------------------------------------------
SYS
login_username=SYS client_info= OS_username=NT AUTHORITY\SYSTEM Machine_name=INF
OCROSSING\E642-FITZJARREL OS_terminal=E642-FITZJARREL OS_process_id=4772:4752 OS
_program_name=oradim.exe
10 rows selected.
SQL>
This executed on Oracle 11.2.0.3.
David Fitzjarrell
0 new messages