SkillBuilders | Oracle 10g: RMAN | Lesson 13: RMAN Backup & Recovery Enhancements

Instructor's Notes

(If applicable)

Lesson 13 – Oracle 10g RMAN (Backup and Recovery) Enhancements. (A practical introduction to new and improved features related to Oracle 10g Backup and Recovery.)
Author: Dave Anderson, SkillBuilders
www.skillbuilders.com
Last Update: October 17, 2007

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

Updated for R2!

Instructor's Notes

(If applicable)

In this lesson you will learn about Oracle 10g RMAN and Backup and Recovery features such as:

Flash Recovery Area
Flashback Database
Restore Points
Simplified Recovery through RESETLOGS
Compressed Backups
Change Tracking
Incrementally Updated Image Copies
SWITCH DATABASE
New V$ Views
Encrypted Backups
CATALOG Command
DROP DATABASE Command
Miscellaneous New Features

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

Instructor's Notes

(If applicable)

The Flash Recovery Area, introduced with Oracle 10g Release 1, is an optional (but recommended) storage area for many types of recovery-related files. In addition to redo logs, archive logs, RMAN backupsets, this includes flashback logs, required for the FLASHBACK DATABASE statement. You will learn more about flashback logs and the FLASHBACK DATABASE statement later in this course.
The flash recovery area is optional. However, it is required for the 10g features Flashback Database and Guaranteed Restore Points. Refer to the Flashback Enhancements lesson of this course for more information on these features.
The flash recovery area can be created on a OS file system or on Automated Storage Management (ASM) disks. ASM is the new integrated file and volume management system included in all editions of Oracle 10g. Refer to the ASM lesson in this course for more information on ASM.
Whatever type of file system chosen, DBAs will want to create the flash recovery area on separate disks from the datafile and redo log disks. This will prevent the loss of both the primary database files and the backups if a disk fails.

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

Instructor's Notes

(If applicable)

The flash recovery area can simplify database management. For example, the database automatically deletes obsolete logs and backup sets when space is required, removing the need for the DBA to manage (e.g. clean-up) the disks containing these files. Recovery-related files become obsolete when they exceed the RMAN retention policy or have been backed up to tape. Refer to the next page to see the alert log error message issued if the flash recovery area begins to run out of space.
Another new RMAN command provides a convenient technique for backing up the recovery area to tape (tape is the mandatory destination for this command). This is accomplished with the “BACKUP RECOVERY AREA” command (BACKUP DB_RECOVERY_FILE_DEST is a synonym for BACKUP RECOVERY AREA). Files in the flash recovery area that are backed up to tape are retained on disk but are eligible for deletion if space becomes constrained. Refer to the BACKUP command in the Oracle® Database Recovery Manager Reference 10g (Release 2) for more information.
Oracle 10g RMAN also introduces the BACKUP RECOVERY FILES command. This command backups all disk-based recovery files to tape, whether they are in the flash recovery area or not.
Notes for this slide continue on the next page…

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

RMAN> BACKUP RECOVERY AREA; RMAN> BACKUP DB_RECOVERY_FILE_DEST; RMAN> BACKUP RECOVERY FILES; Equivalent Backup all recovery files to tape, even if not in flash recovery area

Instructor's Notes

(If applicable)

This is the alert log entry if you run short of space in the flash recovery area:
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_2723.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 92.12% used, and has 169270784 remaining bytes available.
*********************************************************
You have the following choices to free up space from
flash recovery area:
1. Consider changing your RMAN retention policy.
If you are using dataguard, then consider changing your RMAN archivelog deletion policy.
2. Backup files to tertiary device such as tape using the
RMAN command BACKUP RECOVERY AREA.
3. Add disk space and increase the db_recovery_file_
dest_size parameter to reflect the new space.
4. Delete unnecessary files using the RMAN DELETE command. If an OS command was used to delete files, then use RMAN CROSSCHECK and RMAN DELETE EXPIRED commands.
********************************************************

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

Instructor's Notes

(If applicable)

Set the location, space limit and retention limit for flashback logs (if flashback database capability is desired) when establishing the flash recovery area. Since this is a recovery-related area, keep this on separate disk devices from the database files.
Refer to Section 3.2 of the Oracle® Database Backup and Recovery Basics 10g Release 2 manual and Chapter 5 of the Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 for more information on configuring the Flash Recovery Area.
Supplemental Notes

A related step (for configuration of the flash recovery area) is to enable flashback for the database with the ALTER DATABASE FLASHBACK ON statement. An example of this statement is shown later in this course.
The Database Configuration Assistant supports the configuration of the Flash Recovery Area.
The LOG_ARCHIVE_START parameter is deprecated in Oracle10g.

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

