In Oracle : Upgrade the 9.2.0.6 database to 10.2.0.3 for APPs

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle Applications EBS

Oracle Apps 11i DBA (I) Oracle Apps 11i DBA (II) Oracle Apps 11i DBA (III) Oracle Apps 11i Sys Admin UPGRADE 11i Apps to R12 Oracle Apps R12 DBA

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle Applications (apps)  -> R12

-> Upgrade the 9.2.0.6 database to 10.2.0.3 for APPs

 

Upgrade the 9.2.0.6 database to 10.2.0.3 for APPs

 

 

Notes: 

  • For more details please check Metalink Note 362203.1: Oracle E-Business Suite Release 11i with Oracle Database 10g Release 2

  • The following tasks are specific to an Linux system

1. Install the Oracle 10.2.0.1 binaries 

Install the Oracle 10.2.0.1 binaries in the new ORACLE_HOME (In my case this is /APPS_DB_R12/db/tech_st/10.2.0 )

For more details click here: Install Oracle home binaries.

 

2. Install the Oracle 10.2.0.3 patchset 

Install the Oracle 10.2.0.3 patchset into the new ORACLE_HOME (In my case this is /APPS_DB_R12/db/tech_st/10.2.0 ).

For more details click here: Install Oracle patchset 10.2.0.3

 

 

3. Prepare the SO and the 9i database for the upgrade

For more details on the OS patching & configuration needed for 10g upgrade click here: Prepare Linux/ Unix for Oracle database installation.

For more details about the Pre-Check upgrade tasks click here:Oracle Database Pre-Check upgrade

 

 

4. Upgrade the database

 

- Create nls/data/9idata directory

export ORACLE_HOME=/APPS_DB_R12/db/tech_st/10.2.0

cd $ORACLE_HOME/nls/data/old

perl cr9idata.pl

 

- Apply an additional 10.2.0.3 RDBMS patche

Apply 5257698 - TOO MANY 'LX40030.NLB' FILES OPEN AFTER UPGRADE TO 10GR2

 

- Copy the following files from <9i ORACLE_HOME>/network/admin to <10g ORACLE_HOME>/network/admin

tnsnames.ora

sqlnet.ora

listener.ora

 

- Copy the following files from <9i ORACLE_HOME>/dbs to <10g ORACLE_HOME>/dbs

initVIS.ora

 

- Create the log directory & utl_file_dir (connected as oracle)

mkdir -p /APPS_DB_R12/admin/udump

mkdir -p /APPS_DB_R12/admin/bdump

mkdir -p /APPS_DB_R12/admin/cdump

mkdir  /APPS_DB_R12/utl_file_dir

 

- Modify the initVIS.ora

Modify in initVIS.ora the following parameter:

 

control_files

user_dump_files 

background_dump_dest

core_dump_dest

utl_file_dir

 

- Shut down Applications server processes and database listener

/APPS_DB/visdb/9.2.0/appsutil/scripts/VIS_db/addlnctl.sh stop VIS
/APPS_DB/visdb/9.2.0/appsutil/scripts/VIS_db/addbctl.sh stop immediate VIS

 

- Move the datafiles from 9i location to the new R12 location  

