14 Replies Latest reply: Dec 3, 2012 11:43 PM by 953987 RSS

    EBS Database upgrade to 11.2.0.3

    953987
      Hi All,

      I am in stage of upgrading EBS database to 11.2.0.3 from 10.2.0.5 and database is standalone.

      Version - EBS 12.1.3
      OS - RHEL 5.5 32bit

      I am follwoing the note : Interoperability Notes EBS R12 with Database 11gR2[ 1058763.1].

      1- Step 2: Apply patch 6400501 to the IAS 10.1.2. Do I really need to apply this patch ?( I am not sure how I can check whether this has already applied or not)

      2- Step 6: Install Oracle database 11g Products from the 11g Example CDs. Do I need to do this step and please ket me know whre I can get this CD ( I have 2 files : p10404530_112030_LINUX_1of7.zip and p10404530_112030_LINUX_2of7.zip)
      Do I need to download anything extra here to fullfil the step.


      3- Step 8: Apply additional 11.2.0.3 RDBMS pathes :

      Before applying this additional patches, I hv to upgrade the database to 11.2.0.3. Please correct me if I am wrong.
      As per the note, step 13 is to upgrade database to 11.2.0.3. I got confused here, It means, Do I need to apply additional patches on 10.2.0.5 database then upgrade to 11.2.0.3





      Kindly help me on this.


      Thanks,
        • 1. Re: EBS Database upgrade to 11.2.0.3
          Srini Chavali-Oracle
          950984 wrote:
          Hi All,

          I am in stage of upgrading EBS database to 11.2.0.3 from 10.2.0.5 and database is standalone.

          Version - EBS 12.1.3
          OS - RHEL 5.5 32bit

          I am follwoing the note : Interoperability Notes EBS R12 with Database 11gR2[ 1058763.1].

          1- Step 2: Apply patch 6400501 to the IAS 10.1.2. Do I really need to apply this patch ?( I am not sure how I can check whether this has already applied or not)
          Since you are on Linux, you need to apply this patch. Follow the steps in the README of patch 6400501. You can check if this patch has been applied by using the opatch lsinventory command (after invoking the 10.1.3 HOME in a separate window).
          2- Step 6: Install Oracle database 11g Products from the 11g Example CDs. Do I need to do this step and please ket me know whre I can get this CD ( I have 2 files : p10404530_112030_LINUX_1of7.zip and p10404530_112030_LINUX_2of7.zip)
          Do I need to download anything extra here to fullfil the step.
          Yes - installation of the Examples CD is a requirement. It states so on the step heading. You will need to download the rest of the files (3,4,5,6,7 of 7) for 11.2.0.3 - File 6 is the examples - see the README for 11.2.0.3 patchset.

          >
          3- Step 8: Apply additional 11.2.0.3 RDBMS pathes :

          Before applying this additional patches, I hv to upgrade the database to 11.2.0.3. Please correct me if I am wrong.
          As per the note, step 13 is to upgrade database to 11.2.0.3. I got confused here, It means, Do I need to apply additional patches on 10.2.0.5 database then upgrade to 11.2.0.3

          These patches need to be applied before the upgrade. Any post installation steps need to be performed after the upgrade (see Step 15).

          >
          >
          >
          Kindly help me on this.


          Thanks,
          HTH
          Srini
          • 2. Re: EBS Database upgrade to 11.2.0.3
            Hussein Sawwan-Oracle
            Srini Chavali wrote:

            Since you are on Linux, you need to apply this patch. Follow the steps in the README of patch 6400501. You can check if this patch has been applied by using the opatch lsinventory command (after invoking the 10.1.3 HOME in a separate window).
            This is not correct, you need to source the application env file (10.1.2 ORACLE_HOME) and NOT 10.1.3 ORACLE_HOME.

            Thanks,
            Hussein
            • 3. Re: EBS Database upgrade to 11.2.0.3
              953987
              Thanks Srini and Hussein.

              I will follow your comments and please help me if I get a issue during upgrade.
              • 4. Re: EBS Database upgrade to 11.2.0.3
                953987
                Hi,

                I am in stage of running dbua. it is trunning for almost 3hr now. Now on post upgrade steps.In dbua log, it is compiling the objects.

                Please let me know normally how long this dbua task will take ?

                Any problem in my upgrade/do I need to stop ?

                But I can see the changes in alert.log file and no error is there.Only redo log file changing.



                Logs at this time:


                sqls.log
                ----------
                set pagesize 0
                set feedback on
                set linesize 4000
                set sqlprompt 'SQL_ENGINE_END_OF_SQL'
                show sqlprompt
                set sqlprompt 'SQL>'
                connect /****** as SYSDBA
                set echo on;
                @/u01/ora/DEV/db/11.2.0/rdbms/admin/utlprp.sql << 1 arguments passed >>
                set heading on
                set timing off
                set pagesize 0
                set feedback on
                set linesize 4000
                set sqlprompt 'SQL_ENGINE_END_OF_SQL'
                show sqlprompt
                set sqlprompt 'SQL>'



                PostUpgrade.log
                ----------------------
                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
                2 threads pls_integer := &&1;
                3 BEGIN
                4 utl_recomp.recomp_parallel(threads);
                5 END;



                alert_DEV.log

                Wed Nov 28 12:56:41 2012
                Completed checkpoint up to RBA [0x62.2.10], SCN: 3131302319
                Wed Nov 28 12:59:27 2012
                Beginning log switch checkpoint up to RBA [0x63.2.10], SCN: 3131324935
                Thread 1 advanced to log sequence 99 (LGWR switch)
                Current log# 3 seq# 99 mem# 0: /u02/oradata/DEV/log03a.dbf
                Current log# 3 seq# 99 mem# 1: /u02/oradata/DEV/log03b.dbf
                Wed Nov 28 12:59:52 2012
                Completed checkpoint up to RBA [0x63.2.10], SCN: 3131324935
                Wed Nov 28 13:00:46 2012
                Beginning log switch checkpoint up to RBA [0x64.2.10], SCN: 3131339427
                Thread 1 advanced to log sequence 100 (LGWR switch)
                Current log# 1 seq# 100 mem# 0: /u02/oradata/DEV/log01a.dbf
                Current log# 1 seq# 100 mem# 1: /u02/oradata/DEV/log01b.dbf


                Fatal NI connect error 12541, connecting to:

                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ts)(PORT=1541))(CONNECT_DATA=(SERVICE_NAME=DEV)(INSTANCE_NAME=DEV)(CID=(PROGRAM=oracle)(HOST=ts)(USER=oradevdb))))


                VERSION INFORMATION:
                TNS for Linux: Version 11.2.0.3.0 - Production
                Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
                TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
                Time: 28-NOV-2012 12:54:10
                Tracing not turned on.
                Tns error struct:
                ns main err code: 12541

                TNS-12541: TNS:no listener
                ns secondary err code: 12560
                nt main err code: 511


                I didn't create a listener and I hv stopped listener from old home. Do I need to create listener for 11.2.0 home and this need to be up ?

                And when I tried to do sqlplus '/as sysdba' from another session to check the number of invalid objetcs, It is showing connected to an idle instance.So database not started to run utlrp.sql.




                Please help on this asap.



                Thanks,
                • 5. Re: EBS Database upgrade to 11.2.0.3
                  Srini Chavali-Oracle
                  DBUA will take upto 8-10 hours depending on your hardware specs. Let it complete compiling all of the invalid objects. When you are trying to connect to the database, have you set all of the environment variables to point to the 11gR2 HOME ? Pl post the output of the command "env | sort" just before you issue the "sqlplus" command

                  HTH
                  Srini
                  • 6. Re: EBS Database upgrade to 11.2.0.3
                    Hussein Sawwan-Oracle
                    I am in stage of running dbua. it is trunning for almost 3hr now. Now on post upgrade steps.In dbua log, it is compiling the objects.

                    Please let me know normally how long this dbua task will take ?
                    It depends on many factors. If it is at the stage of compiling invalid objects then it is normal for it to take that long.
                    Any problem in my upgrade/do I need to stop ?
                    No. Please run the following query to check the number of invalid objects and make sure it is decreasing.
                    SQL> select count(*)
                    From dba_objects
                    Where status = 'INVALID';
                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ts)(PORT=1541))(CONNECT_DATA=(SERVICE_NAME=DEV)(INSTANCE_NAME=DEV)(CID=(PROGRAM=oracle)(HOST=ts)(USER=oradevdb))))
                    Do you have proper entry in the hosts file?

                    Is the database listener up and running?
                    I didn't create a listener and I hv stopped listener from old home. Do I need to create listener for 11.2.0 home and this need to be up ?
                    Yes, please.
                    And when I tried to do sqlplus '/as sysdba' from another session to check the number of invalid objetcs, It is showing connected to an idle instance.So database not started to run utlrp.sql.
                    Please make sure you set ORACLE_SID before you connect to sqlplus as sysdba.

                    Thanks,
                    Hussein
                    • 7. Re: EBS Database upgrade to 11.2.0.3
                      953987
                      Thanks Hussein and Srini again.

                      I hv stopped the first one and started another again. Again this showing 80% on PostUpgrade step.


                      Before I do dbua,

                      1 -- I have copied pfile form 10g Home to 11g and removed the hidden parameters.

                      2 - I hv copied tnsname.ora from 10g/network/admin to 11g/network/admin/

                      3 - stopped listener from 10gHome and created one in 11gHomw with netca and started



                      4 - Below script I can't check

                      SQL> select count(*) From dba_objects Where status = 'INVALID';


                      When I do sqlplus '/as sysdba' , am getting below output

                      [oradevdb@qvcts01adm0 ~]$ sqlplus '/as sysdba'
                      libnuma: Warning: /sys not mounted or no numa system. Assuming one node: No such file or directory

                      SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 29 10:45:49 2012

                      Copyright (c) 1982, 2011, Oracle. All rights reserved.

                      Connected to an idle instance.

                      SQL> exit




                      5 - Please find my below env details -> env|sort

                      [oradevdb@ts ~]$ env|sort
                      _=/bin/env
                      CVS_RSH=ssh
                      G_BROKEN_FILENAMES=1
                      HISTSIZE=1000
                      HOME=/home/oradevdb
                      HOSTNAME=ts
                      INPUTRC=/etc/inputrc
                      LANG=en_US.UTF-8
                      LD_LIBRARY_PATH=/u01/oraapps/DEV/db/11.2.0//lib
                      LESSOPEN=|/usr/bin/lesspipe.sh %s
                      LOGNAME=oradevdb
                      LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
                      MAIL=/var/spool/mail/oradevdb
                      ORACLE_BASE=/u01/oraapps/DEV/db/
                      ORACLE_HOME=/u01/oraapps/DEV/db/11.2.0/
                      ORACLE_SID=DEV
                      ORA_NLS10=/u01/oraapps/DEV/db/11.2.0/nls/data/9idata
                      PATH=/u01/oraapps/DEV/db/11.2.0//bin:/u01/oraapps/DEV/db/11.2.0//perl/bin:/u01/oraapps/DEV/db/11.2.0//OPatch:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin
                      PERL5LIB=/u01/oraapps/DEV/db/11.2.0/perl/lib/5.10.0/:/u01/oraapps/DEV/db/11.2.0/perl/lib/site_perl/5.10.0/
                      PWD=/home/oradevdb
                      SHELL=/bin/bash
                      SHLVL=1
                      SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
                      TERM=xterm
                      TNS_ADMIN=/u01/oraapps/DEV/db/11.2.0//network/admin
                      USER=oradevdb
                      [oradevdb@ts ~]$




                      6 - I tried to do sqlplus '/as sysdba after doing the export ORACLE_SID= DEV. Still the same output.



                      7 - Please find Postupgrade.log

                      Database closed.
                      Database dismounted.
                      ORACLE instance shut down.
                      ORACLE instance started.
                      Total System Global Area 1071333376 bytes
                      Fixed Size 1349732 bytes
                      Variable Size 427820956 bytes
                      Database Buffers 629145600 bytes
                      Redo Buffers 13017088 bytes
                      Database mounted.
                      Database opened.
                      Connected.


                      Rem Copyright (c) 2003, 2010, Oracle and/or its affiliates.
                      Rem All rights reserved.
                      Rem
                      Rem NAME
                      Rem utlprp.sql - Recompile invalid objects in the database
                      Rem
                      Rem DESCRIPTION
                      Rem This script recompiles invalid objects in the database.
                      Rem
                      Rem This script is typically used to recompile invalid objects
                      Rem remaining at the end of a database upgrade or downgrade.
                      Rem
                      Rem Although invalid objects are automatically recompiled on demand,
                      Rem running this script ahead of time will reduce or eliminate
                      Rem latencies due to automatic recompilation.
                      Rem
                      Rem This script is a wrapper based on the UTL_RECOMP package.
                      Rem UTL_RECOMP provides a more general recompilation interface,
                      Rem including options to recompile objects in a single schema. Please
                      Rem see the documentation for package UTL_RECOMP for more details.
                      Rem
                      Rem INPUTS
                      Rem The degree of parallelism for recompilation can be controlled by
                      Rem providing a parameter to this script. If this parameter is 0 or
                      Rem NULL, UTL_RECOMP will automatically determine the appropriate
                      Rem level of parallelism based on Oracle parameters cpu_count and
                      Rem parallel_threads_per_cpu. If the parameter is 1, sequential
                      Rem recompilation is used. Please see the documentation for package
                      Rem UTL_RECOMP for more details.
                      Rem
                      Rem NOTES
                      Rem * You must be connected AS SYSDBA to run this script.
                      Rem * There should be no other DDL on the database while running the
                      Rem script. Not following this recommendation may lead to deadlocks.
                      Rem
                      Rem MODIFIED (MM/DD/YY)
                      Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name
                      Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
                      Rem not needed.
                      Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
                      Rem cdilling 01/21/08 - add support for ORA-30552

                      Rem cdilling 08/27/07 - check disabled indexes only
                      Rem cdilling 05/22/07 - add support for ORA-38301
                      Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
                      Rem rburns 03/17/05 - use dbms_registry_sys
                      Rem gviswana 02/07/05 - Post-compilation diagnostics
                      Rem gviswana 09/09/04 - Auto tuning and diagnosability
                      Rem rburns 09/20/04 - fix validate_components
                      Rem gviswana 12/09/03 - Move functional-index re-enable here
                      Rem gviswana 06/04/03 - gviswana_bug-2814808
                      Rem gviswana 05/28/03 - Created
                      Rem

                      SET VERIFY OFF;

                      SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;
                      COMP_TIMESTAMP UTLRP_BGN 2012-11-29 10:36:36

                      1 row selected.


                      DOC
                      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
                      2 threads pls_integer := &&1;
                      3 BEGIN
                      4 utl_recomp.recomp_parallel(threads);
                      5 END;




                      8 - When I do ps-ef|grep smon

                      [root@ts upgrade1]# ps -ef|grep smon
                      oradevdb 6498 1 1 10:36 ? 00:00:14 ora_smon_DEV



                      9 - But as per the above output(Postupgrade.log) database is opened. How I can connect to that database ?



                      10 - Please suggest what I can do here.



                      11 - Manual upgrade will be ok ? If yes, please confirm the scripts to run/ missing ones

                      @catupgrd.sql -> startup -> @utlu112s.sql -> @utlrp.sql ->upgrade time zone -> stop and start DB -> then the from step 18 [ ID:1058763.1]





                      Thanks,
                      • 8. Re: EBS Database upgrade to 11.2.0.3
                        Srini Chavali-Oracle
                        950984 wrote:
                        Thanks Hussein and Srini again.

                        I hv stopped the first one and started another again. Again this showing 80% on PostUpgrade step.


                        Before I do dbua,

                        1 -- I have copied pfile form 10g Home to 11g and removed the hidden parameters.
                        Pl confirm that you have also removed any parameters that are obsolete in 11gR2 - these would have been identified by the pre-upgrade script.
                        2 - I hv copied tnsname.ora from 10g/network/admin to 11g/network/admin/

                        3 - stopped listener from 10gHome and created one in 11gHomw with netca and started



                        4 - Below script I can't check

                        SQL> select count(*) From dba_objects Where status = 'INVALID';


                        When I do sqlplus '/as sysdba' , am getting below output

                        [oradevdb@qvcts01adm0 ~]$ sqlplus '/as sysdba'
                        libnuma: Warning: /sys not mounted or no numa system. Assuming one node: No such file or directory
                        For the libnuma warning, pl see this MOS DOc

                        After Installing 10.2.0.4, Database Startup Reports "libnuma: Warning: /sys not mounted. Assuming one node per CPU: No such file or directory" [ID 559636.1]
                        SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 29 10:45:49 2012

                        Copyright (c) 1982, 2011, Oracle. All rights reserved.

                        Connected to an idle instance.

                        SQL> exit




                        5 - Please find my below env details -> env|sort

                        [oradevdb@ts ~]$ env|sort
                        Your earlier sqlplus command was from a different host ( [oradevdb@qvcts01adm0 ~]$ sqlplus '/as sysdba' ) - is this intentional ?
                        _=/bin/env
                        CVS_RSH=ssh
                        G_BROKEN_FILENAMES=1
                        HISTSIZE=1000
                        HOME=/home/oradevdb
                        HOSTNAME=ts
                        INPUTRC=/etc/inputrc
                        LANG=en_US.UTF-8
                        LD_LIBRARY_PATH=/u01/oraapps/DEV/db/11.2.0//lib
                        LESSOPEN=|/usr/bin/lesspipe.sh %s
                        LOGNAME=oradevdb
                        LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
                        MAIL=/var/spool/mail/oradevdb
                        ORACLE_BASE=/u01/oraapps/DEV/db/
                        ORACLE_HOME=/u01/oraapps/DEV/db/11.2.0/
                        ORACLE_SID=DEV
                        ORA_NLS10=/u01/oraapps/DEV/db/11.2.0/nls/data/9idata
                        PATH=/u01/oraapps/DEV/db/11.2.0//bin:/u01/oraapps/DEV/db/11.2.0//perl/bin:/u01/oraapps/DEV/db/11.2.0//OPatch:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin
                        PERL5LIB=/u01/oraapps/DEV/db/11.2.0/perl/lib/5.10.0/:/u01/oraapps/DEV/db/11.2.0/perl/lib/site_perl/5.10.0/
                        PWD=/home/oradevdb
                        SHELL=/bin/bash
                        SHLVL=1
                        SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
                        TERM=xterm
                        TNS_ADMIN=/u01/oraapps/DEV/db/11.2.0//network/admin
                        USER=oradevdb
                        [oradevdb@ts ~]$

                        The variables look good.

                        >
                        >
                        6 - I tried to do sqlplus '/as sysdba after doing the export ORACLE_SID= DEV. Still the same output.
                        What does "same output" mean ?

                        >
                        >
                        7 - Please find Postupgrade.log

                        Database closed.
                        Database dismounted.
                        ORACLE instance shut down.
                        ORACLE instance started.
                        Total System Global Area 1071333376 bytes
                        Fixed Size 1349732 bytes
                        Variable Size 427820956 bytes
                        Database Buffers 629145600 bytes
                        Redo Buffers 13017088 bytes
                        Database mounted.
                        Database opened.
                        Connected.


                        Rem Copyright (c) 2003, 2010, Oracle and/or its affiliates.
                        Rem All rights reserved.
                        Rem
                        Rem NAME
                        Rem utlprp.sql - Recompile invalid objects in the database
                        Rem
                        Rem DESCRIPTION
                        Rem This script recompiles invalid objects in the database.
                        Rem
                        Rem This script is typically used to recompile invalid objects
                        Rem remaining at the end of a database upgrade or downgrade.
                        Rem
                        Rem Although invalid objects are automatically recompiled on demand,
                        Rem running this script ahead of time will reduce or eliminate
                        Rem latencies due to automatic recompilation.
                        Rem
                        Rem This script is a wrapper based on the UTL_RECOMP package.
                        Rem UTL_RECOMP provides a more general recompilation interface,
                        Rem including options to recompile objects in a single schema. Please
                        Rem see the documentation for package UTL_RECOMP for more details.
                        Rem
                        Rem INPUTS
                        Rem The degree of parallelism for recompilation can be controlled by
                        Rem providing a parameter to this script. If this parameter is 0 or
                        Rem NULL, UTL_RECOMP will automatically determine the appropriate
                        Rem level of parallelism based on Oracle parameters cpu_count and
                        Rem parallel_threads_per_cpu. If the parameter is 1, sequential
                        Rem recompilation is used. Please see the documentation for package
                        Rem UTL_RECOMP for more details.
                        Rem
                        Rem NOTES
                        Rem * You must be connected AS SYSDBA to run this script.
                        Rem * There should be no other DDL on the database while running the
                        Rem script. Not following this recommendation may lead to deadlocks.
                        Rem
                        Rem MODIFIED (MM/DD/YY)
                        Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name
                        Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
                        Rem not needed.
                        Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
                        Rem cdilling 01/21/08 - add support for ORA-30552

                        Rem cdilling 08/27/07 - check disabled indexes only
                        Rem cdilling 05/22/07 - add support for ORA-38301
                        Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
                        Rem rburns 03/17/05 - use dbms_registry_sys
                        Rem gviswana 02/07/05 - Post-compilation diagnostics
                        Rem gviswana 09/09/04 - Auto tuning and diagnosability
                        Rem rburns 09/20/04 - fix validate_components
                        Rem gviswana 12/09/03 - Move functional-index re-enable here
                        Rem gviswana 06/04/03 - gviswana_bug-2814808
                        Rem gviswana 05/28/03 - Created
                        Rem

                        SET VERIFY OFF;

                        SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;
                        COMP_TIMESTAMP UTLRP_BGN 2012-11-29 10:36:36

                        1 row selected.


                        DOC
                        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
                        2 threads pls_integer := &&1;
                        3 BEGIN
                        4 utl_recomp.recomp_parallel(threads);
                        5 END;

                        This indicates that the upgrade has completed and that parallel recompilation is going on.
                        >
                        >
                        8 - When I do ps-ef|grep smon

                        [root@ts upgrade1]# ps -ef|grep smon
                        oradevdb 6498 1 1 10:36 ? 00:00:14 ora_smon_DEV
                        This indicates that the DEV instance on server "ts" is up and running.

                        >
                        >
                        9 - But as per the above output(Postupgrade.log) database is opened. How I can connect to that database ?
                        Are you connected to the correct server and ORACLE_HOME ?

                        >
                        >
                        10 - Please suggest what I can do here.



                        11 - Manual upgrade will be ok ? If yes, please confirm the scripts to run/ missing ones

                        @catupgrd.sql -> startup -> @utlu112s.sql -> @utlrp.sql ->upgrade time zone -> stop and start DB -> then the from step 18 [ ID:1058763.1]

                        Not sure why you want to pursue a manual upgrade since DBUA is working as expected.

                        >
                        >
                        >
                        Thanks,
                        HTH
                        Srini
                        • 9. Re: EBS Database upgrade to 11.2.0.3
                          Hussein Sawwan-Oracle
                          I hv stopped the first one and started another again. Again this showing 80% on PostUpgrade step.
                          Why? What is the reason of stopping the first upgrade and starting a new one?

                          Did you restore the database before starting the upgrade again?
                          Before I do dbua,

                          1 -- I have copied pfile form 10g Home to 11g and removed the hidden parameters.

                          2 - I hv copied tnsname.ora from 10g/network/admin to 11g/network/admin/

                          3 - stopped listener from 10gHome and created one in 11gHomw with netca and started



                          4 - Below script I can't check

                          SQL> select count(*) From dba_objects Where status = 'INVALID';


                          When I do sqlplus '/as sysdba' , am getting below output

                          [oradevdb@qvcts01adm0 ~]$ sqlplus '/as sysdba'
                          libnuma: Warning: /sys not mounted or no numa system. Assuming one node: No such file or directory

                          SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 29 10:45:49 2012

                          Copyright (c) 1982, 2011, Oracle. All rights reserved.

                          Connected to an idle instance.

                          SQL> exit




                          5 - Please find my below env details -> env|sort

                          [oradevdb@ts ~]$ env|sort
                          _=/bin/env
                          CVS_RSH=ssh
                          G_BROKEN_FILENAMES=1
                          HISTSIZE=1000
                          HOME=/home/oradevdb
                          HOSTNAME=ts
                          INPUTRC=/etc/inputrc
                          LANG=en_US.UTF-8
                          LD_LIBRARY_PATH=/u01/oraapps/DEV/db/11.2.0//lib
                          LESSOPEN=|/usr/bin/lesspipe.sh %s
                          LOGNAME=oradevdb
                          LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
                          MAIL=/var/spool/mail/oradevdb
                          ORACLE_BASE=/u01/oraapps/DEV/db/
                          ORACLE_HOME=/u01/oraapps/DEV/db/11.2.0/
                          ORACLE_SID=DEV
                          ORA_NLS10=/u01/oraapps/DEV/db/11.2.0/nls/data/9idata
                          PATH=/u01/oraapps/DEV/db/11.2.0//bin:/u01/oraapps/DEV/db/11.2.0//perl/bin:/u01/oraapps/DEV/db/11.2.0//OPatch:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin
                          PERL5LIB=/u01/oraapps/DEV/db/11.2.0/perl/lib/5.10.0/:/u01/oraapps/DEV/db/11.2.0/perl/lib/site_perl/5.10.0/
                          PWD=/home/oradevdb
                          SHELL=/bin/bash
                          SHLVL=1
                          SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
                          TERM=xterm
                          TNS_ADMIN=/u01/oraapps/DEV/db/11.2.0//network/admin
                          USER=oradevdb
                          [oradevdb@ts ~]$

                          6 - I tried to do sqlplus '/as sysdba after doing the export ORACLE_SID= DEV. Still the same output.
                          What output?
                          7 - Please find Postupgrade.log

                          Database closed.
                          Database dismounted.
                          ORACLE instance shut down.
                          ORACLE instance started.
                          Total System Global Area 1071333376 bytes
                          Fixed Size 1349732 bytes
                          Variable Size 427820956 bytes
                          Database Buffers 629145600 bytes
                          Redo Buffers 13017088 bytes
                          Database mounted.
                          Database opened.
                          Connected.


                          Rem Copyright (c) 2003, 2010, Oracle and/or its affiliates.
                          Rem All rights reserved.
                          Rem
                          Rem NAME
                          Rem utlprp.sql - Recompile invalid objects in the database
                          Rem
                          Rem DESCRIPTION
                          Rem This script recompiles invalid objects in the database.
                          Rem
                          Rem This script is typically used to recompile invalid objects
                          Rem remaining at the end of a database upgrade or downgrade.
                          Rem
                          Rem Although invalid objects are automatically recompiled on demand,
                          Rem running this script ahead of time will reduce or eliminate
                          Rem latencies due to automatic recompilation.
                          Rem
                          Rem This script is a wrapper based on the UTL_RECOMP package.
                          Rem UTL_RECOMP provides a more general recompilation interface,
                          Rem including options to recompile objects in a single schema. Please
                          Rem see the documentation for package UTL_RECOMP for more details.
                          Rem
                          Rem INPUTS
                          Rem The degree of parallelism for recompilation can be controlled by
                          Rem providing a parameter to this script. If this parameter is 0 or
                          Rem NULL, UTL_RECOMP will automatically determine the appropriate
                          Rem level of parallelism based on Oracle parameters cpu_count and
                          Rem parallel_threads_per_cpu. If the parameter is 1, sequential
                          Rem recompilation is used. Please see the documentation for package
                          Rem UTL_RECOMP for more details.
                          Rem
                          Rem NOTES
                          Rem * You must be connected AS SYSDBA to run this script.
                          Rem * There should be no other DDL on the database while running the
                          Rem script. Not following this recommendation may lead to deadlocks.
                          Rem
                          Rem MODIFIED (MM/DD/YY)
                          Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name
                          Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
                          Rem not needed.
                          Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
                          Rem cdilling 01/21/08 - add support for ORA-30552

                          Rem cdilling 08/27/07 - check disabled indexes only
                          Rem cdilling 05/22/07 - add support for ORA-38301
                          Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
                          Rem rburns 03/17/05 - use dbms_registry_sys
                          Rem gviswana 02/07/05 - Post-compilation diagnostics
                          Rem gviswana 09/09/04 - Auto tuning and diagnosability
                          Rem rburns 09/20/04 - fix validate_components
                          Rem gviswana 12/09/03 - Move functional-index re-enable here
                          Rem gviswana 06/04/03 - gviswana_bug-2814808
                          Rem gviswana 05/28/03 - Created
                          Rem

                          SET VERIFY OFF;

                          SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;
                          COMP_TIMESTAMP UTLRP_BGN 2012-11-29 10:36:36

                          1 row selected.


                          DOC
                          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
                          2 threads pls_integer := &&1;
                          3 BEGIN
                          4 utl_recomp.recomp_parallel(threads);
                          5 END;
                          And this is what you posted before, which indicates that the script is compiling the invalid objects.
                          8 - When I do ps-ef|grep smon

                          [root@ts upgrade1]# ps -ef|grep smon
                          oradevdb 6498 1 1 10:36 ? 00:00:14 ora_smon_DEV

                          9 - But as per the above output(Postupgrade.log) database is opened. How I can connect to that database ?
                          What if you issue? --> sqlplus / as sysdba (after sourcing the correct env file)
                          10 - Please suggest what I can do here.


                          11 - Manual upgrade will be ok ? If yes, please confirm the scripts to run/ missing ones

                          @catupgrd.sql -> startup -> @utlu112s.sql -> @utlrp.sql ->upgrade time zone -> stop and start DB -> then the from step 18 [ ID:1058763.1]
                          No, it is not supported.

                          Thanks,
                          Hussein
                          • 10. Re: EBS Database upgrade to 11.2.0.3
                            953987
                            Thanks Hussein and Srini.


                            My upgrade has been completed successfully without doing anything(took 5 hrs).Thanks for the support.


                            1 - I hv stopped previous upgrade and restored from backup
                            2 - Removed obsolete parameters from pfile.
                            3 - Thanks for the MOS Docs - 559636.1 for libnuma warning


                            My main concern was - even if I do sqlplus /'as sysdba' from the same console where dbua running. it is showing "connected to an idle instance".


                            I have completed all the steps mentioned in docs and I could able to start apps.



                            Please clarify me on this.

                            1 - In my dbside, DB init file: utl_file_dir='/usr/tmp','/usr/tmp','$ORACLE_HOME/appsutil/outbound/DEV_ts','/usr/tmp'

                            and for applmgr is -> echo $APPLPTMP = /usr/tmp

                            But I can't find a directory named "outbound" in "$ORACLE_HOME/appsutil"( in both 10g and 11g homes).

                            Do I need to do anything here as APPLPTMP is showing /usr/tmp.?

                            Please suggest me what iis the best to make my system in a proper way.



                            Thanks,
                            • 11. Re: EBS Database upgrade to 11.2.0.3
                              Hussein Sawwan-Oracle
                              My main concern was - even if I do sqlplus /'as sysdba' from the same console where dbua running. it is showing "connected to an idle instance".
                              What if you connect as apps user?

                              Please make sure that ORACLE_SID is set correctly.
                              1 - In my dbside, DB init file: utl_file_dir='/usr/tmp','/usr/tmp','$ORACLE_HOME/appsutil/outbound/DEV_ts','/usr/tmp'

                              and for applmgr is -> echo $APPLPTMP = /usr/tmp

                              But I can't find a directory named "outbound" in "$ORACLE_HOME/appsutil"( in both 10g and 11g homes).

                              Do I need to do anything here as APPLPTMP is showing /usr/tmp.?

                              Please suggest me what iis the best to make my system in a proper way.
                              No, you can keep it set to /usr/tmp and you should be good -- Difference between APPLPTMP and APPLTMP Directories in EBS [ID 1355735.1]

                              Thanks,
                              Hussein
                              • 12. Re: EBS Database upgrade to 11.2.0.3
                                953987
                                Thanks...


                                Sorry to ask you simple questions. I am new to DBA.

                                This is cloned instance from production and I want to make a proper steps before I upgrade in production.


                                1 - Reg:Java Version


                                DBmgr - > $java -version
                                $Java -version
                                java version "1.4.2"
                                gij (GNU libgcj) version 4.1.2 20080704 (Red Hat 4.1.2-18)


                                Applmgr -> $java -version

                                $Java -version
                                java version "1.6.0_21"
                                Java(TM) SE Runtime Environment(build 1.6.0_21-b06)

                                I think, both DB and Apps should have same java version, If yes , Please let me know steps/docs to follow for the same.


                                2 - Reg: Environment file - DEV_ts.env

                                I can see environment file in old home - /u01/..../DEV_ts.env. and we are loading that file in profile. But in my new home I can't see the same.

                                How I can make DEV_ts.env file for new 11g home. ?


                                Thanks,
                                • 13. Re: EBS Database upgrade to 11.2.0.3
                                  Hussein Sawwan-Oracle
                                  I think, both DB and Apps should have same java version, If yes , Please let me know steps/docs to follow for the same.
                                  Using Latest Java 6.0 Update With Oracle E-Business Suite Release 12 [ID 455492.1]
                                  2 - Reg: Environment file - DEV_ts.env

                                  I can see environment file in old home - /u01/..../DEV_ts.env. and we are loading that file in profile. But in my new home I can't see the same.

                                  How I can make DEV_ts.env file for new 11g home. ?
                                  There must be an env file under 11g ORACLE_HOME that was created by AutoConfig, and you need to source this one instead of the 10g one -- Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]

                                  Thanks,
                                  Hussein
                                  • 14. Re: EBS Database upgrade to 11.2.0.3
                                    953987
                                    Thanks a lot Hussein...


                                    I have done for Java and found the file *.env for profile.

                                    You guys helped me a lot on this.



                                    Thanks,