Normalmente encontro mais esse problema de objetos invalidos na dba_registry\u00a0 apos uma migra\u00e7\u00e3o , s\u00f3 que dessa vez foi antes de migrar mesmo :(, segue a corre\u00e7\u00e3o<\/p>\n
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\r\n\r\nCOMP_NAME COMP_ID VERSION STATUS\r\n------------------------------ ---------- ------------ -----------\r\nOracle Database Catalog Views CATALOG 10.2.0.4.0 VALID\r\nOracle Database Packages and T CATPROC 10.2.0.4.0 INVALID\r\nOracle Workspace Manager OWM 10.2.0.4.3 VALID\r\nJServer JAVA Virtual Machine JAVAVM 10.2.0.4.0 VALID\r\nOracle XDK XML 10.2.0.4.0 VALID\r\nOracle Database Java Packages CATJAVA 10.2.0.4.0 VALID\r\nOracle Expression Filter EXF 10.2.0.4.0 VALID\r\nOracle Data Mining ODM 10.2.0.4.0 VALID\r\nOracle Text CONTEXT 10.2.0.4.0 VALID\r\nOracle XML Database XDB 10.2.0.4.0 VALID\r\nOracle Rule Manager RUL 10.2.0.4.0 VALID\r\n\r\nCOMP_NAME COMP_ID VERSION STATUS\r\n------------------------------ ---------- ------------ -----------\r\nOracle interMedia ORDIM 10.2.0.4.0 VALID\r\nOLAP Analytic Workspace APS 10.2.0.4.0 VALID\r\nOracle OLAP API XOQ 10.2.0.4.0 VALID\r\nOLAP Catalog AMD 10.2.0.4.0 VALID\r\nSpatial SDO 10.2.0.4.0 VALID\r\nOracle Enterprise Manager EM 10.2.0.4.0 VALID\r\nOracle Real Application Cluste RAC 10.2.0.4.0 VALID\r\nOracle Application Express APEX 3.1.2.00.02 VALID\r\n\r\n19 rows selected.\r\n\r\nSQL> select count(*) from dba_objects where status='INVALID';\r\n\r\nCOUNT(*)\r\n----------\r\n92\r\n\r\n<\/pre>\nVamos executar os scripts\u00a0catalog.sql,catproc.sql,utlrp.sql<\/strong><\/p>\n
sqlplus \/ as sysdba\r\nSQL> startup restrict\r\nSQL > @?\/rdbms\/admin\/catalog.sql\r\nSQL > @?\/rdbms\/admin\/catproc.sql\r\nSQL > @?\/rdbms\/admin\/utlrp.sql<\/pre>\nConnected to:\r\nOracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, OLAP, Data Mining\r\nand Real Application Testing options\r\n\r\nSQL> shutdown immediate\r\nDatabase closed.\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\nSQL> startup restrict\r\nORACLE instance started.\r\n\r\nTotal System Global Area 2.1475E+10 bytes\r\nFixed Size 2122368 bytes\r\nVariable Size 1.0754E+10 bytes\r\nDatabase Buffers 1.0704E+10 bytes\r\nRedo Buffers 14651392 bytes\r\nDatabase mounted.\r\nDatabase opened.\r\nSQL>Total System Global Area 2.1475E+10 bytes\r\nFixed Size 2122368 bytes\r\nVariable Size 1.0754E+10 bytes\r\nDatabase Buffers 1.0704E+10 bytes\r\nRedo Buffers 14651392 bytes\r\nDatabase mounted.\r\nDatabase opened.\r\n\r\n<\/pre>\nSQL> @?\/rdbms\/admin\/catalog.sql\r\n\r\nDOC>######################################################################\r\nDOC>######################################################################\r\nDOC> The following statement will cause an \"ORA-01722: invalid number\"\r\nDOC> error and terminate the SQLPLUS session if the user is not SYS.\r\nDOC> Disconnect and reconnect with AS SYSDBA.\r\nDOC>######################################################################\r\nDOC>######################################################################\r\nDOC>#\r\n\r\nno rows selected\r\nPackage created.\r\nPackage body created.\r\nGrant succeeded.\r\nPackage created.\r\nSynonym created.\r\nGrant succeeded.\r\nGrant succeeded.\r\nPL\/SQL procedure successfully completed.\r\nSQL>\r\nSQL><\/pre>\n<\/p>\n
SQL> @?\/rdbms\/admin\/catproc.sql\r\n\r\nDOC>######################################################################\r\nDOC>######################################################################\r\nDOC> The following PL\/SQL block will cause an ORA-20000 error and\r\nDOC> terminate the current SQLPLUS session if the user is not SYS.\r\nDOC> Disconnect and reconnect with AS SYSDBA.\r\nDOC>######################################################################\r\nDOC>######################################################################\r\nDOC>#\r\n\r\nPL\/SQL procedure successfully completed.\r\nPL\/SQL procedure successfully completed.\r\nView created.\r\nView created.\r\nComment created.\r\nComment created.\r\nComment created.\r\nComment created.\r\n\r\n....\r\n\r\nSQL> execute ORACLE_OCM.MGMT_CONFIG.run_now;\r\nPL\/SQL procedure successfully completed.\r\nSQL>\r\nSQL>\r\nSQL> Rem ADD NEW PACKAGES\/NEW POST CATPROC FIXED VIEWS ABOVE THIS BLOCK\r\nSQL> ------------------------------------------------------------------------------\r\nSQL>\r\nSQL> SET SERVEROUTPUT ON\r\nSQL>\r\nSQL> Rem Indicate CATPROC load complete and check validity\r\nSQL> BEGIN\r\n2 dbms_registry.update_schema_list('CATPROC',\r\n3 dbms_registry.schema_list_t('SYSTEM', 'OUTLN', 'DBSNMP'));\r\n4 dbms_registry.loaded('CATPROC');\r\n5 dbms_registry_sys.validate_catproc;\r\n6 dbms_registry_sys.validate_catalog;\r\n7 END;\r\n8 \/\r\nPL\/SQL procedure successfully completed.\r\nSQL>\r\nSQL> SET SERVEROUTPUT OFF\r\n\r\n<\/pre>\n<\/p>\n
SQL> @?\/rdbms\/admin\/utlrp.sql\r\nSQL> Rem\r\nSQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $\r\nSQL> Rem\r\nSQL> Rem utlrp.sql\r\nSQL> Rem\r\nSQL> Rem Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.\r\nSQL> Rem\r\nSQL> Rem NAME\r\nSQL> Rem utlrp.sql - Recompile invalid objects\r\nSQL> Rem\r\nSQL> Rem DESCRIPTION\r\nSQL> Rem This script recompiles invalid objects in the database.\r\nSQL> Rem\r\nSQL> Rem MODIFIED (MM\/DD\/YY)\r\nSQL> Rem rburns 03\/17\/05 - use dbms_registry_sys\r\nSQL> Rem gviswana 02\/07\/05 - Post-compilation diagnostics\r\nSQL> Rem gviswana 09\/09\/04 - Auto tuning and diagnosability\r\nSQL> Rem rburns 09\/20\/04 - fix validate_components\r\nSQL> Rem gviswana 12\/09\/03 - Move functional-index re-enable here\r\nSQL> Rem gviswana 06\/04\/03 - gviswana_bug-2814808\r\nSQL> Rem gviswana 05\/28\/03 - Created\r\nSQL> Rem\r\nSQL>\r\nSQL> SET VERIFY OFF;\r\nSQL>\r\nSQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;\r\n\r\nTIMESTAMP\r\n--------------------------------------------------------------------------------\r\nCOMP_TIMESTAMP UTLRP_BGN 2018-02-15 10:08:43\r\n\r\n1 row selected.\r\n\r\nSQL>\r\nSQL> DOC\r\nDOC> The following PL\/SQL block invokes UTL_RECOMP to recompile invalid\r\nDOC> objects in the database. Recompilation time is proportional to the\r\nDOC> number of invalid objects in the database, so this command may take\r\nDOC> a long time to execute on a database with a large number of invalid\r\nDOC> objects.\r\nDOC>\r\nDOC> Use the following queries to track recompilation progress:\r\nDOC>\r\nDOC> 1. Query returning the number of invalid objects remaining. This\r\nDOC> number should decrease with time.\r\nDOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);\r\nDOC>\r\nDOC> 2. Query returning the number of objects compiled so far. This number\r\nDOC> should increase with time.\r\nDOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;\r\nDOC>\r\nDOC> This script automatically chooses serial or parallel recompilation\r\nDOC> based on the number of CPUs available (parameter cpu_count) multiplied\r\nDOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).\r\nDOC> On RAC, this number is added across all RAC nodes.\r\nDOC>\r\nDOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel\r\nDOC> recompilation. Jobs are created without instance affinity so that they\r\nDOC> can migrate across RAC nodes. Use the following queries to verify\r\nDOC> whether UTL_RECOMP jobs are being created and run correctly:\r\nDOC>\r\nDOC> 1. Query showing jobs created by UTL_RECOMP\r\nDOC> SELECT job_name FROM dba_scheduler_jobs\r\nDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';\r\nDOC>\r\nDOC> 2. Query showing UTL_RECOMP jobs that are running\r\nDOC> SELECT job_name FROM dba_scheduler_running_jobs\r\nDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';\r\nDOC>#\r\nSQL>\r\nSQL> DECLARE\r\n2 threads pls_integer := &&1;\r\n3 BEGIN\r\n4 utl_recomp.recomp_parallel(threads);\r\n5 END;\r\n6 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>\r\nSQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;\r\n\r\nTIMESTAMP\r\n--------------------------------------------------------------------------------\r\nCOMP_TIMESTAMP UTLRP_END 2018-02-15 10:11:31\r\n\r\n1 row selected.\r\n\r\nSQL>\r\nSQL> Rem\r\nSQL> Rem Re-enable functional indexes disabled by the recompile\r\nSQL> Rem\r\nSQL> DECLARE\r\n2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;\r\n3 commands tab_char;\r\n4 table_exists number;\r\n5 BEGIN\r\n6 -- Check for existence of the table marking disabled functional indices\r\n7 SELECT count(*) INTO table_exists FROM DBA_OBJECTS\r\n8 WHERE owner = 'SYS' and object_name = 'UTLIRP_ENABLED_FUNC_INDEXES' and\r\n9 object_type = 'TABLE';\r\n10\r\n11 IF (table_exists > 0) THEN\r\n12 -- Select indices to be re-enabled\r\n13 EXECUTE IMMEDIATE q'+\r\n14 SELECT 'ALTER INDEX \"' || u.name || '\".\"' || o.name || '\" ENABLE'\r\n15 FROM utlirp_enabled_func_indexes e, ind$ i, obj$ o, user$ u\r\n16 WHERE e.obj# = i.obj# AND i.obj# = o.obj# and o.owner# = u.user#\r\n17 AND bitand(i.flags, 1024) != 0+'\r\n18 BULK COLLECT INTO commands;\r\n19\r\n20 IF (commands.count() > 0) THEN\r\n21 FOR i IN 1 .. commands.count() LOOP\r\n22 EXECUTE IMMEDIATE commands(i);\r\n23 END LOOP;\r\n24 END IF;\r\n25\r\n26 EXECUTE IMMEDIATE 'DROP TABLE utlirp_enabled_func_indexes';\r\n27 END IF;\r\n28 END;\r\n29 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>\r\nSQL> DOC\r\nDOC> The following query reports the number of objects that have compiled\r\nDOC> with errors (objects that compile with errors have status set to 3 in\r\nDOC> obj$). If the number is higher than expected, please examine the error\r\nDOC> messages reported with each object (using SHOW ERRORS) to see if they\r\nDOC> point to system misconfiguration or resource constraints that must be\r\nDOC> fixed before attempting to recompile these objects.\r\nDOC>#\r\nSQL> select COUNT(*) \"OBJECTS WITH ERRORS\" from obj$ where status = 3;\r\n\r\nOBJECTS WITH ERRORS\r\n-------------------\r\n88\r\n\r\n1 row selected.\r\n\r\nSQL>\r\nSQL>\r\nSQL> DOC\r\nDOC> The following query reports the number of errors caught during\r\nDOC> recompilation. If this number is non-zero, please query the error\r\nDOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors\r\nDOC> are due to misconfiguration or resource constraints that must be\r\nDOC> fixed before objects can compile successfully.\r\nDOC>#\r\nSQL> select COUNT(*) \"ERRORS DURING RECOMPILATION\" from utl_recomp_errors;\r\n\r\nERRORS DURING RECOMPILATION\r\n---------------------------\r\n4\r\n\r\n1 row selected.\r\n\r\nSQL>\r\nSQL>\r\nSQL> Rem =====================================================================\r\nSQL> Rem Run component validation procedure\r\nSQL> Rem =====================================================================\r\nSQL>\r\nSQL> SET serveroutput on\r\nSQL> EXECUTE dbms_registry_sys.validate_components;\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> SET serveroutput off\r\nSQL>\r\nSQL>\r\nSQL> Rem ===========================================================================\r\nSQL> Rem END utlrp.sql\r\nSQL> Rem ===========================================================================\r\nSQL>\r\nSQL><\/pre>\nSQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)\r\ncomp_id,substr(version,1,12) version,status from dba_registry; 2\r\n\r\nCOMP_NAME COMP_ID VERSION STATUS\r\n------------------------------ ---------- ------------ -----------\r\nOracle Database Catalog Views CATALOG 10.2.0.4.0 VALID\r\nOracle Database Packages and T CATPROC 10.2.0.4.0 VALID\r\nOracle Workspace Manager OWM 10.2.0.4.3 VALID\r\nJServer JAVA Virtual Machine JAVAVM 10.2.0.4.0 VALID\r\nOracle XDK XML 10.2.0.4.0 VALID\r\nOracle Database Java Packages CATJAVA 10.2.0.4.0 VALID\r\nOracle Expression Filter EXF 10.2.0.4.0 VALID\r\nOracle Data Mining ODM 10.2.0.4.0 VALID\r\nOracle Text CONTEXT 10.2.0.4.0 VALID\r\nOracle XML Database XDB 10.2.0.4.0 VALID\r\nOracle Rule Manager RUL 10.2.0.4.0 VALID\r\nOracle interMedia ORDIM 10.2.0.4.0 VALID\r\nOLAP Analytic Workspace APS 10.2.0.4.0 VALID\r\nOracle OLAP API XOQ 10.2.0.4.0 VALID\r\nOLAP Catalog AMD 10.2.0.4.0 VALID\r\nSpatial SDO 10.2.0.4.0 VALID\r\nOracle Enterprise Manager EM 10.2.0.4.0 VALID\r\nOracle Real Application Cluste RAC 10.2.0.4.0 VALID\r\nOracle Application Express APEX 3.1.2.00.02 VALID\r\n\r\n19 rows selected.<\/pre>\nProblema resolvido!<\/p>\n","protected":false},"excerpt":{"rendered":"
Normalmente encontro mais esse problema de objetos invalidos na dba_registry\u00a0 apos uma migra\u00e7\u00e3o , s\u00f3 que dessa vez foi antes de migrar mesmo :(, segue a corre\u00e7\u00e3o 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[…]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0},"categories":[21],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/zmsolution.com\/wp-json\/wp\/v2\/posts\/1587"}],"collection":[{"href":"http:\/\/zmsolution.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/zmsolution.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/zmsolution.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/zmsolution.com\/wp-json\/wp\/v2\/comments?post=1587"}],"version-history":[{"count":0,"href":"http:\/\/zmsolution.com\/wp-json\/wp\/v2\/posts\/1587\/revisions"}],"wp:attachment":[{"href":"http:\/\/zmsolution.com\/wp-json\/wp\/v2\/media?parent=1587"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/zmsolution.com\/wp-json\/wp\/v2\/categories?post=1587"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/zmsolution.com\/wp-json\/wp\/v2\/tags?post=1587"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}