SAP oracle upgrade 10G to 11G on AIX


Summary:-

This document contains the steps of  SAP oracle upgrade 10G to 11G on AIX (Operating system) using oracle DBUA menthod.

Author: Brindavan Mookaiah

Designation: SAP BASIS Consultant

Pre-requisite steps


Backup


  1) Before starting the ORACLE Upgrade 11g please take the backup of ORACLE_HOME (102_64) and oraInventory folder.

  2) /oracle/SID/102_64 — Please take backup of entire 102_64 folder to some secured location.


  3) /oracle/SID/oraInventory — Please take the backup of entire oraInventory folder to some secured location.

   4)  Before starting the upgrade make sure you have run a FULL ONLINE backup of the database

Filesystem and Tablespace

      1) Check  the for ‘CIO’ option.

      2) File system need space “/oracle/stage” — Needs to be mounted with enough space (around 15GB)

      3) Filesystem need space” oracle/<SID>/11203” —  Needs to be mounted with enough space (around 12GB)

      4) If you are mounting the ORACLE_HOME filesystem with /oracle/<SID>/11203 then please create a softlink of /oracle/<SID>/112_64 linking to            /oracle/<SID>/11203  with ora<sid>as follows: ln –s  /oracle/<SID>/11203  112_64

  4) Export TEMP_DIR to a filesystem which has enough space (around 5GB).

  5) Extend  SYSTEM TABLESPACE & SYSAUX TABLESPACE (Mandatory) Enough space of around 2 GB in SYSAUX and  around 3GB in SYSTEM TABLESPACE)

Filesystem

Steps 1

Stop SAP appliaction

Capture71.JPG

Login into OS level with root or oraSID user.

Check  the for ‘CIO’ option in all filesystem started with “/oracle”.

Capture.JPG

Capture1.JPG

All the above filesystem’s “options” should be WITHOUTCIO” option. If it’s with CIO option than the upgrade to oracle 11g or the oracle patch set upgrade will fail at the time of relinking of the binaries phase. This is only specific to IBM AIX systems.

Check the oracle environment variable for oracle. use Echo command like


ORASID> echo $ ORACLE_HOME

ORASID> echo $ ORACLE_BASE

ORASID> echo $ ORACLE_SID



Now change the environment variable like below command  with orasid user.


Execute the below command


Export  ORACLE_HOME_SRC & ORACLE_HOME_TGT as below

export ORACLE_HOME_SRC=/oracle/SID/102_64

export ORACLE_HOME_TGT=/oracle/SID/11203

export  TEMP=/oracle/stage/11203/tmp


In AIX we need to run the “rootpre.sh” script as shown below before running the actual upgrade and should fix the possible error if any as per the logs.


Capture2.JPG

You can find the rootpre.sh script in the extracted oracle software folder (“/11203/database”)


Captur3e.JPG

We have added the user “oraSID” into the group “hagsuser” and again run the script.

Below screen shows the group subsystems service is not active. We have to ask Unix guy to start the services.


Capture4.JPG

Now run the ./RUNINSTALLER from ora<sid> user as shown in below screen. From the location /oracle/stage/11203/database/SAP



Capture5.JPG

Capture6.JPG

Capture7.JPG

Click Next

Capture8.JPG

Click Yes

Capture9.JPG

Select Skip option and then click Next

Capture.10JPG.JPG

Select option Upgrade an existing database and click Next

Capture11.JPG

Click Next

Capture12.JPG

Select Enterprsie Edition and then click Next

Capture13.JPG

Click Next

Capture14.JPG

Click on YES and continue.


Capture15.JPG

Keep the defaults and Click Next

Capture15.JPG

Capture16.JPG

Click on “Fix and Check again” to fix the Warnings.


Capture.17JPG.JPG

Capture.18JPG.JPG

After click on OK button the prerequisite check will run again.

We had the below check which failed in 2nd run.


Capture18.JPG

Capture19.JPG

All these are “Warning” we decided to continue with this by selecting Ignore all button as shown below.


Capture20.JPG

Click Next

Capture.21JPG.JPG

