Saturday, December 18, 2010

Oracle Database Useful Queries - Scripts - I

Oracle Database Useful Queries - Scripts - I
---------------------------------------------

The below scripts are useful for DBA's day to day Monitoring activities.


1) To get sql text of a session

break on hash_value
set pagesize 1000
set long 200000
select hash_value, sql_text
from v$sqltext
where hash_value in
(select sql_hash_value from
v$session where sid = &1)
order by piece
/

2) To get why a session is waiting

col event for a25 word_wrap;
select a.event event, a.seconds_in_wait, s.status
from v$session_wait a, v$session s
where a.sid=s.sid
and a.sid=&1
/

3) To get session information, you can replace the last condition in where clause as per the requirement ( to sid or client process id or client terminal etc)

select ' Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||
s.audsid||chr(10)|| ' DB User / OS User : '||s.username||
' / '||s.osuser||chr(10)|| ' Machine - Terminal : '||
s.machine||' - '|| s.terminal||chr(10)||
' OS Process Ids : '||
s.process||' (Client) '||p.spid||' (Server)'|| ' (Since) '||
to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS')||chr(10)||
' Client Program Name : '||s.program||chr(10) ||
' Action / Module : '||s.action||' / '||s.module||chr(10) || chr(10) ||
' Wait Status : '||s.event || ' ' || s.seconds_in_wait || ' ' || s.state "Session Info"
from v$process p,v$session s
where p.addr = s.paddr
and p.spid=&1

4) To get lock details with holders and waiters

SELECT lpad('-->',DECODE(a.request,0,0,5),' ')||a.sid sess
, a.id1
, a.id2
, a.lmode
, a.request req, a.type
, b.event
, b.seconds_in_wait
FROM V$LOCK a, v$session_wait b
WHERE a.id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
and a.sid=b.sid
ORDER BY id1,request
/

5) To get row information of a session, if the information is changing that means the session is actually active else it means inactive

column name format a30 word_wrapped
column vlu format 999,999,999,999
select b.name, a.value vlu
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and sid =&1
and a.value != 0
and b.name like '%row%'
/

6) Rollback segment usage by running sessions in the database

set pages 1000
set lines 132
col name format a15 wrap
col username format a10
col module format a12
col space_used format 9,999,999,999,999
select a.username,a.sid,r.name,b.start_time,a.module,a.action, (b.used_ublk * 8192) space_used
from v$session a, v$transaction b,v$rollname r
where a.saddr=b.ses_addr
and b.xidusn = r.usn
order by 7
/

7) To get the details of long running sessions in the database, it gives info on how much work completed, how much is left and also the time it takes to complete

select SID,TARGET,SOFAR,TOTALWORK,UNITS,TIME_REMAINING,ELAPSED_SECONDS from v$session_longops
/

8) To find the jobs running under all concurrent managers in the instance, used for monitoring if requests are getting backedup under any manager

set linesize 140
col CONTAINER_NAME for a28
col PROCID for 999999
col PROCID for a10
col TARGET for a6
col TARGET for 999
col ACTUAL for a6
col ACTUAL for 999
col ENABLED for a7
col COMPONENT_NAME for a30
col STARTUP_MODE for a12
col COMPONENT_STATUS for a17
select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name,
DECODE(fcp.OS_PROCESS_ID,NULL,'Not Running',fcp.OS_PROCESS_ID) PROCID, fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL,fcq.ENABLED_FLAG ENABLED,fsc.COMPONENT_NAME,fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES fcp,
fnd_svc_components fsc where fcq.MANAGER_TYPE = fcs.SERVICE_ID and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+) and fcp.process_status_code(+) = 'A'
and fcq.USER_CONCURRENT_QUEUE_NAME like '%Mail%' order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE
/

9) To get information on a single concurrent request id

select r.request_id,
r.oracle_process_id,
r.oracle_session_id,
r.os_process_id,
s.sid,
s.serial#,
s.paddr
from fnd_concurrent_requests r,
v$session s
where request_id = &1
and r.oracle_session_id = s.audsid(+)
/


10)To find out the Temp Tablespace Usage:

set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB, (total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;

11)TO FIND OUT THE SID,SPID,CLIENT PID when one of them given

col program for a15
col machine for a15
col terminal for a15
col sid for 9999
col serial# for 9999999
col action for a40
set lines 152

select s.sid,
s.serial#,
'*'||s.process||'*' Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.program || s.module,
s.action,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600 from gv$session s, gv$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));


12)

REM checking Timing details, Client PID of associated oracle SID
REM ============================================================
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));



12)Finding Locks On a Particler Object

select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id
and c.object_name like 'MTL%';


How to Disable(end date) large number of Users in Oracle Apps

How to end date large number of Users - fnd_users in Oracle Apps

To Disable/Enable bulk number of users in oracle Applications, we have a API

apps.fnd_user_pkg.EnableUser =>To Enable Users
apps.fnd_user_pkg.DisableUser =>To Disable Users,

