DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, December 31, 2018

EXPDP Failed with ORA-39065, ORA-39079, ORA-06512, ORA-06512, ORA-24033

ERROR :

Error Description: IMPDP fails or terminate with following errors.
ORA-39065: unexpected master process exception in SEND
ORA-39079: unable to enqueue message RP,KUPC$C_3_20120305232114,MCP,KUPC$A_4_232736061619000,1,N
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPC$QUE_INT", line 965
ORA-24033: no recipients for message
Job "SYS"."SCHEMAEXP" stopped due to fatal error at 02:12:47


Solution :


1. Remove the parallel clause from datapump expdp/impdp and retry the job.
2.  If that does not work try increasing the size of streams_pool_size.
Alter system set streams_pool_size=120M;

ASM - ORA-00020: maximum number of processes



The Oracle parameter PROCESSES has been exceeded as a result of multiple database instances connecting to the ASM instance. The default value (often 40) is insufficient to support more than one database connecting to the ASM instance

Solution : Increase the PROCESSES parameter in the ASM parameter file and bounce the ASM instance.

For 11.2 set up, the formula is  PROCESSES = 50 + 50*n
 where n is the number database instances connecting to the Oracle ASM instance. 

where n is the number of instances on the box using ASM for their storage.

NOTE : this formula is for a basic instance and does not accommodate for
 
             * Multiple ARCH processes
             * Multiple LGWR processes

Query to check Table Full Scan

Query:

spool large_table_scans.txt
--Find Large Table Scans 
set linesize 132
SELECT substr(table_owner,1,10) Owner,
       substr(table_name,1,15) Table_Name,
       size_kb, statement_count, reference_count,
       substr(executions,1,4) Exec, 
       substr(executions * reference_count,1,8) tot_scans
FROM (SELECT a.object_owner table_owner,
             a.object_name table_name,
             b.segment_type table_type,
             b.bytes / 1024 size_kb,
             SUM(c.executions ) executions,
             COUNT( DISTINCT a.hash_value ) statement_count,
             COUNT( * ) reference_count
      FROM sys.v_$sql_plan a, sys.dba_segments b, sys.v_$sql c
      WHERE a.object_owner (+) = b.owner
        AND a.object_name (+) = b.segment_name
        AND b.segment_type IN ('TABLE', 'TABLE PARTITION')
        AND a.operation LIKE '%TABLE%'
        AND a.options = 'FULL'
        AND a.hash_value = c.hash_value
        AND b.bytes / 1024 > 1024
        AND a.object_owner != 'SYS'
      GROUP BY a.object_owner, a.object_name, a.operation, b.bytes/1024, b.segment_type
      ORDER BY 4 DESC, 1, 2 );
spool off 

Size need to check as DBAs


How to find database size?

SQL> select sum(bytes)/1024/1024 from dba_data_files;

                              (OR)
SQL>select sum(result) from (
select 'dba_data_files',sum(bytes)/1024/1024/1024 result from dba_data_files
union
select 'dba_temp_files',sum(bytes)/1024/1024/1024 result from dba_temp_files);

(OR)

