Send SMS using Shell Script, checking Transaction Count of Table in Oracle

Being a DBA requires us many a times to step outside our role and solve a problem in a smart way. One of the issues we faced recently with one of our application, was the application server hanging and not being able to function properly. The application team did not have any means of getting a quick alert and depending on contact center  to get alerts about the application being down. This required the application server to be monitored round the clock using SMS alerts. Now the application itself wasn’t smart enough to do this. So i did a bit of analysis on the application to figure out a way . Now the Oracle database on Exadata rarely if ever goes down (A very Good DBA team 🙂 ) So to find out the application has a problem, we figured to keep the database as a reference point of availability would be best.

The application writes to a table called authorizations. This being a payment gateway application the transaction count is very high. We receive 24/7 about 10TPS. So in a minute the transaction count will never be below 500-600 anytime . So the first step in creating this script is to create a query to check the transaction count and spool it to file called smscard.txt

select count(*) from authorizations where ltimestamp > sysdate – 5/1440 and I039_RSP_CD=’00’;

SQL>
COUNT(*)
———-
177
This will give us the transaction count in the last 5 minutes

Next We create a logic to check this count with a defined threshold. Using AWK we can remove the garbage information and get the count(*) output

####### AWK BLOCK #########
awk ‘NF=5’ /u03/scripts/smscard.txt > /u03/scripts/trimoutput
pattern=”$(awk ‘NR==5 {print;exit}’ /u03/scripts/trimoutput)”
if [[ “$pattern” -lt 3 ]]
then
sh /u03/scripts/newftptsys.sh
fi

First Line Removes the Extra spaces from the smscard.txt file and writes to file “trimoutput”

Second Prints Line 5 and writes to variable “pattern” from  “trimoutput” file

Third Line checks IF pattern is less than threshold. If condition is true it sends an SMS file to the SMS gateway using FTP. Our SMS gateway requires a text file with mobile numbers to be send to a FTP server.

So there are 3 scripts. A FTP script, a SQL script and the Master script. The Master script calls the SQL script and the FTP script and sends the SMS file based on the IF THEN logic. Below are the 3 scripts

FTP Script : /u03/scripts/newftptsys.sh

#!/bin/bash
USER=abc
PASSWD=abc
FILE=SMSTSYS.TXT
HOST=prodlive
ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
lcd /u03/scripts
cd /QDLS/SMSDTA
put $FILE
quit
END_SCRIPT
exit 0

SQL Script : /u03/scripts/counttsys.sql

select count(*) from authorizations where ltimestamp > sysdate – 5/1440 and I039_RESP_CD=’00’;

Main/Master Script : /u03/scripts/smsTSYS.sh

