ORA-65092 when Updating to Oracle 19.13

We are cloning 19.12 and below non-CDB databases to 19.13 PDB using procedure described by Mike Dietrich.

Datapatch fails with the following error message:

Validating logfiles...done
Patch 33192793 apply (pdb PDB): WITH ERRORS
  logfile: /u00/oracle/orabase/cfgtoollogs/sqlpatch/33192793/24462514/33192793_apply_TCDB_PDB_2021Nov02_09_19_59.log (errors)
  -> Error at line 728: script rdbms/admin/backport_files/bug_32583355_apply.sql
      - ORA-65092: system privilege granted with a different scope to 'GGSYS'
      - ORA-06512: at line 105
      - ORA-06512: at line 16

In contrast, datapatch runs correctly when cloning from PDB to PDB.

The patch 32583355 is a part of the release update (RU). It’s an undocumented security patch that revokes unnecessary privileges from GGSYS:

grep -i revoke rdbms/admin/backport_files/bug_32583355_apply.sql
execute immediate 'REVOKE create ANY table FROM ggsys';
execute immediate 'REVOKE alter  ANY index FROM ggsys';
execute immediate 'REVOKE INSERT on SYS.DDL_REQUESTS FROM ggsys';
execute immediate 'REVOKE INSERT on SYS.DDL_REQUESTS_PWD from ggsys';
execute immediate 'REVOKE create database link  FROM ggsys';
execute immediate 'REVOKE select ANY dictionary FROM ggsys';
execute immediate 'REVOKE select ANY table      FROM ggsys';
execute immediate 'REVOKE create ANY table      FROM ggsys';
execute immediate 'REVOKE alter  ANY table      FROM ggsys';
execute immediate 'REVOKE alter  ANY index      FROM ggsys';

The revoke commands failed, presumably, because in 19.13, unlike in the previous RUs, GGSYS doesn’t have any of these privileges in root CDB:

19.13:

select privilege from dba_sys_privs where grantee = 'GGSYS' ;

no rows selected

19.12 and below:

select privilege from dba_sys_privs where grantee = 'GGSYS' ;

PRIVILEGE
----------------------------------------
CREATE ANY TABLE
CREATE DATABASE LINK
SELECT ANY DICTIONARY
ALTER ANY INDEX
SELECT ANY TABLE
ALTER ANY TABLE
UNLIMITED TABLESPACE

7 rows selected.

This seems to be an unknown issue.

I figured out a workaround by analyzing the SQL patch. I exploited the feature that the patch suppresses the exception if the privilege isn’t granted, for example:

...
BEGIN
  execute immediate 'REVOKE create database link  FROM ggsys';
EXCEPTION   WHEN OTHERS THEN
  IF SQLCODE IN (-1952) THEN NULL;
	ELSE RAISE;
  END IF;
END;
...

1952 is the error code when you try to revoke the privilege which isn’t granted:

oerr ora 1952
01952, 00000,  "system privileges not granted to '%s'"
// *Cause:  A system privilege you tried to revoke was not granted to the user.
// *Action: Make sure the privileges you are trying to revoke are granted.

Consequently, if you revoke the privileges before cloning the database, the patch will run without errors:

REVOKE create ANY table FROM ggsys ;
REVOKE alter  ANY index FROM ggsys;
REVOKE INSERT on SYS.DDL_REQUESTS FROM ggsys;
REVOKE INSERT on SYS.DDL_REQUESTS_PWD from ggsys;
REVOKE create database link  FROM ggsys;
REVOKE select ANY dictionary FROM ggsys;
REVOKE select ANY table      FROM ggsys;
REVOKE create ANY table      FROM ggsys;
REVOKE alter  ANY table      FROM ggsys;
REVOKE alter  ANY index      FROM ggsys;

This isn’t an official workaround, so check with Oracle Support prior to using it. I’m, though, confident that this procedure is safe.