This discussion is archived
14 Replies Latest reply: Dec 3, 2012 9:43 PM by 953987 RSS

EBS Database upgrade to 11.2.0.3

953987 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points