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
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
Stephane,
this is interesting. From which release where you upgrading and which grants did you miss?
Thanks in advance – cheers,
Mike
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