Re: v$logmnr_contents with username as unknown

From: ddf <oratune_at_msn.com>
Date: Thu, 13 Sep 2012 13:07:15 -0700 (PDT)
Message-ID: <8192b46e-5072-4f62-952e-b94da1d8b74a_at_googlegroups.com>

On Thursday, September 13, 2012 10:57:21 AM UTC-6, jbe..._at_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:

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 Received on Thu Sep 13 2012 - 15:07:15 CDT