Find out long running Concurrent Requests.

Find out long running Concurrent Requests:
==============================

SELECT gv.inst_id, gv.SID, fcr.request_id, fcr.program, fcr.phase,
fcr.status, fcr.actual_start_date "start",
fcr.actual_completion_date "end",
ROUND ( ( NVL (fcr.actual_completion_date, SYSDATE) - fcr.actual_start_date )
* 60 * 24, 2 ) TIME, fcqtl.user_concurrent_queue_name "Concurrent Manager", fcr.user_name, fcr.argument_text text, fcr.concurrent_program_id, gvw.event
FROM apps.fnd_amp_requests_v fcr,
gv$session gv,
gv$session_wait gvw,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_processes fcproc
WHERE phase_code = 'R'
AND fcr.oracle_session_id = gv.audsid(+)
AND gv.SID = gvw.SID(+)
AND gv.inst_id = gvw.inst_id(+)
AND fcr.controlling_manager = fcproc.concurrent_process_id
AND fcproc.queue_application_id = fcqtl.application_id
AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
AND fcqtl.LANGUAGE = 'US'
ORDER BY 9 DESC;

Long running concurrent requests for more than 1 hour

To identify long running concurrent requests for more than 1 hour:

=======================================================================

# To identify the long running concurrent requests for more than 1 hour
Mailid=<List of Mail ids>
<Set the Environment>
sqlplus -s 'apps/<apps password><<EOF >> /usr/tmp/logli1.log
spool /usr/tmp/longrunco.log
set line 200
set pagesize 500
col PROGRAM_NAME format a30
col concreq format a8
col Username format a10
col opid format a4
col dbuser format a6
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
           fcp.USER_CONCURRENT_PROGRAM_NAME "Program_Name",
                fu.user_name "Username",
               round((sysdate - actual_start_date) * 24 ,2) "Running_Hrs",
           SUBSTR(proc.os_process_id,1,15) clproc,
           SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
           SUBSTR(vsess.username,1,10) dbuser,
           SUBSTR(vproc.spid,1,10) svrproc,
           vsess.sid sid,
           vsess.serial# serial#
    FROM   fnd_concurrent_requests req,
           fnd_concurrent_processes proc,
           fnd_lookups look,
           fnd_lookups look1,
           V\$process vproc,
           V\$session vsess,
           fnd_concurrent_programs_vl fcp,
        fnd_user fu
    WHERE  req.controlling_manager = proc.concurrent_process_id(+)
    AND    req.status_code = look.lookup_code
    AND    look.lookup_type = 'CP_STATUS_CODE'
    AND    req.phase_code = look1.lookup_code
    AND    look1.lookup_type = 'CP_PHASE_CODE'
    AND    look1.meaning = 'Running'
    and    req.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
    AND    proc.oracle_process_id = vproc.pid(+)
    AND    vproc.addr = vsess.paddr(+)
    AND    fu.user_id = req.requested_by
    AND    round((sysdate - actual_start_date) * 24) > 1;
spool off
quit;
EOF
echo `date` >> $ORACLE_HOME/admin/$CONTEXT_NAME/udump/longrun_concurrent.log
cat /usr/tmp/longrunco.log >> $ORACLE_HOME/admin/$CONTEXT_NAME/udump/longrun_concurrent.log
cat /usr/tmp/longrunco.log |mailx -s "Alert:Long Running Concurrent requests Check" $Mailid
rm -rf /usr/tmp/longrunco.log

APPS & DATABASE SCRIPTS

APPS & DATABASE SCRIPTS:

This will give the number of users on the system in the past 1 hour:-

SQL> select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1/24 and user_id != '-1';

  users

----------

         4

This will give the number of users on the system in the past 1 day:-

SQL> select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1 and user_id != '-1';

   users

----------

        12

This will show the activity in the last 15 minutes:-

SQL> select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time", user_id, disabled_flag from icx_sessions where  last_connect > sysdate - 1/96;

 LIMIT_TIME LIMIT_CONNECTS Last Connection time           USER_ID D

---------- -------------- --------------------------- ---------- -

       999           9000 19-SEP-12 02:25:37             1013630               N

       999           9000 19-SEP-12 02:12:50             1013691               Y

       999           9000 19-SEP-12 02:12:58                   6                     N

       999           9000 19-SEP-12 02:25:23             1013555               N

Total size of Database:-

SQL> SELECT ROUND(SUM(bytes/1024/1024/1024)) FROM dba_data_files;

ROUND(SUM(BYTES/1024/1024/1024))

--------------------------------

                             184

Free size of database:-

SQL> SELECT /*+ FIRST_ROWS*/ROUND(SUM(bytes/1024/1024/1024)) FROM dba_free_space;

 ROUND(SUM(BYTES/1024/1024/1024))

--------------------------------

                              38

All Invalid objects in database:-

SQL> SELECT COUNT(*) FROM all_objects WHERE Status = 'INVALID';

 COUNT(*)

----------

       641

Total Blocking Session:-

