Oracle Upgrade

Oracle upgrade in SAP
Share on Facebook0Share on LinkedIn0Tweet about this on TwitterShare on Google+0
Please Share!!

Source Database Version : => Target Database Version :


1) Checking the Correct UNIX Users, Groups, and Authorizations
–> You need to check that the ora user has write authorization for the /oracle directory, since the ora user performs the database installation.

touch /oracle/write_test
rm /oracle/write_test

–> If the user ora does not have write authorization, log on to your system with the user root and enter the command:

chgrp dba /oracle
chmod 775 /oracle

–> Enter the following command with the user ora:
umask 022

2) Checking the Directory Structure
You need to verify the availability and correctness of the following Oracle directories:
a) stage
b) oracle inventory directory
c) home directory

a) For the upgrade to Oracle Database 10g Release 2 (10.2) create the staging directory /oracle/stage/102_64.

–> When the RDBMS CDs have been mounted, use the SAP tool SAPCAR to extract all the RDBMS CDs to /oracle/stage/102_64 as user ora.

SAPCAR –xvf OR110264.SAR

–> After extraction of the Oracle software to /oracle/stage/102_64 do the following:
Rename the original SAP folder by performing
mv /oracle/stage/102_64/database/SAP /oracle/stage/102_64/database/SAP_ORIG

–> Download the file (for 64-bitplatforms) attached to SAP Note

819830 and copy it to a temporary location such as /tmp.

–> Extract the zip file by performing
cd /oracle/stage/102_64/database
unzip /tmp/

b)–> Check the Oracle inventory directory.
The Oracle inventory directory maintains the inventory of all Oracle software installed on the system. The path to the Oracle inventory directory is mentioned in the file oraInst.loc, which you can find under
/var/opt/oracle or under /etc.

–> One of the entries of oraInst.loc or inventory_location points to the location of the Oracle inventory. When you execute the OUI for the first time on the system, the OUI prompts you for the path to theinventory directory. The
default location for the Oracle inventory is


c)–> Create the empty Oracle home directory before starting the OUI.
You need to create the Oracle home directory before you can start the Oracle software installation. The Oracle Home directory is normally

3) Running the Pre-Upgrade Script

–> Execute the following commands:
sqlplus /nolog
SQL> connect / as sysdba
SQL> spool /tmp/pre_upgrade.log
SQL> @/oracle/stage/102_64/database/SAP/utlu102i.sql

4)Checking the Prerequisites for the Oracle Universal Installer
–> Log in as the Oracle user ora.
cd /oracle/stage/102_64/database
./runInstaller –executeSysPrereqs

5) Checking the SAP System Prerequisites
–> Download the latest version of dbsl (,, or dboraslib.o) for your release from the SAP Service Marketplace at:

6) Migrating to Automatic Undo Management
–> Refer note:- 600141

7) Checking the SYSTEM and SYSAUX Tablespace for Freespace
–> You can check this by executing one of the following commands as the ora user:

brspace –f dbshow –c tsinfo –s SYSTEM
brspace –f dbshow –c tsinfo –s SYSAUX

8) Checking for Invalid Database Objects
–> This optional step lets you check if there are any invalid database objects before the database upgrade to Oracle database 10g starts. After the upgrade It helps you distinguish database objects that have become invalid due to the upgrade process from those that were already invalid to start with.

–> Execute following commands
SQL> spool invalid_objects
SQL> select unique object_name, object_type, owner from dba_objects
where status =’INVALID’;
SQL> @?/rdbms/admin/utlrp.sql
SQL> select unique object_name, object_type, owner from dba_objects
where status = ‘INVALID’;
SQL> spool off

–> Check the list of invalid objects after the upgrade.

9) Installing the Oracle Instant Client

–> Refer note 819829.

Oracle Database Upgrade

1) –> To install the database software you need to check and set the environment variables of user ora because this user installs the Oracle software.

–> You set the environment variables below by editing the files and .dbenv_.csh in the home directory of ora.

–> Make sure the following environment variables are not set:


–> Set the following environment variables:
ORACLE_HOME /oracle/SID/102_64
NLS_LANG /oracle/stage/102_64/database/SAP

–> Set the corresponding environment variable for the library path:

–> Include $ORACLE_HOME/bin in your PATH or path variable,

2) Backing Up the Oracle Inventory

–> If the Oracle inventory exists, check its location by looking at the entry in one of the following files: /var/opt/oracle/oraInst.loc /etc/oraInst.loc The location is shown as follows: inventory_loc=

–> Make a backup of your existing Oracle inventory before you start the Oracle Database 10g Release 2 (10.2) software installation.

3) Running the Oracle Universal Installer
–> cd /oracle/stage/102_64/database/Disk1/SAP

4) Installing the Current Patch Set
–> Refer note 871735. Install as ora user.

5) Installing Required Interim Patches via OPatch
–> Refer note 871096.

6) Copying and Adapting the SQL*Net Files

