ORACLE Upgrade from 10.2 to 11.2 on hp unix

ORACLE 11g Upgrade

This procedure explains upgrading ORACLE database from 10.2.0.4 or 10.2.0.5 to 11.2.0.2 or 11.2.0.3. If the source database runs Oracle 10.2.0.2, you need to first update the source database to at least patch set 10.2.0.5.

In the below example we would be upgrading oracle version from 10.2.0.5.0 to 11.2.0.2.0

Prerequisites:

Files needed for upgrade

Download oracle related software from http://service.sap.com/oracle-download
Download sap specific patches from http://service.sap.com/swdc

1.       Oracle Database software

The Oracle Database DVD contains a total of six or seven SAR files (depending on the platform), which include the various Oracle 11.2
components such as database, client, grid, gateways, and so on.

For a single-instance database upgrade, you only need to extract the database software. The database components are packed in the files OR111264.SAR and OR211264.SAR. If you only use a single instance database, these 2 SAR files alone are sufficient. In this case, you do not need to extract any other SAR files.

2.       Patch set for Oracle 11.2.0.2 ////database patches/oracle11.2.0.2/database rdbms

3.       Oracle Client

4.       Latest Opatch and Mopatch files

5.       Latest version of SAP specific installation scripts RUNINSTALLER, RUNINSTALLER_CHECK, and 11ginstall.sh from OSS Note  1524205

6.       Latest version of DBA Tools (BR* Tools)

                In summary, below are the files required for the upgrade

               

Planning:

1.       To check the original version of your database, do one of the following from the environment of the source database
Enter one of the following command

sqlplus —version

or

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

2.       Check the hardware and software requirements

3.       Review Oracle specific Kernel parameters and Shell Limits

Preparation

1.       Check if the new stage directory /oracle/stage/112_64is created. As per the standard upgrade document this directory will be named /oracle/stage/11202. But we name the directory 112_64 to be in par with the old directory 102_64. One other reason is we can use the same directory 112_64 for any other oracle 11g release also

2.       RDBMS DVD content has to be extracted to the stage directory:  Extract OR111264.SAR and OR21164.SAR files to the new stage directory: /oracle/stage/112_64

SAPCAR –xvf OR111264.SAR

SAPCAR –xvf OR211264.SAR

3.       Check the Oracle inventory directory.

4.       The Oracle inventory directory maintains the inventory of all Oracle software installed on the system. The path to the Oracle inventory directory  fileoraInst.loc. 

ORACLE 11g Upgrade

This procedure explains upgrading ORACLE database from 10.2.0.4 or 10.2.0.5 to 11.2.0.2 or 11.2.0.3. If the source database runs Oracle 10.2.0.2, you need to first update the source database to at least patch set 10.2.0.5.

In the below example we would be upgrading oracle version from 10.2.0.5.0 to 11.2.0.2.0

Prerequisites:

Files needed for upgrade

Download oracle related software from http://service.sap.com/oracle-download
Download sap specific patches from http://service.sap.com/swdc

1.       Oracle Database software

The Oracle Database DVD contains a total of six or seven SAR files (depending on the platform), which include the various Oracle 11.2 components such as database, client, grid, gateways, and so on.

For a single-instance database upgrade, you only need to extract the database software. The database components are packed in the files OR111264.SAR and OR211264.SAR. If you only use a single instance database, these 2 SAR files alone are sufficient. In this case, you do not need to extract any other SAR files.

2.       Patch set for Oracle 11.2.0.2 ////database patches/oracle11.2.0.2/database rdbms

3.       Oracle Client

4.       Latest Opatch and Mopatch files

5.       Latest version of SAP specific installation scripts RUNINSTALLER, RUNINSTALLER_CHECK, and 11ginstall.sh from OSS Note  1524205

6.       Latest version of DBA Tools (BR* Tools)

                In summary, below are the files required for the upgrade

               

Planning:

1.       To check the original version of your database, do one of the following from the environment of the source database
Enter one of the following command

sqlplus —version

or

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

2.       Check the hardware and software requirements

3.       Review Oracle specific Kernel parameters and Shell Limits

Preparation

