Use Oracle date function to implement alert in shell script

 

1         Practice: calculate diff of DB time column in shell script

1.1        Practice: check_diff_time.sh

oracle@oraclevm01: /monitoring/bin  % : more check_diff_time.sh

#!/bin/bash

lag=$(sqlplus -s  TESTUSER/oracle@pstg <<END

set pagesize 0 feedback off verify off heading off echo off;

select ROUND(TO_NUMBER(cast(target_commit as date)-cast(source_commit as date))*24*60) as lag_time from TESTUSER.HEARTBEAT

exit;

END

)

echo $lag

 

https://unix.stackexchange.com/questions/85180/assigning-the-output-of-a-sql-query-to-variable

https://stackoverflow.com/questions/17774405/bash-sql-query-outputs-to-variable

1.2        Practice: check_diff_time.sh

oracle@oraclevm01: /monitoring/bin  % : vi check_diff_time.sh

#!/bin/bash

export LOGDIR=/u01/app/oracle/monitoring/log

SP_CASS_PPE_OUT=$LOGDIR/hb.out

sqlplus -s <<EOF>> $SP_CASS_PPE_OUT TESTUSER/oracle@pstg

select ROUND(TO_NUMBER(cast(target_commit as date)-cast(source_commit as date))*24*60) as lag_time from TESTUSER.HEARTBEAT

/

EOF

 

1.3        Practice: Pass variable to sqlplus in shell script

dbconn=”testuser/oracle@pdb01″

# Get the Lag time of Heartbeat

LAG_HB=`sqlplus -s $dbconn <<END

set pagesize 0 feedback off verify off heading off echo off;

select ROUND(TO_NUMBER(cast(target_commit as date)-cast(source_commit as date))*24*60) as lag_time from TESTUSER.HEARTBEAT

exit;

END

`

echo $LAG_HB

 

 

1.4        PracticePass variables to where of SQL in shell script

1.4.1        Option1: use read command

For this case instead of calling @select.sql just put the contents of @select.sql to your shell script. In this case you will be able to pass shell variables.
Example:

