Don’t drop the AUDSYS user before upgrading

This is a very interesting case Martin Berger sent to me a week ago. A colleague and him were basically wondering if they should follow the advice in the Oracle 12.2 upgrade guide and drop the AUDSYS user, or if they better don’t drop the AUDSYS user before upgrading.

What is AUDSYS meant for?

In short, it is the auditing user schema for Oracle 12c and especially Unified Auditing. You’ll find a bit more information regarding Unified Auditing on this blog:

• Unified Auditing – is it ON or OFF in Oracle 12.1.0.2?
• Unified Auditing – is it ON or OFF in Oracle Database 12.2.0.1?
• Unified Auditing – Performance Improvements in Oracle 12.1.0.2
• How to migrate to Unified Auditing?

What did the Upgrade Guide recommend?

The Oracle Database 12.2 Upgrade Guide had this section:

Remove the AUDSYS schema and the AUDIT_ADMIN and AUDIT_VIEWER roles. At this stage, there should be no AUDSYS schema. If you want to be able to back up from the upgrade to a previous release, then before you start this procedure, back up your existing audits, perform a SQL query to select from the UNIFIED_AUDIT_TRAIL view, and insert that output into your own table, using similar definitions.

This is/was actually misleading. There’s only one case when you have to remove the AUDSYS schema before upgrading to Oracle Database 12c: When you created an schema named AUDSYS by yourself. In all other cases you should not drop the schema.

And as we all agreed on this, the particular section will disappear from the Upgrade Guide within the next revision of it. This had been logged as a documentation bug in the past (thanks Byron for pointing me to it) but must have slipped through the cracks as it was supposed to be fixed already:

Bug 26423233 : DO NOT DROP AUDSYS USER, AUDIT_ADMIN,AUDIT_VIEWER ROLES WHEN UPGRADING FROM 12.1

Furthermore, Martin pointed me to this section in the PL/SQL Packages and Types Reference Guide:

26.7.21 TRANSFER_UNIFIED_AUDIT_RECORDS Procedure
This procedure transfers unified audit records that were in a pre-upgraded Oracle database to an internal relational table that is designed to improve read performance.
Usage Notes:
It is not mandatory to run DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS after an upgrade, but for better read performance of the unified audit trail, Oracle highly recommends that you run this procedure. The DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS is designed to be a one-time operation, to be performed after you upgrade from Oracle Database 12c release 12.1.

And again, there’s no need for you to migrate your audit records into the new structure. The operation may be resource intense and I’d rather export the existing records and store them somewhere, or let them sit where they are instead of starting a move.

Thanks again for bringing this to our attention!

–Mike