SELECT ROUND (SUM (used.bytes) / 1024 / 1024 / 1024) || ' GB'
"Database Size",ROUND (SUM (used.bytes) / 1024 / 1024 / 1024)
- ROUND (free.p / 1024 / 1024 / 1024)|| ' GB' "Used space",
ROUND (free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
FROM (SELECT   bytes FROM v$datafile UNION ALL
SELECT bytes FROM v$tempfile UNION ALL SELECT bytes FROM v$log) used,
(SELECT SUM (bytes) AS p FROM dba_free_space)free GROUP BY free.p;

How to find Schema size?

SQL> select sum(bytes)/1024/1024 from dba_segments where OWNER='SCOTT';

How to find tablespace size?
SQL> select tablespace_name,file_name,(bytes/1024/1024) from dba_data_files where tablespace_name='USERS'

How to find free space of tablespace?

SQL> select sum(bytes/1024/1024/1024) from dba_free_space where TABLESPACE_NAME= 'USERS’;

How to find datafile size?

SQL>select file#,status,enabled,checkpoint_change#,bytes,create_bytes,name from v$datafile;

How to find table size?

SQL>select segment_name,segment_type,bytes/1024/1024 MB
 from dba_segments
 where segment_type='TABLE' and segment_name= <’table_name'>;

How to find used size in database?

SQL> select sum(bytes)/1024/1024 from dba_segments;

How to find free space in database?

select sum(bytes)/1024/1024/1024 from dba_free_space;

How to find redo log size?

SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;

How to find temp file size?

SELECT sum(bytes)/1024/1024 temp_size FROM dba_temp_files;

How to find default tablespace in user?

SQL> select default_tablespace from dba_users where username='<User_name>’;

How find default tablespace, user in particular table?

SQL> select OWNER,TABLESPACE_NAME from dba_tables where TABLE_NAME='<Table_name>';

Deconfig Oracle RAC Grid Installation


Steps to deconfig GRID Installation :-

To cleanup the failed installation of GRID,run the script of failed node.


[root@test ~]# cd /u01/app/grid/crs/install/rootcrs.pl  -deconfig -force

[root@test ~]# rm –rf /etc/ora*

[root@test ~]# rm –rf /etc/inittab.no*

[root@test ~]# rm –rf /var/tmp/.oracle/*

Again run script on all other nodes,On the last node in your cluster, you should add the -lastnode

[root@test ~]# cd /u01/app/grid/crs/install/rootcrs.pl  -deconfig -force -lastnode


If its failed,remove manually using below commands,

Manual cleanup GRID Installation :-

Remove Inventory :-

The Oracle inventory location is stored in the oraInst.loc file:

[root@test etc]# cat /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory

inst_group=oinstall

[root@test etc]# cd /u01/app/oraInventory
[root@test etc]# vi ContentsXML/inventory.xml

Remove the GRID_HOME entry details from inventory.xml file.

[root@test etc]# rm -rf /u01/app/grid/*
[root@test etc]# rm -rf /etc/oracle/* 
[root@test etc]# rm /etc/oraInst.loc 
[root@test etc]# rm /etc/oratab 
[root@test etc]# rm /var/tmp/.oracle/*

Show the High Water Mark for Specific Table


SET LINESIZE 300
SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
  CURSOR cu_tables IS
    SELECT a.owner,
           a.table_name
    FROM   all_tables a
    WHERE  a.table_name = Decode(Upper('&&Table_Name'),'ALL',a.table_name,Upper('&&Table_Name'))
    AND    a.owner      = Upper('&&Table_Owner') 
    AND    a.partitioned='NO'
    AND    a.logging='YES'
order by table_name;

  op1  NUMBER;
  op2  NUMBER;
  op3  NUMBER;
  op4  NUMBER;
  op5  NUMBER;
  op6  NUMBER;
  op7  NUMBER;
BEGIN

  Dbms_Output.Disable;
  Dbms_Output.Enable(1000000);
  Dbms_Output.Put_Line('TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK');
  Dbms_Output.Put_Line('------------------------------  ---------------  ---------------  ---------------');
  FOR cur_rec IN cu_tables LOOP
    Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
    Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
                         LPad(op3,15,' ')                ||
                         LPad(op1,15,' ')                ||
                         LPad(Trunc(op1-op3-1),15,' ')); 
  END LOOP;

END;
/

Bursting Fails With File Is Not Accessible From Node/Machine

BI Publisher (formerly XML Publisher) - Version 12.0 to 12.2 [Release 12.0 to 12.2]
Information in this document applies to any platform.

Issue:

Bursting program failing with the following message:
null output =/app/oracle/inst/apps/EBSDEV02_ebsdevapp02/logs/appl/conc/out/o9381314.out
File /app/oracle/inst/apps/logs/appl/conc/out/o9381313.out is not accessible from node/machine
--Exception
File /app/oracle/inst/apps/logs/appl/conc/out/o9381313.out is not accessible from node/machine .
oracle.apps.fnd.cp.request.FileAccessException: File /app/oracle/inst/apps/logs/appl/conc/out/o9381313.out is not accessible from node/machine .
at oracle.apps.fnd.cp.request.RemoteFile.getFile(RemoteFile.java:440)
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:233)
at oracle.apps.fnd.cp.request.Run.main(Run.java:157)

Solution:

1. Ensure that this concurrent program is not waiting for the output of another request. 
2. Check the Parent Concurrent program.
For example: if the bursting concurrent program (B) was submitted from another concurrent program (A). When submitting program B, program A will wait to see if it completed.
However, because program A is still running the output file will not be written and not found by program b.

3. Edit the concurrent program definitions and make the programs incompatible with each other so that each one must complete before the next program is started. 
4. Check the Temp Directory to ensure that it has enough free space and re-test.

Reference:

Bursting Fails With File Is Not Accessible From Node/Machine (Doc ID 1540005.1)

XML Publisher Bursting Failing with error: oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getSystemTempDirectory

Issue:

XML Publisher Bursting program - XDOBURSTREP errors with:

Exception null java.lang.NullPointerException at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getSystemTempDirectory(JCP4XDOBurstingEngine.java:413) at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:106) at oracle.apps.fnd.cp.request.Run.main(Run.java:161)----------------------------------+


