LONG-CM-JOBS

 set echo off pages 100 lines 202 

column REQUEST heading 'Request' format a9 

column PHASE heading 'Phase' format A8 

column STATUS heading 'Status' format A8 

column PROGRAM heading 'Program Name' format A60 

column SHORT heading 'Short Name' format A15 

column REQUESTOR heading 'Requestor' format A15 

column START_TIME heading 'Start Time' format A15 

column RUN_TIME justify left heading 'Time(e)' format 999999.9 

column OSPID heading 'OSPID' format a5 

column OS_PIDa heading 'OSPIDA' format a6 

column SID heading 'SID' format 99999 

column serial# heading 'Serial#' format 99999 

select substr(fcrv.request_id,1,9)REQUEST, 

decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE, 

decode(fcrv.status_code, 

'A','Waiting', 

'B','Resuming', 

'C','Normal', 

'F','Scheduled', 

'G','Warning', 

'H','On Hold', 

'I','Normal', 

'M','No Manager', 

'Q','Standby', 

'R','Normal', 

'S','Suspended', 

'T','Terminating', 

'U','Disabled', 

'W','Paused', 

'X','Terminated', 

'Z','Waiting',fcrv.status_code)STATUS, 

substr(fcrv.program,1,60)PROGRAM, 

substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT, 

substr(fcrv.requestor,1,15)REQUESTOR, 

-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME, 

round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME, 

substr(fcr.oracle_process_id,1,7)OSPID, 

s.sid,s.serial# 

--substr(fcr.os_process_id,1,7)OS_PIDa 

from apps.fnd_conc_req_summary_v fcrv, 

apps.fnd_concurrent_requests fcr, 

v$session s, 

v$process p 

where fcrv.phase_code = 'R' 

and fcrv.request_id = fcr.request_id 

and s.paddr = p.addr 

and fcr.oracle_process_id = p.spid 

and fcrv.concurrent_program_id not in ('40112','40113','36887') 

--and trunc(fcrv.actual_start_date) like trunc(sysdate) 

order by PHASE, STATUS, REQUEST desc;

FND-PROFILEOPTION-VALUE-SELECT QUERRY

 set lines 400;

 set pages 4000;

 col profile_option_value format a25

 col user_profile_option_name format a25

col Level format a14

 select user_profile_option_name,

  2  decode(level_id,10001,'Site',

  3  10002,'Application',

  4  10003,'Responsibility',

  5  10004,'User',

  6  10005,'Server',

  7  10006,'Organization',

  8  10007, 'ServResp',

  9  level_id) "Level",

 10  profile_option_value,

 11  level_value

 12  from

 13  fnd_profile_option_values,

 14  fnd_profile_options_vl

 15  where

 16  upper(user_profile_option_name) like upper('%ICX%%SESS%%TIME%')

 17  AND

 18  fnd_profile_option_values.profile_option_id =

 19  fnd_profile_options_vl.profile_option_id;

USER_PROFILE_OPTION_NAME  Level          PROFILE_OPTION_VALUE      LEVEL_VALUE

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

ICX:Session Timeout       Site           30                                  0

SQL>

TABLE-LEVEL-LOCKS

 select distinct a.process

 from v$session a, v$locked_object b, dba_objects c

 where b.object_id = c.object_id

 and a.sid = b.session_id

 and OBJECT_NAME=upper('&TABLE_NAME');

DEV> SELECT * FROM v$session where sid=2144;

no rows selected

DEV> SELECT * FROM dba_ddl_locks where name like 'XX_WIP_SHORTAGE_REP_PKG%';

no rows selected

DB-Alert-log

 SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

PROD1    READ WRITE

SQL> show parameter backg

NAME                                 TYPE        VALUE

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

background_core_dump                 string      partial

background_dump_dest                 string      /us1101/app/oracle/product/11. 2.0/diag/rdbms/trace

SQL>

FRONT-END CONNECTED_LIST

 

SQL> select count(distinct d.user_name) from apps.fnd_logins a,

