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. <br