Monthly Archives: January 2014

PURGE DBA_RECYCLEBIN System Privilege in Oracle 12c

PURGE DBA_RECYCLEBIN  command empties the recyclebin at  database level i.e. purges the recyclebin for all users in the database.  Until 11g , you need to have powerful  SYSDBA system privilege to execute this command.  To some extent it made sense as this option was mainly used for upgrades or migration or to apply DST patch. One of focus areas of Oracle12c is separation of duties resulting in  new roles like SYSKM for wallet management, SYSBACKUP for backups and restore, SYSDG  for administration of Data Guard.

Along the same lines, We have new privilege  PURGE DBA_RECYCLEBIN that helps with implementing separation of duty. Users granted the PURGE DBA_RECYCLEBIN system privilege can purge the recyclebin for all users in the database using the PURGE DBA_RECYCLEBIN command.

Unified Auditing (Part-I)

Increased scrutiny of Organizations adherence to  regulatory guidance like SOX, Hippa & PCI DSS has made audit of   critical and privileged actions in the database more likely to be mandatory than optional. Therefore forcing database or software vendors to implement auditing with piecemeal approach. Oracle is no different with various  ways to audit  & various  locations  to store them in various formats.  For example some of the auditing options available with Oracle 11g are mandatory auditing, standard database auditing , Data Vault auditing & FGA and so on ….

Staying complaint is expensive , time consuming and complex. Some of the complexities involved with auditing has been simplified with unified auditing available with Oracle12c. In this blog , we will discuss about Unified auditing in 4 part series. Here is the 1st part.

So what is unified auditing ?
Unified Auditing is selective and effective auditing  of database actions to a single unified audit trail  inside of the database using policies and conditions.  Most of unified auditing features are implemented using DBMS_AUDIT_MGMT or Oracle Grid Control. DBMS_AUDIT_MGMT is not a new to some/most of us as it is available with Oracle11g. It is enhanced in Oracle12c to support unified auditing.  Unified auditing supports auditing of following components.

  1. FGA
  2. Real Application Security
  3. RMAN
  4. Database Vault
  5. Oracle Label Security
  6. Data Mining
  7. Data Pump
  8. SQL*Loader

My next question why do we need a unified auditing? What is wrong with existing auditing?  Why couldn’t we audit everything in the database and get done with it and make our auditors happy. What actually stopped us from auditing everything in the database? The main reasons were performance impact caused by auditing followed by security.  Now we have the burden on us to prove that all our audit is safe, correct and not tampered with. And finally where do I store all the audit data ; Getting additional storage for database itself is cumbersome…. Now you need space to store all of audit data.

Let us look at how unified auditing measures  itself against the 3 metrics: Performance, Security & Storage


As per Oracle, Unified auditing  results in  negligible overhead. Why ? Because it  is implemented in SGA using named Queues. Dequeing is continuous with each audit client (Example: RMAN, Oracle Label Security) having two separate queues so that the client can continue to write to the second queue while the first queue is being dequeued to database table by background process GEN0. if needed audit Data can also be manually flushed to the database using procedure FLUSH_UNIFIED_AUDIT_TRAIL.


As usual we have the choice of synchronous and asynchronous dequeuing. In  unified audit terms, they are called as Immediate write mode and queued write mode.

Immediate-Write mode

Audit records are immediately written to the trail and there is a performance overhead associated with this approach


Queued-Write mode(Default)

The named queue in the SGA is  dequeued periodically. In this mode , data could be lost after an instance crash and therefore its a trade-in  for data loss for performance. How cruel …. never can have everything


Summary: Performance is better mainly because of  queued-write mode architecture; Using immediate-write mode will cause performance impact but not as significant pre12c auditing.



Let us look at other metrics in Part-II of this series. I will post the link once  I am ready.