v$session b, v$process c, apps.fnd_user d

where b.paddr = c.addr

and a.pid=c.pid

and a.spid = b.process

and d.user_id = a.user_id

and (d.user_name = 'USER_NAME' OR 1=1);  2    3    4    5    6    7

COUNT(DISTINCTD.USER_NAME)

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

                       237

3. Run the following queries:

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

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

    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;

http://appsdbafix.blogspot.com/2013/02/number-of-users-connected-with-oracle.html

PURGE-RUNNINGLOG

 tail -10000 filename.log > filename_tail.log 

head -10000 filename.log > filename_head.log

and then 

we can give 

> filename.log

Linux-PORT-Usage

 ss -lntu|grep ":80*"

DB-CONNECTED-COUNT

 select machine,count(*) from v$session group by machine;

CM-JOBS-ON-HOLD

 Put jobs on hold

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

Put Jobs on Hold

 

<<< Check Putty Spool Log >>>

SQL> spool Spool_Jobs_on_hold_09JAN2021.txt;

SQL> SELECT SYSTIMESTAMP FROM DUAL;

 

1.Connect as apps user, Drop old table (jobs_already_on_hold)

 

SQL> DROP TABLE jobs_already_on_hold;

 

2. List out the Jobs which were already on HOLD

 

SQL> SELECT request_id, phase_code, status_code

     FROM fnd_concurrent_requests

     WHERE hold_flag = 'Y';

 

3.Create backup of the table with the jobs which were already ON HOLD.

 

SQL> 

 

CREATE TABLE jobs_already_on_hold

AS

   (SELECT request_id, phase_code, status_code

      FROM fnd_concurrent_requests

     WHERE hold_flag = 'Y');

                

4. Verify the count from backup table with the above query output

 

SQL> SELECT COUNT (*) FROM jobs_already_on_hold;

 

SQL> SELECT * FROM jobs_already_on_hold;

 

5.Now, Place the pending jobs on HOLD using the below update command.

 

SQL> 

 

UPDATE fnd_concurrent_requests

   SET hold_flag = 'Y'

WHERE phase_code = 'P' AND hold_flag = 'N';

 

SQL> COMMIT;

 

6. After Putting the Jobs ON HOLD, now verify if any Running Requests with PAUSED Status.

   If there are any Running requests with PAUSED status, please verify the child requests status of this Program. 

   If there were any put on HOLD as part of the putting Jobs on Hold process.

   Release them from HOLD and process the requests.

  

7.Check again the pending scheduled program during the time frame 3 PM to 5 PM CST  

 

8.Please ensure to check the below query for every 5 minutes until it returns 0 rows.

  List the pending ,running and passed  requests

  

SQL> 

SELECT REQUEST_ID,

       PHASE_CODE,

       STATUS_CODE,

       HAS_SUB_REQUEST,

       IS_SUB_REQUEST,

       hold_flag,

       REQ_INFORMATION

  FROM apps.fnd_concurrent_requests

WHERE    (phase_code = 'P' AND hold_flag = 'N')

       OR phase_code = 'R'

       OR status_code = 'W';

##############################################USE THIS FOR RUNNIG REQUESTS##############################

SQL> col USER_CONCURRENT_PROGRAM_NAME for a30

SQL> col REQUESTOR for a10

SELECT request_id, --parent_request_id,      

         user_concurrent_program_name,

         --program,

        --(select responsibility_name from apps.fnd_responsibility_vl fr where fr.responsibility_id = fcs.responsibility_id)    responsibility_name,

      --   program_short_name,

         argument_text,

         requestor,

         phase_code,

         status_code,

      --   completion_text,

         to_char (actual_start_date, 'DD-MON-YY HH24:MI:SS') actual_start_date,

         to_char (actual_completion_date, 'DD-MON-YY HH24:MI:SS') actual_completion_date,

         NUMTODSINTERVAL (

            NVL (actual_completion_date, SYSDATE) - actual_start_date,

            'day')

            run_time,requested_start_date

    FROM apps.fnd_conc_req_summary_v fcs

