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.
SET HEA OFF
SET FEED OFF
SET LINES 500
SET PAGES 500
SET TRIM ON
where rman_Status_stamp IN (
select stamp from V$RMAN_STATUS where parent_stamp =
where start_time =
(select max(start_time) from
V$RMAN_BACKUP_JOB_DETAILS where input_type = 'DB FULL')));