NAME VALUE ------------------------------ -------------------------- db_recovery_file_dest /mnt/mickeymantle db_recovery_file_dest_size 10G db_flashback_retention_target 1440 Size Limit How long to keep flash logs Location

Instructor's Notes

(If applicable)

A new view, V$RECOVERY_FILE_DEST, is available to describe the current flash recovery area. Here we can quickly find space allocated, used and “reclaimable”. Reclaimable space can be made available via the database deleting “obsolete, redundant or low priority files.” Files become obsolete or redundant when they exceed the RMAN retention policy or have been backed up to tape.

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

SQL> exec print_table('select * from v$recovery_file_dest') NAME : /mnt/mickeymantle/ SPACE_LIMIT : 10737418240 SPACE_USED : 2485816832 SPACE_RECLAIMABLE : 19995648 NUMBER_OF_FILES : 35 ----------------- How much space can be made available through delete of “obsolete, redundant or low priority files” Total number of archive and flashback logs in the recovery area

Instructor's Notes

(If applicable)

Oracle10g Release 2 provides another dynamic performance view, V$FLASH_RECOVERY_AREA_USAGE. This view records space used by file type, where the file type can be CONTROLFILE, ONLINELOG, ARCHIVELOG, BACKUPPIECE, IMAGECOPY, FLASHBACKLOG. The PERCENT_SPACE_USED is the percentage of the total space as reported by V$RECOVERY_FILE_DEST.SPACE_USED.

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

SQL> exec system.print_table – > ('select * from v$flash_recovery_area_usage') FILE_TYPE : ARCHIVELOG PERCENT_SPACE_USED : 6.02 PERCENT_SPACE_RECLAIMABLE : 0 NUMBER_OF_FILES : 5 ----------------- FILE_TYPE : BACKUPPIECE PERCENT_SPACE_USED : .21 PERCENT_SPACE_RECLAIMABLE : .1 NUMBER_OF_FILES : 2 ----------------- R2

Instructor's Notes

(If applicable)

Oracle 10g Release 1 (Enterprise Edition only) provides a new and easy alternative to using RMAN (or other methods) to perform a point-in-time (incomplete) recovery: Flashback Database. The FLASHBACK DATABASE statement is implemented as an SQL statement and an RMAN command. (The RMAN command provides more functionality, such as the ability to flashback database to a log sequence number.)
Flashback database is an alternative to point-in-time (incomplete) recovery. However, it cannot be used in cases of corrupt datafiles or media failure. The database must be in good working order for the FLASHBACK DATABASE statement to work.

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

EE Feature

Instructor's Notes

(If applicable)

The benefits (versus traditional point-in-time recovery) include:

As I will demonstrate later in this lesson, it is easy to use.
Restoring datafiles from backup sets or image copies is unnecessary. The existing datafiles are recovered to the SCN specified.
Recovery is faster than traditional point-in-time recovery because it is applying changed blocks (stored in the flashback logs, discussed in more detail later in this lesson) – not reapplying transactions to a restored datafile.
After you have flashed back the database you can query the database, confirm you like what you see, then use OPEN RESETLOGS to make the database generally available. If you do not like the point in time you have flashed back to, simply flash back again.

The flashback database operation uses a combination of flashback logs and archived redo logs to restore the database to the point requested. Next, we’ll learn about flashback logs.

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

Instructor's Notes

(If applicable)

Flashback logs are written to the flash recovery area if the database is configured for flashback database support. (We will see how to do that next.) The logs contain changed blocks. The flashback logs are different from redo logs and undo – they are a separate, optional recording of database activity.
The transaction rate on your database will have an affect on the performance of creating the logs and the number and size of the logs. Oracle says the overhead of enabling flashback database is “comparatively limited”. Refer to chapter 5 of the Oracle Database Backup and Recovery Basics manual for tuning tips. I’ll summarize them here:

Use fast disks for the flash recovery area
Avoid file system caching. Use ASM if possible (refer to the Automatic Storage Management lesson of this course for more information on ASM).
Use striped storage volumes, with a small (128k) stripe size (using ASM takes care of this).

Notes for this slide continue on the next page…

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

Instructor's Notes

(If applicable)

Logs are automatically deleted when:

The flash recovery area becomes constrained (i.e. runs out of free space)
The log is not needed for a recovery within the window specified in the DB_FLASHBACK_RETENTION_TARGET parameter.

There are a few undocumented flashback database-related parameters that could be investigated for additional control and tuning options. See the script FLASHBACK_PARAMETERS_UNDOCUMENTED.SQL for the following code:

SYS@orcl> SELECT x$ksppi.ksppinm, x$ksppi.ksppdesc, x$ksppcv.ksppstvl
2 FROM x$ksppi, x$ksppcv
3 WHERE x$ksppi.indx = x$ksppcv.indx
4 and x$ksppi.ksppinm like '/_%flashback%' escape '/'
5 order by 1;

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

