Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :-

Monday, January 10, 2011

Self Service or DMZ Setup (from 11.5.10)

In Self Service or DMZ Setup (from 11.5.10) you might have noticed new configuration file url_fw.conf under $IAS_ORACLE_HOME/Apache/Apache/conf . In this post We’ll understand requirement of this file , building blocks of this file, mod_rewrite module of apache and regular expressions.

Overview of url_fw.conf file is required ?

This file is delievered by patch 3942483 (included in 11.5.10) and called by Apache/Webserver configuration file httpd.conf .

This file uses mod_rewrite module of Apache to allow/disallow URL’s matched by regular expression.

What this does? - > This file provide extra security for DMZ or Self Service implementation accessible over internet. Only few URL’s opened/allowed by this file are accessible thus protecting secured URL which should not be accessible via internet.

Why its decided to include url_fw.conf -> If node trust level is marked as external (Three type of Node Trust level for a node , External, Internal, Administration) then Autoconfig includes url_fw.conf file in httpd.conf

What is mod_rewrite and where to get more information -> mod_rewrite is URL Rewrite Engine in Apache (on which Oracle-Apache or Oracle HTTP Server or Web Server in Apps). mod_rewrite is powerful tool for URL manipulation like to

- Restrict Access to directories and files

- Conditional redirection of access

- Relocating Servers, File System or Directories

- Regeneration of static pages based on HTTP Header Variable

For more information on mod_rewrite module of apache visit

How to debug mod_rewite issues ?

If you think some of URL’s (complete url or partial - gif, jpg, html or jsp file) are blocked by above URL Firewall and you wish to know which file is blocked , you can enable logging by adding following directive in url_fw.conf

RewriteLog “/your_log_directory/rewrite.log”

RewriteLogLevel 7

By default logging is disabled , logLevel value is from 0-10 (0 means no logging and 10 is log everything which records all steps mod_rewrite is doing in background) a sensible value is 6 or 7 and you will see in log what URL is blocked and by what rule; so that if you think user should have access to that URL you can grant access on that resource by adding new rule in url_fw.conf

Sample url_fw.conf value and its meaning -

RewriteRule ^/$ /OA_HTML/AppsLocalLogin.jsp [R,L]


RewriteRule ^/OA_HTML/jsp/fnd/fndhelp.jsp$ - [L]

Here first rule is saying that when user type / i.e. after hostname , domainname and port number and then /; redirect user to /OA_HTML/AppsLocalLogin.jsp and stop applying any rewrite rule after that.

In second rule; - which means don’t do any thing and present User same url as mentioned in left side i.e. /OA_HTML/jsp/fnd/fndhelp.jsp

here [R,L] in end

R- Means Rewrite

L - Last rewrite rule (No more rule to apply after this)

In order to understand above rules , you should know regular expression and here few tips/meta characters on regular expressions

1) . (dot) means matches any characters

2) [] specifies a class


—> [a-z] matches any lower case characters from a to z

—>[a-zA-Z0-9] matches any character upper or lower case from a to z and numeric 0 to 9

—> [abc$] matches a or b or c or $

—> [^0-9] matches anything except digit 0 to 9 . Here ^ is negation

Meta Characters in Regular Expressions

^ -> Matches Start of a line

$ -> Matches End of line


^appsdba -> Matches any line starting with appsdba

appsdba$ -> Matches any line ending with appsdba

^appsdba$ -> Matches any line which consist of just one word appsdba

Quantifiers for Characters

–> ? matches zero or one instance of character

–> + matches one or more instance of character

–> * matches zero or more instance of character

For Example

appsdba? matches appsdb or appsdba

appasdba+ matches appsdba, appsdbaa, appsdbaaa and so on

appsdba* matches appsdb, appsdba, appsdbaa, appsdbaaa and so on

Few error messages related to URL Firewall are

– Access to requested URL has been blocked by the url firewall

– Gone URL you are looking for is blocked by url Firewall

– Error in opening up attachments or date picker in iStore, iRec, iProc

– FW-1 at Firewall-2: Access denied

For more information on DMZ and E-Business Suite visit Steven Chan’s post at

Oracle E –Business suite Database upgradation from 10.2.01. to


1. Introduction

Upgrading from Oracle database from to on Sun OS using DBUA is as we upgrade on other platforms using DBUA on other platforms. Following are the differences in various platforms

• Setting operating system parameters like kernel configuration,

• Checking memory,

• Disks and other resources.


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


3 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)


Refer Metalink Note 362203.1

4. 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 program. Staging area for Oracle 10GR2 requires around 3-4 GB of disk space.

Mount the DVD’s of Oracle 10GR2/Download from oracle websites 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


5. 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’).

6. 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”

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


Create Unix Users “oracle ”

Add these lines in “/etc/passwd” file:


Add these lines in “/etc/shadow” file:


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


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


# ls

install runInstaller stage1

doc response stage welcome.html


# chmod -R 777 *

# pwd




8. Universal Installer

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

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


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

9.2 Product Specific Prerequisite Checks

In “Product Specific Prerequisite Checks” Click Next . If any errors or warning appears rectify an proceed


9.3 Configuration Options

Select “Install Database Software Only ” and click “Next”.


9.4 Oracle Universal Installer Summary

In the “Oracle Universal Installer Summary” screen accept the default and click “Install ”.

9.5 Install

In “Install” You have to monitor the log file. The log file location has been highlighted in the screen.

9.6 Execute Configuration Scripts

In “Configuration Scripts” screen select “ok” and follow the instruction the screen.

9.7 End of Installation

In the “End of Installation” Screen click Exit

9.8 Oracle 10G Companion Software Installation

Go to”/d33/stage/stage1” and execute “./runInstaller” command. In the welcome screen click next

9.9 Select Product to Install

9.10 Specify Home Details

In the “Specify home Details” screen update the details as required and click “Next”.

9.11 Product Specific Prerequisite Checks

In “Product Specific Prerequisite Checks” Click Next. If any errors or warning appears rectify and proceed.

9.12 Oracle Universal Installer Summary

In the “Oracle Universal Installer Summary” screen accept the default and click “Install”.

9.13 End of Installation

In the “End of Installation” Screen click Exit.

9.14 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 - 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 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options SQL>

9.15 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


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:



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 ‘


’database link ‘






’connect to ‘


‘ identified by ”’


”’ using ”’



’;’ TEXTFROM$ 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

9.16 Copy the Important Parameter files

a. Login to Source ORACLE_HOME $ cd $ORACLE_HOME/dbsCopy init.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.ora and the changes which was suggested by the utlu102i.sql output.c. Made changes in Tnsanames.ora and Listener.ora as per situation.


10. Database Upgrade Assistant

Login to Xsession again as target Oracle_10g home owner

$cd $ORACLE_HOME/bin $./dbua

10.1 Database Upgrade Assistant Welcome Screen

In the “Database Upgrade Assistant Welcome Screen” Click Next

10.2 Database Upgrade Assistant Steps 1

In “Database Upgrade Assistant” choose the proper source database click Next

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

10.4 Database Upgrade Assistant Steps 3

In the “Database Upgrade Assistant Steps 3” Click Yes

10.5 Database Upgrade Assistant Steps 4

In the “Database Upgrade Assistant Steps 4” Change the datafile location according to requirement Click Next

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

10.7 Database Upgrade Assistant Steps 6

In “Database Upgrade Assistant Steps 6” Select “I have already backed up my database”

And click Next

10.8 Database Upgrade Assistant Steps 7 Database Credentials

In “Database Upgrade Assistant Steps 7 Database Credentials” Enter password and click Next

10.9 Database Upgrade Assistant Steps 7 of 7 Summary

In “Database Upgrade Assistant Steps 7 of 7 Summary” Click Finish

10.10 Database Upgrade Assistant Progress

Now its time to monitor log file

End of Database Upgrade Assistant


11a 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

1. 10Gr2_db_sol.cpio (Oracle 10GR2 Software )

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

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

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


$ 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 / (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 /* 0×0008 15 */ .ascii “v2dba”/* 0×0014 20 */ .align 8.L13:/* 0×0014 22 */ .ascii “v2dba”to/* 0×0008 15 */ .ascii “dba”/* 0×0014 20 */ .align 8 .L13:/* 0×0014 22 */ .ascii “dba”$Edit From : KPIC_OPTION=-K PICTo : KPIC_OPTION=-xarch=v9 -K PIC$ make -f 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 to 10GR2

15. References

Oracle Metalink: