Linx /Unix history command by Arrow key.

Few lines in .profile will make life easier of DBA on Unix platform.

edit below command in .profile.

-------
#  set Unix command prompt 
export PS1=`uname -n`@':$PWD :$ORACLE_SID $'
# To enable backspace. 
set -o emacs
stty erase ^?
#simple alias to make life easier & save 5 second each time you login to sqlplus  
alias ll='ls -lart'
alias lsd='ls -l | grep ^d'
alias home='cd $ORACLE_HOME'
alias psora='ps -ef | grep pmon'
alias orasql='sqlplus / as sysdba'

##### To enable Arrow key for History commands 

alias __A=$(print '\0020') # ^P = up = previous command
alias __B=$(print '\0016') # ^N = down = next command
alias __C=$(print '\0006') # ^F = right = forward a character
alias __D=$(print '\0002') # ^B = left = back a character
alias __H=$(print '\0001') # ^A = home = beginning of line
export COLUMNS=130
clear

Generate tablespace usage trend / growth.

Hi

The script mention below generates tablespace usage trend/growth.

This is useful to understand when we deal with TEMP / UNDO tablespace peak usage.

It creates a spool file as excel sheet format (.xls), which helps to create graph for reporting purpose as a business standpoint.
=========================================================

SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF;
set linesize 125
set numwidth 20
set pagesize 50
COL NAME FOR A30
col SNAP_ID for 9999999
set serveroutput off;
SPOOL TBS_TREND.xls;
set verify off;
set echo off;

SELECT
distinct DHSS.SNAP_ID,VTS.NAME,
TO_CHAR(DHSS.END_INTERVAL_TIME, 'DD-MM HH:MI') AS SNAP_Time,
ROUND((DHTS.TABLESPACE_USEDSIZE*8192)/1024/1024)/&&max_instance_num AS USED_MB,
ROUND((DHTS.TABLESPACE_SIZE*8192)/1024/1024)/&&max_instance_num AS SIZE_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE DHTS,V$TABLESPACE VTS,DBA_HIST_SNAPSHOT DHSS
WHERE VTS.TS#=DHTS.TABLESPACE_ID
AND DHTS.SNAP_ID=DHSS.SNAP_ID
AND DHSS.INSTANCE_NUMBER=1
AND TABLESPACE_ID=&id
ORDER BY 1;
SPOOL OFF;
EXIT;

Exadata Performance Monitoring | ASH Report

Script is to get Essence of AWR.

it takes last 24 Hour snapshot and shows statistics of Queries which has elapsed time more than 1 Hours. which query used offloading and bytes returned by Exadata cell. (which can be customize).
It also shows Top wait class & 40 Hot objects by Physical Reads.

set linesize 250
set numwidth 20
set pagesize 555
set serveroutput off;
SET ECHO OFF;
set termout on;
set verify off;
set recsep off;
col TEXT FOR A30
col ELAPSED_TIME_SEC for 99999999
col objects for a50
col INST for 9999
col ITL_WT FOR 9999999
COL PHY_WRIT FOR 9999999999
COL ROW_LCK_WT FOR 999999999
COL ELAPSED_TIME FOR 999999999999
COL CPU_TIME FOR 99999999
COL EXECUTIONS FOR 9999999999
COL PX_SERVERS FOR 99999999999
COL DISK_READ_BYTES FOR 999999999999999
COL DISK_WRITE_BYTES FOR 999999999999999
COL ROWS_PROCESSED FOR 9999999999999

--SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF;
undefine MIN_SNAP
undefine MAX_SNAP

col MAX_SNAP new_value MAX_SNAP
col MIN_SNAP new_value MIN_SNAP

---PROMPT "USE THIS WHEN SNAPSHOT TAKEN BY EVERY HALF HOUR"
---SELECT MAX(SNAP_ID) MAX_SNAP,MIN(SNAP_ID) MIN_SNAP FROM ---DBA_HIST_SNAPSHOT
---WHERE BEGIN_INTERVAL_TIME > (SYSDATE - 1) - 4/ 24
---AND END_INTERVAL_TIME <= (SYSDATE) - 3/ 24 ORDER BY 1;

PROMPT "USE THIS WHEN SNAPSHOT IS TAKEN EVERY 1 HOUR"
SELECT MAX(SNAP_ID) MAX_SNAP,MIN(SNAP_ID) MIN_SNAP FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > (sysdate -1) - 4/24
and BEGIN_INTERVAL_TIME <= (SYSDATE) - 3/24 ORDER BY 1;

--SET VARIFY OFF;
set verify off;
SET TERMOUT ON;
SPOOL /home/oracle/scripts/monitor/HM_repot.txt;
prompt " snapshot between &&MIN_SNAP and &&MAX_SNAP"

