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