Instructor's Notes

(If applicable)

The database must be configured for flashback database:

The database must be in archivelog mode.
Configure a flash recovery area. The flash recovery area is the repository for the flashback logs. Refer to the information presented earlier in this lesson and Chapter 5 of the Oracle Database Backup and Recovery Basics manual to learn how to configure the flash recovery area.
Put the database in flashback mode. As shown above, this is done with the ALTER DATABASE FLASHBACK ON statement. This starts a new background process (RVWR) to write the logs.

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

SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLA --- YES Starts recovery writer background process (RVWR)

Instructor's Notes

(If applicable)

Here is a simple example of using flashback database. Here, I flashback to an SCN which I might have gotten from a flashback version query (or the logminer utility). The database is opened in read-only mode, at which time tables can be queried. If you decide you would like to flashback to a different point in time, shutdown and mount the database, then use flashback database again. When the database is flashed back to the desired point in time, open the database with the RESETLOGS option.
Supplemental Notes
- Offline datafiles are not flashed back. However, if there are referential integrity issues because you have not flashed back one or more datafiles, you’ll receive the error:
- ORA-01152: file 4 was not restored from a sufficiently old backup
- So, essentially, you cannot do this – you will have to either recover or drop the file.
- Finally, note that I would normally recommend a full backup be taken after the OPEN RESETLOGS operation. However, Oracle 10g provides a seamless recovery across RESETLOGS so this is not as essential as it used to be. Refer to the Backup and Recovery lesson of this course for more information.

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

SQL> startup mount SQL> flashback database to scn 1427369; Flashback complete. SQL> alter database open read only; Database altered. Flashback to SCN or timestamp. Need SYSDBA privilege. Can open READ ONLY to check things out If necessary, re-mount, flashback to different SCN or time SQL> alter database open resetlogs; Database altered.

Instructor's Notes

(If applicable)

Release 2 of Oracle 10g introduces the “TO BEFORE RESETLOGS” clause of the FLASHBACK DATABASE statement. It provides an easy way to flashback across a RESETLOGS operation – to restore to the previous incarnation of the database. (It is actually supported by the same new architecture that supports the “simplified recovery across resetlogs operations”, discussed later in this lesson.) The FLASHBACK DATABASE TO BEFORE RESETLOGS statement provides an easy way to undo an OPEN RESETLOGS operation.
See Chapter 7 of the Backup and Recovery Basics manual for more information and examples of this feature. Also refer to the Oracle Backup and Recovery Reference 10g Release 2 and the Oracle Database SQL Reference 10g Release 2 for syntax and more information on the FLASHBACK DATABASE statement.

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

R2 SYS@orcl> startup mount ORACLE instance started. SYS@orcl> flashback database to before resetlogs; Flashback complete. SYS@orcl> alter database open resetlogs; Database altered.

Instructor's Notes

(If applicable)

The new V$FLASHBACK_DATABASE_LOG dynamic performance view reveals some useful information about the state of flashback database and flashback log size requirements. For example, OLDEST_FLASHBACK_SCN and OLDEST_FLASHBACK_TIME reveal how far back the database can be flashed back. FLASHBACK_SIZE shows the amount of space currently being used by flashback data. ESTIMATED_FLASHBACK_SIZE shows how much space – given the current workload – is actually required to maintain 1440 minutes of retention.

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

SYSTEM@orcl> exec print_table- ('select * from v$flashback_database_log') OLDEST_FLASHBACK_SCN : 3581965 OLDEST_FLASHBACK_TIME : 30-may-2006 14:35:02 RETENTION_TARGET : 1440 FLASHBACK_SIZE : 701038592 ESTIMATED_FLASHBACK_SIZE : 112975872 ----------------- Lowest SCN in flashback logs

Instructor's Notes

(If applicable)

The V$FLASHBACK_DATABASE_STAT view can be used to see how many bytes of flashback data (database and redo too) were written during the last “interval”. How long the interval is undocumented (or at least I could not find it).
It also can be helpful in determining space requirements for the flashback logs.

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

SYSTEM@orcl> exec print_table- ('select * from v$flashback_database_stat') BEGIN_TIME : 09-jun-2006 14:17:59 END_TIME : 09-jun-2006 14:56:44 FLASHBACK_DATA : 5103616 DB_DATA : 26370048 REDO_DATA : 1952768 ESTIMATED_FLASHBACK_SIZE : 0 -----------------

Instructor's Notes

(If applicable)

