13 Replies Latest reply: Feb 28, 2014 12:01 PM by rp0428 RSS

    How to recompile invalid PKG in PDB refering to CDB as METADATA LINK

    user10363847

      Hi,

       

      I would like recompile invalid package owned by SYS in PDB. This package point as METADATA LINK to package in SYS schema in CDB (it was recompiled sucessfully in CDB previously).

      See scenario:

      sqlplus sys/pass@192.168.0.1/cdb_db as sysdba

       

      SQL> select con_id, owner, object_name, status from cdb_objects where object_name='HTMLDB_SYSTEM' and object_type='PACKAGE BODY';

      CON_ID OWNER      OBJECT_NAME     STATUS

      ------ ---------- ---------------  --------

      1     SYS        
      HTMLDB_SYSTEM   
      VALID  
      2     SYS    HTMLDB_SYSTEMINVALID
      3     SYS    HTMLDB_SYSTEMINVALID

       

      SQL> alter session set container=pdb12;

       

      Session altered.

       

      SQL> show con_id

       

      CON_ID

      ------------------------------

      3

       

      SQL> select owner,object_name, object_type,status,sharing,con_id from cdb_objects where status <>'VALID';

       

      OWNER     OBJECT_NAME                OBJECT_TYPE     STATUS  SHARING           CON_ID

      --------- -------------------------- --------------- ------- ------------- ----------

      SYS       HTMLDB_SYSTEM              PACKAGE BODY    INVALID METADATA LINK          3

      PUBLIC    APEX_PKG_APP_INSTALL_LOG   SYNONYM         INVALID METADATA LINK          3

      SYS       HTMLDB_SYSTEM              PACKAGE BODY    INVALID METADATA LINK          2

      PUBLIC    APEX_PKG_APP_INSTALL_LOG   SYNONYM         INVALID METADATA LINK          2

       

       

      SQL> alter package SYS.HTMLDB_SYSTEM compile body;

      alter package SYS.HTMLDB_SYSTEM compile body

      *

      ERROR at line 1:

      ORA-65040: operation not allowed from within a pluggable database

       

       

      How to compile HTMLDB_SYSTEM in PDB when refering to CDB over metadata link ?


        • 1. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
          rp0428
          I would like recompile invalid package owned by SYS in PDB. This package point as METADATA LINK to package in SYS schema in CDB (it was recompiled sucessfully in CDB previously).

          That's a new one for me. Search MOS for a previously reported bug - you may need to file one if not.

           

          As you suggest the system packages only exist in the CDB so they can only be compiled there.

           

          Were ALL of the PDBs open when you performed the recompile in the CDB?

           

          It this is just a test system you could try rerunning catalog.sql and catproc.sql in the root.

          • 2. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
            Solomon Yakobson

            Could you issue:

             

            select con_id,name from v$containers;

             

            and post results? My understanding is creating CDB creates CDB$ROOT as first container (CON_ID = 1), PDB$SEED as second container (CON_ID = 2) and PDB as third container. So fact HTMLDB_SYSTEM is invalid in CON_ID = 2 indicates issues with seed. And since PDB is created from seed HTMLDB_SYSTEM is invalid in PDB too.

             

            SY.

            • 3. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
              user10363847

              I did not searched MOS ofr a reported bug.

              Problem arised probably during deinstall and install of newer APEX version. definitely all PDBs were open during reinstall of APEX in CDB container. I did not try to rerun catproc.sql manually, I simply rely on APEX install completness.

              • 4. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
                user10363847

                Hi,

                 

                This is output from V$CONTAINERS:

                 

                CON_ID      CON_NAME

                ------------------------------

                1               CDB$ROOT

                2               PDB$SEED

                3               PDB12

                 

                The problem arised in this sequence:

                 

                1) Installed database CDB with initial creation of PDB (name PDB12), so from very beginning ALL objects were valid.

                2) Applying new patch to CDB (and pdbs of course) - all objects valid

                3) Deinstall of APEX using ORACLE_HOME/apex/ deinstall scripts --> FAILED !  (because scripts was not ready for patched CDB I suspect)

                4) Replacing original ORACLE_HOME/apex with latest APEX scripts downloaded from WEB

                5) Re-runing Deinstall of APEX from CDB (and pdbs automagically) using new scripts from ORACLE_HOME/apex with SUCCESS!

                6) Install newest APEX using scripts from ORACLE_HOME/apex - SUCESS for CDB (and PDBs automagically)

                7) Finding invalid package sys.HTMLDB_SYSTEM in CDB, PDB$SEED, PDB12 and recompiling it in CDB with success.

                8) Unable to recompile sys.HTMLDB_SYSTEM in PDBs as they refer to valid package in CDB.

                • 5. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
                  rp0428

                  Ok - but you never answered this question that I ask:

                  Were ALL of the PDBs open when you performed the recompile in the CDB?

                  Both the SEED and other PDB reflect an INVALID package. So either the package was invalid to begin with and you never noticed it or your upgrade invalidated the package in the CDB and that rippled to the PDBs.

                   

                  I'm not clear on just when CDB operations get replicated to the PDBs. For some operations (e.g. creating a new common user) the replication only gets done on PDBs that are currently OPEN. So if a PDB is closed it will NOT contain that new common user.

                   

                  That has implications for certain root operations that are intended to be replicated to all PDBs.

                   

                  So I'm wondering if the recompile of the package in the root is getting replicated to the PDBs or not and if not, why not.

                   

                  What dates (created date, last ddl, etc) are you showing for that package in the root and each of the PDBs?

                  • 6. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
                    Solomon Yakobson

                    user10363847 wrote:

                     

                     

                    3) Deinstall of APEX using ORACLE_HOME/apex/ deinstall scripts --> FAILED !  (because scripts was not ready for patched CDB I suspect)

                     

                     

                    Did you run apxremov.sql or apxremov_con.sql? Former deinstalls apex just in CDB while latter in CDB and all other containers.

                     

                    SY.

                    • 7. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
                      user10363847

                      My startup database script has command to open all PDBs right after start of CDB.

                      alter pluggable database all open;

                      However, it might be there was some manual restart from sqlplus and PDBs was not opened later, I do not remember.

                      Current listing from CDB:

                      COMP_IDCOMP_NAMEVERSIONSTATUSMODIFIED
                      APEXOracle Application Express4.2.4.00.08VALID18.2.2014 17:37
                      APSOLAP Analytic Workspace12.1.0.1.0VALID18.2.2014 17:37
                      CATALOGOracle Database Catalog Views12.1.0.1.0VALID18.2.2014 17:37
                      CATJAVAOracle Database Java Packages12.1.0.1.0VALID18.2.2014 17:37
                      CATPROCOracle Database Packages and Types12.1.0.1.0VALID18.2.2014 17:37
                      CONTEXTOracle Text12.1.0.1.0VALID18.2.2014 17:37
                      DVOracle Database Vault12.1.0.1.0VALID18.2.2014 17:37
                      JAVAVMJServer JAVA Virtual Machine12.1.0.1.0VALID18.2.2014 17:37
                      OLSOracle Label Security12.1.0.1.0VALID18.2.2014 17:37
                      ORDIMOracle Multimedia12.1.0.1.0VALID18.2.2014 17:37
                      OWMOracle Workspace Manager12.1.0.1.0VALID18.2.2014 17:37
                      RACOracle Real Application Clusters12.1.0.1.0OPTION OFF24-MAY-2013 13:19:57
                      SDOSpatial12.1.0.1.0VALID18.2.2014 17:37
                      XDBOracle XML Database12.1.0.1.0VALID18.2.2014 17:37
                      XMLOracle XDK12.1.0.1.0VALID18.2.2014 17:37
                      XOQOracle OLAP API12.1.0.1.0VALID18.2.2014 17:37

                       

                      Listing from PDB12

                      COMP_IDCOMP_NAMEVERSIONSTATUSMODIFIED
                      APEXOracle Application Express4.2.4.00.08VALID18.2.2014 12:42
                      APSOLAP Analytic Workspace12.1.0.1.0VALID16.1.2014 0:01
                      CATALOGOracle Database Catalog Views12.1.0.1.0VALID16.1.2014 0:01
                      CATJAVAOracle Database Java Packages12.1.0.1.0VALID16.1.2014 0:01
                      CATPROCOracle Database Packages and Types12.1.0.1.0VALID16.1.2014 0:01
                      CONTEXTOracle Text12.1.0.1.0VALID16.1.2014 0:01
                      DVOracle Database Vault12.1.0.1.0VALID16.1.2014 0:01
                      JAVAVMJServer JAVA Virtual Machine12.1.0.1.0VALID16.1.2014 0:01
                      OLSOracle Label Security12.1.0.1.0VALID16.1.2014 0:01
                      ORDIMOracle Multimedia12.1.0.1.0VALID16.1.2014 0:01
                      OWMOracle Workspace Manager12.1.0.1.0VALID16.1.2014 0:01
                      RACOracle Real Application Clusters12.1.0.1.0OPTION OFF24-MAY-2013 13:19:59
                      SDOSpatial12.1.0.1.0VALID16.1.2014 0:01
                      XDBOracle XML Database12.1.0.1.0VALID16.1.2014 0:01
                      XMLOracle XDK12.1.0.1.0VALID16.1.2014 0:01
                      XOQOracle OLAP API12.1.0.1.0VALID16.1.2014 0:01

                       

                      Status of package:

                       

                      CON_IDSTATUSOWNEROBJECT_NAMEOBJECT_TYPECREATEDLAST_DDL_TIME
                      1VALIDSYSHTMLDB_SYSTEMPACKAGE BODY24.5.2013 0:0018.2.2014 0:00
                      3INVALIDSYSHTMLDB_SYSTEMPACKAGE BODY24.5.2013 0:0024.5.2013 0:00
                      2INVALIDSYSHTMLDB_SYSTEMPACKAGE BODY24.5.2013 0:0024.5.2013 0:00

                      To summarize, during APEX reinstall there was definitely some problem, because I could not open PDB12 in read-write, only in RESTRICTED mode. However, after deinstall using newest set of APEX scripts downloaded from web, and install of newest APEX no problem with opening PDBs anymore.

                       

                      To be honest, I do not care how went it wrong, I would like to know how to recompile invalid package in PDB referred to CDB.

                      • 8. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
                        user10363847

                        Solomon Yakobson wrote:

                         

                        user10363847 wrote:

                         

                         

                        3) Deinstall of APEX using ORACLE_HOME/apex/ deinstall scripts --> FAILED !  (because scripts was not ready for patched CDB I suspect)

                         

                         

                        Did you run apxremov.sql or apxremov_con.sql? Former deinstalls apex just in CDB while latter in CDB and all other containers.

                         

                        SY.

                        <script>_con.sql version of deinstall and install scripts were used.

                        However first deinstall failed, and succeeded only after downloading latest apex scripts 4.2.4.

                        I believe that default apex scripts could not handle changes from regular database patches applied meantime to 12c (?)

                        • 9. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
                          ***Anuj***

                          Hi There

                           

                          Check this note

                           


                          HTH

                          Anuj

                          • 11. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
                            rp0428

                            user10363847 wrote:

                             

                            Thanks, I will remove it then.

                            You will remove 'what'?

                             

                            Post the steps you used to resolve the problem. Do NOT post quotes or content from MOS articles, however.

                            • 12. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
                              user10363847

                              I mean HTMLDB_SYSTEM package is what I have dropped, based on MOS it is not anymore part of APEX 4.x

                              • 13. Re: How to recompile invalid PKG in PDB refering to CDB as METADATA LINK
                                rp0428

                                Thanks for posting that resolution!