10 Replies Latest reply: Dec 4, 2009 8:52 AM by EdStevens RSS

    Shell script to get the output of RMAN report need backup

    Mahipal
      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
          Robert Geier
          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
            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
              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
                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
                  And exceptions never happen........
                  Are you from Stuckeyville?
                  • 6. Re: Shell script to get the output of RMAN report need backup
                    user222828
                    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
                      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
                        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
                          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
                            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