Below is the syntax which can used to Huge number of Users in one GO !!
-------------------------------------------------------------------------------------

declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) Not IN ('username','username', .......);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.EnableUser(all_user.user_name);
commit;
end loop;
End

Database Upgrade to 10.2.0.4

Database Upgrade 10G to 10.2.0.4 - DB Upgrade to 10.2.0.4::

Please fine the below steps which will outline 10.2.0.4 DB upgrade action plan.


0)Check the status of all the components before the upgrade:

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
SQL>select count(*) from dba_objects where status='INVALID';

1)Download and unzip patch p6810189_10204_Linux-x86-64.zip in /stage directory.

2)Shutdown database and listener

Set the following env variables:

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch:$PATH:
ORACLE_HOME=Oracle_home
ORACLE_SID=sid
export ORACLE_HOME ORACLE_SID

3)Install the 10.2.0.4 patch set

From VNC launch the runinstaller.

cd /stage/Disk1
./runInstaller
On the Welcome screen, click Next.....

4)Upgrading the databse to 10.2.0.4

a)Run the Pre-Upgrade Information Tool

Start the database in the UPGRADE mode:

sqlplus '/as sysdba'
SQL> STARTUP UPGRADE

SQL> SPOOL upgrade_info.log

SQL> @?/rdbms/admin/utlu102i.sql

SQL> SPOOL OFF
Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log file,Make sure all are fine.

b)Upgrade the Database

SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF

c)Restart the database:& Compile Invalids.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL>@?/rdbms/admin/utlrp.sql


5)Check the status of all the components after the upgrade:

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
SQL>select count(*) from dba_objects where status='INVALID';


Discoverer 10G Clone

Oracle Discoverer 10G Clonining :
----------------------------------------

Perform the below actions to complete the Discoverer 10G clone.

A) Pre-clone Steps:
----------------
1) Login on Source Discoverer Machine as applmgr. There is no need to shutdown the services
$ export ORACLE_HOME=/u01/product/10gR2
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
$ cd $ORACLE_HOME/clone/bin
$ chmod 755 prepare_clone.pl
$ ./prepare_clone.pl
Clone log file location: /u01/product/10gR2/clone/logs/clone.log
Error log file location: /u01/product/10gR2/clone/logs/error.log

2) Copy the Discoverer directory to the target machine.

3) Get the ias_admin_old_pwd as it is necessary as input for the clone
B)Clone Steps
--------------
1) Login on the Linux box on target machine as root and set the inventory locations as:
mkdir /etc/oraInventory
chmod -R 777 /etc/oraInventory
chown -R applmgr:dba /etc/oraInventory
touch /etc/oratab
chmod a+rw /etc/oratab
chown applmgr:dba /etc/oratab
echo 'inventory_loc=/etc/oraInventory' > /etc/oraInst.loc
chown applmgr:dba /etc/oraInst.loc

2) Rename the old inventory directory, which is copied from source.
7) Start the clone activity.
$ export ORACLE_HOME=/u01/product/10gR2
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
$ cd $ORACLE_HOME/clone/bin
$ perl clone.pl ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME= -instance
-ias_admin_old_pwd welcome -ias_admin_new_pwd secret
Note: If the error occurs during the clone, clean the oraInventory directory and restart the clone process, otherwise the clone process will fail.
C)Post Clone Steps
-------------------
1) Remove Old dbc files from /u01/product/10gR2/discoverer/secure

2) Copy new dbc file from the application node to /u01/product/10gR2/discoverer/secure

3) Change the old servername to new server name in httpd.conf


4) $ORACLE_HOME/opmn/bin/opmnctl stopall

5) $ORACLE_HOME/opmn/bin/opmnctl startall

How to find out Apps password when we dont know the old password.

How to find out Apps password when we dont know the old password.


The below methog helps us to find out when we lost the Apps password, Please note you need have system password to perform the below actions.The below method is not suggested for Production or Critical systems.




Login to Middle Tier as ap user.
-------------------------------


$cd $FND_TOP/patch/115/sql


$cp AFSCJAVS.pls AFSCJAVS.pls.orig


Add following lines to the AFSCJAVS.pls file




CREATE OR REPLACE PACKAGE apps.fnd_web_sec AS




/*--------------------------------------------------------------*/


function decrypt(key in varchar2, value in varchar2)
return varchar2;


/*--------------------------------------------------------------*/




$sqlplus system/ @AFSCJAVS.pls


Package created.




Commit complete.




as system excute the following to find out if anything marked as invalid.


SQL> show user
USER is "SYSTEM"


SQL>col OBJECT_NAME for a30;
set line 200;
select object_name,object_type,status,owner from dba_objects where object_name='FND_WEB_SEC';


SQL>col OBJECT_NAME for a30;
set line 200;
select object_name,object_type,status,owner from dba_objects where object_name='FND_WEB_SEC';


OBJECT_NAME OBJECT_TYPE STATUS OWNER
------------------------------ ------------------ ------- ------------------------------
FND_WEB_SEC PACKAGE VALID APPS
FND_WEB_SEC PACKAGE BODY INVALID APPS


SQL> alter PACKAGE apps.FND_WEB_SEC compile body;


Package body altered.




If there are any invalids compile them.


Obtain Guest user ID and password.

as system excute the following.


SQL> show user
USER is "SYSTEM"


SQL> select profile_option_value from apps.fnd_profile_option_values where
profile_option_id = (select profile_option_id from apps.fnd_profile_options where
profile_option_name='GUEST_USER_PWD');
2 3
PROFILE_OPTION_VALUE
----------------------------------------------------------------------------------------------------
GUEST/ORACLE


SQL> show user
USER is "SYSTEM"


SQL>create synonym FND_WEB_SEC_TEST for apps.FND_WEB_SEC;


Synonym created.


SQL> show user
USER is "SYSTEM"
SQL> create synonym FND_WEB_SEC_TEST for apps.FND_WEB_SEC;


Synonym created.


-- Now Obtain Apps password using the below sql :)


SQL> SELECT(
SELECT
fnd_web_sec_test.decrypt('GUEST/ORACLE',encrypted_foundation_password)
FROM dual
)AS APPS_PASSWORD
FROM
apps.fnd_user
WHERE
user_name like 'GUEST'; 2 3 4 5 6 7 8 9


APPS_PASSWORD
----------------------------------------------------------------------------------------------------
APPS


-- WOW we got the Apps password now


SQL> drop synonym FND_WEB_SEC_TEST;


Synonym dropped.




-bash-3.2$ sqlplus apps/ @AFSCJAVS.pls




Package created.




Commit complete.






Please find the below explanation for the above steps:
---------------------------------------------------------------


A)
We know in Oracle Applications passwords are stored in FND_USER and FND_ORACLE_USERID tables,The FND_USER table stores application user account passwords and
the FND_ORACLE_USERID table stores internal Oracle Applications database account passwords,Both tables use the same encryption algorithm to protect the passwords.




The FND_USER table contains all the application accounts.There are two password columns in this table: ENCRYPTED_FOUNDATION_PASSWORD and ENCRYPTED_USER_PASSWORD.


====================================================
Column Value Encryption Key
===================================================
ENCRYPTED_FOUNDATION_PASSWORD APPS username/password
ENCRYPTED_USER_PASSWORD user APPS password




The 2 columns as...


a)If we know the username/password we can get the Apps Password (ENCRYPTED_FOUNDATION_PASSWORD)
b)If we know the Apps password we can get any users password (ENCRYPTED_USER_PASSWORD)




B)


To decrypt and verify user passwords Oracle Apps uses FND_WEB_SEC package in Apps.The DECRYPT function in the FND_WEB_SEC package is a local function and cannot be called from outside the package.


So to make the function available outside the package,we have altered the AFSCJAVS.pls above.






What happens during a hot backup

What happens during a hot backup::

What happens during a hot backup is widely misunderstood. Many people believe that while a tablespace is in backup mode, the datafiles within that tablespace are not written to. They believe that all changes to these files are kept in the redologs until the tablespace is taken out of backup mode, at which point all changes are applied to the datafiles just as they are during a media recovery. Although this explanation is easier to understand (and swallow) than how things really work, it is absolutely not how hot backups work in Oracle.

A common reaction to this statement is a very loud "What?" followed by crossed arms and a really stern look. (I reacted the same way the first time I heard it.) "How could I safely back up these files if they are changing as I'm backing them up?" Don't worry -- Oracle has it all under control. Remember that every Oracle datafile has an SCN that is changed every time an update is made to the file. Also remember that every time Oracle makes a change to a datafile, it records the vector of that change in the redolog. Both of these behaviors change during hot backups. When a tablespace is put into backup mode, the following three things happen:


1. Oracle checkpoints the tablespace, flushing all changes from shared memory to disk.

2. The SCN markers for each datafile in that tablespace are "frozen" at their current values. Even though further updates will be sent to the datafiles, the SCN markers will not be updated until the tablespace is taken out of backup mode.

3. Oracle switches to logging full images of changed database blocks to the redologs. Instead of recording how it changed a particular block the change vector), it will log the entire image of the block after he change. This is why the redologs grow at a much faster rate while hot backups are going on.


After this happens, your backup program works happily through this datafile, backing it up block by block. Since the file is being updated as you are reading it, it may read blocks just before they're changed, after they're changed, or even while they're changing. Suppose that your filesystem block size is 4 KB, and Oracle's block size is 8 KB. Your backup program will be reading in increments of 4 KB. It could back up the first 4 KB of an 8-KB Oracle data block before a change is made to that block, then back up the last 4 KB of that file after a change has been made. This results in what Oracle calls a "split block". However, when your backup program reaches the point of the datafile that contains the SCN, it will back up that block the way it looked when the backup began, since that block is frozen. Once you take the tablespace out of backup mode, the SCN marker is advanced to the current value, and Oracle switches back to logging change vectors instead of full images of changed blocks.