cp /APPS_DB/visdata/*.* /APPS_DB_R12/db/apps_st/data

 

- Update the environment variable like ORACLE_HOME & PATH

Modify /etc/oratab (or /var/opt/oracle/oratab on Solaris) to set the new Oracle_Home and disable the automatic startup

 

VIS:/APPS_DB_R12/db/tech_st/10.2.0:N

 

. oraenv

unset ORA_NLS33

export ORA_NLS10=$ORACLE_HOME/nls/data

export PATH=$ORACLE_HOME/bin:$PATH

 

- Change in control file the new location for the data files

 

sqlplus /nolog

conn / as sysdba

startup mount;

 

ALTER DATABASE RENAME FILE '/APPS_DB/visdata/archive1.dbf' TO '/APPS_DB_R12/db/apps_st/data/archive1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/archive2.dbf' TO '/APPS_DB_R12/db/apps_st/data/archive2.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/ctx1.dbf' TO '/APPS_DB_R12/db/apps_st/data/ctx1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/log1.dbf' TO '/APPS_DB_R12/db/apps_st/data/log1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/log2.dbf' TO '/APPS_DB_R12/db/apps_st/data/log2.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/log3.dbf' TO '/APPS_DB_R12/db/apps_st/data/log3.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/media1.dbf' TO '/APPS_DB_R12/db/apps_st/data/media1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/media2.dbf' TO '/APPS_DB_R12/db/apps_st/data/media2.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/media3.dbf' TO '/APPS_DB_R12/db/apps_st/data/media3.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/mobile01.dbf' TO '/APPS_DB_R12/db/apps_st/data/mobile01.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/nologging1.dbf' TO '/APPS_DB_R12/db/apps_st/data/nologging1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/odm.dbf' TO '/APPS_DB_R12/db/apps_st/data/odm.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/olap.dbf' TO '/APPS_DB_R12/db/apps_st/data/olap.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/owa1.dbf' TO '/APPS_DB_R12/db/apps_st/data/owa1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/portal.dbf' TO '/APPS_DB_R12/db/apps_st/data/portal.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/queues1.dbf' TO '/APPS_DB_R12/db/apps_st/data/queues1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/queues2.dbf' TO '/APPS_DB_R12/db/apps_st/data/queues2.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/reference1.dbf' TO '/APPS_DB_R12/db/apps_st/data/reference1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/reference2.dbf' TO '/APPS_DB_R12/db/apps_st/data/reference2.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/summary1.dbf' TO '/APPS_DB_R12/db/apps_st/data/summary1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/summary2.dbf' TO '/APPS_DB_R12/db/apps_st/data/summary2.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/summary3.dbf' TO '/APPS_DB_R12/db/apps_st/data/summary3.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/summary4.dbf' TO '/APPS_DB_R12/db/apps_st/data/summary4.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/summary5.dbf' TO '/APPS_DB_R12/db/apps_st/data/summary5.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/sys1.dbf' TO '/APPS_DB_R12/db/apps_st/data/sys1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/sys2.dbf' TO '/APPS_DB_R12/db/apps_st/data/sys2.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/sys3.dbf' TO '/APPS_DB_R12/db/apps_st/data/sys3.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/sys4.dbf' TO '/APPS_DB_R12/db/apps_st/data/sys4.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/sys5.dbf' TO '/APPS_DB_R12/db/apps_st/data/sys5.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/sys6.dbf' TO '/APPS_DB_R12/db/apps_st/data/sys6.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/sys7.dbf' TO '/APPS_DB_R12/db/apps_st/data/sys7.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tmp1.dbf' TO '/APPS_DB_R12/db/apps_st/data/tmp1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data10.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data10.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data11.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data11.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data1.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data2.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data2.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data3.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data3.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data4.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data4.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data5.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data5.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data6.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data6.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data7.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data7.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data8.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data8.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_data9.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_data9.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx10.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx10.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx11.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx11.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx1.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx1.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx2.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx2.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx3.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx3.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx4.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx4.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx5.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx5.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx6.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx6.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx7.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx7.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx8.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx8.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/tx_idx9.dbf' TO '/APPS_DB_R12/db/apps_st/data/tx_idx9.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/undo01.dbf' TO '/APPS_DB_R12/db/apps_st/data/undo01.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/undo02.dbf' TO '/APPS_DB_R12/db/apps_st/data/undo02.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/undo03.dbf' TO '/APPS_DB_R12/db/apps_st/data/undo03.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/undo04.dbf' TO '/APPS_DB_R12/db/apps_st/data/undo04.dbf';
ALTER DATABASE RENAME FILE '/APPS_DB/visdata/apps_ts_tx_interface.dbf' TO '/APPS_DB_R12/db/apps_st/data/apps_ts_tx_interface.dbf';


If you want to verify the new location you can run:

 

set pagesize 200
col name format a54
select name, status from v$datafile df;

shutdown immediate;

 

- Start the database upgrade

startup upgrade;

 

- Create the new SYSAUX tablespace

create tablespace SYSAUX datafile '/APPS_DB_R12/db/apps_st/data/sysaux1.dbf'

SIZE 500M reuse

extent management local

segment space management auto

online;

 

- Run the upgrade script

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

conn / as sysdba

 

spool /APPS_DB_R12/upgrade.log

set term off

@catupgrd.sql;

spool off

set term on

 

When we run the @utlu102s.sql TEXT we must have the following result:

 

Oracle Database 10.2 Upgrade Status Utility 05-06-2008 21:45:30  Component Status Version HH:MM:SS

Oracle Database Server                 VALID        10.2.0.3.0            00:41:32
JServer JAVA Virtual Machine      VALID         10.2.0.3.0            00:06:10
Oracle XDK                                  VALID        10.2.0.3.0             00:05:54
Oracle Database Java Packages     VALID         10.2.0.3.0            00:00:28
Oracle Text                                    VALID         10.2.0.3.0            00:01:33
Oracle XML Database                   VALID         10.2.0.3.0            00:01:40
Oracle Data Mining                        VALID         10.2.0.3.0            00:00:25
OLAP Analytic Workspace           VALID          10.2.0.3.0            00:00:15
OLAP Catalog                               VALID         10.2.0.3.0            00:01:13
Oracle OLAP API                         VALID          10.2.0.3.0           00:00:43
Oracle interMedia                          VALID          10.2.0.3.0            00:05:45
Spatial                                            VALID         10.2.0.3.0            00:03:29

To validate the Oracle components we can query the DBA_REGISTRY view as well. 

 

- Check for the invalid objects:

 

select count(*) from dba_objects where status <> 'VALID'; 

 

select owner,  object_type, count(*) from dba_objects where status <> 'VALID'

group by owner,  object_type; 

 

select owner,  object_type, object_name from dba_objects where status <> 'VALID'; 

 

- Recompile the invalid objects

 

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

conn / as sysdba

 

@utlrp.sql;

 

> Check the status of the objects again. !!!

 

You must have the same number of invalid objects as before the upgrade. If there are more errors, please check to see what is wrong and fix the errors. 

 

- Shutdown the database

shutdown immediate;

 

- Change the initialization parameters & Additional database configuration

For more information check the Metalink note 216205.1.

 

- Start the listener

Since AutoConfig has not yet been implemented, start the listener with the lsnrctl executable.

If the new Oracle_Home is not yet modified in the listener.ora, do it now.

 

export TNS_ADMIN=/APPS_DB_R12/db/tech_st/10.2.0/network/admin

lsnrctl start

 

- Run adgrants.sql

a) copy $APPL_TOP/admin/adgrants.sql from the administration server node to the database server node into the $ORACLE_HOME

 

b) on the database server:

    cd $ORACLE_HOME

    sqlplus "/ as sysdba" @adgrants.sql APPLSYS

 

  APPLSYS = is the Applications Object Library user.

 

- Grant create procedure privilege on CTXSYS

a) Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node into the $ORACLE_HOME

 

b) on the database server:

    cd $ORACLE_HOME

    sqlplus apps/<APPS password> @adctxprv.sql  <SYSTEM password> CTXSYS

 

    sqlplus apps/apps @adctxprv.sql  manager CTXSYS 

 

- 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.

 

For more details click here

 

- Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node.

 

0n the database node:

 

$ sqlplus "/ as sysdba"


SQL> shutdown normal;      (or immediate)
SQL> startup restrict;
SQL> @adstats.sql;
SQL> shutdown normal;      (or immediate)
SQL> startup; 
SQL> exit;

- Re-create grants and synonyms

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.

 

 

In-Oracle.com  -> Oracle Applications (apps)  -> R12

-> Upgrade the 9.2.0.6 database to 10.2.0.3 for APPs

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.