Skip to Content

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

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Jul 25, 2016 at 11:00 PM

    Hi Jesus,

    Please check below SAP Note:

    2320927 - ORA-04063 when upgrading to Oracle 12.1.0.2




    Thanks,

    Manish

    Add a comment
    10|10000 characters needed characters exceeded

    • Manish Singh Jesus A. Rodriguez S.

      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

  • Posted on Jul 26, 2016 at 11:03 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.