Oracle Cluster commands

To stop all cluster services,ASMm, database and instance:

Login as root 

export GRID_HOME=/oradata/oracle/TSTDB/bin/
cd $GRID_HOME

[root@rac01 ]# ./crsctl stop cluster

To start all cluster services,ASM, database and instance:

Login as root 

export GRID_HOME=/oradata/oracle/TSTDB/bin/
cd $GRID_HOME

[root@rac01 ]# ./crsctl start cluster

Login as root 

export GRID_HOME=/oradata/oracle/TSTDB/bin/
cd $GRID_HOME


$ ./crsctl check cluster -all [verify cluster status on all nodes]
$ ./crsctl stop cluster -all [stop cluster on all nodes]
$ ./crsctl start cluster -all [start cluster on all nodes]
$ ./crsctl check cluster -n <nodename> [verify the cluster status on a particular remote node]

To check CRS (Cluster ready service) status:

Login as root

export GRID_HOME=/oradata/oracle/TSTDB/bin/
cd $GRID_HOME

[root@rac01 ]# ./crsctl check crs

To check status of node apps - where node is the name of the node where the applications are running:

Login as root

cd /oradata/oracle/db/tech_st/11.2.0/bin/

[root@rac01 ]# ./srvctl status nodeapps -n node

crs_stat: Displays CRS resource status:

Login as root

export GRID_HOME=/oradata/oracle/TSTDB/bin/
cd $GRID_HOME

[root@rac01 ]# ./crs_stat -t

To start particular resource:

Login as root

export GRID_HOME=/oradata/oracle/TSTDB/bin/
cd $GRID_HOME

[root@rac01 ]# ./crsctl start resource ora.oc4j

RAC Database Startup, Stop and Status using srvctl

RAC Database Startup in NORMAL mode:

Run below command from primary node as a oracle owner:

srvctl stop database -d DB_NAME

srvctl start database -d DB_NAME

srvctl status database -d DB_NAME


RAC Database Startup in MOUNT /NOMOUNT / RESTRICT mode:

Startup in mount mode:

srvctl start database -d DB_NAME -o mount

Startup in nomount mode:

srvctl start database -d DB_NAME -o nomount

Startup in restrict mode:

srvctl start database -d DB_NAME -o restrict


START / STOP Specific RAC Instance:

To Stop specific RAC instance:

srvctl stop instance -d DB_NAME -i INSTANCE_NAME


To Start specific RAC instance:

srvctl start instance -d DB_NAME -i INSTANCE_NAME

PRVF-4664 : Found inconsistent name resolution entries for SCAN name

Issue:

INFO: PRVF-4657 : Name resolution setup check for "rac-scan" (IP address: 192.168.1.50) failed
INFO: ERROR:
INFO: PRVF-4657 : Name resolution setup check for "rac-scan" (IP address: 192.168.1.40) failed
INFO: ERROR:
INFO: PRVF-4664 : Found inconsistent name resolution entries for SCAN name "rac-scan"
INFO: Verification of SCAN VIP and Listener setup failed

Cause:

In my case DNS is configured, still I was getting this error due to the scan host entries added into the host file.

