Home » RDBMS Server » Server Administration » can not audit entry in adump (oracle 10g, Red Hat Linux 5.3)
can not audit entry in adump [message #561908] Thu, 26 July 2012 04:35 Go to next message
morad_dba
Messages: 72
Registered: June 2008
Member

Dear all,

I am trying to enable auditing as

SQL> alter system set audit_trail=OS SCOPE=SPFILE;

System altered.

SQL> STARTUP FORCE
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string OS
SQL>

SQL> create user apexos identified by abc1;

User created.

SQL> grant connect, resource to apexos;

Grant succeeded.

SQL> audit select table, insert table by apexos by access;

Audit succeeded.

SQL> audit table by apexos by access;

SQL> SELECT audit_option, failure, success, user_name
FROM dba_stmt_audit_opts;

AUDIT_OPTION FAILURE SUCCESS USER_NAME
---------------------------------------- ---------- ---------- ------------------------------
TABLE BY ACCESS BY ACCESS APEXOS
SELECT TABLE BY ACCESS BY ACCESS APEXOS
INSERT TABLE BY ACCESS BY ACCESS APEXOS

cONN APPOS/ABC1

SQL> CREATE TABLE TAB1 (ID NUMBER, NAME VARCHAR2(20));

Table created.

SQL> insert into tab1 values (10, 'Michel');

1 row created.

SQL> insert into tab1 values (30, 'Andrew');

1 row created.

SQL> select * from tab1;

ID NAME
---------- --------------------
10 Michel
30 Andrew

SQL> /

ID NAME
---------- --------------------
10 Michel
30 Andrew

SQL>

SQL> select username, timestamp, action_name, action, SES_ACTIONs, sql_text
2 from USER_audit_trail where username='APEXOS';

no rows selected

SQL>

I also did not find any file contiaing the above statement as audit record in
/u01/app/oracle/admin/orcl/adump.

There are numerous old file in the /u01/app/oracle/admin/orcl/adump locaton. But When I executed the sql statement then that time no audit file was not generated in the location.

Please help me ... how to find audit record.

Regards,
Morad.



Re: can not audit entry in adump [message #561914 is a reply to message #561908] Thu, 26 July 2012 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ "STARTUP FORCE", do NOT use that just shutdown and then startup, it is not so painful to write 2 commands and it is at least far painless than to have to restore and recover your database because of a corruption.

2/ "grant connect, resource to apexos", do NOT use predefined roles, create your own, it is not so painful to create a role and grant it the appropriate privilege for your usage.

As we say in French "Laziness is the source of all evil".

3/ "no rows selected", expected

4/ "There are numerous old file in the /u01/app/oracle/admin/orcl/adump locaton. But When I executed the sql statement then that time no audit file was not generated in the location." Maybe an old file was reused. Drop all of them, restart the instance (with shudown+startup) and retry.

Regards
Michel
Re: can not audit entry in adump [message #561929 is a reply to message #561914] Thu, 26 July 2012 05:14 Go to previous messageGo to next message
morad_dba
Messages: 72
Registered: June 2008
Member
Dear Michel,

I shudown database and deleted all audit files and started database. and I issued the sql statement as

SQL> CREATE TABLE TAB1 (ID NUMBER, NAME VARCHAR2(20));

Table created.

SQL> insert into tab1 values (10, 'Michel');

1 row created.

SQL> insert into tab1 values (30, 'Andrew');

1 row created.

SQL> select * from tab1;

ID NAME
---------- --------------------
10 Michel
30 Andrew

SQL> /

ID NAME
---------- --------------------
10 Michel
30 Andrew

SQL> /

ID NAME
---------- --------------------
10 Michel
30 Andrew

SQL> /

ID NAME
---------- --------------------
10 Michel
30 Andrew

SQL>


And I read audit file in the /u01/app/oracle/admin/orcl/adump

[oracle@DBTEST adump]$ cat ora_11525.aud

Thu Jul 26 17:27:46 2012
SESSIONID: "24830" ENTRYID: "7" STATEMENT: "8" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "1" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle" PRIV$USED: 40

Thu Jul 26 17:27:53 2012
SESSIONID: "24830" ENTRYID: "8" STATEMENT: "9" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "2" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"

Thu Jul 26 17:27:58 2012
SESSIONID: "24830" ENTRYID: "9" STATEMENT: "10" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "2" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"

Thu Jul 26 17:28:07 2012
SESSIONID: "24830" ENTRYID: "10" STATEMENT: "11" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"

Thu Jul 26 17:28:10 2012
SESSIONID: "24830" ENTRYID: "11" STATEMENT: "12" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"

Thu Jul 26 17:30:24 2012
SESSIONID: "24830" ENTRYID: "12" STATEMENT: "13" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"

Thu Jul 26 17:30:27 2012
SESSIONID: "24830" ENTRYID: "13" STATEMENT: "15" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"


So how can i customize and format the output ...

pls help me

Regards,

Morad.

Re: can not audit entry in adump [message #561938 is a reply to message #561929] Thu, 26 July 2012 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no way to customse it in Oracle.
You can modify it afterwards as you want using the usual sed, awk, cut... or perl programs.
It is no so hard to convert it a csv file, for instance.

Regards
Michel
Re: can not audit entry in adump [message #561958 is a reply to message #561938] Thu, 26 July 2012 06:56 Go to previous messageGo to next message
morad_dba
Messages: 72
Registered: June 2008
Member

Ok, Thanks.
Re: can not audit entry in adump [message #561959 is a reply to message #561958] Thu, 26 July 2012 07:02 Go to previous message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post (attach) your audit file, if I have a moment I will try to create a script to convert it.

Regards
Michel
Previous Topic: ORA-00942: table or view does not exist
Next Topic: dbstart/dbstop named listener
Goto Forum:
  


Current Time: Thu Oct 02 04:04:27 CDT 2014

Total time taken to generate the page: 0.10400 seconds