How does Oracle straighten this out during media recovery? It's actually very simple. You use your backup program to restore the datafile. When you attempt to start the instance, Oracle looks at the datafile and sees an old SCN value. Actually, it sees the value that the SCN marker had before the hot backup began. When you enter recover datafile, it begins to apply redo against this datafile. Since the redologs contain a complete image of every block that changed during your backup, it can rebuild this file to a consistent state, regardless of when you backed up a particular block of data.

ORACLE APPS R12 - SOCKET to SERVLET conversion (vice-versa) Release R12

ORACLE APPS R12 - SOCKET to SERVLET conversion (vice-versa) Release R12
=========================================================

1) $INST_TOP/admin/scripts/adstpall.sh

2) To change value in context_file & execute.
For SOCKET MODE
===============
$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \
[-contextfile=] \
-mode=socket \
[-port=] \
-runautoconfig= \
-appspass=
For Servlet MODE
================
$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \
[-contextfile=] \
[-mode=servlet] \
-runautoconfig= \
-appspass=
3) $INST_TOP>/admin/scripts/adstrtal.sh
4) To Verify
$INST_TOP>/admin/scripts/adformsrvctl.sh status
&
For SOCKET MODE
===============
Log in to Oracle Applications and launch a Forms-based application.
Check whether the "mode" directive displayed in Sun Java Console when launching forms-based applications is set to socket.
For Servlet MODE
================
Log in to Oracle Applications and launch a Forms-based application.
Check whether the "mode" directive displayed in Sun Java Console when launching forms-based applications, is set to http,native
5) Direct Launch ( This will work only if "authentication" is set to "OFF" )
For SOCKET MODE
===============
://.:/OA_HTML/frmservlet
For Servlet MODE
================
://.:/forms/frmservlet

How to Find Out Versions - I

Finding Versions - I
============

OS VERSION
----------
$ uname -a
Linux auohszcoi01 2.4.9-e.35enterprise #1 SMP Tue Dec 23 00:06:16 EST 2003 i686

file /sbin/init
/sbin/init: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), stripped

JAVA VERSION
-----------

$ grep JSERVJAVA $APACHE_TOP/Apache/bin/java.sh
$ /usr/java/jdk1.3.1_02/jre/bin/java version
$ ls -l /usr/java/jdk1.3.1_02/jre/bin/java

FINDING JAVA FILE VERSION
-------------------------
Once you are in the correct directory (which should be the $JAVA_TOP), you
can perform the following to get the file version to get the version of

ItemEOImpl.class, for example:
---------------------------------------------------------------------------
ident apps.zip | grep -i 'ItemEOImpl'
---------------------------------------------------------------------------

If the above doesn't work, please try the following:
---------------------------------------------------------------------------
strings -a apps.zip|grep -i header|grep ItemEOImpl.java

strings -a ./apps/inv/transaction/server/BaseTransaction.class | grep Header

IF apps.zip is not available under JAVA_TOP, then,

1- Go to $JAVA_TOP
2- give a find Command with file name..
eg:
find . -print | grep BaseTransaction
find . -print | grep telnetdemo

out put could be like this...

/oracle/apps/inv/transaction/server/BaseTransaction.class
then cd to the /oracle/apps/inv/transaction/server/

strings -a BaseTransaction.class|grep Header

Or from SQLPLUS

sql> set serveroutput on
sql> exec
fnd_aolj_util.getclassversionfromdb('oracle.apps.inv.transaction.server.TrxP
rocessor');

DISCOVERER
----------

cd $ORACLE_HOME/discwb4/bin

strings -a dis4ws | grep -i 'Discoverer version'

In both cases make sure you have read / write priviledges on the files dis51ws and dis4ws

$ strings -a dis4ws | grep -i 'Discoverer version'
Discoverer Version:Session 4.1.48.08.00

PORTAL verions
--------------

select version from wwc_version$;
or
select i.instance_name INSTANCE, i.host_name HOST , f.release_name release, i.version from v$instance i, fnd_product_groups f where upper(substr(i.instance_name,1,4)) = upper(substr(f.applications_system_name,1,4));

DB version
----------

col instance_name format a13
col host_name format a15
col version format a9
col release format a7
select i.instance_name, i.host_name, f.release_name release, i.version
from v$instance i, fnd_product_groups f
where i.instance_name = f.applications_system_name;
or
select * from v$version;
or
select BANNER from V$VERSION where BANNER like 'Oracle%';

Apps Version
-------------
SELECT 'VERSION = ', release_name FROM fnd_product_groups;
Or
SELECT substr(a.application_short_name, 1, 5) code,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG');
Or
select product_group_id, product_group_name, release_name, product_group_type, argument1 from fnd_product_groups;


PRODUCT versions
----------------
cd $AD_TOP/sql/adutconf.sql
SQL> @adutconf.sql
This creates a text file adutconf.lst in the current working directory
Or
a) Find all products and statuses:
select application_id, status
from fnd_product_installations;
or
b) Find the status of a particular product:

E.g. Application_id:
PER (Human Resources) = 800
PAY (Payroll) = 801

select application_id, status
from fnd_product_installations;
where application_id = '%80%';
Status:
I = Installed
S = Shared
N = Not Installed

WEBCACHE version
----------------

$ORACLE_HOME/webcache/bin/webcached –version
output :
Oracle Web Cache, Version 2.0.0.4.0
Copyright: Oracle Corporation, 1999-2002
Build CALYPSO_2.0.0_SOLARIS_020405

.pld version
-----------
strings –a $AU_TOP/resource/.plx | grep ‘$Header:’

.pls version
-------------

strings –a $/patch/115/sql/.pls | grep ‘$Header:’

.pll version
-------------

strings –a $/plsql/.pll | grep ‘$Header:’

.plx version
-------------
strings –a $/resource/US/.plx | grep ‘$Header:’

.rdf version
-------------
strings –a $JE_TOP/reports/US/.rdf | grep ‘$Header:’

OA Framework Version
-----------------------
http://:/OA_HTML/OAInfo.jsp

Jinitiator version
-------------
select text from wf_resources where name like 'WF_PLUGIN_VERSION%';

Forms Version
-------------

cd 806_ORACLE_HOME/bin
type “ f60run ” and hit enter
This will give you the forms version.
Other option, open any form from front-end applications and click on About Oracle Applications.

Useful Metalink Note ID's

Useful Metalink Note ID's
==========================

Below are the metalink (support.oracle.com) note id's usefull for DBA's.

Please note to access them you need to have support.oracle.com login account.


Physical Standby
----------------
Note:180031.1 Creating a Data Guard physical standby
Note:214071.1 Creating a Data Guard physical standby with Data Guard Manager
Note:232649.1 Configuring gap resolution
Note:232240.1 Performing a switchover
Note:227196.1 Performing a failover
Note:187242.1 Applying Patchsets with Physical Standby in Place

Logical Standby
--------------
Note:186150.1 Creating a logical standby
Note:214071.1 Creating a logical standby with Data Guard Manager
Note:232240.1 Performing a switchover
Note:227196.1 Performing a failover
Note:233261.1 Tuning Log Apply Services
Note:215020.1 Troubleshooting Logical Standbys
Note:210989.1 Applying Patchsets with Logical Standby in Place
Note:233519.1 Known Issues with Logical Standby

Dataguard General Information
-----------------------------
Note:205637.1 Configuring Transparent Application Failover with Data Guard
Note:233509.1 Data Guard Frequently Asked Questions
Note:225633.1 Using SSH with 9i Data Guard
Note:233425.1 Top Data Guard Bugs
Note:219344.1 Usage, Benefits and Limitations of Standby RedoLogs
Note:201669.1 Setup and maintenance of Data Guard Broker using DGMGRL
Note:203326.1 Data Guard 9i Log Transportation on RAC
Note:239100.1 Data Guard Protection Modes Explained

Dataguard Configuration Best Practices
--------------------------------------
Note:240874.1 Primary Site and Network Configuration Best Practices
Note:240875.1 9i Media Recovery Best Practices


RMAN and Backup & Restore
-------------------------

60545.1 How to Extract Controlfiles, Datafiles, and Archived Logs from RMAN Backupsets

10gR2 Setup Installation, ASM,CRS, RAC , Troubleshooting
---------------------------------------------------------

