OraFAQ Forum: Unix » oracle error with date and timestamp

Home » Infrastructure » Unix » oracle error with date and timestamp

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

oracle error with date and timestamp [message #98309]

Sat, 10 July 2004 01:37

ramya
Messages: 87
Registered: August 2002

Member

Dear Sir
i want to get the oracle error with the date & timestamp to a file how to combine these two things
pls hlp
$ grep ORA- alert_sid.log >>oraerror.txt

with regards
ramya

Report message to a moderator

Re: oracle error with date and timestamp [message #98311 is a reply to message #98309]

Wed, 14 July 2004 06:28

Jai Vrat Singh
Messages: 205
Registered: September 2002
Location: Singapore

Senior Member

grep 'ORA-' alert_sid.log

you should enclose - in qoute ' because this hypen(-) is used to pass command line oprions as well.
Can you please get the alert_sid.log so that exact answer can be written.

Report message to a moderator

Re: oracle error with date and timestamp [message #98313 is a reply to message #98311]

Sat, 17 July 2004 21:29

ramya
Messages: 87
Registered: August 2002

Member

hi jai
i am giving the smaple i need if the logswitch occurs
or ora errrors occurs i have write to a file
with the name and timestamp eg
Thu Jun 17 11:06:54 2004
here no errors but i need to track that also

Thu Jun 17 11:06:54 2004
alter database mount exclusive
Thu Jun 17 11:06:55 2004
Successful mount of redo thread 1.
Thu Jun 17 11:06:55 2004
Completed: alter database mount exclusive
Thu Jun 17 11:06:55 2004
alter database open
Thu Jun 17 11:06:55 2004
Thread 1 opened at log sequence 5071
Current log# 1 seq# 5071 mem# 0: /db05/gms/kugmsorafiles/log/logkugms_01a.rlg
Current log# 1 seq# 5071 mem# 1: /db03/gms/kugmsorafiles/log/logkugms_01b.rlg
Successful open of redo thread 1.
Thu Jun 17 11:06:56 2004
SMON: enabling cache recovery
SMON: enabling tx recovery
Thu Jun 17 11:06:58 2004
Completed: alter database open
Shutting down instance (immediate)
License high water mark = 1
Fri Jun 18 00:35:27 2004
ALTER DATABASE CLOSE NORMAL
Fri Jun 18 00:35:28 2004
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Jun 18 00:35:29 2004
Thread 1 closed at log sequence 5071
Current log# 1 seq# 5071 mem# 0: /db05/gms/kugmsorafiles/log/logkugms_01a.rlg
Current log# 1 seq# 5071 mem# 1: /db03/gms/kugmsorafiles/log/logkugms_01b.rlg
Fri Jun 18 00:35:29 2004
Completed: ALTER DATABASE CLOSE NORMAL
Fri Jun 18 00:35:29 2004
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0

with regards
ramya

Report message to a moderator

Re: oracle error with date and timestamp [message #98323 is a reply to message #98313]

Wed, 21 July 2004 19:25

Jai Vrat Singh
Messages: 205
Registered: September 2002
Location: Singapore

Senior Member

-

Please write and save this file of name: date_expr

[SMTWF][uouehra][neduit] [A-Z][a-z][a-z] [0-3][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9] 2[0-9]*

Save this file as : filterErr.ksh in the same directory as above

#!/bin/ksh

TEMP_DATE_LIST=`echo /tmp/dtlist.$$`
#echo dateliest is $TEMP_DATE_LIST
egrep -nf date_expr $1>$TEMP_DATE_LIST

EXEC_STR=""
let PREV_LINE=0;
let SEARCH_RANGE=0;

for linenum in `cat $TEMP_DATE_LIST&#124cut -d':' -f1`
do
  let SEARCH_RANGE=$linenum-$PREV_LINE;

#  echo ----------SEARCHING FROM  $linenum  back to $SEARCH_RANGE lines ---------

  EXEC_STR="head -$linenum  $1 | tail -$SEARCH_RANGE &#124grep -i 'ORA-'  " 

  echo $EXEC_STR>/tmp/execfile.$$
  . /tmp/execfile.$$  >/tmp/thiserr.$$

  if [[ -s /tmp/thiserr.$$ ]] 
  then
#    echo TIME '--&gt;' 
    cat $TEMP_DATE_LIST &#124grep -i "^$PREV_LINE"  &#124sed -e 's/^[0-9]*://g'
    cat /tmp/thiserr.$$   
  fi
 
  let PREV_LINE=$linenum;
done

rm -f /tmp/thiserr.$$
rm -f $TEMP_DATE_LIST
rm -f /tmp/thiserr.$$

For this sample file : r.log

hu Jun 17 11:06:54 2004
alter database mount exclusive
Thu Jun 17 11:06:55 2004
Successful mount of redo thread 1.
Thu Jun 17 11:06:55 2004
Completed: alter database mount exclusive
Thu Jun 17 11:06:55 2004
alter database open
Thu Jun 17 11:06:55 2004
Thread 1 opened at log sequence 5071
Current log# 1 seq# 5071 mem# 0: /db05/gms/kugmsorafiles/log/logkugms_01a.rlg
Current log# 1 seq# 5071 mem# 1: /db03/gms/kugmsorafiles/log/logkugms_01b.rlg
ORA-2376 SOMETHINIG HAS OCCURRED HERE
Successful open of redo thread 1.
Thu Jun 17 11:06:56 2004
SMON: enabling cache recovery
SMON: enabling tx recovery
Thu Jun 17 11:06:58 2004
Completed: alter database open
Shutting down instance (immediate)
ORA-7634 SOMETHINIG HAS OCCURRED HERE AGAIN
License high water mark = 1
Fri Jun 18 00:35:27 2004
ALTER DATABASE CLOSE NORMAL
Fri Jun 18 00:35:28 2004
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Jun 18 00:35:29 2004
Thread 1 closed at log sequence 5071
Current log# 1 seq# 5071 mem# 0: /db05/gms/kugmsorafiles/log/logkugms_01a.rlg
Current log# 1 seq# 5071 mem# 1: /db03/gms/kugmsorafiles/log/logkugms_01b.rlg
Fri Jun 18 00:35:29 2004
Completed: ALTER DATABASE CLOSE NORMAL
Fri Jun 18 00:35:29 2004
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0

Usage:

$ ./filterErr.ksh r.log

Gives

Thu Jun 17 11:06:55 2004
ORA-2376 SOMETHINIG HAS OCCURRED HERE
Thu Jun 17 11:06:58 2004
ORA-7634 SOMETHINIG HAS OCCURRED HERE AGAIN

This program will not be efficient for very large files. But for the sample you sent , I have made it according to that. In case you file is very large then you can use perl program to do line by line reading.Is that what you were looking for?

-

Report message to a moderator

Re: oracle error with date and timestamp [message #98327 is a reply to message #98323]

Thu, 22 July 2004 02:42

ramya
Messages: 87
Registered: August 2002

Member

hi jai
realyy thnks fpr ur efforts
i will try this and letu know cos we have holiday 2days
then iam a beginer of unix and shell. i need to work more on
unix and shell scripting.How to get some good resource or
hlp for this.Here unix forum is so slow compares to other forums.
will u provide some good resources or links to learn more on this
shell programming
with regards

Report message to a moderator

Re: oracle error with date and timestamp [message #98328 is a reply to message #98327]

Thu, 22 July 2004 18:59

Jai Vrat Singh
Messages: 205
Registered: September 2002
Location: Singapore

Senior Member

Take any UNIX operating system book for an idea of unix. Then study about processes, shells, variables, arithmetic, string and file checking expressions, interpretation, expressions, loops, interprocess communication( pipes etc) . file handling, file system , input output redirection in shells, scheduling . You can search these individual topics on the internet and have study. Further see the basic semantics of sed , awk and regular expressions. After this you can concentrate on any particular shell ( say k-shell) for a practice. If you are a C programmer or you have enough got time then you can go through first 2-3 chapter of unix system programming by stevens. This will make your concepts very clear. After all Practice is ultimate.. Try whatever wild comes to your mind.. I am trying to learn this in the same way.
Jai Vrat

Report message to a moderator

Re: oracle error with date and timestamp [message #98329 is a reply to message #98323]

Fri, 23 July 2004 03:12

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

cool!

Report message to a moderator

Re: oracle error with date and timestamp [message #98331 is a reply to message #98328]

Fri, 23 July 2004 06:54

ramya
Messages: 87
Registered: August 2002

Member

THNKS jai
i ahve the basic idea of these things and comfortable with
commands piping those things not much familira wiht shell and this regual exp.i am trying to build these things by self study so hope that
u also help me
with regards
ramya

Report message to a moderator

Re: oracle error with date and timestamp [message #98332 is a reply to message #98328]

Fri, 23 July 2004 07:20

ramya
Messages: 87
Registered: August 2002

Member

Report message to a moderator

Re: oracle error with date and timestamp [message #98380 is a reply to message #98331]

Tue, 31 August 2004 03:16

Andy Black
Messages: 6
Registered: August 2004

Junior Member

Hiya,
Create file dba_alertlog.ksh and add the following to it. If you have any more queries just send me an email. I've loads of other scripts and will be uploading them to a site somewhere. Let me know how you get on.
Regards,
Andy

#!/usr/bin/ksh

#------------------------------------------------------------------------------------------------
usage()
#------------------------------------------------------------------------------------------------
{
cat <<END

Filename : dba_alertlog.ksh
Version : 1.0
Summary : Prints the alertlog with date/time stamps

Parameters : Mandatory:
: No parameters are mandatory.
: If no parameters are supplied then the alert log for the current ORACLE_SID will
: be displayed.
: Optional:
: Filename(s) of the alertlogs to be processed.

Calls : No other scripts

Date Author Version
20-Aug-04 ASBlack 1.0 Created.

NOTES:
1) This script is self documented. To get a program flow execute:
grep "#-- " dba_alertlog.ksh

EXAMPLES:
1) dba_alertlog.ksh
This will display the contents of the current alert log for the ORACLE_SID that is currently set

2) dba_alertlog.ksh alert_somesid.log.2004*
This will display the contents of all alert logs that have are named alertsomesid.log.2004*
which can be produced if automated housekeeping of the alertlogs has been implemented to "roll
over" alertlogs daily or weekly ...

END
}

#------------------------------------------------------------------------------------------------
# MAIN PROGRAM STARTS HERE
#------------------------------------------------------------------------------------------------

#-- Display usage if requested
if [[[[ ${1} = "help" ]]]]; then usage; exit 0; fi

#-- Set variables
PRG=`basename $0`
LOGFILES=""
CMD=""

#-- Get command line parameters
if [[ $# -ne 0 ]]; then LOGFILES=$@; fi

#-- Echo start time and command
date +"%Y/%m/%d %H:%M:$S Started ${PRG} ${PARAMETERS}"
echo

#-- Determine the location of the alert log if not specified
if [[ -z "${LOGFILES}" ]]; then

#-- Determine the ORACLE_HOME
ORAHOME=""
ORAHOME=`grep -h "^${ORACLE_SID}:" /var/opt/oracle/oratab /etc/oratab 2>/dev/null &#124head -1 &#124awk -F":" '{print $2}'`
if [[[[ -z "${ORAHOME}" ]]]]; then
echo "ERROR: Cannot determine ORACLE_HOME for ${ORACLE_SID} ..."
exit 10
fi

#-- Determine if there is an spfile
SPFILE=${ORAHOME}/dbs/spfile${ORACLE_SID}.ora

#-- If there isn't an spfile then use the init.ora file
if [[[[ ! -f ${SPFILE} ]]]]; then

#-- Determine where the init.ora file is
INITORA=${ORAHOME}/dbs/init${ORACLE_SID}.ora
echo INITORA = ${INITORA}

#-- Determine if there is an ifile
IFILE=`egrep -i "ifile&#124spfile" ${INITORA} &#124grep -v "^#" &#124head -1 &#124awk -F"=" '{print $2}' &#124sed -e 's/[[ ]]//g' -e "s/'//g"`
echo IFILE = ${IFILE}
fi

#-- Determine what background_dump_dest is set to in the init.ora file
# (It defaults to ?/rdbms/log within the database if it's not in the init.ora file)
BACKGROUND_DUMP_DEST=`grep -i "background_dump_dest" ${SPFILE} ${INITORA} ${IFILE} 2>/dev/null &#124grep -v "^#" &#124head -1 &#124awk -F"=" '{print $2}' &#124sed -e 's/[[ ]][[ ]]*//g' -e "s/'//g"`
if [[ -z "${BACKGROUND_DUMP_DEST}" ]]; then
BACKGROUND_DUMP_DEST=${ORACLE_HOME}/rdbms/log
fi

#-- Ensure that $ORACLE_HOME is expanded out
# This fixes a strange bug when running remotely, and
# background_dump_dest contains string: $ORACLE_HOME
eval BACKGROUND_DUMP_DEST=`echo ${BACKGROUND_DUMP_DEST} &#124sed -e "s+$ORACLE_HOME+${ORAHOME}+"`

#-- Determine where the alertlog file is
LOGFILES=${BACKGROUND_DUMP_DEST}/alert_${ORACLE_SID}.log

fi

#-- Check the log file(s) exist and are readable
for FILE in ${LOGFILES}
do

#-- Check that the file exist and is readable
if [[ ! -r "${FILE}" ]]; then
echo
date +"%Y/%m/%d %H:%M:%S ERROR: Log ${FILE} does not exist or is not readable"
echo
break
fi

#-- List alertlog for reference
echo
date +"%Y/%m/%d %H:%M:%S Processing alertlog ${FILE}"
ls -l ${FILE}
echo

#-- Get the command to send the alertlog to stdout
if [[ `echo ${FILE} &#124grep -c ".gz$"` -eq 1 ]]; then
CMD="gzip -dc ${FILE}"
elif [[ `echo ${FILE} &#124grep -c ".Z$"` -eq 1 ]]; then
CMD="zcat ${FILE}"
else
CMD="cat ${FILE}"
fi

#-- Print out the alert log with time stamps at the start of each record
#-- Note that date's in the alert log have the format:
#-- Fri Aug 16 12:52:55 2002
echo ${CMD} &#124ksh
&#124awk '{

if($1=="Mon" || $1=="Tue" || $1=="Wed" || $1=="Thu" || $1=="Fri" || $1=="Sat" || $1=="Sun")
{
DAY=$1
TIME=$4
YEAR=$5

DATE=$3
if(length(DATE)==1){DATE="0"DATE}

if($2=="Jan"){MONTH="01"}
if($2=="Feb"){MONTH="02"}
if($2=="Mar"){MONTH="03"}
if($2=="Apr"){MONTH="04"}
if($2=="May"){MONTH="05"}
if($2=="Jun"){MONTH="06"}
if($2=="Jul"){MONTH="07"}
if($2=="Aug"){MONTH="08"}
if($2=="Sep"){MONTH="09"}
if($2=="Oct"){MONTH="10"}
if($2=="Nov"){MONTH="11"}
if($2=="Dec"){MONTH="12"}

}
else
{
print YEAR"/"MONTH"/"DATE" "DAY" "TIME" "$0
}
}'

done # End of loop for FILE

Report message to a moderator

Re: oracle error with date and timestamp [message #98393 is a reply to message #98380]

Wed, 08 September 2004 03:10

Andy Black
Messages: 6
Registered: August 2004

Junior Member

Hi,

I've submitted the script in my previous reply to this site and it resides under the UNIX section.

Regards, Andy

Report message to a moderator

Previous Topic:	search and replace in vi editor
Next Topic:	How to Store a Table Filed Value to a UNIX Shell Variable

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 21:56:21 CST 2024