Wednesday 28 October 2015

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)
    )
  )

Monday 26 October 2015

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

Sunday 25 October 2015

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 

Monday 19 October 2015

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

Saturday 17 October 2015

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 

Tuesday 6 October 2015

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='';

Number of Visitors