Shell Script to Monitor Oracle Tablespace and Send Email Alert Only If Threshold Exceeds


export MMSG=/tmp/$$.mail
export ADDR="email address here"
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=appdev1

file=/tmp/${$}_`date +%Y%m%d`

sqlplus -s system/password << .eof > $file
set pages 0
select df.tablespace_name tspace,
round(sum(fs.bytes)/(df.bytes) * 100) "%_free",
round(sum(fs.bytes)/(1024*1024)) free_ts_size,
df.bytes/(1024*1024) tot_ts_size
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
having round(sum(fs.bytes)/(df.bytes) * 100) < 20;
exit;
.eof
#list all datafile below
egrep "SOE|APEX|SYSAUX|SYSTEM|TEMP|UNDOTBS1|USERS|AUDIT_TBS|APP_ENCRYPTED" /tmp/${$}_`date +%Y%m%d` >/tmp/table${ORACLE_SID}.txt
check_stat=`cat /tmp/table${ORACLE_SID}.txt|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -ne 0 ]
then
echo "tablespace less than 20% for $ORACLE_SID : $oracle_num" > $MMSG
mail -s "TABLESPACE WITH LESS THAN 20% FREE SPACE" $ADDR < $MMSG
mail -s "TABLESPACE WITH LESS THAN 20% FREE SPACE IN DATABASE $ORACLE_SID" $ADDR < /tmp/table$ORACLE_SID.txt
fi

rm -f $MMSG > /dev/null 2>&1
rm $file

Advertisements

Shell Script to Monitor AIX Filesystem and Send Email

The Below Shell Script checks the Filesystem mount points and using AWK outputs all filesystem exceeding 90% space to a file called diskspacepoll. Once that is done the sed command removes any special character like ‘%’ from the output file and cleans it to a file called output.log

The next important logic is in the AWK block. Here a variable called pattern is defined using the threshold of 90%. Another variable called var is defined. This is your baseline metric. So it value of pattern exceeds var then the mail is dispatched else the script does nothing. You can put this in crontab as a every 5 minute job to continuously poll the filesystems and incase the threshold is exceed it will dispatch an email immediately to the admin


#!/bin/ksh
df -P | grep -v Capacity | awk '{if ($5 >= 90) {print $5;}}' > /home/root/diskspacepoll
sed 's/[!@#\$%^&*()]//g' /home/root/diskspacepoll > /home/root/output.log
####### AWK LOGICAL BLOCK #########
pattern=$(awk '$1 > 90 {print $1}' /home/root/output.log)
var=90
if [[ $pattern > $var ]]
then
echo "Please Check with System Administrator" | mailx -s "90% Threshold of DiskSpace exceeded on Server 1 (ESB1)" sysadmin@company.com
fi

Notepad++ find and replace string with a new-line

Most of the times DBA’s need to edit blocks of PL/SQL or SQL scripts and perform some modifications. Notepad++ is a very powerful and lightweight tool to perform edits.

 

Lets say you have a document with text like below:

 

111

XYZ

DCD

999 ABC

Now you need  add a line break if you find pattern 999

In the lower left hand corner of the Replace box, you’ll see a section called “Search Mode”. Just select the “Extended (\n, \r, \t, , \x…)” choice and you can enter in \r as the replace variable and it will put the line break in.

notepad

 

 

The new file would look like below:

111

XYZ

DCD

999

ABC

 

Schema Replication using Oracle Goldengate 11g (Installation & Configuration)

GoldenGate is touted by Oracle as its future for data replication. It will slowly phase out Oracle Streams and will be more closely coupled with Oracle products. Golden Gate is relatively simpler to use and monitor than Oracle streams. Below I will demonstrate how to configure ASYNC Schema Replication one side using Oracle GoldenGate 11g on a Oracle 11gR2 database.

SID of Source : IVRPROD
SID of Target : IVRDR
Source Server name : ivrhodb
Target Server name : ivrdrdb
Source Schema name : IVR
Target Schema name : IVR
Tables : All tables

1. Tell database to log more (supplemental logging ~10% of more redo
writing) on source database only.

alter database add supplemental log data;

2. GoldenGate user creation on source and target side (if on Unix, on windows use same user in ora_dba group with which you installed Oracle)

useradd -d /data/home/gguser gguser

3. Copy the downloaded installation zip file ogg112101_ggs_Windows_x64_ora11g_64bit.zip) to a directory and unzip it.

4. Set the Environment variables in .bash_profile of user gguser on
both source and target database servers.

— source —

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRPROD
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

— Detination —

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRDR
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

5. create the tablespace for both databases (source + target ) like:

Create tablespace ggate datafile ‘/u01/oradata/ggate01.dbf’ size 1000M autoextend on next 100M;

6. GoldenGate schema creation into source and target database

create user ggate identified by ggate default tablespace ggate
temporary tablespace TEMP profile DEFAULT;
alter user ggate QUOTA UNLIMITED ON ggate;
grant CONNECT to ggate;
grant CREATE SESSION to ggate;
grant RESOURCE to ggate;
grant SELECT ANY TABLE to ggate;
grant ALTER SESSION to ggate;
grant CREATE TABLE to ggate;
grant FLASHBACK ANY TABLE to ggate;
grant SELECT ANY DICTIONARY to ggate;
grant DBA to ggate;

logis as sysdba on both source and target run following steps…

SQL> grant execute on utl_file to ggate;

SQL> @C:\ggs_Windows_x64_ora11g_64bit/marker_setup.sql

SQL> @C:\ggs_Windows_x64_ora11g_64bit/ddl_setup.sql

SQL> @C:\ggs_Windows_x64_ora11g_64bit/role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

And more important grant on dictionay views otherwise extract will keep on abending on both source and target schema (in our case IVR and IVR on both source and target. Schema name is same on both databases)

— on source —

SQL> grant select any dictionary to ivr;

— on target —

SQL> grant select any dictionary to ivr;

7. Go to installation directory and execute ./ggsci to see if your able to run the golden gate console on both source and target

8.
// Name of schema to be replicated will be IVR residing on IVRPROD database, it will be replicated to same schema name in IVRDR database.Make sure the characterset is same on Source and Target databases. //

— Source Configuration —

GGSCI (ivrhodb) 1> create subdirs
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> edit params mgr

port 7809
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrhodb) 1> start mgr
GGSCI (ivrhodb) 1> info all

If manager is running then manager configuration is ok.

GGSCI (ivrhodb) 1> dblogin userid ggate, password ggate
GGSCI (ivrhodb) 1> list tables ivr.*

If you see the list of tables then your configuration is good and you can continue:

GGSCI (ivrhodb) 1> add trandata IVR.*
GGSCI (ivrhodb) 1> info trandata IVR.*

Lets create and configure now the extractor process, edit add these files and save:

GGSCI (ivrhodb) 1> edit params ext1

extract ext1
userid ggate, password ggate
discardfile ./dirrpt/ext1.dsc,purge
reportcount every 15 minutes, rate
exttrail ./dirdat/t1
table IVR.*;

GGSCI (ivrhodb) 1> add extract ext1, tranlog, begin now
GGSCI (ivrhodb) 1> add exttrail ./dirdat/t1, extract ext1, megabytes 100
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> start ext1

Extractor created, now we create the data pump process:

GGSCI (ivrhodb) 1> edit params dpe1

extract dpe1
passthru
rmthost ivrdrdb, mgrport 7809
rmttrail ./dirdat/t1
table IVR.*;

//rmthost is the target server name or ip address. Ensure port 7809 is open on target server

GGSCI (ivrhodb) 1> add extract dpe1, exttrailsource ./dirdat/t1
GGSCI (ivrhodb) 1> add rmttrail ./dirdat/t1, extract dpe1, megabytes 100
GGSCI (ivrhodb) 1> status all

We are not going to start pump process yet, because destination is not configured.

9.
— Destination Configuration —
Target side on your ggsci prompt:

GGSCI (ivrdrdb) 1> create subdirs
GGSCI (ivrdrdb) 1> edit params mgr

port 7809
dynamicportlist 7900-7950
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrdrdb) 1> start mgr
GGSCI (ivrdrdb) 1> status all
GGSCI (ivrdrdb) 1> view report mgr

Manager created and verified, now we are going to create replicat processes. We’ll create the replicat with the 5 minutes lag, purposely for replication to be behind 5 minutes :

GGSCI (ivrdrdb) 1> edit params rep1

replicat rep1
userid ggate, password ggate
discardfile ./dirrpt/rep1.dsc, purge
assumetargetdefs
reportcount every 15 minutes, rate
batchsql
deferapplyinterval 5 mins
map IVR.*, target IVR.*;

GGSCI (ivrdrdb) 1> add replicat rep1, exttrail ./dirdat/t1
GGSCI (ivrdrdb) 1> ADD CHECKPOINTTABLE GGATE.CHKPTAB

That should be all about configuration , now last two steps, lets start data pump process on source side and replicat process on destination.

— on source —
GGSCI (ivrhodb) 1> start dpe1

— on target —
GGSCI (ivrdrdb) 1> start rep1

Now just check statuses on both sides with “status all” command. If everything is running and nothing abandoned you can check event logs on both sides and activity:

stats ext1, totalsonly *, reportrate sec
stats rep1, totalsonly *, reportrate sec
send rep1, status

And we have configured Oracle Golden Gate installation and setup forone asynchronous data replication.

Monitor User Processes and Send Email Shell Script Solaris10

Sometimes on a system it is important to know the maximum user processes does not increase so much that it consumes all shared memory. Recently we had an incident in our ORganization where a rogue application bug caused user processes to spike upto 20,000 and it consumed all memory and swap and the entire server crashed.  If we pro-actively monitored the system for increasing user processes we could have averted this disaster. Below is script to monitor user processes and send email.

swx – is name process name

500- threshold for permitted processes

#!/bin/ksh
VAL=`ps -ef | grep -c swx | awk ‘{if ( $1 > 500 ) print $1 }’`

if [ “$VAL” -ge “500” ]; then
echo “$VAL user processes found running with SWX user. Exceeded the Threshold,
Please take immediate action.” | /usr/bin/mailx -s “Alert !! $VAL SWX Applicati
on processes” email@test.com
fi

exit 0

You can add this to crontab like below:

* * * * * /u02/scripts/userprocmon.sh >/dev/null 2>&1

Configuring DNS on Solaris 10 (Joining Solaris10 server to Domain)

################ Configure Solaris 10 machine to be on DNS##############

1. Check for file resolv.conf in /etc directory, if it doenst exist create it

2. add below information to the resolv.conf file

domain mydomain.com
nameserver 172.20.4.10
nameserver 172.20.4.166
search mydomain.com

3. Check for file nsswitch.conf in /etc directory

hosts:      files
ipnodes:    files

## edit the above 2 lines and add ‘dns’ in front of file

hosts:      files dns
ipnodes:    files dns

save and exit

$ nslookup swxscan

Server:         172.20.4.10
Address:        172.20.4.10#53

Name:   swxscan.mydomain.com
Address: 172.21.51.18
Name:   swxscan.mydomain.com
Address: 172.21.51.17
Name:   swxscan.mydomain.com
Address: 172.21.51.19

Voila

Setting up user profiles for Grid and Oracle user : Oracle RAC 11gR2 on Solaris 10

Below is the Solaris user profiles for “grid” and “oracle” user. If you have followed Oracles official documentation for installation ORacle Rac 11gR2. It is best practice to install Oracle clusterware under grid user and oracle database with Oracle user. It takes a little time to get used to this setup specially for RAC users coming from 10g. But once you get a hang of it. It will work like a charm.

FYI the below user profiles have been set on Solaris 10\

___________________________________________

GRID USER PROFILE
___________________________________________

grid:x:100:100::/export/home/grid:/usr/bin/bash
oracle:x:101:100::/export/home/oracle:/usr/bin/bash

cd /export/home/grid

root@swxracnode1 # more .bash_profile

## If doesnt exist create it and give permission as 775 and owner as grid:oinstall

ORACLE_HOME=/u02/11.2.0/grid
GRID_HOME=/u02/11.2.0/grid
ORACLE_SID=+ASM1
export ORACLE_HOME ORACLE_SID GRID_HOME
PATH=/u02/11.2.0/grid/bin:$PATH

. .bash_profile

___________________________________________

ORACLE USER PROFILE
___________________________________________

grid:x:100:100::/export/home/grid:/usr/bin/bash
oracle:x:101:100::/export/home/oracle:/usr/bin/bash

cd /export/home/oracle

root@swxracnode1 # more .bash_profile

## If doesnt exist create it and give permission as 775 and owner as oracle:oinstall

ORACLE_HOME=’/u01/app/oracle/product/11.2.0/db_1′
ORACLE_SID=’swx1′
export ORACLE_HOME ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
export PATH
ORACLE_UNQNAME=’swx’
export ORACLE_UNQNAME