SQL> SELECT count(*) FROM v$lock WHERE BLOCK='1';

 COUNT(*)

----------

         0

Total Locked objects:-

SQL> SELECT COUNT(*) FROM v$locked_object;

 COUNT(*)

----------

         1

Total no of session :-

 SQL> SELECT count(*) FROM v$session;

COUNT(*)

----------

       105

Total no of concurrent Process:-

SQL> SELECT COUNT(*) FROM apps.fnd_concurrent_processes;

 COUNT(*)

----------

       672

Total no of concurrent request:-

SQL> SELECT COUNT(*) FROM apps.fnd_concurrent_requests;

COUNT(*)

----------

     21681

Running Concurrent Requests and scheduled Concurrent Requests:-

SQL> SELECT phase_code,COUNT(*) FROM apps.fnd_concurrent_requests WHERE phase_code IN('P','R') GROUP BY phase_code ORDER BY 1 DESC;

P   COUNT(*)

- ----------

P          4

Last day total no. of concurrent requests:-

SQL> SELECT sysdate -1 , count(*) FROM apps.fnd_concurrent_requests WHERE to_char(actual_completion_date,'YYYYMMDD') =(SELECT to_char(sysdate -1,'YYYYMMDD') FROM dual);

SYSDATE-1            COUNT(*)

------------------ ----------

18-SEP-12                 218

Check archive gaps in primary&Standby:

Check archive gaps in primary&Standby and missing archivelogs:
======================================================================


S>SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#))
APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;


and



Any missing Archive logs can be found by:
==========================================
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Show the top 10 longest-active user sessions

Show the top 10 longest-active user sessions:

================================================

/* top10active.sql
shows the top 10 longest-active user sessions
*/
col osuser format a10 trunc
col LastCallET format 99,999
col sid format 9999
col spid formar 999999
col username format a10 trunc
col uprogram format a25 trunc
col machine format a10 trunc
set linesize 132
set verify off
select * from (
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram,
s.machine, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and module is not null
and s.status = 'ACTIVE'
order by 4 desc)
where rownum < 11;

AFPASSWD - New password utility in EBS 12.1.2

AFPASSWD -New password utility in EBS 12.1.2:

=====================================

AFPASSWD:

In R12.1.1 and before oracle EBS Versions EBS passwords are changed using FNDCPASS.

From R12.1.2 Oracle has come up with another Utility AFPASSWD for the same purpose.

Advantage is that you can run AFPASSWD utility from DB tier or from Midtier where as FNDCPASS can only be run from midtier side.

AFPASSWD is an enhanced version of FNDCPASS, and includes the following features:

     AFPASSWD only prompts for passwords required for the current operation,

allowing separation of duties between applications administrators and database administrators.

This also improves interoperability with Oracle Database Vault. In contrast, the FNDCPASS utility currently requires specification of the APPS and the SYSTEM usernames and corresponding passwords, preventing separation of duties between applications administrators and database administrators.

   

     When changing a password with AFPASSWD, the user is prompted to enter the

New password twice to confirm.

  

     AFPASSWD can be run from the database tier as well as the application tier. In

contrast, FNDCPASS can only be run from the application tier.

AFPASSWD Usage and Syntax:

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-f <FNDUSER>]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-o <DBUSER>]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-a]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-l <ORACLEUSER> [<TRUE>] |[<FALSE>]]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-L [<TRUE>] | [<FALSE>]]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-s] <APPLSYS>

These options have the following functions:

• -c {APPSUSER}[@{TWO_TASK}] - Specifies the connection string to use, the

Applications user, and/or the value of TWO_TASK. This option can be use in

combination with others. If it is not specified, default values from the environment

will be used.

Note: The password will be prompted for, and is not to be

provided in the connection string.

• -f {FNDUSER} - Changes the password for an Applications user. A username that

contains spaces must be enclosed in double quotation marks; for example, "JOHN

SMITH".

• -o {DBUSER} - Changes the password for an Oracle E-Business Suite database user.

Note: This only applies to users listed in the FND_ORACLE_USERID table, not database users in general.

•  -a - Changes all Oracle (ALLORACLE) passwords (except the passwords of APPS,APPLSYS, APPLSYSPUB) to the same password, in the same way as the ALLORACLE mode does in FNDCPASS.

•  -l - Locks individual {ORACLE_USER} users (except required schemas). {TRUE} =LOCK, {FALSE} = UNLOCK.

•  -L - Locks all Oracle (ALLORACLE) users (except required schemas). {TRUE} =LOCK, {FALSE} = UNLOCK.

•  -s {APPLSYS} - Changes the password for the APPLSYS user and the APPS user. This requires the execution of autoconfig (in ALL-TIERS) to distribute the changes on your instance \

Ie: DB-TIER and ADMIN-TIER.

•  -h - Displays help.

 AFPASSWD Examples:

 AFPASSWD Examples:

FNDUSER:

DBUSER:

APPLSYS:

Run autoconfig on db tier and application tier after changing apps password:

Db side:

Application side: