Unified Auditing policies can slow down the upgrade

Unified Auditing policies can slow down the upgradeI posted several blog posts about Unified Auditing in the past. And recently a large customer in the UK alerted me about their upgrades with PDBs from 12.1 to 12.2 taking much longer when Unified Auditing policies were enabled. It looks like as if Unified Auditing policies can slow down the upgrade.

We did some further investigations. See the results below.

Unified Auditing

You find a good number of blog posts explaining how to migrate to Unified Auditing, the performance tweaks in Oracle 12.1.0.2 and some other things:

For the following examples I link in Unified Auditing into my databases with:

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

And then check afterwards that the option is visible:

SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

PARAMETER         VALUE
----------------  ---------- 
Unified Auditing  TRUE

Upgrading a PDB from Oracle 12.1.0.2 to Oracle 18c

In this part I will run three upgrades.

Run 1: No additional audit policies turned on
  • Number of audit records after the upgrade:
    select count(*) from v$unified_audit_trail;
    
      COUNT(*)
    ----------
          7464
  • Audit policies enabled:
    SELECT policy_name, enabled_opt, user_name FROM audit_unified_enabled_policies;
    
    POLICY_NAME		  ENABLED_OPT		USER_NAME
    ------------------------- --------------------- --------------
    ORA_SECURECONFIG	  BY			ALL USERS
    ORA_LOGON_FAILURES	  BY			ALL USERS
  • Total upgrade time:
    Total Upgrade Time:          [0d:0h:18m:55s]
    
         Time: 1143s For PDB(s)
    
    Grand Total Time: 1143s
Run 2: Only these audit policies turned on
  • Number of audit records after the upgrade:
      COUNT(*)
    ----------
          7465
  • Audit policies enabled:
    POLICY_NAME		  ENABLED_OPT		   USER_NAME
    ------------------------- ------------------------ --------------
    ORA_LOGON_FAILURES	  BY			   SYS
    ORA_LOGON_FAILURES	  BY			   SYSTEM
    ORA_SECURECONFIG	  BY			   SYS
    ORA_SECURECONFIG	  BY			   SYSTEM
    ORA_CIS_RECOMMENDATIONS   BY			   SYS
    ORA_CIS_RECOMMENDATIONS   BY			   SYSTEM
    ORA_ACCOUNT_MGMT	  BY			   SYS
    ORA_ACCOUNT_MGMT	  BY			   SYSTEM
    ORA_DATABASE_PARAMETER	  BY			   SYS
    ORA_DATABASE_PARAMETER	  BY			   SYSTEM
    ORA_SECURECONFIG	  BY			   ALL USERS
    ORA_LOGON_FAILURES	  BY			   ALL USERS
  • Total upgrade time:
    Total Upgrade Time:          [0d:0h:19m:43s]
    
         Time: 1191s For PDB(s)
    
    Grand Total Time: 1191s
Run 3: More audit policies turned on
  • Number of audit records after the upgrade:
      COUNT(*)
    ----------
        203367
  • Audit policies enabled:
    POLICY_NAME		  ENABLED_OPT		   USER_NAME
    ------------------------- ------------------------ --------------
    ORA_LOGON_FAILURES	  BY			   SYS
    ORA_LOGON_FAILURES	  BY			   SYSTEM
    ORA_SECURECONFIG	  BY			   SYS
    ORA_SECURECONFIG	  BY			   SYSTEM
    ORA_CIS_RECOMMENDATIONS   BY			   SYS
    ORA_CIS_RECOMMENDATIONS   BY			   SYSTEM
    ORA_ACCOUNT_MGMT	  BY			   SYS
    ORA_ACCOUNT_MGMT	  BY			   SYSTEM
    ORA_DATABASE_PARAMETER	  BY			   SYS
    ORA_DATABASE_PARAMETER	  BY			   SYSTEM
    ORA_RAS_SESSION_MGMT	  BY			   SYS
    ORA_RAS_SESSION_MGMT	  BY			   SYSTEM
    ORA_RAS_POLICY_MGMT	  BY			   SYS
    ORA_RAS_POLICY_MGMT	  BY			   SYSTEM
    ORA_SECURECONFIG	  BY			   ALL USERS
    ORA_LOGON_FAILURES	  BY			   ALL USERS
  • Total upgrade time:
    Total Upgrade Time:          [0d:0h:52m:17s]
    
         Time: 3148s For PDB(s)
    
    Grand Total Time: 3148s

    You can see that the ORA_RAS_POLICY_MGMT policy made the difference.

Upgrading a non-CDB from Oracle 12.1.0.2 to Oracle 18c

In this part I will run only one upgrade with the same auditing policies on as in the Run 3 from above.

  • Number of audit records after the upgrade:
      COUNT(*)
    ----------
        221233
  • Audit policies enabled:
    POLICY_NAME		  ENABLED_OPT		   USER_NAME
    ------------------------- ------------------------ --------------
    ORA_LOGON_FAILURES	  BY			   SYS
    ORA_LOGON_FAILURES	  BY			   SYSTEM
    ORA_SECURECONFIG	  BY			   SYS
    ORA_SECURECONFIG	  BY			   SYSTEM
    ORA_CIS_RECOMMENDATIONS   BY			   SYS
    ORA_CIS_RECOMMENDATIONS   BY			   SYSTEM
    ORA_ACCOUNT_MGMT	  BY			   SYS
    ORA_ACCOUNT_MGMT	  BY			   SYSTEM
    ORA_DATABASE_PARAMETER	  BY			   SYS
    ORA_DATABASE_PARAMETER	  BY			   SYSTEM
    ORA_RAS_SESSION_MGMT	  BY			   SYS
    ORA_RAS_SESSION_MGMT	  BY			   SYSTEM
    ORA_RAS_POLICY_MGMT	  BY			   SYS
    ORA_RAS_POLICY_MGMT	  BY			   SYSTEM
    ORA_SECURECONFIG	  BY			   ALL USERS
    ORA_LOGON_FAILURES	  BY			   ALL USERS
  • Total upgrade time:
    Grand Total Time: 3328s 
    
    Grand Total Upgrade Time:    [0d:0h:55m:28s]

    I know that my upgrade on this server takes usually a little bit more than 20 minutes.

Summary and Workaround

The database upgrade to Oracle 12.2.0.1 and Oracle 18c is much slower when ORA_RAS_POLICY_MGMT policy is enabled. A significant higher number of audit records get written. And this will slow down the upgrade.

Especially phases where statistics generation happens during the upgrade, a lot of audit records get written. Another pattern: As soon as you have more options, the more audit records will be generated, the slower the upgrade performs.

Currently a valid workaround will be to disable the ORA_RAS_POLICY_MGMT policy before upgrade, and enable it after the upgrade.

noaudit policy ORA_RAS_POLICY_MGMT;

Development is has found the root cause of this issue. It gets tracked and solved under Bug 28411230 - 12102->12201 UPGRADE TAKES A VERY LONG TIME WHEN ALL AUDIT POLICIES ARE ENABLED.

The problem results in a wrong value passed to an internal audit vector – which, as part of any upgrades to 12.2. or higher, leads to EVERY action being audited by this policy during upgrade.

Issues is supposed to be fixed in Oracle 20c.

–Mike

 

 

 

Share this: