Value FROM v$option where parameter = ‘Unified Auditing’

Hi all,
So, just a quick post to clarify once it cost me a few mins to explain this to a client and, indeed, Oracle could have made it easier.

So, facing the info below, doesn’t mean that you don’t have Unified Auditing enabled:

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

It does mean you just don’t have PURE Unified auditing, but a MIXED configuration, where old fashioned Auditing is also accepted.

See, this way, the MIXED Auditing is by default it is enabled in 12c. The idea is that you use apart from traditional auditing all the features of unified auditing. Once we are comfortable with the unified concept, we can migrate exiting audit setup to unified policy , we can enable pure auditing. This serves as a good mediator for an easy and hassle free switch to the preferred Unified auditing.

HOWEVER, let’s agree that Oracle could have made the value column a little bit m ore meaningful, if this was the case. The actual values actually mean:


OK, but what if I want to enable ONLY the PURE Auditing, disabling the ‘Classic’ Auditing and changing this value on v$option to TRUE? Well, then you need to relink library for your instance. Note, for RAC linking need to be done all the nodes.

Steps to Change to PURE Unified Auditing Only:

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

sqlplus / as sysdba <<
shutdown immediate;

cd $ORACLE_HOME/rdbms/lib
make -f unaiaud_on ioracle
sqlplus / as sysdba<<
SELECT value FROM v$option WHERE parameter = 'Unified Auditing';


Hope it helps, cheers!

Everything Stopped on ORA-02002!

Hi all,
So, I got contacted by a client in emergency because no more sessions were being opened on database with error ORA-02002. Even worse, everything stopped working and started raising this error.

Unusual, right?

So , checking on this trace generated for error here is what I found:

ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8 in tablespace AUDIT_TBS

Seems client has moved the AUD$ to another tablespace to avoid filling SYSTEM, just like described in this post. The tablespace got full, however, due bad monitoring, it got full.

As the auditing facility was unable to write to the AUDIT_TRAIL table, SQL statements that were currently being audited also failed.

What to do?

  1. Turn off auditing for the database
  2. Restart database
  3. Add space to tablespace
  4. Re-enable auditing, if required.

To avoid application back and forth, I did it in restrict mode, until get all fixed.

To avoid this sort of issue:

  • Be sure you are monitoring properly the new tablespace.
  • Place the audit tablespace on a reliable disk location and perhaps mirrored for protection.

Hope it helps,

Moving Audit Trails (AUD$ and FGA_LOG$) to a Different Tablespace

Hi all!
There is one thing that is always true for any version  of the Oracle Database: the audit trail simply keeps growing and growing.

Some important facts in this regard:
– It’s based on activity, not the amount of data changed.
– The default location of the database audit trail is the AUD$ table, in the SYSTEM tablespace.
– As you know, If this tablespace gets filled up there could be serious consequences.
– The same concern is true for FGA_LOG$, which is located in the SYSAUX tablespace.

Ok, so if you could move these two tables  to specific tablespaces, the root problem would be solved, right?

From 11g R2 on you can move the some or all audit trails to other tablespaces you choose. How?

To move the AUD$:

audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'AUDIT_TBS');

To move the FGA_LOG$:

audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std,
audit_trail_location_value => 'AUDIT_TBS');


There is only one important thing to keep in mind:  This tablespace needs to be online when the database is open. Otherwise, if any auditable operation try to be executed, you’ll get an ORA-02002.

This is something recommendable if you are setting the Auditing for your database. This is also something included Oracle in scripts like the Exacheck.

Hope it helps!

Oracle Database Licensing: First Step!

Oracle licensing is always a complex question, right?
I made some search about it today and decided to share, on quick mode. As I usually do. I focused on Database, by the way. 🙂

The first step is to understand Features vs Options vs Packs relation. Oracle documentation is always good for that. I recommend you to spend some time on Database Licensing Information User Manual.

Ok, now the best way to undestand how evaluate my environment is searching on Oracle Support, right?
And it do not disappoint: Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2 and 12c (Doc ID 1317265.1)
In this note you can get a complete and actual script used to evaluate features/options/packs utilization (options_packs_usage_statistics.sql). This is a good way if you are preparing for an auditing…


I made some simple queries to validate/understand results from Oracle Script. So, if you don’t have access to Oracle Support, it might help you:

Get Features usage:

FROM   dba_feature_usage_statistics u1
WHERE  u1.version = (SELECT MAX(u2.version)
FROM   dba_feature_usage_statistics u2
AND    u1.dbid = (SELECT dbid FROM v$database)
--WHERE DETECTED_USAGES>0 -- To get used features only

Get Options usage:

col parameter for a50
select parameter,value from v$option
--where value='TRUE' -- To get used options only

Information about Session license limits:

SELECT sessions_max s_max,sessions_warning s_warning,
sessions_current s_current,sessions_highwater s_high,users_max
FROM v$license;

Information about CPU license limits:

select cpu_count_current, cpu_core_count_current,CPU_SOCKET_COUNT_CURRENT,
FROM v$license;

An interesting point is that you can disable and enable options through the command chopt. But, you must to get database down first. Example to disable partitioning option:

chopt disable partitioning

The complete explanation and examples (including right values to activate/deactivate options) can be found on Oracle Database Postinstallation Tasks – Enabling and Disabling Database Options.

Some time ago I wrote a post about evaluating Database license in all database park through OEM. It remains valid, I recommend you take a look in this post too.


KB and other interesting stuffs:
Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2 and 12c (Doc ID 1317265.1)
12c Release 1 Database Licensing Information User Manual
11g Release 1 Database Licensing Information User Manual
Enabling and Disabling Database Options
Excellent and actual presentation by Martin Berger
Article about top license pitfalls. Good to reflection. Written by OMTCO Consulting