After the database software installation, you must copy and modify the old SQL*Net files, that is, listener.ora, sqlnet.ora, and tnsnames.ora.

–> Logon as ora user.
If ora has environment variable TNS_ADMIN set, do the following:

a) Choose a new location for the new SQL*Net parameter files and set TNS_ADMIN to point to the new location .

b) Copy the SQL*Net parameter files from the old location to

c) Edit the new SQL*Net files by adapting all file paths for the new directory. Be sure to adapt the value of ORACLE_HOME in the listener.ora file.

–> The SQL*Net parameter files are located in /network/admin:

a) Copy the SQL*Net parameter files from to.

b) Edit the new SQL*Net files by adapting all file paths for the new directory. Be sure to adapt the value of ORACLE_HOME in the listener.ora file.

7) Backing Up the Oracle Database

–> Create a complete database backup.

8) Shutting Down the SAP System and Oracle Listener Process

—>You need to shut down the SAP system and the Oracle listener running process running in the old ORACLE_HOME, for example, in /oracle//920_64.

9) –> Stop the SAP application server by entering the following command as user sapsid>adm:
stopsap r3

Stop the Oracle listener by entering the following command as user ora:
lsnrctl stop

10) Checking the oratab Entry
–> Make sure that the oratab file contains the following entry:
Where old_ORACLE_HOME stands for the path to the old oracle software Installation.

–> If this entry does not exist, insert it using a text editor.

11) Starting the Database Upgrade Assistant
–> Make sure that you start the DBUA in the Oracle 10.2 user environment. To do this, carefully check the first point below.

Confirm that the following environment variables point to Oracle 10.2 directories:
ORACLE_HOME points to /oracle//102_64
–> Before you start the DBUA make sure that the DISPLAY environment variable is correctly set
–> You start the DBUA:

Respond appropriately to the DBUA dialogs:
The Welcome screen provides information about the upgrade assistant

a) Choose Next to continue.

The Databases screen lets you select the database to be upgraded.

b) Select the correct database and choose Next.
The DBUA now analyzes the database to be upgraded, performs pre-upgrade checks, and issues warnings if necessary. You can ignore warnings about the following:

Stale optimizer statistics
Changes to the Oracle CONNECT role

–> Make the following entries:
Datafile Path: $SAPDATA_HOME/sapdata1/sysaux_1/sysaux.data1

–> Select Reuse Existing File Name.
Enter 1000 MB in Size.
Enter 100 MB in Increment.
Enter 2000 MB in Maximum Size.

–>Accept the default values, select Recompile invalid objects after the upgrade, turn off archiving for the duration of the upgrade, and choose Next

–> Since you have already backed up the database, select I have already backed up my database and choose Next.

–> Make sure that Configure the database with Enterprise Manager is not selected and choose Next.

–>Review the summary of changes and choose Back to make any final adjustments, if required.

–> Start the database upgrade by choosing Finish.

–> Review the changes made and exit the DBUA.

Post-Upgrade Steps

1)–> Finalizing the Environment of the oraUser
You need to make sure that the changes you made to the Oracle user environment are permanently stored in the profiles (.cshrc, .dbenv_.csh, and so on).

–> Make sure that the environment does not contain any variables that still refer to the old database release.

2) Checking the Upgraded Database

–> Check that the database is now running with Oracle Database 10g Release 2
(10.2) software in an Oracle Database 10g Release 2 (10.2) environment by entering the following commands:

a) Log onto the database as the ora user.

b) Enter the following command to start SQLPLUS:
sqlplus / as sysdba

c) Enter the following SQL commands:
–> Enter the following command to determine the release of the installed software:

SQL> select version from V$INSTANCE;

–> Enter the following command to determine the release and status of the installed database components:
SQL> select comp_id, version, status from dba_registry;

Make sure that the command returns the status VALID.
–> Check that the version returned in the previous two commands is identical.

3) –> Check that the value for the version returned from the above command is the same level as the installed patchset.

For example, if the installed patchset is Oracle, the above command should also report the version for the CATALOG and CATPROC components as

4)–> After the upgrade check that the following document is created:


5) Updating BR*Tools

–> Download the most current

–> After you have extracted the newest BR*Tools, you also need to copy the BR*Tools configuration files and init.utl from the old $ORACLE_HOME/dbs directory to the new one (init.utl is only available if you are using a third-party backup

cd /dbs
cp init.utl $ORACLE_HOME/dbs

–> Execute the following commands as the ora user to update the SAPDBA role:
cp /usr/sap//SYS/exe/run/sapdba_role.sql $ORACLE_HOME/dbs
cd $ORACLE_HOME/dbs.
sqlplus /nolog @sapdba_role .

6) Backing Up the Database

–> You need to back up your upgraded Oracle Database 10g database.

Perform a full database backup.

7) Performing Post-Upgrade Tasks

–> Change database compatibility by executing the following SQL command as the ora user:
SQL> alter system set compatible=’10.2.0′ scope = spfile;

–> Restart the Oracle database

