ORACLE 11g Upgrade

[Former Member]

Getting started:

1. This documentation explains how to upgrade the Oracle database for the SAP system from 10.2.0.5.0 to 11.2.0.2.0 on the Windows operating system.

Overview procedure:

• Ø Initial pre-requisites steps before the actual upgrade
• Ø Upgrading the oracle through the scripts
• Ø Post upgrades activities.

Initial pre requisite:

• Copy the old oracle home directory for a backup perspective in case if we required later to roll back to the old state.
• Download (51041033) all the 5 parts from the SMP and Install the oracle 11G software.
• Update the oracle instance client.  Reference (sap note :998004)
• Adjust the listener.
• Adjust the environmental variables
• Run the pre upgrade scripts

Up gradation of oracle:

• Upgrade the oracle by executing the upgrade scripts.

Post Upgrade activities:

• Run the post upgrade scripts.
• Set the environmental variables
• Activate the listener
• Change the registry value of oracle auto start parameter
• Update BR tools and SAPDBA Role
• Perform database update statistics commands
• Take database backup

Pre requisite: Step by step screen shots- Detailed procedure.

Step1: Take old oracle home directory as a backup. 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.

Step2:  stop the oracle services. To stop the oracle services go to SERVICES.MSC and stop the following services.

Step  3:  Installing the Oracle 11g Server Software.  

For installing the oracle 11G go to the respective folder where you extracted the oracle software 11G 51041033 and execute the command sapserver.cmd

It opens the Oracle Universal installer.

Step 4: No need of giving the email address and don choose the check box. Skip the warning and click next

Step 5: Give next

Step 6: choose the default option as install database software only and click next

Step 7: Choose single instance database installation and give next

Step 8: Choose English by default.

Step 9: select the default option and choose next

Step 10:

• Specify the oracle base value which should be set in the environment to <drive_containing_oracle_home>:\oracle
• Ø It also displays the value of ORACLE_HOME which should be \oracle\<DBSID>\1120<X> where 1120<X> is the oracle version number, here it is 11202

Step 11: The pre requisite check will run.

Step 12:  Once it got completed summary screen appears. Click install

Oracle 11G gets completed now.

Step 13: Updating the Oracle 11.2.0.2 Client Software:

We have to update the Oracle Instant Client with the newest version that is available on SAP Service Marketplace.

http://service.sap.com/swcenter-3pmain -> Oracle.  On the right-hand part of the Window, click on Oracle 11.2 Software (<your platform>) for the Oracle 11.2 Instant Client, or Oracle 10.2Software (<your platform>) for the Oracle 10.2 Instant Client. Choose Installation -> Microsoft Windows -> ORACLE database.

In the list of the downloadable objects, you find Oracle Client <our Oracle version>.

By default, the installation stores the Oracle Instant Client in the $(DIR_CT_RUN) directory. During the startup of an SAP instance, it is copied to the $(DIR_EXECUTABLE) directory.

NOTE: If we already have oracle instance client software then we have to delete that manually before we update the new oracle client software.

In our environment already there is no oracle client software and hence we download the oracle client software and placed in the new CRM DEV server as shown in the screen below.

Copy the client software that we had downloaded to the new CRM system.

Step 14 .Copying and Adapting the SQL*Net Files:

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

Procedure (NOTE: If you have multiple instances in the old Oracle_Home, you have to perform steps 1 to 4 only once before you upgrade the first database instance.)

1. 1. Log on as user <sid>adm in the old  database server .
2. 2. Copy the SQL*Net parameter files from the directory <old_ORACLE_HOME>\network\admin to the directory <new_ORACLE_HOME>\network\admin. I.e here copy to f:\oracle\CD7\11202\network\admin

1. 3. Edit the file listener.ora in the <new_ORACLE_HOME>\network\admin directory.

Identify the parameter ORACLE_HOME that belongs to the database you want to upgrade (SID_NAME in section SID_LIST) and change the value of the parameter ORACLE_HOME to point to the new ORACLE_HOME.

1. 4. Add the following entry to the file listener.ora:

ADR_BASE_LISTENER = <full_path_to_saptrace_directory>

Since our database is still running in the old environment, do not start the listener with the new listener.ora file at this point.

Step 15: Changing and Checking the Environment for User <dbsid>adm:

In theenvironment of the user <dbsid>adm, delete the following variables:

• ORACLE_HOME
• TNS_ADMIN (if it exists)

  Log off and log on again to activate the changes. 

Checking the Environment for User <dbsid>adm :

The environment variable SAPDATA_HOME must point to the directory where the subdirectories saparch, sapbackup, sapcheck, sapreorg, and saptrace are located.

Step 16: Run the Pre upgrade scripts:

Run the following pre upgrade scripts as shown in the screenshot.

Procedure to Run the pre upgrade scripts before the database upgrade:

• Log on to the database server as the Oracle database administrator user ('oracle' or 'ora<dbsid>').

• change the current directory to the location of the pre-upgrade scripts
• Execute the pre upgrade scripts as below

   Eg: SQL>@utlu112i.sql.  Similarly run all other pre upgrade scripts.

Step 17: Performing the Database Upgrade:

To upgrade the database, you need to start the Oracle Database Upgrade Assistant (DBUA), which is a graphical tool that leads you step by step through the upgrade

