Automatic Maintenance Jobs are enabled after upgrade

This is a strange behavior – but it seems as automatic maintenance jobs are enabled after upgrade. A customer (thanks Naveen!!) sent me an email the other week asking if there’s a flag in DBUA to prevent this enabling as on some of their databases the automatic maintenance jobs are disabled on purpose.

Automatic Maintenance Jobs are enabled after upgrade

It sounded kind of strange to me – and my first test was to use the catctl.pl instead of the DBUA.

First of all I did check the status of the Automatic Maintenance Jobs in my 11.2.0.4 database:

SELECT client_name, status FROM dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
sql tuning advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED

So all three standard jobs are enabled. I disabled them now:

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);

And verified that they are really disabled:

SQL> SELECT client_name, status FROM dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
sql tuning advisor                                               DISABLED
auto optimizer stats collection                                  DISABLED
auto space advisor                                               DISABLED

Afterwards I started upgrades to Oracle 12.1.0.2 and Oracle 12.2.0.1 – and in all cases, regardless of catctl.pl or DBUA been used, the result looked like this:

SELECT client_name, status FROM dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
sql tuning advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENAB

What is happening here?

Actually this change came in with Oracle 12.1.0.1 – and we can’t find out anymore why the change came in as the person who implemented it is not at Oracle anymore.

In the script execsvrm.sql in ?/rdbms/admin the following call:

DECLARE
  DUPLICATE_KEY exception;
  pragma EXCEPTION_INIT(DUPLICATE_KEY, -1);
BEGIN
dbms_auto_task_admin.default_reset('ALL');
EXCEPTION
  WHEN DUPLICATE_KEY THEN NULL;
  WHEN OTHERS THEN RAISE;
END;
/

resets the setting for the automatic maintenance jobs – regardless of their value before. And after upgrade the default jobs are enabled again.

How to prevent this?

The best thing you could do: Adjust the status manually after the upgrade based on your previous settings (see the statements above). We agreed internally that this should be fixed and filed Bug 27593501 – AUTOMATIC MAINENANCE JOBS GET ENABLED DURING UPGRADE – EVEN IF DISABLED BEFORE for it.

Thanks as well to Hector helping to identify the root cause.

Important Update – Sept 24, 2018

Thanks to the responsible development team, this behavior gets fixed in Oracle 19c and backported to 12.2.0.1 and 18c.

–Mike

7 thoughts on “Automatic Maintenance Jobs are enabled after upgrade

  1. Hi,

    we noticed this behavior, and also something additional, XA grants seem to be removed as well during the upgrade. The grants on PENDING_TRANS$, DBA_2PC_PENDING and DBA_PENDING_TRANSACTIONS which are needed by the xa_recover are gone after upgrade, we had to apply them again. Take note of your grants before upgrading, or grant select to public on these objects (unless there is an issue with this).

    Regards
    Stéphane

  2. Mike,

    we are upgrading from 11.2.0.4, and the following grants were present in 11g:

    GRANT SELECT ON PENDING_TRANS$ TO PUBLIC;
    GRANT SELECT ON DBA_2PC_PENDING TO PUBLIC;
    GRANT SELECT ON DBA_PENDING_TRANSACTIONS TO PUBLIC;

    They are gone after upgrade. We are using Java code and these grants are needed to recover XA transactions (usually the Java code dequeues a MQ message and interacts with Db, if anything goes wrong, both operations are rolled back).

    Stéphane

    • Hi Stephane,

      let me first ask which patch level your 11.2.0.4 database were on?
      I did check in my 11.2.0.4 dbs which are on July 2018 PSU and this is the result:

      select table_name from dba_tab_privs where grantee=’PUBLIC’ and PRIVILEGE=’SELECT’ and table_name like ‘DBA%’ order by 1

      TABLE_NAME
      ——————————
      DBA_AUTO_SEGADV_CTL
      DBA_AUTO_SEGADV_SUMMARY
      DBA_COL_PENDING_STATS
      DBA_DBFS_HS_FIXED_PROPERTIES
      DBA_EDITIONING_VIEW_COLS
      DBA_EDITIONING_VIEW_COLS_AE
      DBA_FLASHBACK_ARCHIVE
      DBA_FLASHBACK_ARCHIVE_TABLES
      DBA_FLASHBACK_ARCHIVE_TS
      DBA_IND_PENDING_STATS
      DBA_SCHEDULER_REMOTE_DATABASES
      DBA_SYNC_CAPTURE_TABLES
      DBA_TAB_HISTGRM_PENDING_STATS
      DBA_TAB_PENDING_STATS
      DBA_TAB_STAT_PREFS
      DBA_TSTZ_TABLES
      DBA_XMLSCHEMA_LEVEL_VIEW

      There is a change in 12.2 (haven’t verified for 12.1) – this query reports zero rows there.

      Cheers,
      Mike

      • Hi Mike,

        these were not grants that were present by default, but grants that we added manually to support the XA recover operation, otherwise some Java code fails with XA_RMERR. We have noticed that the grants added on these objects are gone after upgrading. I think we’re not the only one adding grants on these objects to support XA. The only problem is that you notice that grants are missing only when it fails.

        Stéphane

        • Thanks Stephane!

          Now it makes sense. I see your point. Let me check which part of the upgrade scripts take away the privileges. I know that this is intended due to security constraints with the PUBLIC “schema”. That’s why in 12.2 onwards there are no such grants to public by default from our side. And of course we “clean up” your grants as well.

          Let me figure this out – I’ll get back to you.

          Cheers,
          Mike

          • Stephane,

            I received an answer from our security team.

            And as I assumed already, the privileges got revoked due to a security issue.

            “Since there is a known security vulnerability related to SELECT privilege on objects, we are granting READ object privilege on the object to PUBLIC and revoking the SELECT object privilege so that customers’ read operations on the object continue to work.”

            Does the READ privilege help you or cause any other trouble?

            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.