czwartek, 16 maja 2013

Audit queries on Read Only standby

I was asked about the possibility of audit queries on the physical standby database
 open in read mode.

Just turn on the audit select on primary  database:

AUDIT ALL BY TEST_USER BY ACCESS;
AUDIT SELECT TABLE BY TEST_USER BY ACCESS;


Standby is open in read only mode  and audit trails can't be written to the database since it's read only. So, where?

Audit trail automaticly switch to OS files when database is open in read only mode.

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
audit_trail                          string      OS
 


 Here is a example of audit trail file:

Thu May 16 11:57:09 2013 +03:00
LENGTH: "253"
SESSIONID:[10] "4294967295" ENTRYID:[2] "13" STATEMENT:[1] "8" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$N
AME:[4] "DUAL" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "257"
SESSIONID:[10] "4294967295" ENTRYID:[2] "14" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[7] "X$KCCDI" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "258"
SESSIONID:[10] "4294967295" ENTRYID:[2] "15" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[8] "X$KCCDI2" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "262"
SESSIONID:[10] "4294967295" ENTRYID:[2] "16" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[11] "GV$DATABASE" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "261"
SESSIONID:[10] "4294967295" ENTRYID:[2] "17" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[10] "V$DATABASE" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "262"
SESSIONID:[10] "4294967295" ENTRYID:[2] "18" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[11] "V_$DATABASE" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:32 2013 +03:00
LENGTH: "225"
SESSIONID:[10] "4294967295" ENTRYID:[1] "1" USERID:[2] "TEST_USER" ACTION:[3] "101" RETURNCODE:[1] "0" LOGOFF$PREAD:[1] "5" LOGOFF$LREAD:[3] "125" LOGOFF$LWRITE:[1] "0" LOGOFF$DEAD:[1] "0" DBID:[10
] "1234567890" SESSIONCPU:[1] "1"