1) Upgrade 9i DB to 10g DB on AIX

A) INSTALL 10G (10.2.0.1) BINARIES ON AIX

we need to first install 10.2.0.1 base release  and then alter on upgrade it to 10.2.0.5

Connect to VNC viewer and set the display accordingly

cd to Disk1 and run “runIntsaller” and follow on screen commands

Select Enterprise and click “Next”

Provide Oracle_Home name and Location and click “Next”

This screen will verify RPMs and Kernel setting .If any issue gets reported correct it.

Select NO and press NEXT

Click Install Software only

Provide Oracle Home owner

This is install summary screen click Next

Install will porgress

run “root.sh” by root user

INSTALLATION WILL COMPLETE AFTER IT

At this Stage Oracle Version is 10.2.0.1 ,the next step is to run 10.2.0.5 patch on this oracle Home

B) UPGRADE 10G  TO (10.2.0.5) VERSION ON AIX

Using the VNC viewer cd to 10.2.0.5_SW directory

And run “runInstaller”,it’s a GUI tool following screen will guide you through the binary upgrade process

Click NEXT

In this screen the 10.2.0.1 Home installed in Previous step should appear in the list .Select the same and click next

press NEXT

This will do another set of OS tests 

10.2.0.5 Patch in process

10.2.0.5 Binary Installed

C) Pre Upgrade tasks In 11i instance

1) Disable Audit Trail

Part A :

1. System Administrator responsibility

2. Navigate to Security > Audit Trail > Groups.

3. F11, Enter the group name in the “Audit Group” field listed below, ctrl+F11

4. Set the Group State field to ‘Disable – Purge Table’ 

5. Save

Part B :

Run the ‘AuditTrail Update Tables’ concurrent program

If the above program fails, go with process documented in 393582.1

2) Apply Patches on 11i

Below patches were applied. Review and apply  :

1. 7429271 (11i.AD.I.6 (ADI.7)) (15mb) 

It must be present in your system in order to upgrade to Release 12.1.1.See About Oracle Applications DBA Minipack 11i.AD.I.6 (Doc ID:

233044.1).

2. 3036401 (Mini-Pack 11i.HZ.L) (29mb)

3. 3178291 (HZ CONSOLIDATED ROLLUP PATCH - V3 ON TOP OF HZ.K (11.03)) (48 mb)

4. 3438354 (Patch 11i.ATG_PF.H) (358mb)

5. 5989593 (REMOVE "REVOKE ALL FROM PUBLIC" FROM ADGRANTS.SQL) (5.1mb)

6. 4206794 (Patch 11i.FRM.H) (5.4mb)

7. 5903765 (11i.ATG_PF.H.delta.6: 11i.ATG_PF.H.RUP6) (331mb)

8. 7705743 (TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12) (39.5kb)

Generate TUMS report using

$AD_TOP/patch/115/sql/adtums.sql

Login as apps

and run

@adtums.sql /usr/tmp

This directory(/usr/tmp) must be listed in the UTL_FILE_DIR parameter of the init.ora file before TUMS can write the report and must have the

appropriate WRITE permissions

The TUMS report lists tasks that you can omit from the upgrade because they do not apply to your system (for example, a task required for a product that you do not use or

for applying a patch that you have previously applied). TUMS is delivered in a patch, which supplies the scripts you need to examine your system and create the report. We

strongly recommend you create and review the TUMS report before you begin the upgrade.

3) Convert To OATM 

Apply patch 7197489 (AD Preparation Scripts for R12)

a) Add Datafile to SYSTEM TS (as Required SYSTEM tablespace size is 9216 MB) if datafile size is less than required.

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='SYSTEM';

SUM(BYTES)/1024/1024/1024

-------------------------

               8.63001251

alter tablespace SYSTEM add datafile '/u01/oradata/data02/system12.dbf' size 1024 M;

SQL>  select sum(bytes)/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='SYSTEM';

SUM(BYTES)/1024/1024/1024

-------------------------

               9.63001251

b) 

Run sqlplus <APPS username>/<APPS password> @adgncons.sql apps  provided by the patch

The tablespace model for this release (OATM) is based on database object type rather than product affiliation. The adgncons.sql script prepares adcrtbsp.sql,

configures the database to hold the new products to be added during the upgrade, and switches your system to use the new tablespace model.

c)

1.This will generate adcrtbsp.sql with OATM TS the script (MODIFY to include proper paths)

2.Login as system and run adcrtbsp.mod.sql

Generated by adgncons.sql, this script creates the new tablespaces, allocates unlimited tablespace to all APPS users, updates fnd_product_installation table with

correct data and index tablespace information, assigns default tablespace to all APPS users, and sets the new_ts_mode flag in fnd_product_groups to Y.

Ensure that you have adequate disk space for all the changes and additions that adcrtbsp.sql is going to make.

d)

3.Copy adgrants.sql from patch directory to DB Server $ORACLE_HOME/appsutil/admin

Login to DB server as sysdba

 @adgrants.sql APPS

Grants SYS privileges needed by Applications, and creates required views in SYS

4) Run utlu102i.sql and utltzuv2.sql scripts

a)Copy the following Files from new Home

$ORACLE_HOME/rdbms/admin/utlu102i.sql

