Invalid objects exists in prerequisite checks of DBUA 11g > 12c

[former_member248376]

Hi all, I'm in the middle of a database upgrade from 11g to 12c and after doing all the preparation steps prior to the upgrade and starting the Database Upgrade Assistant, the prerequisite checks shows that there are still invalid objects in the database. I executed the utlprp.sql script in order to solve the issue (as stated in the SAP Oracle Upgrade Manual) and although it says it finishes without errors, checking the execution I found this message:

ERROR at line 1:

ORA-04063: package body "SYS.DBMS_SQLTUNE_INTERNAL" has errors

ORA-06508: PL/SQL: could not find program unit being called:

"SYS.DBMS_SQLTUNE_INTERNAL"

ORA-06512: at "SYS.UTL_RECOMP", line 865

ORA-06512: at line 4

If I try to re-compile the package I get the following message:

SQL> alter PACKAGE SYS.DBMS_SQLTUNE_INTERNAL compile body;

Warning: Package Body altered with compilation errors.

SQL> show error

Errors for PACKAGE BODY SYS.DBMS_SQLTUNE_INTERNAL:

LINE/COL ERROR

-------- -----------------------------------------------------------------

3233/5   PL/SQL: SQL Statement ignored

3233/25  PL/SQL: ORA-00904: "P"."MASKED_BINDS_FLAG": invalid identifier

SQL>

After running the script I ran the checks again in the DBUA but the warning continues:

Invalid objects exist     