471165.1 Additional steps to install 10gR2 RAC on IBM zSeries Based Linux (SLES10)
407086.1 USING CLONING IN CRS/RAC WINDOWS ENVIRONMENTS TO ADD A NODE
414163.1 10gR2 RAC Install issues on Oracle EL5 or RHEL5 or SLES10 (VIPCA Failures)
467753.1 Veritas clusterware 5.0 not recognized by Oracle due to the fact that Veritas
467176.1 RAC: Installing RDBMS Oracle Home Hangs The Oui
466975.1 Step to remove node from Cluster when the node crashes due to OS or H/w
330358.1 CRS 10g R2 Diagnostic Collection Guide
401132.1 How to install Oracle Clusterware with shared storage on block devices
392207.1 CSSD Startup fails with NSerr (12532,12560) transport:(502,0,0) during Install
333166.1 CSSD Startup Fails with NSerr (12546,12560) transport:(516,0,0) During install
330929.1 CRS Stack Fails to Start After Reboot ORA-29702 CRS-0184
463255.1 Enable trace for gsd issues on 10gR2 RAC
338924.1 CLUVFY Fails With Error: Could not find a suitable set of interfaces for VIPs
462616.1 Reconfiguring the CSS disktimeout of 10gR2 Clusterware for Proper LUN Failover
461884.1 How To Disable Fatal Mode Oprocd On HP-UX Itanium 10gR2
404474.1 Status of Certification of Oracle Clusterware with HACMP 5.3 & 5.4
329530.1 Using Redhat Global File System (GFS) as shared storage for RAC
458324.1 Increased 'Log File Sync' waits in 10gR2
341214.1 How To clean up after a Failed (or successful) Oracle Clusterware Installation
454638.1 srvctl command failed - An unexpected exception has been detected in native
276434.1 Modifying the VIP or VIP Hostname of a 10g Oracle Clusterware Node
383123.1 PRKP-1001 CRS-215 srvctl Can not Start 2nd Instance
358620.1 How To Recreate Voting And OCR Disk In 10gR1/2 RAC
200346.1 RAC: Frequently Asked Questions
220970.1 RAC: Frequently Asked Questions
269320.1 Removing a Node from a 10g RAC Cluster
430266.1 How to install 10gR2 and 9iR2 on the same node with different UDLM requirement
283684.1 How to Change Interconnect/Public Interface IP Subnet in a 10g Cluster
391790.1 Unable To Connect To Cluster Manager Ora-29701
294430.1 CSS Timeout Computation in RAC 10g (10g Release 1 and 10g Release 2)
316583.1 VIPCA FAILS COMPLAINING THAT INTERFACE IS NOT PUBLIC
416868.1 CDMP DIRECTORIES AND TRW FILES ON RAC
414177.1 Executing root.sh errors with "Failed To Upg Oracle Cluster Registry Config
390483.1 DRM - Dynamic Resource management
390880.1 OCR Corruption after Adding/Removing voting disk to a cluster when CRS stack
309542.1 How to start/stop the 10g CRS ClusterWare
396643.1 CVU HAS INCORRECT ORA_CRS_HOME VARIABLE AFTER APPLYING CRS BUNDLE II
387205.1 The 10.1.0.4 DB Cannot Start With 10.2.0.2.0 CRS And ASM
270512.1 Adding a Node to a 10g RAC Cluster
395156.1 Startup (mount) of 2nd RAC instance fails with ORA-00600 [kccsbck_first]
363777.1 How to Completely Remove a Service so that its Service_id Can Be Reused
391112.1 Database Resource Manager Spins Lmon To 100% Of Cpu
365530.1 Permissions not set correctly after 10gR2 installation
357808.1 Diagnosability for CRS / EVM / RACG
284752.1 10g RAC: Steps To Increase CSS Misscount, Reboottime and Disktimeout
332180.1 ASMCMD - ASM command line utility
371434.1 Using Openfiler iSCSI with an Oracle database
338047.1 cluvfy ERROR: Unable to retrieve database release version
183408.1 Raw Devices and Cluster Filesystems With Real Application Clusters
367564.1 Server Reboots When Rolling Upgrading CRS(10gr1 -> 10gr2)
358545.1 Root.sh is failing with CORE dumps, during CRS installation
343092.1 How to setup Linux md devices for CRS and ASM
295871.1 How to verify if CRS install is Valid
331934.1 RAC Single Instance (ASM) startup fails with ORA-27300/ORA-27301/ORA-27302
341974.1 10gR2 RAC Scheduling and Process Prioritization
341971.1 10gR2 RAC GES Statistics
341969.1 10gR2 RAC OS Best Practices
341965.1 10gR2 RAC Reference
341963.1 10gR2 RAC Best Practices
313540.1 Manually running cvu to verify stages during a CRS/RAC installation
331168.1 Oracle Clusterware consolidated logging in 10gR2
339710.1 Abnormal Program Termination When Installing 10gR2 on RHAS 4.0
339383.1 CSSD FAILURE DOES NOT REBOOT THE NODE
337937.1 Step By Step - 10gR2 RAC with ASM install on Linux(x86) - Demo
280209.1 10g RAC Performance Best Practices

Real Application Clusters(RAC)
-----------------------------

