Monday, May 24, 2010

OracleApps11i: Database Upgrade from 9i to 10gR2

- Database
- Database Companion CD
- Database patch set# 4547817
- Korean Lexer Patch 5005469
- TXK AutoConfig Template Rollup Patch M (4709948) or Higher
- 11.5.10 INTEROP PATCH FOR 10GR2 (4653225) ---> This patch should be applied to
an environment before upgrading to 10gR2.

1. Create 10.2.0 OracleHome directory

[oracle@myserver 10gR2]$ cd $ORACLE_HOME/..
[oracle@myserver proddb]$ mkdir 10.2.0

2. Install 10.2.0 software to ORACLE_HOME 10.2.0

[oracle@myserver proddb]$ echo $DISPLAY $ORACLE_SID $ORACLE_HOME PROD /oracle/proddb/9.2.0
[oracle@myserver proddb]$ export ORACLE_HOME=/oracle/proddb/10.2.0
[oracle@myserver proddb]$ cd /oracle/softwares/10gR2/database/
[oracle@myserver database]$ ./runInstaller

3. Install 10.2.0 Companion CD Products
Oracle® Database Companion CD Installation Guide
10g Release 2 (10.2) for Linux x86
Part Number B15664-02
Section 3.5 - Installing Oracle Database 10g Products

[oracle@myserver 10.2.0]$ cd /oracle/softwares/10gR2/companion
[oracle@myserver companion]$ ./runInstaller

4. If this is the first installation of Oracle products on this system, then Oracle Universal Installer displays the Specify Inventory Directory and Credentials screen. Specify the following information, and then click Next:
* Enter the full path of the inventory directory:
Verify that the path is similar to the following, where oracle_base is the value you specified for the ORACLE_BASE environment variable: oracle_base/oraInventory
* Specify operating system group name.

5. Apply patch set (Note 316900.1 ---> Note 368732.1)

[oracle@myserver proddb]$ export ORACLE_HOME=/oracle/proddb/10.2.0
[oracle@myserver Disk1]$ echo $DISPLAY $ORACLE_HOME /oracle/proddb/10.2.0
[oracle@myserver Disk1]$ pwd
[oracle@myserver Disk1]$ ./runInstaller

6. NLS Settings: Create 10g $ORACLE_HOME/nls/data/9idata directory.

[oracle@myserver 10.2.0]$ cd /oracle/proddb/10.2.0/nls/data/old/
[oracle@myserver old]$ ls -l
-rw-r----- 1 oracle dba 10849 Jun 9 2004

[oracle@myserver old]$ perl
Creating directory //oracle/proddb/10.2.0/nls/data/9idata ...
Copying files to //oracle/proddb/10.2.0/nls/data/9idata...
glob failed (child exited with status 127) at line 148.
glob failed (child exited with status 127) at line 162.
Copy finished.
Please reset environment variable ORA_NLS10 to //oracle/proddb/10.2.0/nls/data/9idata!
[oracle@myserver old]$ export ORA_NLS10=/oracle/proddb/10.2.0/nls/data/9idata

