sapnote_0001431793

08.08.2011 of 12

SAP Note 1431793 - Oracle 11.2.0: Upgrade Scripts

Note Language: English Version: 31 Validity: Valid Since 05.07.2011

Summary

Symptom

This note describes the use of additional Shell- and SQL-scripts to performan Oracle database upgrade to release 11.2.0.X with the Database UpgradeAssistant (DBUA).

What's new

2011-05-09

o New version of upgrade scripts for release 11.2.0.2 available.Available as generic patch p9584028_112020_Generic.zip and includedin SAP Bundle Patch May 2011.Changes/Modifications:

- upgrade hanging during custom scripts fixed

- dbua.sap.ps1/dbua.sap.sh: general improvements

Other terms

p9584028_102040_Generic.zipp9584028_102050_Generic.zipp9584028_112010_Generic.zipp9584028_112020_Generic.zip

Reason and Prerequisites

Database Upgrade to Oracle Database Release 11.2.0.X

The scripts described in this note are used to automate pre- andpost-upgrade tasks and to simplify the database upgrade process of yourSAP/Oracle database to release 11.2.0.X.

Prerequisites

Added 1. April 2011If in the past the database views SAP_USER_INDEXES, SAP_USER_IND_COLUMNSand corresponding synonyms have been created and still exist in yourdatabase, you need to drop them before you upgrade to release 11.2 (checkSAP note 519448). This clean up task is included in the current version ofupgrade scripts for release 11.2.0.2 (available April 2011).

Solution

08.08.2011 of 12


Table of Contents

1. Overview2. Prerequisites3. Installing Upgrade Scripts4. Running Pre-Upgrade Scripts5. DBUA Wrapper6. Running Post-Upgrade Scripts7. Troubleshooting

AppendixA DBUA Wrapper ScriptB Upgrade Scripts Version history

1. Overview

The following scripts are provided for the database upgrade of anSAP/Oracle database:

o Pre-Upgrade-Scripts

- create_restore_point.sqlThis script lets you create a guaranteed restore point beforethe upgrade.Creating a guaranteed restore point enables you to flashbackthe database. This feature is useful especially during upgradetesting to quickly reset the database. For more informationabout FLASHBACK DATABASE, see SAP note 966117.

- pre_upgrade_checks.sqlThis script contains database pre-upgrade checks.Run this script on the 10.2 database before the upgrade.

- pre_upgrade_status.sqlThis script runs database pre-upgrade checks and automaticallylogs the result in a log file.Run this script on the 10.2 database before the upgrade,

- pre_upgrade_tasks.sqlThis script contains database pre-upgrade tasks to minimize thenumber of warnings in the pre-upgrade check and to improve theperformance of the upgrade like:- Recompile invalid objects- Gathering Oracle Dictionary StatisticsRun this script on the 10.2 database before the upgrade.

- utlu112i.sqlOracle Pre-Upgrade Information Utility(My Oracle Support article 884522.1)

- utlu112i_sap.sqlOracle Pre-Upgrade Information Utility for SAPThis script is run when running pre_upgrade_checks.sql orpre_upgrade_status.sql.

08.08.2011 of 12


o Post-Upgrade-Scripts

- post_upgrade_checks.sqlThis script contains database post-upgrade checks.Run this script on the 11.2 database after the upgrade.

- post_upgrade_status.sqlThis script runs database post-upgrade checks and automaticallylogs the result in an log file.

- post_upgrade_tasks.sqlThis script contains database post-upgrade tasks.Run this script on the 11.2 database after the upgrade.

- set_compatible.sqlThis script sets parameter COMPATIBLE to '11.2.0'.Run this script on the 11.2 database after the upgrade.

- set_remote_os_authent.sqlThis script sets parameter remote_os_authent=TRUE.(for SAP installations with ABAP stack on Unix platforms)

- sapuprof_profile.sqlScript to create and configure SAPUPROF (SAP note 1519872)

- upgrade_TSTZ.sqlThis script performs the upgrade of the timestamp with timezone(TSTZ) data. Starting release 11.2.0.2 the upgrade of TSTZ datacan be performed by DBUA.

- dbua.sap.shWrapper script for DBUA for Unix/Linux platforms

- dbua.sap.ps1Wrapper script for DBUA for Windows platforms

- check_dbua_logs.shCheck script for DBUA logs for Unix/Linux platforms

The post-upgrade SQL scripts can be used for manual or DBUA upgrade.

2. Prerequisites

The following upgrade scenarios are supported:

o Major Release UpgradeOracle database source release(s): 10.2.0.4, 10.2.0.5Oracle database target release(s): 11.2.0.X