Capture22.JPG

Click Install

Capture23.JPG

Login into OS level with root user and run the “root.sh”

After running the root.sh script as shown from the above location click on the OK button on the above screen.

Before executing the root.sh script please take a copy of all the three files (dbhome, oraenv, coraenv)

cp -pr  dbhome  dbhome.original

cp -pr  oraenv  oraenv.original

cp -pr  coraenv  coraenv.original

Capture24.JPG

Capture25.JPG

Then continue from the point of stop,click on ok

Capture26.JPG

select” Perform typical configuration” and click Next

Capture27.JPG

Capture28.JPG

Before pressing Next please check below:

Running the Pre-Upgrade Scripts

You run the Oracle pre-upgrade scripts, which automatically perform various tasks and checks on your database. See SAP Note 1431793.

This section only applies if your source database is Oracle 10.2.0.4, 10.2.0.5, or 11.2.0.1. The scripts do the following on your pre-upgrade database:

Provide information on the database

Suggest any changes that you need to make to the database before you start the upgrade

Perform some of the pre-upgrade tasks on the database

NOTE

You always execute these scripts as the ora<dbsid> user connected to the database as sysdba in the Oracle 10.2 environment.

Prerequisites

You are logged on as user ora<dbsid> in the Oracle 10.2 environment with the database also started up.

The directory where the upgrade scripts are installed is called ora_upgrade.

By default, the scripts are installed as part of the SAP bundle patch in the relevant Oracle 11.2 software home directory:

64-bit: /oracle/<DBSID>/112_64/sap/ora_upgrade

32-bit: /oracle/<DBSID>/112_32/sap/ora_upgrade

OR

You need to download it from the latest SAP bundle patch on service market place.

Procedure

Change to the correct directory by entering the relevant command (since the scripts have been extracted to the Oracle 11.2 software home):

64-bit: cd /oracle/<DBSID>/112_64/sap/ora_upgrade/pre_upgrade

32-bit: cd /oracle/<DBSID>/112_32/sap/ora_upgrade/pre_upgrade

Execute the following commands to check the pre-upgrade status of your database:

sqlplus / as sysdba

SQL> @pre_upgrade_status.sql

Execute the following commands to perform pre-upgrade tasks, which can improve the performance of the upgrade process:

sqlplus / as sysdba

SQL> spool pre_upgrade_tasks.log

SQL> @pre_upgrade_tasks.sql

SQL> spool off

Result

You can check the results of the pre-upgrade scripts by looking at the following log files:

pre_upgrade_status.log

pre_upgrade_tasks.log

You can run the pre-upgrade scripts as often as you like on the pre-upgrade Oracle 10.2 database.

CAUTION

Make the changes listed in the log file, otherwise you might have problems during the upgrade. However, you must ignore the warning regarding the database parameter remote_os_authent, which is reported as DEPRECATED.

Do not touch this parameter. The post-upgrade script sets it to TRUE again after the database upgrade. This parameter must be set to TRUE for the SAP logon mechanism to work correctly after the database upgrade.


IF YOU CANNOT FIND SAP FOLDER IN ORACLE_HOME FOR THE PRE-UPGRADE & POST-UPGRADE SCRIPTS THEN PLEASE REFER NOTE: Note 1431793 – Oracle 11.2.0: Upgrade Scripts

As of 2012-Jun-22 the scripts are included in the patch 9584028 updated for release 11.2.0.3.

You can find the above patch 9584028 in the current SAP Bundle Patch file located  in service market place http://www.service.sap.com

Download the above latest SAP Bundle Patch on your local machine extract on your server host


Loginto into oslevel with orasid and login to sql


Capture29.JPG


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

  Pre-Upgrade-Scripts

  create_restore_point.sql  —  (Not required to do this step)

This script lets you create a guaranteed restore point before the upgrade.

Creating a guaranteed restore point enables you to flashback the database. This feature is useful especially during upgrade testing to quickly reset the database. For more information about FLASHBACK DATABASE, see SAP note 966117.

The use of this script is optional.

pre_upgrade_checks.sql

