Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Posts
    143

    Unanswered: Problem while emailing a log file

    Hi,
    If I can get some help on this it will be great.
    All I am trying to do here is - My shell script runs sqlplus (and connects to oracle using scott and tiger and password). It then runs a scott_query_database.sql file and creates a log file called scott_query_database.log all in the same directory.

    Finally my shell script should send this log file to my email mansac@rediffmail.com

    My script is doing the first part perfectly. And it creates the log file too(i checked..) However it does not email me anything. This is a little weird because it ran the first two times perfectly when I ran the script....also emailed me the log. Since the 3rd time onwards, it is not doing so. The log gets created but no email. The program wasn't touched so I am not sure what is happening and where to look to troubleshoot in unix(i am on AIX 4.3). (something like where these emails are hitting....or something similar..)

    Thanks

    My script is
    #######################################
    sqlplus -s scott/tiger <<-SQL
    @scott_query_database.sql
    exit
    SQL
    cat scott_query_database.log | mailx -r "DBUSER@`hostname`" -s "ALERT: A MESSAGE FROM SERVER01 " "mansac@rediffmail.com"
    #######################################

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Try this:
    Code:
    cat - scott_query_database.log <<! >mail.msg
    To:mansac@rediffmail.com
    Subject: ALERT: A MESSAGE FROM SERVER01
    !
    mailx -r "DBUSER@`hostname`" -t <mail.msg
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Feb 2004
    Posts
    143
    Thanks for the response.
    This didn't work either. The emails are not getting sent. What is mail.msg in the above code ? There is no mail.msg getting created either.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question

    The script woks, I use it every day!

    Did you code it correctly? mail.msg should be a temporary file created by the script.
    Where does scott_query_database.log come from? Is it from a spool statement in scott_query_database.sql?

    Your script should look like this if scott_query_database.log is from a spool statement:

    Code:
    #!/bin/ksh
    sqlplus -s scott/tiger <<-SQL
    @scott_query_database.sql
    exit
    SQL
    
    #   +--- Notice the '-'
    #   V
    cat - scott_query_database.log <<! >/tmp/mail.msg
    To: mansac@rediffmail.com
    Subject: ALERT: A MESSAGE FROM SERVER01
    !
    mailx -r "DBUSER@`hostname`" -t </tmp/mail.msg
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Feb 2004
    Posts
    143
    Thanks for your response.
    Yes, scott_query_database.log is from a spool statement in the .sql file.

    I made the code exactly as your latest post. Ditto the same. However, I am still not able to get it working.. (Either I am going crazy or must have done something silly...., but I just cannot spot where I am going wrong..)
    Everything is exactly as you mentioned.
    As a single command,
    mail mansac@rediffmail.com < scott_query_database.log

    works, but not the script.

    Any other ideas or suggestions, please if any. Thanks

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Post your script
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Feb 2004
    Posts
    143
    Hello,
    Here are the script, sql file and the log file that gets generated.
    Thanks for helping me out -----

    ***Contents of scott_query_database.sql **********************

    set feedback off;
    set heading off;
    spool scott_query_database.log
    Prompt The total number of records in emp table is
    select count(*) from emp;
    Prompt The current value of sequence EMP_SEQ is
    select last_number - cache_size "EMP_SEQ"
    from user_sequences where sequence_name = EMP_SEQ';
    spool off
    set heading on;
    set feedback on;

    ************************************************** ****

    ***Contents of scott_query_database.sh *************************
    #!/bin/ksh
    sqlplus -s scott/tiger <<-SQL
    @scott_query_database.sql
    exit
    SQL
    cat - scott_query_database.log <<! >/tmp/mail.msg
    To: mansac@rediffmail.com
    Subject: ALERT: A MESSAGE FROM SERVER01
    !
    mailx -r "DBUSER@`hostname`" -t </tmp/mail.msg

    ************************************************** ****

    **Contents of scott_query_database.log *************************

    The total number of records in emp table is

    22
    The current value of sequence EMP_SEQ is

    2998

    ************************************************** ****

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    Mine works, this is the e-mail:
    Date: Thu, 8 Jul 2004 15:02:33 -0400 (EDT)
    To: <mortiz1@beethoven.com>
    Subject: ALERT: A MESSAGE FROM SERVER01
    From: Oracle Admin <oracle@erorat.cerpzone.org>
    To : <mortiz1@beethoven.com>
    CC :
    --------------------------------------------------------------------------

    The total number of records in emp table is

    14
    The current value of sequence EMP_SEQ is

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

    Prev | Next | Reply | Reply All | Forward | Delete | Print | Move to folder...DraftMiscSentSpam
    And here is the code:
    Code:
    #!/bin/ksh
    sqlplus -s scott/tiger <<-SQL
    @scott_query_database.sql
    exit
    SQL
    cat - scott_query_database.log <<! >/tmp/mail.msg
    To: mortiz1@beethoven.com
    Subject: ALERT: A MESSAGE FROM SERVER01
    !
    mailx -r "oracle@`hostname`" -t </tmp/mail.msg
    Note that the '-r' user id MUST EXIST! (And have permissions to send e-mails)
    Last edited by LKBrwn_DBA; 07-08-04 at 16:15.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Feb 2004
    Posts
    143
    Allright ,,,, as a last resort, even though I coded everything exactly as your sh script, I now copied from your posting and pasted to my script and ran it...
    it created the log file without emailing me anything but also gave me a message saying

    mailx: Not a recognized flag: t

    (The line is exactly as yours ....
    mailx -r "DBUSER@`hostname`" -t </tmp/mail.msg

    And the DBUSER does exist.

    What could the problem be ?

    Thanks

  10. #10
    Join Date
    Feb 2004
    Posts
    143
    Out of curiousity, what is your unix flavour/version ? I am trying this script from an AIX but also have a HP-UX on another machine. I haven't tried it on HP-UX yet which I am planning to do now.......

  11. #11
    Join Date
    Feb 2004
    Posts
    143
    The mailx -t flag is not working there either....
    man mailx
    does not show the t option. This confuses me the fact that it worked for you.

  12. #12
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Solaris 8.

    Try it without the -t:

    mailx -r "DBUSER@`hostname`" </tmp/mail.msg
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  13. #13
    Join Date
    Feb 2004
    Posts
    143
    Tried that - - got this messsage

    The flags you gave make no sense since you're not sending mail.


    Thanks

  14. #14
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Sorry,

    Don't know why mailx is different in AIX and HP/UX, whan flags does man mailx say are valid?

    Look for one that does what solaris -t flag does:


    -t Scan the input for To:, Cc:, and Bcc: fields. Any
    recipients on the command line will be ignored.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •