Oracle Database 10g: The Top 20 Features for DBAs

Join Oracle Magazine's 2003 "DBA of the Year" Arup Nanda over the next 20 weeks as he presents his list of the top Oracle Database 10g features for database administrators

Follow along! Download Oracle Database 10g


Week 9
RMAN

RMAN becomes more powerful with a redesigned incremental backup scheme, offline recovery of incremental backups, previewing restore, recovering through resetlogs, file compression, and much more

Most people would agree that RMAN is the de facto tool of choice for Oracle database backup. But as powerful as they were, early versions of RMAN left something to be desired. Like many DBAs, I had pet peeves about the absence of what I consider to be must-have features.

Fortunately, Oracle Database 10g addresses many of these issues by incorporating many desirable features, making RMAN an even more powerful and useful tool. Let's take a look.

Incremental Backups Revisited

RMAN includes an option for incremental backups. But truthfully, how often do you use it? Probably occasionally, or possibly even never.

This option instructs the tool to back up blocks that have changed since the last incremental backup at the same level or below. For instance, a full backup (level_0) is taken on day 1 and two incrementals of level_1 are taken on days 2 and 3. The latter two merely back up the changed blocks between days 1 and 2 and days 2 and 3, not across the entire backup time. This strategy reduces backup size, requiring less space, and narrows the backup window, reducing the amount of data moving across the network.

The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files—hence, no media recovery is possible. Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are neither desirable nor practical. Rather, doing incremental backups in RMAN is an ideal alternative.

So why do many DBAs do incremental backups only rarely? One reason is that in Oracle9i and below, RMAN scans all the data blocks to identify candidates for backup. This process puts so much stress on the system that doing incrementals becomes impractical.

Oracle Database 10g RMAN implements incremental backups in a manner that disposes of that objection. It uses a file, analogous to journals in filesystems, to track the blocks that have changed since the last backup. RMAN reads this file to determine which blocks are to be backed up.

You can enable this tracking mechanism by issuing the following command:

SQL> alter database enable block change tracking using file '/rman_bkups/change.log';

This command creates a binary file called /rman_bkups/change.log for tracking purposes. Conversely, you can disable tracking with

SQL> alter database disable block change tracking;

To see whether change tracking is currently enabled, you can query:

SQL> select filename, status from v$block_change_tracking;

Flash Recovery Area

Flashback queries, introduced in Oracle9i, depend on undo tablespace to flash-back to a prior version, thereby limiting its ability go too far into the past. Flash recovery provided an alternative solution by creating flashback logs, which are similar to redo logs, to revert the database to a prior state. In summary, you create a flash recovery area for the database, specify its size, and place the database in flash recovery mode with the following SQL commands:

alter system set db_recovery_file_dest = '/ora_flash_area';
alter system set db_recovery_file_dest_size = 2g;
alter system set db_flashback_retention_target = 1440;
alter database flashback on; 

The database must be in archive log mode to be flashback-enabled. That process creates Oracle Managed Files in the directory /ora_flash_area, with a total size of up to 2GB. The database changes are written to these files and can be used to quickly recover the database to a point in the past.

By default, RMAN also uses /ora_flash_area to store backup files; thus, RMAN backups are stored on disk, not tape. For that reason, you have the ability to specify how many days you need to keep backups. After that period, the files are automatically deleted if more space is required.

The flash recovery area needn't be a filesystem or a directory, however—alternatively, it could be an Automatic Storage Management (ASM) diskgroup. In that case, the flash recovery area is specified by:

alter system set db_recovery_file_dest = '+dskgrp1';

Consequently, using ASM and RMAN in combination, you can build a highly scaleable, fault-tolerant storage system using cheap disks such as Serial ATA or SCSI drives, with no additional software required. (For more details about ASM, see the Week 8 installment in this series.) This approach not only makes the backup process much faster but also cheap enough to compete with the tape-based approach.

An additional benefit is protection against user errors. Because ASM files are not true filesystems, they are less likely to be corrupted accidentally by DBAs and sysadmins.

Incremental Merge

Let's say you have the following backup schedule:

