Shell Script to Ping Hostnames and Send SMS if Host is not Available

We use the below procedure to ping hosts and based on availability send SMS to our team. The SMS is send by calling a web service from the BASH Shell script using curl 

The hosts are defined in a file called hosts.txt

— Shell Script —

#!/bin/bash
current_time=$(date “+%Y%m%d%H%M%S%3N”)
#### Above Date Format is in MilliSeconds Accuracy ###
cd /home/online
rm -rf /home/online/SMSHOST.TXT
date
#### Below Code Will Loop the Hosts.TXT file for the Ping Probes ####
cat /home/online/hosts.txt | while read output
do
ping -w 30 -c 1 $output > /dev/null
if [ $? -ne 0 ]
then
SMSTEXT=’The+Host+’$output’+is+not+reachable.+Please+Contact+the+Database+Team.’
echo “Host $output is Down”
curl “http://192.168.10.100:8080/SMSConnect/SendServlet?application=http_gw2&password=http_gw2&content=$SMSTEXT&destination=9998887190&source=97249&mask=COMPANY”
else
echo “Host $output is Up”
fi
done

 

— hosts.txt file containing list of hostnames —

proddb01

proddb02

proddb03

drdb01

drdb02

drdb03

Shell Script to Add Date to Filenames

Below Shell Script will append Date to all FileNames in Directory with extension .TXT. Also it will create a folder with the Date and move the files to it.

#!/bin/bash
FILE=”$(date +%Y%m%d)”
cd /bi_dataload
mkdir -p /bi_dataload/BI_$FILE
for f in *.TXT
do
mv -n “$f” /bi_dataload/BI_$FILE/”${f%%.*}”_”$FILE”.TXT
done

Rebuild All Indexes on a Schema in Oracle

spool index_rebuild.sql

select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’
from dba_indexes
where owner=upper(‘SAMPLE’);
spool off

Monitoring Oracle with oratop

Rick Miners' Oracle Blog

Intro

I recently had a situation where I was troubleshooting a system that had fallen victim to some intermittent and very high load averages. At first, there were no apparent reasons for the high load averages, so that sent me searching for some monitoring tools to help identify the issue. Besides using OSWBB and dstat, I also used the oratop utility which turns out to be a great way to get a quick overview and monitor active sessions in near real time in Oracle 11g and 12c databases. It is also RAC and ASM aware.

References

Here’s the MOS note for more info and downloads.
oratop – Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

Supported Oracle Releases

11gR2 (11.2.0.3, 11.2.0.4)

12cR1

oratop in Action

There is a user guide available for download on the MOS note, but I will show my favorite options.

View original post 240 more words

First Day and Last Day of Current Month Oracle

Query to Select the First Day and Last Day of the Current Month

 

select trim(to_date(last_day(sysdate),’DD/MM/YYYY’)) as lastday from dual;

select trunc((sysdate),’month’) as FirstDay from dual;

Move Table to Another Tablespace Online Oracle Database 12c

The new PL/SQL procedure REDEF_TABLE in Oracle 12c  lets you redefine a table online in a single step. It can do the below changes

  • Move table,partition or index to another tablespace
  • Compression type change on a table including for table,partition and index

More information on DBMS_REDFINITION can be accessed on Oracle Support : Master Note:Overview of Online Redefinition of Tables (DBMS_REDEFINITION) (Doc ID 1357825.1)

We will now go ahead and move the table HR.EMP to another tablespace called TBS01 online.

EXEC
BEGIN
DBMS_REDEFINITION.REDEF_TABLE(
uname => ‘HR’,
tname => ‘EMP’,
table_part_tablespace => ‘TBS01’
);
END;
/

uname –> Schema Name in which Table resides
tname –> Table Name
table_part_tablespace –> New Tablespace to which table will be moved
index_tablespace => If you need to move the indexes to another table it can be done using an additional arguement

Data Recovery Advisor

SELECT * FROM DBA_TIPS;

Hello everyone!

During my career as an Oracle DBA, I saw many DBAs using this tool, the Data Recovery Advisor.
This is a tool that can assist with diagnosing media failure, providing RMAN commands to solved the problems.

Let’s start:

View original post 207 more words