ORA-00600 error DB is still up and running many sys objects invalid

mjimison1956
mjimison1956 used Ask the Experts™
on
The data base is running a heavy batch load of data.
I do not want to shut the db down as it is still processing, however I have many objects invalid in various schemas.  When I try to compile the will compile and almost instantly go invalid again.  Here is a block from the .trc file.  I have .trc for many many packages..this is just an example but the others are the same with the exception of the object.

What could have happened to make these object invalidate and how do I determine same.

*** ACTION NAME:(Auto ADDM Slave Action) 2008-04-02 12:00:52.107
*** MODULE NAME:(MMON_SLAVE) 2008-04-02 12:00:52.107
*** SERVICE NAME:(SYS$BACKGROUND) 2008-04-02 12:00:52.107
*** SESSION ID:(220.27232) 2008-04-02 12:00:52.107
KSV 604 error in slave process
*** 2008-04-02 12:00:52.108
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at "SYS.PRVT_HDM", line 112
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at line 1
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at "SYS.PRVT_HDM", line 112
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at line 1
$ ORA-00447: fatal error in background process
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006
Commented:
sounds to me like you need to rerun the sys schema creation scripts.  there are 2 of them, catalog.sql and catproc.sql.  they can be found in ${ORACLE_HOME}/rdbms/admin and need to be run as sys.  they will recreate any missing sys objects for you.

as for how you got into this state, i have no idea.
Mark GeerlingsDatabase Administrator
Commented:
Please give us some more information like:
1. Which version of Oracle is this?
2. What is the server O/S?
3. Is this a 32-bit or 64-bit system?  If 64-bit, is Oracle also 64-bit?
4. How much RAM does the server have?
5. Is this the only Oracle instance on this server?
6. Does the server run other processes/application also?  Or is this server dedicated to Oracle?
7. How long has this version of Oracle been running on this server? (Days, Weeks, Months, Years?)
Richard OlutolaConsultant
Commented:
Have you tried recompiling this package body alone? If you  get an error then do show error (or is it show errors?) This will give you an idea why this being invalidated.

Is your loading process amending an underlying object which the package body relies on?

R.  
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

David VanZandtOracle Database Administrator III
Commented:
Adding to rbooker, Oracle provides the recompilation script: ?/rdbms/admin/utlrp.sql

Author

Commented:
The batch is still loading that is the good news.   Contemplating all ideas here.

1. Which version of Oracle is this?              10.2.0.2
2. What is the server O/S?                          Linux Red Hat
3. Is this a 32-bit or 64-bit system?  If 64-bit, is Oracle also 64-bit?        64 and 64
4. How much RAM does the server have?               64GB
5. Is this the only Oracle instance on this server?       No several
6. Does the server run other processes/application also?  Or is this server dedicated to Oracle?  Dedicated
7. How long has this version of Oracle been running on this server? (Days, Weeks, Months, Years?)
6 months

Author

Commented:
dvz

When running the script utlrp.sql........  here is the result:

 @utlrp.sql
SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual
                                                                  *
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors


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>#
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SYS.STANDARD"
ORA-06508: PL/SQL: could not find program unit being called: "SYS.STANDARD"
ORA-06512: at "SYS.UTL_RECOMP", line 608
ORA-06512: at line 4


SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual
                                                                  *
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors



PL/SQL procedure successfully completed.

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>#

OBJECTS WITH ERRORS
-------------------
                  3

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>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

BEGIN dbms_registry_sys.validate_components; END;

*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.DBMS_REGISTRY_SYS"
ORA-06512: at line 1


SQL> @UTL_RECOMP
SP2-0310: unable to open file "UTL_RECOMP.sql"
SQL>
Mark GeerlingsDatabase Administrator

Commented:
This looks to me like a bug or problem in utlrp.sql for Oracle10.2.  Have you opened a Service request with Oracle on this question?
David VanZandtOracle Database Administrator III

Commented:
For the fun of it:      ALTER  PACKAGE sys.dbms_registry_sys COMPILE BODY;  and post the results.

Author

Commented:
SQL> ALTER  PACKAGE sys.dbms_registry_sys COMPILE BODY;

Warning: Package Body altered with compilation errors.

Author

Commented:
Still can not compile any of the other invalid packages.  There are views, packages, procedures...some valid some not.
Richard OlutolaConsultant

Commented:
As I said earlier, recompile an invalid package/body and then do SHOW ERROR (or SHOW ERRORS) and post the output here.

R.

Author

Commented:
show errors
Errors for PACKAGE BODY SYS.DBMS_REGISTRY_SYS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
129/3    PL/SQL: Statement ignored
129/29   PLS-00302: component 'SCHEMA' must be declared
214/4    PL/SQL: Statement ignored
214/21   PLS-00302: component 'IS_COMPONENT' must be declared
309/8    PL/SQL: Statement ignored
309/22   PLS-00302: component 'INVALID' must be declared
322/7    PL/SQL: Statement ignored
322/21   PLS-00302: component 'INVALID' must be declared
336/10   PL/SQL: Statement ignored
336/24   PLS-00302: component 'VALID' must be declared
360/8    PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
360/22   PLS-00302: component 'INVALID' must be declared
373/7    PL/SQL: Statement ignored
373/21   PLS-00302: component 'INVALID' must be declared
387/10   PL/SQL: Statement ignored
387/24   PLS-00302: component 'VALID' must be declared
413/7    PL/SQL: Statement ignored
413/21   PLS-00302: component 'INVALID' must be declared
426/7    PL/SQL: Statement ignored
426/21   PLS-00302: component 'INVALID' must be declared
SQL>
Richard OlutolaConsultant

Commented:
Do the same on the other invalid objects to see if there are common problems.

I'm afraid I need to go out now but in the mean time, see if this link helps:

http://forums.oracle.com/forums/thread.jspa?threadID=381498&start=15&tstart=0

R.

Author

Commented:
As always..help was fantastic.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial