Oracle E –Business suit 11.5.10.2 does not work with the database 10.2.0.1 so we need to upgrade the database to the next patch set level 10.2.0.2
Next post following this one is about upgradation from 10.2.01. to 10.2.0.2
.
Introduction
Upgradation of Oracle Database on Sun OS using DBUA is same as like other DBUA on other platforms. The only difference lies in setting operating system parameters like kernel configuration, checking memory, disks and other resources.
.
2. Objective
The objective of this document is to detail out the Upgradation steps of Oracle 9.2.0.6 to 10GR2 on Sun OS using DBUA .
.
3. Audience
This document is for Oracle Applications DBA. It is expected DBA performing the installation has fair knowledge of the Sun Solaris (UNIX) Operating System.
.
4. Assumption
• The Sun Solaris (UNIX) Operating System is installed on the Sun Solaris Hardware with all the required resources.
• All the required file systems are created with sufficient disk space as required for Oracle Applications Software and Database.
• The Staging area for Oracle Database is created
• Application DBA performing installation has a graphical interface with the Sun OS system. We are going to use xmanger 2.0 software for accessing the Sun OS from remote client.
• The openwin – xterm process is running on the server.
.
5. Pre – Requisite
Before Starting Upgrading process we have to apply two important patches in the Source
1. 10g Release 2 interoperability patch for 11.5.10 (4653225)
2. 5478710 (TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH O
Refer Metalink Note 362203.1
6. Creating Staging Area on the Server
Staging Area is an area from where you will stage the Oracle 10GR2/Companion Software and run the installation programme. Staging area for Oracle 10GR2 requires around 3-4 GB of disk space.
Mount the DVD’s of Oracle 10GR2/Download from oracle websites otn.oracle.com on the server and copy the DVD’s in the staging location.
Create directories named as the stage (this you can get from the DVD’s component.label file).
Copy all the DVD’s of similar components in same stage area.
Stage No. of DVD’s DVD Contents
Oracle11 Software 1 Disk1 /d33/stage
Oracle Companion 1 Disk2 d33/stage /stage1
.
7. Checking the Sun Operating System
System and hardware Configuration:
Hardware Status: sun4 (UNIX command; ‘arch’)
Operating System: SunOS 5.11 Generic sun4u sparc SUNW, Sun-Fire-V240 (UNIX command; ‘uname –a’).
CPU: 2 CPU’s. Each having the sparcv9 processor operates at 1503 MHz and has a sparcv9 floating point processor (UNIX command; ‘psrinfo –v’).
Memory size: 5120 Megabytes (UNIX command; ‘prtconf |grep size’).
Swap Total: (45440k bytes allocated + 4032k reserved = 49472k used) 14468936k available, i.e., 14GB (UNIX command; ‘swap –s’).
Check file System mounted and its respective size (UNIX command; ‘df -h’).
8. Create base directories for Oracle 10GR2 installation
From the mounted file system decide which file system will hold the database tier
For Ex:
/d35 – 32 GB – Oracle 10G Database Software (ORACLE_10GHOME RDBMS,)Create directory “/d35/ora10G/10.2.0”
9. Create OS group and Users and granting required privileges and file permissions
Create operating system group “DBA” and OS users “ora10G” for database tier . Note that we are planning to upgrade database tier owned by “oracle” user but both on the same host / system. But new Oracle_10G Home owner and source Oracle_9i Home owner should fall under same group.
Check the group created in “/etc/group”
# cat /etc/group |grep dba
dba::110:
Create Unix Users “oracle ”
Add these lines in “/etc/passwd” file:
ora10G:x:113:110::/orahome/ora10g:/bin/sh
Add these lines in “/etc/shadow” file:
Ora10g:30oQlK30ch6xY:13187::::::
Note that the user id is always unique and also the home directory given is the base directory as created above.
Give ownership of “/d35/ora10G” to “ora10G” .
# chown -R ora10G:dba /d35/ora10G
# ls -lrt /d35
/u01:
total 18
drwx—— 2 root root 8192 Jan 13 13:16 lost+found
drwxr-xr-x 2 oracle dba 512 Jan 23 12:34 ora10G
Give full file permissions on the staging area of Oracle Applications. Give “777” privileges on the staging area. Some unzip files are not able to get unzipped and then the installation gives error.
# pwd
/u06/stage
# ls
install runInstaller stage1
doc response stage welcome.html
#
# chmod -R 777 *
# pwd
/u06/stage
#
.
10. Universal Installer
11 Starting the X Windows session
Login to Server from a graphical interface console.
We are using Xmanager2.0 software for Oracle Universal Install and DBUA.
a href=”https://onlineappsdba.com/wp-content/uploads/2008/07/c1.JPG” mce_href=”https://onlineappsdba.com/wp-content/uploads/2008/07/c1.JPG”>
</a
Go to option and select ->Session->Java Desktop System 3
Environment settings
Logged in as “ora10G” and set the profile Example ORACLE_BASE=/d35/test/v10G;export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/10.2.0; export ORACLE_HOMEORACLE_SID=PATCH ; export ORACLE_SIDLD_LIBRARY_PATH=$ORACLE_HOME/lib ; export LD_LIBRARY_PATHPATH=$PATH:/usr/local/bin:/usr/ccs/bin:/usr/sfw/bin:$ORACLE_HOME/bin ; export PATHVerify the profile using $ env|more Check the inventory_loc in Sun Solaris it is located in /var/opt/oracle/oraInst.loc change the inventory location Check the oratab file in Sun Solaris it is located in /var/opt/oracle/oratab and comment the source instance because we are following the same ORACLE_SID for new ORACLE_10G .Example is below :- #v1:/d35/oracle/v1db/9.2.0:N
.
11.2 Starting Oracle 10G Software Installation.
Go to”/d33/stage” and execute “./runInstaller” command. The “Oracle 10g Database Installation Screen will Appear”.
In this screen change Global Database Name and enter password click Next.
.
11.3 Product Specific Prerequisite Checks
In “Product Specific Prerequisite Checks” Click Next . If any errors or warning appears rectify an proceed
.
11.4 Configuration Options
Select “Install Database Software Only ” and click “Next”.
.
11.5 Oracle Universal Installer Summary
In the “Oracle Universal Installer Summary” screen accept the default and click “Install ”.
11.6 Install
In “Install” You have to monitor the log file. The log file location has been highlighted in the screen.
11.7 Execute Configuration Scripts
In “Configuration Scripts” screen select “ok” and follow the instruction the screen.
11.8 End of Installation
In the “End of Installation” Screen click Exit
11.9 Oracle 10G Companion Software Installation
Go to”/d33/stage/stage1” and execute “./runInstaller” command. In the welcome screen click next
11.10 Select Product to Install
11.11 Specify Home Details
In the “Specify home Details” screen update the details as required and click “Next”.
11.12 Product Specific Prerequisite Checks
In “Product Specific Prerequisite Checks” Click Next. If any errors or warning appears rectify and proceed.
11.13 Oracle Universal Installer Summary
In the “Oracle Universal Installer Summary” screen accept the default and click “Install”.
11.14 End of Installation
In the “End of Installation” Screen click Exit.
11.15 Post Install Checks Check the log file for any errors .Login as Oracle 10G_home owner and check whether you are able to connect as sys $ sqlplus “/as sysdba”
you will get the follwing output $ sqlplus “/as sysdba” SQL*Plus: Release 10.2.0.1.0 – Production on Wed May 23 13:19:31 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit ProductionWith the Partitioning, OLAP and Data Mining options SQL>
11.16 Before you Begin Up gradation in Source Database
a. Log in to the system as the owner of the new 10gR2 ORACLE_HOME and copy the following files from the 10gR2 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the /tmp directory on your system: ORACLE_HOME/rdbms/admin/utlu102i.sql ORACLE_HOME/rdbms/admin/utltzuv2.sql Make a note of the new location of these files.
.
b. Login as ORACLE_SOURCE home owner. Go to /var/opt/oracle/oratab remove the comment and startup the database. Now you connect as sys owner from location where you have copied utlu102i.sql and utltzuv2.sql in sqlplus $ sqlplus “/as sysdba” SQL>@ utlu102i.sqlSQL> spool off
c. The above sql generate output file called updatelog.lst review the output made the changes accordingly in target init.ora .SQL>@ utltzuv2.sqlSQL> spool off The above sql generate output file called timezone.log review the output and change accordingly .
.
d. Check for the deprecated CONNECT Role After upgrading to 10gR2, the CONNECT role will only have the CREATE SESSIONprivilege; the other privileges granted to the CONNECT role in earlier releases will be revoked during the upgrade.
To identify which users and roles in your database are granted the CONNECT role, use the following query:
SELECT grantee FROM dba_role_privs
WHERE granted_role = ‘CONNECT’ and grantee NOT IN ( ‘SYS’, ‘OUTLN’, ‘SYSTEM’, ‘CTXSYS’, ‘DBSNMP’, ‘LOGSTDBY_ADMINISTRATOR’, ‘ORDSYS’, ‘ORDPLUGINS’, ‘OEM_MONITOR’, ‘WKSYS’, ‘WKPROXY’, ‘WK_TEST’, ‘WKUSER’, ‘MDSYS’, ‘LBACSYS’, ‘DMSYS’, ‘WMSYS’, ‘OLAPDBA’, ‘OLAPSVR’, ‘OLAP_USER’, ‘OLAPSYS’, ‘EXFSYS’, ‘SYSMAN’, ‘MDDATA’, ‘SI_INFORMTN_SCHEMA’, ‘XDB’, ‘ODM’);
If users or roles require privileges other than CREATE SESSION, then grant the specific required privileges prior to upgrading. The upgrade scripts adjust the privilegesfor the Oracle-supplied users.
.
e. In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:
SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVSWHERE GRANTEE=’CONNECT’ ;
GRANTEE PRIVILEGE—————————— —————————CONNECT CREATE VIEWCONNECT CREATE TABLECONNECT ALTER SESSIONCONNECT CREATE CLUSTERCONNECT CREATE SESSIONCONNECT CREATE SYNONYMCONNECT CREATE SEQUENCECONNECT
CREATE DATABASE LINK In Oracle 10.2 the CONNECT role only includes CREATE SESSION privilege.
.
f. Create the script for dblink incase of downgrade of the database. During the upgrade to 10gR2, any passwords in database links will be encrypted. To downgrade back to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links will not exist in the downgraded database. If you anticipate a requirement to be able to downgrade back to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can recreate the database links after the downgrade.
.
g. Following script can be used to construct the dblink.
SELECT‘create ‘||DECODE(U.NAME,’PUBLIC’,’public ‘)||’database link ‘||CHR(10)||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.’)|| L.NAME||chr(10) ||’connect to ‘ || L.USERID || ‘ identified by ”’||L.PASSWORD||”’ using ”’ || L.host || ”” ||chr(10)||’;’ TEXTFROM sys.link$ L, sys.user$ UWHERE L.OWNER# = U.USER# ;
.
h. Check the invalid objects in source instance using the following qyery :-
spool invalid_pre.lstselect substr(owner,1,12) owner, substr(object_name,1,30) object, substr(object_type,1,30) type, status from dba_objects where status <>’VALID’;
spool off
Compile all the invalid objects using Run the following script and then requery invalid objects: This script must be run as a user with SYSDBA privs using SQL*Plus:
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus ‘/as sysdba’
SQL> @utlrp.sql
Even compile all the objects using Adamin utility of Oracle E –Business Suit .
.
i. Take Complete Backup of source Application and Database
11.17 Copy the Important Parameter files
a. Login to Source ORACLE_HOME $ cd $ORACLE_HOME/dbsCopy init<SID>.ora to target ORACLE_10G home $ORACLE_HOME/network
Copy tnsnames.ora and listener.ora to target TNS_ADMIN directory
b. Made changes in init<SID>.ora and the changes which was suggested by the utlu102i.sql output.c. Made changes in Tnsanames.ora and Listener.ora as per situation.
.
12. Database Upgrade Assistant
Login to Xsession again as target Oracle_10g home owner
$cd $ORACLE_HOME/bin $./dbua
12.1 Database Upgrade Assistant Welcome Screen
In the “Database Upgrade Assistant Welcome Screen” Click Next
12.2 Database Upgrade Assistant Steps 1
In “Database Upgrade Assistant” choose the proper source database click Next
12.3 Database Upgrade Assistant Steps 2
In “Database Upgrade Assistant Steps 2 “ It will take 20 to 30 minutes to get the database information and then click Next.
12.4 Database Upgrade Assistant Steps 3
In the “Database Upgrade Assistant Steps 3” Click Yes
12.5 Database Upgrade Assistant Steps 4
In the “Database Upgrade Assistant Steps 4” Change the datafile location according to requirement Click Next
12.6 Database Upgrade Assistant Steps 5
Compile invalid Objects
In the “Database Upgrade Assistant Steps 5 Compile Invalid Objects” Select Degree of Parallelism. Degree of Parallelism depend on Number of CPU.
12.7 Database Upgrade Assistant Steps 6
In “Database Upgrade Assistant Steps 6” Select “I have already backed up my database”
And click Next
12.8 Database Upgrade Assistant Steps 7 Database Credentials
In “Database Upgrade Assistant Steps 7 Database Credentials” Enter password and click Next
12.9 Database Upgrade Assistant Steps 7 of 7 Summary
In “Database Upgrade Assistant Steps 7 of 7 Summary” Click Finish
12.10 Database Upgrade Assistant Progress
Now its time to monitor log file
End of Database Upgrade Assistant
.
13 Errors Encountered and the Solution
a. While running runInstaller Script encountered the following errors :- ./runInstaller: /backup/database/install/.oui: cannot execute This error was due to bad media, So I downloaded following files from otn.oracle.com
- 10Gr2_db_sol.cpio (Oracle 10GR2 Software )
- 10Gr2_companion_sol.cpio (Oracle Companion Software)
Create Stage directory in any mount point give e.g. /d33/stage 777 permission to that directory # cat 10Gr2_db_sol.cpio | cpio –icd <Enter>
It will extract entire software under stage directory.
Create Stage1 directory under stage directory e.g. d33/stage /stage1 give 777 permission to that directory
# cat 10Gr2_companion_sol.cpio | cpio –icd <Enter>
It will extract entire software under stage directory.
b. While Running DBUA I have found the following errors :-
This is due to target and the source database owner is not in same group
Login as root; Example source oracle database owner group is dba , so change it accordingly .
# usermod -g dba ora10g
Login as ora10g owner and execute the following command
$cd $ORACLE_HOME
$ ls -l `find . ! -user ora10g`
In my system it gave the following output
-rwsr-x— 1 root dba 69028 2006-07-17 21:20 ./bin/extjob*
-rwsr-s— 1 root dba 18845 2006-06-21 09:13 ./bin/nmb*
-rwsr-s— 1 root dba 19999 2006-06-21 09:13 ./bin/nmo*
-r-sr-s— 1 root dba 14456 2006-02-04 21:57 ./bin/oradism*
-rw-r—– 1 root dba 1534 2005-12-22 13:39 ./rdbms/admin/externaljob.ora
Change ownership to the Oracle_10g Home using following command
#chown –R ora10g:dba /<Location> (Loging as Oracle_10g home owner) example ora10g
$ cd $ORACLE_HOME/rdbms/lib
$mv config.o config.o.bkpEdit config.s file
and change the lines /* 0x0008 15 */ .ascii “v2dba”/* 0x0014 20 */ .align 8.L13:/* 0x0014 22 */ .ascii “v2dba”to/* 0x0008 15 */ .ascii “dba”/* 0x0014 20 */ .align 8 .L13:/* 0x0014 22 */ .ascii “dba”$Edit env_rdbms.mk From : KPIC_OPTION=-K PICTo : KPIC_OPTION=-xarch=v9 -K PIC$ make -f ins_rdbms.mk config.o ioracleIt has solved the problem
.
14. Summary
In this document we have covered:
1. Preparing the Sun Solaris system for Oracle 10GR2 Up gradation .
2. Creating Staging Area of Oracle 10GR2 Software.
3. Installing Oracle 10GR2 along with Companion Products on Sun Solaris Server.
4. Upgrading Oracle Database from 9.2.0.6 to 10GR2
15. References
Oracle Metlink: https://metalink.oracle.com