Prerequisite:

• Stop the SAP system in MMC .

• Change the registry entry of ORA_<DBSID>_AUTOSTART by entering the command:

<new_Oracle_Home>\bin\oradim -edit –sid <dbsid> –startmode manual

• start the listener services of 10.2 

Start the oracle upgrade Assistant as follows:

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

Select the Database we want to upgrade and choose next.

Recompile invalid objects at the end of upgrade. Keep this option as unchanged and choose next . This option automatically recompiles all the PL/SQL modules after the upgrade.

Leave the default setting Do Not Move Database Files as Part of Upgrade and choose next.

• Do not select Specify Fast Recovery Area.
• If you opened the DBUA with the Start menu, in the Diagnostic Destination field you have to specify the location of the saptrace directory using the Browse button.

Deselect Configure the Database with Enterprise Manager, if selected, and choose next.

Summary screen: Review the summary screen and make changes if needed

• This includes the warnings that you ignored or did not fix, components to be upgraded, parameters to be added, removed, and changed after the upgrade.
• Choose Back to make any final adjustments, if required. Otherwise, leave the default settings and choose Finish to start the upgrade.
• We can ignore the warning ORA-32004 obsolete or deprecated parameter(s) specified for RDBMS instance.
• The upgrade process itself runs for some time, depending mainly on the size of the database and the capacity of the hardware.

When the DBUA has completed successfully, the results of the upgrade are displayed (Click attached OracleUpgradeResult document below). Review the changes made and close the DBUA.

POST UPGRADE:

Step 18: Additional Steps When Starting the DBUA from the Start Menu

If you started the DBUA from the Start menu, you have to check the upgraded database and perform the post-upgrade script manually.

• To do this login to the database server and connect with sysdba .
• Go to the directory where we keep the post upgrade scripts.
• Run all the necessary post upgrade scripts manually as same as pre upgrade scripts.

Step 19: Post-Upgrade  Steps

1. set the user environment variables :

• Login in to the server with <SID>adm user

• Right click my computer and check the environment variable and path variable.

• if required, remove all parts referring to the old Oracle version

• In the user environment of <DBSID>adm, set the ORACLE_HOME variable to the correct value for Oracle

• Copy tnsnames.ora and sqlnet.ora from %ORACLE_HOME%\network\admin to \\<sapglobalhost>\sapmnt\<DBSID>\SYS\profile\oracle.

• Set the user environment variable TNS_ADMIN to point to the directory \\<sapglobalhost>\sapmnt\<DBSID>\SYS\profile\oracle.

• Log off and log on again to activate the environment changes .

1. create the Oracle TNSListener service :

• Go to the services.msc
• Stop the old Oracle listener before we create the Oracle TNSListener Service.
• Open a command prompt and enter the following command:

<complete_path_to_new_Oracle_Home>\bin\lsnrctl start

1. Changing the Registry Value of Oracle Autostart :

• Open the command prompt and go the directory   <new_Oracle_Home>\bin\
• Execute the command oradim –edit –sid <dbsid> -startmode manual
• Ignore the error message: Unable to start service, OS Error 1056.

NOTE: If the above command is not worked then we can set the parameter value via regedit. In this document we set the parameter via regedit in CD7. i.e

• Go to run.
• Type regedit
• Go to the path as shown in the below screenshot and set the ORA-CD7_AUTOSTART parameter to false.

1. Checking the Start Up Type of the Oracle Services :

• Log in to the server with sidadm
• Go to services.msc
• Check the oracle services. If not started then start the following oracle services.

  Ø  OracleService<DBSID> 

• Oracle<ORACLE_HOME_NAME>TNSListener

1. Updating the SAP Executables and BR Tools:

First update the SAP executables (kernel upgrade) and then update the BR tools.

Procedure to upgrade the SAP Executables:

Follow the kernel upgrade standard procedure.Here  BR tools are also gets updated.

1. update the SAPDBA role

Update BR*Tools to the latest release with the latest patch level. After extracting the latest BR*Tools release, we  need to update the SAPDBA role by executing the script sapdba_role.sql. This is essential so that the new release of BR*Tools works correctly. The script is automatically extracted when you extract BR*Tools.

Step to Follow:

• Go to %ORACLE_HOME% \Database directory.
• Copy the sapdba_role.sql from the SAPHOME\usr\sap\sid\sys\exe\uc\NT directory to the %ORACLE_HOME%\database directory as shown in the screenshot

Now log in SQL plus as follows:

• Go to cmd
• Login as sqlplus/nolog
• SQL> Connect / as sysdba
• Execute the following SQL commands:

  sqlplus /nolog  @sapdba_role <SAPSR3> 

sqlplus /nolog @sapdba_role<SAPSR3DB>

Run the update statistics commands:

Gather Oracle optimizer statistics by executing the following commands

• brconnect -u / -c -f stats -t system_stats
• brconnect -u / -c -f stats -t oradict_stats

Update the Oracle database statistics:

• brconnect -c -u / -f stats -t all –f collect -p 4

Run the Post upgrade scripts:

Go to the directory where we keeping the post upgrade scripts.

• Go to cmd
• Login as sqlplus /nolog
• Sql>connect /as sysdba
• Execute the command as follows
• SQL>@post upgrade_status.sql

After the post upgrade perform full database backup