Restore points give the DBA a convenient method of defining an arbitrary string that is equated to an SCN. The restore point (i.e. the “string”) can be used in a FLASHBACK DATABASE statement.
Normal restore points can be used to flashback database as far back as the DB_FLASHBACK_RETENTION_VALUE (flashback logs older than that can be purged). SELECT ANY DICTIONARY or FLASHBACK ANY TABLE privilege is required to create a normal restore point.
Guaranteed restore points prohibit the database from purging flashback logs needed to flashback the database to that point. CAUTION: If you run out of space in the flash recovery area, the database will hang. Oracle 10g issues an “alert” if reclaimable space is less than 15%. (“Reclaimable” means creating free/available space by deleting obsolete logs, RMAN backup pieces, etc.) Alerts are recorded in the DBA_OUTSTANDING_ALERTS view and, using Enterprise Manager, can easily notify the DBA via email or page. SYSDBA privilege is required to create a guaranteed restore point.
Next, let’s look at an example…

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

Instructor's Notes

(If applicable)

Creating a restore point is done with the CREATE RESTORE POINT statement. In this example, a guaranteed restore point is created, requiring SYSDBA privilege.
Details concerning restore points are recorded in a new view V$RESTORE_POINT.
Refer to Chapter 5 of the Backup and Recovery Basics manual for more information and examples of this feature. Also refer to the Oracle10g SQL Reference manual for the syntax of the CREATE RESTORE POINT statement.
Refer to the supplied script V$RESTORE_POINT.SQL for a copy of this code.

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

SYSTEM@orcl> exec p_t('select * from v$restore_point') SCN : 3760762 DATABASE_INCARNATION# : 11 GUARANTEE_FLASHBACK_DATABASE : YES STORAGE_SIZE : 11943936 TIME : 09-JUN-06 01.42.40.00 PM NAME : FRIDAY140 V$RESTORE_POINT records details about restore points SYSTEM@orcl> connect / as sysdba Connected. SYS@orcl> create restore point friday140 2 guarantee flashback database; Restore point created.

Instructor's Notes

(If applicable)

In this example we see the FLASHBACK DATABASE statement being used to flashback to the restore point “FRIDAY140” (created in the previous example). The OPEN RESETLOGS is required to open the database for read / write activity.
Restore points are a persistent object. Therefore, you can flashback database to a restore point, retry the upgrade or application and, if another failure occurs, flashback again to the restore point.
Because restore points persist in the database, they should (must in the case of guaranteed restore points) be dropped; otherwise, the flash recovery area can fill with unnecessary flashback logs.

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

SYS@orcl> startup mount ORACLE instance started. SYS@orcl> flashback database to restore point friday140; Flashback complete. SYS@orcl> alter database open resetlogs; Database altered. SYS@orcl> drop restore point friday140; Restore point dropped. Drop the restore point when no longer needed

Instructor's Notes

(If applicable)

After a point-in-time recovery (incomplete recovery) or a recovery using a backup control file, the Oracle database must be opened with the RESETLOGS operation. RESETLOGS creates a new incarnation of the database. An incarnation SCN and timestamp are recorded in the control file, data file headers, redo logs, archived logs and backup pieces.
In prior releases, backups and archive logs created before a RESETLOGS could not be used in the recovery of the current database incarnation. With Oracle10g, this is no longer true. This provides several benefits:

There is no need to do a full database backup after OPEN RESETLOGS and before opening the database for general use.
“Old” backups can be used in recovery. If newer backups are corrupted or lost, “old” backup can be used if available.
RMAN incremental backups can be based on a level 0 backup taken before RESETLOGS.

Oracle has implemented this so as to be transparent to the DBA. i.e. Existing RMAN restore and recovery scripts will work, even if they access a backup created before a RESETLOGS operation.

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

Instructor's Notes

(If applicable)

Obviously, there are some architecture changes required to support the simplified recovery through resetlogs. In essence, what Oracle 10g needs to do is keep track of each incarnation and be able to determine what recovery-related files belong to each incarnation. To do this, Oracle 10g adds the following changes:

The LOG_ARCHIVE_FORMAT parameter (which controls the name of the archive log) must include a new variable, “%r”. The %r variable provides an incarnation identifier (Oracle calls it a “resetlogs identifier”). %r insures that the archive log name is unique if a new incarnation of the database is created. (Other items in the archive log name typically include %t (thread) and %s (sequence number) which can be redundant across incarnations because the sequence number is reset to 0 by RESETLOGS.)
V$ARCHIVED_LOG view contains a new column, RESETLOGS_ID, that contains the resetlogs identifier.

Architecture changes continued on the next slide…

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

Instructor's Notes

(If applicable)

Architecture changes (continued):

Dynamic performance views V$LOG_HISTORY and V$OFFLINE_RANGE are not cleared by RESETLOGS operations.
The V$LOG_HISTORY and V$OFFLINE_RANGE views contain new columns that hold the SCN and date of the database incarnation that the log belongs to.
The dynamic performance view V$DATABASE_INCARNATION contains new columns to support the tracking of the parent / child relationship between incarnations and the RESETLOGS identifier for each incarnation. Release 2 adds a new column, FLASHBACK_DATABASE_ALLOWED, which is demonstrated next in this lesson.

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

R2

Instructor's Notes

(If applicable)

Release 2 of Oracle 10g adds a new column to the V$DATABASE_INCARNATION view called FLASHBACK_DATABASE_ALLOWED. This indicates whether or not the database will support a FLASHBACK DATABASE statement to some time or SCN within that incarnation. The ability to flashback into an incarnation would require flashback logs and archived redo logs old enough to support the operation.
See the supplied script V$DATABASE_INCARNATION.SQL for a copy of this code.

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

SYS@orcl> select prior_resetlogs_time, 2 resetlogs_time, 3 flashback_database_allowed 4 from v$database_incarnation; PRIOR_RES RESETLOGS FLASHBACK_DATABASE_ALLOWED --------- --------- -------------------------- 30-AUG-05 27-JAN-06 NO 27-JAN-06 31-MAR-06 NO 27-JAN-06 31-MAR-06 NO 31-MAR-06 25-APR-06 NO 31-MAR-06 26-APR-06 NO 31-MAR-06 26-APR-06 NO 26-APR-06 26-APR-06 NO 26-APR-06 27-APR-06 NO 26-APR-06 27-APR-06 YES 27-APR-06 02-JUN-06 YES R2

Instructor's Notes

(If applicable)

RMAN now supports the creation of compressed backupset pieces. As you can see, my tests show significant reduction in piece size. Compression not only saves space but can save lots of time for network backups (due to the reduced number of bytes sent across the network).
Supplemental Notes
- You can configure your RMAN environment so compression is the default. For example:

RMAN> configure device type disk backup type
2> to compressed backupset;
RMAN> configure device type sbt backup type
2> to compressed backupset;

The V$BACKUP_PIECE view has a new column, COMPRESSED, which will contain “YES” for compressed backup pieces.

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

RMAN> backup as compressed backupset full database plus archivelog; Starting backup at 08-SEP-04 current log archived +ASM> select a.name, f.blocks, f.bytes 2 from v$asm_alias a, v$asm_file f 3 where a.file_number=f.file_number 4 and type = 'BACKUPSET' 5* order by f.type, a.name +ASM> / NAME BLOCKS BYTES ----------------------------------- ---------- ---------- annnf0_TAG20040908T162645_0.284.5 12115 6202880 nnndf0_TAG20040908T161717_0.343.13 136003 557068288 Savings of 123888 blocks! Your results will vary…

Instructor's Notes

(If applicable)

Oracle10g RMAN incremental backups are potentially much faster because the entire datafile does not have to be read to determine which blocks have changed. Instead, the database records (i.e. tracks) changed blocks in a file. When an incremental backup is taken, RMAN accesses the “block change tracking file” to determine which blocks to backup.
In the test shown above, the elapsed time of the level 3 incremental backup is 3 seconds; the same level 3 incremental backup with change tracking disabled required 46 seconds.
This behavior must be configured with the ALTER DATABASE ENABLE BLOCK CHANGE TRACKING statement as shown above. The default location of the block change tracking file is the Oracle Managed File DB_CREATE_FILE_DEST parameter. Use the “FILE” clause if you are not using OMF or you want to put the file in a location other than DB_CREATE_FILE_DEST.
Refer to the Oracle10g SQL Reference for more information on the ALTER DATABASE statement and the Oracle10g Database Backup and Recovery Basics manual for more information on this feature in general.

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

Instructor's Notes

(If applicable)

Change tracking is implemented by the creation of a file that records which blocks are changed. The Oracle documentation says that the initial size of the file is 10MB and grows in 10MB increments. The space required is 1/30,000 of the blocks tracked. The file size shown in the example above is roughly 10MB.
While the file size should not be an issue, Oracle does mention a performance hit related to the use of this feature. Test before implementing.
Refer to Chapter 4 of the Oracle10g Database Backup and Recovery Basics manual for more information on change tracking. Specifically, read the section entitled “Improving Incremental Backup Performance: Change Tracking.”
Supplemental Notes
- Execute the following query to determine if change tracking is enabled for your database:

DAVE@linux3> select status from v$block_change_tracking;

STATUS
----------
ENABLED

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

LINUX> select * from v$block_change_tracking; STATUS FILENAME BYTES -------- ----------------------------------------------- ---------- ENABLED +ASM_DISK_GROUP1/orcl/changetracking/ctf.343.9 11599872

Instructor's Notes

(If applicable)

The RMAN Incrementally Updated Backup feature is designed to limit the amount of redo you will need to apply during recovery operations – thus reduce recovery time.
The basic idea is to create image copies of your datafiles, then subsequently update the image copies with incremental backups, i.e. merge level 1 incremental backups into an existing image copy.

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