Error:

Error received getSystemTempDirectory due to the temporary directory not being defined.


Solution:

1) Set the Temporary Directory through the Admin Tab of XML Publisher Administrator to a valid
path on the concurrent manager node.

Reference:

XML Publisher Bursting Failing with error: oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getSystemTempDirectory (Doc ID 1076944.1)

WSHPSGL: Pick Selection List Generation Errors Out : ORA-06508: PL/SQL: could not find program unit being called

Issue:

WSHPSGL: Pick Selection List Generation Errors Out : ORA-06508: PL/SQL: could not find program unit being called

Symptoms:

On : 12.1.1 version, Pick Release Process

When attempting to pick release,the following error occurs.

ERROR
-----------------------
[26-JAN-11 12:06:54] WMS_DEVICE_INTEGRATION_PVT: No device exist in this Org4045
[26-JAN-11 12:06:54] PICKREL: headeR_id for line index 1 is 3035451
[26-JAN-11 12:06:54] PICKREL: calling cartonize api
[26-JAN-11 12:06:54] PICKREL: Other error: ORA-06508: PL/SQL: could not find program unit being called
Error occurred in Inv_Pick_Release_Pub.Pick_Release
ORA-06508: PL/SQL: could not find program unit being called in Package INV_Pick_
Release_PUB Procedure Pick_Release
Unexpected error from INV_Pick_Release_Pub.Pick_Release. Exiting
SQLCODE: 1 SQLERRM: User-Defined Exception
Exception occurred in WSH_PICK_LIST.Release_Batch_Sub
Error occurred in Release_Batch_Sub
SQLCODE: 1 SQLERRM: User-Defined Exception
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create Internal Requisition=> Destination WMS organization
2. Done Order Import => Run pick release

CAUSE:

Package WMS_CARTNZN_PUB  is invalid.

If compiling package WMS_CARTNZN_PUB you get :

Errors for PACKAGE BODY APPS.WMS_CARTNZN_PUB:
8027/10 PL/SQL: SQL Statement ignored
8027/21 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got
DATE

Solution:

1. Download and review the readme and pre-requisites for Patch 17794932:R12.INV.B

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
 
WMS_CARTONIZATION_TEMP.xdf 120.4.12010000.8

5. Retest the issue.


Reference:

WSHPSGL: Pick Selection List Generation Errors Out : ORA-06508: PL/SQL: could not find program unit being called (Doc ID 1295452.1)

Script to Monitor Cost Manager and other Inventory Interface Managers

#!/bin/bash

###############################################################################
# Script Name : MonInterfaceMgr.sh                                                                                                #
#                                                                                                                                                         #
# Description:                                                                                                                                    #
# Script to monitor Inventory Interface Manager                                                                             #
# Cost Manager; Lot Move Transaction; Material transaction; Move transaction                          #
# to send alert notification with the details to DBA Team if its INACTIVE                                  #
#                                                                                                                                                       #
# Usage : sh <script_name> <ORACLE_SID>                                                                               #
# For example : sh MonInterfaceMgr.sh ORCL                                                                              #
#                                                                                                                                                        #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)                                                            #
###############################################################################

# Initialize variables

INSTANCE=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE 
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   #cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script to monitor Inventory Interface Manager"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID" >> $LOG_FILE
        echo
}