Database contains INVALID objects prior to upgrade. The list of invalid SYS/SYSTEM objects was written to registry$sys_inv_objs. The list of non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs unless there were over 5000. Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.     Details: SYS |||GV_$XSTREAM_TRANSACTION |||VIEW,SYS |||V_$XSTREAM_TRANSACTION |||VIEW,SYS |||GV_$GOLDENGATE_TRANSACTION |||VIEW,SYS |||V_$GOLDENGATE_TRANSACTION |||VIEW,SYS |||V_$DB_OBJECT_CACHE |||VIEW,SYS |||V_$ARCHIVE_PROCESSES |||VIEW,SYS |||V_$RSRC_SESSION_INFO |||VIEW,SYS |||V_$WLM_PCMETRIC |||VIEW,SYS |||V_$WLM_PCMETRIC_HISTORY |||VIEW,SYS |||V_$PROCESS_MEMORY_DETAIL |||VIEW,SYS |||GV_$DLM_MISC |||VIEW,SYS |||GV_$DATAFILE |||VIEW,SYS |||GV_$FILESTAT |||VIEW,SYS |||GV_$ROWCACHE |||VIEW,SYS |||GV_$SQLAREA_PLAN_HASH |||VIEW,SYS |||GV_$DB_OBJECT_CACHE |||VIEW,SYS |||GV_$DELETED_OBJECT |||VIEW,SYS |||GV_$ARCHIVE_GAP |||VIEW,SYS |||GV_$ARCHIVE_PROCESSES |||VIEW,SYS |||GV_$SESSION_CONNECT_INFO |||VIEW,SYS |||GV_$SYSTEM_EVENT |||VIEW,SYS |||GV_$FILE_HISTOGRAM |||VIEW,SYS |||GV_$RSRC_SESSION_INFO |||VIEW,SYS |||V_$LOGMNR_REGION |||VIEW,SYS |||GV_$LOGMNR_REGION |||VIEW,SYS |||GV_$ASM_DISK_STAT |||VIEW,SYS |||GV_$ASM_OPERATION |||VIEW,SYS |||V_$RMAN_STATUS |||VIEW,SYS |||GV_$BUFFERED_SUBSCRIBERS |||VIEW,SYS |||V_$BUFFERED_SUBSCRIBERS |||VIEW,SYS |||GV_$WLM_PCMETRIC |||VIEW,SYS |||GV_$WLM_PCMETRIC_HISTORY |||VIEW,SYS |||GV_$PROCESS_MEMORY_DETAIL |||VIEW,SYS |||GV_$RESULT_CACHE_OBJECTS |||VIEW,SYS |||V_$RESULT_CACHE_OBJECTS |||VIEW,SYS |||GV_$SQL_MONITOR |||VIEW,SYS |||V_$SQL_MONITOR |||VIEW,SYS |||GV_$DNFS_STATS |||VIEW,SYS |||V_$DNFS_STATS |||VIEW,SYS |||V_$DNFS_CHANNELS |||VIEW,SYS |||V_$HANG_INFO |||VIEW,SYS |||DBA_KGLLOCK |||VIEW,SYS |||DBA_LOCK_INTERNAL |||VIEW,SYS |||DBA_DDL_LOCKS |||VIEW,SYS |||SCHEDULER$_REMOTE_ARG |||TYPE,SYS |||SCHEDULER$_REMOTE_ARG_LIST |||TYPE,SYS |||SCHEDULER$_REMOTE_DB_JOB_INFO |||TYPE,SYS |||LOGMNR_GTLO3 |||PROCEDURE,SYS |||LOGMNR_DICT_CACHE |||PACKAGE BODY,SYS |||DBMS_SQLTUNE_INTERNAL |||PACKAGE BODY,SYS |||DBMS_WRR_INTERNAL |||PACKAGE BODY,SYS |||DBMS_WORKLOAD_REPLAY |||PACKAGE BODY,SYS |||AS_REPLAY |||PACKAGE BODY,SYS |||PRVT_SQLPA |||PACKAGE BODY,SYS |||DBMS_STREAMS_ADM_UTL |||PACKAGE BODY,SYS |||DBMS_STREAMS_ADM |||PACKAGE BODY,SYS |||DBMS_APPLY_ADM_INTERNAL |||PACKAGE BODY,SYS |||DBMS_LOGREP_UTIL |||PACKAGE BODY,SYS |||DBMS_LOGREP_EXP |||PACKAGE BODY,SYS |||DBMS_LOGREP_IMP_INTERNAL |||PACKAGE BODY,SYS |||DBMS_APPLY_HANDLER_INTERNAL |||PACKAGE BODY,SYS |||DBMS_XSTREAM_ADM_UTL |||PACKAGE BODY,SYS |||DBMS_XSTREAM_AUTH |||PACKAGE BODY,SYS |||DBMS_XSTREAM_ADM_INTERNAL |||PACKAGE BODY,SYS |||AQ$_SCHEDULER$_EVENT_QTAB_F |||VIEW,SYS |||AQ$SCHEDULER$_EVENT_QTAB_R |||VIEW,SYS |||AQ$_SCHEDULER$_REMDB_JOBQTAB_F |||VIEW,SYS |||AQ$SCHEDULER$_REMDB_JOBQTAB |||VIEW,SYS |||AQ$SCHEDULER$_REMDB_JOBQTAB_R |||VIEW,SYS |||AQ$_SCHEDULER_FILEWATCHER_QT_F |||VIEW,SYS |||AQ$SCHEDULER_FILEWATCHER_QT_R |||VIEW,SYS |||AQ$_ALERT_QT_F |||VIEW,SYS |||AQ$ALERT_QT_R |||VIEW,SYS |||AQ$_AQ$_MEM_MC_F |||VIEW,SYS |||AQ$_AQ_PROP_TABLE_F |||VIEW,SYS |||AQ$AQ_PROP_TABLE_R |||VIEW,SYS |||SAP_$KSLEI |||VIEW,SYS |||KU$_OPTION_OBJNUM_VIEW |||VIEW,SYS |||KU$_OPTION_VIEW_OBJNUM_VIEW |||VIEW,SYS |||PRVT_CPADDM |||PACKAGE BODY,SYS |||DBMS_REDACT_INT |||PACKAGE BODY,SYS |||DBMS_REDACT |||PACKAGE BODY,SYSTEM |||LOGMNR$TAB_GG_TABF_PUBLIC |||FUNCTION   

Cause: Invalid object found in the database.    

Action: It is recommended that utlprp.sql be run to attempt to validate objects

I don't know if I should ignore these invalid objects or find a way to solve them before continuing with the upgrade, but any help in order to fix this I would greatly appreciate it.

Thanks & Regards,

Jesus R.

[Brindavan_M]

Hi Rodriguez,

Try the below option and let me know the same.

Cause : The problem is caused by the public synonym XMLCONCAT

Solution:

1. drop the synonym:

SQL> startup upgrade

-- Drop the synonym:

SQL> drop public synonym XMLCONCAT;

--Recompile the package

SQL> alter package SYS.DBMS_SQLTUNE_INTERNAL compile body;

If the above is successful, then re-run CATUPGRD.SQL again:

SQL> spool /tmp/catupgrd.log

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

SQL> exit

Thanks,

BM

[former_member248376]

