I 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:
- Unified Auditing – Is it ON or OFF in Oracle 12.2.0.1?
- Unified Auditing – Is it ON or OFF in Oracle 12.1.0.2?
- Unified Auditing – Performance Improvements in Oracle 12.1.0.2
- How to migrate to Unified Auditing?
- Don’t drop the AUDSYS user before upgrading
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
