DBA Tips Archive for Oracle

  


BACKUP Examples

by Jeff Hunter, Sr. Database Administrator


Contents

  1. RMAN Default Configuration Parameters
  2. Offline Backup - (using configured defaults)
  3. Offline Backup - (without using configured defaults)
  4. Backup Archive Logs (All)
  5. Online Backup - (using configured defaults)
  6. Keeping Backups
  7. Incremental Backups
  8. Mirroring Backups
  9. Copying Disk Backup to Tape
  10. Backup Failures



RMAN Default Configuration Parameters

Several of the examples in this article will make use of RMAN default configuration parameters. Starting with Oracle9i, many of the RMAN parameters can be configured with default settings. Here is a listing of those parameters that I have set for all of the examples in this article:



    This example assumes that the target database is already configured with 
    automatic channels. The following will perform an offline backup. Notice that you
    you can use RMAN to shutdown and startup the database (which is required for performing
    an offline backup):

    
% ORACLE_SID=TARGDB; export TARGDB

% rman target backup_admin/backup_admin nocatalog

Recovery Manager: Release 9.2.0.5.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: TARGDB (DBID=2528050866)
using target database controlfile instead of recovery catalog

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup/rman/TARGDB/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/orabackup/rman/TARGDB/backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_TARGDB.f'; # default



Offline Backup - (using configured defaults)

This example assumes that the target database is already configured with automatic channels. The following will perform an offline backup. Notice that you you can use RMAN to shutdown and startup the database (which is required for performing an offline backup):
% rman target backup_admin/backup_admin

RMAN> shutdown immediate
RMAN> startup mount
RMAN> backup database;
RMAN> alter database open;



Offline Backup - (without using configured defaults)

This example assumes that the target database is not configured with and will not be using any configured defaults (i.e. automatic channels). The following will perform an offline backup. Notice that you you can use RMAN to shutdown and startup the database (which is required for performing an offline backup):
% rman target backup_admin/backup_admin nocatalog

RMAN> shutdown
RMAN> startup mount
RMAN> run {
2>  allocate channel c1 device type disk format '/orabackup/rman/TARGDB/backup_db_%d_S_%s_P_%p_T_%t';
3>  allocate channel c2 device type disk format '/orabackup/rman/TARGDB/backup_db_%d_S_%s_P_%p_T_%t';
4>  setlimit channel c1 maxpiecesize 25m;
5>  set maxcorrupt for datafile 1 to 0;
6>  backup full
7>      skip inaccessible
8>      tag rman_backup_hot_full
9>      filesperset 4
10>     (database include current controlfile);
11> # backup current controlfile;
12> }

allocated channel: c1
channel c1: sid=13 devtype=DISK

allocated channel: c2
channel c2: sid=14 devtype=DISK


executing command: SET MAX CORRUPT

Starting backup at 22-DEC-04
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00011 name=/u06/app/oradata/TARGDB/perfstat01.dbf
input datafile fno=00007 name=/u06/app/oradata/TARGDB/example01.dbf
input datafile fno=00004 name=/u06/app/oradata/TARGDB/drsys01.dbf
input datafile fno=00006 name=/u06/app/oradata/TARGDB/xdb01.dbf
channel c1: starting piece 1 at 22-DEC-04
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00002 name=/u06/app/oradata/TARGDB/undotbs01.dbf
input datafile fno=00003 name=/u06/app/oradata/TARGDB/cwmlite01.dbf
input datafile fno=00005 name=/u06/app/oradata/TARGDB/odm01.dbf
input datafile fno=00008 name=/u06/app/oradata/TARGDB/indx01.dbf
channel c2: starting piece 1 at 22-DEC-04
channel c1: finished piece 1 at 22-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_18_P_1_T_545604600 comment=NONE
channel c1: starting piece 2 at 22-DEC-04
channel c1: finished piece 2 at 22-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_18_P_2_T_545604600 comment=NONE
channel c1: starting piece 3 at 22-DEC-04
channel c1: finished piece 3 at 22-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_18_P_3_T_545604600 comment=NONE
channel c1: starting piece 4 at 22-DEC-04
channel c1: finished piece 4 at 22-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_18_P_4_T_545604600 comment=NONE
channel c1: starting piece 5 at 22-DEC-04
channel c1: finished piece 5 at 22-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_18_P_5_T_545604600 comment=NONE
channel c1: starting piece 6 at 22-DEC-04
channel c2: finished piece 1 at 22-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_19_P_1_T_545604600 comment=NONE
channel c2: backup set complete, elapsed time: 00:02:11
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00001 name=/u06/app/oradata/TARGDB/system01.dbf
input datafile fno=00009 name=/u06/app/oradata/TARGDB/tools01.dbf
input datafile fno=00010 name=/u06/app/oradata/TARGDB/users01.dbf
channel c2: starting piece 1 at 22-DEC-04
channel c1: finished piece 6 at 22-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_18_P_6_T_545604600 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:14
channel c2: finished piece 1 at 22-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_20_P_1_T_545604731 comment=NONE
channel c2: backup set complete, elapsed time: 00:01:58
Finished backup at 22-DEC-04