if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_count()
{
 sqlplus -s '/as sysdba' <<!
 set heading off
 set feedback off
 SELECT COUNT(*) FROM
 (
 SELECT
   x.PROCESS_TYPE "Name",
   decode((select '1'
  FROM APPS.FND_CONCURRENT_REQUESTS cr,
  APPS.FND_CONCURRENT_PROGRAMS_VL cp,
  APPS.FND_APPLICATION A
    WHERE cp.concurrent_program_id = cr.concurrent_program_id
   AND cp.CONCURRENT_PROGRAM_NAME = x.PROCESS_NAME
   AND cp.APPLICATION_ID = a.application_id
   AND a.APPLICATION_SHORT_NAME = x.PROCESS_APP_SHORT_NAME
   AND PHASE_CODE != 'C' and rownum=1),'1','Active','Inactive') "Status",
   x.WORKER_ROWS "Worker Rows",
   x.TIMEOUT_HOURS "Timeout Hours",
   x.TIMEOUT_MINUTES "Timeout Minutes",
   x.PROCESS_HOURS "Process Interval Hours",
   x.PROCESS_MINUTES "Process Interval Minutes",
   x.PROCESS_SECONDS "Process Interval Seconds"
 FROM (
   SELECT
   MIPC.PROCESS_CODE ,
   MIPC.PROCESS_STATUS ,
   MIPC.PROCESS_INTERVAL ,
   MIPC.MANAGER_PRIORITY ,
   MIPC.WORKER_PRIORITY ,
   MIPC.WORKER_ROWS ,
   MIPC.PROCESSING_TIMEOUT ,
   MIPC.PROCESS_NAME ,
   MIPC.PROCESS_APP_SHORT_NAME ,
   A.MEANING PROCESS_TYPE ,
   FLOOR(MIPC.PROCESS_INTERVAL/3600) PROCESS_HOURS ,
   FLOOR((MIPC.PROCESS_INTERVAL -
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) PROCESS_MINUTES ,
   (MIPC.PROCESS_INTERVAL - (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600) -
   (FLOOR((MIPC.PROCESS_INTERVAL -
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) * 60)) PROCESS_SECONDS ,
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) TIMEOUT_HOURS ,
   FLOOR((MIPC.PROCESSING_TIMEOUT -
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) * 3600)/60) TIMEOUT_MINUTES
   FROM
   APPS.MTL_INTERFACE_PROC_CONTROLS MIPC,
   APPS.MFG_LOOKUPS A
   WHERE
   A.LOOKUP_TYPE = 'PROCESS_TYPE' AND
   A.LOOKUP_CODE = MIPC.PROCESS_CODE
 ) x
 -- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager; Possible Values: Cost Manager; Lot Move Transaction; Material transaction; Move transaction
 WHERE decode((select '1'
  FROM APPS.FND_CONCURRENT_REQUESTS cr,
  APPS.FND_CONCURRENT_PROGRAMS_VL cp,
  APPS.FND_APPLICATION A
    WHERE cp.concurrent_program_id = cr.concurrent_program_id
   AND cp.CONCURRENT_PROGRAM_NAME = x.PROCESS_NAME
   AND cp.APPLICATION_ID = a.application_id
   AND a.APPLICATION_SHORT_NAME = x.PROCESS_APP_SHORT_NAME
   AND PHASE_CODE != 'C' and rownum=1),'1','Active','Inactive') <> 'Active'
 ORDER BY 1
 );

 exit;
!
}

count=`get_count`
#echo $count

