SkillBuilders | Oracle 10g: RMAN | Lesson 6: Backup with RMAN

Instructor's Notes

(If applicable)

Lesson 6 – Performing Backups with RMAN (A hands-on lesson on backing up an Oracle database with RMAN)
Author: Dave Anderson, SkillBuilders
www.skillbuilders.com
Last Update: October 17, 2007

This lesson will cover the administration following topics:

Database Backup Concepts
Backup Sets & Pieces
Control File Autobackup
Backup Examples
Full, Incremental and Cumulative Backups
Duplexing Backups
Backing Up the Archivelogs

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

This lesson will teach you – with working examples – how to backup your databases with Recovery Manager.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Backups are accomplished with the RMAN BACKUP command. The output of a backup operation is one or more backup sets and backup metadata (stored in the repository). The backup sets are used by RMAN in RESTORE operations.
RMAN remembers what backup sets are available and where they are located. It does this by storing this information in the target database control file and, if available, the recovery catalog. (Remember that one of the benefits of the recovery catalog is that it keeps all historical information; the control file history of backup sets eventually gets written over – see the CONTROL_FILE_RECORD_KEEP_TIME parameter.)
RMAN can backup directly to tape - if you have acquired and installed an MML on the target database. The Media Management Library is vendor-supplied software that the server sessions performing the backup can communicate with. So the server sessions created on behalf of RMAN can not talk directly to the tape; they require this intervening software. Oracle ships with a freebee from Legato called “Legato Single Server Version”. You can find a complete description of LSSV at www.legato.com/lssv.
A list of all Oracle-certified MML software can be found at www.oracle.com.

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> backup tablespace users; Starting backup at 19-NOV-03 allocated channel: ORA_DISK_1

Instructor's Notes

(If applicable)

RMAN requires that the database be either mounted or open – it cannot be entirely closed. This is because RMAN needs to update the target database control file with the backup information. (Remember, mounted means that the control file is open.) The exception to this is a database running in NOARCHIVELOG mode; in this case the database must be mounted, but cannot be open.
What can RMAN backup? Essentially RMAN backs up datafiles, control files and archive logs. But RMAN backup options allow many levels of granularity. For example, we can back up the whole database (all datafiles and the control file), a single tablespace, or even a specific datafile. RMAN also allows backup of the control file, archive logs and backup sets. Backup of backup sets can be useful to move the backup set to tape. (Though RMAN can backup directly to tape, perhaps the write to tape is too slow to do during the backup itself.)

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> backup datafile 9; Starting backup at 19-NOV-03 using channel ORA_DISK_1

Instructor's Notes

(If applicable)

The BACKUP command creates one or more backup sets. The backup sets contain the data that you have backed up in one or more operating system files (“backupset pieces”) and can be restored with the RESTORE command.
By default, one backupset is created for each channel allocated to the backup. The example above allocates two channels, so two sets (actually three, one for the control file autobackup) are created. This can be verified with the LIST command:

The Tag is a comment assigned by the BACKUP command and contains the date and time the backup was taken.

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> run { 2> allocate channel d1 type disk; 3> allocate channel d2 type disk; 4> backup full database; 5> }

Instructor's Notes

(If applicable)

The backup set is the unit of restart in the BACKUP operation. If a backup fails, we need to restart at the beginning of the set. By limiting backup set size, we can reduce the amount of time and work necessary in a restart situation. Use the MAXSETSIZE of the CONFIGURE and BACKUP commands to limit set size.
If a backup fails, you can restart the backup by using the ‘NOT BACKED UP SINCE TIME’ clause. For example, to restart a backup, skipping all files successfully backed up in the last 1 hour, execute:
RMAN> backup database not backed up since time 'sysdate – 1/24';

Starting backup at 23-MAY-05
...

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> backup database maxsetsize 500m; RMAN> configure maxsetsize to 25G; new RMAN configuration parameters: CONFIGURE MAXSETSIZE TO 25 G; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete This job only Persistent setting affects all jobs unless overridden

Instructor's Notes

(If applicable)

