doubt in log switch frequency map option in toad

[Nirmalkumar]

Hi all,

Is it possible to get previous 5 months log switches using log switch frequency map option in toad.if so,please reply me.

Note:i can able to get current month log switches using log switch frequency map option in toad

Help apperciated..

Thanks,
Nirmal.

[spex · Accepted solution]

Hi Nirmal,

TOAD generates the frequency map from V$LOG_HISTORY, and V$LOG_HISTORY only goes back MAXLOGHISTORY entries. If MAXLOGHISTORY was set too low at control file creation, old entries will be dropped from V$LOG_HISTORY. The only way to change this parameter is to recreate your control files.

It is also possible to generate a log switch frequency map based on the alert log file. Depending on how long your retain old alert logs, this method may work for your needs.

PCS

[Nirmalkumar]

Hi pcs,

Thank u for ur reply..

How to create log switch frequency map option in toad for previous 5 months log switches using alertlog file.could u please help me out.


Thanks,
Nirmal.

[spex]

Hi Nirnal,

I wrote this (ugly) script for use on my servers, so you may have to modify it a bit. It is known to work with Oracle 9i. If you have multiple (archived) alert logs, add their filenames to the call to grep before '${BDUMP}/alert_${SID}.log'. Note that this script requires GNU grep for its '-B' option (before context). If you call the script like this './logswitch_hist.sh email', the report will be emailed to you instead of being written to stdout.

#!/usr/bin/sh

# modify the following variables to match your system
SID=yoursid
FNAME=${SID}
BDUMP=/opt/oracle/admin/${SID}/bdump

# bomb if alertlog doesn't exist
if [ ! -f ${BDUMP}/alert_${SID}.log ]
then
exit 1
fi

# remove tempfiles on signal
trap "[ -f /tmp/${FNAME}.${$}.1.tmp ] && rm -f /tmp/${FNAME}.${$}.1.tmp; \
[ -f /tmp/${FNAME}.${$}.2.tmp ] && rm -f /tmp/${FNAME}.${$}.2.tmp;" 0 1 2 3 15

# use gnu grep for '-B' switch
/usr/local/bin/grep -B 1 'Thread.*advanced' ${BDUMP}/alert_${SID}.log | \
tr -d '\n' | tr '-' '\n' | awk '!/^$/{print $2,$3,$4,$11}' | grep -E '[:digit:]+' > /tmp/${FNAME}.${$}.1.tmp

awk -F'[ :]' '{printf("log switch(es) on %s %s from %02d:00:00 to %02d:59:59\n",$1,$2,$3,$3)}' \
/tmp/${FNAME}.${$}.1.tmp | uniq -c > /tmp/${FNAME}.${$}.2.tmp

if [ "${1:-interactive}" != "email" ]
then
# good stuff on bottom
cat /tmp/${FNAME}.${$}.1.tmp
echo
cat /tmp/${FNAME}.${$}.2.tmp
else
# good stuff on top
mailx -m -s "${SID} LogSwitch Report - $(hostname) $(date +\%a\ \%Y\%m\%d)" your@email.addr <<- EOF
${SID} LogSwitch Report
-----------------------

$(cat /tmp/${FNAME}.${$}.2.tmp)

$(cat /tmp/${FNAME}.${$}.1.tmp)
EOF
fi

exit 0

PCS

[Yogeeraj_1]

Hi Nirmal,

PCS script works fine.

At the database level, if you want to know the amount of info that can be queried, you can also run the following sql:

yogeeraj@mydb.mu>select min(first_time) from v$log_history;

MIN(FIRST
_________
07-APR-07

Elapsed: 00:00:00.36
yogeeraj@mydb.mu>

You may also wish to create a custom table that would collect information from v$log_history daily or weekly (using a database trigger). Hence you will have log switch history for as much time as you wish.

hope this helps too!
yogeeraj

[Nirmalkumar]

Hi all,

PCS --- Thank you for your script.

Yogeeraj ---- Thank you for your information.


Thanks,
Nirmal.

[Nirmalkumar]

Hi pcs,

when i try to run the script which provided by u throws some error.the error as follows.
--------------------------------------------

/bin/grep: illegal option -- B
Usage: grep -hblcnsviw pattern file . . .
/bin/grep: illegal option -- E
Usage: grep -hblcnsviw pattern file . . .
logswitch.sh: syntax error at line 45: `end of file' unexpected

--------------------------------------------

could you please help me out to solve the errors.

Thanks,
Nirmal.

[spex]

Hello,

As I said in my last post, the script requires the GNU version of grep, available here:

http://hpux.connect.org.uk/hppd/hpux/Gnu/grep-2.5.1a/

After you install it, make sure the script uses GNU grep by prepending the full path ('/usr/local/bin/grep' instead of 'grep').

PCS

[Nirmalkumar]

Hi PCS,

Thank u for ur information..

As iam testing the script in test server of production box.To install gnu version of grep ,it will be tedious process (i have get approval from lot of persons).Is it possible to modify the script without using GNU version of grep.
Kindly help me out..

Thanks,
Nirmal.

[spex]

Hello (again),

To remove the script's reliance on GNU grep, replace:

/usr/local/bin/grep -B 1 'Thread.*advanced' ${BDUMP}/alert_${SID}.log | \
tr -d '\n' | tr '-' '\n' | awk '!/^$/{print $2,$3,$4,$11}' | grep -E '[:digit:]+' > /tmp/${FNAME}.${$}.1.tmp

with:

awk '/Thread.*advanced/{split(x,y);print y[2],y[3],y[4],$7};{x=$0};' ${BDUMP}/alert_${SID}.log > /tmp/${FNAME}.${$}.1.tmp

PCS