Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Getachew.

Asked: October 16, 2005 - 6:01 pm UTC

Last updated: July 30, 2019 - 3:42 am UTC

Version: 9.2.0.5

Viewed 1000+ times

You Asked

Hi Tom!,
First, thank you for taking my question.

I am auditing database activities by privileged users (mainly DBAs) when the login is "as SYSDBA". The trace file shows someting like the following:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /oracle/product/9.2.0.5
System name: HP-UX
Node name: mynode1
Release: B.11.23
Version: U
Machine: ia64
Instance name: TESTDB
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 14621, image: oracle@mynode1 (TNS V1-V3)

Mon Oct 10 20:11:04 2005
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: xx25dmx
CLIENT TERMINAL: pts/0
STATUS: 0

Mon Oct 10 20:00:20 2005
ACTION : 'select count(*) from dual'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/0
STATUS: 0

My goal is to transform this log into an External table input file. The information I am interested in includes, the Instance name (from the top section) and, each of the bottom two sections (each section starting with the date). How could I transform these sections into records? It would be ideal to have each line (example below) to be a field value and, the whole section as one record:

Mon Oct 10 20:00:20 2005
ACTION : 'select count(*) from dual'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/0
STATUS: 0

Is this possible?

Thank you!!


and Connor said...

analytics rock and roll - start with this... run the queries from inside out to see what each does - they build on eachother:


ops$tkyte@ORA9IR2> create or replace directory data_dir as '/tmp/'
2 /

Directory created.

ops$tkyte@ORA9IR2> drop table et;

Table dropped.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table et
2 (str varchar2(4000))
3 ORGANIZATION EXTERNAL
4 ( type oracle_loader
5 default directory data_dir
6 access parameters
7 ( records delimited by newline fields missing field values are null ( str position(1:4000) ) )
8 location ('test.dat')
9 )
10 /

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> column dt format a30
ops$tkyte@ORA9IR2> column action format a10
ops$tkyte@ORA9IR2> column dbuser format a10
ops$tkyte@ORA9IR2> column privilege format a10
ops$tkyte@ORA9IR2> column client_user format a10
ops$tkyte@ORA9IR2> column terminal format a10
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select max( dt ) dt,
2 max( decode(field,'ACTION',val) ) action,
3 max( decode(field,'DATABASE USER',val) ) dbuser,
4 max( decode(field,'PRIVILEGE',val) ) privilege,
5 max( decode(field,'CLIENT USER',val) ) client_user,
6 max( decode(field,'CLIENT TERMINAL',val) ) terminal,
7 max( decode(field,'STATUS',val) ) status
8 from (
9 select case when isdate is not null then str end dt,
10 case when isdate is null then trim(substr( str, 1, instr(str,':')-1)) end field,
11 case when isdate is null then trim(substr( str, instr(str,':')+1)) end val,
12 iname,
13 max(isdate) over (order by r) grp
14 from (
15 select str, rownum r,
16 max( case when str like 'Instance name: %' then substr(str,16) end) over () iname,
17 case when str like '___ ___ __ __:__:__ ____' then rownum end isdate
18 from et
19 )
20 where isdate is not null
21 or str like 'ACTION : %'
22 or str like 'DATABASE USER: %'
23 or str like 'PRIVILEGE : %'
24 or str like 'CLIENT USER: %'
25 or str like 'CLIENT TERMINAL: %'
26 or str like 'STATUS: %'
27 )
28 group by grp
29 /

DT ACTION DBUSER PRIVILEGE CLIENT_USE TERMINAL STATUS
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
Mon Oct 10 20:11:04 2005 'CONNECT' '/' SYSDBA xx25dmx pts/0 0
Mon Oct 10 20:00:20 2005 'select co '/' SYSDBA oracle pts/0 0
unt(*) fro
m dual'


Addenda: Note that the audit file format has changed from release to release. See the reviews for a 19c sample

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

excellent solution

Getachew Geressu, October 18, 2005 - 5:12 pm UTC

It is the best solution!! I have already implemented
it and it works perfectly.
One thing that I added to this process was to append all
the trace files from the audit directory in to one file
and report off of the one file.

Thank you much!!

No rows returned for 11g or 12c Databases

Chi Le, July 26, 2019 - 5:13 am UTC

Hi Tom,

Thank you for such a great way of reporting on these audit files.
I tried to run this against our 11g and 12c databases and the query returned zero rows.
I did compare the audit files from 9i to those from 11g and 12c but they look similar to me.

Much appreciated if you could provide some review for an update if possible.
Many thanks in advance

Connor McDonald
July 30, 2019 - 3:42 am UTC

I just work my way from inside out

SQL> create or replace directory adump as '/u01/app/oracle/admin/db192/adump';

Directory created.

SQL>
SQL> create table et
  2  (str varchar2(4000))
  3  ORGANIZATION EXTERNAL
  4  ( type oracle_loader
  5  default directory adump
  6  access parameters
  7  ( records delimited by newline fields missing field values are null ( str position(1:4000) ) )
  8  location ('db192_ora_6826_20190219000719844365733343.aud'));

Table created.

SQL>
SQL> select * from et;

STR
----------------------------------------------------------------------------------------------------------------------------------
Audit file /u01/app/oracle/admin/db192/adump/db192_ora_6826_20190219000719844365733343.aud
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
Build label:    RDBMS_19.2.0.0.0_LINUX.X64_190204
ORACLE_HOME:    /u01/app/oracle/product/19.2.0/dbhome_1
System name:    Linux
Node name:      db192.localdomain
Release:        4.1.12-94.2.1.el7uek.x86_64
Version:        #2 SMP Wed Apr 26 15:32:38 PDT 2017
Machine:        x86_64
Instance name: db192
Redo thread mounted by this instance: 1
Oracle process number: 54
Unix process pid: 6826, image: oracle@db192.localdomain (TNS V1-V3)

Tue Feb 19 00:07:19 2019 -08:00
LENGTH : '268'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
USERHOST:[17] 'db192.localdomain'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[3] '100'

Tue Feb 19 00:07:19 2019 -08:00
LENGTH : '266'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
USERHOST:[17] 'db192.localdomain'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[2] '44'

Tue Feb 19 00:07:19 2019 -08:00
LENGTH : '266'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
USERHOST:[17] 'db192.localdomain'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[2] '44'

Tue Feb 19 00:07:19 2019 -08:00
LENGTH : '335'
ACTION :[75] 'select lower(user) || '@'||lower(instance_name) global_name
from v$instance'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
USERHOST:[17] 'db192.localdomain'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[1] '3'

Tue Feb 19 00:07:34 2019 -08:00
LENGTH : '295'
ACTION :[33] 'alter pluggable database all open'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
USERHOST:[17] 'db192.localdomain'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[3] '227'

Tue Feb 19 00:07:41 2019 -08:00
LENGTH : '301'
ACTION :[39] 'alter pluggable database all save state'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'

...

114 rows selected.


So I know that I can see the raw data. Then I take the inner most part of the query

SQL>
SQL> select str
  2  from
  3  (
  4   select str, rownum r,
  5   max( case when str like 'Instance name: %' then substr(str,16) end) over () iname,
  6   case when str like '___ ___ __ __:__:__ ____' then rownum end isdate
  7   from et
  8   )
  9   where isdate is not null
 10   or str like 'ACTION : %'
 11   or str like 'DATABASE USER: %'
 12   or str like 'PRIVILEGE : %'
 13   or str like 'CLIENT USER: %'
 14   or str like 'CLIENT TERMINAL: %'
 15   or str like 'STATUS: %';

no rows selected


That looks like where the problem is. So I match up the criteria with my raw data, and see that the spacing has changed. So I adjust...

SQL>
SQL>
SQL>  select str
  2   from
  3   (
  4    select str, rownum r,
  5    max( case when str like 'Instance name: %' then substr(str,16) end) over () iname,
  6    case when str like '___ ___ __ __:__:__ ____' then rownum end isdate
  7    from et
  8    )
  9    where isdate is not null
 10    or str like 'ACTION :%'
 11    or str like 'DATABASE USER:%'
 12    or str like 'PRIVILEGE :%'
 13    or str like 'CLIENT USER:%'
 14    or str like 'CLIENT TERMINAL:%'
 15   or str like 'STATUS:%';

STR
----------------------------------------------------------------------------------------------------------------------------------
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[75] 'select lower(user) || '@'||lower(instance_name) global_name
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[33] 'alter pluggable database all open'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[39] 'alter pluggable database all save state'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[34] 'alter session set container = pdb1'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'

42 rows selected.

SQL>
SQL>


and things are looking the better.

The rest is left as a user exercise :-)

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.