Each backup piece is equivalent to a single operating system file. The backup piece (or OS file) contains the blocks from the datafiles that were backed up. It is in an Oracle proprietary format and can only be restored with the RMAN RESTORE command.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

By default, RMAN will create one (potentially very large) backup piece in each backup set. This can be overridden with the MAXPIECESIZE parameter of the ALLOCATE CHANNEL or CONFIGURE CHANNEL commands. This can be necessary if your operating systems has a maximum file size.

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> configure channel device type disk 2> maxpiecesize 2g;

Instructor's Notes

(If applicable)

The CONTROL FILE is a critical file – we cannot mount and open the database without it. You should always maintain duplexed copies of the control file by specifying multiple files in the CONTROL_FILES initialization parameter.
In addition, you should configure RMAN to take control file AUTOBACKUPS. An AUTOBACKUP is a new Oracle9i feature; it causes RMAN to:

Automatically back up the control file and server parameter file anytime a BACKUP or COPY command is executed.
Use a specific name for the backup piece, allowing RMAN to find the file if a restore is required – even without a recovery catalog.
Backup the information about the current backup with the control file, providing the ability to roll forward to the most recent backup.

This means that as long as you have an AUTOBACKUP, you can restore the AUTOBACKUP, mount and recover the database, even if you have lost your control file and Recovery Catalog.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Enable AUTOBACKUP with the CONFIGURE command. All configured settings, including AUTOBACKUP, are persistent, i.e. once enabled with the CONFIGURE command, it remains enabled until you explicitly disable it.
The default location for the AUTOBACKUP backup piece is different on Windows and Unix machines. The default for Windows is ORACLE_HOME\database, the default for Unix is ORACLE_HOME/dbs. The name of the file is derived from the RMAN variable %F. %F resolves into a string containing the DBID, for example:
piece handle=C:\ORACLE\ORA81\DATABASE\C-4167529595-20020511-00
Use of a specific name and location is how RMAN locates the file during a RESTORE CONTROLFILE FROM AUTOBACKUP operation. RMAN expects the backup piece to have a certain name and location. Refer to the lesson titled Restore and Recovery for examples of restoring a control file.

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> configure controlfile autobackup on;

Instructor's Notes

(If applicable)

As shown above, we can use the CONFIGURE command to specify an alternate location (disk or tape) for the control file AUTOBACKUP. YOU MUST ALSO EXECUTE THE “CONFIGURE CONTROLFILE AUTOBACKUP ON” COMMAND. The CONFIGURE CONTROFILE AUTOBACKUP FORMAT is a different command and effects a different persistent setting.
You can also use the SET CONTROLFILE AUTOBACKUP FORMAT instead of the CONFIGURE command for one-time operations. The scope of the SET command is session; i.e the SET command affects only the current session.
However, note that “%F” must be used as the controlfile autobackup piece name. The variable “%F” is case sensitive and must be specified in upper case.
Again, you can find examples of restoring a control file in the lesson titled Restore and Recovery.

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> configure controlfile autobackup 2> format for device type disk 3> to 'c:\oracle\backups\%F'; SET CONTROLFILE AUTOBACKUP FORMAT … RMAN> list backup of controlfile;

Instructor's Notes

(If applicable)

A full backup copies all ever-used datafile blocks to the backup set. Blocks never used will not be written to the backup set.
The database must be at least mounted. If the database is running in ARCHIVELOG mode, the database can be open. There is no need to put the database in hot backup mode. RMAN will automatically detect inconsistent blocks and re-read those blocks.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The BACKUP DATABASE command will, by default, do a FULL backup of all datafiles (tempfiles are not backup up). A control file AUTOBACKUP will also occur if, as in this example, the BACKUP command is the last command in the RUN block and CONFIGURE CONTROLFILE AUTOBACKUP is ON. A control file AUTOBACKUP will also be done if the BACKUP command is run from the RMAN prompt.
The Oracle8i example shown above requires the manual channel allocation; note that this example still works in Oracle9i. The following Oracle9i example is simpler. Assuming that the default channel is defined with:

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;