1.       Check if the new stage directory /oracle/stage/112_64is created. As per the standard upgrade document this directory will be named /oracle/stage/11202. But we name the directory 112_64 to be in par with the old directory 102_64. One other reason is we can use the same directory 112_64 for any other oracle 11g release also

2.       RDBMS DVD content has to be extracted to the stage directory:  Extract OR111264.SAR and OR21164.SAR files to the new stage directory: /oracle/stage/112_64

SAPCAR –xvf OR111264.SAR

SAPCAR –xvf OR211264.SAR

3.       Check the Oracle inventory directory.

4.       The Oracle inventory directory maintains the inventory of all Oracle software installed on the system. The path to the Oracle inventory directory  fileoraInst.loc.

Before starting the installation of the new Oracle software on the database host, make sure that the file oraInst.loc is present in either the directory /etc or directory /var/opt/oracle and check that it has the following entries

·         inventory_loc=<location of the oracle inventory>

·         inst_group=dba

5.       Updating the Oracle Installation Scripts

In directory /orcle/stage/112_64/database, Move the SAP directory to a backup directory named SAP.SAVE

Extract the contents of 11ginstall-11202-029-complete.zip file downloaded from OSS note 1524205 to this directory

6.       Confirm if new oracle mount point /oracle/<DBSID>/112_64 is created

7.       Backup oraInventory directory

cp -pr /oracle/oraInventory /oracle/oraInventory.old

Procedure

1.       Login as ora<SID> user

2.       Check Prerequisites for OUI

Navigate to /oracle/stage/11202/database and execute

./runInstaller –executeSysPrereqs

3.       Checking Operating system requirements

Navigate to /oracle/stage/11202/database/SAP and execute

./RUNINSTALLER _CHECK

4.       We can ignore the above kernel parameter errors as the set value in the server is greater than the minimum required values.

5.       The below parameters has to be set/unset for the database software installation. This should be temporarily unset for the database software installation session only

6.       Set Oracle home to the new software location

Installation of Oracle database software

1.       Navigate to /oracle/stage/112_64/database/SAP and execute

      You can leave the email field blank

Confirm with “yes”

 

Confirm with “yes”

 

Choose ”skip software updates”

 

Choose “install database software only”

 

Choose “Single Instance database installation”

 

Select the default option “English”

 

Choose “Enterprise Edition”

 

The default software location will be /oracle/<SID>/11202

 

Change the software location to /oracle/SMD/112_64

 

Continue with the default

 

Prerequisites check is again performed and the failed items are displayed

 

Choose the “ignore all” option and click next

14.   Choose install

 

Monitor the progress

 

At this point login as root user and execute /oracle/SMD/112_64/root.sh

 

Confirm successful installation

ORACLE 11g Upgrade

This procedure explains upgrading ORACLE database from 10.2.0.4 or 10.2.0.5 to 11.2.0.2 or 11.2.0.3. If the source database runs Oracle 10.2.0.2, you need to first update the source database to at least patch set 10.2.0.5.

In the below example we would be upgrading oracle version from 10.2.0.5.0 to 11.2.0.2.0

Prerequisites:

Files needed for upgrade

Download oracle related software from http://service.sap.com/oracle-download
Download sap specific patches from http://service.sap.com/swdc

1.       Oracle Database software

The Oracle Database DVD contains a total of six or seven SAR files (depending on the platform), which include the various Oracle 11.2 components such as database, client, grid, gateways, and so on.

For a single-instance database upgrade, you only need to extract the database software. The database components are packed in the files OR111264.SAR and OR211264.SAR. If you only use a single instance database, these 2 SAR files alone are sufficient. In this case, you do not need to extract any other SAR files.

2.       Patch set for Oracle 11.2.0.2 ////database patches/oracle11.2.0.2/database rdbms

3.       Oracle Client

4.       Latest Opatch and Mopatch files

5.       Latest version of SAP specific installation scripts RUNINSTALLER, RUNINSTALLER_CHECK, and 11ginstall.sh from OSS Note  1524205

6.       Latest version of DBA Tools (BR* Tools)

                In summary, below are the files required for the upgrade

               

Planning:

1.       To check the original version of your database, do one of the following from the environment of the source database
Enter one of the following command

sqlplus —version

or

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

2.       Check the hardware and software requirements