***** Soln: Manually copy .nlb(s) *****
[oracle@myserver 9idata]$ cp /oracle/proddb/10.2.0/nls/data/old/*.nlb $ORACLE_HOME/nls/data/9idata

Downtime Starts (20hrs.)
7. Shutdown middle-tier services and db listener. (5min)

[oracle@myserver ~]$ . /oracle/prodappl/APPSORA.env
[oracle@myserver ~]$ cd $COMMON_TOP/admin/scripts/PROD_aoltest8
[oracle@myserver PROD_aoltest8]$ sh apps/apps
[oracle@myserver PROD_aoltest8]$ . /oracle/proddb/9.2.0/PROD_aoltest8.env

[oracle@myserver PROD_aoltest8]$ cd $ORACLE_HOME/appsutil/scripts/PROD_aoltest8
[oracle@myserver PROD_aoltest8]$ sh stop PROD

8. Unset TNS_ADMIN and If event="38004 trace name context forever, level 1" is defined in either the init.ora initialization parameter file or the spfile.ora server parameter file, remove it. (5min)

[oracle@myserver 9idata]$ unset TNS_ADMIN

9. Copy 10g ORACLE_HOME/rdbms/admin/utlu102i.sql to any dir outside ORACLE_HOME (5min)
[oracle@myserver 9idata]$ cd $ORACLE_HOME/rdbms/admin
[oracle@myserver admin]$ cp utlu102i.sql /oracle/tempdir

10. Log in to the system as the owner of the Oracle home directory of the database to be upgraded. Run utlu102i.sql (Oracle Database 10.2 Upgrade Information Utility) as SYSDBA (15min)
SQL> spool 10gUpgFrm9i.lst

SQL> @utlu102i.sql

Review the spool file and modify changes (15min)-

10.1. "initPROD.ora"

#Parameters added for 10g upgrade

11. Upgrade the database -

11.1. Upgrade using Database Upgrade Assistant (dbua)
Call dbua from 10g ORACLE_HOME

[oracle@myserver proddata]$ cd $ORACLE_HOME/bin
[oracle@myserver proddata]$ nohup ./dbua &
nohup ./dbua -dbName PROD -oracleHome /oracle/proddb/9.2.0 -sourceDBVersion &

11.2.Manual Upgrade <#><#><#> RELIABLE <#><#><#>
11.2.1. Create SYSAUX tablespace and run 10g ORACLE_HOME/rdbms/admin/catupgrd.sql (90min for PROD)
[oracle@myserver admin]$ export PATH=/oracle/proddb/10.2.0/bin:$PATH

*** [oracle@myserver bin]$ sqlplus
*** Error 5 initializing SQL*Plus
*** NLS initialization error
*** Soln. unset ORA_NLS10

[oracle@myserver mesg]$ cd $ORACLE_HOME/rdbms/admin
[oracle@myserver admin]$ sqlplus /nolog
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 566231040 bytes
Fixed Size 1261984 bytes
Variable Size 385879648 bytes
Database Buffers 167772160 bytes
Redo Buffers 11317248 bytes

Database mounted.

Database opened.

SQL> CREATE TABLESPACE sysaux DATAFILE '/oracle/proddata/sysaux01.dbf'

########### Start Upgrade ###########

SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off

11.2.2. Check Upgrade Status.(5min)

SQL> @utlu102s.sql
Oracle Database 10.2 Upgrade Status Utility 04-09-2007 12:10:35
Component Status Version HH:MM:SS
Oracle Database Server VALID 00:36:07
JServer JAVA Virtual Machine VALID 00:05:16
Oracle XDK VALID 00:03:37
Oracle Database Java Packages VALID 00:00:19
Oracle Text VALID 00:01:02
Oracle XML Database VALID 00:01:01
Oracle Real Application Clusters INVALID 00:00:01
Oracle Data Mining VALID 00:00:17
OLAP Analytic Workspace VALID 00:00:16
OLAP Catalog VALID 00:01:00
Oracle OLAP API VALID 00:00:29
Oracle interMedia INVALID 00:03:48
Spatial VALID 00:03:42

Total Upgrade Time: 01:04:43

PL/SQL procedure successfully completed.

In case any installed component comes as INVALID, run $ORACLE_HOME/rdbms/admin/utlrp.sql to validate.

11.2.3. Shut down and restart the instance to reinitialize the system parameters for normal operation.(10min)


11.2.4. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.(from 2007-04-09 12:30:42 to 2007-04-09 21:22:10)

SQL> @utlrp.sql
# Check number of invalid objects remaining
SELECT count(*) FROM dba_objects WHERE status='INVALID';

12. Change init.ora as per Note 216205.1 (20min)
Refer Appendix A for altered/new/removed parameters.

13. Enabling System Managed Undo (Note 216205.1) *** Skipped, as 9iR2 is already on SMU ***

14. Verify that Oracle Data Mining and OLAP are installed in your database.
select comp_id
from dba_registry
where comp_id='ODM' or comp_id='AMD';

If ODM is not returned, install SQL> @$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP
If AMD is not returned, install SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP

########### Post Upgrade ###########

15. Fix Korean lexers (patch 5005469)

SQL> @$ORACLE_HOME/ctx/sample/script/drkorean.sql

16. Setup and start listener.

[oracle@myserver admin]$ cd /oracle/proddb/9.2.0/network/admin/
[oracle@myserver admin]$ cp -r PROD_aoltest8 $ORACLE_HOME/network/admin/
[oracle@myserver admin]$ cd $ORACLE_HOME/network/admin/PROD_aoltest8

Modify the listener.ora and sqlnet.ora files.

[oracle@myserver PROD_aoltest8]$ export TNS_ADMIN=/oracle/proddb/10.2.0/network/admin/PROD_aoltest8

Start Listener and create soft link 9.2.0 for 10.2.0 ORACLE_HOME -
[oracle@myserver PROD_aoltest8]$ lsnrctl start PROD

17. Copy $APPL_TOP/admin/adgrants.sql from admin node to db server node and run.

[oracle@myserver PROD_aoltest8]$ cd /oracle/prodappl/admin/

[oracle@myserver admin]$ sqlplus "/ as sysdba" @adgrants.sql APPLSYS

18. Copy $AD_TOP/patch/115/sql/adctxprv.sql from admin node to db server node and run: Grant "create procedure" privilege to CTXSYS.
[oracle@myserver admin]$ cd /oracle/prodappl/ad/11.5.0/patch/115/sql/
[oracle@myserver sql]$ sqlplus apps/ @adctxprv.sql CTXSYS

19. Implement AutoConfig on dbTier

[oracle@myserver ~]$ . /oracle/prodappl/APPSORA.env
[oracle@myserver admin]$ cd $AD_TOP/bin
[oracle@myserver bin]$ perl
[oracle@myserver bin]$ cp /oracle/prodappl/admin/out/ /oracle/proddb/10.2.0/

On dbTier (10.2.0) -
[oracle@myserver sql]$ cd $ORACLE_HOME
[oracle@myserver 10.2.0]$ ls -l
-rw-r--r-- 1 oracle dba 2777725 Apr 10 10:19
[oracle@myserver 10.2.0]$ unzip -o
[oracle@myserver appsutil]$ cd appsutil/bin/
[oracle@myserver bin]$ perl tier=db appsuser=APPS appspasswd=APPS

Error# 1:
JRE_TOP not found at its desired location /oracle/proddb/10.2.0/jre/1.1.8
Restart with value for jtop as an necessary arguement
Fix# 1:
[oracle@myserver bin]$ perl tier=db \
appsuser=APPS appspasswd=APPS jtop=/oracle/proddb/10.2.0/jre/1.4.2

Error# 2:
3 >= 3
java.lang.ArrayIndexOutOfBoundsException: 3 >= 3
at java.util.Vector.setElementAt(Unknown Source)
Fix# 2:
Apply TXK RUP M (4709948) or higher. Regenerate and retest.

Run AutoConfig -

[oracle@myserver bin]$ sh $ORACLE_HOME/appsutil/bin/ contextfile=/oracle/proddb/10.2.0/appsutil/PROD_aoltest8.xml appspass=apps

Error# 3:
Error 5 initializing SQL*Plus
NLS initialization error
... ... ...
Fix# 3:
Note 362203.1 - Step# 11: run the $ORACLE_HOME/nls/data/old/
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.

20. Copy $APPL_TOP/admin/adstats.sql from admin node to db server node and run in RESTRICTed mode.

SQL> conn / as sysdba
SQL> shutdown immediate
SQL> startup restrict
SQL> @/oracle/prodappl/admin/adstats.sql
SQL> shutdown normal
SQL> startup

21. Run "Recreate Grants and Synonyms" from adadmin.

22. Run AutoConfig on appsTier (optional) and start services.

Downtime Ends

Appendix A. New/Altered/Removed Parameters for 10g upgrade
compatible=10.2.0 #10g Upgrade
#db_cache_size=167772160 #10g Upgrade
#enqueue_resources=32000 #10g Upgrade
#java_pool_size=67108864 #10g Upgrade
#large_pool_size=16777216 #10g Upgrade
#max_enabled_roles=100 #10g Upgrade
nls_length_semantics=BYTE #10g Upgrade
olap_page_pool_size=4194304 #10g Upgrade
open_cursors=600 #10g Upgrade
#optimizer_features_enable=9.2.0 #10g Upgrade
#optimizer_max_permutations=2000 #10g Upgrade
pga_aggregate_target=1073741824 #10g Upgrade (1GB)
#query_rewrite_enabled=true #10g Upgrade
#row_locking=always #10g Upgrade
session_cached_cursors=500 #10g Upgrade
shared_pool_reserved_size=41943040 #10g Upgrade (40MB)
shared_pool_size=419430400 #10g Upgrade (400MB)
#sql_trace=FALSE #10g Upgrade (deprecated parameter)
undo_management=AUTO #10g Upgrade
#undo_retention=1800 #10g Upgrade
#undo_suppress_errors=FALSE #10g Upgrade
undo_tablespace=APPS_UNDOTS1 #10g Upgrade
workarea_size_policy=AUTO #10g Upgrade
##### New Parameters #####
sga_target=1073741824 #10g Upgrade (1GB)
_kks_use_mutex_pin=TRUE #10g Upgrade (Enables use of more efficient mutex mechanism for implementing library cache pins
plsql_optimize_level=2 #10g Upgrade
plsql_code_type=native #10g Upgrade
plsql_native_library_dir=/oracle/proddb/10.2.0/plsql/plsql_nativelib #10g Upgrade
plsql_native_library_subdir_count=149 #10g Upgrade
_b_tree_bitmap_plans=FALSE #10g Upgrade (CBO)
optimizer_secure_view_merging=FALSE #10g Upgrade (CBO)

No comments:

Post a Comment