In this article I do Oracle Database Upgrade from Oracle 10.2.0.5 to Oracle 188.8.131.52 in a standalone server.
1. Check the Original Oracle Version. You can use the following command:
> sqlplus / as sysdba; > select version from v$instance;
from your SAP -> System -> Status
Or from transaction code DBACOCKPIT.
2. Check Hardware and software requirements
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
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 184.108.40.206 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 220.127.116.11 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 18.104.22.168 Software as describe in article How to Install SAP Oracle Database Software.
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
- You have stopped the SAP system (explained in How to startup / shutdown SAP in windows)
- Update Oracle Client as described in How to update Oracle Client on Windows.
- Do not restart the SAP Central Instance or Dialog Instance
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
- Create a complete database and operating system backup, and back up any archive logs from Oracle
- 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
- 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.
- You have stopped the SAP system.
- Make sure that the database is running.
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.
- 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
- 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.
- 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.
- 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’.
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
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.