14 Replies Latest reply: Dec 5, 2008 7:36 AM by 609456 RSS

    ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

    609456
      Hi,
      i am on 11.5.10.2 RHEL 4.0
      i tried running catalog.log on DB Tier as sys, but was not successful.

      later sqlplus apps/apps would start giving
      ORA-00604 : error occured at recursive SQL level 1
      ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

      $sqlplus /nolog works fine... BUT
      $sqlplus conn /as sysdba HANGS!!!!

      Also logging via URL > after clicking on E-Business Home Page gives

      Internal Server Error
      The server encountered an internal error or misconfiguration and was unable to complete your request.
      Please contact the server administrator, changeOnOHSInstall and inform them of the time the error occurred, and anything you might have done that may have caused the error.

      More information about this error may be available in the server error log.


      could anybody suggest on this error?

      Thanking You
      Regards,
      SK
        • 1. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
          Hussein Sawwan-Oracle
          ORA-00604 : error occured at recursive SQL level 1
          ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
          Please verify the status of this object (and other objects). Run utlrp.sql to compile all invalid objects in the database.

          Also, make sure PLSQL_V2_COMPATIBLITY is not set to TRUE.

          Note: 170282.1 - PLSQL_V2_COMPATIBLITY=TRUE causes STANDARD and DBMS_STANDARD to Error at Compile
          https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=170282.1
          Also logging via URL > after clicking on E-Business Home Page gives

          Internal Server Error
          The server encountered an internal error or misconfiguration and was unable to complete your request.
          Please contact the server administrator, changeOnOHSInstall and inform them of the time the error occurred, and anything you might have done that may have caused the error.

          More information about this error may be available in the server error log.
          I would suggest you resolve the database issue first then check this error. (You may review Apache log file for more details about the error under $IAS_ORACLE_HOME/ Apache/Apache/logs).
          • 2. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
            609456
            Thanks for your quick reply

            status of the object: INVALID

            PLSQL_V2_COMPATIBLITY is set to FALSE

            Run utlrp.sql to compile all invalid objects in the database
            it gave me error as follows:

            SQL> @utlrp.sql
            declare
            *
            ERROR at line 1
            ORA -00604 : error occured at recursive SQL level 1
            ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

            CREATE TABLE utl_recomp_invalid (obj# number)
            *
            ERROR at line 1
            ORA -00604 : error occured at recursive SQL level 1
            ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

            CREATE TABLE utl_recomp_sorted (obj# number, depth number)
            *
            ERROR at line 1
            ORA -00604 : error occured at recursive SQL level 1
            ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

            CREATE TABLE utl_recomp_compiled(obj# number)
            *
            ERROR at line 1
            ORA -00604 : error occured at recursive SQL level 1
            ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

            CREATE INDEX utl_recomp_comp_idx1 ON utl_recomp_compiled(obj#)
            *
            ERROR at line 1
            ORA -00604 : error occured at recursive SQL level 1
            ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

            CREATE TABLE utl_recomp_backup_jobs (command varchar2(100), status varchar2(1000))
            *
            ERROR at line 1
            ORA -00604 : error occured at recursive SQL level 1
            ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

            SELECT o.obj#, o.type#, o. owner# FROM obj# o

            ERROR at line 2
            ORA -00604 : error occured at recursive SQL level 1
            ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD



            *

            Edited by: mirage on Dec 4, 2008 6:45 PM
            • 3. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
              Hussein Sawwan-Oracle
              What is the error when you compile the package manually?
              i tried running catalog.log on DB Tier as sys, but was not successful.
              What is the error in the log file?
              • 4. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                609456
                hi,

                i did not find any errors, the session in which i ran catalog.sql just got hanged for hours. Then i terminated the session.

                I tried recompiling DBMS_STANDARD, but that session also hangs

                Please advice

                Regards,
                SK
                • 5. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                  Hussein Sawwan-Oracle
                  i did not find any errors, the session in which i ran catalog.sql just got hanged for hours. Then i terminated the session.
                  Did you run the script again after terminating the session? If not, please run it again and make sure it completes successfully, then compile the invalid objects using the utlrp.sql script. Trying to resolve invalid objects without completing the catalog.sql is not a good idea.
                  • 6. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                    609456
                    i tried to running catalog.sql BUTgot errors

                    SQL>@catalog.sql
                    create or replace
                    *
                    ERROR at line 1
                    ORA -00604 : error occured at recursive SQL level 1
                    ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

                    create or replace package dbms_standard is
                    *
                    ERROR at line 1
                    ORA -00604 : error occured at recursive SQL level 1
                    ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

                    create or replace public synonym dbms_standard for sys.dbms_standard
                    *
                    ERROR at line 1
                    ORA -00604 : error occured at recursive SQL level 1
                    ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

                    grant execute on dbms_standard to public
                    *
                    ERROR at line 1
                    ORA -00604 : error occured at recursive SQL level 1
                    ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

                    CREATE TABLE registry$ (
                    *
                    ERROR at line 1
                    ORA -00604 : error occured at recursive SQL level 1
                    ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

                    FROM regisrty$ r, user$ s, user$ i
                    *
                    ERROR at line 22:
                    ORA -00604 : error occured at recursive SQL level 1
                    ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD

                    CREATE OR REPLACE PUBLIC SYNONYM dba_registry FOR dba_registry
                    *
                    ERROR at line 1
                    ORA -00604 : error occured at recursive SQL level 1
                    ORA - 04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD



                    Is there a possibility that the Data Dictionary got corrupt?
                    • 7. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                      Hussein Sawwan-Oracle
                      It could be. I would suggest you log a SR, or restore the database from a backup and run catalog.sql again.

                      By the way, are you following any Metalink note?
                      • 8. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                        609456
                        By the way, are you following any Metalink note?

                        -- no i am not

                        i got couple of more days left to resolve this issue, will try
                        Thanks for the help.

                        Need to work this out from the base...
                        • 9. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                          Hussein Sawwan-Oracle
                          -- no i am not
                          From what version to what version you are upgrading?

                          What is the status of the database components in the dba_registry view?
                          • 10. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                            609456
                            hsawwan,

                            the scenario here is that running catalog.sql was a mistake done by one of our newbie DBA
                            in process recovering it back to normal.


                            will recreating the the package DBMS_STANDARD would be of any help?
                            could you help me getting the script?

                            i tried
                            SELECT text FROM user_source WHERE name ='OBJECTNAME' AND test like '%Header%';
                            but got confused...


                            Regards,
                            SK
                            • 11. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                              Hussein Sawwan-Oracle
                              Is this the only invalid object you have?

                              I believe it is better you restore the database from a valid backup rather than trying to fix the invalid objects.
                              • 12. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                                Hussein Sawwan-Oracle
                                utlirp.sql script recompiles existing PL/SQL modules and reloads package STANDARD and DBMS_STANDARD.

                                Note: 272322.1 - Difference between UTLRP.SQL - UTLIRP.SQL - UTLIP.SQL?
                                https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=272322.1
                                • 13. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                                  609456
                                  Select count(*) from dba_objects where status='INVALID';
                                  gives around 31272 odd invalid objects.

                                  yes, restoring the database should work out BUT i'm trying to find a fix for this problem.

                                  i'll try with utlirp.sql ; thats one remaining hoep it works out !!! wil inform you

                                  Thanks
                                  SK
                                  • 14. Re: ORA-04045 : errors during recompilation/revalidation of SYS.DBMS_STANDARD
                                    609456
                                    tried running utlirp.sql

                                    it gave
                                    ORA - 04045 errors during recompilation/revalidation of
                                    SYS.CDC_CREATE_CTABLE_BEFORE
                                    ORA - 06553 : PLS - 213: package STANDARD not accessible
                                    ORA - 06553 : PLS - 213: package STANDARD not accessible
                                    ORA - 06553 : PLS - 213: package STANDARD not accessible

                                    hard luck. lot to do for the week-end.

                                    regards,
                                    SK