echo "$LOG_DATE" > $ERR_FILE
get_count >> $ERR_FILE
ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l`

if [ $ERR_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Critical : $APPS_ID - One or More Interface Managers are Inactive on $HOST_NAME " $DBA_EMAIL_LIST
 exit
fi

if [ $count -gt 0 ];
then

 sqlplus -s '/as sysdba' <<EOF

 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180
 SET MARKUP HTML ON SPOOL ON -
 HEAD '<title></title> -
 <style type="text/css"> -
    table { background: #eee; } -
    th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
    td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
 </style>' TABLE "border='1' align='left'" ENTMAP OFF

 spool $OUT_FILE


 PROMPT Hi Team,

 PROMPT
 PROMPT Please check below for the Inactive Interface Managers. Please start Inactive Managers ASAP.
 PROMPT


  SELECT
   x.PROCESS_TYPE "Name",
   decode((select '1'
  FROM APPS.FND_CONCURRENT_REQUESTS cr,
  APPS.FND_CONCURRENT_PROGRAMS_VL cp,
  APPS.FND_APPLICATION A
    WHERE cp.concurrent_program_id = cr.concurrent_program_id
   AND cp.CONCURRENT_PROGRAM_NAME = x.PROCESS_NAME
   AND cp.APPLICATION_ID = a.application_id
   AND a.APPLICATION_SHORT_NAME = x.PROCESS_APP_SHORT_NAME
   AND PHASE_CODE != 'C' and rownum=1),'1','Active','Inactive') "Status",
   x.WORKER_ROWS "Worker Rows",
   x.TIMEOUT_HOURS "Timeout Hours",
   x.TIMEOUT_MINUTES "Timeout Minutes",
   x.PROCESS_HOURS "Process Interval Hours",
   x.PROCESS_MINUTES "Process Interval Minutes",
   x.PROCESS_SECONDS "Process Interval Seconds"
 FROM (
   SELECT
   MIPC.PROCESS_CODE ,
   MIPC.PROCESS_STATUS ,
   MIPC.PROCESS_INTERVAL ,
   MIPC.MANAGER_PRIORITY ,
   MIPC.WORKER_PRIORITY ,
   MIPC.WORKER_ROWS ,
   MIPC.PROCESSING_TIMEOUT ,
   MIPC.PROCESS_NAME ,
   MIPC.PROCESS_APP_SHORT_NAME ,
   A.MEANING PROCESS_TYPE ,
   FLOOR(MIPC.PROCESS_INTERVAL/3600) PROCESS_HOURS ,
   FLOOR((MIPC.PROCESS_INTERVAL -
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) PROCESS_MINUTES ,
   (MIPC.PROCESS_INTERVAL - (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600) -
   (FLOOR((MIPC.PROCESS_INTERVAL -
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) * 60)) PROCESS_SECONDS ,
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) TIMEOUT_HOURS ,
   FLOOR((MIPC.PROCESSING_TIMEOUT -
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) * 3600)/60) TIMEOUT_MINUTES
   FROM
   APPS.MTL_INTERFACE_PROC_CONTROLS MIPC,
   APPS.MFG_LOOKUPS A
   WHERE
   A.LOOKUP_TYPE = 'PROCESS_TYPE' AND
   A.LOOKUP_CODE = MIPC.PROCESS_CODE
 ) x
 -- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager; Possible Values: Cost Manager; Lot Move Transaction; Material transaction; Move transaction
 ORDER BY 1;

PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT <b>Step To Start Interface Managers which are in Inactive State:</b>
PROMPT
PROMPT 1. Login wuth SYSADMIN -> Select Responsibility - "Inventory" ->
PROMPT 2. Navigate to "Setup" -> Transactions > Interface Managers
PROMPT 3. Go to Menu -> Tools -> Launch Manager
PROMPT
PROMPT <b>Steps to Start -> "Cost Manager":</b>
PROMPT
PROMPT 1. Login with SYSADMIN -> Select Responsibility - "Inventory"
PROMPT 2. Navigate to "Setup" > Transactions > Interface Managers
PROMPT 3. Select "Cost Manager"
PROMPT 4. Go to Menu -> Tools -> Launch Manager
PROMPT 5. Go to Menu -> View -> Requests -> Query Name = "Cost Manager"
PROMPT
PROMPT <b>Steps to Start -> "Process transaction interface" or "Transaction Manager":</b>
PROMPT
PROMPT 1. Login wuth SYSADMIN -> Select Responsibility - "Inventory"
PROMPT 2. Navigate to "Setup" -> Transactions > Interface Managers
PROMPT 3. Select "Material Transaction"
PROMPT 4. Go to Menu -> Tools -> Launch Manager
PROMPT 5. Go to Menu -> View -> Requests -> Query Name = "Process transaction interface"  and ""Inventory transaction worker"
PROMPT

 SPOOL OFF
 SET MARKUP HTML OFF
 exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Critical : $APPS_ID - One or More Interface Managers are Inactive on $HOST_NAME"
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else
    echo "$LOG_DATE" > $OUT_FILE
 echo "Interface Managers Running Fine" >> $OUT_FILE
fi

Script to Monitor Long Running Concurrent Requests

#!/bin/bash

#############################################################################################
# Script Name : LongRunningReq.sh                                                           #
#                                                                                           #
# Description:                                                                              # 
# Script to check Concurrent requests which are running for more than given threshold (mins)#
# to send alert notification with the details to DBA Team                                   #
#                                                                                           #
# Usage : sh <script_name> <ORACLE_SID> <Time in mins>                                      #
# For example : sh LongRunningReq.sh ORCL 30                                                #
#                                                                                           #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)                                 #
#############################################################################################

# Initialize variables

INSTANCE=$1
THRESHOLD=$2
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script To Check Long Running Concurrent Requests"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <Time in mins>" >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 30" >> $LOG_FILE
        echo
}

if [ $# -lt 2 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_count()
{
 sqlplus -s '/as sysdba' <<!
 set heading off
 set feedback off
 select count(1) from 
 (
  select distinct qt.user_concurrent_queue_name "Queue Name"
      ,c2.user_concurrent_program_name "Program Name" 
     ,a.request_id "Request Id"
     ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
     ,FLVP.meaning "Phase"
     ,FLVS.meaning "Status"
     ,a.argument_text " Arguments " 
     ,b.os_process_id "OS Process"
     ,vs.sid "DB SID"
     ,vp.spid "DB PID"
     ,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
     ,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "Time Spent (Min)"
     ,u.user_name "Who Submitted"
     ,u.email_address "Submitted By - Email"
  from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
   ,applsys.fnd_concurrent_queues q
   ,applsys.fnd_concurrent_queues_tl qt
   ,APPLSYS.fnd_concurrent_programs_tl c2
   ,APPLSYS.fnd_concurrent_programs c
   ,APPLSYS.FND_LOOKUP_VALUES FLVP
   ,APPLSYS.FND_LOOKUP_VALUES FLVS
   ,APPLSYS.FND_USER u
   ,gv\$session vs
   ,gv\$process vp
  where a.controlling_manager = b.concurrent_process_id
    and a.concurrent_program_id = c.concurrent_program_id
    and a.program_application_id = c.application_id
    and c2.concurrent_program_id = c.concurrent_program_id
    and a.phase_code in ('I','P','R','T')
    and u.user_id=a.requested_by
    and a.phase_code=FLVP.Lookup_Code
    and FLVP.Lookup_Type='CP_PHASE_CODE'
    and FLVP.language='US'
    and a.status_code=FLVS.Lookup_Code
    and FLVS.Lookup_Type='CP_STATUS_CODE'
    and FLVS.language='US'
    and FLVS.view_application_id=0
    and b.queue_application_id = q.application_id
    and b.concurrent_queue_id = q.concurrent_queue_id
    and q.application_id = qt.application_id
    and qt.language='US'
    and q.concurrent_queue_id = qt.concurrent_queue_id
    and c2.language = 'US'
    and vs.process (+) = b.os_process_id
    and vs.paddr = vp.addr (+)
    and a.status_code='R'
    and c2.USER_CONCURRENT_PROGRAM_NAME not in ('Planning Manager','Cost Manager')
    and vs.inst_id=vp.inst_id
    and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > $THRESHOLD
  order by 11 desc
 );

 exit;
!
}

count=`get_count`
#echo $count

echo "$LOG_DATE" > $ERR_FILE
get_count >> $ERR_FILE
ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l`

