Upgrading SAP Oracle Database

by

In this article I do Oracle Database Upgrade from Oracle 10.2.0.5 to Oracle 11.2.0.3 in a standalone server.

Preparation

1. Check the Original Oracle Version. You can use the following command:

>sqlplus -version

Or

> sqlplus / as sysdba;
> select version from v$instance;

Or

from your SAP -> System -> Status

Or from transaction code DBACOCKPIT.

2. Check Hardware and software requirements 

Procedure

You need to check that you meet the hardware and software requirements for running the new Oracle
release. The complete list of hardware and software requirements for every platform is included in the
Oracle documentation.

3. Checking Additional Requirements for the Upgrade

  • Make sure you have read the SAP Notes for the upgrade
  • Oracle 11.2 is only released and certified for SAP products running with SAP BASIS 640 and above,
    and SAP kernel release 640_EX2 and above. Check the Product Availability Matrix for compatibility.
  • Download the Oracle Database 11g Release 2 (11.2) installation guide.
  • Download the Oracle Database 11g Release 2 (11.2) software including the 11.2.0.3 patch sets and
    required patches or patch collection.
  • Download the latest version of the Oracle 11.2 Instant Client software
  • Download the latest version of the DBATOOLS (BR*Tools)

4. Checking the SAP System Prerequisites

  • Check related SAP notes according to Oracle Upgrade Guide.

SAP Oracle Database Upgrade

1. Installing the Oracle 11G Database Software

Install Oracle 11.2.0.3 in new Oracle DB Home. In this tutorial i use E:\oracle\SOL\11203 as new Oracle DB Home.

Note: You install the Oracle Database 11.2.0.3 Software as describe in article How to Install SAP Oracle Database Software.

Prerequisites

You have stopped the SAP system (not the database instance) with the SAP MMC. For more detailed, read Starting and Stopping the SAP System on Windows.

2.  Installing Required Patches

After the database software installation, you need to install all required Oracle database patches, which on Windows include one patch collections and one or more additional (generic) patches. Find available patches on SAP Service Marketplace.

3. Updating the Oracle Client Software

Procedure:

4. Copying and Adapting the SQL*Net Files

You must copy the old oracle profile that is, init<SID>.ora, init<SID>.sap, init<SID>.dba, to <new Oracle_Home>/database as described below (or using Windows Explorer).

>copy \oracle\SOL\102\database\initSOL.ora \oracle\SOL\11203\database
>copy \oracle\SOL\102\database\initSOL.sap \oracle\SOL\11203\database
>copy \oracle\SOL\102\database\initSOL.dba \oracle\SOL\11203\database
>copy \oracle\SOL\102\database\pwdSOL.ora \oracle\SOL\11203\database

And copy also SQL*Net files,  listener.ora, sqlnet.ora, and tnsnames.ora, to <new Oracle_Home>\database\network\admin and adapt them as described below.

>copy \oracle\SOL\102\network\admin\listener.ora \oracle\SOL\11203\NETWORK\ADMIN
>copy \oracle\SOL\102\network\admin\sqlnet.ora \oracle\SOL\11203\NETWORK\ADMIN
>copy \oracle\SOL\102\network\admin\tnsnames.ora \oracle\SOL\11203\NETWORK\ADMIN

Adapt listener.ora: change Oracle Home to E:\oracle\SOL\11203:

5. Backing Up the Oracle Database

You must have a complete and usable database backup otherwise you might lose data if the upgrade
fails.

Procedure:

  • Create a complete database and operating system backup, and back up any archive logs from Oracle
    10g.
  • Check that the backup is usable. If for any reason you have problems during the upgrade you must
    be able to restore the database from this backup.

6. Removing Old Oracle Listeners

You use the Oracle Net Configuration Assistant from the old <Oracle_Home> to delete old or unused
Oracle Listeners.

Procedure

  • Choose Start All Programs <Old_Oracle_Home> Configuration and Migration Tools Net Configuration
    Assistant. On the Welcome screen, choose Listener Configuration and then Next.

  • Choose Delete listener and select the listener you want to delete from the drop-down list (normally
    this is the listener with the name LISTENER).

7. Changing the Environment for User <sapsid>adm

In this step you will change the environment for user <sapsid>adm.

Prerequisites:

  • You have stopped the SAP system.
  • Make sure that the database is running.

Procedure:

In the user environment of <sapsid>adm, rename the following variables:

  • ORACLE_HOME to <New_ORACLE_HOME>

