Upgrade oracle database from 9i to 10g

Oracle apps version-11.5.10.2, database version-9.20.5, os-solaris 9 -64 bit, upgraded database version-10.2.0.4
Space requirement-->
RAM-atleast 1G, Swap space-1.5 times to .75 times the space of RAM, 400 MB of disk space in the /tmp directory, Between 1.5 GB and 3.5 GB of disk space for the Oracle software, depending on the installation type
/bin/isainfo -kv
If it displays -64-bit sparcv9 kernel modules then only you can install oracle 10g.
The following kernel parameters are also required->
Parameter Recommended Value
noexec_user_stack 1
semsys:seminfo_semmni 100
semsys:seminfo_semmns 1024
semsys:seminfo_semmsl 256
semsys:seminfo_semvmx 32767
shmsys:shminfo_shmmax 4294967295
shmsys:shminfo_shmmin 1
shmsys:shminfo_shmmni 100
shmsys:shminfo_shmseg 10
One can check these values in /etc/system.You can modify them by taking a backup of system file and the parameter
set semsys:seminfo_semmni=100
and then reboot the server using "reboot" command.
The database sid and listener port was kept same.
-Apply apps interoperability patch-10g Release 2 interoperability patch for 11.5.10 (4653225)
Install 10.2 oracle home in a different directory than 9i
login as "oracle" user
Set the following environment variables->>
TMP=/tmp
TMPDIR=/tmp
export TMP TMPDIR
ORACLE_HOSTNAME=xxx.yyy.com
export ORACLE_HOSTNAME
ORACLE_BASE=/u07/fatstdb
export ORACLE_BASE
ORACLE_HOME=/u07/fatstdb/10g
export ORACLE_HOME
unset TNS_ADMIN
Goto Advanced option->

Select Enterprise Edition->

Specify the path for 10g oracle home->

Verify that all the pre-req checks completed successfully->

Select "No" when it prompts you for "Do you want to upgrade the database now?"

Select "Install Database Software Install' only.


Run root.sh as root user when it prompts to do so.

The 10g software has been successfully installed.
Now install the 10g companion products-->

Specify the same ORACLE_HOME and path as specified while installing 10g software->

Select "Oracle 10g Products 10.2.0.1.0"

Verify that all the pre-req checks completed successfully->



Above shows that the oracle 10g products installation was successful.
Perform 10.2.0.4 patch set pre-installation tasks

SELECT version FROM v$timezone_file;
If this reports a version lower or higher then 4, see OracleMetalink document 553812.1 Actions for the DSTv4 update in the Release 10.2.0.4 patchset.

ORACLE_HOME=/u07/fatstdb/10g

export ORACLE_HOME


PERL5LIB=/u07/fatstdb/10g/perl/lib/5.8.3:/u07/fatstdb/10g/perl/lib/site_perl/5.8.3


export PERL5LIB


LD_LIBRARY_PATH=/u07/fatstdb/10g/lib:/usr/dt/lib:/usr/openwin/lib::/opt/sfw/lib


export LD_LIBRARY_PATH


PATH=/usr/ccs/bin:/usr/sbin/usr/bin:/usr/ucb:/usr/ccs/bin:/etc:/usr/local/bin:/u07/fatstdb/10g/bin:/u07/fatstdb/10g/perl/bin:/usr/bin


export PATH

Run runinstaller.sh

Specify the same ORACLE_HOME and path as specified while installing 10g software->

Verify that all the pre-req checks completed successfully->



Run root.sh from root user

The above shows that 10.2.0.4 patch set installation was successful.
Create nls/data/9idata directory-
Login to new 10g oracle home and execute ->
perl cr9idata.pl
Creating directory /u07/fatstdb/10g/nls/data/9idata ...
Copying files to /u07/fatstdb/10g/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /u07/fatstdb/10g/nls/data/9idata!
Now set
ORA_NLS10=/u07/fatstdb/10g/nls/data/9idata
export ORA_NLS10
Shut down Applications server processes

If event="38004 trace name context forever, level 1" is defined in either the init<SID>.ora initialization parameter file or the spfile<SID>.ora server parameter file, remove it.

Unset the TNS_ADMIN environment variable.
Run the Pre-Upgrade Information Tool
copy the "utlu102i.sql" file from 10g oracle_home/rdbms/admin to some directory outside 10g home. Then connect to 9i oracle.
cp utlu102i.sql /u07/10gpreupg
sqlplus "/ as sysdba"
SPOOL info.log
@utlu102i.sql
Then, check the spool file and examine the output of the upgrade information tool.
Issues-->
Deprecated CONNECT Role
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
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.
select 'create database link ' name ' connect to ' userid ' identified by ' password ' using '''''host ''''';' from sys.link$;
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
Optimizer Statistics
exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade =>TRUE);
exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade =>TRUE);
exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade =>TRUE);
exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade =>TRUE);
exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade =>TRUE);
exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade =>TRUE);
Make a list of invalid objects
select object_name,owner from dba_objects where status='INVALID';
Shutdown db listener and database
Run $10oracle_home/bin/netca to configure a version 10 listener
select listener configuration->

select Add->

select listener name->

select TCP protocol->

Specify the listener port->

Specify No.

Listener configuration is successful.
Now copy this init<sid>.ora to $10goracle_home/dbs
Add the following parameters------->
streams_pool_size = 50331648
large_pool_size =8388608
session_max_open_files = 20
Comment the following parameters---->
--> "optimizer_max_permutations"
--> "row_locking"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "enqueue_resources"
--> "sql_trace"
Upgrade the database

Select the database you want to upgrade->

Click Yes


Specify location of sysaux tablespace

Select "recompile invalid objects at the end of upgrade"->

select the backup option you want->

Specify the Enterprise manager option->

Specify "Flash Recovery" option if you want->

Specify the passwords for dbsnmp and sysman->

Finally it shows the database upgrade summary-->


DBUA will show the progress->

Finally DBUA shows the 10g upgrade result->


Modify initialization parameters
Fix Korean lexers
sqlplus "/ as sysdba" @$ORACLE_HOME/ctx/sample/script/drkorean.sql
Run adgrants.sql -
if >=AD.I or 11.5.10

sqlplus "/ as sysdba" @adgrants.sql  APPLSYS

Grant create procedure privilege on CTXSYS

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

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

Implement and run AutoConfig on db tier

Gather statistics for SYS schema
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node.
sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;

Re-create grants and synonyms
Apply Oracle Receivables patch
Apply Oracle Receivables patch 5753621.
Restart the application tier