Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

doubt in log switch frequency map option in toad

SOLVED
Go to solution
Nirmalkumar
Frequent Advisor

doubt in log switch frequency map option in toad

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.

9 REPLIES
spex
Honored Contributor
Solution

Re: doubt in log switch frequency map option in toad

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
Frequent Advisor

Re: doubt in log switch frequency map option in toad

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
Honored Contributor

Re: doubt in log switch frequency map option in toad

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
Honored Contributor

Re: doubt in log switch frequency map option in toad

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Nirmalkumar
Frequent Advisor

Re: doubt in log switch frequency map option in toad

Hi all,

PCS --- Thank you for your script.

Yogeeraj ---- Thank you for your information.


Thanks,
Nirmal.
Nirmalkumar
Frequent Advisor

Re: doubt in log switch frequency map option in toad

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
Honored Contributor

Re: doubt in log switch frequency map option in toad

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
Frequent Advisor

Re: doubt in log switch frequency map option in toad

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
Honored Contributor

Re: doubt in log switch frequency map option in toad

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