$ORACLE_HOME/rdbms/admin/utltzuv2.sql

1.Run

sqlplus '/as sysdba'

SQL> spool Database_Info.log

SQL> @utlu102i.sql

SQL> spool off

Check the output generated and correct the errors showing up

Following two init ora parameters may be required to be changed

WARNING: --> "streams_pool_size" is not currently defined and needs a value of at least 50331648

WARNING: --> "session_max_open_files" needs to be increased to at least 20

5) Run Gather Stats

Run the gather stat script as mentioned below by replacing the vale of <schema_name>.

exec dbms_stats.gather_schema_stats('<schema_name>',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

6) Check for deprecated CONNECT ROLE

Spool depreciated grantees of connect role using

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');

7) Create DBLINKS script

Create DB_LINK script using

SELECT

'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)

||'connect to ' || L.USERID || ' identified by '''

||L.PASSWORD||''' using ''' || L.host || ''''

||chr(10)||';' TEXT

FROM sys.link$ L,

sys.user$ U

WHERE L.OWNER# = U.USER# ;

8) Compile Invalid

a) Compile Invalid using

@$ORACLE_HOME/rdbms/admin/utlrp.sql

b) Spool Invalid using

spool invalid_pre.lst

select substr(owner,1,12) owner,

substr(object_name,1,30) object,

substr(object_type,1,30) type, status from

dba_objects where status <> 'VALID';

spool off 

9) Disabling Archive Log Mode

Connect as SYSDBA

startup mount

alter database noarchivelog;

alter database open;

shut immediate;

D) Upgrade Database

Database can be upgraded by DBUA or manual method.

DBUA is easy and recommended method.

1.Initiate a VNC session to 9i DB server

2.source10g Home environment (sample file below)

ORACLE_HOME="<give 10g path>"

export ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

ORA_NLS10="<ORACLE_HOME_PATH>/nls/data/9idata"

export ORA_NLS10;

ORACLE_SID="<SID>";

LD_LIBRARY_PATH="/u01/oracle10g/lib:/usr/openwin/lib:/u01/oracle10g/ctx/lib";

export LD_LIBRARY_PATH;

LIBPATH="/u01/oracle10g/lib:/usr/dt/lib:/usr/openwin/lib:/u01/oracle10g/ctx/lib:/u01/oracle10g/lib32";

export LIBPATH;

export ORACLE_SID;

unset ORA_TZFILE

3.Go to 10gHOME/bin

4.Run dbua 

E) Post- Upgrade Steps

1) Run olstrig.sql

In 10g Upgraded instance

As sysdba run $ORACLE_HOME/rdbms/admin/olstrig.sql

2) Fix Korean Lexer

Patch 5005469: NEED SCRIPT TO ASSIST WITH MIGRATION FROM KOREAN_LEXER TO KOREAN_MORPH_LEXER

Set your current directory to the directory where the patch is located: # #    % cd 5005469 # #  Ensure that the directory containing the opatch script appears in #  your $PATH; then enter the following command: # #    % opatch apply

cd to <patch_dir>/5005469/files/ctx/sample/script

as sysdba run

drkorean.sql

SQL> @drkorean.sql

List of indexes that use KOREAN_LEXER as top level lexer:

List of indexes that use KOREAN_LEXER as a sub lexer:

Migrate KOREAN_LEXER to KOREAN_MORPH_LEXER

Rebuild all indexes that use korean lexer as top level lexer:

Reindex all documents that use KOREAN_LEXER as sub lexer

3) Grant create procedure privilege on CTXSYS

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

se SQL*Plus to connect to the database as APPS and run the script using the following command:

$ sqlplus apps/[APPS password] @adctxprv.sql [SYSTEM password] CTXSYS

Connecting to SYSTEM

Connected.

PL/SQL procedure successfully completed.

Commit complete.

4) Set compatible paramaeter

Set the compatible paramaeter to 10.2.0  in init<env>.ora file

#NOTE THIS IS VERY IMPORTANT FOR THE SUCCESS OF DATA MIGRATION PROCESS LATER ON

5) Check the self containment of Tablespaces

Run the below tts_check.sql to check the self containment of tablespaces

declare

checklist varchar2(4000);

i number := 0;

begin

for ts in

(select tablespace_name

from dba_tablespaces

where tablespace_name not in ('SYSTEM','SYSAUX')

and contents = 'PERMANENT')

loop

if (i=0) then

checklist := ts.tablespace_name;

else

checklist := checklist||','||ts.tablespace_name;

end if;

i := 1; end loop;

dbms_tts.transport_set_check(checklist,TRUE,TRUE);

end;

/

select * from transport_set_violations;

You may get following messages which requires correction :

Sys owned object  <object_name> in tablespace USERS not allowed in pluggable set                                                                    

Index <schema>.<index> in tablespace SYSTEM points to partition INTERNAL  of table <schema>.<table> in tablespace <tsname> outside of transportable set 

For which you need to move/drop objects as follows

SQL> alter table <object_name> move tablespace SYSTEM;

Table altered.

SQL> drop index <schema>.<index> ;

Index dropped.

and run again the script :

SQL> @tts_check.sql

PL/SQL procedure successfully completed.

no rows selected