Below script is helpful in monitoring lag in standby database and send mail to DBAs in case the lag is increasing. For the script to work, make sure dataguard broker is enabled between primary and standby database.   SEE DGBROKER ARTICLE:  How to setup dgbroker in oracle 12c: Useful dgmgrl commands:   SCRIPT PREPARATION: PRIMARY […]

OBJECTIVE: Write a script is to monitor goldengate processes like extract and replicat, And in case extract or replicat is down, it will send alert to the respective email ids. SOLUTION: Below is the shell script.(gg_alert.sh) cat gg_alert.sh #!/bin/bash EMAIL_LIST=”support@dbaclass.com” OIFS=$IFS IFS=” ” NIFS=$IFS function status { OUTPUT=`$GG_HOME/ggsci << EOF info all exit EOF` } […]

If the requirement is to delete archive log backups automatically (without taking backup), then below shell script can be configured in crontab. prepare the shell script. cat rman_arch_del.sh #!/bin/bash export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2.0 export ORACLE_SID=PARIS12C export PATH=$ORACLE_HOME/bin:$PATH delBackup () { rman log=/home/oracle/arch_del.log << EOF connect target / DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-1’; CROSSCHECK ARCHIVELOG ALL; […]

Below is the shell script, to be configured in crontab, which will send mail incase of blocking session observed in the database . In the mail body it will contain the blocking sessions details also. 1. Prepare the blocker.sql file.[ for blocking sessions more than 10 seconds) set feed off set pagesize 200 set lines […]

REQUIREMENT: Write a shell script, which will trigger a mail alert, if the utilization of the asm diskgroup reached 90 percent. SOLUTION: 1. Below is the shell script. Make sure to update ORACLE_HOME, ORACLE_SID inside the shell script. cat /export/home/oracle/asm_dg.sh export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1 export ORACLE_SID=PRODDB1 export PATH=$ORACLE_HOME/bin:$PATH logfile=/export/home/oracle/asm_dg.log sqlplus -s “/as sysdba” > /dev/null << EOF […]

Requirement: Configure a shell script in crontab, that will send alert to DB support Team, in the case of any invalid login attempts in the database.   1. First, enable audit for create session SQL> audit create session; Audit succeeded. 2. Final shell script Below script for any invalid login attempts in last 15 minutes. […]

Below is script for sending notification ,when a mount point or filesystem crosses a threshold value. For solaris #!/bin/sh df -h | egrep -v ‘/system|/platform|/dev|/etc|lib’ | awk ‘{print $6 ” ” $5}’|cut -d% -f1|while read fs val do if [ $val -ge 90 ] then echo “The $fs usage high $val% \n \n \n `df […]

Day by day, alert log size will grow in Oracle database. So for housekeeping, we need to move the existing alert log to a backup location and compress there. Upon moving the alert log, the database will create a fresh alert log automatically. Below is the shell script. WE need to define the ORACLE_HOME in […]

Below script can be configured in crontab to send a notification to the support DBAs in case tablespace usage crosses a threshold. 1. First, make the below .sql file, which will be used inside the shell script. In this script we have defined the threshold as 90%. You can change it as per your requirement. […]

Requirement: Configure a shell script to monitor alert log for all the databases on a server once in every 15 min.And in the case of any ORA- error mail to the DBA TEAM. Below script is prepared using the ADRCI utility of oracle 11g. It will monitor alert log for all the databases having same […]