Then the Oracle9i Full backup can be accomplished with:

RMAN> backup database plus archivelog;

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> configure controlfile autobackup on; RMAN> run { allocate channel d1 type disk format 'c:\oracle\backups\full.%d.%T.%s.%p'; backup database plus archivelog; } RMAN> backup database plus archivelog;

Instructor's Notes

(If applicable)

We can ask RMAN to do some archive log file housekeeping for us. For example, adding the PLUS ARCHIVELOG DELETE INPUT causes RMAN to delete all the archive logs it backs up.
Supplemental Notes
Note that the DELETE INPUT command also works when backing up backup sets (consider this as a way to move backup sets to different location or devices) and image copies. For example, I can move a backupset from an undesirable location to the pre-configured location with this command:
RMAN> backup backupset 83 delete input;

Starting backup at 23-MAY-05
Refer to the Oracle9i Recovery Manager Reference manual for a complete description of this option.

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> backup database plus archivelog delete input; . . . channel ORA_SBT_TAPE_1: starting archive log backupset channel ORA_SBT_TAPE_1: specifying archive log(s) in backup . . . channel ORA_SBT_TAPE_1: deleting archive log(s) archive log filename=/opt/oracle/oradata/test/arch/arch1_8.db

Instructor's Notes

(If applicable)

Another way to backup – and optionally delete – archive logs is to use the BACKUP ARCHIVELOG command. Use the DELETE INPUT option to delete all logs backed up.
In this example I also use the LIST BACKUP command to see all backups of archive logs in the last day. Refer to the Scripting and Reporting lesson for more information and examples of the LIST BACKUP command.

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> backup archivelog all delete input; Starting backup at 14-JUL-04 current log archived allocated channel: ORA_DISK_1 . . . RMAN> list backup of archivelog from time 'sysdate-1'; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 2 24M DISK 00:02:40 14-JUL-04

Instructor's Notes

(If applicable)

The complete syntax for the BACKUP command is found in the Oracle9i Recovery Manager Reference manual.
Brackets indicate optional parameter in the syntax shown here.
BACKUP Command Parameter information:

FULL - do a full backup - all datafiles and the current control file. This is the default.
INCREMENTAL LEVEL - do an incremental backup of the level specified by integer. See examples later in this lesson.

Continued on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

BACKUP [ FULL | INCREMENTAL LEVEL integer ] [ CUMULATIVE ] [ FORMAT '/path/filename' ] [ TAG tag ] {DATABASE | TABLESPACE 'name' , … | DATAFILE 'name' | number BACKUPSET ALL | number | time CURRENT CONTROLFILE} (choose one) [ PLUS ARCHIVELOG [DELETE INPUT] ]

Instructor's Notes

(If applicable)

FORMAT – specify the path and filename of the backup set. Incorporate the RMAN format specifiers such as %s and %p. The format specifiers are CASE sensitive. Some of the supported substitution variables are:

%s - Unique backup set number stored in the control file.
%p - Backup piece number within the backup set. Starts at one and incremented by 1 for each piece.
%d - Database name
%T - Year, month, and day in the following format: YYYYMMDD
%t - A time stamp, e.g. 470658069
%c - Integer representing copy number of backup piece when duplexing. Required when the COPIES option is use to create multiple copies of a backup set. See the section on “Duplexing”.
%u – Generated string (Oracle states this is a “compressed representation of the backup set number and time … created”.
%U – Shorthand for %u_%p_%c which generates a unique filename. This is the default backup set piece file name.

DATABASE | TABLESPACE - DATABASE requests a full database (same as the FULL option); TABLESPACE requests a backup of a specific tablespace.
TAG - Supply a unique name to identify the backup. Consider a standard that includes type of backup (FULL, INCREMENTAL), the target of the backup (DATABASE, TABLESPACE, DATAFILE, ARCHIVELOG) and a unique number.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Here is the output of the BACKUP … PLUS ARCHIVELOG command.
We can see that that there are 4 phases: switch current log and backup, backup database, switch current log and backup (again) and finally AUTOBACKUP of the control file.

Continued on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Starting backup at 11-MAY-02 current log archived channel d1: starting archive log backupset channel d1: starting full datafile backupset current log archived channel d1: starting archive log backupset Starting Control File Autobackup at 11-MAY-02

Instructor's Notes

(If applicable)

RMAN> run {
2> allocate channel d1 type disk format
3> 'c:\oracle\backups\full.%s.%p';
4> backup database plus archivelog;
5> }

released channel: ORA_DISK_1
allocated channel: d1
channel d1: sid=9 devtype=DISK

Starting backup at 11-MAY-02
current log archived
channel d1: starting archive log backupset
channel d1: specifying archive log(s) in backup set
input archive log thread=1 sequence=10 recid=1 stamp=461580898
input archive log thread=1 sequence=11 recid=2 stamp=461580898
channel d1: starting piece 1 at 11-MAY-02
channel d1: finished piece 1 at 11-MAY-02
piece handle=C:\ORACLE\BACKUPS\FULL.19.1 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:05
Finished backup at 11-MAY-02

Starting backup at 11-MAY-02
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\ORADATA\PROD\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\ORADATA\PROD\UNDOTBS01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\PROD\DRSYS01.DBF
input datafile fno=00008 name=C:\ORACLE\ORADATA\PROD\SKILL_INDEXES.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\PROD\TEST.DBF
input datafile fno=00005 name=C:\ORACLE\ORADATA\PROD\TOOLS01.DBF
channel d1: starting piece 1 at 11-MAY-02
channel d1: finished piece 1 at 11-MAY-02
piece handle=C:\ORACLE\BACKUPS\FULL.20.1 comment=NONE
channel d1: backup set complete, elapsed time: 00:07:06
Finished backup at 11-MAY-02

Continues…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Starting backup at 11-MAY-02
current log archived
channel d1: starting archive log backupset
channel d1: specifying archive log(s) in backup set
input archive log thread=1 sequence=12 recid=3 stamp=461581332
channel d1: starting piece 1 at 11-MAY-02
channel d1: finished piece 1 at 11-MAY-02
piece handle=C:\ORACLE\BACKUPS\FULL.21.1 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-MAY-02

Starting Control File Autobackup at 11-MAY-02
piece handle=C:\ORACLE\ORA81\DATABASE\C-4167529595-20020511-00 comment=NONE
Finished Control File Autobackup at 11-MAY-02
released channel: d1

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The example shown above makes use of the Automatic Channel Allocation feature of Oracle9i. Note that RMAN uses a standard channel name for automatically allocated channels: ORA_DISK_n, where n starts at 1 and goes up by 1 for each additional channel allocated.
Note that “TEST” is a reserved word and thus must be enclosed in quotes in the example above. Since it is enclosed in quotes, the name is also case sensitive.
Multiple tablespaces can be backed up in a single command:
RMAN> backup tablespace 'TEST', skill_indexes;

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> backup tablespace 'TEST'; Starting backup at 11-MAY-02 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=00004 name=C:\ORACLE\ORADATA\PROD\TEST.DBF channel ORA_DISK_1: starting piece 1 at 11-MAY-02 channel ORA_DISK_1: finished piece 1 at 11-MAY-02 piece handle=C:\ORACLE\ORA81\DATABASE\0IDO69D4_1_1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08 Finished backup at 11-MAY-02

Instructor's Notes

(If applicable)

In addition to tablespaces, you can also backup specific datafiles by name or number (use the REPORT SCHEMA command to see the datafile number).
Note that RMAN converts BACKUP TABLESPACE commands into a series of datafile names; BACKUP TABLESPACE is a convenience for the DBA.
Note also that the datafile name contains special characters ( \ ), and therefore must be enclosed in quotes. The operating system determines if the filename is case sensitive.

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> backup datafile 'C:\ORACLE\ORADATA\PROD\TEST.DBF'; RMAN> backup datafile 'C:\ORACLE\ORADATA\PROD\TEST.DBF', 2> 'C:\ORACLE\ORADATA\PROD\TOOLS01.DBF&