if [ $ERR_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Alert: $APPS_ID - Long Running Concurrent Requests on $HOST_NAME " $DBA_EMAIL_LIST
 exit
fi

if [ $count -gt 0 ];
then

 sqlplus -s '/as sysdba' <<EOF

 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180
 SET MARKUP HTML ON SPOOL ON -
 HEAD '<title></title> -
 <style type="text/css"> -
    table { background: #eee; } -
    th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
    td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
 </style>' TABLE "border='1' align='left'" ENTMAP OFF

 spool $OUT_FILE


 PROMPT Hi Team,

 PROMPT
 PROMPT Please check below concurrent requests which are running for more than $THRESHOLD Mins 
 PROMPT


 select distinct qt.user_concurrent_queue_name "Queue Name"
     ,c2.user_concurrent_program_name "Program Name" 
    ,a.request_id "Request Id"
    ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
    ,FLVP.meaning "Phase"
    ,FLVS.meaning "Status"
    ,a.argument_text " Arguments " 
    ,b.os_process_id "OS Process"
    ,vs.sid "DB SID"
    ,vp.spid "DB PID"
    ,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
    ,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "Time Spent (Min)"
    ,u.user_name "Who Submitted"
    ,u.email_address "Submitted By - Email"
 from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
  ,applsys.fnd_concurrent_queues q
  ,applsys.fnd_concurrent_queues_tl qt
  ,APPLSYS.fnd_concurrent_programs_tl c2
  ,APPLSYS.fnd_concurrent_programs c
  ,APPLSYS.FND_LOOKUP_VALUES FLVP
  ,APPLSYS.FND_LOOKUP_VALUES FLVS
  ,APPLSYS.FND_USER u
  ,gv\$session vs
  ,gv\$process vp
 where a.controlling_manager = b.concurrent_process_id
   and a.concurrent_program_id = c.concurrent_program_id
   and a.program_application_id = c.application_id
   and c2.concurrent_program_id = c.concurrent_program_id
   and a.phase_code in ('I','P','R','T')
   and u.user_id=a.requested_by
   and a.phase_code=FLVP.Lookup_Code
   and FLVP.Lookup_Type='CP_PHASE_CODE'
   and FLVP.language='US'
   and a.status_code=FLVS.Lookup_Code
   and FLVS.Lookup_Type='CP_STATUS_CODE'
   and FLVS.language='US'
   and FLVS.view_application_id=0
   and b.queue_application_id = q.application_id
   and b.concurrent_queue_id = q.concurrent_queue_id
   and q.application_id = qt.application_id
   and qt.language='US'
   and q.concurrent_queue_id = qt.concurrent_queue_id
   and c2.language = 'US'
   and vs.process (+) = b.os_process_id
   and vs.paddr = vp.addr (+)
   and a.status_code='R'
   and c2.USER_CONCURRENT_PROGRAM_NAME not in ('Planning Manager','Cost Manager')
   and vs.inst_id=vp.inst_id
   and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > $THRESHOLD
 order by 11 desc;

 SPOOL OFF
 SET MARKUP HTML OFF
 exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Alert : $APPS_ID - Long Running Concurrent Requests on $HOST_NAME"
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "No Long Running Concurrent Requests" >> $OUT_FILE
fi

Script to check workflow mailer service components status and send notification if DOWN

#!/bin/bash
################################################################################### # Script Name : WorkflowStatusCheck.sh # # # # Description: # # Script to check workflow mailer service components status and # # to send alert notification if one or more workflow mailer components are down # # # # Usage : sh <script_name> <ORACLE_SID> # # For example : sh WorkflowStatusCheck.sh ORCL # # # # Created by : Kiran Jadhav - (https://h2hdba.blogspot.com) # ################################################################################### # Initialize variables INSTANCE=$1 HOST_NAME=`hostname` PROGRAM=`basename $0 | cut -d'.' -f1` export DBA_SCRIPTS_HOME=$HOME/DBA_MON APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'` LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err LOG_DATE=`date` # Source the env . $HOME/.bash_profile . $DBA_SCRIPTS_HOME/.sysenv if [ $? -ne 0 ]; then echo "$LOG_DATE" > $LOG_FILE echo "Please pass correct environment : exiting the script \n" >> $LOG_FILE cat $LOG_FILE exit fi if [ -s $OUT_FILE ]; then echo "$LOG_DATE" > $LOG_FILE echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE rm -f $OUT_FILE cat $LOG_FILE fi # If there is a planned downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window. if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then echo "$LOG_DATE" >> $LOG_FILE echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE cat $LOG_FILE exit fi if [ $DOWNTIME_MODE = "Y" ]; then echo "$LOG_DATE" >> $LOG_FILE echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE cat $LOG_FILE exit fi usage() { echo "$LOG_DATE" > $LOG_FILE echo "Script To Check Workflow Mailer Components Status" >> $LOG_FILE echo "Usage : sh <script_name> <ORACLE_SID> " >> $LOG_FILE echo "For example : sh $PROGRAM.sh $ORACLE_SID" >> $LOG_FILE echo } if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then usage echo "Error : Insufficient arguments." >> $LOG_FILE cat $LOG_FILE exit fi get_count() { sqlplus -s '/as sysdba' <<! set heading off set feedback off select count(1) from ( select component_id, startup_mode,component_status,component_type,component_name from applsys.fnd_svc_components where component_status <> 'RUNNING' order by component_id ); exit; ! } count=`get_count` #echo $count echo "$LOG_DATE" > $ERR_FILE get_count >> $ERR_FILE ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l` if [ $ERR_COUNT -gt 0 ]; then cat $ERR_FILE | mailx -s "<ERROR> Critical : $APPS_ID - Workflow Mailer Components are DOWN on $HOST_NAME " $DBA_EMAIL_LIST exit fi if [ $count -gt 0 ]; then sqlplus -s '/as sysdba' <<EOF SET ECHO OFF SET pagesize 1000 set feedback off set lines 180 SET MARKUP HTML ON SPOOL ON - HEAD '<title></title> - <style type="text/css"> - table { background: #eee; } - th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } - td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } - </style>' TABLE "border='1' align='left'" ENTMAP OFF spool $OUT_FILE PROMPT Hi Team, PROMPT PROMPT Logon on to OAM (Oracle Applications Manager) and make sure the following processes are running, if not restart them. PROMPT select component_id, startup_mode,component_status,component_type,component_name from applsys.fnd_svc_components where component_status <> 'RUNNING' order by component_id; SPOOL OFF SET MARKUP HTML OFF exit; EOF ( echo "To: $DBA_EMAIL_LIST" echo "MIME-Version: 1.0" echo "Content-Type: multipart/alternative; " echo ' boundary="PAA08673.1018277622/server.xyz.com"' echo "Subject: Critical : $APPS_ID - Workflow Mailer Components are DOWN on $HOST_NAME " echo "" echo "This is a MIME-encapsulated message" echo "" echo "--PAA08673.1018277622/server.xyz.com" echo "Content-Type: text/html" echo "" cat $OUT_FILE echo "--PAA08673.1018277622/server.xyz.com" ) | /usr/sbin/sendmail -t echo "`date`" > $LOG_FILE echo "Details sent through an email" >> $LOG_FILE cat $LOG_FILE else echo "`date`" > $OUT_FILE echo "Workflow Mailer Components are up and running fine" >> $OUT_FILE fi

Script To Monitor "Gather Schema Statistics" concurrent request

#!/bin/bash

#############################################################################################################
# Script Name : MonGSSRequest.sh                                                                                                    #
#                                                                                                                                                            #
# Description:                                                                                                                                       #
# Script To Monitor "Gather Schema Statistics" concurrent request.                                                  #
# If error-out then send notification to DBA Team                                                                              #
#                                                                                                                                                            #
# Usage : sh <script_name> <ORACLE_SID> <History_No_Days>                                                   #
#                                                                                                                                                              #
#                                                                                                                                                              #
#                                                                                                           #
#############################################################################################################

# Initialize variables

INSTANCE=$1
THRESHOLD=$2
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE 
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script to monitor Gather Schema Statistics Conc. request"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <no_days> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 7" >> $LOG_FILE
        echo
}

if [ $# -lt 2 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_err_count()
{
 sqlplus -s '/as sysdba' <<!
 set heading off
 set feedback off
 select count(*) from
  (
  SELECT a.request_id "Req Id"
      ,substr(u.user_name,1,25) "Submitted by"
      ,decode(a.PHASE_CODE,'C','Completed','I','Inactive','P','Pending','R','Running','NA') "Phasecode",
      decode(a.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"
      , a.argument_text " Arguments "
      , TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
      , TO_CHAR(actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "Completion Date"
      , ctl.user_concurrent_program_name "Conc Program Name",a.COMPLETION_TEXT "Error/Warning"
      FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
      ,applsys.fnd_concurrent_queues q
      ,APPLSYS.fnd_concurrent_programs c
      ,APPLSYS.fnd_concurrent_programs_tl ctl
      ,APPLSYS.fnd_user u
      WHERE a.controlling_manager = b.concurrent_process_id
      AND a.concurrent_program_id = c.concurrent_program_id
      AND a.program_application_id = c.application_id
      AND a.status_code in ('D','E','G','H','M','S','T','U','X','W')  -- for the status ERROR
      AND a.phase_code = 'C' -- Phase code COMPLETED
      AND actual_start_date >= sysdate - $THRESHOLD
      AND b.queue_application_id = q.application_id
      AND b.concurrent_queue_id = q.concurrent_queue_id
      AND ctl.concurrent_program_id = c.concurrent_program_id
      AND ctl.LANGUAGE = 'US'
      AND a.REQUESTED_BY=u.user_id
      AND ctl.user_concurrent_program_name = 'Gather Schema Statistics'
  );

 exit;
!
}

err_count=`get_err_count`
#echo $err_count

echo "$LOG_DATE" > $ERR_FILE
get_err_count >> $ERR_FILE
ERR_FILE_COUNT=`grep "ORA-" $ERR_FILE |wc -l`

if [ $ERR_FILE_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Alert: $APPS_ID - Gather Schema Statistics Request not completed normal on $HOST_NAME " $DBA_EMAIL_LIST
 exit
fi

if [ $err_count -gt 0 ];
then

 sqlplus -s '/as sysdba' <<EOF

 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180
 SET MARKUP HTML ON SPOOL ON -
 HEAD '<title></title> -
 <style type="text/css"> -
    table { background: #eee; } -
    th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
    td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
 </style>' TABLE "border='1' align='left'" ENTMAP OFF

 spool $OUT_FILE


 PROMPT Hi Team,

 PROMPT
 PROMPT Gather Schema Statistics request not completed normal, take necessary action ASAP.
 PROMPT


 SELECT a.request_id "Req Id"
    ,substr(u.user_name,1,25) "Submitted by"
    ,decode(a.PHASE_CODE,'C','Completed','I','Inactive','P','Pending','R','Running','NA') "Phasecode",
    decode(a.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"
    , a.argument_text " Arguments "
    , TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
    , TO_CHAR(actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "Completion Date"
    , ctl.user_concurrent_program_name "Conc Program Name",a.COMPLETION_TEXT "Error/Warning"
    FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_concurrent_programs_tl ctl
    ,APPLSYS.fnd_user u
    WHERE a.controlling_manager = b.concurrent_process_id
    AND a.concurrent_program_id = c.concurrent_program_id
    AND a.program_application_id = c.application_id
    AND a.status_code in ('D','E','G','H','M','S','T','U','X','W')  -- for the status ERROR
    AND a.phase_code = 'C' -- Phase code COMPLETED
    AND actual_start_date >= sysdate - $THRESHOLD
    AND b.queue_application_id = q.application_id
    AND b.concurrent_queue_id = q.concurrent_queue_id
    AND ctl.concurrent_program_id = c.concurrent_program_id
    AND ctl.LANGUAGE = 'US'
    AND a.REQUESTED_BY=u.user_id
    AND ctl.user_concurrent_program_name = 'Gather Schema Statistics';

    SPOOL OFF
 SET MARKUP HTML OFF
 exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Alert: $APPS_ID - Gather Schema Statistics Request not completed normal on $HOST_NAME"
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "`date`" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else
    echo "`date`" > $OUT_FILE
 echo "Gather Schema Statistics Request completed normal in last $THRESHOLD Days" >> $OUT_FILE
fi