Hi Brindavan, I tried what you suggested but the drop synonym didn't work:

SQL> drop public synonym XMLCONCAT;

drop public synonym XMLCONCAT

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

SQL>

Thanks & regards,

Jesus R.

[Brindavan_M]

Hi Jesus.R,

Could you please give the output of the two below query.

show error


Errors for PACKAGE BODY SYS.DBMS_SQLTUNE_INTERNAL:


LINE/COL ERROR
-------- -----------------------------------------------------------------
3233/5   PL/SQL: SQL Statement ignored
3233/25  PL/SQL: ORA-00904: "P"."MASKED_BINDS_FLAG": invalid identifier

SQL>  MASKED_BINDS_FLAG --> find the owner , object type, and table owner for

If the Owner is PUBLIC and object name is SYNONYM is SYS.MASKED_BINDS_FLAG then execute the below command

SQL> Create public synonym for the MASKED_BINDS_FLAG for SYS.MASKED_BINDS_FLAG;

SQL> ALTER PACKAGE  SYS . DBMS_SQLTUNE_INTERNAL  compile body ;

SQL>Show error

Thanks ,

BM

[former_member248376]

Hi Brindavan,

I tried as suggested but got the following message after the first statement:

SQL> Create public synonym for the MASKED_BINDS_FLAG for SYS.MASKED_BINDS_FLAG;

Create public synonym for the MASKED_BINDS_FLAG for SYS.MASKED_BINDS_FLAG

                      *

ERROR at line 1:

ORA-00995: missing or invalid synonym identifier

SQL>

Any other suggestion?

Thanks & Regards,

Jesus R.

[Brindavan_M]

Hi Jesus.R,

Better you can ignore the this error and start the upgrade. Before perform the upgrade. do the below steps.

List all invalid objects before executing utlrp.sql

   spool invalid_objects_before_utlrp.txt

   set cmdsep on

   set lines 150 pages 40

   col compiled_at for a30; col compiled_by for a10; col owner for a10; col object_name for a35;

   col object_type for a12; col object_id for 999999 head obj_id

   select do.owner,do.object_name,do.object_type,urc.*

   from utl_recomp_compiled urc, dba_objects do where urc.obj# = do.object_id order by compiled_at;

   select owner,object_name,object_id,object_type,created,last_ddl_time,timestamp,status

   from dba_objects where status = 'INVALID' order by object_name;

   spool off

-- Execute utlrp

   @@rdbms/admin/utlrp.sql

-- List all invalid objects after executing utlrp.sql

   spool invalid_objects_after_utlrp.txt

   set cmdsep on

   set lines 150 pages 40

   col compiled_at for a30; col compiled_by for a10; col owner for a10; col object_name for a35;

   col object_type for a12; col object_id for 999999 head obj_id

   select do.owner,do.object_name,do.object_type,urc.*

   from utl_recomp_compiled urc, dba_objects do where urc.obj# = do.object_id order by compiled_at;

   select owner,object_name,object_id,object_type,created,last_ddl_time,timestamp,status

   from dba_objects where status = 'INVALID' order by object_name;

   spool off

Note : Once the upgrade done then run the catproc.

Thanks,

BM

[manish_singh13]

Hi Jesus,

Please check below SAP Note:

2320927 - ORA-04063 when upgrading to Oracle 12.1.0.2

Thanks,

Manish

[former_member248376]

Hi Manish,

Thanks for your reply, I've already seen this note but no luck so far since all 3 commands come back with invalid objects .

Best regards,

Jesus R.

[manish_singh13]

Hi Jesus,

Please let me know if you have you tried executing script from path ("$ORACLE_HOME/rdbms/admin/utlrp.sql").

Thanks,

Manish

[former_member248376]

Hi Manish,

Yes, I've already tried executing that script with the same outcome described before (ORA-04063: package body "SYS.DBMS_SQLTUNE_INTERNAL" has errors).

Regards,

Jesus R.

[manish_singh13]

Hi Jesus,

If possible could you please generate spool files for utlrp.sql and pre_upgrade_checks.sql and attach them here.

Thanks,

Manish

[former_member248376]

Hi Manish,

Please find attached the results of both scripts.

Thanks & regards,

Jesus R.

pre_upgrade_checks.sql.txt.zip

@utlrp.sql2.txt.zip

[manish_singh13]

Hi Jesus,

Below is part of output of pre_upgrade_checks.sql output:

*

Executing Pre-Upgrade Checks in QAS...