Sunday - Level 0 (full), with tag level_0
Monday - Level 1 (incremental) with tag level_1_mon
Tuesday - Level 1 (incremental) with tag level_1_tue

and so on. If the database fails on Saturday, prior to 10g you would have had to restore the tag level_0 and then apply all six incrementals. It would have taken a long time, which is another reason many DBAs shun incremental backups.

Oracle Database 10g RMAN radically changes that equation. Now, your incremental backup command looks like this:

RMAN> backup incremental level_1 for recover of copy with tag level_0 database;

Here we have instructed RMAN to make an incremental level_1 backup and merge that with the full backup copy with the tag level_0. After this command, level_0 becomes a full backup of that day.

So, on Tuesday, the backup with tag level_0, when merged with incremental level_1 backup, becomes identical to the full Tuesday backup. Similarly, the incremental taken on Saturday, when applied to the backup on disk, will be equivalent to a full level_0 Saturday backup. If the database fails on Saturday, you just need to restore the level_0 backup plus a few archive logs to bring the database into a consistent state; there is no need to apply additional incrementals. This approach cuts down recovery time dramatically, speeds backup, and eliminates the need to make a full database backup again.

Compressed Files

With disk-based backups in the flash recovery area, you still have a big limitation: disk space. Especially when going across a network—as is usually the case—it's advisable to create as small a backup set as possible. In Oracle Database 10g RMAN, you can compress files inside the backup command itself:

RMAN> backup as compressed backupset incremental level 1 database;

Note the use of the clause COMPRESSED. It compresses backup files with an important difference: while restoring, RMAN can read the files without uncompressing. To confirm compression, check for the following message in the output:

channel ORA_DISK_1: starting compressed incremental level 1 datafile backupset

Furthermore, you can verify that the backup was compressed by checking the RMAN list output:

RMAN> list output;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Incr 1  2M         DISK        00:00:00     26-FEB-04      
        BP Key: 3   Status: AVAILABLE  Compressed: YES  Tag: TAG20040226T100154
        Piece Name: /ora_flash_area/SMILEY10/backupset/2004_02_26/o1_mf_ncsn1_TAG20040226T100154_03w2m3lr_.bkp
  Controlfile Included: Ckp SCN: 318556       Ckp time: 26-FEB-04
  SPFILE Included: Modification time: 26-FEB-04

As with any compression process, this approach puts pressure on CPUs. As a tradeoff, you can keep more RMAN backups on disk that are readily available for restore-and-recover operations. Alternatively, you can make RMAN backups at the Physical Standby Database that can be used to recover the primary database. That approach will offload backup resourses to another host.

Look Before You Leap: Recovery Preview

In Oracle Database 10g, RMAN has gone one more step ahead by providing the ability to preview the backups required to perform a restore operation.

RMAN> restore database preview;

Listing 1 shows the output of this operation. You can also preview specific restore operations; for example:

restore tablespace users preview;

Preview allows you to ensure the recovery readiness of your backup infrastructure by making periodic and regular checks.

Resetlogs and Recovery

Let's imagine that you have lost the current online redo log files and you have to perform an incomplete database recovery—a rare but not unheard of situation. The biggest problem is resetlogs; after incomplete recovery you must open the database with the resetlogs clause, which sets the sequence number of the log threads to 1, making your earlier backups obsolete in RMAN and making the recovery operation more of a challenge.

In Oracle9i and below, if you need to restore the database to a version prior to resetlogs, you have to restore to a different incarnation. In Oracle Database 10g, you don't have to do that. Thanks to additional infrastructure in the control file, RMAN can now readily use all backups, before and after a resetlogs operation, to recover the Oracle database. There is no need to shut down the database to make a backup. This new capability means that the database can be re-opened immediately for the user community after a resetlogs operation.

Ready for RMAN

The enhancements in Oracle Database 10g RMAN make it an even more compelling tool in your backup strategy. The improvements to the incremental backup process alone make RMAN tough to ignore.

For more information about RMAN in 10g, see Chapter 4 of Oracle Database Backup and Recovery Basics 10g Release 1 (10.1).

Next Week: Auditing

Back to Series Index


Please rate this document:

Excellent Good Average Below Average Poor

Send us your comments