Bash Script to Upload RMAN Backups via FTP

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 ( 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”.

Please share

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.


  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.

    Norm. @NormanDunbar

    • Gokhan Atil

      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

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

      Norm. @NormanDunbar

  2. Pingback: Setting Oracle Environment in Scripts | Rants & Raves – The Blog! - My blogspace for rants and raves about anything I like! (or don't like!)

  3. Roshan Jose

    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;

    ——— ————-
    09-DEC-12 DB INCR
    14-DEC-12 DB INCR
    16-DEC-12 DB INCR
    23-DEC-12 DB INCR
    30-DEC-12 DB INCR
    06-JAN-13 DB INCR

    • Gokhan Atil

      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

      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

  4. Dimitrios Spanos

    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’;

  5. Pingback: Setting Oracle Environment in Scripts - Oracle - Oracle - Toad World

  6. Ademar Salomão

    Is there a way to implement this process in a Windows based system without third-part softwares and OS errors ?

Leave Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.