3.       Review Oracle specific Kernel parameters and Shell Limits

Preparation

1.       Check if the new stage directory /oracle/stage/112_64is created. As per the standard upgrade document this directory will be named /oracle/stage/11202. But we name the directory 112_64 to be in par with the old directory 102_64. One other reason is we can use the same directory 112_64 for any other oracle 11g release also

2.       RDBMS DVD content has to be extracted to the stage directory:  Extract OR111264.SAR and OR21164.SAR files to the new stage directory: /oracle/stage/112_64

SAPCAR –xvf OR111264.SAR

SAPCAR –xvf OR211264.SAR

3.       Check the Oracle inventory directory.

4.       The Oracle inventory directory maintains the inventory of all Oracle software installed on the system. The path to the Oracle inventory directory  fileoraInst.loc.

Before starting the installation of the new Oracle software on the database host, make sure that the file oraInst.loc is present in either the directory /etc or directory /var/opt/oracle and check that it has the following entries

·         inventory_loc=<location of the oracle inventory>

·         inst_group=dba

5.       Updating the Oracle Installation Scripts

In directory /orcle/stage/112_64/database, Move the SAP directory to a backup directory named SAP.SAVE

Extract the contents of 11ginstall-11202-029-complete.zip file downloaded from OSS note 1524205 to this directory

6.       Confirm if new oracle mount point /oracle/<DBSID>/112_64 is created

7.       Backup oraInventory directory

cp -pr /oracle/oraInventory /oracle/oraInventory.old

Procedure

1.       Login as ora<SID> user

2.       Check Prerequisites for OUI

Navigate to /oracle/stage/11202/database and execute

./runInstaller –executeSysPrereqs

3.       Checking Operating system requirements

Navigate to /oracle/stage/11202/database/SAP and execute

./RUNINSTALLER _CHECK

4.       We can ignore the above kernel parameter errors as the set value in the server is greater than the minimum required values.

5.       The below parameters has to be set/unset for the database software installation. This should be temporarily unset for the database software installation session only

6.       Set Oracle home to the new software location

Installation of Oracle database software

1.       Navigate to /oracle/stage/112_64/database/SAP and execute

2.       You can leave the email field blank

3.       Confirm with “yes”

4.       Choose ”skip software updates”

5.       Choose “install database software only”

6.       Choose “Single Instance database installation”

7.       Select the default option “English”

8.       Choose “Enterprise Edition”

9.       The default software location will be /oracle/<SID>/11202

10.   Change the software location to /oracle/SMD/112_64

11.   Continue with the default

12.   Prerequisites check is again performed and the failed items are displayed

13.   Choose the “ignore all” option and click next

14.   Choose install

15.   Monitor the progress

16.   At this point login as root user and execute /oracle/SMD/112_64/root.sh

17.   Confirm successful installation

Installing SAP bundle Patch

You only need to install the latest SAP bundle patch because these patches are cumulative.

18.   Go through the “Readme*” file provided with the bundle patch beforehand.

19.   Copy the new version of downloaded Opatch and MoPatch files to the new home directory

20.   At previous ORACLE_HOME,check whether listener is down.

21.   Make sure that the ORACLE_HOME is set to /oracle/SMD/112_64

22.   Run MOPatch in documentation mode

/cddump/SAP_112025_201203_HPUXIA64.zip

Now Run the actual bundle patch installation using MOPatch

/cddump/SAP_112025_201203_HPUXIA64.zip

 

Below script has to be run to fix any invalid objects

This should be done with the preupgrade parameters

Run utlrp.sql script as follows

cd /oracle/SMD/102_64/rdbms/admin

sqlplus “/as sysdba”

SQL>@utlrp.sql

If this command gives error.then

run

4^th step is wrong,----You should open the database in the upgrade mode to run utlirp.sql script, once it is done shutdown the database and open it normally to run utlrp.sql script.

25.   Invalid objects if any can be found out using the below command

Select owner,object_type,object_name,status from dba_objects where status='INVALID';

Copying and Adapting the SQL*Net Files

26.   Copy “listener.ora”, “tnsnames.ora”, “sqlnet.ora” from old ORACLE_HOME to new ORACLE_HOME