181503.1 Real Application Clusters Whitepapers (OTN)
280209.1 10g RAC Performance Best Practices (INTERNAL ONLY)
302806.1 IBM General Parallel File System (GPFS) and Oracle RAC on AIX 5L and IBM eServer pSeries
270512.1 Adding a Node to a 10g RAC Cluster
137288.1 Manual Database Creation in Oracle9i (Single Instance and RAC)
292776.1 10g RAC Lessons Learned
280216.1 10g RAC Reference (INTERNAL ONLY)
269320.1 Removing a Node from a 10g RAC Cluster
226561.1 9iRAC Tuning Best Practices (INTERNAL ONLY)
220178.1 Installing and setting up ocfs on Linux - Basic Guide
208375.1 How To Convert A Single Instance Database To RAC In A Cluster File System Configuration
255359.1 Automatic Storage Management (ASM) and Oracle Cluster File System (OCFS) in Oracle10g
341963.1 10gR2 RAC Best Practices (INTERNAL ONLY)
273015.1 Migrating to RAC using Data Guard
329530.1 Using Redhat Global File System (GFS) as shared storage for RAC
270901.1 How to Dynamically Add a New Node to an Existing 9.2.0 RAC Cluster
203326.1 Data Guard 9i Log Transportation on RAC
169539.1 A Short Description of HA Options Available in 9i
160120.1 Oracle Real Application Clusters on Sun Cluster v3
226569.1 9iRAC Most Common Performance Problem Areas (INTERNAL ONLY)
251578.1 Step-By-Step Upgrade of Oracle Cluster File System (OCFS v1) on Linux
247135.1 How to Implement Load Balancing With RAC Configured System Using JDBC
139436.1 Understanding 9i Real Application Clusters Cache Fusion
285358.1 Creating a Logical Standby from a RAC Primary Using a Hot Backup
222288.1 9i Rel 2 RAC Running on IBM’s General Parallel File System
226567.1 9iRAC Related Init.ora Parameters (INTERNAL ONLY)
210889.1 RAC Installation with a NetApp Filer in Red Hat Linux Environment
341965.1 10gR2 RAC Reference (INTERNAL ONLY)
341969.1 10gR2 RAC OS Best Practices (INTERNAL ONLY)
226566.1 9iRAC Related Latches (INTERNAL ONLY)
220970.1 RAC: Frequently Asked Questions
268202.1 Dynamic node addition in a Linux cluster
285455.1 HOW TO MAKE AN EXCLUSIVE INSTANCE AVAILABLE ON MULTIPLE CLUSTER NODES.
332257.1 Using Oracle Clusterware with Vendor Clusterware FAQ
245079.1 Steps to clone a 11i RAC environment to a non-RAC environment
235158.1 How To Enable/Disbale Archive Log Mode on Oracle9i Real Application Cluster
210022.1 How To Add A New Instance To The Existing Two Nodes RAC Database Manually
317516.1 Adding and Deleting a Cluster Node on 10gR2 / Linux
271685.1 How to Run Autoconfig for RAC Environment on Apps Tier Only
278816.1 How to Setup Parallel Concurrent Processing using Shared APPL_TOP for RAC Environment
334459.1 How to change hostname in RAC environment
250378.1 Migrating Applications 11i to use Oracle9i RAC (Real Application Clusters).
295998.1 How to solve corruptions on OCFS file system
345081.1 How to Rename a RAC Database in a 10g Real Application Clusters Environment
312051.1 How To Remove Ocfs From Linux Box.


EXPORT / IMPORT
---------------

Note 230627.1 - 9i Export/Import Process for Oracle Applications Release 11i
Note 331221.1 - 10g Export/Import Process for Oracle Applications Release 11i
Note 362205.1 - 10g Release 2 Export/Import Process for Oracle Applications Release 11i
Note 277650.1 - How to Use Export and Import when Transferring Data Across Platforms or Acros...
Note 243304.1 - 10g: Transportable Tablespaces Across Different Platforms
Note 341733.1 - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload..

RDBMS and E-Business Suite Installation and Configuration
---------------------------------------------------------
118218.1 11i. Installing a Digital Cerificate on both the Server and Client.
252217.1 Requirements for Installing Oracle 9iR2 on RHEL3
146469.1 Installation & Configuration of Oracle Login server & Portal3i
146468.1 Installation of Oracle9i Application Server(9iAS)
152775.1 XML gateway installation
165700.1 Multiple Jserv configuration
207159.1 Documentation of 9iAS
210514.1 Express Server WebIV Note numbers
170931.1 Notes on Motif troubleshooting
177610.1 Oracle Forms in Applications FAQ
258021.1 How to monitor the progress of a materialized view refresh (MVIEW)
330250.1 Tips & Tricks To Make Apache Work With Jserv
139684.1 Oracle Applications Current Patchset Comparison Utility - patchsets.sh
236469.1 Using Distributed AD in Applications Release 11.5.
96630.1 Cash Management Overview
233428.1 Sharing the Application Tier File System in Oracle Applications 11i
243880.1 Shared APPL_TOP FAQ
330250.1 Tips & Tricks To Make Apache Work With Jserv
241370.1 Concurrent Manager Setup and Configuration Requirements in an 11i RAC Environment
209721.1 How to Change the Port Number on one Machine, When we Use Multiple Collaboration Suite Tiers
177377.1 How to change passwords in Portal (Database and lightweight user passwords)
304748.1 Internal: E-Business Suite 11i with Database FAQ
166213.1 SPFILE internals ** INTERNAL ONLY **
216208.1 Oracle9i Application Server (9iAS) with Oracle E-Business Suite Release

UPGRADES
--------
125767.1 Upgrading Devloper6i with Oracle Applications 11i
216550.1 RDBMS upgrade to 9.2.0
161779.1 Upgradation of HTTP Server
212005.1 Upgrade Oracle Applications to 11.5.8
139863.1 Self Servie Framework Upgrade
112867.1 Express Server & OFA upgrade
124606.1 Jinitiator upgrade
130091.1 JDK upgrade to 1.3
130091.1 Upgrading Oracle Applications 11i to use JDK 1.3
144069.1 Upgrading to Workflow 2.6 with Oracle Applications 11i
159657.1 Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9i