o Patch Set Release UpgradeOracle database source release(s): 11.2.0.1Oracle database target release(s): 11.2.0.X

08.08.2011 of 12


3. Installing Upgrade Scripts

The upgrade scripts are delivered as SAP-specific generic Oracle databasepatch (patch 9584028).

o For Unix/Linux platforms:The current version of the upgrade scripts is automaticallyinstalled if the current SAP Bundle Patch is installed. No furtherinstallation is required.

To install a newer version with mopatch, download the patch and runthe following command:

OS> $ORACLE_HOME/MOPatch/mopatch.sh -s \ p9584028_112020_Generic.zip

o For Windows platforms:To install the upgrade scripts patch 9584028, download the patchfile and run the following commands (see SAP note 1503709):OS> unzip p9584028_112020_Generic.zipOS> cd 9584028OS> $ORACLE_HOME/OPatch/opatch apply

For information how to install a patch with OPatch see note 839182.

Note:

o SAP-specific database patches are installed in the Oracle Home insubdirectory '<ORACLE_HOME>/sap'.

o The upgrade scripts are installed in the Oracle Home in directory'<ORACLE_HOME>/sap/ora_upgrade'.

The directory '<ORACLE_HOME>/sap/ora_upgrade' is denoted by <ora_upgrade>within the context of this note.

4. Running the Pre-Upgrade Scripts

Running the pre-upgrade scripts is not integrated into the DBUA. They canonly be run manually outside of DBUA.

1. Log on to the database server as the Oracle database administratoruser ('oracle' or 'ora<dbsid>') and change the current directory tothe location of the pre-upgrade scripts.

OS> cd <ora_upgrade>/pre_upgrade

08.08.2011 of 12


2. To check the pre-upgrade status, run the following command:

OS> sqlplus / as sysdba @pre_upgrade_status.sql

Both pre-upgrade information tools utlu112i.sql (Oracle-generic) andutlu112i_sap.sql (SAP-specific) are run in this step.

The result is logged in 'pre_upgrade_status.log'.

To run the pre-upgrade information scripts one by one:

a) Running the generic pre-upgrade information utility utlu112i.sql:SQL> spool pre_upgrade_info.logSQL> @utlu112i.sqlSQL> spool off

b) Running the SAP-specific pre-upgrade checks:SQL> spool pre_upgrade_checks.logSQL> @pre_upgrade_checks.sqlSQL> spool off

3. Running pre-upgrade-tasks is recommended to improve the performance ofthe upgrade process:

OS> sqlplus / as sysdbaSQL> spool pre_upgrade_tasks.logSQL> @pre_upgrade_tasks.sqlSQL> spool off

Note:

o For manual database upgrades, running the script utlu112i.sql atleast once is mandatory.

o The latest version of the pre-upgrade information scriptutlu112i.sql is available via My Oracle Support note 884522.1.

5. DBUA wrapper scripts

DBUA wrapper scripts provide the following benefits:

o The database upgrade is based on DBUA.

o easy to use

o Support of Windows and Unix platforms

o environment check included

o standard post-ugprade tasks included

08.08.2011 of 12


Prerequisites:

o Oracle Database Software Release 11.2 is installed (incl. requiredpatches).

For Unix platforms, see SAP note 1524205.

o The current version of the upgrade scripts is installed.

Using the DBUA Wrapper on Unix

The dbua wrapper script dbua.sap.sh is used on Unix/Linux platforms to runthe DBUA with the correct command line options.

1. Logon to the database server as the Oracle database administrator user('oracle' or 'ora<dbsid>') and change the current directory to thelocation of the post-upgrade scripts.

OS> cd <ora_upgrade>/post_upgrade

2. Set required environment variables.

a) For non-silent database upgrade with DBUA, ensure that environmentvariable DISPLAY is set.OS> env | grep DISPLAYOS> xclock &

3. Specify source and target Oracle homes.

a) ORACLE_HOME_SRC (mandatory)<ORACLE_HOME_SRC> specifies the location of the source Oracle home.Example for release 10.2:OS> setenv ORACLE_HOME_SRC /oracle/C11/102_64

Example for release 11.2.0.1:OS> setenv ORACLE_HOME_SRC /oracle/C11/112_64

b) ORACLE_HOME_TGT (mandatory)<ORACLE_HOME_TGT> specifies the installation location (!) of thesource Oracle home. For information about the differences betweenthe 'installation location' and the 'run-time location' see SAPnote 1524205.

Example for release 11.2.0.2:OS> setenv ORACLE_HOME_TGT /oracle/C11/11202

