11 Replies Latest reply: Jan 16, 2013 12:34 AM by 966638 RSS

    EBS database  pre-upgrade tool

    966638
      Hi All,

      I am upgrading EBS database from 10.2.0.3 to 11.2.0.3
      OS - RHEL 5
      EBS - 12.1.3


      While running pre-upgrade too , utl112i.sql am getting some warning. Please help me what step I can follow for this warning.


      WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
      .... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
      .... USER APPS has dependent objects.



      Thanks,
        • 1. Re: EBS database  pre-upgrade tool
          Hussein Sawwan-Oracle
          963635 wrote:
          Hi All,

          I am upgrading EBS database from 10.2.0.3 to 11.2.0.3
          OS - RHEL 5
          EBS - 12.1.3


          While running pre-upgrade too , utl112i.sql am getting some warning. Please help me what step I can follow for this warning.


          WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
          .... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
          .... USER APPS has dependent objects.
          Please see the Upgrade manual (Page 3-13) -- http://docs.oracle.com/cd/E11882_01/server.112/e23633.pdf

          Thanks,
          Hussein
          • 2. Re: EBS database  pre-upgrade tool
            Srini Chavali-Oracle
            Pl also ensure that you are running the latest version of the pre-upgrade tool

            How to Download and Run Oracle's Database Pre-Upgrade Utility [ID 884522.1]

            HTH
            Srini
            • 3. Re: EBS database  pre-upgrade tool
              Hussein Sawwan-Oracle
              Srini Chavali wrote:
              Pl also ensure that you are running the latest version of the pre-upgrade tool

              How to Download and Run Oracle's Database Pre-Upgrade Utility [ID 884522.1]

              HTH
              Srini
              This is already mentioned in the upgrade doc referenced above -- http://docs.oracle.com/cd/E11882_01/server.112/e23633.pdf

              Thanks,
              Hussein
              • 4. Re: EBS database  pre-upgrade tool
                966638
                Srini,Hussein.

                Thanks for the update. I have gone through the mentioned link and doc - http://docs.oracle.com/cd/E11882_01/network.112/e16543.pdf.

                I didn't understand properly. And we haven't implement LDAP/AD inegration to EBS. And this DB need a connection other database thorugh DBLINK.


                When I query : select * from dba_dependencies where referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDE','DBMS_LDAP') and owner NOT IN('SYS','PUBLIC','ORDPLUGINS'); Getting a long output. Sorry to put the below.


                APPS OKS_MAIL PACKAGE PUBLIC UTL_SMTP SYNONYM HARD
                APPS MTH_EVENT_PKG PACKAGE BODY PUBLIC UTL_SMTP SYNONYM HARD
                APPS IBY_BANKACCXFR_PUB PACKAGE BODY PUBLIC UTL_TCP SYNONYM HARD
                APPS JTF_DIAGNOSTIC_COREAPI PACKAGE BODY PUBLIC UTL_TCP SYNONYM HARD
                APPS OKS_MAIL PACKAGE BODY PUBLIC UTL_TCP SYNONYM HARD
                APPS WSH_OTM_HTTP_UTL PACKAGE BODY PUBLIC UTL_TCP SYNONYM HARD
                APPS HZ_HTTP_PKG PACKAGE BODY PUBLIC UTL_TCP SYNONYM HARD
                APPS IBY_NETUTILS_PVT PACKAGE BODY PUBLIC UTL_TCP SYNONYM HARD
                APPS IBY_PAYMENT_ADAPTER_PUB PACKAGE BODY PUBLIC UTL_TCP SYNONYM HARD
                APPS IBY_BANKACCXFR_PUB PACKAGE BODY APPS UTL_TCP NON-EXISTENT HARD
                APPS JTF_DIAGNOSTIC_COREAPI PACKAGE BODY APPS UTL_TCP NON-EXISTENT HARD
                APPS OKS_MAIL PACKAGE BODY APPS UTL_TCP NON-EXISTENT HARD
                APPS WSH_OTM_HTTP_UTL PACKAGE BODY APPS UTL_TCP NON-EXISTENT HARD
                APPS HZ_HTTP_PKG PACKAGE BODY APPS UTL_TCP NON-EXISTENT HARD
                APPS IBY_NETUTILS_PVT PACKAGE BODY APPS UTL_TCP NON-EXISTENT HARD
                APPS IBY_PAYMENT_ADAPTER_PUB PACKAGE BODY APPS UTL_TCP NON-EXISTENT HARD

                OWNER NAME TYPE REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_LINK_NAME DEPE
                --------------- ---------------------------------------- ----------------- -------------------- ------------------------------ ----------------- ------------------------------ ----
                APPS FND_OID_PLUG PACKAGE BODY APPS DBMS_LDAP NON-EXISTENT HARD
                APPS FND_SSO_REGISTRATION PACKAGE BODY APPS DBMS_LDAP NON-EXISTENT HARD
                APPS FND_LDAP_UTIL PACKAGE APPS DBMS_LDAP NON-EXISTENT HARD
                APPS FND_SSO_REGISTRATION PACKAGE APPS DBMS_LDAP NON-EXISTENT HARD
                APPS FND_LDAP_USER PACKAGE APPS DBMS_LDAP NON-EXISTENT HARD
                APPS FND_OID_PLUG PACKAGE APPS DBMS_LDAP NON-EXISTENT HARD
                APPS FND_OID_UTIL PACKAGE APPS DBMS_LDAP NON-EXISTENT HARD
                APPS WF_OID PACKAGE BODY APPS DBMS_LDAP NON-EXISTENT HARD
                APPS WF_LDAP PACKAGE BODY APPS DBMS_LDAP NON-EXISTENT HARD
                APPS FND_OID_UTIL PACKAGE BODY APPS DBMS_LDAP NON-EXISTENT HARD
                APPS FND_LDAP_UTIL PACKAGE BODY APPS DBMS_LDAP NON-EXISTENT HARD
                APPS WF_OID PACKAGE APPS DBMS_LDAP NON-EXISTENT HARD
                APPS FND_OID_DIAG PACKAGE BODY APPS DBMS_LDAP NON-EXISTENT HARD
                APPS FND_LDAP_USER PACKAGE BODY APPS DBMS_LDAP NON-EXISTENT HARD
                APPS WSH_U_CSPV PACKAGE BODY APPS UTL_HTTP NON-EXISTENT HARD
                APPS MSC_E1APS_UTIL PACKAGE BODY APPS UTL_HTTP NON-EXISTENT HARD
                APPS BIS_CORRECTIVE_ACTION_PVT PACKAGE BODY APPS UTL_HTTP NON-EXISTENT HARD
                APPS HZ_LOCATION_SERVICES_PUB PACKAGE APPS UTL_HTTP NON-EXISTENT HARD
                APPS OKS_MAIL PACKAGE BODY APPS UTL_HTTP NON-EXISTENT HARD
                APPS BIS_PMV_PMF_PVT PACKAGE BO




                Am getting total - 166 rows selected


                Kndly suggest me what I need to do here before upgrade and after upgrade. In the document, need to do some action after upgrade to 11.2.0.3. But I didn't get a clarity what I need to do. I know this showing my lack in security knowledge.

                How important this one and What step I can follow for this output. Do I need to check/consider for this warnings. ?



                Thanks and sorry to ask these problems.
                • 5. Re: EBS database  pre-upgrade tool
                  Hussein Sawwan-Oracle
                  Please review "Assessing Dependencies and Adding ACLs for Network Utility Packages" section in the upgrade doc.

                  Thanks,
                  Hussein
                  • 6. Re: EBS database  pre-upgrade tool
                    966638
                    Thanks for the reply.

                    I hv gone thru this note. To make a calrity


                    a) Before upgrading database ,

                    1 - when I quey -> select * from DBA_NETWORK_ACLS;

                    select * from DBA_NETWORK_ACLS
                    *
                    ERROR at line 1:
                    ORA-00942: table or view does not exist


                    2- select * from dba_dependencies where referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDE','DBMS_LDAP') and owner NOT IN('SYS','PUBLIC','ORDPLUGINS');
                    Getting 166 rows with owner "Apps"



                    b)After upgrade to 11.2.0.3

                    1 - When query -> select * from DBA_NETWORK_ACLS;

                    SQL> select * from DBA_NETWORK_ACLS;

                    HOST LOWER_PORT UPPER_PORT ACL ACLID
                    --------------- ---------- ---------- ---------------------------------------- --------------------------------
                    * /sys/acls/OracleEBS.xml D141CD111203205CE043B8321CACECC4


                    2 - And when I qurey -> select * from dba_dependencies where referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDE','DBMS_LDAP') and owner NOT IN('SYS','PUBLIC','ORDPLUGINS');

                    getting 167 rows with owner as 'Apps"


                    c) In this condition, do I need to do any step here ?


                    d) If yes for (c), below step is enough and could you please correct me with proper step.


                    DECLARE
                    ACL_PATH VARCHAR2(4000);
                    BEGIN
                    SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
                    WHERE HOST = 'host_name' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

                    IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH,'user_name','connect') IS NULL THEN
                    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'user_name',TRUE,'connect');

                    END IF;
                    EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml','ACL_description','user_name',True,'connect');
                    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name');

                    END;
                    /
                    COMMIT;


                    As per my understanding the values will be for the below variables

                    host_name=*
                    user_name=apps
                    ACL_name.xml = /sys/acls/OracleEBS.xml ( But there no system directory " acls " file in this path ( /sys/ )
                    ACL_description = anything
                    ACL_PATH = ?


                    e) Please let me know what values I hv to give for [ host_name,user_name,ACL_PATH, ACL_name.xml , ACL_description ] as per my above output. ?



                    Thanks,
                    • 7. Re: EBS database  pre-upgrade tool
                      Hussein Sawwan-Oracle
                      As per my understanding the values will be for the below variables

                      host_name=*
                      user_name=apps
                      ACL_name.xml = /sys/acls/OracleEBS.xml ( But there no system directory " acls " file in this path ( /sys/ )
                      ACL_description = anything
                      ACL_PATH = ?


                      e) Please let me know what values I hv to give for [ host_name,user_name,ACL_PATH, ACL_name.xml , ACL_description ] as per my above output. ?
                      Please see (Understanding DBMS_NETWORK_ACL_ADMIN With Example [ID 1080105.1]).

                      Thanks,
                      Hussein
                      • 8. Re: EBS database  pre-upgrade tool
                        966638
                        Thanks Hussein,

                        I will go through this and update you my feedback.



                        Thanks,
                        Salim
                        • 9. Re: EBS database  pre-upgrade tool
                          user99472
                          Folks, I spent a few days on this exact same question awhile back when doing my upgrade. I searched and searched and couldn't find anything that made sense as it relates to this step.

                          Eventually, an MOS ticket with the EBS Admin team gave me this answer:

                          ......................................................................................................
                          When you run AutoConfig (which is part of the upgrade procedure), AutoConfig runs a script called txkcreateACL.sh, which will create the ACLs for you:
                          # | DESCRIPTION
                          # | Script to create ACL required for 11g DB for utl_http pkg
                          ......................................................................................................

                          So, the quick answer is that nothing needs to be done related to EBS and ACLs. After spending a huge amount of time on this task I requested that DOC ID 881505.1 be updated to reflect this important information. They then added this line in Oct, 2012:

                          ......................................................................................................
                          AutoConfig will manage the configuration of the E-Business Suite Network ACLs.
                          ......................................................................................................

                          Hope this helps.
                          • 10. Re: EBS database  pre-upgrade tool
                            Hussein Sawwan-Oracle
                            Thanks for the update, user11988333!
                            • 11. Re: EBS database  pre-upgrade tool
                              966638
                              Great Thanks for the update, user11988333!