***************************************************************************

      ************************************************************

                   ====>> ERRORS FOUND for QAS <<====

The following are *** ERROR LEVEL CONDITIONS *** that must be addressed

                    prior to attempting your upgrade.

            Failure to do so will result in a failed upgrade.

           You MUST resolve the above errors prior to upgrade

      ************************************************************

      ************************************************************

                ====>> PRE-UPGRADE RESULTS for QAS <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:

/oracle/cfgtoollogs/QAS/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

/oracle/cfgtoollogs/QAS/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

/oracle/cfgtoollogs/QAS/preupgrade/postupgrade_fixups.sql

In the same output file I am seeing below log also:

**********************************************************************

Database Components

**********************************************************************

The following database components are installed:

WARNING: ----> INVALID_DATABASE_COMPONENTS

WARNING: ----> Invalid Database Components

.... Component ID                   Version     Status

.... CATALOG                        11.2.0.2.0  INVALID

.... CATPROC                        11.2.0.4.0  INVALID

Component ID                   Version     Status

.

**********************************************************************

Please check if you have executed scripts in proper environment. Also please review preupgrade.log to verify it contains similar invalid objects information and afterwards try to execute preupgrade_fixups.sql

Thanks,

Manish

[Former Member]

Hi Jesus,

Below is the snippet from the sap note 618868

*********************************************************

Use the following statement to check whether both CATALOG and CATPROC in the DBA_REGISTRY have the current patchset status, and are VALID:

SELECT COMP_ID, VERSION, STATUS

FROM DBA_REGISTRY

WHERE COMP_ID IN ('CATALOG', 'CATPROC');

If the displayed patch level is older than the patch set used, the catalog scipts have not run correctly (see Note 539921). You can use Note 582427 to correct this problem. If objects are INVALID, validate them as described in Note 648203. In certain cases, serious performance problems may occur when the catalog information is obsolete.

On UNIX, make sure that the Oracle Software was correctly relinked. No serious errors can be reported in the file $ORACLE_HOME/install/make.log.

*********************************************************

Hope this helps.

Regards

Prithviraj

[former_member248376]

Hi Prithviraj, I executed the following scripts: catalog.sql, catproc.sql and utlrp.sql in order to activate the invalid database components and after that, only the CATALOG got valid:

SQL> select comp_id, version, status from dba_registry;

CATALOG                        11.2.0.4.0                     VALID

CATPROC                        11.2.0.4.0                     INVALID

SQL>

Is there another way that I can change the status of the CATPROC?

Thanks & Regards,

Jesus R.

[manish_singh13]

Hi Jesus,

Could you please execute below steps (Oracle 11 home) and provide result:

1. SQL > exec DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

2. SQL > select count(*) from dba_objects where status='INVALID'; (If you get objects more >0 then proceed with next steps)

3. SQL > shutdown

4. SQL > startup restrict

5. SQL > @?/rdbms/admin/catproc.sql

6. SQL > @?/rdbms/admin/utlrp.sql

Check status of CATPROC

Thanks,

Manish

[former_member248376]

Hi Manish,

I tried your suggestion but I'm still getting the CATPROC invalid status:

WARNING: ----> Invalid Database Components

.... Component ID                   Version     Status

.... CATPROC                        11.2.0.4.0  INVALID

Component ID                   Version     Status

CATALOG                        11.2.0.4.0  VALID

I opened a ticket in SAP and they suggested the following:

sqlplus / a sysdba

startup upgrade

@?/rdbms/admin/catnomwn.sql

@?/rdbms/admin/catnosch.sql

@?/rdbms/admin/catsch.sql

@?/rdbms/admin/catmwin.sql

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/utlrp.sql

shutdown immediate

startup

But the result is the same.

Any other suggestion?

Thanks & Regards,

Jesus R.

[manish_singh13]

Hi Jesus,

Please provide output of these two commands:

1. SELECT O.NAME FROM OBJ$ O, REGISTRY$ R

WHERE O.STATUS > 1 AND

      (O.CTIME BETWEEN R.DATE_LOADING AND R.DATE_LOADED OR

       O.MTIME BETWEEN R.DATE_LOADING AND R.DATE_LOADED OR

       O.STIME BETWEEN R.DATE_LOADING AND R.DATE_LOADED);

2. select action_time, version, bundle_series, comments from dba_registry_history where bundle_series = 'SBP' order by action_time desc

Thanks,

Manish