4. Run the wrapper script in query mode.

OS> ./dbua.sap.sh -q

08.08.2011 of 12


The following tasks are performed in query mode:

- environment is checked

- Certain parameter files (i.e. init<DBSID>.sap, listener.ora)are copied from the source Oracle Home to the target OracleHome.

- DBUA is not started!!

5. If the configuration is fine, go to the next step. If you need tochange the configuration, see 'dbua.sap.sh -help' for availableoptions of the wrapper script.

6. Upgrade the database

Now the database will be upgraded with DBUA to the new databaserelease. After DBUA has finished, the instance is be stopped.

- In non-silent mode:OS> ./dbua.sap.sh

On each screen choose 'Next' to go to the next page. On thesummary screen, review the summary of changes and start thedatabase upgrade by choosing 'Finish'.

- In silent mode:OS> ./dbua.sap.sh -silent

7. Verify the DBUA logs for unexpected ORA-messages.OS> ./check_dbua_logs.sh

ORA-32004/ORA-32006 are expected and can be safely ignored.

8. Configure the run-time environment

See 'Step 3: Post-Upgrade Steps' in SAP note 1524205.

9. Start the instance from the run-time environment.

10. To check the post-upgrade status, run the following command:

OS> sqlplus / as sysdba @post_upgrade_status.sql

11. Perform Post-Upgrade TasksThere might be additional post-upgrade tasks that need to be performedwhich are documented in the SAP Database Upgrade Guide 'Upgrade toOracle Database 11g Release 2 (11.2): UNIX, Based on Oracle Patch SetRelease 11.2.0.2'.

Using the DBUA wrapper on Windows

The dbua wrapper script dbua.sap.ps1 is used on Windows platforms to runthe DBUA with the correct command line options.

08.08.2011 of 12


1. Open a Powershell in elevated mode (run as administrator) and changethe working directory to <ora_upgrade>\post_upgrade.

2. From this Powershell start the Database Upgrade Assistant with thewrapper script.

PS> dbua.sap.ps1

3. On each screen choose 'Next' to go to the next page. On the summaryscreen, review the summary of changes and start the database upgradeby choosing 'Finish'.

4. When finished, review the DBUA upgrade results and close DBUA.

6. Running Post-Upgrade Scripts

If necessary, run the post-upgrade scripts after the upgrade.Change the directory to '<ora_upgrade>/post_upgrade' and run the scriptsfrom SQL*Plus as SYSDBA:

1. Post-upgrade tasksSQL> spool post_upgrade_tasks.logSQL> @post_upgrade_tasks.sqlSQL> spool off

2. Upgrade of timezone file versionSQL> spool post_upgrade_tz_upgrade.logSQL> @upgrade_tstz.sqlSQL> spool off

3. Set database parametersSQL> @set_remote_os_authent.sql (Unix/Linux only)SQL> @set_compatible.sql (Unix + Windows)

4. Perform post-upgrade checksCheck whether the database is configured correctly.

SQL> @post_upgrade_status.sql

The result of this check is logged in 'post_upgrade_status.log'.

7. Troubleshooting the Database Upgrade

For solutions and workarounds of known upgrade issues, see SAP note1431797.

If DBUA seems to hang during the upgrade, check whether there is a DBUAwarning message that has come up in a window behind the 'Database UpgradeAssistant: Progress' window.

08.08.2011 of 12


During the 'Running Custom Scripts' phase you can safely ignore ORA-32004and ORA-32006 messages and continue with the upgrade. The messages can beaccessed later from the DBUA upgrade summary.

After the upgrade and on Unix platforms only you can check the log files ofthe last DBUA run for error messages that have occurred during the upgradeusing the check script check_dbua_logs.sh.OS> cd <ora_upgrade>/post_upgradeOS> /bin/sh check_dbua_logs.sh

If a detailled analysis of upgrade issues is needed, the following logfiles of DBUA provide relevant information:

o $ORACLE_BASE/cfgtoollogs/dbua/$DB_SIDFor each upgrade run there is a separate directory 'upgrade<n>'created containing the logs and traces of this run.

Examples of log files are: upgrade.xml, Upgrade_Directive.log,SpaceUsage.txt, PreUpgradeResults.html, PreUpgrade.log,Oracle_Server.log, PostUpgrade.log, UpgradeTimezone.log,CustomScript.log, UpgradeResults.html, trace.log, sqls.log)

o $ORACLE_BASE/cfgtoollogs/dbua/logsIn case of a silent upgrade, this directory contains the log file'silent.log'.

o Log file of dbua.sap.sh11gupgrade_11202.log

A DBUA Wrapper Script

