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"
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"