27.   Add all three files to adapt to the  new directory. Be sure to adapt the value of ORACLE_HOME in the listener.ora file.

28.   Add the below line in the listener.ora file.

ADR_BASE_LISTENER = /oracle/DMC/saptrace

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

Running the Pre-Upgrade Scripts

You always execute these scripts as the ora<dbsid> user connected to the database as sysdba in the pre-upgrade environment of the database to be upgraded.

Login as orasmd user in the 10.2 environment.Start Databse

31.   Navigate to directory /oracle/DMC/112_64/sap/ora_upgrade/pre_upgrade

32.   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

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

sqlplus“/as sysdba”

SQL> spool pre_upgrade_status.log

SQL> @pre_upgrade_status.sql

SQL> spool off

34.   Check both the log files and look for any warning or errors and fix them.

35.   Make sure the below two components are in valid state

Change Database user profile using note 1519872

Configuration of the AWR retention period  (OSS Note 1326067)

37.   Run Update statistics before the upgrade

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

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

brconnect -u / -c -f stats -f collect -t all -p < CPU *2 val>

38.   Now take a complete offline backup of the database

ORACLE Database Upgrade

1.       Make sure the database backup is  successful. 

Stop the SAP system and listener running under the old ORACLE_HOME

3.       Ensure that you have write permissions on /etc/oratab.

4.       Login as ora<SID> user and set/unset  the below environmental variables

setenv ORACLE_HOME /oracle/SMD/112_64

setenv ORACLE_HOME_SRC /oracle/SMD/102_64

setenv ORACLE_HOME_TGT /oracle/SMD/112_64

unsetenv TWO_TASK

unsetenv ORA_NLS

unsetenv ORA_NLS32

unsetenv ORA_NLS33

unsetenv ORA_NLS10

umask 022

5.       Confirm that the following environment variables point to Oracle 11.2 directories

·         ORACLE_HOME points to /oracle/<DBSID>/112_64

·         PATH, SHLIB_PATH

setenv SHLIB_PATH /usr/sap/DMC/SYS/exe/run:/oracle/DMC/11202/lib

LIBPATH=/sapmnt/NSQ/exe:/usr/sap/NSQ/SYS/exe/run:/oracle/NSQ/11202/lib

setenv DISPLAY 120.10.2.103:0.0

setenv PATH /oracle/SMD/112_64/bin:.:/oracle/SMD:/usr/sap/SMD/SYS/exe/run:/usr/bin:/usr/ccs/bin:/usr/contrib/bin:/usr/contrib/Q4/bin:/opt/perl/bin:/opt/ipf/bin:/opt/gvsd/bin:/opt/nettladm/bin:/opt/fcms/bin:/opt/wbem/bin:/opt/wbem/sbin:/opt/sas/bin:/opt/graphics/common/bin:/opt/atok/bin:/usr/bin/X11:/usr/contrib/bin/X11:/opt/sec_mgmt/bastille/bin:/opt/caliper/bin:/opt/drd/bin:/opt/dsau/bin:/opt/dsau/sbin:/opt/resmon/bin:/opt/firefox:/opt/gnome/bin:/usr/contrib/kwdb/bin:/opt/mozilla:/opt/perl_32/bin:/opt/perl_64/bin:/opt/prm/bin:/opt/sfm/bin:/opt/swm/bin:/opt/sec_mgmt/spc/bin:/opt/ssh/bin:/opt/sentinel/bin:/opt/swa/bin:/opt/hpsmh/bin:/opt/thunderbird:/opt/langtools/bin:/opt/gwlm/bin:/opt/ignite/bin

6.       Navigate to $ORACLE_HOME_TGT/sap/ora_upgrade/post_upgrade and start the DBUA in query mode

./dbua.sap.sh –q

During this step, the SQL*Net profiles are copied.

 

Now start DBUA in interactive mode

./ dbua.sap.sh

 

I have decided to turn off the archiving during the upgrade

 

9.       Check the summary and click finish

10.   Upgrade Process starts

Monitor the progress of the upgrade

12.   As per OSS Note you can ignore all ORA-32004/ORA-32006 messages

13.   Verify the upgrade results

Post Upgrade steps