#!/bin/bash
current_time=$(date “+%Y%m%d%H%M%S”)
export ORACLE_HOME=/u03/app/oracle/product/12.1.0.2/payapps
cd /u03/scripts
$ORACLE_HOME/bin/sqlplus xyz/******@onlnprd << EOF
whenever sqlerror exit sqlcode
set serveroutput on size 1000000
set feedback off
set line 200
spool /u03/scripts/smscard.txt
@/u03/scripts/counttsys.sql
spool off
exit
EOF
rm -f /u03/scripts/SMSTSYS.TXT
touch /u03/scripts/SMSTSYS.TXT
chmod 777 /u03/scripts/SMSTYS.TXT
echo “$current_time,ACQUIRER,33578971,30116935″>/u03/scripts/output
cat /u03/scripts/output > /u03/scripts/SMSTSYS.TXT
####### AWK BLOCK #########
awk ‘NF=5’ /u03/scripts/smscard.txt > /u03/scripts/trimoutput
pattern=”$(awk ‘NR==5 {print;exit}’ /u03/scripts/trimoutput)”
if [[ “$pattern” -lt 3 ]]
then
sh /u03/scripts/newftptsys.sh
fi

The Final Script will generate an SMS file called SMSTYS.TXT in format with mobile numbers and send to the SMS ftp folder based on the count(*) output being less than threshold

20161129124001,ACQUIRER,33578971,30116935

Summary:

Using a little bit of logic and the magic of Shell scripts we created a customized SMS probe for a very critical business application. Even an Email alert can be configured using a similar mechanism. A bit of innovation goes a long way to solve a business problem  🙂

Advertisements

Create ACFS Filesystem Oracle 12c Linux 12.1.0.2

— Create ACFS filesystem on 12c Linux Exadata —
1. Create a volume in ASM

ASMCMD [+] > volcreate -G datac1 -s 500G ACFS_VOL

If you get like below error

ORA-15032: not all alterations performed
ORA-15479: ASM diskgroup does not support volumes
ORA-15221: ASM operation requires compatible.asm of 12.1.0.2.0 or higher (DBD ERROR: OCIStmtExecute

Check the Current Compatibility for the Diskgroup

select group_number, name,compatibility, database_compatibility from v$asm_diskgroup
GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
———— —————————— ———————————————————— ————————————————————
1 DATAC1 12.1.0.1.0 11.2.0.2.0
2 DBFS_DG 12.1.0.0.0 11.2.0.2.0
3 RECOC1 12.1.0.1.0 11.2.0.2.0
SQL> alter diskgroup DATAC1 set attribute ‘compatible.asm’=’12.1.0.2.0’;

Diskgroup altered.

SQL> alter diskgroup RECOC1 set attribute ‘compatible.asm’=’12.1.0.2.0’;

Diskgroup altered.

SQL> alter diskgroup DBFS_DG set attribute ‘compatible.asm’=’12.1.0.2.0′;

Diskgroup altered.

SQL> select group_number, name,compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
———— —————————— ———————————————————— ————————————————————
1 DATAC1 12.1.0.2.0 11.2.0.2.0
2 DBFS_DG 12.1.0.2.0 11.2.0.2.0
3 RECOC1 12.1.0.2.0 11.2.0.2.0

Run again the volcreate command
ASMCMD [+] > volcreate -G datac1 -s 500G ACFS_VOL
2. Check the volume information
ASMCMD [+] > volinfo -G datac1 ACFS_VOL

Diskgroup Name: DATAC1

Volume Name: ACFS_VOL
Volume Device: /dev/asm/acfs_vol-45
State: ENABLED
Size (MB): 512000
Resize Unit (MB): 64
Redundancy: MIRROR
Stripe Columns: 8
Stripe Width (K): 1024
Usage:
Mountpath:
sqlplus “/as sysasm”

SELECT volume_name, volume_device FROM V$ASM_VOLUME
WHERE volume_name =’ACFS_VOL’;

VOLUME_NAME
——————————
VOLUME_DEVICE
——————————————————————————–
ACFS_VOL
/dev/asm/acfs_vol-45

3. Create a file system with the Oracle ACFS mkfs command using output of above command

With root user run below command

/sbin/mkfs -t acfs /dev/asm/acfs_vol-45
mkfs.acfs: version = 12.1.0.2.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/acfs_vol-45
mkfs.acfs: volume size = 536870912000 ( 500.00 GB )
mkfs.acfs: Format complete.
4. Register the file system with the acfsutil registry command.

Create a directory called ACFS

cd /
mkdir /ACFS

/sbin/acfsutil registry -a /dev/asm/acfs_vol-45 /ACFS

acfsutil registry: mount point /ACFS successfully added to Oracle Registry
Imp Note 1: Registering an Oracle ACFS file system also causes the file system to be mounted automatically whenever Oracle Clusterware or the system is restarted.
Imp Note 2: Oracle ACFS registration (acfsutil registry) is not supported in an Oracle Restart (standalone) configuration, which is a single-instance (non-clustered) environment.
5. Verify if ACFS filesystem mounted automatically

$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VGExaDb-LVDbSys1
30G 17G 12G 59% /
tmpfs 252G 22G 231G 9% /dev/shm
/dev/sda1 496M 54M 418M 12% /boot
/dev/mapper/VGExaDb-LVDbOra1
99G 57G 37G 61% /u01
/dev/mapper/VGExaDb-LVDbOra2
197G 68G 119G 37% /u02
/dev/mapper/VGExaDb-LVBkp1
985G 288G 648G 31% /u03
/dev/asm/acfs_vol-45 500G 1.1G 499G 1% /ACFS
As you can see from above output the ACFS filesystem moutned automatically after registration
6. If you did not register the ACFS filesystem it will not mount automatically, you can mount the ACFS filesystem manually using below command

As root user

/bin/mount -t acfs /dev/asm/acfs_vol-45 /ACFS

7. Give appropriate permissions to the filesystem required by Oracle users
chown -R oracle:dba /ACFS

su – oracle

cd /ACFS

touch abc.txt

Create or Drop Public Synonyms in Oracle

Synonym is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.

— Create a Public Synonym for a Table —

CREATE OR REPLACE PUBLIC SYNONYM EASYNAME for EASYORADBA.TABLE00001 ;

— Drop a Public Synonym for a Table —

DROP PUBLIC SYNONYM EASYNAME ;

— View to Check Synonyms in Oracle —

SELECT * from DBA_SYNONYMS;

Generate Excel | CSV Report from SQL*PLUS

Shell Script to generate a column seperator report in SQL*PLUS using a shell script. It can be opened in Excel and or text editor like NOTEPAD++

 

#!/bin/ksh
. $HOME/.bash_profile
NOW=$(date +”%a %b %e %T %Y”)
HOST=$(hostname)
MON2=$(date +”%b%Y”)

cd $HOME/scripts
rm -rf Report_$MON2.csv

sqlplus -s /NOLOG << EOF > /tmp/log.txt
CONNECT abc/*****

set colsep ;
set pagesize 1000
set trimspool on
set headsep off
set linesize 9999

alter session set nls_date_format = ‘DD/MM/YYYY’;
spool Report_$MON2.csv
select * from tablename where condition;
spool off
exit
EOF