From System Properties -> Advanced -> Environment Variables change ORACLE_HOME to E:\oracle\SOL\11203

  • TNS_ADMIN (if it exists)

8. Performing the Database Upgrade

You use the Oracle Database Upgrade Assistant (DBUA) to upgrade the database.

Prerequisites

  • the SAP system is stopped. Do not stop the database instance. The database instance must be running in the current (old) environment.
  • Change the registry entry of ORA_<DBSID>_AUTOSTART by entering the command:
<new_Oracle_Home>\bin\oradim -edit –sid <dbsid> –startmode manual

Procedure

  • Log on as <sapsid>adm user.
  • Start the Oracle Database Upgrade Assistant using one of the following options:

A. Use a PowerShell script (recommended).

To start the Oracle Database Upgrade Assistant (DBUA) with a PowerShell script, proceed as described in SAP note 1431793, section “Performing Database Upgrade on Windows”. The advantage of using a PowerShell script is that all required parameters are already specified in the DBUA, and manual post-installation tasks are reduced to a minimum.

In this example i use a PowerShell script.

B. Use the Start menu.

Choose Start All Programs Oracle – <NEW_ORACLE_HOME_NAME> Configuration and
Migration Tools Database Upgrade Assistant .

PERFORMING DBUA using Powershell:

On Windows you can use the dbua wrapper script dbua.sap.ps1 to run the DBUA with the correct command line options.

Procedure:

  • Open a Windows Powershell in elevated mode (run as administrator) and change the working directory to <ORACLE_HOME>\sap\ora_upgrade\post_upgrade (if you don’t find this folder you need to apply generic Oracle database patch (patch id 9584028); read article Install Oracle Patch using OPatch for details.
PS> cd <ORACLE_HOME>\sap\ora_upgrade\post_upgrade
PS

  • From this Powershell you run the dbua.sap.ps1 script. And answer with the correct target version, then DBUA will be started.
          PS> .\dbua.sap.ps1

  • On each screen of DBUA choose ‘Next’ to go to the next page. On the summary screen, review the summary of changes and start the database upgrade by choosing ‘Finish’.

Post-Upgrade Steps

1. Changing Environment Variables

Set Oracle_HOME variable to E:\oracle\SOL\11203

2. Creating the Oracle TNSListener Service

  • Stop the old Oracle listener before you create the Oracle TNSListener Service

  • Create New Listener Service

Open a command prompt and enter the following command:

#<Complete_Path_To_New_Oracle_Home>\bin\lsnrctl start <Listener_Name>

3. Changing the Registry Value of Oracle Autostart

Run command: <new_Oracle_Home>\bin\oradim -edit -sid <dbsid> -startmode manual

4. Checking the Start Up Type of the Oracle Services

Here you start the Oracle services (Database and Listener), if not yet started and Start the Distributed Transaction Coordinator Service.

5. Updating the SAP Executables and the BR*Tools

You need to update the SAP Executables and BR*Tools to the latest version.

6. Performing Post-Upgrade Tasks

You need to perform certain post-upgrade tasks.

  • Gather Oracle optimizer statistics by executing the following commands as the <sapsid>adm user:
    brconnect -u / -c -f stats -t system_stats
    brconnect -u / -c -f stats -t oradict_stats
  • Check the Oracle database parameters against SAP Note 1431798 and adjust them if necessary.
    You can find an automated script in SAP Note 1171650 to help you check whether your SAP system
    complies with the database parameter recommendations at any given point in time.
  • Update the Oracle database statistics with BRCONNECT as follows:
    > brconnect -u / -c -f stats -t all -f collect -p 4
  • Run post_ugprade_status.sql.
    Open a command prompt and change to the directory <new_oracle_home>\sap\ora_upgrade
    \post_ugprade.From this directory start sqlplus.exe and enter the following commands:
    SQL>connect / as sysdba
    SQL>@post_upgrade_status.sql
    This provides an overview of the status of the upgraded database.

7. Uninstalling the Old Oracle Software

To deinstall the Oracle 10.2 software, you use the Oracle Universal Installer (OUI).

8. Backing Up the Database

You need to perform full back up your upgraded Oracle Database 11g database.

9. Configuring the New Features for Oracle Database 11g Release 2 (11.2)

10. Check the Oracle Version.

From your SAP -> System -> Status, verify that your Oracle version is updated.

Leave a Reply