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 http://httpd.apache.org/docs/1.3/mod/mod_rewrite.html
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]
or
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
i.e.
—> [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
like
^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
http://blogs.oracle.com/schan/2006/05/17
HAPPY LEARNING!
Oracle Apps 11i/R12 --- Oracle 10gAS/Weblogic Server 11g --- Oracle 9i,10g,11g - Oracle APEX
Monday, January 10, 2011
Oracle E –Business suite 11.5.10.2 Database upgradation from 10.2.01. to 10.2.0.2
.
1. Introduction
Upgrading from Oracle database from 10.2.0.1 to 10.2.0.2 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)
2. 5478710 (TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH O
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 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
.
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
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
#
.
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.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 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>
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
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
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 otn.oracle.com
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
$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 / (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 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 Metalink: https://metalink.oracle.com
HAPPY LEARNING!
.
1. Introduction
Upgrading from Oracle database from 10.2.0.1 to 10.2.0.2 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)
2. 5478710 (TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH O
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 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
.
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
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
#
.
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
.
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 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>
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
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
9.16 Copy the Important Parameter files
a. Login to Source ORACLE_HOME $ cd $ORACLE_HOME/dbsCopy init
Copy tnsnames.ora and listener.ora to target TNS_ADMIN directory
b. Made changes in init
.
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 otn.oracle.com
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
$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 /
$ 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 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 Metalink: https://metalink.oracle.com
HAPPY LEARNING!
Subscribe to:
Posts (Atom)