This script contains database pre-upgrade checks.

Run this script before the upgrade.

  pre_upgrade_status.sql

This script performs pre-upgrade checks, the result is written into a log file.

Run this script before the upgrade.

  pre_upgrade_tasks.sql

This script contains database pre-upgrade tasks like recompile invalid objects or gather Oracle Dictionary Statistics to reduce the number of warnings in the pre-upgrade check and to improve upgrade performance (minimize upgrade time).

Run this script before the upgrade.

  utlu112i.sql

Oracle Pre-Upgrade Information Utility

(My Oracle Support article 884522.1)

  utlu112i_sap.sql

Oracle Pre-Upgrade Information Utility for SAP

This script is run when running pre_upgrade_checks.sql or pre_upgrade_status.sql.

Once the script are completed then go back the DBUA and then click next

Capture31.JPG

Capture32.JPG

Click Yes

Capture32.JPG

Select “Recompile invalid objects at the end of upgrade” & “Upgrade Timezone Version and TIMESTAMP WITH TIME ZONE Data”. Click NEXT


Capture33.JPG

Click Yes

Capture34.JPG

Click Next

Capture35.JPG

Click Next

Capture36.JPG

Unselect the above “Configure the Database with Enterprise Manager” option


Capture41.JPG

Capture42.JPG

Capture43.JPG

KEEP AN EYE ON ORAARCH DIRECTORY AFTER YOU PRESS FINISH (DURING STARING OF THE ORACLE UPGRADE PROCESS)

Capture44.JPG

Capture46.JPG

Click ok

Capture47.JPG

Click Close

Capture48.JPG

Capture49.JPG

Click close

From Oracle 11g the Runtime Oracle_Home should point to /oracle/<SID>/112_64 and the Installation Oracle_Home should point to /oracle/<SID>/11203



Please change the below profile parameters so that it points to 112_64

Change from 102_64 to 112_64

grep -R 102_64 /oracle/CS8/ —  to search for 102_64 in all the profiles

grep -R 102_64 /home/cs8adm —  to search for 102_64 in all the profiles

Command to change in profile parameter files

:1,$s/102_64/112_64/g


Capture50.JPG

Capture50.JPG

You might need to change on below parameters as well based on the list which is present.


After the above changes ORACLE_HOME is pointing to /oracle/<SID>/112_64. So export the oracle home to /oracle/<SID>/11203 and shutdown the database because the database is still running with 11203 installation home.


oracSID> export ORACLE_HOME=/oracle/SID/11203


oracSID> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 3 12:29:56 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>



Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

oract2> lsnrctl stop

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 – Production on 14-OCT-2012 14:32:11

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

The command completed successfully

Now login with a new console and start the listener and database which should start from ORACLE_HOME as /oracle/<SID>/112_64.

SQL> startup;

ORACLE instance started.

Total System Global Area 3858374656 bytes

Fixed Size 2227256 bytes

Variable Size 1929380808 bytes

Database Buffers 1895825408 bytes

Redo Buffers 30941184 bytes

Database mounted.

Database opened.

Please refere the below before  starting the SAP and run the post-upgrade Scripits

Note 1431793 – Oracle 11.2.0: Upgrade Scripts

SQLPLUS> SHUTDOWN IMMEDIATE;

SQLPLUS> STARTUP;

Copy the post-upgrade scripts to some location and execute it one by one on SQLPLUS level.

–>  Post-Upgrade-Scripts

  –> post_upgrade_checks.sql

This script contains database post-upgrade checks.

Run this script on the 11.2 database after the upgrade.

  –> post_upgrade_status.sql

This script runs database post-upgrade checks and automatically logs the result in an log file.

–> post_upgrade_tasks.sql

This script contains database post-upgrade tasks.

Run this script on the 11.2 database after the upgrade.

–>set_compatible.sql

This script sets parameter COMPATIBLE to ‘11.2.0’.

Run this script on the 11.2 database after the upgrade.

–> set_remote_os_authent.sql

This script sets parameter remote_os_authent=TRUE.

(for SAP installations with ABAP stack on Unix platforms)

  –>.sapuprof_profile.sql

Script to create and configure SAPUPROF (SAP note 1519872)

  –> upgrade_TSTZ.sql

With this script you upgrade timestamp with timezone (TSTZ) data in release 11.2.0.1.

Starting with release 11.2.0.2 this task is integrated in the Database Upgrade Assistant (DBUA) and enabled by default in the DBUA wrapper scripts (dbua.sap.sh, dbua.sap.ps1).

  dbua.sap.sh  —  (Not required to run this script)

Wrapper script for DBUA for Unix/Linux platforms

  dbua.sap.ps1  —  (Not required to run this script)

Wrapper script for DBUA for Windows platforms

  check_dbua_logs.sh  —  (Not required to run this script)

Check script for DBUA logs for Unix/Linux platforms

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

APPLYING SBP

With ORACS8 user

Make a copy of the original MOPatch OPatch

Capture52.JPG

Copy the MOPatch and OPatch folder from the SBP folder to /oracle/SID/112_64


Capture54.JPG

export PATH=$PATH:/oracle/SID/112_64/OPatch

Capture55.JPG

Copy mopatch.sh from /oracle/SID/112_64/MOPatch to /oracle/SID/112_64



You have to zip the SBP folder as the mopatch.sh only recognizes the zip folder of SBP in order to extract the required patches from it and install it into ORACLE_HOME.


Capture57.JPG

Installing the SAP Bundle Patch

Follow the steps below.

  1. 1.       Stop all database applications, in particular all SAP applications.
  2. 2.       Shut down the database instances, the listeners, and other processes associated with the Oracle Home.
  3. 3.       After having shut down all database processes as described in the previous step, use the fuser OS command to check for stale SQL*Plus sessions

   $SBPFUSER $OHRDBMS/bin/oracle

If that command reports running processes, these are stale SQL*Plus sessions, which you can safely terminate with the kill command

  1. 4.       For IBM AIX on POWER Systems only:

Run the slibclean command under /usr/sbin as super user:

#temporarily change to super user

      su root -c slibclean

SQLPLUS> SHUTDOWN IMMEDIATE;

TEST MODE:

/bin/sh $ORACLE_HOME/mopatch.sh -v -d -s /oracle/stage/11203/ORACLE_11_2_0_3_Upgrade_Media/SAP_112031_201203.zip

ACTUAL SBP RUN COMMAND:

/bin/sh /oracle/SID/112_64/mopatch.sh -v -s

/dbexport_ecc/Oracle11g_media_AIX/ SAP_112033_201209_AIX.zip



Capture58.JPG

Capture59.JPG

Run

opatch lsinventory

from the path /oracle/SID/11203/OPatch to check whether patches are applied or not.

POST STEPS AFTER APPLYING SBP

The post steps described here are based on SBP level SAP Bundle Patch 11.2.0.3.2 – 201206

Check the generated MOPatch report for errors or unresolved conflicts. To resolve them, refer to section “Resolving Patch Conflicts and Errors” of SAP Note 1027012 MOPatch – Install Multiple Oracle Patches in One Run.

Start up the database instances, the listeners, and other processes associated with the Oracle Home.

For each database instance running from the Oracle home*, connect to the database instance as SYSDBA using SQL*Plus and run the post-installation script catsbp.sql:

  $ cd /oracle/SID/11203/rdbms/admin

  $ sqlplus / as sysdba

  SQL> @/oracle/SID/11203/sapbundle/SBP_112032_201206/catsbp.sql

  SQL> quit



Executing Post-Installation Instructions

Follow the instructions listed below after installation of this SAP Bundle Patch. Except when otherwise stated, execute the instructions after starting the database instances.

Patch 13943375 (CBO Merge Patch), added in SBP 11.2.0.3.2 201205

Please use the following statements to adjust your _fix_control and event

settings in your spfile according to SAP note 1431798 with the new SBP:

ALTER SYSTEM SET “_FIX_CONTROL”=

