Monday, May 16, 2011

Find component Version in Apps 11i / R12 / 12i

How to find Apps Version
select release_name from apps.fnd_product_groups;

Web Server/Apache or Application Server in Apps 11i/R12
Log in as Application user, set environment variable and run below query $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version

Forms & Report version in R12/12i
Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release

 Oracle Jinitiator in 11i/R12/12i
Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE

(Default is Java Plug-In for R12/12i )
Oracle Java Plug-in in 11i/R12/12i
Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE.

File Version on file system
adident Header
strings | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility.

Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx.  *.pll is in $AU_TOP/resource and to find its version check
adident Header $AU_TOP/resource/.pll
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
strings $AU_TOP/resource/.pll | grep -i header
FDRCSID(’$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);

Workflow Version with Apps
select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;

Identity Management component Version/Release Number
Oracle Single Sign On
select version from orasso.wwc_version$;
Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)
To find software/binary version
$ORACLE_HOME/bin/oidldapd -version
To find Schema Version/ database use
ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \ -s base “objectclass=*” orcldirectoryversion 
select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;

Application Server
Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ | grep Version
For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database
select * from ias_versions;
AOC4J (Oracle Container for J2EE)
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version 

Oracle Portal
select version from portal.wwc_version$; 

Database Component 
To find database version
select * from v$version;
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail
Unix Operating System
Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release

Sukhwinder Singh

How to find the locked objects and Kill the Session in Oracle

Step-1 Run the following SQL query to find out the list of objects that has been locked

SELECT aob.object_name
FROM all_objects aob, v$locked_object b
WHERE aob.object_id = b.object_id


SELECT aob.object_name
FROM all_objects aob, v$locked_object b
,V$session a
WHERE aob.object_id = b.object_id
and a.sid=b.session_id ;

Step-2 Now run the following SQL query with session id (from step-1)

Note <SID> <SERIAL#>

Step-3 Run the following Query to kill the session with session_id and Serial no (from step-2)


Sukhwinder Singh

Configure Parallel Concurrent Processing

1 Check prerequisites for setting up Parallel Concurrent Processing

To set up Parallel Concurrent Processing (PCP), you must have more than one Concurrent Processing node in your environment

2 Set Up PCP

1. Edit the applications context file via Oracle Applications Manager, and set the value of the variable APPLDCP to ON.

2. Execute AutoConfig by running the following command on all concurrent processing nodes:

$ $INST_TOP/admin/scripts/

3. Source the Applications environment.

4. Check the tnsnames.ora and listener.ora configuration files, located in $INST_TOP/ora/10.1.2/network/admin. Ensure that the required FNDSM and FNDFS entries are present for all other concurrent nodes.

5. Restart the Applications listener processes on each application tier node.

6. Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator Responsibility. Navigate to Install > Nodes screen, and ensure that each node in the cluster is registered.

7. Verify that the Internal Monitor for each node is defined properly, with correct primary and secondary node specification, and work shift details. For example, Internal Monitor: Host2 must have primary node as host2 and secondary node as host3. Also ensure that the Internal Monitor manager is activated: this can be done from Concurrent > Manager > Administrator.

8. Set the $APPLCSF environment variable on all the Concurrent Processing nodes to point to a log directory on a shared file system.

9. Set the $APPLPTMP environment variable on all the CP nodes to the value of the UTL_FILE_DIR entry in init.ora on the database nodes. (This value should be pointing to a directory on a shared file system.)

10. Set profile option 'Concurrent: PCP Instance Check' to OFF if database instance-sensitive failover is not required. By setting it to 'ON', a concurrent manager will fail over to a secondary Application tier node if the database instance to which it is connected becomes unavailable for some reason.

3 Set Up Transaction Managers ,Internal Concurrent Manager ,Standard Manager

1. Shut down the application services (servers) on all nodes

2. Shut down all the database instances cleanly in the Oracle RAC environment, using the command:

SQL>shutdown immediate;

3. Edit $ORACLE_HOME/dbs/_ifile.ora. Add the following parameters:

+ _lm_global_posts=TRUE
+ _immediate_commit_propagation=TRUE

4. Start the instances on all database nodes, one by one.

5. Start up the application services (servers) on all nodes.

6. Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator responsibility. Navigate to Profile > System, change the profile option ‘Concurrent: TM Transport Type' to ‘QUEUE', and verify that the transaction manager works across the Oracle RAC instance.

7. Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction managers.

8. Restart the concurrent managers.

9. If any of the transaction managers are in deactivated status, activate them from Concurrent > Manager > Administrator.

Transaction Manager (Internal use only)

Standard Manager

Internal Concurrent Manager

Internal Monitor for Apps-1

Internal Monitor for Apps-2

CRP Manager

4 Set Up Load Balancing on Concurrent Processing Nodes

1. Edit the applications context file through the Oracle Applications Manager interface, and set the value of Concurrent Manager TWO_TASK (s_cp_twotask) to the load balancing alias (OURPROD_balance>).

Note: Windows users must set the value of "Concurrent Manager TWO_TASK" (s_cp_twotask context variable) to the instance alias.

2. Execute AutoConfig by running $INST_TOP/admin/scripts/ on all concurrent nodes.


Then restart your application tier it will work .

Sukhwinder singh

How to identify all the Corrupted Objects in the Database reported by RMAN

Step 1: Identify the corrupt blocks

Populate the v$database_block_corruption view with  information of all the corrupted blocks by executing the following command from RMAN:
RMAN> backup validate check logical database;

To make it faster, itt can be configured to use PARALLELISM with multiple channels:

RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;


RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;

The corrupted blocks are listed in the view v$database_block_corruption:
select * from v$database_block_corruption;

Step 2: Identify the corrupt segments :
The next query can be run to map each block to a segment in the database.  It will map each block from v$database_block_corruption to either a segment or if the block is free.

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

Sukhwinder Singh
There was an error in this gadget