Query this sql.....
select distinct aprb.application_short_name as "Affected Modules"
from ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_patch_run_bugs aprb
where aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and apr.patch_run_id = aprb.patch_run_id
and aprb.applied_flag = 'Y'
and aap.patch_name = '&PatchName';
Wednesday, February 16, 2011
Sunday, February 13, 2011
Oracle RAC Architecture
In this post, you can see Oracle RAC Architecture Videos.... You will get better understanding of RAC after seeing....
Second Video
Diagnosing RAC
Second Video
Diagnosing RAC
Oracle Architecture
Oracle Real Application Clusters
In this post, I will explain you briefly about Oracle RAC and it's background process.
A cluster comprises multiple interconnected computers or servers that appear as if they are one server to end users and applications. Oracle Clusterware is a portable cluster management solution that is integrated with the Oracle database. Oracle Clusterware enables you to create a clustered pool of storage to be used by any combination of single-instance and Oracle RAC databases.
Oracle Clusterware is the only clusterware that you need for most platforms on which Oracle RAC operates. You can also use clusterware from other vendors if the clusterware is certified for Oracle RAC.
Single-instance Oracle databases have a one-to-one relationship between the Oracle database and the instance. Oracle RAC environments, however, have a one-to-many relationship between the database and instances. An Oracle RAC database can have up to 100 instances, all of which access one database. All database instances must use the same interconnect, which can also be used by Oracle Clusterware.
Each Oracle RAC database instance also has:
- At least one additional thread of redo for each instance
- An instance-specific undo tablespace
Figure 1-1 shows how Oracle RAC is the Oracle Database option that provides a single system image for multiple servers to access one Oracle database. In Oracle RAC, each Oracle instance usually runs on a separate server.
Oracle Clusterware for Oracle Real Application Clusters
Oracle Clusterware provides a complete, integrated clusterware management solution on all Oracle Database platforms. This clusterware functionality provides all of the features required to manage your cluster database including node membership, group services, global resource management, and high availability functions. You can install Oracle Clusterware independently or as a prerequisite to the Oracle RAC installation process. Oracle database features such as services use the underlying Oracle Clusterware mechanisms to provide their capabilities. Oracle also continues to support select third-party clusterware products on specified platforms.
Oracle Clusterware is designed for, and tightly integrated with, Oracle RAC. When you create an Oracle RAC database using any of the management tools, the database is registered with and managed by Oracle Clusterware, along with the other Oracle processes such as Virtual Internet Protocol (VIP) address, Global Services Daemon (GSD), the Oracle Notification Service (ONS), and the Oracle Net listeners. These resources are automatically started when Oracle Clusterware starts the node and automatically restarted if they fail. The Oracle Clusterware daemons run on each node.
You can use Oracle Clusterware to manage high-availability operations in a cluster. Anything that Oracle Clusterware manages is known as a CRS resource, which could be a database, an instance, a service, a listener, a VIP address, an application process, and so on. Oracle Clusterware manages CRS resources based on the resource's configuration information that is stored in the Oracle Cluster Registry (OCR). You can use SRVCTL commands to administer other node resources.
Oracle Real Application Clusters Architecture and Processing
At a minimum, Oracle RAC requires a cluster software infrastructure that can provide concurrent access to the same storage and the same set of data files from all nodes in the cluster, a communications protocol for enabling interprocess communication (IPC) across the nodes in the cluster, enable multiple database instances to process data as if the data resided on a logically combined, single cache, and a mechanism for monitoring and communicating the status of the nodes in the cluster.
Understanding Cluster-Aware Storage Solutions
An Oracle RAC database is a shared everything database. All data files, control files, SPFILEs, and redo log files in Oracle RAC environments must reside on cluster-aware shared disks so that all of the cluster database instances can access these storage components. All database instances must use the same interconnect, which can also be used by Oracle Clusterware. Because Oracle RAC databases use a shared everything architecture, Oracle RAC requires cluster-aware storage for all database files.
In Oracle RAC, the Oracle Database software manages disk access and the Oracle software is certified for use on a variety of storage architectures. It is your choice as to how to configure your disk, but you must use a supported cluster-aware storage solution. Oracle Database provides the following file storage options for Oracle RAC:
- Automatic Storage Management (ASM)
This is the recommended solution to manage your disk.
- OCFS2 and Oracle Cluster File System (OCFS)
OCFS2 is available for Linux and OCFS is available for Windows platforms. However you may optionally use a third-party cluster file system or cluster-aware volume manager that is certified for Oracle RAC.
- A network file system
- Raw devices
Overview of Connecting to the Oracle Database Using Services and VIP Addresses
All nodes in an Oracle RAC environment must connect to a Local Area Network (LAN) to enable users and applications to access the database. Applications should use the Oracle Databaseservices feature to connect to an Oracle database. Services enable you to define rules and characteristics to control how users and applications connect to database instances. These characteristics include a unique name, workload balancing and failover options, and high availability characteristics. Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database.
Users can access an Oracle RAC database using a client/server configuration or through one or more middle tiers, with or without connection pooling. Users can be database administrators, developers, application users, power users, such as data miners who create their own searches, and so on.
Most public networks typically use TCP/IP, but you can use any supported hardware and software combination. Oracle RAC database instances can be accessed through a database's default IP address and through VIP addresses.
The interconnect network is a private network that connects all of the servers in the cluster. The interconnect network uses a switch (or multiple switches) that only the nodes in the cluster can access. Configure User Datagram Protocol (UDP) on a Gigabit Ethernet for your cluster interconnect. On Linux and Unix systems, you can configure Oracle Clusterware to use either the UDP or Reliable Data Socket (RDS) protocols. Windows clusters use the TCP protocol. Crossover cables are not supported for use with Oracle Clusterware interconnects.
In addition to the node's host name and IP address, you must also assign a virtual host name and an IP address to each node. You should use the virtual host name or VIP address to connect to the database instance. For example, you might enter the virtual host name CRM in the address list of the tnsnames.ora file.
A virtual IP address is an alternate public address that client connections use instead of the standard public IP address. To configure VIP addresses, you need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.
If a node fails, then the node's VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections. Generally, VIP addresses fail over when:
- The node on which a VIP address runs fails
- All interfaces for the VIP address fail
- All interfaces for the VIP address are disconnected from the network
Clients that attempt to connect to the VIP address receive a rapid connection refused error instead of waiting for TCP connect timeout messages. You configure VIP addresses in the address list for your database connection definition to enable connectivity.
If you use Network Attached Storage (NAS), then you are required to configure a second private network. Access to this network is typically controlled by the vendor's software. The private network uses static IP addresses.
About Oracle Real Application Clusters Software Components
Oracle RAC databases have two or more database instances that each contain memory structures and background processes. An Oracle RAC database has the same processes and memory structures as a single-instance Oracle database as well as additional process and memory structures that are specific to Oracle RAC. Any one instance's database view is nearly identical to any other instance's view in the same Oracle RAC database; the view is a single system image of the environment.
Each instance has a buffer cache in its System Global Area (SGA). Using Cache Fusion, Oracle RAC environments logically combine each instance's buffer cache to enable the instances to process data as if the data resided on a logically combined, single cache.
To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy a query or transaction, Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances, which effectively increases the size of the SGA for an Oracle RAC instance.
After one instance caches data, any other instance within the same cluster database can acquire a block image from another instance in the same database faster than by reading the block from disk. Therefore, Cache Fusion moves current blocks between instances rather than re-reading the blocks from disk. When a consistent block is needed or a changed block is required on another instance, Cache Fusion transfers the block image directly between the affected instances. Oracle RAC uses the private interconnect for interinstance communication and block transfers. The GES Monitor and the Instance Enqueue Process manages access to Cache Fusion resources and enqueue recovery processing.
About Oracle Real Application Clusters Background Processes
The GCS and GES processes, and the GRD collaborate to enable Cache Fusion. The Oracle RAC processes and their identifiers are as follows:
- ACMS—Atomic Controlfile to Memory Service (ACMS)
In an Oracle RAC environment, the atomic controlfile to memory service (ACMS) per-instance process is an agent that contributes to ensuring a distributed SGA memory update is either globally committed on success or globally aborted in the event of a failure.
- GTX0-j—Global Transaction Process
The GTX0-j process provides transparent support for XA global transactions in a RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions.
- LMON—Global Enqueue Service Monitor
The LMON process monitors global enqueues and resources across the cluster and performs global enqueue recovery operations.
- LMD—Global Enqueue Service Daemon
The LMD process manages incoming remote resource requests within each instance.
- LMS—Global Cache Service Process
The LMS process maintains records of the datafile statuses and each cached block by recording information in a Global Resource Directory (GRD). The LMS process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances. This processing is part of the Cache Fusion feature.
- LCK0—Instance Enqueue Process
The LCK0 process manages non-Cache Fusion resource requests such as library and row cache requests.
- RMSn—Oracle RAC Management Processes (RMSn)
The RMSn processes perform manageability tasks for Oracle RAC. Tasks accomplished by an RMSn process include creation of resources related Oracle RAC when new instances are added to the clusters.
- RSMN—Remote Slave Monitor manages background slave process creation and communication on remote instances. These background slave processes perform tasks on behalf of a coordinating process running in another instance.
Wednesday, February 9, 2011
Merging of oracle apps patches
Merging apps patches will be useful when you want to reduce the downtime for patch application
Simple steps to merge two patches.
1. Download two required apps patches (Patch1.zip) and (Patch2.zip).
2. Create one source directory (for e.g. source)
3. Unzip two apps patches into src directory.
4. Create one destination directory (for e.g. destination)
5. Source the Apps env and issue the following command.
6. Perl <ad_top>/bin/admerge.pl –s source –d destination
7. Patch is merged and created u_merged.drv.
Use this u_merged.drv file for adpatch application
Simple steps to merge two patches.
1. Download two required apps patches (Patch1.zip) and (Patch2.zip).
2. Create one source directory (for e.g. source)
3. Unzip two apps patches into src directory.
4. Create one destination directory (for e.g. destination)
5. Source the Apps env and issue the following command.
6. Perl <ad_top>/bin/admerge.pl –s source –d destination
7. Patch is merged and created u_merged.drv.
Use this u_merged.drv file for adpatch application
How to find an AD patchset level ?
SQL> select PATCH_LEVEL from fnd_product_installations where APPLICATION_ID=50;
PATCH_LEVEL
——————————
11i.AD.I
PATCH_LEVEL
——————————
11i.AD.I
How to find rapidwiz version
Go to: cd startCD\Disk1\rapidwiz
type: rapidwiz Version
Output will be:
Oracle Applications Rapid Install Wizard
Version 12.0.0.22
(c) Copyright 2000-2006 Oracle Corporation. All rights reserved.
Press any key to continue . . .
type: rapidwiz Version
Output will be:
Oracle Applications Rapid Install Wizard
Version 12.0.0.22
(c) Copyright 2000-2006 Oracle Corporation. All rights reserved.
Press any key to continue . . .
Find Blocking Session details
In first step, find SID from v$session.
SQL> select process,sid, blocking_session from v$session where blocking_session is not null;
PROCESS SID BLOCKING_SESSION
———— ———- —————-
1234 365 366
1234 366 365
In second step find the serial number for the Blocking Session to kill using SID
SQL> select SERIAL# from v$session where SID=365;
SERIAL#
———-
130
In third step, kill the blocking session using SID and serial number
SQL> alter system kill session ’365,130′;
System altered.
SQL> select process,sid, blocking_session from v$session where blocking_session is not null;
PROCESS SID BLOCKING_SESSION
———— ———- —————-
1234 365 366
1234 366 365
In second step find the serial number for the Blocking Session to kill using SID
SQL> select SERIAL# from v$session where SID=365;
SERIAL#
———-
130
In third step, kill the blocking session using SID and serial number
SQL> alter system kill session ’365,130′;
System altered.
How to find Release version of Oracle Apps?
Login to sqlplus as apps/apps
SQL> select release_name from fnd_product_groups;
RELEASE_NAME
————————————————–
12.0.0
SQL> select release_name from fnd_product_groups;
RELEASE_NAME
————————————————–
12.0.0
How to find a product version ?
There are so many ways to find out the product version, but there is simple way to find it out.
1.Go to $AD_TOP/sql
2.Connect to sqlplus as apps/apps
3.Run (@adutconf.sql)
4.Output will be in same directory in the name of adutconf.lst
1.Go to $AD_TOP/sql
2.Connect to sqlplus as apps/apps
3.Run (@adutconf.sql)
4.Output will be in same directory in the name of adutconf.lst
How to recover the Oracle Applications context file
The Oracle Applications context file can be recovered by running the adclonectx.pl script.
perl /clone/bin/adclonectx.pl retrieve
On being prompted for the context file to be retrieved, select the option of retrieving the Applications tier context file that has been lost and retrieve it to the default location specified by the script.
The above command can be used only when INST_TOP the is still intact. In case the has also been lost accidentally, the Applications tier context file may be retrieved as follows:
Execute the following command on the Database tier:
perl /appsutil/clone/bin/adclonectx.pl retrieve
On being prompted for the context file to be retrieved, select the option of retrieving the Applications tier context file that has been lost.
While confirming the location for the context file, set it to any existing directory with write permission.
Once the context file has been generated in the specified location, move it to the location specified for the context file in the context variable ‘s_contextfile’.
perl /clone/bin/adclonectx.pl retrieve
On being prompted for the context file to be retrieved, select the option of retrieving the Applications tier context file that has been lost and retrieve it to the default location specified by the script.
The above command can be used only when INST_TOP the is still intact. In case the has also been lost accidentally, the Applications tier context file may be retrieved as follows:
Execute the following command on the Database tier:
perl /appsutil/clone/bin/adclonectx.pl retrieve
On being prompted for the context file to be retrieved, select the option of retrieving the Applications tier context file that has been lost.
While confirming the location for the context file, set it to any existing directory with write permission.
Once the context file has been generated in the specified location, move it to the location specified for the context file in the context variable ‘s_contextfile’.
Friday, February 4, 2011
Check version of a file in Oracle Apps
How to know the version of a file in Oracle Apps ??????
This is a common question in interviews...
Just type
adident Header <FILE LOCATION>
This is a common question in interviews...
Just type
adident Header <FILE LOCATION>
Applying a Patch
Hi, in this post, I will explain how to apply a patch to oracle applications and patching procedure.
Consider you have 3-node architecture (1-db; 1-concurrent node(db-ap), 1-AP node).
If you want to apply patch 6824767, follow these steps:
1. Shutdown db-ap and ap nodes and keep db and db listener up.
2. Take Invalid object List before patching.
COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
3. Enable maintenance mode using adadmin.
4. Apply patch on db-ap node.
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults_file.txt options=noautoconfig,nomaintainmrc,nocompilejsp \
patchtop=/patch/6824767 driver=u6824767.drv logfile=eap1_6824767.log workers=8
5. Apply patch on ap node.
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults_file.txt options=noautoconfig,nomaintainmrc,nocompilejsp \
patchtop=/patch/6824767 driver=u6824767.drv logfile=eap1_6824767.log workers=8
6. After applying the patch, again check for invalid objects and compare it with the before applying patch result.
If any new invalid found then run adadmin and compile apps schema.
7. Disable maintenance mode using adadmin.
8. Start db-ap and ap nodes.
9. Do health check by logging into front-end and by submitting a simple concurrent request like "print environment variables".
that's all.........U r done with it
Consider you have 3-node architecture (1-db; 1-concurrent node(db-ap), 1-AP node).
If you want to apply patch 6824767, follow these steps:
1. Shutdown db-ap and ap nodes and keep db and db listener up.
2. Take Invalid object List before patching.
COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
3. Enable maintenance mode using adadmin.
4. Apply patch on db-ap node.
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults_file.txt options=noautoconfig,nomaintainmrc,nocompilejsp \
patchtop=/patch/6824767 driver=u6824767.drv logfile=eap1_6824767.log workers=8
5. Apply patch on ap node.
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults_file.txt options=noautoconfig,nomaintainmrc,nocompilejsp \
patchtop=/patch/6824767 driver=u6824767.drv logfile=eap1_6824767.log workers=8
6. After applying the patch, again check for invalid objects and compare it with the before applying patch result.
If any new invalid found then run adadmin and compile apps schema.
7. Disable maintenance mode using adadmin.
8. Start db-ap and ap nodes.
9. Do health check by logging into front-end and by submitting a simple concurrent request like "print environment variables".
that's all.........U r done with it
Find more than 1GB files in Unix
You can find more than 1GB files, which are there in the file system using this command in UNIX. This typically requires if your mount got full or near to 100% usage. Using this command, you can see what are the big files in that mount point and based on the importance you can delete some files to get rid of mount point alert.
find . -size +100000 -exec ls -lrt {} \;
find . -size +100000 -exec ls -lrt {} \;
Get the patch numbers from merged patch
In this post, I will explain you how to get patch numbers from ad_applied_patches when you previousply applied merged patch.
You can apply multiple patches in oracle applications using merged patch utility. If you apply merged patch, it will be updated in the ad_applied_patches table with patch_name like 'merged,' but it won't show what are the patches you have applied previously and it's numbers. To know what are the patches you have merged, see the procedure here:
First Step
1. Query the ad_applied_patches with the following the details:
select APPLIED_PATCH_ID,PATCH_NAME,MERGE_DATE from ad_applied_patches where PATCH_NAME like '%merg%';
2. Now using patch_id from 1st step, get driver_id from AD_PATCH_DRIVERS table.
select PATCH_DRIVER_ID,APPLIED_PATCH_ID,DRIVER_FILE_NAME,MERGED_DRIVER_FLAG from AD_PATCH_DRIVERS where APPLIED_PATCH_ID like '524';
3. Now, using driver_id from 2nd step, get bug_id from AD_COMPRISING_PATCHES table.
select COMPRISING_PATCH_ID,BUG_ID from AD_COMPRISING_PATCHES where PATCH_DRIVER_ID like '562';
4. Now using Bug_id from the 3rd step, get details of patches applied as part of a merged patch from ad_pugs table.
select BUG_NUMBER,CREATION_DATE,BUG_ID from ad_bugs where BUG_ID in ('118552','118532','118522','118510');
Now, you can see what are the bugs you have fixed..............
You can apply multiple patches in oracle applications using merged patch utility. If you apply merged patch, it will be updated in the ad_applied_patches table with patch_name like 'merged,' but it won't show what are the patches you have applied previously and it's numbers. To know what are the patches you have merged, see the procedure here:
First Step
1. Query the ad_applied_patches with the following the details:
select APPLIED_PATCH_ID,PATCH_NAME,MERGE_DATE from ad_applied_patches where PATCH_NAME like '%merg%';
2. Now using patch_id from 1st step, get driver_id from AD_PATCH_DRIVERS table.
select PATCH_DRIVER_ID,APPLIED_PATCH_ID,DRIVER_FILE_NAME,MERGED_DRIVER_FLAG from AD_PATCH_DRIVERS where APPLIED_PATCH_ID like '524';
3. Now, using driver_id from 2nd step, get bug_id from AD_COMPRISING_PATCHES table.
select COMPRISING_PATCH_ID,BUG_ID from AD_COMPRISING_PATCHES where PATCH_DRIVER_ID like '562';
4. Now using Bug_id from the 3rd step, get details of patches applied as part of a merged patch from ad_pugs table.
select BUG_NUMBER,CREATION_DATE,BUG_ID from ad_bugs where BUG_ID in ('118552','118532','118522','118510');
Now, you can see what are the bugs you have fixed..............
Thursday, February 3, 2011
JRE Upgrade
Hi, in this post I will explain how to upgrade JRE in oracle applications
Steps To Upgrade JRE
1. Download the JRE 6 (JRE 1.6.0_X) Plug-in http://java.sun.com/javase/downloads/index.jsp
jre-6u10-windows-i586-p.exe
2. Rename the JRE Plug-in and Place it on Webserver
Rename the downloaded JRE Native plug-in file from jre-6_uX-windows-i586-p.exe to j2se160X.exe
jre-6u10-windows-i586-p.exe to j2se16010.exe
3. Place the JRE 6 Plug-in on the Web Application Tier
Move the j2se160X.exe file to the web application tier and place it in [COMMON_TOP]/util/jinitiator. ie cp j2se16010.exe %COMMON_TOP%/util/jinitiator
4. Shutdown apache or complete application tier
5. Enable maintenance mode
6. Apply the JRE plug-in Interoperability Patch 6863618 by following the steps below. English and Arabic
7. Run the txkSetPlugin.sh Script
You can also visit Steven Chan's post for more details about JRE
Thank u...
Steps To Upgrade JRE
1. Download the JRE 6 (JRE 1.6.0_X) Plug-in http://java.sun.com/javase/downloads/index.jsp
jre-6u10-windows-i586-p.exe
2. Rename the JRE Plug-in and Place it on Webserver
Rename the downloaded JRE Native plug-in file from jre-6_uX-windows-i586-p.exe to j2se160X.exe
jre-6u10-windows-i586-p.exe to j2se16010.exe
3. Place the JRE 6 Plug-in on the Web Application Tier
Move the j2se160X.exe file to the web application tier and place it in [COMMON_TOP]/util/jinitiator. ie cp j2se16010.exe %COMMON_TOP%/util/jinitiator
4. Shutdown apache or complete application tier
5. Enable maintenance mode
6. Apply the JRE plug-in Interoperability Patch 6863618 by following the steps below. English and Arabic
7. Run the txkSetPlugin.sh Script
You can also visit Steven Chan's post for more details about JRE
Thank u...
Oracle Database Inventory Details
What is Oracle Database Inventory and where it is located?
Oracle Inventory is the location or the place where all the information about an Oracle Home is stored and maintained. When ever we install an RDBMS Oracle Home, a new inventory gets created. Applying any new patch to Oracle Home will update the invnrtory for that Oracle Home and record the details for the patch applied. This inventory is in the form of XML files.
The location of inventory is defined in a file called oraInst.loc. The path for this file is provided while installing Oracle Home. If we dont supply any path, the is file will be present at central location /etc/oraInst.loc. Also we can have a central inventory if its not used by any other oracle installation. If the central inventory is used by previous Oracle installation we can create local inventory. The content of oraInst.loc file is as shown below.
$ cd $ORACLE_HOME
$ cat oraInst.loc
inventory_loc=/slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInventory
inst_group=ems2029
$
Here “inventory_loc” gives the location of Inventory directory where as inst_group gives the name of the group which is the owner of this inventory.
How to create Local Inventory?
You can create local inventory at any location while installing Oracle Home. You need to use -invPtrLoc variable along with runInstaller command and give the location of oraInst.loc. Otherwise the default path assumed for oraInst.loc is /etc/oraInst.loc.
./runInstaller -invPtrLoc /slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInst.loc
After the installation starts it will ask for location of oraInventory directory and group which should own it. It will make entry of these into oraInst.loc file.
Oracle Inventory is the location or the place where all the information about an Oracle Home is stored and maintained. When ever we install an RDBMS Oracle Home, a new inventory gets created. Applying any new patch to Oracle Home will update the invnrtory for that Oracle Home and record the details for the patch applied. This inventory is in the form of XML files.
The location of inventory is defined in a file called oraInst.loc. The path for this file is provided while installing Oracle Home. If we dont supply any path, the is file will be present at central location /etc/oraInst.loc. Also we can have a central inventory if its not used by any other oracle installation. If the central inventory is used by previous Oracle installation we can create local inventory. The content of oraInst.loc file is as shown below.
$ cd $ORACLE_HOME
$ cat oraInst.loc
inventory_loc=/slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInventory
inst_group=ems2029
$
Here “inventory_loc” gives the location of Inventory directory where as inst_group gives the name of the group which is the owner of this inventory.
How to create Local Inventory?
You can create local inventory at any location while installing Oracle Home. You need to use -invPtrLoc variable along with runInstaller command and give the location of oraInst.loc. Otherwise the default path assumed for oraInst.loc is /etc/oraInst.loc.
./runInstaller -invPtrLoc /slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInst.loc
After the installation starts it will ask for location of oraInventory directory and group which should own it. It will make entry of these into oraInst.loc file.
OPATCH AND IT'S OPTIONS
This post will explain about OPATCH and it's options.
What is Opatch?
OPatch is a java based utility.
How to check the Opatch version?
$ ./opatch version
Invoking OPatch 10.2.0.3.0
OPatch Version: 10.2.0.3.0
OPatch succeeded.
How to know which version is the correct version of Opatch for your RDBMS home?
You can verify whether the Opatch version for your RDBMS release is correct or not using metalink note ID 357221.1.
This note ID gives the copatibilities between OPatch version and RDBMS version.
How to get the latest version of OPatch?
You can download the latest version of OPatch from metalink using following URL.
http://updates.oracle.com/download/6880880.html
In the release dropdown you can select the OPatch release that you want to download based on your RDBMS version. Please read the README.txt before applying the patch.
Opatch help
You can get all the options using opatch -help
$ ./opatch -help
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Usage: opatch [ -help ] [ -r[eport] ] [ command ]
apply
lsinventory
prereq
query
rollback
util NApply
version
<global_arguments> := -help Displays the help message for the command.
-report Print the actions without executing.
How to apply a single patch using opatch
If you are applying a single intrim patch to oracle home, then change the location to the directory of that patch and then invoke following command.
Example if I need to apply patch 6972343 to RDBMS Oracle Home
-bash-3.00$ cd 6972343
-bash-3.00$ pwd
/slot/ems2029/oracle/db/tech_st/11.1.0/patches/6972343
-bash-3.00$ ls
etc files README.txt
$ORACLE_HOME/OPatch/opatch apply
If you have created local inventory then you need to give the correct path for local inventory stored in oraInst.loc. If you have updated /etc/oraInst.loc with correct location of your inventory, then above command will work, If not you can give the correct location by giving local oraInst.loc file.
$ORACLE_HOME/OPatch/opatch apply -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
Remember that when we are applying patch, patch number is NOT provided as an input to opatch command. So we have to to be in the directory of patch since opatch will pick the patch files from current directory.
Rolling back a patch
In case if a patch did not fix the issue or if you has applied wrong patch to oracle home, you can always rollback the patch using following comamnd.
opatch rollback -id <Patch Number>
Applying bundle patches
Some times if you have to apply bundle patch having many patches, example lets say you want to a bundle patch 6778860 (11.1.0.6 BUNDLED PATCH FOR EBS 11I)) containing many other patches to RDBMS home, OPatch provides a facility to apply many patches to RDBMS home using a single comamnd. Following command should be used for applying bundle patches.
Download the patch and extract the same. You will see many directories (one for each patch) inside the main patch directory. Execute following command to apply all patches.
$ORACLE_HOME/OPatch/opatch util NApply -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
NApply -> Will apply all patches to RDBMS home.
You can also use -phBaseDir to point to the directory where bundle patch is installed. So you can run opatch command from any location
$ORACLE_HOME/OPatch/opatch util NApply -phBaseDir /slot/ems2029/oracle/db/tech_st/11.1.0/patches/6778860 -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
You can also apply specific patches using NApply
opatch util napply <patch_location> -id 1,2,3 -skip_subset -skip_duplicate
This will apply patches 1, 2, and 3 which are under < the patch_location> directory. OPatch will skip duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the ORACLE_HOME)
You can see all the options for NApply using following help command.
$ORACLE_HOME/OPatch/opatch util NApply -help
Query the inventory for patches applied
We can query the inventory to check various components installed for database as well as to check various patches applied to database. Use following command to get a minimum information from inventory about patches applied and components installed.
$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
Patch 5763576 : applied on Wed May 28 03:20:53 PDT 2008
Created on 6 Feb 2008, 02:26:04 hrs PST8PDT
Bugs fixed:
5763576
If you are using central inventory then -invPtrLoc variable is not required.
You can also get the detailed view of inventory using following command. In case of detail information it gives which are the files that this patch have touched.
$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
Patch 6318357 : applied on Wed May 28 03:33:27 PDT 2008
Created on 4 Dec 2007, 22:02:16 hrs PST8PDT
Bugs fixed:
6318357
Files Touched:
udjvmrm.sql –> ORACLE_HOME/javavm/install/udjvmrm.sql
Patch Location in Inventory:
/slot/ems2029/oracle/db/tech_st/11.1.0/inventory/oneoffs/6318357
Patch Location in Storage area:
/slot/ems2029/oracle/db/tech_st/11.1.0/.patch_storage/6318357_Dec_4_2007_22_02_16
Which options are installed in Oracle Home?
You can check the options installed in RDBMS home using above command. The options installed will be listed at the start of output.
Example: $ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
The complete output of this command can be seen at this location.
Opatch Log files
Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch
What is Opatch?
OPatch is a java based utility.
How to check the Opatch version?
$ ./opatch version
Invoking OPatch 10.2.0.3.0
OPatch Version: 10.2.0.3.0
OPatch succeeded.
How to know which version is the correct version of Opatch for your RDBMS home?
You can verify whether the Opatch version for your RDBMS release is correct or not using metalink note ID 357221.1.
This note ID gives the copatibilities between OPatch version and RDBMS version.
How to get the latest version of OPatch?
You can download the latest version of OPatch from metalink using following URL.
http://updates.oracle.com/download/6880880.html
In the release dropdown you can select the OPatch release that you want to download based on your RDBMS version. Please read the README.txt before applying the patch.
Opatch help
You can get all the options using opatch -help
$ ./opatch -help
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Usage: opatch [ -help ] [ -r[eport] ] [ command ]
apply
lsinventory
prereq
query
rollback
util NApply
version
<global_arguments> := -help Displays the help message for the command.
-report Print the actions without executing.
example:
‘opatch -help’
‘opatch apply -help’
‘opatch lsinventory -help’
‘opatch prereq -help’
‘opatch rollback -help’
‘opatch util -help’
'opatch util NApply-help
How to apply a single patch using opatch
You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
Make sure you have a good backup of database.
Make a note of all Invalid objects in the database prior to the patch.
Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
You MUST Backup your oracle Home and Inventory
Unzip the patch in $ORACLE_HOME/patches
If you are applying a single intrim patch to oracle home, then change the location to the directory of that patch and then invoke following command.
Example if I need to apply patch 6972343 to RDBMS Oracle Home
-bash-3.00$ cd 6972343
-bash-3.00$ pwd
/slot/ems2029/oracle/db/tech_st/11.1.0/patches/6972343
-bash-3.00$ ls
etc files README.txt
$ORACLE_HOME/OPatch/opatch apply
If you have created local inventory then you need to give the correct path for local inventory stored in oraInst.loc. If you have updated /etc/oraInst.loc with correct location of your inventory, then above command will work, If not you can give the correct location by giving local oraInst.loc file.
$ORACLE_HOME/OPatch/opatch apply -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
Remember that when we are applying patch, patch number is NOT provided as an input to opatch command. So we have to to be in the directory of patch since opatch will pick the patch files from current directory.
Rolling back a patch
In case if a patch did not fix the issue or if you has applied wrong patch to oracle home, you can always rollback the patch using following comamnd.
opatch rollback -id <Patch Number>
Applying bundle patches
Some times if you have to apply bundle patch having many patches, example lets say you want to a bundle patch 6778860 (11.1.0.6 BUNDLED PATCH FOR EBS 11I)) containing many other patches to RDBMS home, OPatch provides a facility to apply many patches to RDBMS home using a single comamnd. Following command should be used for applying bundle patches.
Download the patch and extract the same. You will see many directories (one for each patch) inside the main patch directory. Execute following command to apply all patches.
$ORACLE_HOME/OPatch/opatch util NApply -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
NApply -> Will apply all patches to RDBMS home.
You can also use -phBaseDir to point to the directory where bundle patch is installed. So you can run opatch command from any location
$ORACLE_HOME/OPatch/opatch util NApply -phBaseDir /slot/ems2029/oracle/db/tech_st/11.1.0/patches/6778860 -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
You can also apply specific patches using NApply
opatch util napply <patch_location> -id 1,2,3 -skip_subset -skip_duplicate
This will apply patches 1, 2, and 3 which are under < the patch_location> directory. OPatch will skip duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the ORACLE_HOME)
You can see all the options for NApply using following help command.
$ORACLE_HOME/OPatch/opatch util NApply -help
Query the inventory for patches applied
We can query the inventory to check various components installed for database as well as to check various patches applied to database. Use following command to get a minimum information from inventory about patches applied and components installed.
$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
Patch 5763576 : applied on Wed May 28 03:20:53 PDT 2008
Created on 6 Feb 2008, 02:26:04 hrs PST8PDT
Bugs fixed:
5763576
If you are using central inventory then -invPtrLoc variable is not required.
You can also get the detailed view of inventory using following command. In case of detail information it gives which are the files that this patch have touched.
$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
Patch 6318357 : applied on Wed May 28 03:33:27 PDT 2008
Created on 4 Dec 2007, 22:02:16 hrs PST8PDT
Bugs fixed:
6318357
Files Touched:
udjvmrm.sql –> ORACLE_HOME/javavm/install/udjvmrm.sql
Patch Location in Inventory:
/slot/ems2029/oracle/db/tech_st/11.1.0/inventory/oneoffs/6318357
Patch Location in Storage area:
/slot/ems2029/oracle/db/tech_st/11.1.0/.patch_storage/6318357_Dec_4_2007_22_02_16
Which options are installed in Oracle Home?
You can check the options installed in RDBMS home using above command. The options installed will be listed at the start of output.
Example: $ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
The complete output of this command can be seen at this location.
Opatch Log files
Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch
FNDCPASS usage in Oracle Applications
FNDCPASS SYNTAX AND TROUBLE SHOOTING
In Oracle Applications, we have an FND functionality for changing the passwords for application users, product schema passwords, and “APPS and APPLSYS.” The location of FNDCPASS utility is $FND_TOP/bin directory.
FNDCPASS Usage:
FNDCPASS logon 0 Y system/password mode username new_password
where logon is username/password[@connect]
system/password is password of the system account of that database
mode is SYSTEM/USER/ORACLE
username is the username where you want to change its password
new_password is the new password in unencrypted format
example FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME
If you just type FNDCPASS and press enter, it will give you these details.
The first usage
FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
is for changing the password for apps and applsys. These are the database schema users (most important for application to work). Password for both these users should be in synch. You can change the password of these users using this command. Note that this is the only way to change the password for apps and applsys. Please do not try any other method for changing apps and applsys password. Oracle recomends using FNDCPASS only to change apps and applsys password. Also note that using this command will change the password for both apps and applsys.
Following activities will take place
(1) applsys validation. (make sure APPLSYS name is correct)
(2) re-encrypt all password in FND_USER
(3) re-encrypt all password in FND_ORACLE_USERID
(4) update applsys’s password in FND_ORACLE_USERID table.
(5) Update apps password in FND_ORACLE_USERID table.
Also changes are made in DBA_USERS table.
The second usage
FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
is for changing password for any other product schema like MSC, GL etc.
Following activities will take place
(1) update GL’s password in FND_ORACLE_USERID table. The new password is re-encrypted with the current applsys password.
If GL does not exists, step (2) below does not happen. Message for invalid oracle user is written in the log file.
(2) alter user to change GL’s password.
The third usage
FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME
is for changing the application level passwords like sysadmin etc used for logging into application.
Following activities will take place
(1) update VISION’s password in FND_USER table. The new password is re-encrypted with the current applsys password.
If VISION does not exist, message for invalid application user is written in the log file.
No products affected by the patch
When you run FNDCPASS command it will check the integrity of all schema password in the application. If any of the password is corrupt then this will through and error and will not change the password.
The tables that it uses is FND_USER and FND_ORACLE_USERID. All the application passwords and schema passwords are stored in these two tables. Ofcourse DBA_USERS will have the schema users and password stored as well.
When we run FNDCPASS it will update all the above 3 tables.
Best usage of FNDCPASS
Before using FNDCPASS:
1) Always keep the back of tables FND_USER and FND_ORACLE_USERID. You can take back of these tables using CREATE TABLE — AS SELECT * FROM —.
You must have backup of these tables before running FNDCPASS. In case if FNDCPASS fails then it might corrupt the passwords of your application and worst can happen that the application wont come up. So always be cautions about this command.
2) If possible also keep an export dump of these two tables.
3) verify each arguement you are providing to FNDCPASS. Like verify that apps and system passwords you are providing is correct.
4) Never update apps, applsys or any schema password directly from database using the alter command. Always use FNDCPASS. System password can be set directly using ALTER command in database.
Issue with APPLSYS and APPS password
Issue 1:
As you know that apps and applsys password should be in synch and should be changed using FNDCPASS.
There can be situation where a novice user changes applsys password from the backend database. In that case when you try to start the services it will show following error
APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password.
You can even reproduce this issue (ofcourse after taking the backup of FND_USER and FND_ORACLE_USERID table) using the following steps
1. Use the ALTER USER command to change the APPLSYS password
2. Try to run the adstrall.sh script to start Apps services.
3. You will get an error “Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.”
4. Then try FNDCPASS to fix password and you will get the error the APP-FND-01496 error.
If this situation happens then you cannot access the application. Infact the services even wont start.
Resolution to such problem is to rollback the 2 tables FND_USER and FND_ORACLE_USERID. Once you rollback the tables, apps and applsys passwords will be in synch and password will be older one. You can then run FNDCPASS and change the password.
Issue 2:
Some times when you run FNDCPASS, you get following error
APP-FND-01502: Cannot encrypt application ORACLE password
Cause: Application Object Library was unable encrypt your ORACLE password.
Action: Contact your support representative. (ORACLEUSER=APPS_SERV)
The error comes because the table fnd_oracle_userid contain rows for schemas that does not exist. Those rows must be deleted from the table.
Use the following query to get the details of the schema that doest not exists
select * from fnd_oracle_userid
where oracle_username not in
(select username from all_users);
The rows returned by this query can be deleted from FND_ORACLE_USERID table. This will resolve this issue.
Issue 3:
There can be situation where users has update APPLSYS password using ALTER command in database directly and also you dont have backup of those tables. Under such situation, it is very difficult to recover the application and make it working. Still following methodology is proposed which might help you to restore the password back and make your application work fine.
For this to work you should have some other application (may be debug or UAT) which is having the same passwords or default passwords for schemas. If you have such application the following the below steps in the application which is affected by password mismatch.
This method is for resetting apps and applsys passwords. Below are the SQL statements that will help you reset the APPS and APPLSYS passwords to APPS, the APPLSYSPUB password to PUB, and the SYSADMIN password to SYSADMIN.
WARNING: This procedure will cause all user passwords to become invalid. ALL users passwords will need to be reset through the sysadmin responsibility.
Step 1) Reset the Oracle User IDs
Open a SQL*Plus as SYSTEM and reset the passwords for the APPS, APPLSYS, and the APPLSYSPUB Oracle user ID:
ALTER USER apps IDENTIFIED BY apps;
ALTER USER applsys IDENTIFIED BY apps;
ALTER USER applsyspub IDENTIFIED BY pub;
Step 2) Backup the FND_ORACLE_USERID and FND_USER tables (even though these tables are right now corrupted, do take a backup. You can restore the same when ever you want).
Open a SQL*Plus session as APPLSYS and backup the tables:
create table FND_ORACLE_USERID_BAK as (select * from FND_ORACLE_USERID);
create table FND_USER_BAK as (select * from FND_USER);
Step 3) Reset the APPS and APPLSYS application encrypted passwords
Open a SQL*Plus session as APPLSYS and update the FND_ORACLE_USERID table.
update FND_ORACLE_USERID
set ENCRYPTED_ORACLE_PASSWORD = ‘ZGA34EA20B5C4C9726CC95AA9D49EA4DBA8EDB705CB7673E645EED570D5447161491D78D444554655B87486EF537ED9843C8′
where ORACLE_USERNAME in (‘APPS’, ‘APPLSYS’);
commit;
This encrypted string we are updating is the default encrypted string for apps. So if your application is having apps password the encrypted string will look like this. We are updating this encrypted string here directly.
Verify the table update:
select ENCRYPTED_ORACLE_PASSWORD
from FND_ORACLE_USERID
where ORACLE_USERNAME IN (‘APPS’, ‘APPLSYS’);
Step 4) Reset the APPLSYSPUB application encrypted password
Open a SQL*Plus session as APPLSYS and update the FND_ORACLE_USERID table.
update FND_ORACLE_USERID
set ENCRYPTED_ORACLE_PASSWORD = ‘ZG31EC3DD2BD7FB8AD2628CE87DDDF148C1D2F248BE88BE987FDF82830228A88EF44BC78BC7A9FAD4BFB8F09DAD49DF7280E’
where ORACLE_USERNAME = (‘APPLSYSPUB’);
commit;
The above encrypted string is the encrypted string for password pub. If your applsyspub password is pub then the encrypted string in FND_ORACLE_USERID will look like this.
Verify the table update:
select ENCRYPTED_ORACLE_PASSWORD
from FND_ORACLE_USERID
where ORACLE_USERNAME = ‘APPLSYSPUB’;
Once these updates are done, try your luck by running FNDCPASS and it should work fine.
References
Metalink note ID 445153.1
Metalink note ID 429244
In Oracle Applications, we have an FND functionality for changing the passwords for application users, product schema passwords, and “APPS and APPLSYS.” The location of FNDCPASS utility is $FND_TOP/bin directory.
FNDCPASS Usage:
FNDCPASS logon 0 Y system/password mode username new_password
where logon is username/password[@connect]
system/password is password of the system account of that database
mode is SYSTEM/USER/ORACLE
username is the username where you want to change its password
new_password is the new password in unencrypted format
example FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME
If you just type FNDCPASS and press enter, it will give you these details.
The first usage
FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
is for changing the password for apps and applsys. These are the database schema users (most important for application to work). Password for both these users should be in synch. You can change the password of these users using this command. Note that this is the only way to change the password for apps and applsys. Please do not try any other method for changing apps and applsys password. Oracle recomends using FNDCPASS only to change apps and applsys password. Also note that using this command will change the password for both apps and applsys.
Following activities will take place
(1) applsys validation. (make sure APPLSYS name is correct)
(2) re-encrypt all password in FND_USER
(3) re-encrypt all password in FND_ORACLE_USERID
(4) update applsys’s password in FND_ORACLE_USERID table.
(5) Update apps password in FND_ORACLE_USERID table.
Also changes are made in DBA_USERS table.
The second usage
FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
is for changing password for any other product schema like MSC, GL etc.
Following activities will take place
(1) update GL’s password in FND_ORACLE_USERID table. The new password is re-encrypted with the current applsys password.
If GL does not exists, step (2) below does not happen. Message for invalid oracle user is written in the log file.
(2) alter user to change GL’s password.
The third usage
FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME
is for changing the application level passwords like sysadmin etc used for logging into application.
Following activities will take place
(1) update VISION’s password in FND_USER table. The new password is re-encrypted with the current applsys password.
If VISION does not exist, message for invalid application user is written in the log file.
No products affected by the patch
When you run FNDCPASS command it will check the integrity of all schema password in the application. If any of the password is corrupt then this will through and error and will not change the password.
The tables that it uses is FND_USER and FND_ORACLE_USERID. All the application passwords and schema passwords are stored in these two tables. Ofcourse DBA_USERS will have the schema users and password stored as well.
When we run FNDCPASS it will update all the above 3 tables.
Best usage of FNDCPASS
Before using FNDCPASS:
1) Always keep the back of tables FND_USER and FND_ORACLE_USERID. You can take back of these tables using CREATE TABLE — AS SELECT * FROM —.
You must have backup of these tables before running FNDCPASS. In case if FNDCPASS fails then it might corrupt the passwords of your application and worst can happen that the application wont come up. So always be cautions about this command.
2) If possible also keep an export dump of these two tables.
3) verify each arguement you are providing to FNDCPASS. Like verify that apps and system passwords you are providing is correct.
4) Never update apps, applsys or any schema password directly from database using the alter command. Always use FNDCPASS. System password can be set directly using ALTER command in database.
Issue with APPLSYS and APPS password
Issue 1:
As you know that apps and applsys password should be in synch and should be changed using FNDCPASS.
There can be situation where a novice user changes applsys password from the backend database. In that case when you try to start the services it will show following error
APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password.
You can even reproduce this issue (ofcourse after taking the backup of FND_USER and FND_ORACLE_USERID table) using the following steps
1. Use the ALTER USER command to change the APPLSYS password
2. Try to run the adstrall.sh script to start Apps services.
3. You will get an error “Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.”
4. Then try FNDCPASS to fix password and you will get the error the APP-FND-01496 error.
If this situation happens then you cannot access the application. Infact the services even wont start.
Resolution to such problem is to rollback the 2 tables FND_USER and FND_ORACLE_USERID. Once you rollback the tables, apps and applsys passwords will be in synch and password will be older one. You can then run FNDCPASS and change the password.
Issue 2:
Some times when you run FNDCPASS, you get following error
APP-FND-01502: Cannot encrypt application ORACLE password
Cause: Application Object Library was unable encrypt your ORACLE password.
Action: Contact your support representative. (ORACLEUSER=APPS_SERV)
The error comes because the table fnd_oracle_userid contain rows for schemas that does not exist. Those rows must be deleted from the table.
Use the following query to get the details of the schema that doest not exists
select * from fnd_oracle_userid
where oracle_username not in
(select username from all_users);
The rows returned by this query can be deleted from FND_ORACLE_USERID table. This will resolve this issue.
Issue 3:
There can be situation where users has update APPLSYS password using ALTER command in database directly and also you dont have backup of those tables. Under such situation, it is very difficult to recover the application and make it working. Still following methodology is proposed which might help you to restore the password back and make your application work fine.
For this to work you should have some other application (may be debug or UAT) which is having the same passwords or default passwords for schemas. If you have such application the following the below steps in the application which is affected by password mismatch.
This method is for resetting apps and applsys passwords. Below are the SQL statements that will help you reset the APPS and APPLSYS passwords to APPS, the APPLSYSPUB password to PUB, and the SYSADMIN password to SYSADMIN.
WARNING: This procedure will cause all user passwords to become invalid. ALL users passwords will need to be reset through the sysadmin responsibility.
Step 1) Reset the Oracle User IDs
Open a SQL*Plus as SYSTEM and reset the passwords for the APPS, APPLSYS, and the APPLSYSPUB Oracle user ID:
ALTER USER apps IDENTIFIED BY apps;
ALTER USER applsys IDENTIFIED BY apps;
ALTER USER applsyspub IDENTIFIED BY pub;
Step 2) Backup the FND_ORACLE_USERID and FND_USER tables (even though these tables are right now corrupted, do take a backup. You can restore the same when ever you want).
Open a SQL*Plus session as APPLSYS and backup the tables:
create table FND_ORACLE_USERID_BAK as (select * from FND_ORACLE_USERID);
create table FND_USER_BAK as (select * from FND_USER);
Step 3) Reset the APPS and APPLSYS application encrypted passwords
Open a SQL*Plus session as APPLSYS and update the FND_ORACLE_USERID table.
update FND_ORACLE_USERID
set ENCRYPTED_ORACLE_PASSWORD = ‘ZGA34EA20B5C4C9726CC95AA9D49EA4DBA8EDB705CB7673E645EED570D5447161491D78D444554655B87486EF537ED9843C8′
where ORACLE_USERNAME in (‘APPS’, ‘APPLSYS’);
commit;
This encrypted string we are updating is the default encrypted string for apps. So if your application is having apps password the encrypted string will look like this. We are updating this encrypted string here directly.
Verify the table update:
select ENCRYPTED_ORACLE_PASSWORD
from FND_ORACLE_USERID
where ORACLE_USERNAME IN (‘APPS’, ‘APPLSYS’);
Step 4) Reset the APPLSYSPUB application encrypted password
Open a SQL*Plus session as APPLSYS and update the FND_ORACLE_USERID table.
update FND_ORACLE_USERID
set ENCRYPTED_ORACLE_PASSWORD = ‘ZG31EC3DD2BD7FB8AD2628CE87DDDF148C1D2F248BE88BE987FDF82830228A88EF44BC78BC7A9FAD4BFB8F09DAD49DF7280E’
where ORACLE_USERNAME = (‘APPLSYSPUB’);
commit;
The above encrypted string is the encrypted string for password pub. If your applsyspub password is pub then the encrypted string in FND_ORACLE_USERID will look like this.
Verify the table update:
select ENCRYPTED_ORACLE_PASSWORD
from FND_ORACLE_USERID
where ORACLE_USERNAME = ‘APPLSYSPUB’;
Once these updates are done, try your luck by running FNDCPASS and it should work fine.
References
Metalink note ID 445153.1
Metalink note ID 429244
Wednesday, February 2, 2011
Oracle Apps 11.5.10 Installation on OEL 4.7 IN VMWARE
THIS POST WILL HELP U TO INSTALL ORACLE APPS 11.5.10 ON OEL 4.7
Unzip files into /soft directory in OEL 4.7
Apply the required rpms after installing OEL4.7 in VMware
rpm -Uvh compat-libcwait-2.1-1.i386.rpm
rpm -Uvh compat-oracle-rhel4-1.0-5.i386.rpm
rpm -Uvh libaio-0.3.105-2.i386.rpm
Create directories for installation as a root user
mkdir ora
mkdir apps
create group and OS user
groupadd dba
useradd oracle -g dba
Give permission to user to access those directories
chmod -R oracle:dba /ora
chmod -R oracle:dba /apps
Check that the /etc/hosts entries are as follows
127.0.0.1 localhost.localdomain localhost
<ip address> <hostname>.<domainname> aliasname
Entries should be as follows
127.0.0.1 localhost.localdomain localhost
192.169.9.99 learn.com learn
Download OEL 4.7 from Oracle E-Delivery site
Download Oracle Apps 11.5.10 from Oracle E-Delivey Site
Unzip files into /soft directory in OEL 4.7
Apply the required rpms after installing OEL4.7 in VMware
rpm -Uvh compat-libcwait-2.1-1.i386.rpm
rpm -Uvh compat-oracle-rhel4-1.0-5.i386.rpm
rpm -Uvh libaio-0.3.105-2.i386.rpm
Create directories for installation as a root user
mkdir ora
mkdir apps
create group and OS user
groupadd dba
useradd oracle -g dba
Give permission to user to access those directories
chmod -R oracle:dba /ora
chmod -R oracle:dba /apps
Check that the /etc/hosts entries are as follows
127.0.0.1 localhost.localdomain localhost
<ip address> <hostname>.<domainname> aliasname
Entries should be as follows
127.0.0.1 localhost.localdomain localhost
192.169.9.99 learn.com learn
Oracle Applications 11.5.10 Installation on OEL 4.7 in VMWare- Single Node
go to cd /soft/StartCD/Disk1/rapidwiz
./rapidwiz
go to cd /soft/StartCD/Disk1/rapidwiz
./rapidwiz
Subscribe to:
Comments (Atom)