Scripts

script for trimming alert log to 1 day and taking backup of prev day
Posted by Mohammed on April 23, 2010
#!/usr/bin/ksh

############################################################################
## Program : save_alert_log.sh #
## #
## Purpose : The alert logs on many Oracle databases can grow to a very #
## large size over time. This can often impede the maintenace#
## of the system – because the DBA will need to sometimes scan#
## through many days or months of data when researching an #
## issue. This script tries to avoid that by ensuring that #
## the log file can be “refreshed” on a daily basis, meaning #
## that only the current day’s data will be kept in the log, #
## while the previous day’s data will be saved to another file#
## in a backup area. #
## #
## This script should be run from Oracle’s crontab at midnight#
## every night, so that the database will always have a new #
## alert log file each day. An example crontab entry could be#
## 0 00 * * * /oracle/product/local/scripts/save_alert.sh 2>&1#
## #
## Date : 19 May 2006. #
## Author : Basil S. Mullings #
############################################################################
## Modified: #
## #
## #
# Modification History: #
# DATE WHO DESC #
# ——– —– —————————————————-#
# 05/29/06 Basil Add an extra variable LOG_KEEP_DAYS to hold the #
# number of days that the log files should be kept on #
## the server before being deleted. #
## #
## #
############################################################################

##Setup some needed variables.
BKUP=bkup ##The backup directory to store the logs…
ORATAB=”/etc/oratab”
LOG_KEEP_DAYS=365 ##Keep this many days of log files on the server.
TMPFILE=/var/tmp/OracleAlertLog ##Just a temp scratch work area.
SQLUSER=”/ as sysdba”
GEN_ORA_ERROR=”ORA\-[0-9][0-9]*”
PATH=”$HOME:$HOME/bin:/usr/contrib/bin:/usr/local/bin:/usr/bin:/bin:/etc:.”
export PATH

## Now, parse the oratab file for all databases on the system.
## Then use the ORACLE_SID that is found in the oratab file
## to log onto that database, and retrieve the directory where
## the alter log file is stored (.ie. retrieve the path to the
## bdump directory.
##
#for sidEntry in `cat $ORATAB | grep -v “^#”`
for sidEntry in `cat $ORATAB | awk -F: ‘{print $1}’ | grep -v “^#”`
do
## Get date and time
CURR_DATE=`date ‘+%a_%m%d%H%M’` ##Example Fri_05191256 for Friday May 19th @1256 PM.

#ORACLE_SID=`echo $sidEntry | cut -f 1 -d :`
ORACLE_SID=$sidEntry
echo “Oracle Sid is $ORACLE_SID”

export ORACLE_SID
## Set the Oracle environment for this SID.
ORAENV_ASK=NO
. /usr/local/bin/oraenv
rm -f $TMPFILE > /dev/null 2>&1

##Now, let’s log onto the DB, and try to
##retrieve the bdump directory path.
sqlplus -s /nolog << EOF > $TMPFILE
connect $SQLUSER
set heading off;
set echo off;
set feedback off;

select ‘BACKGROUND_DUMP_DEST=’ ||value
from v\$parameter
where name=’background_dump_dest’;
exit;
EOF

##Ok, we had a problem talking to the database.
if [ `grep -c $GEN_ORA_ERROR $TMPFILE` -ne 0 ]
then
echo “ERROR: Unable to find the path to the alert log for DB $ORACLE_SID”
rm -f $TMPFILE > /dev/null 2>&1

else ##Ok, we can log into the DB, now let’s go find our bdump directory.

bdump=`grep BACKGROUND_DUMP_DEST $TMPFILE | awk -F “=” ‘{print $2}’`
#echo “BDUMP is $bdump”
bkupDir=$bdump/$BKUP

##Make sure our backup directory exists.
if [ ! -d $bkupDir ]
then
mkdir $bkupDir > /dev/null 2>&1
fi

##Now, move the alert log.
#echo “now moving $bdump/alert_${ORACLE_SID}.log to $bkupDir/alert_${ORACLE_SID}.$CURR_DATE”
mv $bdump/alert_${ORACLE_SID}.log $bkupDir/alert_${ORACLE_SID}.$CURR_DATE

#Procedure to shrink the log to 365 days
##Keep only the last 365 days worth of logs…delete all logs older than 365 days.
#echo “Now shrinking the logs in dir $bkupDir …”
find $bkupDir -name “*.*” -mtime +${LOG_KEEP_DAYS} -exec rm -f {} \;
fi

done
Posted in Administration, Scripts | Leave a Comment »
Good article on Response time Analysis
Posted by Mohammed on April 19, 2010
As DBA’s, one of our primary goal is to achieve optimal response time for the queries. But many a times we would be wondering how we can analyze whether a particular response time is optimal or not. If it is high, what issues are causing this large value etc….
For all such questions, here is one article for you which presents analyzing response time in a wonderful way.
http://www.oracle.com/technology/pub/articles/schumacher_analysis.html
It helped me alot in finding root causes in a performance problem in one of my database. Hope it will be same in your case….
Posted in Performance Tuning | Tagged: avg response time for queries, optimal response time | Leave a Comment »
script to list top 20 fragmented tables
Posted by Mohammed on December 31, 2009
SELECT * FROM
(SELECT
SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
NUM_ROWS,
AVG_ROW_LEN ROWLEN,
BLOCKS,
ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
ROUND(BLOCKS * (8000 – 23 * INI_TRANS) *
(1 – PCT_FREE / 100) / 1000000, 0) GROSS_MB,
ROUND((BLOCKS * (8000 – 23 * INI_TRANS) * (1 – PCT_FREE / 100) -
(AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) “WASTED_MB”
FROM DBA_TABLES
WHERE
NUM_ROWS IS NOT NULL AND
OWNER LIKE ‘%OWNER%’ AND
PARTITIONED = ‘NO’ AND
(IOT_TYPE != ‘IOT’ OR IOT_TYPE IS NULL)
ORDER BY 7 DESC)
WHERE ROWNUM <=20;
Posted in Performance Tuning, Scripts | Tagged: fragmentation, fragmented tables, script for fragmentation | Leave a Comment »
Script to check memory consumption by session
Posted by Mohammed on December 31, 2009
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 999 HEADING ‘SID’
COLUMN oracle_username FORMAT a12 HEADING ‘Oracle User’ JUSTIFY right
COLUMN os_username FORMAT a9 HEADING ‘O/S User’ JUSTIFY right
COLUMN session_program FORMAT a18 HEADING ‘Session Program’ TRUNC
COLUMN session_machine FORMAT a8 HEADING ‘Machine’ JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING ‘PGA Memory’
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING ‘PGA Memory Max’
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING ‘UGA Memory’
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING ‘UGA Memory MAX’
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session pga memory’) session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session pga memory max’) session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session uga memory’) session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session uga memory max’) session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC
/
or
SET LINESIZE 200
SET PAGESIZE 500
SET FEEDBACK OFF
SET VERIFY OFF
SET SERVEROUTPUT ON
SET TRIMSPOOL ON
COL “SESSION” FORMAT A50
COL “PID/THREAD” FORMAT A10
COL “ CURRENT SIZE” FORMAT A18
COL “ MAXIMUM SIZE” FORMAT A18
REM Setting user variables values
SET TERMOUT OFF
DEFINE sort_order = 3
DEFINE show_pga = ‘ON’
DEFINE show_uga = ‘ON’
COL sort_column NEW_VALUE sort_order
COL pga_column NEW_VALUE show_pga
COL uga_column NEW_VALUE show_uga
COL snap_column NEW_VALUE snap_time
SELECT nvl(:sort_choice, 3) “SORT_COLUMN”
FROM dual
/
SELECT nvl(:pga_choice, ‘ON’) “PGA_COLUMN”
FROM dual
/
SELECT nvl(:uga_choice, ‘ON’) “UGA_COLUMN”
FROM dual
/
SELECT to_char(sysdate, ‘YYYYMMDD_HH24MISS’) “SNAP_COLUMN”
FROM dual
/
REM Creating new snapshot spool file
SPOOL MEMORY_&snap_time
REM Showing PGA statistics for each session and background process
SET TERMOUT &show_pga
PROMPT
PROMPT :::::::::::::::::::::::::::::::::: PROGRAM GLOBAL AREA statistics :::::::::::::::::::::::::::::::::
SELECT to_char(ssn.sid, ’9999′) || ‘ – ‘ || nvl(ssn.username, nvl(bgp.name, ‘background’)) || ‘: ‘
|| nvl(lower(ssn.machine), ins.host_name) “SESSION”,
to_char(prc.spid, ’999999999′) “PID/THREAD”,
to_char((se1.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “ CURRENT SIZE”,
to_char((se2.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “ MAXIMUM SIZE”
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins
WHERE se1.statistic# = 20
AND se2.statistic# = 21
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+)
ORDER BY &sort_order DESC
/
REM Showing UGA statistics for each session and background process
SET TERMOUT &show_uga
PROMPT
PROMPT :::::::::::::::::::::::::::::::::::: USER GLOBAL AREA statistics ::::::::::::::::::::::::::::::::::
SELECT to_char(ssn.sid, ’9999′) || ‘ – ‘ || nvl(ssn.username, nvl(bgp.name, ‘background’)) || ‘: ‘
|| nvl(lower(ssn.machine), ins.host_name) “SESSION”,
to_char(prc.spid, ’999999999′) “PID/THREAD”,
to_char((se1.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “ CURRENT SIZE”,
to_char((se2.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “ MAXIMUM SIZE”
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins
WHERE se1.statistic# = 15
AND se2.statistic# = 16
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+)
ORDER BY &sort_order DESC
/
REM Showing sort information
SET TERMOUT ON
PROMPT
BEGIN
IF (&sort_order = 1) THEN
dbms_output.put_line(‘Ordered by SESSION’);
ELSIF (&sort_order = 2) THEN
dbms_output.put_line(‘Ordered by PID/THREAD’);
ELSIF (&sort_order = 3) THEN
dbms_output.put_line(‘Ordered by CURRENT SIZE’);
ELSIF (&sort_order = 4) THEN
dbms_output.put_line(‘Ordered by MAXIMUM SIZE’);
END IF;
END;
/
REM Closing current snapshot spool file
SPOOL OFF
REM Showing the menu and getting sort order and information viewing choice
PROMPT
PROMPT Choose the column you want to sort: == OR == You can choose which information to see:
PROMPT … 1. Order by SESSION … 5. PGA and UGA statistics (default)
PROMPT … 2. Order by PID/THREAD … 6. PGA statistics only
PROMPT … 3. Order by CURRENT SIZE (default) … 7. UGA statistics only
PROMPT … 4. Order by MAXIMUM SIZE
PROMPT
ACCEPT choice NUMBER PROMPT ‘Enter the number of your choice or press to refresh information: ‘
VAR sort_choice NUMBER
VAR pga_choice CHAR(3)
VAR uga_choice CHAR(3)
BEGIN
IF (&choice = 1 OR &choice = 2 OR &choice = 3 OR &choice = 4) THEN
:sort_choice := &choice;
:pga_choice := ‘&show_pga’;
:uga_choice := ‘&show_uga’;
ELSIF (&choice = 5) THEN
:sort_choice := &sort_order;
:pga_choice := ‘ON’;
:uga_choice := ‘ON’;
ELSIF (&choice = 6) THEN
:sort_choice := &sort_order;
:pga_choice := ‘ON’;
:uga_choice := ‘OFF’;
ELSIF (&choice = 7) THEN
:sort_choice := &sort_order;
:pga_choice := ‘OFF’;
:uga_choice := ‘ON’;
ELSE
:sort_choice := &sort_order;
:pga_choice := ‘&show_pga’;
:uga_choice := ‘&show_uga’;
END IF;
END;
/
Posted in Performance Tuning, Scripts | Tagged: memory consumption, script, session memory consumption | Leave a Comment »
Queries to know how many archives generated
Posted by Mohammed on December 31, 2009
# to know archives generated in a month day wise
select to_char(first_time,’DD-MON’),count(*) from v$log_history
where to_char(first_time,’MON’)=’DEC’
group by to_char(first_time,’DD-MON’);
# to know archives generated in a day
select to_char(first_time,’DD-MON’),count(*) from v$log_history
where to_char(first_time,’DD’)=10
group by to_char(first_time,’DD-MON’);

# to know archives generated in a day including time
select to_char(first_time,’DD-MON:hh24:mi:ss’) from v$log_history
where to_char(first_time,’DD’)=10;

# to know archives generated on specific date in hourly basis
select to_char(first_time,’hh24′),count(*) from v$log_history
where to_char(first_time,’dd-mm-yy’) in(’21-05-08′)
group by to_char(first_time,’hh24′);