SELECT END_INTERVAL_TIME as START_TIME FROM DBA_HIST_SNAPSHOt WHERE SNAP_ID=&&MIN_SNAP AND ROWNUM= 1;
SELECT END_INTERVAL_TIME as END_TIME FROM DBA_HIST_SNAPSHOt WHERE SNAP_ID=&&MAX_SNAP and rownum=1;

PROMPT *****************************************************************
prompt *********SQLs with elapsed time more then 1 hour*****************
PROMPT *****************************************************************


SELECT sql_id,
text,
elapsed_time,
CPU_TIME,
EXECUTIONS,
PX_SERVERS,
DISK_READ_BYTES,
DISK_WRITE_BYTES,
IO_INTERCONNECT_BYTES,
OFFLOAD_ELIGIBLE_BYTES,
CELL_SMART_SCAN_ONLY_BYTES,
FLASH_CACHE_READS,
ROWS_PROCESSED
--AVG_PX_SERVER
FROM (SELECT x.sql_id,
SUBSTR ( dhst.sql_text, 1, 30) text,
ROUND ( x.elapsed_time / 1000000,0)  elapsed_time,
ROUND ( x.cpu_time / 1000000,0)  CPU_TIME,
--ROUND ( x.elapsed_time / 1000000, 3) elapsed_time,
--ROUND ( x.cpu_time / 1000000, 3) cpu_time_sec,
x.executions_delta       EXECUTIONS,
ROUND (X.DISK_READ_BYTES/1048576,0)        DISK_READ_BYTES,
ROUND (X.DISK_WRITE_BYTES/1048576,0)       DISK_WRITE_BYTES,
ROUND (X.IO_INTERCONNECT_BYTES/1048576,0)  IO_INTERCONNECT_BYTES,
ROUND (X.OFFLOAD_ELIGIBLE_BYTES/1048576,0) OFFLOAD_ELIGIBLE_BYTES,
X.FLASH_CACHE_READS                        FLASH_CACHE_READS,
ROUND (X.cell_smart_scan_only_BYTES/1048576,0)  CELL_SMART_SCAN_ONLY_BYTES,
(x.ROWS_PROCESSED) ROWS_PROCESSED,
(X.PX_SERVERS) PX_SERVERS,
--ROUND(X.PX_SERVERS/X.executions_delta,0) AVG_PX_SERVER,
row_number () OVER (PARTITION BY x.sql_id ORDER BY 0) rn
FROM dba_hist_sqltext dhst,
(SELECT dhss.sql_id                       sql_id,
SUM (dhss.cpu_time_delta)                 cpu_time,
SUM (dhss.elapsed_time_delta)             elapsed_time,
SUM (dhss.executions_delta)               executions_delta,
SUM (dhss.PHYSICAL_READ_BYTES_DELTA)      DISK_READ_BYTES,
SUM (dhss.PHYSICAL_WRITE_BYTES_DELTA)     DISK_WRITE_BYTES,
SUM (dhss.IO_INTERCONNECT_BYTES_DELTA)    IO_INTERCONNECT_BYTES,
SUM (dhss.IO_OFFLOAD_ELIG_BYTES_DELTA)    OFFLOAD_ELIGIBLE_BYTES,
SUM (dhss.OPTIMIZED_PHYSICAL_READS_DELTA) FLASH_CACHE_READS,
SUM (dhss.IO_OFFLOAD_RETURN_BYTES_DELTA)  cell_smart_scan_only_BYTES,
SUM (dhss.ROWS_PROCESSED_DELTA)      ROWS_PROCESSED,
SUM (dhss.PX_SERVERS_EXECS_DELTA) PX_SERVERS
FROM dba_hist_sqlstat dhss
WHERE dhss.snap_id IN
(SELECT snap_id
FROM dba_hist_snapshot
--WHERE SNAP_ID > 1865 AND SNAP_ID<= 1889)
WHERE SNAP_ID > &&MIN_SNAP AND SNAP_ID<= &&MAX_SNAP)
--comment BELOW line if want to include current executions.
--AND dhss.executions_delta > 0
GROUP BY dhss.sql_id) x
WHERE x.sql_id = dhst.sql_id
AND ROUND ( x.elapsed_time / 1000000, 3) > 3600)
WHERE rn = 1 ORDER BY ELAPSED_TIME DESC;

PROMPT *****************************************************************
PROMPT ********WAIT_CLASS AND COUNTS / NOTE " NULL VALUE IS CPU"********
PROMPT *****************************************************************

select wait_class, count(*) cnt from dba_hist_active_sess_history
WHERE SNAP_ID > &&MIN_SNAP AND SNAP_ID<= &&MAX_SNAP
group by wait_class_id, wait_class
order by 2;

PROMPT *****************************************************************
prompt *********   Top 40 Objects by Physical Read     *****************
PROMPT *****************************************************************