1.       Update the below environmental variables of ora<sid>user to reflect the path of the new ORACLE_HOME directory

.cshrc

.dbenv.csh

.dbenv.sh

.dbenv_solman01.csh

.dbenv_solman01.sh

Change 102_64 to 112_64 and 10x_64 to 11x_64

Similarly update the environmental variable for <sid>adm user,login then then ls -a

3.       Open a new session for orasmd user and start the database using 11g Oracle home

 

Update BR* Tools

Extract the contents of DBATL720O10_23-20006704.SAR to the /sapmnt/SMD/exe

Run ./saproot.sh NSD

Upgrade only brtools---DBATL720O10_27-20005193

Arrange oraarch,sapbackup,saparch as follows

Run ‘post_upgrade_status.sql’ from location /oracle/DMC/112_64/sap/ora_upgrade/post_upgrade

Copy sapdba_role.sql and sapconn_role.sql from /usr/sap/DMC/SYS/exe/run to $ORACLE_HOME/dbs and execute them as ora<sid> user

After wrtinig @command press enter

6.       Updating Oracle instant client

7.       Create this dir /oracle/client/11x_64

As <sid>adm user Extract client file OCL11264.SAR to /oracle/client/11x_64

Create new symbolic link

ln -s instantclient_11202 instantclient

Check if the SAP DBSL can load the newly installed instant client correctly. Change to the exe directory as user <sapsid>adm and run the ldd command on dboraslib.so:

ldd dboraslib.so

this error is for SIDADM User while starting sidadm

Copy two files which are in

/oracle/DMC/112_64/network/admin

To

/usr/sap/DMC/SYS/profile/oracle

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'LISTENER_DMC'

Disconnected

8.       Rename the old oracle home bin directory

9.       mv<old_oracle_home>/bin <old_oracle_home>/bin-old

10.   Start listner

Do a R3trans –d check

12.   Update the attached oracle parameters as per OSS Note 1431798 and restart the database

Changing the location of the JDBC Driver

These steps are required for J2EE system (both JAVA only and ABAP+JAVA)

13.   Edit config.properties on location /usr/sap/SMD/DVEBMGS01/j2ee/configtool. Update the rdbms.driverLocation location to /oracle/client/11x_64/instantclient/ojdbc14.jar

14.   Open configtool. Use edit mode to replace the driver location in the following property sheets that contain the string rdbms.driver location

·         Cluster_data/propertysheetinstance.properties.IDXXXXX/IDXXXX.JAVAParameters

·         Cluster_data/dispatcher/IDXXXX/propertysheet Configuration Manager

·         Cluster_data/server/IDXXXX/propertysheet Configuration Manager

15.   Edit bootstrap.properties located under /usr/sap/SMD/DVEBMGS01/j2ee/cluster/bootstrap for the rdbms.driverLocation

16.   Edit sdmrepository.sdc located under /usr/sap/SMD/DVEBMGS01/SDM/program/config

Use below vi editor command to do the replacement

%s/10x_64/11x_64/g

17.   Edit rdb.properties under location /usr/sap/SMD/DVEBMGS01/j2ee/deploying

18.   Edit templateconfigurator.properties under location /usr/sap/SMD/DVEBMGS01/j2ee

19.   Update SAP Profile parameter

Change driver location in SAP instance profile

j2ee/dbdriver = /oracle/client/11x_64/instantclient/ojdbc14.jar

Now start the SAP system

21.   Database startup may fail with the below error when started as <sid>adm user

Add the below entry in the tnsname.ora file of the SAP profile path /sapmnt/SMD/profile/oracle

This would be already added in the tnsnames.ora file located under ORACLE_HOME

22.   Run update statistics

23.   Take a database backup

24.   Make sure the database is now running under archivelog mode

Reference OSS notes

Note 1431800 - Oracle 11.2.0: Central Technical Note
Note1454627 - Additional Info / Corrections to Oracle 11g Upgrade Guide
Note 1431798 - Oracle 11.2.0: Database Parameter Settings
Note 1503709 - Oracle 11.2.0: Patches / Patch collections for 11.2.0.2
Note 1524205 - Oracle 11.2.0: Database Software Installation
Note 819829 - Oracle Instant Client Installation and Configuration on Unix