Normalmente encontro mais esse problema de objetos invalidos na dba_registry apos uma migração , só que dessa vez foi antes de migrar mesmo :(, segue a correção
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)comp_id,substr(version,1,12) version,status from dba_registry; 2 COMP_NAME COMP_ID VERSION STATUS ------------------------------ ---------- ------------ ----------- Oracle Database Catalog Views CATALOG 10.2.0.4.0 VALID Oracle Database Packages and T CATPROC 10.2.0.4.0 INVALID Oracle Workspace Manager OWM 10.2.0.4.3 VALID JServer JAVA Virtual Machine JAVAVM 10.2.0.4.0 VALID Oracle XDK XML 10.2.0.4.0 VALID Oracle Database Java Packages CATJAVA 10.2.0.4.0 VALID Oracle Expression Filter EXF 10.2.0.4.0 VALID Oracle Data Mining ODM 10.2.0.4.0 VALID Oracle Text CONTEXT 10.2.0.4.0 VALID Oracle XML Database XDB 10.2.0.4.0 VALID Oracle Rule Manager RUL 10.2.0.4.0 VALID COMP_NAME COMP_ID VERSION STATUS ------------------------------ ---------- ------------ ----------- Oracle interMedia ORDIM 10.2.0.4.0 VALID OLAP Analytic Workspace APS 10.2.0.4.0 VALID Oracle OLAP API XOQ 10.2.0.4.0 VALID OLAP Catalog AMD 10.2.0.4.0 VALID Spatial SDO 10.2.0.4.0 VALID Oracle Enterprise Manager EM 10.2.0.4.0 VALID Oracle Real Application Cluste RAC 10.2.0.4.0 VALID Oracle Application Express APEX 3.1.2.00.02 VALID 19 rows selected. SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) ---------- 92
Vamos executar os scripts catalog.sql,catproc.sql,utlrp.sql
sqlplus / as sysdba SQL> startup restrict SQL > @?/rdbms/admin/catalog.sql SQL > @?/rdbms/admin/catproc.sql SQL > @?/rdbms/admin/utlrp.sql
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup restrict ORACLE instance started. Total System Global Area 2.1475E+10 bytes Fixed Size 2122368 bytes Variable Size 1.0754E+10 bytes Database Buffers 1.0704E+10 bytes Redo Buffers 14651392 bytes Database mounted. Database opened. SQL>Total System Global Area 2.1475E+10 bytes Fixed Size 2122368 bytes Variable Size 1.0754E+10 bytes Database Buffers 1.0704E+10 bytes Redo Buffers 14651392 bytes Database mounted. Database opened.
SQL> @?/rdbms/admin/catalog.sql DOC>###################################################################### DOC>###################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error and terminate the SQLPLUS session if the user is not SYS. DOC> Disconnect and reconnect with AS SYSDBA. DOC>###################################################################### DOC>###################################################################### DOC># no rows selected Package created. Package body created. Grant succeeded. Package created. Synonym created. Grant succeeded. Grant succeeded. PL/SQL procedure successfully completed. SQL> SQL>
SQL> @?/rdbms/admin/catproc.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> terminate the current SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
View created.
View created.
Comment created.
Comment created.
Comment created.
Comment created.
....
SQL> execute ORACLE_OCM.MGMT_CONFIG.run_now;
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> Rem ADD NEW PACKAGES/NEW POST CATPROC FIXED VIEWS ABOVE THIS BLOCK
SQL> ------------------------------------------------------------------------------
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> Rem Indicate CATPROC load complete and check validity
SQL> BEGIN
2 dbms_registry.update_schema_list('CATPROC',
3 dbms_registry.schema_list_t('SYSTEM', 'OUTLN', 'DBSNMP'));
4 dbms_registry.loaded('CATPROC');
5 dbms_registry_sys.validate_catproc;
6 dbms_registry_sys.validate_catalog;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> SET SERVEROUTPUT OFF
SQL> @?/rdbms/admin/utlrp.sql
SQL> Rem
SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlrp.sql - Recompile invalid objects
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem rburns 03/17/05 - use dbms_registry_sys
SQL> Rem gviswana 02/07/05 - Post-compilation diagnostics
SQL> Rem gviswana 09/09/04 - Auto tuning and diagnosability
SQL> Rem rburns 09/20/04 - fix validate_components
SQL> Rem gviswana 12/09/03 - Move functional-index re-enable here
SQL> Rem gviswana 06/04/03 - gviswana_bug-2814808
SQL> Rem gviswana 05/28/03 - Created
SQL> Rem
SQL>
SQL> SET VERIFY OFF;
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2018-02-15 10:08:43
1 row selected.
SQL>
SQL> DOC
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
SQL>
SQL> DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2018-02-15 10:11:31
1 row selected.
SQL>
SQL> Rem
SQL> Rem Re-enable functional indexes disabled by the recompile
SQL> Rem
SQL> DECLARE
2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 commands tab_char;
4 table_exists number;
5 BEGIN
6 -- Check for existence of the table marking disabled functional indices
7 SELECT count(*) INTO table_exists FROM DBA_OBJECTS
8 WHERE owner = 'SYS' and object_name = 'UTLIRP_ENABLED_FUNC_INDEXES' and
9 object_type = 'TABLE';
10
11 IF (table_exists > 0) THEN
12 -- Select indices to be re-enabled
13 EXECUTE IMMEDIATE q'+
14 SELECT 'ALTER INDEX "' || u.name || '"."' || o.name || '" ENABLE'
15 FROM utlirp_enabled_func_indexes e, ind$ i, obj$ o, user$ u
16 WHERE e.obj# = i.obj# AND i.obj# = o.obj# and o.owner# = u.user#
17 AND bitand(i.flags, 1024) != 0+'
18 BULK COLLECT INTO commands;
19
20 IF (commands.count() > 0) THEN
21 FOR i IN 1 .. commands.count() LOOP
22 EXECUTE IMMEDIATE commands(i);
23 END LOOP;
24 END IF;
25
26 EXECUTE IMMEDIATE 'DROP TABLE utlirp_enabled_func_indexes';
27 END IF;
28 END;
29 /
PL/SQL procedure successfully completed.
SQL>
SQL> DOC
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
SQL> select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status = 3;
OBJECTS WITH ERRORS
-------------------
88
1 row selected.
SQL>
SQL>
SQL> DOC
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;
ERRORS DURING RECOMPILATION
---------------------------
4
1 row selected.
SQL>
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
PL/SQL procedure successfully completed.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL>
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10) comp_id,substr(version,1,12) version,status from dba_registry; 2 COMP_NAME COMP_ID VERSION STATUS ------------------------------ ---------- ------------ ----------- Oracle Database Catalog Views CATALOG 10.2.0.4.0 VALID Oracle Database Packages and T CATPROC 10.2.0.4.0 VALID Oracle Workspace Manager OWM 10.2.0.4.3 VALID JServer JAVA Virtual Machine JAVAVM 10.2.0.4.0 VALID Oracle XDK XML 10.2.0.4.0 VALID Oracle Database Java Packages CATJAVA 10.2.0.4.0 VALID Oracle Expression Filter EXF 10.2.0.4.0 VALID Oracle Data Mining ODM 10.2.0.4.0 VALID Oracle Text CONTEXT 10.2.0.4.0 VALID Oracle XML Database XDB 10.2.0.4.0 VALID Oracle Rule Manager RUL 10.2.0.4.0 VALID Oracle interMedia ORDIM 10.2.0.4.0 VALID OLAP Analytic Workspace APS 10.2.0.4.0 VALID Oracle OLAP API XOQ 10.2.0.4.0 VALID OLAP Catalog AMD 10.2.0.4.0 VALID Spatial SDO 10.2.0.4.0 VALID Oracle Enterprise Manager EM 10.2.0.4.0 VALID Oracle Real Application Cluste RAC 10.2.0.4.0 VALID Oracle Application Express APEX 3.1.2.00.02 VALID 19 rows selected.
Problema resolvido!