SELECT * FROM (
SELECT do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']' AS OBJECTS,
DHSS.INSTANCE_NUMBER AS INST,
SUM(DHSS.LOGICAL_READS_DELTA) LOGICAL_READ,
SUM(DHSS.PHYSICAL_READS_DELTA) PHY_READ,
SUM(DHSS.PHYSICAL_WRITES_DELTA) PHY_WRIT,
SUM(DHSS.ITL_WAITS_DELTA) ITL_WT,
SUM(DHSS.ROW_LOCK_WAITS_DELTA) ROW_LCK_WT
from dba_hist_seg_stat DHSS, DBA_OBJECTS DO
WHERE SNAP_ID > &&MIN_SNAP AND SNAP_ID<= &&MAX_SNAP
--WHERE DHSS.SNAP_ID > 20135 AND DHSS.SNAP_ID<= 20183
AND DHSS.OBJ#=DO.OBJECT_ID
group by do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']',DHSS.INSTANCE_NUMBER
order BY PHY_READ DESC
) WHERE ROWNUM <=40;
SPOOL off;
EXIT;

space alert on filesystem

#!/bin/ksh
LOG=/export/home/space.log
cat /dev/null > $LOG
df -g | sort -rnk 4 | grep -vE '^-|Free|proc' | awk '{sub (/%/,"",$4);print $4 "\t" $7}' | while rea
d output;
do
echo $output
usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1 )
partition=$(echo $output | awk '{ print $2 }' )
#echo $usep
#echo $partition
if [ $usep -ge 70 ]; then
echo "Alert :$usep% used on $partition on $(hostname) \n" >> $LOG
fi
done
####################
if [[ -s $LOG ]]; then
#echo "sending mail"
mailx -s "space alert on $(hostname)" jignesh.kankrecha@xxx.com < $LOG
else
#echo "not sending"
fi;

Oracle output into excel sheet

Hi All

this is the proto-type format of shell script embedded with SQL script , which output into a excel sheet.
=========================================
#!/bin/ksh
echo $ORACLE_HOME
myfile=/home/oracle/awr/perf.sql
emailfile=/home/oracle/awr/MY_TEST_FILE.xls
echo $myfile
echo $emailfile
$ORACLE_HOME/bin/sqlplus -s xxx/yyy@conn_identifier @$myfile
cat $emailfile | uuencode $emailfile | mailx -s "report" jignesh.kankrecha@jpmchase.com
exit;

============================================

####################################
put $myfile which is /home/oracle/awr/perf.sql in below format
####################################
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF;
set linesize 125
set numwidth 10
set pagesize 50
col TEXT FOR A30
col ELAPSED_TIME_SEC for 99999999
SET ECHO OFF;
SPOOL MY_TEST_FILE.xls;
--your SQL STATEMENT HERE;
spool off;
exit;
####################################

generate AWR automatic

hmm so , there was a performance problem in my prod server,

which pushed me to create a baseline of whole month and we never had it. To create that i needed AWR report of every day , every hour which made it even hard for me to take snapshot of it.

To create AWR manually needs a lot of times and obviously LABOR.

so i created my favorite script which is derived from BASH shell , where i had aix 5.1 and never had bash 3.x , which dont allowed `seq` in for loop.

so i wrote in (ksh). which is as below.

#!/bin/ksh
if [ $# != 4 ]
then
echo "Syntax awr_generation.sh dbid instanceId startsnapid endsnapid"
exit 1
fi
l_dbid=$1
l_instid=$2
l_start_snapid=$3
let l_end_snapid=$4-1
i=${l_start_snapid}
until [[ $i = $l_end_snapid ]];
do
let l_next_snapid=$i+1;
l_awr_log_file="/home/oracle/awr/awrrpt_${2}_${i}_${l_next_snapid}.log"
sqlplus -s / as sysdba << EOF set head off set pages 0 set lines 132 set echo off set feedback off spool $l_awr_log_file SELECT output FROM TABLE (dbms_workload_repository.awr_report_html
($l_dbid,$l_instid,$i,$l_next_snapid)
);
spool off
EOF
let i=i+1
done

GOLDEN GATE LAG MONITORING

Hi

There are two script i have created
1) Linux
2) solaris and other platform.

First one i just wrote with modified logic and less processing using files.
Second script is specially for other Unix OS ,due to tac command does not work there.

Please inform me on jonyjt@gmail.com , if you find problem regarding it.


1 script

#!/bin/ksh
###############################
# monitor gg process
###############################
export ORACLE_HOME=/u00/app/oracle/product/10.2.0/DB
export GG_HOME=/ORAGG/product/11.2.1/gghome10
export PATH=$GG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:$LD_LIBRARY_PATH
export LOGFILE=/tmp/testoutput.log
export DT=`date +%d/%m/%Y\ %k:%M:%S`
export BOX=`hostname -s`
export COLUMNS=130
#echo "############################################################"
#echo `date +%d/%m/%Y\ %k:%M:%S`
################################
echo $DT
rm -rf /tmp/testoutput.log
###############################
#check if process has abended.
${GG_HOME}/ggsci <<!| while read;do printf '%s\n' ;egrep -i '(EXTRACT|REPLICAT)'|tr ":" " " | tr -s '[:space:]' | awk '{if ($2=="ABENDED" || $5>=05 || $7 >=05 ) {print $1" "$3" is "$2" with lag of " $4"HR:"$5"MI and checkpoint of "$7"HR:"$8"MI" "\n">> "/tmp/testoutput.log"} else {print "No lag for"$3 > "/tmp/nolag.log"} }' ;  done
info all
exit
!
###############################
if [ -f ${LOGFILE} ]; then
        x=$( grep -c ABENDED /tmp/testoutput.log )
        y=$( grep -c RUNNING  /tmp/testoutput.log )
        if [ $x -gt 0 ]; then
                echo "please refer below logs" >> $LOGFILE
                echo "##################################################################" >> $LOGFILE
                tac ${GG_HOME}/ggserr.log | grep -m 1 -A 2 -B 2 ABEND >> $LOGFILE
                mailx -s "Alert GG Abended on $BOX" fistname.lastname@company.com < $LOGFILE
        fi
        if [ $y -gt 0 ]; then
                ##echo "process running "
                mailx -s "Lag Found on $BOX" fistname.lastname@company.com < $LOGFILE
        fi
else
        echo "NO LAG FOUND"
fi
###################################

2 script It was created on Solaris platform for ksh shell. ggs_lag.ksh is main script which calls ggs.ksh , you can also merge both into one script also.

#!/bin/ksh
############################################
# Name: ggs_lag.ksh                             #
# PURPOSE: TO MONITOR LAG OF GOLDEN GATE        #
# NOTE: THIS SCRIPT CALLS ggs.ksh               #
# THIS SCRIPT NOTIFY IF LAG IS MORE THEN 30 MIN #
# ONLY FOR FOR EXT AND PMP PROCESS GROUP #
###########################################
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;./ggsci'
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
LOGDIR=/export/home/oracle/dba_scripts/ggs/logs
EMAILFile=$LOGDIR/ggs_email.log
BOX=$(uname -a | awk '{print $2}')

##########################################################################
# RUNNING SCRIPT TO GET GOLDEN GATE INFORMATION #
##########################################################################

/export/home/oracle/dba_scripts/ggs/ggs.ksh > $LOGDIR/ggs_1.log

#to check when script was running

echo "script ggsksh completed from ggs_lag at `date`" >> /tmp/ggs_check.log

##################################################################################
## FORMATING INFORMATION: change cut -d":" -f 1,4 TO cut -d":" -f 1,2 ##
## to getinformation about lag instead of checkpoint ##
## this command grep only EXT_ and PMP_ if you need more pattern ##
## if you need more pattern to be greped please add to '(EXT_|PMP_|pattern)' ##
##################################################################################


cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log

# uncomment below command if you want to get lag and checkpoint both information #

#cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_|DART)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log

# uncomment below command if you want to get lag information about running process #

#cat $LOGDIR/ggs_1.log|grep RUNNING|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 >
$LOGDIR/ggs_2.log

##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################

##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################


awk '{if ( $4 > 00 || $5 >=30 ) {print $1 " " $3 " HAS LAG of " $4" hour " $5 " min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " " d >> "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log

# uncomment below command if you want to get lag and checkpoint both information #

#awk '{if ($4 >=30 || $5>=30 ) {print $1 " " $3 " has lag of "$4" min with checkpoint of "$5" min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " "d > "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log

##########################################################
## SENDING EMAIL IF ERRORS ARE IN LOGFILE ###
##########################################################

if [ -s $EMAILFile ]; then
#echo "ERRORS FOUND"
mailx -s "GG LAG FOUND ON: $BOX" your.email@gmail.com team.email@gmail.com < $EMAILFile else cat /dev/null > $EMAILFile
#echo "ERRORS NOT FOUND"
fi

################# SCRIPT END ######################

This is ggs.ksh which calls by above ggs_lag.ksh

#!/bin/ksh
#########################################
#Name: ggs.ksh #
#THIS SCRIPT WILL CALLED BY ggs_lag.ksh #
#########################################
#alias gate='clear;cd $GGATE;./ggsci'
echo "ggsksh started `date`" >> /tmp/ggs_check.log
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;ggsci'
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
#cd $GGATE
/opt/oracle/u01/app/oracle/ggs/ggsci <<>
info all
exit
EOF
echo "ggsksh completed at `date` " >> /tmp/ggs_check.log