Our customer asked if they can automatically upload latest RMAN backups via FTP to another server. Because they keep latest 3 backups in same directory, I wrote a small bash script which calls an SQL script to queries RMAN backups and then uploads only the backup pieces belong to the latest backup job. I wanted to share this script because it demonstrates some interesting methods such as handling arrays returned from SQLPLus.

Here’s the SQL script (rmanfind.sql) to query RMAN backups. Our customer takes archive log backups during the day, so I needed to state full backup jobs while querying.

The bash script (rmanftp.sh) can divided to 3 parts. In first part, the required credentials and environment variables are defined. On second part, rmanfind.sql is called. On last part, the resultset is processed and files are uploaded via FTP:

Here are the important parts of the script:

Line 20: The resultset returned from SQL Plus is put into an array variable. Be careful about the brackets.
Line 22: How many rows we have?
Line 26: A while loop starts here to processes all rows in the result set.
Line 30-31: I extract the path and file name because ftp client does not support full path names. So I create “cd” and “put” commands for each backup piece.
Line 36: SENDTHEMALL variable has newline (\n) characters but when I put it directly to EOF block, these newline characters are not honored, so I call “echo -e” and assign the result to FTPCMD variable.
Line 43: FTPCMD contains ftp commands such as “lcd XXXXX put YYYYY”.

11 Responses to “Bash Script to Upload RMAN Backups via FTP”

  1. Speaking as someone how absolutely hates to see oracle homes etc hard coded – personal experience, has caused no end of problems in the past – I tend to use this when I’m setting the correct Oracle environment in a script:

    export ORAENV_ASK=NO
    export ORACLE_SID=ORCL
    . oraenv
    export ORAENV_ASK=YES

    That way, you don;t have to ever change the hard coded ORACLE_HOME when you upgrade the database, as long as /etc/oratab is correct, so is your environment.

    Equally, I’d be inclined to set up an SSH key-pair and have the public key lodged on the “ftp” server and just use a quick scp command to tranfer the files over to the desired server.

    However, a very interesting and potentially useful script – thanks.

    Cheers,
    Norm. @NormanDunbar

    • Gokhan Atil says:

      Norman, thanks for your valuable feedback. I don’t know why I didn’t use oraenv. I’ve updated the script.

      By the way, I didn’t know that ORAENV_ASK trick. I use input redirection:

      . oarenv <<EOF
      ORCL
      EOF

    • We learn from each other. I wasn’t aware that oraenv took input redirection. Thanks!

      Cheers,
      Norm. @NormanDunbar

  2. Roshan Jose says:

    An interesting fact is that I dont find the DB FULL entries as below. I wasn’t getting output, so I split the query to check where the issue was:

    SQL> select start_time,input_type from V$RMAN_BACKUP_JOB_DETAILS;

    START_TIM INPUT_TYPE
    ——— ————-
    09-DEC-12 DB INCR
    13-DEC-12 ARCHIVELOG
    14-DEC-12 DB INCR
    16-DEC-12 DB INCR
    20-DEC-12 ARCHIVELOG
    23-DEC-12 DB INCR
    27-DEC-12 ARCHIVELOG
    30-DEC-12 DB INCR
    03-JAN-13 ARCHIVELOG
    06-JAN-13 DB INCR
    11-JAN-13 ARCHIVELOG

    • Gokhan Atil says:

      Roshan, if you take the full backups as “level 0″, then they will be shown as “DB INCR” in V$RMAN_BACKUP_JOB_DETAILS.

    • Roshan Jose says:

      You are right. its taken as “level 0″. So instead of DB FULL, if I give DB INCR, it will give the correct output right

    • Gokhan Atil says:

      Yes it should give the correct output.

  3. Roshan Jose says:

    Wonderful. Thanks. I have accomodate this into my backup scripts.

  4. Dimitrios Spanos says:

    Very nice transfer script !
    A proposition for the ‘rmanfind.sql’ script, that would be simpler with less views used and may be faster :

    select handle from V$BACKUP_PIECE
    where rman_status_recid in
    (SELECT recid FROM v$rman_status
    WHERE session_recid = (select max(session_recid) from v$rman_status where OBJECT_TYPE=’DB FULL’)
    AND operation =’BACKUP’);

    which is faster with a join :

    select handle from V$BACKUP_PIECE b, v$rman_status s
    where session_recid = (select max(session_recid) from v$rman_status where OBJECT_TYPE=’DB FULL’)
    and b.rman_status_recid=s.recid
    and s.operation=’BACKUP';

Trackbacks/Pingbacks

  1. Setting Oracle Environment in Scripts | Rants & Raves – The Blog! - My blogspace for rants and raves about anything I like! (or don't like!) - [...] Today, I learned a new way to do the above, with much less typing. You can see an example …
  2. Setting Oracle Environment in Scripts - Oracle - Oracle - Toad World - [...] Today, I learned a new way to do the above, with much less typing. You can see an example …

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">