1. Global Data Services Concepts and Administration
  2. Invalid Objects Related Issues

Invalid Objects Related Issues

Database Release Update (DBRU) patches can invalidate GSMADMIN_INTERNAL objects; verify schema empty, drop or recreate with scripts, recompile, validate, and drop SYS duplicates.

After installing a Database Release Update (DBRU) or individual patches, some objects in the GSMADMIN_INTERNAL schema can become invalid in the database as a whole or in individual containers (PDBs). For example, assume the query below is issued after a DBRU:

select owner, object_name, object_type, status from dba_objects
where status='INVALID' and owner='GSMADMIN_INTERNAL' order by owner, object_name, object_type;
OWNER                 OBJECT_NAME               OBJECT_TYPE      STATUS     CON_ID
-----------------     ----------------------    -------------    -------    ------
GSMADMIN_INTERNAL     DBMS_GSM_POOLADMIN        PACKAGE BODY     INVALID    3
GSMADMIN_INTERNAL     CAT_ROLLBACK_TRIGGER      TRIGGER          INVALID    3
GSMADMIN_INTERNAL     REQUEST_DELETE_TRIGGER    TRIGGER          INVALID    3
GSMADMIN_INTERNAL     DONE_TRIGGER              TRIGGER          INVALID    3
...

The datapatch script execution failed due to an Invalid Objects error on the GSMADMIN_INTERNAL schema. To resolve the invalid object issues, re-running Oracle-provided scripts is necessary. Complete these steps:

  1. Ensure that the GSMADMIN_INTERNAL schema does not contain any user data.

If the Global Data Services (GDS) or Oracle Sharding features are in use, the GSMADMIN_INTERNAL schema may contain important configuration data.

Run the following query as SYS to verify that the schema is empty. For container databases (CDBs), this query MUST be run in CDB$ROOT and in all pluggable databases (PDBs).

select count(*) from gsmadmin_internal.cloud;

If this query returns 0 (zero), then move on to the next step. If it returns non-zero, contact Oracle Customer Support for further guidance.

  1. Connect to the database as SYS using SQL*Plus. If the database is a CDB, connect to CDB$ROOT, not to any individual PDB.
  2. Run the following commands:
SQL> spool /tmp/invalids.out
SQL> alter session set "_ORACLE_SCRIPT" = true;
SQL> drop user gsmadmin_internal cascade;
SQL> alter session set "_ORACLE_SCRIPT" = false;
SQL> @?/rdbms/admin/catgwm.sql
SQL> @?/rdbms/admin/dbmsgwm.sql
SQL> @?/rdbms/admin/catgwmcat.sql
SQL> @?/rdbms/admin/prvtgwm.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
  1. Validate that there are no invalid objects owned by GSMADMIN_INTERNAL by running the following query:

select owner, object_name, object_type, status from dba_objects where status='INVALID' and owner='GSMADMIN_INTERNAL' order by owner, object_name, object_type;

If no rows are returned, then the GSMADMIN_INTERNAL schema is now correct.

If there are still invalid objects, then contact Oracle Customer Support for further guidance and provide the output from the scripts as found in /tmp/invalids.out.

  1. Finally, in certain instances, it is possible that some GSMADMIN_INTERNAL objects have been incorrectly created in the SYS schema.

These objects should therefore be dropped since they are duplicates of the correct objects in the GSMADMIN_INTERNAL schema.

To identify objects owned by SYS which should be dropped, run the following query as SYS: select object_name, object_type from dba_objects where owner = 'SYS' and (object_name, object_type) in (select object_name, object_type from dba_objects where owner = 'GSMADMIN_INTERNAL') order by object_name, object_type;

This will generate a list of all objects owned by SYS that are also in GSMADMIN_INTERNAL.

Since these objects were created in the wrong schema, they should all be dropped using the appropriate DROP commands from SQL*Plus.

Re-running this query should return 0 (zero) rows when all listed objects have been dropped.

Parent topic: Troubleshooting Global Data Services