ORA-28575: unable to open RPC connection to external procedure agent

Check Oracle home path in listener file:

SID_LIST_LISTENER_EXTPROC =
  (SID_LIST =
    (SID_DESC =
    (ENVS="EXTPROC_DLLS=ANY,LD_LIBRARY_PATH=/pwrcard/home/usr/lib,MYPATH=/usr/bin:/pwrcard/home/usr/bin,APL_ENV_FILE=/pcard17/trace/env.txt")
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/11.2.0/db_1) ---  /oracle/app/11.2.0/db
      (PROGRAM = extproc)
    )
  )

Delete/Remove windows service from command prompt

sc stop [Service name]
sc delete [service name]

Ex.
C:\ias\10.1.2\opmn\bin>sc delete OracleIAS_HOMEProcessManager

[SC] DeleteService SUCCESS

One or more post-processing actions failed. Consult the OPP service log for details / Java heap space

Query for the logfile:

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = &request_id;

In my case:

/d01/applprod/inst/apps/PROD_server077/logs/appl/conc/log/FNDOPP281472.txt

java.lang.reflect.InvocationTargetException

Caused by: oracle.xdo.parser.v2.XPathException: Extension function error: Error invoking 'ms_format_number':'java.lang.OutOfMemoryError: Java heap space'

As a workaround we killed the OPP process:

ps -ef | grep OPP

kill -9 1943

Permanent Solution:

We increased the Java heap size from 1024M to 2048M.
a. Bring down the concurrent managers.

b. Use the Update statement below, for example:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

c. Bring concurrent managers up again 

Query to connect PER_PEOPLE_F and FND_USER

Below is Simple Query to connect PER_PEOPLE_F  and FND_USER in Oracle Applications and print following details

1. User ID
2. User name
3. Email Address
4. Contact #

SELECT fu.user_id, fu.user_name,
(select hr.email_address from PER_PEOPLE_F hr
  where fu.employee_id = hr.person_id
    and nvl(hr.effective_start_date, sysdate-1) < sysdate
    and nvl(hr.effective_END_date, sysdate+1) > sysdate) email_address,
(select pho.phone_number from PER_PEOPLE_F hr, per_phones pho
  where fu.employee_id = hr.person_id
    and hr.person_id = pho.parent_id(+)
    AND pho.phone_type(+) = 'W1'
    AND pho.parent_table(+) = 'PER_ALL_PEOPLE_F'
    AND sysdate between pho.date_from(+)
    and nvl(pho.date_to(+), sysdate)
    and nvl(hr.effective_start_date, sysdate-1) < sysdate
    and nvl(hr.effective_END_date, sysdate+1) > sysdate) phone_number
    from fnd_user fu
    where fu.user_id = fnd_global.user_id

ORA-19554: error allocating device, device type: SBT_TAPE, device name: ORA-27211: Failed to load Media Management Library

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on t1 channel at 10/18/2015 10:28:11
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

As this was an out-of-place upgrade, ORACLE_HOME has changed. Due to ORACLE_HOME change, link between oracle MML & vendor libraries is broken.

1. Verify the link

bash-3.2$ ls -al $ORACLE_HOME/lib | grep obk | wc -l

       0

2. Create the required link

$ ln -s /usr/lib/libobk64.a $ORACLE_HOME/lib/libobk.a

3. Verify the link again

Now it should work 

Oracle query to find value of bind variables used by SQL

For less than 30 min running query:

select s.sid,

s.username,

sq.sql_text,

s.sql_hash_value,

s.sql_id,

s.sql_child_number,

spc.name,

spc.value_string,

last_captured

from v$sql_bind_capture spc, v$session s,v$sql sq

where s.sql_hash_value = spc.hash_value

and s.sql_address = spc.address

and sq.sql_id=s.sql_id

and spc.was_captured='YES'

and s.type<>'BACKGROUND'

and s.status='ACTIVE'

For historical query:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM DBA_HIST_SQLBIND WHERE SQL_ID='' and SNAP_ID='';