WHERE 1=1

--and program like 'Requisition%Import%'

--AND FCS.REQUEST_ID = 99819130

--AND REQUESTOR = 'RMUTTA'

and PHASE_CODE = 'R'   -- Request Status

--AND STATUS_CODE <> 'Q'

order by  run_time desc;

 

** once activity is completed and start the application services and perform the sanity checks and release the on hold jobs

 

#####################################################################CHECK BEFORE RELEASE################

- Release Jobs from HOLD

connect as apps user and perform the below:

 

SQL> 

 

UPDATE fnd_concurrent_requests

   SET hold_flag = 'N'

WHERE     hold_flag = 'Y'

       AND request_id NOT IN (SELECT request_id FROM jobs_already_on_hold);

                   

SQL> COMMIT;

=====Cancel requests manually============================

update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id='&REQUEST_ID';

Release child requests

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

UPDATE fnd_concurrent_requests SET hold_flag = 'N' where REQUEST_ID='&REQUEST_ID';

DB OPEN TIME

  SET LINES 200

SET PAGES 999

COLUMN INSTANCE_NAME FOR A20

SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME"

FROM V$INSTANCE;

REQ-HIST

 REM +======================================================================+

REM

REM File Name: reqhistory.sql

REM 

REM Description:

REM   Query To Check Concurrent Program Run History

REM   

REM Notes:

REM   Usage: sqlplus <apps_user/apps_passwd> @reqhistory.sql 

REM 

REM Input Required :

REM    Number of days and User Concurrent Program Name  

REM   

REM +======================================================================+

ACCEPT NO_DAYS PROMPT "Enter Number of Days for History: ";

ACCEPT USER_CONC_PROG_NAME PROMPT "Enter User Concurrent Program Name: ";

        clear columns

set lines 180

set pages 100

col Parameters for a20 WORD_WRAPPED

set pages 100

col "Conc Program Name" for a30 WORD_WRAPPED

col "Started at" for a20

col "Completed at" for a20

col "Username" for a10 WORD_WRAPPED

SELECT distinct t.user_concurrent_program_name "Conc Program Name",

r.REQUEST_ID "Request ID",

to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",

to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') "Completed at",

decode(r.PHASE_CODE,'C','Completed','I','Inactive','P','Pending','R','Running','NA') "Phasecode",

decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',

'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters",

u.user_name "Username",

--ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,

--              2

--            ) "Runtime (in Minutes)" 

round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)"

FROM

apps.fnd_concurrent_requests r ,

apps.fnd_concurrent_programs p ,

apps.fnd_concurrent_programs_tl t,

apps.fnd_user u, apps.fnd_conc_req_summary_v v

WHERE 

r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID

AND r.actual_start_date >= (sysdate - &NO_DAYS)

--AND r.requested_by=22378

AND   r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID

AND t.concurrent_program_id=r.concurrent_program_id

AND r.REQUESTED_BY=u.user_id

AND v.request_id=r.request_id

--AND r.request_id ='2260046' in ('13829387','13850423')

and t.user_concurrent_program_name like '&USER_CONC_PROG_NAME'

order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;

undef NO_DAYS

undef USER_CONC_PROG_NAME

FNDNODES

 clear columns

set lines 180

set pages 50

col NODE_NAME for a15 WORD_WRAPPED

COL SUPPORT_DB for a12

COL SUPPORT_CP for a12

COL SUPPORT_ADMIN for a15

COL SUPPORT_FORMS for a15

COL SUPPORT_WEB for a12

COL HOST for a15 WORD_WRAPPED

Col STATUS for a10

col DOMAIN for a20 WORD_WRAPPED

select NODE_NAME,SUPPORT_DB,SUPPORT_CP,SUPPORT_ADMIN,SUPPORT_WEB,SUPPORT_FORMS,STATUS,HOST,DOMAIN 

from apps.fnd_nodes;