Instructor's Notes

(If applicable)

The script can be run daily. Each day this runs:

The RECOVER COPY command updates all datafile image copies (with the tag “IC1”) with the previous day’s level 1 incremental. If it does not find an image copy to update or a level 1 to apply, the RECOVER command simply issues messages and successfully terminates. Note that an identical user-defined tag must be used on the RECOVER COPY command and the BACKUP FOR RECOVER OF COPY WITH TAG clause.
The BACKUP command creates a new level 1 incremental. However, if a level 0 image copy does not exist (e.g. on the 1st run), the BACKUP command will create one.

Thus, at all times, you have available for recovery:

Image copy up to a maximum of 48 hours old
Level 1 incremental up to a maximum of 24 hours old
Online and archive logs to support point-in-time or complete recovery.

With this strategy, you will never have to apply more than 24 hours of redo (archive logs) to apply to perform a complete recovery.
Using a tag on the INCREMENTAL LEVEL 1 clause is optional, but I did not like the default tag RMAN supplied for the incrementals, so I supplied my own.
Refer to the article “Oracle10g - RMAN Incrementally Updated Backups” by Dave Anderson (find at www.skillbuilders.com) for a detailed discussion of this feature.

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

Instructor's Notes

(If applicable)

A new 10g RMAN command – SWITCH DATABASE TO COPY – provides a convenient technique for quickly switching to the latest image copies (i.e. making the latest image copies the current datafiles). So, in the event of failure of the primary datafiles, you can very quickly switch to the image copies – without the overhead of manually changing the control file or restoring (i.e. copying) the image copies. You’ll notice that after switching the image copies to the current datafiles, you need to recover the new datafiles (i.e. apply all changes made to the database since the image copy was created). If you use the incrementally update image copy technique demonstrated earlier in this lesson, your image copies will be quite current and the RECOVER operation will hopefully be quick (how long it takes is directly related to the amount of updates occurring on your database).
The SWITCH DATABASE command updates the control file(s) to point to the new datafiles, just like the ALTER DATABASE RENAME FILE statement. The SWITCH DATABASE command also updates the RMAN repository to reflect the change (i.e. it deletes the entries for the image copies since they are now the primary datafiles).

Notes for this slide continue on the next page…

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

RMAN> switch database to copy; datafile 1 switched to datafile copy "C:\ORACLE\10.2.0\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSTEM_28F datafile 2 switched to datafile copy "C:\ORACLE\10.2.0\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_UNDOTBS1_2 {etcetera} RMAN> recover database; Starting recover at 07-JUN-06 RMAN> alter database open; database opened Can also use to help migrate to ASM

Instructor's Notes

(If applicable)

This might be a somewhat temporary solution since your database will now be operating using image copies in a backup location as the primary datafiles. In the example shown above, the SWITCH DATABASE command switched the database to use the image copies located in the Flash Recovery Area. However, you can easily take another image copy while the database is online – into the correct location for your datafiles – and then later, when the database can be brought down, use another SWITCH DATABASE TO COPY command to reset the database to use the datafiles in the proper production location. Refer to the supplied demonstration file SWITCH_DATABASE_TO_COPY_DEMO.TXT for an example of using this command.
The SWITCH to COPY command also supports the DATAFILE and TABLESPACE level. Refer to the Oracle Database Backup and Recovery Reference 10g Release 2 for more information on the RMAN SWITCH command.
Finally, note that this command can be used as part of a procedure to migrate to Automatic Storage Management (ASM). Refer to the Oracle Database Backup and Recovery Advanced User’s Guide 10g Release 2 (see the section entitled “Disk-Based Migration of a Database to ASM”).

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

Instructor's Notes

(If applicable)

Another RMAN R2 feature (Enterprise Edition only) is support for encrypted backup sets (encrypting image copies is not supported).
Oracle supports three encryption techniques:

Transparent – In this mode an encryption key is stored in the Oracle Encryption Wallet feature, which must be previously configured. I believe this (Oracle Encryption Wallet) also supports the R2 feature transparent column encryption. A password for encryption and decryption is not required. This is the default.
Password – Password encryption requires that the DBA provide the same password on the backup and restore commands. Forgetting the password renders the backupset useless. This mode can be particularly useful when restoring on a remote system.
Dual Mode – With this technique, either the password or the Wallet can be used to decrypt the backup.

Encryption of data requires CPU resources. I have not yet tested the overhead (June 2006).

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

RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON; R2 RMAN> SET ENCRYPTION ON IDENTIFIED BY xyz ONLY; Advanced Security feature required; extra cost option

Instructor's Notes

(If applicable)