[root@rac02 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6

#Public IP
192.168.1.10   rac01.dba.com  rac01
192.168.1.20   rac02.dba.com  rac02

#Private IP
192.168.2.10  rac01-priv.dba.com        rac01-priv
192.168.2.20  rac02-priv.dba.com        rac02-priv

#Virtual IP
192.168.1.11 rac01-vip.dba.com          rac01-vip
192.168.1.21 rac02-vip.dba.com          rac02-vip

#SCAN IP
192.168.1.30   rac-scan.dba.com        rac-scan
192.168.1.40   rac-scan.dba.com        rac-scan
192.168.1.50    rac-scan.dba.com        rac-scan
[root@rac02 ~]#


Solution:

Solution would be to remove/comment the scan host entries in the host file and reconfigure the SCAN Listener.

Comment the SCAN host entries as shown below: 

[root@rac02 ~]# cat /etc/hosts

#SCAN IP
#192.168.1.30   rac-scan.dba.com        rac-scan
#192.168.1.40   rac-scan.dba.com        rac-scan
#192.168.1.50    rac-scan.dba.com        rac-scan

Steps to reconfigure the scan listener:

[grid@rac01 ~]$ cd /u01/11.2.0/grid

[grid@rac01 grid]$ cd bin

[grid@rac01 bin]$ ./srvctl stop scan_listener

[grid@rac01 bin]$ ./srvctl stop scan

[grid@rac01 bin]$ ./srvctl config scan

SCAN name: rac-scan, Network: 1/192.168.1.0/255.255.255.0/eth1

SCAN VIP name: scan1, IP: /rac-scan/192.168.1.30

[grid@rac01 bin]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1561

[grid@rac01 bin]$

[grid@rac01 bin]$ exit

logout

[root@rac01 rpm]# cd ..

[root@rac01 grid]# cd /u01/11.2.0/grid/bin

[root@rac01 bin]# ./srvctl modify scan -h

Modifies the SCAN name.

Usage: srvctl modify scan -n <scan_name>

    -n <scan_name>           Domain name qualified SCAN name

    -h                       Print usage

[root@rac01 bin]# ./srvctl modify scan -n rac-scan

[root@rac01 bin]# ./srvctl config scan

SCAN name: rac-scan, Network: 1/192.168.1.0/255.255.255.0/eth1

SCAN VIP name: scan1, IP: /rac-scan/192.168.1.40

SCAN VIP name: scan2, IP: /rac-scan/192.168.1.50

SCAN VIP name: scan3, IP: /rac-scan/192.168.1.30

[root@rac01 bin]# ./srvctl modify scan_listener -u

[root@rac01 bin]# ./srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1561

SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1561

SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1561

[root@rac01 bin]# ./srvctl start scan_listener

[root@rac01 bin]# ./srvctl config scan

SCAN name: rac-scan, Network: 1/192.168.1.0/255.255.255.0/eth1

SCAN VIP name: scan1, IP: /rac-scan/192.168.1.40

SCAN VIP name: scan2, IP: /rac-scan/192.168.1.50

SCAN VIP name: scan3, IP: /rac-scan/192.168.1.30

[root@rac01 bin]#

Script to Monitor Oracle Database Sessions Consuming High TEMP Tablespace

Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to check database sessions which are consuming TEMP space more than given threshold(in MB) of TEMP tablespace 
and to send alert notification with the database session details. 



Download(TempTsUsage.sh)

Download TempTsUsage.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 TempTsUsage.sh

#!/bin/bash

####################################################################################################
# Script Name : TempTsUsage.sh                                                                     #
#                                                                                                  #
# Description:                                                                                     #
# Script to check database sessions which are consuming TEMP space more than given threshold(in MB)#
# of TEMP tablespace and to send alert notification with the database session details              #          
#                                                                                                  #
# Usage : sh <script_name> <ORACLE_SID> <Used in MB>                                               #
# For example : sh TempTsUsage.sh ORCL 200                                                         #
#                                                                                                  #
# 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 TEMP Tablespace usage for the database session"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <Used in MB> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 200" >> $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 feedback off
 set head off
 set feed off;
 set veri off;
 select count(1) from (
 SELECT   T.tablespace,S.SID,S.serial#,S.username, S.osuser, s.module,
 S.program,S.MACHINE , SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, 
 COUNT(*) sort_ops
 FROM     v\$sort_usage T, v\$session S, dba_tablespaces TBS, v\$process P
 WHERE    T.session_addr = S.saddr
 AND      S.paddr = P.addr
 AND      T.tablespace = TBS.tablespace_name
 GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
 S.program, TBS.block_size, T.tablespace,S.MACHINE)
    where MB_USED > $THRESHOLD;

    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 - Database Sessions Consuming High TEMP Tablespace on $HOST_NAME" $DBA_EMAIL_LIST
 exit
fi

if [ $count -gt 0 ];
then

  sqlplus -s '/as sysdba' <<EOF

  set echo off
  set feedback off
  set lines 1000
  col bytes for 999999999999999.99
  col value for 999999999999999.99
  set long 2000000
  set pagesize 300
  set linesize 300
  col SQL_FULLTEXT for a9999
  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 database sessions which are consuming more than $THRESHOLD MB of TEMP Tablespace:
  PROMPT


  select * from (
  SELECT   T.tablespace,S.SID,S.serial#,S.username, S.osuser, s.module,
     S.program,S.MACHINE , SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, 
     COUNT(*) sort_ops
  FROM     v\$sort_usage T, v\$session S, dba_tablespaces TBS, v\$process P
  WHERE    T.session_addr = S.saddr
  AND      S.paddr = P.addr
  AND      T.tablespace = TBS.tablespace_name
  GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.client_identifier,
     S.program, TBS.block_size, T.tablespace,S.MACHINE)
       where MB_USED > $THRESHOLD;

  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 - Database Sessions Consuming High TEMP Tablespace 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 "There are no high temp usage sessions" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID> <Used in MB>

$ cd $HOME/DBA_MON/bin
$ sh TempTsUsage.sh ORCL 200

This script will send the notification with database session details which are consuming 

more than 200MB of TEMP tablespace.

Script To Monitor Database Tablespace Usage

Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to monitor all Tablespace Usage and to send alert notification if Tablespace free space % is less than given threshold. 

This script takes care if any datafile is in autoextensible ON, so free space will 

be calculated by considering the MAX Size of datafile.

Download(TSUsageMon.sh)

Download TSUsageMon.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 TSUsageMon.sh

#!/bin/bash

#####################################################################################
# Script Name : TSUsageMon.sh                                                       #
#                                                                                   #
# Description:                                                                      #
# Script to monitor all Tablespace usage and                                        #
# to send alert notification if Tablespace free space % is less than given threshold#
#                                                                                   #
# Usage : sh <script_name> <ORACLE_SID> <%Free Threshold>                           #
# For example : sh TSUsageMon.sh ORCL 10                                            #
#                                                                                   #
# 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 Monitor Tablespace Usage"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <%Free Threshold> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 10"  >> $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 a.tablespace_name,
  SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "Total (MB)",
  (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "Used (MB)",
  round(((SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Used", 
  (SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "Free (MB)",
  round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Free"
  --round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
  from
  dba_data_files a,
  sys.filext$ b,
  (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
  where a.file_id = b.file#(+)
  and a.tablespace_name = c.tablespace_name
  GROUP by a.tablespace_name, c."Free"/1024
  HAVING round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) <= $THRESHOLD
  order by a.tablespace_name
 );

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 - Tablespace Free space is less than $THRESHOLD% 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 Tablespaces which are having less than $THRESHOLD% of free space 
 PROMPT


 select distinct a.tablespace_name,
 SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "Total (MB)",
 (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "Used (MB)",
 round(((SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Used", 
 (SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "Free (MB)",
 round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Free"
 --round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
 from
 dba_data_files a,
 sys.filext$ b,
 (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
 where a.file_id = b.file#(+)
 and a.tablespace_name = c.tablespace_name
 GROUP by a.tablespace_name, c."Free"/1024
 HAVING round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) <= $THRESHOLD
 order by a.tablespace_name;

 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 - Tablespace Free space is less than $THRESHOLD% 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 "Tablespace Usage is Normal" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID> <%Free Threshold>

$ cd $HOME/DBA_MON/bin
$ sh DBAlertMon.sh VIS 10

This script will send the notification with details if there are any tablespaces which are having less 10% of free space.

Sample Notification Mail:

Script to Monitor Long Running Concurrent Requests

Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to check Concurrent requests which are running for more than given threshold (mins) to send alert notification with the details to DBA Team. 



Download(LongRunningReq.sh)

Download LongRunningReq.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 LongRunningReq.sh

#!/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

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID> <Time in mins>

$ cd $HOME/DBA_MON/bin
$ sh LongRunningReq.sh ORCL 30

This script will send the notification with Concurrent request details which are running for 

more than 30 minutes.