Datapatch may be slower when Traditional and Unified Auditing are on

I wrote several times especially about Unified Auditing in the past. It is a very efficient and useful way to audit operations in the database. But you need to be a bit careful about what’s on, and what isn’t. Now we recently had a customer case where it was clearly proven that Datapatch may be slower when Traditional and Unified Auditing are on. But how can this happen?

Photo by marcos mayer on Unsplash

Recapping Traditional Auditing

At first, I am neither a security not an auditing expert. But I played quite a bit with Unified Auditing over the years as you can see on the blog. One thing I never liked was the mixed mode for Unified Auditing. I am a fan of linked options and init.ora parameters. They give you control and allow you to handle things transparently.

Traditional Auditing with the AUD$ table is around for a very long time. Recently in a call we came across something Andy Mendelsohn, our Database EVP had written a long while ago when he was a developer. The use of Traditional Auditing (TA) is controlled by initialization parameters such as AUDIT_TRAIL and some others.

For the upgrade from Oracle 11.2 to 12c or any higher release many of you had one significant change to do: Moving the AUD$ table from SYSTEM to SYS user schema. This change was made a requirement for everybody whose database had LABEL SECURITY installed – which got installed into many databases by default in older releases of Oracle.  Luckily, AutoUpgrade automates such thinks for you. So you may have not even recognized this change.

But actually I recognized one important thing in DBCA (Database Configuration Assistant) for many incarnations of the tool: It enables AUDIT_TRAIL=DB by default unless you turn or switch it off manually. The parameter’s default is NONE – but DBCA took the right to change this.

If you want to change this, I highly recommend you to create your own DBCA template. Otherwise you will have to do this every time – except only once for your template.

In DBCA, before you hit CREATE DATABASE, you need to tick ALL INITIALIZATION PARAMETERS.

But the standard view does not show you the offending candidate – unless you click on SHOW ADVANCED PARAMETERS – and then you need to click once on the NAME column to get an alphabetical order. Not very user friendly indeed.

Once you found AUDIT_TRAIL you will see that it is set to DB by default.

You need to select the value and change it manually to NONE.  At least in the 19c DBCA there is no drop down box with available values. But as you see below the parameter, there is a text box telling you about the available values.

Once you overwrite it with NONE, it will be set to NONE in this database you create now. Please note as well that the parameter is placed into your spfile by default. This of course gives you the chance to discover and change it.

Bad news here: In order to change AUDIT_TRAIL for an up-and-running database, you must restart it.

By the way, I checked this until Oracle Database 21c – and the DBCA behavior is exactly the same. I did not check it in Oracle 23c since things will work a bit differently from 23c onward. More later in another blog post once Oracle Database 23c is available on-prem.

Recapping Unified Auditing

As mentioned before, you will find a number of blog posts about Unified Auditing already. There is no doubt that Unified Auditing is much more superior over Traditional Auditing. Just the way how it got enabled made me raise my eyebrows from second one.

You can link it into your kernel – it is not linked in by default. These would be the steps to link it in:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

And you can easily check whether it is linked in with:

SQL> select VALUE from V$OPTION where PARAMETER='Unified Auditing';

   VALUE 
________ 
FALSE

FALSE means: It is not linked into the kernel. Still, the fact that it got enabled in a so called mixed mode made me wonder. Until then I had learned that things are either ON or OFF – but it was news to me that something could be in Schrödinger’s Cat mode. In my case, Unified Auditing is OFF (i.e., not linked into the kernel) but ON at the same time since these two policies are enabled by default:

SQL> select POLICY_NAME, ENABLED_OPTION  FROM audit_unified_enabled_policies;

          POLICY_NAME    ENABLED_OPTION 
_____________________ _________________ 
ORA_SECURECONFIG      BY USER           
ORA_LOGON_FAILURES    BY USER           

Unified Auditing is already ON. Even though it is OFF. Right?

Mixed Mode? Pure Mode? Which Mode?

But to add some clarification since I double checked with the security team:

• Unified Auditing is not linked into the kernel (default)
  • This is called Mixed Mode
  • It allows both, Traditional Auditing and Unified Auditing to be used at the same time
  • All settings of both, TA and UA will be honored
  • You need to act if you want either one of them, or none (or both, which I disrecommend)
• Unified Auditing is linked into the kernel until Oracle Database 21c
  • This is called Pure Mode
  • It will disable all Traditional Auditing functionality
  • TA parameters will not be honored anymore, and can be safely removed from your SPFILE
  • This is actually the preferred option unless you use software which still settles on the traditional audit trail
• Unified Auditing is linked into the kernel from Oracle Database 23c onward
  • This is still called Pure Mode
  • It allows Traditional Auditing functionality to co-exist
  • TA parameters will be honored to allow smooth and flawless migration to Oracle 23c
  • Take care when you deploy a new CDB and migrate a non-CDB into it in case you’d like to have TA settings being active – you must set TA parameters in the 23c container database you are plugging into since there is no “PDB parameter option” available
  • Existing TA policies by default can’t be changed or newly created but only deleted
    • An underscore parameter may allow to override this restriction if needed
  • A migration procedure for TA policies into UA policies is available. Please see MOS Note: 2909718.1

Datapatch may be slower

Now making a long story short, after recapping the two auditing options and repeating that Unified Auditing is actually a very useful and helpful feature, you can sum up 1+1. When both techniques are on by default at the same time, this may lead to trouble. And since both mechanisms can be ON easily at the same time, you may see lots of auditing activity when datapatch kicks in. And of course, the impact can be much higher if you have very strict and granular policies enabled by yourself.

What we saw with a customer case is that patching to 19.17.0 in one specific case took 7 hours. And the it was clear from the logs that some Java calls – the database has OJVM installed in it – took unusually long.

Now what has been the solution?

Turn off traditional auditing by setting AUDIT_TRAIL=NONE. Unfortunately, as explained above already, this requires a restart of the database. But after it got disabled, datapatch completed within the expected normal time frame.

Have only one auditing on – not two of them

So what is your action here? Please ensure that you have only the auditing technique on you want to work with. Not two of them at the same time. And especially not Traditional Auditing and Unified Auditing linked in at the same time.

Normally, unless you use some legacy tools only dealing with the old audit trail in AUD$, I strongly recommend you to check whether AUDIT_TRAIL=NONE – and if it isn’t, turn it to NONE during your next coming maintenance window. If you want to use auditing – which you should when it’s appropriate – then link in Unified Auditing into your kernel, create your auditing policies – and use only this mechanism. We haven’t seen performance issues during patching with Unified Auditing as far as I am aware.

But having two techniques on at the same time can be harmful performance-wise – and it makes no logic sense to me.

Further Links and Information

• AUDIT_TRAIL Parameter
• Use your own DBCA templates to create databases
• Blog Posts about Unified Auditing
• Unified Auditing – is it ON or OFF?
• Schrödinger’s Cat

–Mike