This discussion is archived
10 Replies Latest reply: Dec 4, 2009 6:52 AM by EdStevens RSS

Shell script to get the output of RMAN report need backup

Mahipal Newbie
Currently Being Moderated
Hi,

I am planning to write a shell script to connect to the target and catalog databases using RMAN and query the "Report need backup" in rman and if it returns the output of any datafiles then I have to get the mail.

Like this the script needs to connect to each and every target database using same catalog and query "Report need backup" if it oputputs any datafiles which needs to take the backup then it has to send the mail.


Can any one please suggest/help me how to write the script for this.


Thanks,
Mahi
  • 1. Re: Shell script to get the output of RMAN report need backup
    RobertGeier Oracle ACE
    Currently Being Moderated
    What OS ? Unix ? Windows ? VMS ?

    I think you need to change your question from

    "I am planning to write a shell script"

    to

    "I want someone to write a shell script for me"

    Start by reading up on shell scripting and loops. You will need to loop through a file listing all of your databases (e.g oratab)
  • 2. Re: Shell script to get the output of RMAN report need backup
    ovasen Newbie
    Currently Being Moderated
    Hi.
    a possible solution would be to add a pl/sql procedure to the catalog database and query the rman schema direct.
    This could be combined with email shipping and Oracle scheduler or dbms_job:
       cursor v_1 (v_dbkey number) is
       select * from rman10204.rc_rman_status where start_time > sysdate - (1/2) and db_key = v_dbkey;
     BEGIN
        v_message_body := v_message_body||'############################ RMAN10204 ###################################################';
        for d_2 in d_1 loop
               v_name := d_2.name;
               v_dbid := d_2.dbid;
               k_dbkey := d_2.db_key;
                  v_message_body := v_message_body||' """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'||crlf;
                  v_message_body := v_message_body||' db : '||V_NAME||crlf;
                  v_message_body := v_message_body||' dbid : '||V_dbid||crlf||crlf;
               for v_2  in v_1 (k_dbkey) loop
                  v_message_body := v_message_body||' jobb : '||v_2.operation||crlf;
                  v_message_body := v_message_body||' type : '||v_2.object_type||crlf;
                  v_message_body := v_message_body||' starttid : '||to_char(v_2.start_time,'dd.mm.yyyy hh24:mi:ss')||crlf;
                  v_message_body := v_message_body||' sluttid : '||to_char(v_2.end_time,'dd.mm.yyyy hh24:mi:ss')||crlf;
                  v_message_body := v_message_body||' status : '||initcap(v_2.status)||crlf||crlf;
               end loop;
                  v_message_body := v_message_body||' """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'||crlf||crlf;
        end loop;
    
        v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
        utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
        utl_smtp.Mail(v_Mail_Conn, v_From);
        utl_smtp.Rcpt(v_Mail_Conn, v_Recipient1);
        utl_smtp.Data(v_Mail_Conn,
        'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
        'From: '   || v_From || crlf ||
        'Subject: '|| v_Subject || crlf ||
        'To: '     || v_Recipient1 || crlf ||
        crlf ||
        crlf || v_message_body|| crlf
      );
      utl_smtp.Quit(v_mail_conn);
     EXCEPTION
      WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
        raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
     END;
    Hopes this helps.
  • 3. Re: Shell script to get the output of RMAN report need backup
    user222828 Explorer
    Currently Being Moderated
    I can definitely get you started...
    You mentioned shell script so I am assuming you are asking about Unix...

    #!/bin/ksh
    #
    . $HOME/.profile
    ORATAB=/var/opt/oracle/oratab

    SID=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "\#" | grep -v "\*"`

    get_backup_needed()
    {
    sqlplus <<!
    / as sysdba
    Your SQL Here
    exit
    !
    }


    for i in $SID ; do

    export ORACLE_SID=$i
    get_backup_needed > output file 1
    grep *"specific output"* *"output file 1"* > output file 2
    if (( $? )); then
    echo "No Backup needed for Oracle Database $i"
    else
    mailx -s "Backup is needed for Oracle Database $i" your email address < output file 2
    fi

    done

    You can play around with the formatting of output files and such, modifying the grep to pull out the datafiles using awk or sed but that should get you started.

    Jamie
  • 4. Re: Shell script to get the output of RMAN report need backup
    EdStevens Guru
    Currently Being Moderated
    Mahi wrote:
    Hi,

    I am planning to write a shell script to connect to the target and catalog databases using RMAN and query the "Report need backup" in rman and if it returns the output of any datafiles then I have to get the mail.

    Like this the script needs to connect to each and every target database using same catalog and query "Report need backup" if it oputputs any datafiles which needs to take the backup then it has to send the mail.


    Can any one please suggest/help me how to write the script for this.


    Thanks,
    Mahi
    the question suggests a band-aid solution to a deeper problem. I'd ask why do you need to get email reporting files that need backing up? With a proper rman backup strategy in place, all of your files will always be sufficiently backed up and you won't have a need for a reactive approach.
  • 5. Re: Shell script to get the output of RMAN report need backup
    ovasen Newbie
    Currently Being Moderated
    And exceptions never happen........
    Are you from Stuckeyville?
  • 6. Re: Shell script to get the output of RMAN report need backup
    user222828 Explorer
    Currently Being Moderated
    That's a valid point.
    If you need to set up backups through a shell script you can just modify the one I gave you for rman...

    Cheers.

    Jamie

    Edited by: Jamie CC on Dec 3, 2009 5:16 AM
  • 7. Re: Shell script to get the output of RMAN report need backup
    EdStevens Guru
    Currently Being Moderated
    user12064975 wrote:
    And exceptions never happen........
    Sure they do. That's why we have the backup jobs themselves send us an email on failure.
    Are you from Stuckeyville?
    I guess that was meant to be some sort of attack on my skill/understanding/knowledge by implying that I live in a backwater town and therefore can't possibly know what I'm talking about. Such a comment, especially in context, says more about the person making the comment than the one receiving it.
  • 8. Re: Shell script to get the output of RMAN report need backup
    ovasen Newbie
    Currently Being Moderated
    Hi.
    That was not my intention.
    Ed Stevens was a character from the TV series called ED. The show was from a fiction town called Stuckeville. just thought you were a fan..... As I am.

    Rgds
    Ovasen
  • 9. Re: Shell script to get the output of RMAN report need backup
    ovasen Newbie
    Currently Being Moderated
    Hi.
    Forgot the technical stuff as I was stunned that you got all this:
    I guess that was meant to be some sort of attack on my skill/understanding/knowledge by implying that I live in a backwater town and therefore can't possibly know what I'm talking about. Such a comment, especially in context, says more about the person making the comment than the one receiving it.
    Out of this:
    Are you from Stuckeyville?
    Anyhow, I still don't entirely agree with you.
    Sure they do. That's why we have the backup jobs themselves send us an email on failure.
    You know, not everyone runs 10g or 11g with dbconsole or grid controle. There might be someone out there on 9i without the management server installed.
    There even might be someone out there that for some reason do not choose to install dbconsole or grid controll on their 10g/11g installations. In their case, a report might be usefull.
    In cases where dbconsole and grid controll is involved, I agree that this is the best tool for reporting.

    Rgds
    Ovasen
  • 10. Re: Shell script to get the output of RMAN report need backup
    EdStevens Guru
    Currently Being Moderated
    ovasen wrote:
    Hi.
    That was not my intention.
    Ed Stevens was a character from the TV series called ED. The show was from a fiction town called Stuckeville. just thought you were a fan..... As I am.
    Ah, I had forgotten about that. Please accept my apology. I had seen a few episodes, enjoyed it, but was never a big fan.

    Actually, the TV reference usually linked to me is Mr. Ed, the talking horse. That's the one where I have to develop some thick skin because people are always referencing it and always think they were the first one clever enough to do so.
    Rgds
    Ovasen

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points