No audit data seems to be generated in sys.aud$

Recently i encountered a situation in a test environment whereby no audit data seems to be generated in sys.aud$. My AUDIT_TRAIL is set to DB and the audit statements were executed successfully as they were recorded in DBA_OBJ_AUDIT_OPTS and DBA_STMT_AUDIT_OPTS views. After much troubleshooting, I realized that by turning on Pure Unified Auditing feature in Oracle 12c, it actually disables the standard audit features ie prevent the standard audit data being written to sys.aud$ and instead all the audit data will be found in a singe source – unified_audit_trail.

What does audit_sys_operations actually audit?

AUDIT_SYS_OPERATIONS enables auditing of operations issued by user SYS, and users connecting with SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSKM and SYSDG privileges. The audit trail is written to the OS file (can be viewed from Event Viewer for Windows platform) which makes it harder to be tampered by database superusers like SYS, unless he/she has OS superuser privilege (aka SYSADM). A few characteristics observed from the test scenarios that I’ve done:

1) As this is a static parameter, the change in value must be accompanied by “scope=spfile”

2) For changes of static parameter to take effect, the database must be bounced so it is usually changed by user with SYSDBA role

3) As this parameter type is of Boolean type, the value should not be embraced with single quotes which otherwise is treated as a String value (see example below)

SQL> alter system set audit_sys_operations=’true’ scope=spfile;
alter system set audit_sys_operations=’true’ scope=spfile
                                      *
ERROR at line 1:
ORA-00922: missing or invalid option

4) It does not log for normal users like SYSTEM or any ordinary users like SCOTT

5) It does not log all the actions done by the database superuser (eg SYS)    

Some scenarios of audit logging as below: (note: certain info has been masked for privacy sake)

Scenario 1A: user XXX connecting as SYSBACKUP into PDB (belonging to CDB) from NAME@HOST on Windows platform

Source: Oracle.CDB

Audit trail: LENGTH: ‘165’ ACTION :[7] ‘CONNECT’ DATABASE USER:[1] ‘/’ PRIVILEGE :[9] ‘SYSBACKUP’ CLIENT USER:[9] ‘HOST\NAME’ CLIENT TERMINAL:[4] ‘HOST’ STATUS:[1] ‘0’ DBID:[9] ‘554253442’ .

Audit trail: LENGTH: ‘164’ ACTION :[6] ‘COMMIT’ DATABASE USER:[1] ‘/’ PRIVILEGE :[9] ‘SYSBACKUP’ CLIENT USER:[9] ‘HOST\NAME’ CLIENT TERMINAL:[4] ‘HOST’ STATUS:[1] ‘0’ DBID:[9] ‘554253442’ .

 

Scenario 1B: user XXX issued “sho con_name” command

SQL> show con_name

CON_NAME
————————
PDB

Source: Oracle.CDB

Audit trail: LENGTH: ‘311’ ACTION :[151] ‘select DECODE(sys_context(‘USERENV’, ‘CDB_NAME’),NULL,’Non Consolidated’, sys_context(‘USERENV’, ‘CON_NAME’)) NAME_COL_PLUS_PDB_CONTAINER from sys.dual’ DATABASE USER:[1] ‘/’ PRIVILEGE :[9] ‘SYSBACKUP’ CLIENT USER:[9] ‘HOST\NAME’ CLIENT TERMINAL:[4] ‘HOST’ STATUS:[1] ‘0’ DBID:[9] ‘554253442’ .

 

Scenario 1C: user XXX issued an explicit command to check the current container name

SQL> select sys_context(‘USERENV’, ‘CON_NAME’) from dual;

SYS_CONTEXT(‘USERENV’,’CON_NAME’)
———————————————————-
PDB

Source: Oracle.CDB

Audit trail: LENGTH: ‘210’ ACTION :[51] ‘select sys_context(‘USERENV’, ‘CON_NAME’) from dual’ DATABASE USER:[1] ‘/’ PRIVILEGE :[9] ‘SYSBACKUP’ CLIENT USER:[9] ‘HOST\NAME’ CLIENT TERMINAL:[4] ‘HOST’ STATUS:[1] ‘0’ DBID:[9] ‘554253442’ .

 

Scenario 1D: user XXX connecting as SYSASM but not successful

SQL> conn XXX/password@HOST:1522/PDB as sysasm
ERROR:
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Audit trail: LENGTH: ‘168’ ACTION :[7] ‘CONNECT’ DATABASE USER:[6] ‘XXX’ PRIVILEGE :[4] ‘NONE’ CLIENT USER:[9] ‘HOST\NAME’ CLIENT TERMINAL:[4] ‘HOST’ STATUS:[4] ‘1031’ DBID:[9] ‘554253442’ .

This further confirms my understanding of what audit_sys_operations audits – it really does not record the fine-grain auditing data of all the actions performed by the database superuser, instead it records the protocol of ALL the connections attempted by the superuser as well as MOST of the actions performed.   

Oracle 12c – need to start container AND pluggable databases

Not used to the multitenant architecture in Oracle 12c, it took me a while to realize that the Pluggable database may still be in a “MOUNTED” state after starting up the Container database. So need to start up the Pluggable database separately too!

ERROR at line 1:
ORA-01109: database not open

SQL> startup
Pluggable Database opened.

Oracle database could not shutdown properly….

My Oracle database seemed to be in a “hanged” stage and could not be shutdown properly.

In the end, it was successful after issuing “shutdown abort” in a new session :)

 

SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress – connection is not permitted
SQL> startup
ORA-01012: not logged on

SQL> shutdown abort
ORA-01031: insufficient privileges
SQL> startup force
ORA-01012: not logged on

 

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 22 22:44:25 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected.
SQL> startup force
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> shutdown abort
ORACLE instance shut down.

Oracle’s undocumented configuration parameters

There are more than 1000 undocumented configuration parameters in Oracle 11g. Undocumented parameters start with an underscore eg _dyn_sel_est_num_blocks, and only the engineers of the Oracle 11g kernel code knows exactly what it does and what value to set. Oracle Support occasionally uses these parameters to enhance the generation of debug information or to work around a bug in the kernel code. But there cases reported by some Oracle savvy senior DBAs who had used these undocumented parameters to perform restart on corrupted databases.