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
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)
2. Check
the hardware and software requirements
3. Review
Oracle specific Kernel parameters and Shell Limits
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
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
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
1. Login
as ora<SID> user
2. Check
Prerequisites for OUI
3. Checking
Operating system requirements
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
1. Navigate
to /oracle/stage/112_64/database/SAP and execute
14. Choose install
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
4th
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
