Oracle RMAN full backup script to disk with compression

April 12, 2013 by 9 Comments 

In this article I’ll share an RMAN script to backup the entire Oracle database including archived redo logs to disk using compression. Before looking into the script see my notes below:

– Read about Oracle Recovery Manager (RMAN) concepts
– Script should work for Oracle 10g and 11g, 12c
Oracle database name = ORCL
– I use 4 RMAN channels to speed up the backup
– I backup intentionally control file manually and automatically
– I limit the size of backup sets by number of files per set and size of backup sets
– I use backup compression in RMAN script (Oracle Advanced Compression license is required)
– The average compression factor can be different but to give you an idea it can be around 7.
– This script is independent from RMAN configuration parameters

# Last update: 14-Mar-2013
#
connect target /
sql 'alter system archive log current';
sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
RUN
{
configure controlfile autobackup on;
set command id to 'ORCLOnlineBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/opt/oracle/backups/ORCL/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup tag ORCL_ARCHIVE format '/opt/oracle/backups/ORCL/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
backup tag ORCL_CONTROL current controlfile format '/opt/oracle/backups/ORCL/%d_%T_%s_%p_CONTROL';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

I find also valuable the below RMAN backup summary report that gives you an idea what backup sets are created as a result of running that RMAN script.

RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
5421 B F A DISK 22-MAR-13 1 1 YES ORCL_FULL
5422 B F A DISK 22-MAR-13 1 1 YES ORCL_FULL
5423 B F A DISK 22-MAR-13 1 1 YES ORCL_FULL
5424 B F A DISK 22-MAR-13 1 1 YES ORCL_FULL
5425 B F A DISK 22-MAR-13 1 1 NO TAG20130322T182553
5426 B A A DISK 22-MAR-13 1 1 NO ORCL_ARCHIVE
5427 B A A DISK 22-MAR-13 1 1 NO ORCL_ARCHIVE
5428 B F A DISK 22-MAR-13 1 1 NO ORCL_CONTROL
5429 B F A DISK 22-MAR-13 1 1 NO TAG20130322T182603

I encourage readers to share / comment own commands and tips on the similar Oracle RMAN full backup script in this post. And I’ll continue with more RMAN scripts in the next articles.

Check out some other RMAN scripts from me:
Oracle RMAN full backup script for EMC NetWorker

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

9 Responses to “Oracle RMAN full backup script to disk with compression”
  1. I used this script and it worked. Thanks

    RUN {
    ALLOCATE CHANNEL c1 DEVICE TYPE disk;
    ALLOCATE CHANNEL c2 DEVICE TYPE disk;
    ALLOCATE CHANNEL c3 DEVICE TYPE disk;
    ALLOCATE CHANNEL c4 DEVICE TYPE disk;
    CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
    BACKUP
    FULL
    FORMAT ‘%d_%U’
    DATABASE
    INCLUDE CURRENT CONTROLFILE
    PLUS ARCHIVELOG
    ;
    RELEASE CHANNEL c1;
    RELEASE CHANNEL c2;
    RELEASE CHANNEL c3;
    RELEASE CHANNEL c4;
    }

  2. vinay says:

    Is there any rman script which will also identify the errors and send it across in mail…
    Failed backup or error need to be reported.

  3. Kirill Loifman says:

    You need to script it or set alert in OEM Cloud Control
    — Kirill

  4. Marc says:

    Hi,

    Is it possible to know how to script getting result values (and transmitting it to the caller)?
    In fact this result is needed to automatically decide the next step of a procedure, that must not be executed if the rman script aborts.

    regards,
    Marc
    p.s. Apologies for my bad English…

  5. Sam says:

    Hi Kiril, I like your backup script. It works perfect. Do you have full database restore script includes control files?

  6. Kirill Loifman says:

    You can find the recovery script including control files here:
    http://www.dadbm.com/oracle-rman-recovery-script-for-emc-networker/
    — Kirill

  7. Liviu says:

    Hi Kirill,

    the script is fine in general. It will work for sure, but it has One potential problem that may affect your PITR restore if you use this backup.
    Because you use 4 channels in the same run block for all jobs (database, switch log and archivelogs),
    one channel can finish earlier than others and switch the current log too early – while db backup still running.
    That makes recovery incomplete.
    I would suggest to do 2 run blocks, one for database backup and the second for switch log and archivelogs.
    When it runs in the block, then all parallel processes will have to finish before it starts another block.

    Cheers

  8. durgaprasad G says:

    hi

    i have one query below script is working fine but it is not removing old files can you please verify if you find free time?

    X:\Oracle\admin\MSIPPROD\scripts>type rman_nightly_backup.bat

    set oracle_sid=MSIPPROD
    set ORACLE_HOME=D:\oracle\product\11.2.0.4\dbhome_1

    rman target / cmdfile=X:\oracle\admin\MSIPPROD\scripts\rman_Nightly_backup.sql log=Y:\oracle\NightlyBackup\rman.log

    exit

    X:\Oracle\admin\MSIPPROD\scripts>type rman_nightly_backup.sql
    SET COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;
    run {
    allocate channel oem_backup_disk1 type disk format ‘Y:\oracle\NightlyBackup\%d_%T_%U’maxpiecesize 10 G;
    backup keep until time ‘SYSDATE+4’ as COMPRESSED BACKUPSET database;
    backup as COMPRESSED BACKUPSET archivelog all not backed up delete all input;
    release channel oem_backup_disk1;
    }

    allocate channel for maintenance type disk;
    delete noprompt obsolete device type disk;
    release channel;

    Thanks & regards
    Durgaprasad G

  9. Vimal says:

    Hi Kirill,
    Below is my RMAN script for 2 node RAC. I ran the below script and it ran in parallel. So is it required to provide
    ” CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;” in scipt for parallelism.

    connect target /
    sql ‘alter system archive log current’;
    sql “alter session set nls_date_format=”dd.mm.yyyy hh24:mi:ss””;
    RUN
    {
    configure controlfile autobackup on;
    configure COMPRESSION ALGORITHM ‘HIGH’;
    set command id to ‘OnlineBackupFull’;
    ALLOCATE CHANNEL c1 DEVICE TYPE disk connect ‘sys/XXX@X1’;
    ALLOCATE CHANNEL c2 DEVICE TYPE disk connect ‘sys/XXX@X1’;
    ALLOCATE CHANNEL c3 DEVICE TYPE disk connect ‘sys/XXX@X2’;
    ALLOCATE CHANNEL c4 DEVICE TYPE disk connect ‘sys/XXX@X2’;

    backup AS COMPRESSED BACKUPSET full database tag PERFOPERA_FULL format ‘/nfsdbshare/%d_%T_%s_%p_FULL’ ;
    sql ‘alter system archive log current’;
    backup AS COMPRESSED BACKUPSET archivelog all tag PERFOPERA_ARCHIVE format ‘/nfsdbshare/%d_%T_%s_%p_ARCHIVE’;
    backup tag PERFOPERA_CONTROL current controlfile format ‘/nfsdbshare/%d_%T_%s_%p_CONTROL’;
    release channel c1;
    release channel c2;
    release channel c3;
    release channel c4;
    }

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email