SHELLS SCRIPTING

 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. cat /export/home/oracle/Housekeeping/scripts/tablespace_alert.sql       set feedback off set pagesize 70; set linesize 2000 set head on COLUMN Tablespace        format a25 heading 'Tablespace Name' COLUMN autoextensible         format a11              heading 'AutoExtend' COLUMN files_in_tablespace    format 999             heading 'Files' COLUMN total_tablespace_space format 99999999 heading 'TotalSpace' COLUMN total_used_space       format 99999999 heading 'UsedSpace' COLUMN total_tablespace_free_space format 99999999 he...

 Alert Log Rotation Script In Oracle ==================================== 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 the script. and ORACLE_SID will be passed as an argument while running the script. # $Header: rotatealertlog.sh # *====================================================================================+ # | AUTHOR : DBACLASS SUPPORT TEAM # | | # +====================================================================================+ # | #!/bin/bash echo ======================== echo Set Oracle Database Env echo ======================== ORACLE_SID=$1; export ORACLE_SID ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1 ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/...

 shell script for file system alert ================================== 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 -h $fs`" | mailx -s "Filesystem $fs Usage high on Server `hostname`" support@dbaclass.com   fi done Put in crontab:   00 * * * * /usr/local/scripts/diskalert.sh  

 Shell script to report failed login attempt in oracle ===================================================== 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. cat /export/home/oracle/invalid_log.sh export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1 export ORACLE_SID=SBIP18DB export PATH=$ORACLE_HOME/bin:$PATH logfile=/export/home/oracle/test.log sqlplus -s "/as sysdba" > /dev/null << EOF spool $logfile set pagesize 1299 set lines 299 col username for a15 col userhost for a13 col timestamp for a39 col terminal for a23 SELECT username,userhost,terminal,to_char(timestamp,'DD/MM/YY HH24:MI:SS' ) "TIMESTAMP" , CASE when returncode=1017 then 'INVALID-attempt' whe...

 Shell script to monitor asm diskgroup usage =========================================== 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 spool $logfile SET LINESIZE 150 SET PAGESIZE 9999 SET VERIFY off COLUMN group_name FORMAT a25 HEAD 'DISKGROUP_NAME' COLUMN state FORMAT a11 HEAD 'STATE' COLUMN type FORMAT a6 HEAD 'TYPE' COLUMN total_mb FORMAT 999,999,999 HEAD 'TOTAL SIZE(GB)' COLUMN free_mb FORMAT 999,999,999 HEAD 'FREE SIZE (GB)' COLUMN used_mb FORMAT 999,999,999 HEAD 'USED SIZE (GB)' COLUMN pct...

 Shell script for monitoring blocking sessions ============================================= 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 299 col event for a31 SELECT s.inst_id, s.blocking_session, s.sid, s.serial#, s.seconds_in_wait, s.event FROM gv$session s WHERE blocking_session IS NOT NULL and s.seconds_in_wait > 10;    2. Shell script.(/home/oracle/monitor/blocker.sh ) You need to […]

 Shell Script To Monitor Lag In Standby Datbase Using Dgmgrl =========================================================== 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 DB UNIQUE_NAME – > PRIMDB STANDBY DB UNIQUE_NAME -> STYDB cat /home/oracle/dgmgrl_standby_lag.sh #!/bin/bash export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1 export ORACLE_SID=primdb export PATH=$ORACLE_HOME/bin:$PATH echo -e “show database stydb”|${ORACLE_HOME}/bin/dgmgrl sys/orcl1234 > DB_DG_DATABASE.log cat /home/oracle/DB_DG_DATABASE.log  | grep “Apply Lag”  > FILTERED_DB_DG_DATABASE.log time_value=`cut -d ” ” -f 14 FILTERED_DB_DG_DATABASE.log` time_param=`cut -d ” ” -f 15 FILTERED_DB_...

 Shell Script To Delete Old Archives Using RMAN ================================================ 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; DELETE EXPIRED ARCHIVELOG ALL; exit EOF } # Main delBackup Now configure in crontab: 00 22 * * * /u01/app/oracle/rman_arch_del.sh > /tmp/rmanarch.log

 Shell Script To Monitor Goldengate Process =========================================== 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)   #!/bin/bash EMAIL_LIST="support@dbaclass.com"   OIFS=$IFS IFS=" " NIFS=$IFS   function status { OUTPUT=`$GG_HOME/ggsci << EOF info all exit EOF` } function alert { for line in $OUTPUT do if [[ $(echo "${line}"|egrep 'STOP|ABEND' >/dev/null;echo $?) = 0 ]] then GNAME=$(echo "${line}" | awk -F" " '{print $3}') GSTAT=$(echo "${line}" | awk -F" " '{print $2}') GTYPE=$(echo "${line}" | awk -F" " '{print $1}') case $GTYPE in "MANAGER") cat $GG_HOME/dirrpt/MGR.rpt | mailx -s "${HOSTNAME} - GoldenGate ${GTYPE} ${GSTAT}" $NOTIFY ;;   "EXTRAC...