–> Deactivate the Oracle recycle bin by executing the
SQL> purge dba_recyclebin;
SQL> alter system set recyclebin = ‘off’
For more information, see SAP Note 838982.

–> Gather Oracle optimizer statistics as follows:

a) Execute the following commands as the ora user:

SQL> spool $ORACLE_HOME/cfgtoollogs/dbstats.log
SQL> execute dbms_scheduler.disable(‘GATHER_STATS_JOB’);
SQL> execute dbms_stats.gather_system_stats;
SQL> execute dbms_stats.gather_dictionary_stats
SQL> execute dbms_stats.gather_fixed_objects_stats;

b) Check the log file dbstats.log created at $ORACLE_HOME/cfgtoollogs for any errors during the gathering of statistics

–> Identify the objects that are invalid after the upgrade as follows:
a) Execute the following commands as the ora user:

SQL> @?/rdbms/admin/utlrp.sql;
SQL> spool $ORACLE_HOME/cfgtoollogs/invalid_post.log
SQL> select count(*) from dba_objects where status = ‘INVALID’;
SQL> 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’ order by owner;
SQL> spool off;

b) Check the log file invalid_post.log. created at $ORACLE_HOME/cfgtoollogs/invalid_post.log to compare the invalid objects before the start of the upgrade..

–> Check the Oracle database parameters against SAP Note 830576 and adjust them if necessary.

–> Make sure that the following database parameters, if set, are removed;


–> Restart the database to activate the changes:

a) Log on to the database with SQL*Plus
b) Execute the following commands:
sqlplus / as sysdba
shutdown immediate

–> Gather statistics for SAP schemas:

a)With Oracle Database 10g all tables have statistics, which means that you need to update some entries in the DBSTATC table before starting BRCONNECT, as follows:
sqlplus /nolog
SQL> connect / as sysdba
SQL> @/oracle/stage/102_64/database/SAP/updDBSTATC10.sql

b)Update the DBCHECKORA control table after the upgrade as follows:
cd /oracle/stage/102_64/database/SAP
sqlplus sap/ @dbcheckora10upgr

c) Update the Oracle database statistics with BRCONNECT as follows:
brconnect -c -u / -f stats -t all –f collect -p 4

–> Installing Oracle Critical Patch Updates via OPatch
Refer note 850306 and 871096

–> Checking the oratab entry
a) You check the entry for the oratab file.
b) Make sure that there is an entry like the following in your oratab file:
new_ORACLE_HOME indicates the path to the Oracle Database 10g
c) If the entry does not exists insert it using a text editor.

–> Starting the Listener Process
Start the listener:
lsnrctl start
Check that you can remotely connect to sqlplus SAP/pwd@SID

–> Renaming the Old $ORACLE_HOME/bin Directory

Rename the old Oracle home directory bin with the following command:
mv /bin /bin-old

–> Checking the Read Authorization of the adm User

1. Make sure that the adm user has read authorization for the directory $ORACLE_HOME.

2. If this is not true, enter the following command as the ora user:
chmod 755 $ORACLE_HOME

–> Changing the Environment for the adm user
You have to change the environment for the adm user, but only on the database server.
1.On the database server set the ORACLE_HOME environment variable to point to the location of the Oracle Database 10g software installation, for example:

2.Make sure that the change to the user environment is permanently stored in the profiles (.cshrc, .dbenv_.csh, and so on).

3.Log out and log on again as the adm user.

4.Check whether R3trans can connect to the database:
R3trans –d

–>Checking SAP Profile Parameters
You have to check the following SAP profile parameters:

1.Set DIR_ORAHOME and DIR_CLIENT_ORAHOME in the instance profiles to the corresponding values of the ORACLE_HOME environment variable of the adm user.
These variables point to the ORACLE_HOME used by the application server.

2.Set DIR_SERVER_ORAHOME in the default profile to the value of the ORACLE_HOME environment variable of the ora user on the database server.

–> Starting the SAP System
1. If R3trans connects without problems, start the SAP system:
3. Check the new Oracle version using SAP transaction ST04.

–> Configuring the New Features for Oracle Database 10g
Release 2 (10.2)
Refer note 828268

Useful Tutorials in SAPYard

1. ABAP for SAP HANA Tutorials
2. ABAP Web Dynpro Tutorials
3. GOS Tutorial
4. OOPs ABAP Tutorial
5. HANA Tutorial
6. SAP Netweaver and OData Tutorial
7. SAP Adobe Form Tutorial
8. SAP Fiori Tutorial

Share on Facebook0Share on LinkedIn0Tweet about this on TwitterShare on Google+0
Please Share!!

About the Author

Vinay Singh
Vinay Singh
Qualification: BE from Bharati Vidyapeeth, India. MBA from Mannheim Business School, Germany. SAP BI/HANA Experience since 2006. Author of books : ‘Real Time Analytics with SAP HANA‘ & ‘Creating and Using Advanced DSOs in SAP BW on SAP HANA‘. Find more about him on LinkedIn.