Find Concurrent Request ID from SID

Find Concurrent Request id from SID:

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
   WHERE s.sid in ('&sid')
  AND s.paddr = c.addr
  AND a.oracle_process_id = c.spid
  AND a.phase_code = UPPER ('R');

Output:

   INST_ID REQUEST_ID        SID    SERIAL# SPID
---------- ---------- ---------- ---------- ------------------------
         1   20602689      15647       2673 5617

Typical DB Environment File Looks Like...

Assumptions that we have Oracle Database 12c Release 1.

Typical environment file in Database Node looks like....

$ cat ORCL.env
export PS1='\[\e[32m\]\u@\h \[\e[33m\]\w\[\e[0m\]\nORCL: '
export ORACLE_SID=ORCL
export ORACLE_BASE=/ora/db/tech_st
export ORACLE_HOME=/ora/db/tech_st/12.1.0.2
export TNS_ADMIN=$ORACLE_HOME/network/admin/<ORCL_HOSTNAME>
export PERL5LIB=/ora/db/tech_st/12.1.0.2/perl/lib/5.14.1
export LD_LIBRARY_PATH=/ora/db/tech_st/12.1.0.2/lib:/ora/db/tech_st/12.1.0.2/ctx/lib:/lib:/usr/lib
export PATH=$PATH:/ora/db/tech_st/12.1.0.2/appsutil/scripts/<ORCL_HOSTNAME>:$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$ORACLE_HOME/OPatch:.
export ORA_NLS10=/ora/db/tech_st/12.1.0.2/nls/data/9idata

Oracle Apps R12.1.3 iHelp Page Prompting for Credentials.

Issue Details:-

EBS iHelp page issue, when user clicks on help page, it is prompting for credentials, which not a normal behaviour.

Once we pass the credentials, it suppose to take that to help page, but it is going to users home page.

Environment Details:

We have one dbTier and two appTiers and login is load balanced url.

Solution:

We reviewd below metalink document, but that didn't help us.

R12: iHelp Not Working After Implementing A Hardware Load Balancer, SSL Acceleration, Or DMZ (Doc ID 728463.1)

Worked with Oracle Support, and they found that the issue occurred due to hardware load balancer implementation.  Oracle recommended to keep NULL for HELP_WEB_AGENT in the $CONTEXT_FILE and run autoconfig.

<HELP_WEB_AGENT oa_var="s_help_web_agent">https://myerp.domain.com</HELP_WEB_AGENT>

To

<HELP_WEB_AGENT oa_var="s_help_web_agent"></HELP_WEB_AGENT>

Run Autoconfig.

This fixed our issue and able to launch help page without prompting for username and password.

Delete Files Older Than X Days in Unix

In this example X is replaced with 7.

List all files older than X days:

find /path/to/files/ -type f -mtime -7 -exec ls -l {} \; 

Delete all files older than X days:

find /path/to/files/ -type f -mtime -7 -exec rm -f {} \;

Move all files with extension '.arc' older than X days:

find /path/to/files/ -type f -name '*.arc' -mtime +7 -exec mv {} /path/to/archive/ \;

Blocking Session Details

How to find blocking / locking session details:

set lines 145
col sid noprint

select distinct
       s1.sid,s1.username || '@' || s1.machine ||
       ' ( INST=' || s1.inst_id || ' SID=' || s1.sid||','||s1.serial# ||'  '||s1.status||': '||s1.last_call_et||' SQLID: '||s1.sql_id||' )  is blocking '
       || s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' AS blocking_status
  from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid
   and s2.sid=l2.sid
   and l1.BLOCK=1
   and l2.request > 0
   and l1.id1 = l2.id1
   and l2.id2 = l2.id2
   and l1.inst_id = s1.inst_id
order by s1.sid;

Output:

BLOCKING_STATUS
-------------------------------------------------------------------------------------------------------------------------------------------------
APPS@mydbserver ( INST=1 SID=6323,26775  ACTIVE: 2494 SQLID: 08pfumd9shm53 )  is blocking APPS@mydbserver ( INST=1 SID=6323 )

1 row selected.

SQL>