CLONING
---------
216664.1 FAQ: Cloning Oracle Applications Release 11i
230672.1 Cloning Oracle Applications Release 11i with Rapid Clone
135792.1 Cloning Oracle Applications Release 11i

Discoverer
-------------
139516.1 Discoverer 4i with Oracle Applications 11i
257798.1 Discoverer 10g (9.0.4) with Oracle Applications 11i
139516.1 Installation of Discoverer 4i

AutoConfig
-----------
165195.1 Using AutoConfig to Manage System Configurations with Oracle Applications 11i
218089.1 Autoconfig FAQ

Frequently Asked Questions
--------------------------
68993.1 Concurrent Managers on NT
1013526.102 Changing and Resetting Release 11 Applications Passwords
130608.1 ADPATCH BASICS
74924.1 ADI (Applications Desktop Integrator) Installation
61552.1 DIAGNOSING DATABASE HANGING ISSUES
114226.1 How to Set Up Apache and JSERV w/ Oracle XSQL, JSP, and Developer
146469.1 Installing and Configuring Oracle Login Server and Oracle Portal 3i with Oracle Applications 11i
146468.1 Installing Oracle9i Application Server 1.0.2.2.2 with Oracle Applications 11i
62463.1 Detailed Guide on How the Intelligent Agent Works
104452.1 Troubleshooting (Concurrent Manager Unix specific)
122662.1 How to change the hostname or domainname of your portal
231286.1 Configuring the Oracle Workflow 2.6 Java-based Notification Mailer with Oracle Applications 11i
230688.1 Basic ApacheJServ Troubleshooting with IsItWorking.class
204015.1 Export/Import Process for Oracle Applications Release 11i Database Instances Using Oracle8i EE
158818.1 Migrating the Workflow Mailer to the APPLMGR Account
185431.1 Troubleshooting Oracle Applications Manager OAM 2.0 for 11i
177089.1 OAM11i Standalone Mode Setup and Configuration
172174.1 WF 2.6: Oracle Workflow Notification Mailer Architecture in Release 11i
166021.1 Oracle Applications Manager 11i - Pre-requisite Patches
166115.1 Oracle Applications Manager 11i integrated with Oracle Applications 11i
165041.1 Generic Service Management Functionality
204090.1 Generic Service Management Configuration using Applications Context Files
139863.1 Configuring and Troubleshooting the Self Service Framework with Oracle Applications (latest version)
187735.1 Workflow FAQ - All Versions
166830.1 Setting up Real Application Cluster (RAC) environment on Linux - Single node
158868.1 Step by Step, Oracle 9iAS Installation Process
123243.1 Scheduling Web Reports Via Oracle Reports Server CGI
165195.1 Using AutoConfig to Manage System Configurations with Oracle Applications 11i



IMP Note ID's
--------------

233428.1 Sharing the Application Tier File System in Oracle Applications 11i
243880.1 Shared APPL_TOP FAQ
313418.1 Using Discoverer 10.1.2 with the E-Business Suite
287176.1 DMZ Configuration with Oracle E-Business Suite 11i
241015.1 Configuring Applications 11i for use with Reverse Proxy and DMZ
1025288.6 How to Diagnose and Resolve ORA-01650,ORA-01652,ORA-01653,ORA-01654,ORA-01688 :
1035935.6 Example of How To Resize the Online Redo Logfiles
133062.1 ORA-25150 Altering Of Extent Parameters Not Permitted While Applying Patch
139516.1 Discoveaer 4i with Oracle Applications 11i
15476.1 FAQ about Detecting and Resolving Locking Conflicts
177240.1 Supported 11i Login URLs - using Dev60cgi / f60cgi to Access Applications is Unsupported
207959.1 All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS
216205.1 Database Initialization Parameters for Oracle Applications 11i
216550.1 Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
218089.1 Autoconfig FAQ
230672.1 Cloning Oracle Applications Release 11i with Rapid Clone
233428.1 Sharing the Application Tier File System in Oracle Applications 11i
236247.1 Transferring Archive Logs to the Standby Database Disconnects
242941.1 How To Troubleshoot Java-based Workflow Notification Mailer In 11.5.9 and OWF.G
260676.1 When Applying Patch 2644375 Worker Is Prompting For Parameter 5 In The Worker Log
277710.1 Ora-00600: Internal Error Code, Arguments: [1236], [], ... Ora-600
282234.1 Error "java.lang.NullPointerException" In Step 3 Of Configuring WF Java Mailer In Applications 11i
268085.1 Configuring the Oracle Workflow 2.6/11i.OWF.H Java-based Notification Mailer with Oracle Applications 11i
164871.1 Configuring the Workflow Notification Mailer in Oracle Applications Manager 11i
172174.1 WF 2.6: Oracle Workflow Notification Mailer Architecture in Release 11i