#!/bin/bash
echo “Enter table name:”
read answer
sqlplus ‘test/****’ <<!
spool /tmp/columns.sql
select * from user_tab_cols where table_name=’$answer’;
pool off
exit
!

1.4.2        Option2: Use define command

Example:

#!/bin/bash
echo “Enter table name:”
read answer
sqlplus ‘test/****’ <<!
define answer=’$answer’
spool /tmp/columns.sql
@select.sql
spool off
exit
!

 

Contents of select.sql would be in this case:

select * from user_tab_cols where table_name=’&answer’;

 

https://community.oracle.com/thread/855475?start=0&tstart=0

 

1.5        Practice: check oracle refresh job

https://stackoverflow.com/questions/6212219/passing-parameters-to-a-bash-function https://stackoverflow.com/questions/4181703/how-to-concatenate-string-variables-in-bash

 

1.5.1        Practice: check refresh job in shell

oracle@oraclevm01: /monitoring/bin  % : more check_refresh_job.sh

#!/bin/bash

 

lag=$(sqlplus -s  testuser/oracle@pdb01 <<END

set pagesize 0 feedback off verify off heading off echo off;

select STATUS,

ROUND(TO_NUMBER(cast(sysdate as date)-cast(ACTUAL_START_DATE as date))*24*60) as JOB_LAG,

extract(day from 24*60*RUN_DURATION) RUN_DURATION_MINUTES

from

( SELECT

 STATUS,

 ACTUAL_START_DATE,

 RUN_DURATION

   FROM sys.dba_scheduler_job_run_details

   WHERE JOB_NAME =’TESTUSER_REFRESH’

   AND OWNER =’TESTUSER’

   AND actual_start_date > systimestamp – numtodsinterval(24,’hour’)

   ORDER BY actual_start_date DESC

   )

   where ROWNUM <= 1;

exit;

END

)

 

echo $lag

 

read var1 var2 var3 <<< $lag

echo $var1

echo $var2

echo $var3

oracle@oraclevm01: /monitoring/bin  % : ./check_refresh_job.sh

FAILED 54 0

FAILED

54

0

 

1.5.2        Practice: check diff of time and pass variables to sql in shell

oracle@oraclevm01: /monitoring/bin  % : more check_refresh_job.sh

#!/bin/bash

 

#First argument: DB connection

DB_CONN=”testuser/oracle@pdb01″

#Second argument: Schema name

DB_SCHEMA=”TESTUSER”

 

JOB_NAME=${DB_SCHEMA}_REFRESH_CASS_TABLES

 

LAG_REFJOB=$(sqlplus -s $DB_CONN <<END

set pagesize 0 feedback off verify off heading off echo off;

define schema_name=$DB_SCHEMA

define job_name=$JOB_NAME

select STATUS,

ROUND(TO_NUMBER(cast(sysdate as date)-cast(ACTUAL_START_DATE as date))*24*60) as JOB_LAG,

extract(day from 24*60*RUN_DURATION) RUN_DURATION_MINUTES

from

( SELECT

 STATUS,

 ACTUAL_START_DATE,

 RUN_DURATION

   FROM sys.dba_scheduler_job_run_details

   WHERE JOB_NAME =’&job_name’

   AND OWNER =’&schema_name’

   AND actual_start_date > systimestamp – numtodsinterval(24,’hour’)

   ORDER BY actual_start_date DESC

   )

   where ROWNUM <= 1;

exit;

END

)

 

echo $LAG_REFJOB

 

read var1 var2 var3 <<< $LAG_REFJOB

echo $var1

echo $var2

echo $var3

 

oracle@oraclevm01: /monitoring/bin  % : ./check_refresh_job.sh

FAILED 187 0

FAILED

187

0

 

 

 

 

 

1.6        alert lag time

oracle@oraclevm01: /monitoring/bin  % : ./alert_lag_time.sh

#!/bin/bash

dbconn=”testuser/oracle@pdb01″

# Get the Lag time of Heartbeat

LAG_HB=`sqlplus -s $dbconn <<END

set pagesize 0 feedback off verify off heading off echo off;

select ROUND(TO_NUMBER(cast(target_commit as date)-cast(source_commit as date))*24*60) as lag_time from TESTUSER.HEARTBEAT

exit;

END

`

echo $LAG_HB

 

if [ $LAG_HB -ge 15 ]

   then

        SP_COUNT=10

        email_notification $DB_SCHEMA

else

        # When Heartbeat status is back to normal.

        echo “INFO – HEARTBEAT LAG status NORMAL @ `date ‘+%m_%d_%Y’_%H:%M`” >> $SP_REFRESHJOB_GEN_LOG

 

fi

}

1.7        alert_refresh_job

oracle@oraclevm01: /monitoring/bin  % : ./alert_refresh_job.sh

#!/bin/bash

 

#First argument: DB connection

DB_CONN=”testuser/oracle@pdb01″

#Second argument: Schema name

DB_SCHEMA=”TESTUSER”

 

 

alert_refreshjob(){

 

#First argument: DB connection

ARG_DB=$1

#Second argument: Schema name

ARG_SCHEMA=$2

 

JOB_NAME=${ARG_SCHEMA}_REFRESH_CASS_TABLES

 

LAG_REFJOB=$(sqlplus -s $ARG_DB <<END

set pagesize 0 feedback off verify off heading off echo off;

define schema_name=$ARG_SCHEMA

define job_name=$JOB_NAME

select STATUS,

ROUND(TO_NUMBER(cast(sysdate as date)-cast(ACTUAL_START_DATE as date))*24*60) as JOB_LAG,

extract(day from 24*60*RUN_DURATION) RUN_DURATION_MINUTES

from

( SELECT

 STATUS,

 ACTUAL_START_DATE,

 RUN_DURATION

   FROM sys.dba_scheduler_job_run_details

   WHERE JOB_NAME =’&job_name’

   AND OWNER =’&schema_name’

   AND actual_start_date > systimestamp – numtodsinterval(24,’hour’)

   ORDER BY actual_start_date DESC

   )

   where ROWNUM <= 1;

exit;

END

)

 

read VAR_STATUS VAR_JOBLAG VAR_DUR_MIN <<< $LAG_REFJOB

 

if [ $VAR_JOBLAG -ge 30 ] || [ $VAR_STATUS == “FAILED” ] || [ $VAR_DUR_MIN -ge 30 ]

   then

        echo “ALERT – REFRESH JOB LAG TIME IS LAGER THAN 15 MINUTES  @ `date ‘+%m_%d_%Y’_%H:%M`” >> $SP_REFRESHJOB_GEN_LOG

        SP_COUNT=10

        email_notification $ARG_SCHEMA

 

else

        # When refresh job status is back to normal after an issue.

        echo “INFO – REFRESH JOB LAG status NORMAL @ `date ‘+%m_%d_%Y’_%H:%M`” >> $SP_REFRESHJOB_GEN_LOG

 

fi

}

 

alert_refreshjob $DB_CONN $DB_SCHEMA

 

 

1.8        Issues:

1.8.1        Issue: warning: here-document at line 14 delimited by end-of-file (wanted `END’)

oracle@oraclevm01: /monitoring/bin  % : more check_diff_time.sh

#!/bin/bash

lag=$(sqlplus -s  TESTUSER/oracle@pstg <<END

set pagesize 0 feedback off verify off heading off echo off;

select ROUND(TO_NUMBER(cast(target_commit as date)-cast(source_commit as date))*24*60) as lag_time from TESTUSER.HEARTBEAT

exit;

END)

echo $lag

 

oracle@oraclevm01: /monitoring/bin  % : ./check_diff_time.sh

./check_diff_time.sh: line 18: warning: here-document at line 14 delimited by end-of-file (wanted `END’)

1

oracle@oraclevm01: /monitoring/bin  % : more check_diff_time.sh

#!/bin/bash

lag=$(sqlplus -s  TESTUSER/oracle@pstg <<END

set pagesize 0 feedback off verify off heading off echo off;

select ROUND(TO_NUMBER(cast(target_commit as date)-cast(source_commit as date))*24*60) as lag_time from TESTUSER.HEARTBEAT

exit;

END

)

echo $lag

1.8.2        Issue: -ge: unary operator expected

oracle@oraclevm01: /monitoring/bin  % : ./check_refreshjob.sh

FAILED

145

0

./check_refreshjob.sh: line 189: [: -ge: unary operator expected

 

 

2         Reference

https://www.akadia.com/services/ora_date_time.html

https://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql

https://blog.csdn.net/han_ying_ying/article/details/62419065

https://blog.csdn.net/xuke6677/article/details/41910469

https://developer.aliyun.com/article/518173

http://www.dba-oracle.com/t_passing_sqlplus_variable_in_shell_script.htm

 

2.1        Oracle function calculate the difference of time

  • two Date type column: START_DATE,END_DATE,
  • Difference of days, hours, minutes, seconds, millionseconds
  • Days: ROUND(TO_NUMBER(END_DATE – START_DATE))
  • Hours: ROUND(TO_NUMBER(END_DATE – START_DATE) * 24)
  • Minutes: ROUND(TO_NUMBER(END_DATE – START_DATE) * 24 * 60)
  • Seconds:ROUND(TO_NUMBER(END_DATE – START_DATE) * 24 * 60 * 60)
  • Millionseconds: ROUND(TO_NUMBER(END_DATE – START_DATE) * 24 * 60 * 60 * 1000)

 

Note:

The date type in Oracle only supports up to seconds, not up to milliseconds, so to_date() cannot take up to milliseconds. If you want to get milliseconds, oracle 9i and above, you can use the timestamp type, which is an extended type of date, which can support up to milliseconds. The display accuracy of milliseconds is 6 digits, but the effective digits are 3 digits, that is, the maximum value reaches 999, full 1000ms Just advance to 1s. The conversion function corresponding to to_date() can use to_timestamp(). select to_timestamp(‘2011-12-15 10:40:10.345′,’yyyy-MM-dd HH24:MI:ss.ff’) as mydate from dual; If you want to convert timestamp to date type again, you can use cast( ) Function, but the date type obtained has no millisecond value. as follows:

 

select cast(to_timestamp(‘2011-12-15 10:40:10.345’, ‘yyyy-MM-dd HH24:MI:ss.ff’) as date) as mydate from dual;

 

Example:

select ROUND(TO_NUMBER(to_date(to_char(sysdate,’yyyy-MM-dd hh24:mi:ss’),’yyyy-MM-dd hh24:mi:ss’) – to_date(‘2012-05-11 10:37:40′,’yyyy-MM-dd hh24:mi:ss’))*24*60*60) from dual .

 

select ROUND(TO_NUMBER(to_date(to_char(sysdate,’yyyy-MM-dd hh24:mi:ss’),’yyyy-MM-dd hh24:mi:ss’)
– to_date(‘2013-01-16 00:00:00′,’yyyy-MM-dd hh24:mi:ss’))*24*60*60) from dual

 

select id,service_name,source_commit,target_commit from TESTUSER.HEARTBEAT;

35626                 TIGER                 8/28/2020 8:02:49.000000 AM                         8/28/2020 8:03:00.000000 AM

 

select cast(target_commit as date)-cast(source_commit as date) as lag_time from TESTUSER.HEARTBEAT;

0.000127314814814815

 

select ROUND(TO_NUMBER(cast(target_commit as date)-cast(source_commit as date))*24*60) as lag_time from TESTUSER.HEARTBEAT;

0

 

select ROUND(TO_NUMBER(cast(target_commit as date)-cast(source_commit as date))*24*60*60) as lag_time from TESTUSER.HEARTBEAT;

11

 

select (target_commit-source_commit) as lag_time from TESTUSER.HEARTBEAT;

+00 00:00:11.000000

 

 

 

 

select trunc(target_commit) from TESTUSER.HEARTBEAT;

8/28/2020

select cast(target_commit as date) from TESTUSER.HEARTBEAT;

8/28/2020 8:05:00 AM

 

 

2.2        Subtraction between Dates

The trouble people get into when using the DATE datatype is doing arithmetic on the column in order to figure out the number of years, weeks, days, hours, and seconds between two dates. What needs to be realized when doing the calculation is that when you do subtraction between dates, you get a number that represents the number of days. You should then multiply that number by the number of seconds in a day (86400) before you continue with calculations to determine the interval with which you are concerned.

DEFINE Today = TO_DATE(‘03.12.2004:10:34:24′,’DD.MM.YYYY:HH24:MI:SS’)

SELECT TO_CHAR(hiredate,’DD.MM.YYYY:HH24:MI:SS’) “Hiredate”,
       TO_CHAR(&Today,’DD.MM.YYYY:HH24:MI:SS’) “Today”,
       trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) “Sec”,
       trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60)) “Min”,
       trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24)) “Hrs”,
       trunc((((86400*(&Today-hiredate))/60)/60)/24) “Days”
FROM emp;

Hiredate            Today               Sec Min Hrs  Days
——————- ——————- — — — —–
17.12.1980:00:00:00 03.12.2004:10:34:24  24  34  10  8752

Check out the above query for a possible solution on how to extract the individual time intervals for a subtraction of two dates. The fractions could be reduced but we wanted to show all the numbers to emphasize the calculation.

If you want a solution which breaks the days in years and month you can use the following query. We will use a leap year date, 01/01/2000 for example, for temporary purposes. This date will provide accurate calculation for most cases.

DEFINE DateDay = 8752.44056

SELECT
    TO_NUMBER(SUBSTR(A,1,4)) – 2000 years,
    TO_NUMBER(SUBSTR(A,6,2)) – 01 months,
    TO_NUMBER(SUBSTR(A,9,2)) – 01 days,
    SUBSTR(A,12,2) hours,
    SUBSTR(A,15,2) minutes,
    SUBSTR(A,18,2) seconds
FROM (SELECT TO_CHAR(TO_DATE(‘20000101′,’YYYYMMDD’)
      + &DateDay,’YYYY MM DD HH24:MI:SS’) A
      FROM DUAL);

     YEARS     MONTHS       DAYS HO MI SE
———- ———- ———- — — —
        23         11         17 10 34 24

 

 

 

2.3        Linux shel Numeric Comparisons

https://www.linuxtechi.com/compare-numbers-strings-files-in-bash-script/

  • num1 -eq num2                  check if 1st  number is equal to 2nd number
  • num1 -ge num2                  checks if 1st  number  is greater than or equal to 2nd number
  • num1 -gt num2                  checks if 1st  number is greater than 2nd number
  • num1 -le num2                   checks if 1st number is less than or equal to 2nd number
  • num1 -lt num2                   checks if 1st  number  is less than 2nd number
  • num1 -ne num2                  checks if 1st  number  is not equal to 2nd number

 

2.4        convert “INTERVAL DAY TO SECOND”

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_SCHEDULER_JOB_RUN_DETAILS.html

https://stackoverflow.com/questions/5063214/convert-interval-to-minutes

https://stackoverflow.com/questions/48444091/converting-interval-day-to-second

 

ACTUAL_START_DATE TIMESTAMP(6) WITH TIME ZONE   Actual date on which the job was run
RUN_DURATION INTERVAL DAY(3) TO SECOND(0)   Duration of the job run

 

 select STATUS,

   ROUND(TO_NUMBER(cast(sysdate as date)-cast(ACTUAL_START_DATE as date))*24*60) as JOB_LAG,

   extract(day from 24*60*RUN_DURATION) RUN_DURATION_MINUTES

   from

   ( SELECT

    STATUS,

    ACTUAL_START_DATE,

    RUN_DURATION

      FROM sys.dba_scheduler_job_run_details

      WHERE JOB_NAME =’TESTUSER_REFRESH’

      AND OWNER =’TESTUSER’

      –AND actual_start_date > systimestamp – numtodsinterval(24,’hour’)

      AND LOG_ID=324484

      ORDER BY actual_start_date DESC

      )

      where ROWNUM <= 1;

      SUCCEEDED                           9764                    3905

 

select STATUS,

ROUND(TO_NUMBER(cast(sysdate as date)-cast(ACTUAL_START_DATE as date))*24*60) as JOB_LAG,

extract(day from 24*60*RUN_DURATION) RUN_DURATION_MINUTES

from

( SELECT

 STATUS,

 ACTUAL_START_DATE,

 RUN_DURATION

   FROM sys.dba_scheduler_job_run_details

   WHERE JOB_NAME =’TESTUSER_REFRESH’

   AND OWNER =’TESTUSER’

   AND actual_start_date > systimestamp – numtodsinterval(24,’hour’)

   ORDER BY actual_start_date DESC

   )

   where ROWNUM <= 1;

 

 

   select * from

   ( SELECT

    “LOG_ID”,

    “LOG_DATE”,

    “OWNER”,

    “JOB_NAME”,

    “STATUS”,

    “ERROR#”,

    “REQ_START_DATE”,

    “ACTUAL_START_DATE”,

    “RUN_DURATION”,

    “INSTANCE_ID”,

    “SESSION_ID”,

    “SLAVE_PID”

      FROM sys.dba_scheduler_job_run_details

      WHERE “JOB_NAME” =’TESTUSER_REFRESH’

      AND “OWNER” =’TESTUSER’

      AND actual_start_date > systimestamp – numtodsinterval(24,’hour’)

      ORDER BY actual_start_date DESC

      )

      where ROWNUM <= 1;

     

Leave a Reply

Your email address will not be published. Required fields are marked *