‘5099019:ON’,’5705630:ON’,’6055658:OFF’,’6399597:ON’,’6430500:ON’,’6440977:ON’,

‘6626018:ON’,’6972291:ON’,’8937971:ON’,’9196440:ON’,’9495669:ON’,’13077335:ON’, ‘13627489:ON’,’13077335:ON’

COMMENT=’SAP_112031_201202 RECOMMENDED SETTINGS’

SCOPE=SPFILE;

ALTER SYSTEM SET EVENT=

‘10027’,

‘10028’,

‘10142’,

‘10183’,

‘10191’,

‘10995 level 2’,

‘38068 level 100’,

‘38085’,

‘38087’,

‘44951 level 1024’,

‘31991’

COMMENT=’SAP_112030_201112 RECOMMENDED SETTINGS’

SCOPE=SPFILE;

Please restart the database after you have adjusted these parameters.

Patch 13575265, added in SBP 11.2.0.3.1 201203

For Database Vault systems, if Database Vault is already installed,

run the following script (for RAC systems, run the script on one of the nodes):

sqlplus / as sysdba                                                    

spool patch_13575265.log                                          

@/oracle/SID/11203/rdbms/admin/prvtlbac.plb                                       

spool off                                                         

exit  

The following error message can be ignored:

‘ORA-00955: name is already used by an existing object’

Execute utlrp

  @/oracle/SID/11203/rdbms/admin/utlrp.sql.

Now all of the above steps are done hence shutdown and startup the database

Just to add in the end and to avoid problem if occurred regarding listener.

Please do the following to avoid any listener pointing problems for oracle 11g database.

Edit the Listener.ora file

The listerner.ora file contains server side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the server.

e.g.: /oracle/SID/11203/network/admin/listener.ora

Add the below extra lines in listener.ora file

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = SID.WORLD)

      (ORACLE_HOME = /oracle/SID/11203)

      (SID_NAME = SID)

    )

  )

——————————————————————————————————————————–

Now do the below to avoid ORACLE error “ORA-28002: the password will expire within string days”

On SQLPLUS level please perform the below steps to set “PASSWORD_LIFE_TIME” from “180” to “UNLIMITED”

SQL> select resource_name,resource_type, limit from dba_profiles where profile=’DEFAULT’;

The above query will give the output something like below:

RESOURCE_NAME                    RESOURCE LIMIT

——————————– ——– ———-

COMPOSITE_LIMIT                  KERNEL   UNLIMITED

SESSIONS_PER_USER                KERNEL   UNLIMITED

CPU_PER_SESSION                  KERNEL   UNLIMITED

CPU_PER_CALL                     KERNEL   UNLIMITED

LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED

LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED

IDLE_TIME                        KERNEL   UNLIMITED

CONNECT_TIME                     KERNEL   UNLIMITED

PRIVATE_SGA                      KERNEL   UNLIMITED

FAILED_LOGIN_ATTEMPTS            PASSWORD 10

PASSWORD_LIFE_TIME               PASSWORD 180

RESOURCE_NAME                    RESOURCE LIMIT

——————————– ——– ———-

PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

PASSWORD_LOCK_TIME               PASSWORD UNLIMITED

PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

Now fire the below SQL command to set parameter “PASSWORD_LIFE_TIME” TO “UNLIMITED”

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

SQL> commit;


PLEASE REFER THE BELOW SAP NOTE AFTER THE UPGRADE WHICH IS IMPORTANT IN TERMS OF DATABASE PARAMETERS AND SETTING THE COMPATIBLE MODE FOR ORACLE IN SPFILE.

Note 1431798 – Oracle 11.2.0: Database Parameter Settings

GO TO DB17 AND CHANGE THE CHECK DB CONDITION FROM 10.2.0 TO 11.2.0 TO AVOID ERROR FOR COMPATIBILITY FOR 11.2.0 IN CHECKDB

Login into SAP level and go t-code DB17

Capture61.JPG

Click on Edit

Capture62.JPG


Change the “Check Condition” from “10.2.0” to “11.2.0” as shown below and then save it


Capture63.JPG

All steps are completed.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply