Don’t drop the AUDSYS user before upgrading

Don't drop the AUDSYS user before upgradingThis 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:

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

2 thoughts on “Don’t drop the AUDSYS user before upgrading

  1. Hi Mike,
    Thank you for update. Preupgrade tool does not show it as for remove or review and I was not sure I it has to be done.

    Someone from ORACLE should review upgrade document. It is more mistakes here. For example
    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/upgrade-scenarios-non-cdb-oracle-databases.html#GUID-D6F6D375-B021-47E0-B0C8-B2C0B191AC0D
    1) dbupgrade is not located in $ORACLE_HOME/rdbms/admin ( and it is not located here: $ORACLE_HOME/rdbms/bin on Linux )
    2) utllu122s.sql : there is no such script
    SQL> @$ORACLE_HOME/rdbms/admin/utllu122s.sql

    I know is just mistake but it does not look good.

    Question : upgrade utility in the emulation mode – what kind of errors we can see here ?

    Thank you,
    Leszek

    • Leszek,

      I’ll pass your feedback on to our doc writer.

      Thanks and sorry for the inconvenience.

      Actually utlu122s.sql is an old post upgrade script which is not useful anymore as the content gets written anyways into upg_summary.log.
      But in my 12.2 environment it exists:

      $ ls -lrt utlu*
      -rw-r–r–. 1 oracle dba 2253 Dec 1 2012 utlupox.sql
      -rw-r–r–. 1 oracle dba 11144 Feb 21 2014 utlurl.sql
      -rw-r–r–. 1 oracle dba 21489 May 13 2016 utlusts.sql
      -rw-r–r–. 1 oracle dba 1079 Aug 8 2016 utlu122s.sql
      -rw-r–r–. 1 oracle dba 3787 Jul 7 2017 utluptabdata.sql
      [CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin

      It’s not necessary to use it anymore.

      “dbupgrade” is located in $ORACLE_HOME/bin, at least on all my test environments:

      $ ls -lrt dbupgrade
      -rwxr-x—. 1 oracle dba 3005 Jan 26 2017 dbupgrade
      [CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/bin

      but of course not in $ORACLE_HOME/rdbms/bin as no such directory exists.

      Thanks again!

      Cheers,
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.