11.5.10_10g_upgrade

1. Apply 10g Release 2 interoperability patch for 11.5.10 (4653225) 3
2. Prepare to create the 10.2.0 Oracle home 3
3. Install the Release 10.2 Oracle Software 3
4. Install Oracle Database 10g Products from the 10g Companion CD 3
5. Perform 10.2.0.2 patch set pre-installation tasks 3
6. Perform 10.2.0.2 patch set (patch# 4547817 ) pre-installation tasks 4
7. Perform 10.2.0.2 patch set installation tasks 4
8. Apply patch# 5117016 (Make sure no other patch is installed on top of 10.2.0.2 before this) Error! Bookmark not defined.
9. Running changePerm.sh script on an Oracle database server home 4
10. Create nls/data/9idata directory 6
11. Remove the following events 6
12. Deprecated CONNECT Role 6
13. Database Links With Passwords 7
14. Optimizer Statistics 7
15. Modify initialization parameters (216205.1) 8
16. Create listener for 10g 8
17. Natively compile PL/SQL code (optional) 8
18. Change DBUA parameters for the rollback segments 9
19. Run DBUA to do the upgrade (Screen shots available in 10g Screenshots doc) 9
20. Take care of the listener now. 9
21. Use new init.ora 10
22. Run adgrants.sql 10
23. Grant create procedure privilege on CTXSYS 10
24. Fix Korean lexers 11
25. Gather statistics for SYS schema 11
26. Re-create custom database links 11
27. Re-create grants and synonyms 12



1. Apply 10g Release 2 interoperability patch for 11.5.10 (4653225)

2. Prepare to create the 10.2.0 Oracle home

unset TNS_ADMIN
export DISPLAY=ushcfpq4j61l-1.clients.am.health.ge.com:0.0
cd /admin/orapatch/oracle/10gDB_R2/DB


3. Install the Release 10.2 Oracle Software
it is recommended that you run the Pre-Upgrade Information
Tool before you upgrade using DBUA, so that you can preview the types of items
the DBUA will be checking.
You can then run the DBUA independently after the installation is
complete.
After the installation of Oracle Database is complete, install the companion Oracle
Database 10g Products if you have JServer, interMedia, Spatial, or Ultra Search in
your existing databases.

4. Install Oracle Database 10g Products from the 10g Companion CD
On the database server node, as the owner of the Oracle RDBMS file system and database instance, perform the tasks in section 3.5, "Installing Oracle Database 10g Products" in the Oracle Database Companion CD Installation Guide for your platform.
In the Installation Types window, use the Product Languages button to select any languages other than American English that are used by your Applications database instance.

5. Perform 10.2.0.3 patch set pre-installation tasks

On the database server node, as the owner of the Oracle 10g file system and database instance, unzip and extract the 10.2.0.3 patch set file for your platform. Read the patch set notes (usually README.html). Make sure you thoroughly understand the upgrade and patch set installation process before you begin. Check OracleMetaLink or contact Oracle Support Services to determine any known issues with the patch set and its interoperability with Oracle Applications.
Perform the tasks in the "Preinstallation Tasks" section of the patch set notes (if they apply to your system).


6. Perform 10.2.0.3 patch set (patch# 5337014 ) pre-installation tasks

If any of the databases use Java Virtual Machine (Java VM) or Oracle interMedia, install the Oracle Database 10g Products installation type from the Oracle Database 10g Companion CD before installing the 10.2.0.3 patch set. This installation type includes the Natively Compiled Java Libraries (NCOMP) files to improve Java performance. If you do not install the NCOMP files, the ORA-29558:JAccelerator (NCOMP) not installed error occurs when a database that uses Java VM is upgraded to the patch release.

7. Perform 10.2.0.3 patch set installation tasks

On the database server node, as the owner of the Oracle RDBMS file system and database instance, perform the tasks in the "Installing the Oracle Database 10g Patch Set Interactively" section of the patch set notes. Make sure that:
The ORACLE_HOME environment variable points to the new 10.2.0 Oracle home.
The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/ and $ORACLE_HOME/perl/lib/site_perl/)

/admin/orapatch/oracle/10.2.0.3/R3_Patch/Disk1
./runInstaller

This screen lists all of the patches available for installation.
When prompted, run the $ORACLE_HOME/root.sh script as the root user


8. ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases To 10.2.0.3

export OPATCH_SKIP_VERIFY=TRUE
cd 5871314
opatch apply -local

cd 5892355
opatch apply -local


9. Mandatory Patch 5752399 for 10.2.0.3 on Solaris 64-bit and Filesystems Managed By Veritas

cd 5752399
opatch apply -local

10. Bug 5916387 - Fix for CRS hang (Only for RAC instance)

cd /admin/orapatch/oracle/10.2.0.3/Patches/5916387
% custom/server/5916387/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/10.2.0.3/DBEE_1
Apply patch
% opatch apply custom/server/5916387 -local -oh /u01/app/oracle/product/10.2.0.3/DBEE_1
Respond y to question.
% custom/server/5916387/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/10.2.0.3/DBEE_1


11. PROBLEMS IN MEDIAN COMPUTATION (MDRCR.C)

cd 5606847
opatch apply -local


12. Bug 5907779 - Self deadlock hang on "cursor: pin S wait on X" (typically from DBMS_STATS)

cd 5907779
opatch apply –local

13. 5605370 – ERPDEV1 crashes
cd 5605370
opatch apply –local
14. Running changePerm.sh script on an Oracle database server home
$ apply patch 5087548
cd 5087548
opatch apply

cd $ORACLE_HOME/install
sh changePerm.sh

and specify the patched server Oracle home location, before accessing client-side utilities or libraries in the database home.

15. Create nls/data/9idata directory
On the database server node, as oracle, run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.
After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 10g Oracle home.

[bioerpora1d::oracle]> perl cr9idata.pl
Creating directory /u01/app/oracle/product/10.2.0.2/nls/data/9idata ...
Copying files to /u01/app/oracle/product/10.2.0.2/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /u01/app/oracle/product/10.2.0.2/DBEE_1/nls/data/9idata

16. Remove the following events

only if you are using Oracle Applications release 11.5.8 or later.
event="10932 trace name context level 32768"
event="10933 trace name context level 512"
event="10943 trace name context level 16384"


17. Deprecated CONNECT Role

After upgrading to Oracle Database 10g, the CONNECT role will only have the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases will be revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:

SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

If users or roles require privileges other than CREATE SESSION, then grant the specific required privileges prior to upgrading. The upgrade scripts adjust the privileges for the Oracle-supplied users.

@/u92/applmgr/anindya/proderp_10g/connect_grants.sql

18. Database Links With Passwords

During the upgrade to Oracle Database 10g, any passwords in database links will be encrypted. To downgrade back to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links will not exist in the downgraded database. If you anticipate arequirement to be able to downgrade back to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can recreate the database links after the downgrade.

/u92/applmgr/anindya/proderp_10g/db_links_in_gaperp.lst


19. Optimizer Statistics

When upgrading to Oracle Database 10g, optimizer statistics are collected for
dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.
To decrease the amount of downtime incurred when collecting statistics, you can
collect statistics prior to performing the actual database upgrade. As of Oracle
Database 10g Release 10.1, Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For
example, you can enter the following:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS:
If you are using Release 9.0.1 or 9.2.0, then you should use the DBMS_STATS.GATHER_
SCHEMA_STATS procedure to gather statistics

@/u92/applmgr/anindya/proderp_10g/backup_dict_stats.sql

@/u92/applmgr/anindya/proderp_10g/gdict.sql


20. Modify initialization parameters (216205.1)

#########
#
# PL/SQL Parameters
#
# The following parameters are used to enable the PL/SQL
# global optimizer as well as native compilation.
#
# Oracle Applications recommends the use of PL/SQL native
# compilation for 10g based Apps environments. The
# parameter (plsql_native_library_dir) should be set
# to the directory path which will be used to store
# the shared libraries generated as part of native
# compilation. Interpreted mode is supported and
# can be used with Oracle Applications, however, native
# compilation is recommended in order to maximize runtime
# performance and scalability.

# Compiling PL/SQL units with native compilation does
# take longer than interpreted mode due to the generation
# and compilation of the native shared libraries.
#
#
#########
plsql_optimize_level = 2 #MP
plsql_code_type = native #MP
plsql_native_library_dir = ?/prod11i/plsql_nativelib
plsql_native_library_subdir_count = 149



21. Create listener for 10g

22. Natively compile PL/SQL code (optional)

If you are on a UNIX/Linux platform, you can choose to run Oracle Applications 11i PL/SQL database objects in natively compiled mode with Oracle Database 10g. See the "Compiling PL/SQL Code for Native Execution" section of Chapter 11 of Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (10.2).
When modifying the initialization parameters pertaining to the PL/SQL native compilation, use document 216205.1, on OracleMetaLink as a guideline. You can set the plsql_native_library_subdir_count parameter as described in the PL/SQL user's guide. Create the associated subdirectories for the given plsql_native_library_dir and plsql_native_library_subdir_count parameters.


23. Change DBUA parameters for the rollback segments

cd $ORACLE_HOME/ass*/dbua
change below highlighted values in mep.cfg

















24. Run DBUA to do the upgrade (Screen shots available in 10g Screenshots doc From section “Start Upgrade Assistant – Migrating 9i to 10g”)

25. Take care of the listener now.
The database should run-off a 10g listener only. Other 9i databases can run-off the 10g listener too.

26. Use new init.ora
DBUA creates spfile by default.

Create pfile from spfile
Keep this pfile as back-up and then use your own pfile for 10g DB

Shutdown database
Remove spfile.ora from $ORACLE_HOME/dbs
Create soft link initerpprd.ora pointing to /u01/app/oracle/admin/erpprd/pfile/ initerpprd.ora_10g_0308

Check in the init.ora for below parameters. Add if not available.

*.plsql_code_type=NATIVE
*.plsql_native_library_dir= /u02/app/oracle/erpprd/plsql_files
*.plsql_native_library_subdir_count=500


sqlplus "sys as sysdba"
startup upgrade
@$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql -- Default parameter passed is TRUE
shutdown immediate
Startup
27. Compile invalids using utlrp.sql_parallel
cd $ORACLE_HOME/rdbms/admin
sqlplus “sys as sysdba”
@utlrp.sql_parallel
28. Run adgrants.sql
If you have at least AD.I or Applications release 11.5.10 installed on your system, copy $APPL_TOP/admin/adgrants.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:

$ sqlplus "/ as sysdba" @/u02/app/applmgr/115/erpprd_web/admin/adgrants.sql

29. Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:

$ sqlplus apps/ @/u02/app/applmgr/115/erpprd_web/ad/11.5.0/patch/115/sql/adctxprv.sql CTXSYS

30. Fix Korean lexers

Apply patch 5005469, use SQL*Plus to connect to the database as SYSDBA, and run drkorean.sql using the following command:

This is to be done once for each home.

cd 5005469
opatch apply

This needs to be run for all databases:
$ sqlplus "/ as sysdba" @$ORACLE_HOME/ctx/sample/script/drkorean.sql



31. Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. Note that adstats.sql has to be run in restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:

$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @/u02/app/applmgr/115/erpdev1c/admin/adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;

Make sure that you have at least 1.5 GB of free default temporary tablespace.

32. Re-create custom database links

To check for the existence of database links, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following query:
$ sqlplus apps/
SQL> select db_link from dba_db_links;
The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig in the previous step.
If you have custom self-referential database links in the database instance, use the following commands to drop and re-create them:
$ sqlplus apps/
drop database link ;
SQL> create database link connect to
identified by using
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)
(PORT=))(CONNECT_DATA=(SID=)))';
where , , , , , and reflect the new Oracle Net listener for the database instance

Use /u92/applmgr/anindya/proderp_10g/db_links_in_.txt to recreate the links


33. Re-create grants and synonyms

You must re-create the grants and synonyms in the APPS schema. On the administration server node, as applmgr, run ADADMIN and select the "Recreate grants and synonyms for APPS schema" task from the Maintain Applications Database Objects menu.

34. Unlock stats
During 10g upgarde, statistics on QUEUE tables will be locked and we need to unlockk them after upgrade.

Metalink Note:375351.1

The below SQL will return the tables

select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;

Use below procedure to unlcok the statics for table

exec dbms_stats.unlock_table_stats('table_owner','table_name');

35. Apply receivables patch 5753621 (Ebiz suite). –Ignore it
This patch is part of second set of merged patch (proderpitest) and so it is already applied.