Upgrade R12 Database server

How to upgrade R12 Database server from 10.2.0 to 11.1.0 (10g to 11g) manually ?

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home
as a separate ORACLE_HOME in parallel to 10g Oracle Home.
We have to install the new database for upgrading the existing database.
But before installing check the software versions for…..
a)Oracle database version and check the appropriate upgrade path accordingly depending on the present version.
b)E-business suite version—minimum is 12.0.4 and apply interoperability patch for 11g on apps node using adpatch
c)Autoconfig has to be upgraded to latest version by applying 7207440.
I did not need to apply as my instance is on 12.0.6 and 6728000(12.0.6 upgrade patch) supersedes this autoconfig patch..
Now steps for database installation
1.Apply patch 6400501 to Apps 10.1.0.5 Oracle-home
2. Deregister the current database if you want to change database sid,host,port.
And update autoconfig for database also before that.
$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl appspass=apps
contextfile=$CONTEXT_FILE -removeserver
3. Update applications context file with new database parameters….
s_dbhost
s_dbdomain
s_db_serv_sid
s_dbport
s_apps_jdbc_connect_descriptor
to new database values. Don’t run auto config on apps tier now….
otherwise apps will not be able to connect to database at all.
Run autoconfig only after complete upgrade process is finished..
Database Installation
The 11.1.0 Oracle home must be installed on the database server node
in a different directory other than the current Oracle home
Log in to the database server node as the owner
of the Oracle RDBMS file system and database instance. Ensure
that environment settings, such as ORACLE_HOME, are set for the new
Oracle home you are about to create, and not for any existing
Oracle homes on the database server node
Choose to install software only (without any default database) in the new location.
After the installation,
Run utlu111i.sql (located in 11g_ORACLE_HOME/rdbms/admin) on source 10g database
and check the output.
SQL>@/11g_oracle_home/rdbms/admin/utlu111i.sql
It displays warnings and recommends steps to clear the issues.
Time Zone Issue
select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 3
If time zone file version is less than 4 then apply time zone patch 5632264
This can be done using opatch.
unzip the patch and run opatch going into the unzipped patch directory.
or manually copy the .dat files under 5632264/files/oracore/zoneinfo into
$ORACLE_HOME/oracore/zoneinfo
Bounce the database and check the TIMEZONE version.
Do not forget to take a backup of old zoneinfo directory before this.
Re-run utlu111i.sql after patching the database to record
the new timezone file version.
This time it says something like Database contains stale optimizer statistics.
So..
Gather statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(’SYS’);
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(’SYSMAN’);
And other schemas which ever it recommends for.
Run the pre-upgrade utility once again to make sure,
you don’t get any warnings.
Copy initialization file (pfile) from source (10g) to target (11g)
Modify initialization parameters
1.
background_dump_dest replaced by diagnostic_dest
user_dump_dest replaced by diagnostic_dest
core_dump_dest replaced by diagnostic_dest
comment above three deprecated parameters
and add
*.diagnostic_dest=’/11g_base’
2.
Change *.compatible=’10.2.0′
to *.compatible=’11.1.0′
Set Environment to new Oracle home
export ORACLE_HOME=/oraDB/app/oracle/product/11.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=TEST
export TNS_ADMIN=$ORACLE_HOME/network/admin/Context_Name
sqlplus ‘/as sysdba’
Startup the database in upgrade mode.
SQL> startup upgrade
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
This takes considerably a long time depending on the size of database.
Once the upgrade finishes,It will shut down the database automatically.
Login again as sysdba and startup in normal mode.
You will get NLS errors as nls directory under new oracle_home does not have 9idata directory
Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.
I , actually copied the 9idata directory from 10g oracle_home to new location.
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 11g Oracle home.
SQL>startup
Check the dba_registry for the components and its status
SQL> select comp_name,version, status from dba_registry;
Run Post-Upgrade Status Tool provides a summary of the upgrade
SQL>@?/rdbms/admin/utlu111s.sql
Perform upgrade actions that do not require the database to be in UPGRADE mode
SQL>@?/rdbms/admin/catuppst.sql
Check for invalid objects
SQL> select object_name, owner, object_type from all_objects where status= ‘INVALID’;
Compile Invalids
SQL> @?/rdbms/admin/utlrp.sql
Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source oracle_home to target oracle_home
This finishes upgrade of 10.2.0.3 database to 11.1.0.6 in E-Business Suite R12.
Further we have to upgrade the existing 11.1.0.6 to 11.1.0.7 using a patchset.
which I will discuss in my next post..
After Upgrade
Start the new database listener
Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.
Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the database server node.
$ sqlplus ‘/ as sysdba’
SQL> @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
SQL>@adctxprv.sql SYSTEM_password CTXSYS
Implement and run AutoConfig in the new database Oracle Home.
How to implement autoconfig in a new oracle_home …coming up…..