Apply 10g Release 2 interoperability patch for 11.5.10 (4653225)
Update application tier context file with new database listener port number (conditional)
The new 10.2.0 Oracle home uses its own database listener for the database instance, replacing the current database listener
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
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.
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.
Perform 10.2.0.2 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.2 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).
Perform 10.2.0.2 patch set (patch# 4547817 ) 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.2 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.
Perform 10.2.0.2 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:
o The ORACLE_HOME environment variable points to the new 10.2.0 Oracle home.
o The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
o The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
o The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/
o Cd /admin/orapatch/oracle/10gDB_R2/DB_R2Patch/4547817/Disk1
o ./runInstaller
o On the Welcome screen, click Next.
o On the Specify File Locations screen, click Browse next to the Path field in the Source section.
o Select the products.xml file from the stage directory where you unpacked the patch set files, then click Next. For example:
o directory_path/stage/products.xml
o In the Name field in the Destination section, select the name of the Oracle home that you want to update from the drop down list, then click Next.
o If you are installing the patch set on a RAC cluster, click Next when the Selected Nodes screen appears.
o On the Summary screen, click Install.
o This screen lists all of the patches available for installation.
o When prompted, run the $ORACLE_HOME/root.sh script as the root user
o
o You use the runInstaller (UNIX/Linux) or the setup.exe executable (Windows) provided in the patch set to run OUI.
o --------------------------
Apply patch# 5117016 (Make sure no other patch is installed on top of 10.2.0.2 before this)
cd 5117016
opatch apply (For RAC opatch apply –local on each node)
Upgrading a Release 9.2 Database Not Using OLS
If you want to upgrade an Oracle9i release 9.2 preconfigured database, and you are not using Oracle Label Security (OLS), complete the following steps to avoid errors during the upgrade:
1. Use Oracle Universal Installer release 9.2 to install OLS using the Custom installation type.
2. Run the $ORACLE_HOME/rdbms/admin/catnools.sql script with the SYSDBA privilege to remove OLS components from the database.
Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters to at least 150MB
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.
Create nls/data/9idata directory
On the database server node, as the owner of the Oracle RDBMS file system and database instance, 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/nls/data/9idata
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"
Shut down Applications server processes and database listener
Prepare to upgrade
Run the Pre-Upgrade Information Tool
Copy the following file from the ORACLE_HOME/rdbms/admin directory of the
new Oracle Database 10g release to a directory outside of the Oracle home, such as
the temporary directory on your system:
■ utlu102i.sql
Make a note of the new location of this file.
3. Log in to the system as the owner of the Oracle home directory of the database to
be upgraded.
4. Change to the directory outside of the Oracle home directory that you copied files
to in Step 2.
5. Start SQL*Plus.
6. Connect to the database instance as a user with SYSDBA privileges.
7. Set the system to spool results to a log file for later analysis:
SQL> SPOOL info.log
8. Run utlu102i.sql:
SQL> @utlu102i.sql
9. Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
Then, check the spool file and
SYSAUX Tablespace
Make it 2 GB
/gaperp/u015/oradata/gaperp/sysaux_01.dbf'
Issues Requiring Further Analysis Prior to Upgrading
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
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 a
requirement 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
TIMESTAMP WITH TIMEZONE Datatype
The time zone files that are supplied with Oracle Database 10g have been updated
from version 1 to version 2 to reflect changes in transition rules for some time zone
regions. The changes may affect existing data of TIMESTAMP WITH TIME ZONE
datatype. To preserve this TIMESTAMP data for updating according to the new time
zone transition rules, you must run the utltzuv2.sql script on the database before
upgrading. This script is located in the new 10.2 ORACLE_HOME/rdbms/admin
directory. This script analyzes your database for TIMESTAMP WITH TIME ZONE
columns that are affected by the updated time zone transition rules.
If the utltzuv2.sql script identifies columns with time zone data affected by a
database upgrade, then back up the data in character format before you upgrade the
database. After the upgrade, you must update the tables to ensure that the data is
stored based on the new rules. If you export the tables before upgrading and import
them after the upgrade, the conversion will happen automatically during the import.
Alternatively, create tables with the time zone information in character format (for
example, TO_CHAR(column, 'YYYY-MM-DD HH24.MI.SS
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
Run the Oracle Net Configuration Assistant
If you are upgrading from Oracle9i and a listener was not configured in the Oracle9i
repository, run Oracle Net Configuration Assistant to configure the listening protocol
address and service information for the new Oracle Database 10g database, including a
listener.ora file. A version 10 listener is required for an Oracle Database 10g
database. Previous versions of the listener are not supported for use with an Oracle
Database 10g database. However, it is le to use a version 10 listener with
previous versions of the Oracle database.
If event="38004 trace name context forever, level 1" is defined in either the init
We recommend that you use 500 MB as the SYSAUX tablespace size. Set autoextend on for the SYSAUX tablespace.
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
Create listener for 10g
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.
Fix Korean lexers
Apply patch 5005469, use SQL*Plus to connect to the database as SYSDBA, and run drkorean.sql using the following command:
cd 5005469
opatch apply
$ sqlplus "/ as sysdba" @$ORACLE_HOME/ctx/sample/script/drkorean.sql
Start the new database listener (conditional)
If the Oracle Net listener for the database instance in the new Oracle home has not been started, you must start it now. Since AutoConfig has not yet been implemented, start the listener with the lsnrctl executable
Run DBUA to do the upgrade (Screen shots available in 10g Screenshots doc)
Run adgrants.sql (conditional)
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/gaperp_web/admin/adgrants.sql
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/
Implement and run AutoConfig
Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener
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> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;
Make sure that you have at least 1.5 GB of free default temporary tablespace.
Re-create custom database links (conditional)
If the Oracle Net listener in the 10.2.0 Oracle home is defined differently than the one used by the old Oracle home, you must re-create any custom self-referential database links that exist in the Applications database instance. 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
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=
(PORT=
where
ORA_NLS10
(Note that the ORA_NLS10 environment variable replaces the ORA_NLS33
environment variable, so you may need to unset ORA_NLS33 and set ORA_
NLS10.)
Re-create grants and synonyms
Oracle Database 10g Release 2 (10.2) contains new functionality for grants and synonyms compared to previous database releases. As a result, you must re-create the grants and synonyms in the APPS schema. On the administration server node, as the owner of the Applications file system, run AD Administration and select the "Recreate grants and synonyms for APPS schema" task from the Maintain Applications Database Objects menu.
If MRC is implemented in your Applications system, then run the "Maintain Multiple Reporting Currencies schema" task to re-create MRC triggers in the Apps schema.
No comments:
Post a Comment