Options and flags of the Windows DBUA wrapper script 'dbua.sap.ps1':None

Options and flags of the Unix DBUA wrapper script 'dbua.sap.sh':

Show DBUA wrapper script online help:OS> ./dbua.sap.sh -h|-help

Show DBUA wrapper script version information:OS> ./dbua.sap.sh -V|-Version

Run DBUA wrapper script in query mode / test mode:OS> ./dbua.sap.sh -q|-query

Upgrade database with DBUA in interactive mode:OS> ./dbua.sap.shThis is the default way to upgrade the database.

Upgrade database with DBUA in silent mode:OS> ./dbua.sap.sh -silent

Skip SAP specific post-upgrade scripts:OS> ./dbua.sap.sh -no_scripts

Skip changing parameter compatible:

08.08.2011 of 12


OS> ./dbua.sap.sh -no_compatible

Skip timezone file version upgrade in DBUA:OS> ./dbua.sap.sh -no_tz

Skip post_upgrade_checks.sql in DBUA:OS> ./dbua.sap.sh -no_puc

Skip sapuprof_profile.sql script (11.2.0.2):OS> ./dbua.sap.sh -no_sapuprof

Use upgrade_TSTZ.sql for timezone file version upgrade:OS> ./dbua.sap.sh -tz_script

B Version History

2011-04-08

o New version of upgrade scripts for release 11.2.0.2 available.The upgrade scripts are available as single database patchp9584028_112020_Generic.zip and in SAP Bundle Patch as of April2011 (see SAP note 1503709).Changes / Modifications are:

- Enhanced pre- and post-upgrade checks

- dbua.sap.sh:new environment variable ORACLE_HOME_TGT, automatic instancestop after the upgrade

- post_upgrade_tasks.sql:automatic clean up of SAP note 519448 (SAP_USER_INDEXES,SAP_USER_IND_COLUMNS), updated parameter settings

Version 11.2.0.1 Rev. 002:- included in SAP Bundle Patch 201008- Modified: script versioning- Updated utlu112i.sql version: 11.2.0.1.0 build: 003- Added: fix_control 9495669- Fixed: Check for AWR retention could result in ORA-01422- several minor changes and bug fixes

Version 2.2.1:- utlu112i.sql updated: version: 11.2.0.1.0 build: 002- shipped as 2 patches for release 10.2.0.4 and 11.2.0.1 - p9584028_102040_Generic.zip - p9584028_112010_Generic.zip

08.08.2011 of 12


Version 2.2.0:overall enhancements + corrections

Version 2.2.0 is shipped as Oracle patch 9584028. This patch is alsoincluded in the SAP Bundle Patches starting May 2010 (see SAP note1431795).

dbua.sap.sh:- new: '-puc' option to run post-upgrade-checks.sql- new: set_compatible.sql is run by default- new: init<SID>.sap and init<SID>.utl are copied to new Oracle Home- new: RMAN libobk library is copied to new Oracle Home (if it exists)- new: check for existence of TDE encryption wallet- new: listener.ora, sqlnet.ora, tnsnames.ora are copied to new OH- new: ADR_BASE_LISTENER is set in sqlnet.ora

post_upgrade_tasks.sql:- new: short syntax is used to set event parameter- new: fix_control set for bug 6972291- new: existing table PLAN_TABLE in SAP schema(s) are recreated

pre_upgrade_check.sql:- new: report of warnings

post_upgrade_check.sql:- new: report of warnings

Version 2.1.0:Added support for Windows platforms dbua.sap.ps1Added SQL script version infoAdded several pre-/post-upgrade checks

dbua.sap.sh:Added logging to dbua.sap.sh (dbua.sap.log)Added check for Oracle Net configuration filesAdded check script check_dbua_logs.sh

post_upgrade_tasks.sql:Added parameter _IN_MEMORY_UNDO=FALSEAdded check for PLAN_TABLE in SAP schema

Version 2.0.1:README updated

Version 2.0:Released for customers

Version 1.0:Initial version

Header Data

Release Status: Released for CustomerReleased on: 22.06.2011 13:58:09

08.08.2011 of 12


Master Language: EnglishPriority: Recommendations/additional infoCategory: ConsultingPrimary Component: BC-DB-ORA Oracle

The Note is release-independent

Related Notes

Number Short Text

1431800 Oracle 11.2.0: Central Technical Note

1431797 Oracle 11.2.0: Troubleshooting the Database Upgrade

974781 Oracle internal maintenance jobs

Attributes

Attribute Value

Database system ORACLE

Database system Oracle 11.2

sapnote_0001431793

Documents