Oracle 10g introduces a DROP DATABASE command. It is offered as both a SQL statement and an RMAN command. The RMAN command offers more functionality than does the SQL statement. For example, the RMAN command offers the ability to optionally drop the archivelogs and backup pieces as shown in this example:
RMAN> DROP DATABASE INCLUDING BACKUPS;
Dropping the database drops the controlfiles, datafiles, logfiles and, if used, the SPFILE. If the database is connected to an RMAN catalog when the database is dropped, RMAN also unregisters the database from the catalog.
The database must not be open; it must be mounted in exclusive mode.
References:

Metalink Note:251412.1
Oracle® Database Recovery Manager Reference 10g Release 2 (10.2)

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

Instructor's Notes

(If applicable)

Oracle9i provided the CATALOG command to add information about datafile copies, control file copies, or archived logs to the target database control file and recovery catalog (if available). Oracle10g extends the capability of the CATALOG command to support:

BACKUPPIECE – Use this option to catalog (in the control file and recovery catalog, if applicable) a backup piece that has been copied or moved to a new location.
RECOVERY AREA or DB_RECOVERY_FILE_DEST – Use this option to catalog all backup sets, image copies and archive logs in the flash recovery area. A NOPROMPT option is available to eliminate prompting for each item. RECOVERY AREA and DB_RECOVERY_FILE_DEST are equivalent clauses.
START WITH ‘location’ – Use this option to catalog all files in the specified location.

The example above demonstrates cataloging an image copy that was copied (using an operating system command) to a secondary location. If desired, after cataloging, use the LIST command to verify the cataloging of the image copy, backup piece or control file copy.
Refer to the Oracle® Database Recovery Manager Reference 10g Release 2 for more information and examples of the RMAN CATALOG command.

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

RMAN> catalog start with 'c:\oracle_backups'; searching for all files that match the pattern c:\oracle_backups List of Files Unknown to the Database ===================================== File Name: C:\oracle_backups\O1_MF_SYSTEM_27JQ9QXC_.DBF Do you really want to catalog the above files (YES/NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: C:\oracle_backups\O1_MF_SYSTEM_27JQ9QXC_.DBF

Instructor's Notes

(If applicable)

The BACKUP DURATION parameter will do one of two things for you:

If MINIMIZE LOAD is specified, RMAN will reduce resource consumption so that the job requires the amount of time specified. This is like the RATE parameter, but perhaps more intuitive.
IF MINIMIZE LOAD is not specified, the time specified is the maximum time the BACKUP can execute. RMAN will kill the job when it reaches the specified time limit.

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

RMAN> backup duration 0:30 minimize load tablespace users; Starting backup at 03-AUG-04 OPTIONAL: “minimize load” says “take the entire 30 minutes” Maximum time allotted is 30 minutes

Instructor's Notes

(If applicable)

Oracle10g adds 18 new backup and recovery-related views. Refer to the Oracle Database Reference manual (Release 2) for details on these views:
V$BACKUP_ARCHIVELOG_DETAILS
V$BACKUP_ARCHIVELOG_SUMMARY
V$BACKUP_CONTROLFILE_DETAILS
V$BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_COPY_DETAILS
V$BACKUP_COPY_SUMMARY
V$BACKUP_DATAFILE_DETAILS
V$BACKUP_DATAFILE_SUMMARY
V$BACKUP_PIECE_DETAILS
V$RMAN_OUTPUT
V$RMAN_STATUS
V$BACKUP_SET_DETAILS
V$BACKUP_SET_SUMMARY
V$BACKUP_SPFILE_DETAILS
V$BACKUP_SPFILE_SUMMARY
V$RMAN_BACKUP_JOB_DETAILS
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_TYPE
V$FLASH_RECOVERY_AREA_USAGE
V$RMAN_ENCRYPTION_ALGORITHMS

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

R2

Instructor's Notes

(If applicable)

Here is a brief description of additional Oracle10g RMAN enhancements:

The CONFIGURE command can be used to configure an archivelog deletion policy. There are 2 main options: “NONE”, which means RMAN can delete archive logs from the flash recovery area only after they’ve been backed up and are obsolete (according to the configured retention policy). “APPLIED ON STANDBY” means the archive logs can be deleted if they have been applied to a standby database.
A new RESTORE DATABASE PREVIEW command is available which identifies which backups RMAN would use to perform the restore.
RMAN will automatically failover to another backup piece (even from an older backup if necessary) if corruption is encountered. It will continue to failover until a non-corrupt backup is found or all possibilities are exhausted.

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

Instructor's Notes

(If applicable)

Additional RMAN enhancements, continued:

RMAN now has the ability to try another channel if a channel fails during backup.
The COPY command is deprecated (will eventually be desupported) because the BACKUP command can be used to create image copies. For example:

RMAN> backup as copy tablespace users;

The CONFIGURE DEVICE TYPE command now supports configuring for normal backupset, compressed backup or image copy. (Note that the image copy option only works for DEVICE TYPE DISK..) For example:

RMAN> show device type ;
RMAN configuration parameters are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
RMAN> configure device type disk backup type to compressed backupset;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored

To alleviate clutter in the SYSTEM tablespace, the RMAN catalog is now by default created in the new SYSAUX tablespace.

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

Instructor's Notes

(If applicable)

Oracle10g Release 2 also provides these RMAN enhancements:

After a whole (full) database restore and recovery, and at database open, Oracle will automatically created missing tempfiles. The original size, location, autoextend and maxsize attributes are used to recreate the tempfile. The tempfile must be locally managed (as opposed to dictionary managed). Refer to Chapter 6 of the Oracle® Database Backup and Recovery Basics 10g Release 2 manual for more information on this feature.
If certain conditions are met, Release 2 of 10g RMAN will backup only in-use blocks (previous releases of RMAN backed up any block that ever contained data – even if it was not currently in-use (this was called “NULL compression”)). Refer to the BACKUP command in the Oracle 10g Release 2 Recovery Manager Reference for more information. The conditions include:

Full or Level 0 backup
No guaranteed restore points in effect
Locally managed datafile
Disk-based backupset

RAC backups no longer require manual channel allocation for each node – dynamic channel allocation is now supported. For more information, refer to Chapter 1 of the Oracle 10g R2 New Features Guide and Chapter 8 of the Oracle 10g R2 Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide.

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

R2

Instructor's Notes

(If applicable)

Oracle 10g R2 provides the ability to create an incremental backup as of some SCN. Only changes made at or after that SCN are backed up. This can be useful for updating a Standby Database with changes made to the primary database since the last synchronization with the standby. For more information refer to the Oracle 10g R2 Backup and Recovery Advanced User's Guide and the BACKUP INCREMENTAL FROM SCN [scn] DATABASE command.
Oracle Enterprise Manager has been enhanced to support:
Compression during cloning operations, reducing data transmitted over a network.
RMAN scripts created by OEM can be edited. Refer to Chapter 9 of the Oracle® Database 2 Day DBA 10g Release 2 manual for more information.
Backup job notification

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

R2

Instructor's Notes

(If applicable)

User-managed hot backups are made simpler with the new command ALTER DATABASE BEGIN BACKUP. This places all online and available datafiles in hot backup mode. This is in contrast to the ALTER TABLESPACE BEGIN BACKUP command, which needs to be repeated for each tablespace to be backed up.

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

SQL> ALTER DATABASE BEGIN BACKUP; SQL> ALTER DATABASE END BACKUP;

Instructor's Notes

(If applicable)

Oracle 10g Release 2 includes Oracle Secure Backup, an integrated tape management software solution. Oracle Secure Backup supports:

RMAN. As an RMAN media manager, it enables RMAN to backup directly to tape devices.
Oracle Secure Backup Express Edition is positioned as a replacement for Legato Single Server Version, which was shipped at no cost with Release 1. LSSV supports just one tape device (among several other limitations).
Encryption of backups.
Any number of output devices, tape or otherwise, at a cost of $3000 per device.
Backup of non-database files. For example, you can backup the Oracle software in the Oracle Home.
Networked environments. A single installation of Oracle Secure Backup can be used to

For more information, refer to the Oracle® Database Readme 10g Release 2 (10.2), section 10 Media Management Software. The Readme document can be accessed from the welcome.html message shipped with Oracle 10g Release 2.

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

R2

Instructor's Notes

(If applicable)

With the new features Oracle10g introduces, I think many DBAs will now start to view RMAN as a viable tool for backup and recovery of production databases.
While I have tried to highlight the “most important” RMAN enhancements in this lesson, there are more and undoubtedly you will find enhancements not covered here that are important to you. So, refer to the section “What’s New in Backup and Recovery” in the Backup and Recovery Advanced User’s Guide for more information.
To summarize what you've learned in this lesson:

Easier management of files with flash recovery area
Flashback Database
Restore points and flashback “TO BEFORE RESETLOGS”
Restore points are a convenient technique for flashback database

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

Instructor's Notes

(If applicable)

To summarize what you've learned in this lesson (continued):

Transparent recovery through RESETLOGS
Real compression for RMAN backup pieces
Much faster incremental backups with Change Tracking
Faster recovery with incrementally updated image copies
Fast recovery with the SWITCH DATABASE Command

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

Instructor's Notes

(If applicable)

To summarize what you've learned in this lesson (continued):

Secure backups with encrypted RMAN backup sets
Integrated media manager for tape backups
“Oracle Secure Backup”
Enterprise Manager enhanced to support RMAN
Lots of minor features!

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