Starting Control File and SPFILE Autobackup at 22-DEC-04
piece handle=/orabackup/rman/TARGDB/c-2528050866-20041222-04 comment=NONE
Finished Control File and SPFILE Autobackup at 22-DEC-04
released channel: c2
released channel: c1

RMAN> exit;

% ls -l /orabackup/rman/TARGDB/*
-rw-r-----   1 oracle   dba      26214400 Dec 22 20:50 backup_db_TARGDB_S_18_P_1_T_545604600
-rw-r-----   1 oracle   dba      26214400 Dec 22 20:50 backup_db_TARGDB_S_18_P_2_T_545604600
-rw-r-----   1 oracle   dba      26214400 Dec 22 20:51 backup_db_TARGDB_S_18_P_3_T_545604600
-rw-r-----   1 oracle   dba      26214400 Dec 22 20:51 backup_db_TARGDB_S_18_P_4_T_545604600
-rw-r-----   1 oracle   dba      26214400 Dec 22 20:51 backup_db_TARGDB_S_18_P_5_T_545604600
-rw-r-----   1 oracle   dba      19333120 Dec 22 20:52 backup_db_TARGDB_S_18_P_6_T_545604600
-rw-r-----   1 oracle   dba      343392256 Dec 22 20:52 backup_db_TARGDB_S_19_P_1_T_545604600
-rw-r-----   1 oracle   dba      331636736 Dec 22 20:54 backup_db_TARGDB_S_20_P_1_T_545604731
-rw-r-----   1 oracle   dba      7946240 Dec 22 20:54 c-2528050866-20041222-04

Observations



Backup Archive Logs (All)

In the following example, I provide an RMAN command to backup all archive logs and to delete the archive redo log files from their archive destination (on disk) when the backup it complete.
% rman target backup_admin/backup_admin nocatalog

RMAN> backup archivelog all delete input;

Starting backup at 23-DEC-04
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=15 recid=5 stamp=545694669
channel ORA_DISK_1: starting piece 1 at 23-DEC-04
channel ORA_DISK_1: finished piece 1 at 23-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_34_P_1_T_545694669 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/app/oradata/TARGDB/archive/arch_t1_s15.dbf recid=5 stamp=545694669
Finished backup at 23-DEC-04

Starting Control File and SPFILE Autobackup at 23-DEC-04
piece handle=/orabackup/rman/TARGDB/c-2528050866-20041223-04 comment=NONE
Finished Control File and SPFILE Autobackup at 23-DEC-04

Observations



Online Backup - (using configured defaults)

This example performs an online (hot) backup of the Oracle database using RMAN. Before performing an online backup, the Oracle database must be in archive log mode. You can use the command "archive log list" to confirm that the database is in archive log mode:
SQL> archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u06/app/oradata/TARGDB/archive
Oldest online log sequence     16
Next log sequence to archive   17
Current log sequence           17
Oracle9i introduced the ability to perform an RMAN backup of the database along with all archived redo logs in one command. The details of what will happen from the command below have been already talked about. The only thing in this example is that all of the commands are put into one line.
% rman target backup_admin/backup_admin nocatalog

RMAN> backup database plus archivelog delete input;

Starting backup at 23-DEC-04
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=17 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=16 recid=6 stamp=545696327
channel ORA_DISK_1: starting piece 1 at 23-DEC-04
channel ORA_DISK_1: finished piece 1 at 23-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_38_P_1_T_545696328 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/app/oradata/TARGDB/archive/arch_t1_s16.dbf recid=6 stamp=545696327
Finished backup at 23-DEC-04

Starting backup at 23-DEC-04
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u06/app/oradata/TARGDB/system01.dbf
input datafile fno=00002 name=/u06/app/oradata/TARGDB/undotbs01.dbf
input datafile fno=00011 name=/u06/app/oradata/TARGDB/perfstat01.dbf
input datafile fno=00007 name=/u06/app/oradata/TARGDB/example01.dbf
input datafile fno=00003 name=/u06/app/oradata/TARGDB/cwmlite01.dbf
input datafile fno=00004 name=/u06/app/oradata/TARGDB/drsys01.dbf
input datafile fno=00005 name=/u06/app/oradata/TARGDB/odm01.dbf
input datafile fno=00006 name=/u06/app/oradata/TARGDB/xdb01.dbf
input datafile fno=00008 name=/u06/app/oradata/TARGDB/indx01.dbf
input datafile fno=00009 name=/u06/app/oradata/TARGDB/tools01.dbf
input datafile fno=00010 name=/u06/app/oradata/TARGDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-DEC-04
channel ORA_DISK_1: finished piece 1 at 23-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_39_P_1_T_545696330 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:15
Finished backup at 23-DEC-04

Starting backup at 23-DEC-04
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=17 recid=7 stamp=545696585
channel ORA_DISK_1: starting piece 1 at 23-DEC-04
channel ORA_DISK_1: finished piece 1 at 23-DEC-04
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_40_P_1_T_545696585 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/app/oradata/TARGDB/archive/arch_t1_s17.dbf recid=7 stamp=545696585
Finished backup at 23-DEC-04

Starting Control File and SPFILE Autobackup at 23-DEC-04
piece handle=/orabackup/rman/TARGDB/c-2528050866-20041223-06 comment=NONE
Finished Control File and SPFILE Autobackup at 23-DEC-04

Observations



Keeping Backups

You can mark backups that can override the default retention policy.

The KEEP FOREVER option requires the use of a true recovery catalog:

RMAN> backup tablespace users keep forever consistent;

RMAN> backup database keep until time '01-MAR-2005' logs;

Starting backup at 04-JAN-05
using channel ORA_DISK_1
backup will be obsolete on date 01-MAR-05
archived logs required to recover from this backup will expire when this backup expires
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u06/app/oradata/TARGDB/system01.dbf
input datafile fno=00002 name=/u06/app/oradata/TARGDB/undotbs01.dbf
input datafile fno=00011 name=/u06/app/oradata/TARGDB/perfstat01.dbf
input datafile fno=00007 name=/u06/app/oradata/TARGDB/example01.dbf
input datafile fno=00010 name=/u06/app/oradata/TARGDB/users01.dbf
input datafile fno=00003 name=/u06/app/oradata/TARGDB/cwmlite01.dbf
input datafile fno=00004 name=/u06/app/oradata/TARGDB/drsys01.dbf
input datafile fno=00005 name=/u06/app/oradata/TARGDB/odm01.dbf
input datafile fno=00006 name=/u06/app/oradata/TARGDB/xdb01.dbf
input datafile fno=00008 name=/u06/app/oradata/TARGDB/indx01.dbf
input datafile fno=00009 name=/u06/app/oradata/TARGDB/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 04-JAN-05
channel ORA_DISK_1: finished piece 1 at 04-JAN-05
piece handle=/orabackup/rman/TARGDB/backup_db_TARGDB_S_126_P_1_T_546727961 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:25
Finished backup at 04-JAN-05

Starting Control File and SPFILE Autobackup at 04-JAN-05
piece handle=/orabackup/rman/TARGDB/c-2528050866-20050104-04 comment=NONE
Finished Control File and SPFILE Autobackup at 04-JAN-05



Incremental Backups

This example performs an incremental (online) backup of the Oracle database using RMAN.

In this example, it is assumed that you want to take one full database backup once a week, and one incremental database backup every day. The backup cycle starts on Friday. A full backup is taken on Friday, and an incremental backup is then taken every day.

Also in this example, it is assumed that I will be using RMAN default configurations. One of the parameters I set is "CONFIGURE RETENTION POLICY TO REDUNDANCY 3". The retention policy of REDUNDANCY 3 applies only to full (not incremental) backups, so the combination of that policy and this backup schedule ensures that you can restore to any incremental backup time for the last 3 weeks.

run {
    # -----------------------------------------------------------
    # The following RMAN commands are run each Friday to start 
    # the backup cycle.
    # The steps are:
    #   - Backup database with incremental level 0.
    #   - Backup (and then delete) all archivelog files.
    # -----------------------------------------------------------
    backup
        incremental level 0
        filesperset 4
        (database);
    backup
        archivelog all
        delete input;
}
Now, let's look at what happens on those other days. The following commands can be run from Saturday through Thursday to take cumulative incremental backups. Notice that LEVEL 1 is specified with the BACKUP command.

Also notice that the options LEVEL 1 CUMULATIVE indicate that only the blocks that have changed since the last level 0 backup will be backed up. If the CUMULATIVE option was not specified, then only the blocks that have changed since the last LEVEL 1 backup will be backed up. The advantage of a cumulative backup is that only one incremental backup ever needs to be applied during recovery.

run {
    backup
        incremental level 1 cumulative
        filesperset 4
        (database);
}



Mirroring Backups

It is possible to multiplex up to 4 copies per set at runtime:
run {
    set backup copies 2;
    backup full
           format '/orabackup1/rman/TARGDB/backup_db_%d_S_%s_P_%p_T_%t'
                , '/orabackup2/rman/TARGDB/backup_db_%d_S_%s_P_%p_T_%t'
           (database);
}



Copying Disk Backup to Tape

It is possible to copy a disk backupset to tape. (Backing up a backup)
backup device sbt backup set all;


You can also copy a disk backup to tape then remove files to create room for the next disk backup:

backup device sbt backup set created before 'SYSDATE - 1' delete input;



Backup Failures

If the above backup fails for any reaon, you can use the NOT BACKED UP SINCE option on the BACKUP command (9i restartable backup feature) to continue from the point of failure. The small value of FILESPERSET is good for restartable backups. However you should note that smaller FILESPERSET produces more backup sets.
run {
    # -----------------------------------------------------------
    # To re-start from the point of failure, run following
    # commands.
    # -----------------------------------------------------------
    backup
        incremental level 0
        filesperset 4 
        not backed up since time 'sysdate-1'
        (database);
}


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 12-Apr-2004 00:00:00 EDT
Page Count: 11482