Hello together,
at a OEL 6 servier I've installed a 11.2.0.4 and a 12.1.0.2 home. For the active monitoring for ORA-Errors I've write a trigger to get a report of them.
At two databases on that server, one 11.2.0.4 and one 12.1.0.2, I've a lot of ORA-01017 errors. So I wanted to know, which user does the incorrect logins. At 11g I've saved me the statement
SELECT "USERNAME", "OS_USERNAME", "USERHOST", "EXTENDED_TIMESTAMP", "TERMINAL", "TIMESTAMP", "ACTION_NAME", "LOGOFF_TIME", "LOGOFF_LREAD", "LOGOFF_PREAD", "LOGOFF_LWRITE", "LOGOFF_DLOCK", "SESSIONID",
"RETURNCODE", "CLIENT_ID", "SESSION_CPU", "PROXY_SESSIONID", "GLOBAL_UID", "INSTANCE_NUMBER", "OS_PROCESS" FROM "SYS"."DBA_AUDIT_SESSION"
WHERE returncode != 0 AND TIMESTAMP > sysdate-1;
which will ask the DBMS_AUDIT_SESSION for entries. There I will see all of the wrong logins from today.
So I've tried the same at the 12.1.0.2 database, but the table is empty here. Also when I'm trying to reproduce the error it is still keeping empty.
The own difference I can see are in these parameters. At the 11.2.0.4 database:
SYS@apex11D> show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /usr/local/oracle/admin/apex11
D/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
SYS@apex11D>
and at the 12.1.0.2 database:
SYS@myhr12D> show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /usr/local/oracle/admin/myhr12
D/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_size integer 1048576
SYS@myhr12D>
Does the parameter audit_sys_operations make the difference? Or is 12c saving the information, that I need at another/new table in the database which I didn't know? Of course I've also tried it without the WHERE criteria for TIMESTAMP like this:
SELECT "USERNAME", "OS_USERNAME", "USERHOST", "EXTENDED_TIMESTAMP", "TERMINAL", "TIMESTAMP", "ACTION_NAME", "LOGOFF_TIME", "LOGOFF_LREAD", "LOGOFF_PREAD", "LOGOFF_LWRITE", "LOGOFF_DLOCK", "SESSIONID",
"RETURNCODE", "CLIENT_ID", "SESSION_CPU", "PROXY_SESSIONID", "GLOBAL_UID", "INSTANCE_NUMBER", "OS_PROCESS" FROM "SYS"."DBA_AUDIT_SESSION"
WHERE returncode != 0;
But still no entries.
Thanks for help and support.
Thanks and regards,
David