New backup and recovery features in Oracle Database 11g save time and data.

A sound database backup and recovery plan is crucial to protecting data in the event of media loss and physical corruption on production storage systems. Oracle backup and recovery technologies enable data to be recovered from media loss and corruption, as well as from logical errors at the row, table, and database level. This column discusses some of the new backup and recovery features in Oracle Database 11g that improve the manageability of Oracle Database backup and recovery. The column also presents sample questions of the type you may encounter when taking the Oracle Database 11g: New Features for Administrators exam, which enables Oracle Certified Professionals (OCPs) certified on Oracle Database 10g to upgrade their certifications to Oracle Database 11g.

Data Recovery Advisor

Successfully diagnosing a data failure and devising an optimal strategy for repair is critical to maintaining high availability in the enterprise. Data recovery advisor is an Oracle Database tool that automatically diagnoses corruption or loss of persistent data on a disk and gathers data failure information. It then determines the best repair option and checks the feasibility of implementing the repair in your environment.

Figure 1 shows the automatic diagnostic workflow in Oracle Database 11g. Health monitor automatically diagnoses failures and logs them into the automatic diagnostic repository. Data recovery advisor consolidates these failures and attaches a severity to them, such as critical or high. If a DBA requests repair advice, data recovery advisor maps any failures to automatic and manual repair options, checks basic feasibility, and presents the repair advice. The DBA can choose to manually execute a repair or ask data recovery advisor to perform it. In addition to the automatic, reactive checks of health monitor and data recovery advisor, Oracle recommends that DBAs use the VALIDATE command with the BACKUP and RESTORE commands to proactively check for corrupt blocks and missing files and also to confirm that backups can be restored.

Figure 1: Automatic diagnostic workflow in Oracle Database 11g

Which of the following failures can data recovery advisor diagnose and repair?

A. Physical corruption such as block checksum failures and invalid block header field values
B. I/O failures such as hardware errors and operating system driver failures
C. Inconsistencies such as a datafile that is older than other database files
D. Failures on standby databases

The correct answers are A, B, and C. Data recovery advisor can detect and repair physical corruptions related to blocks, I/O failures, and also any inconsistencies in datafiles. Option D is incorrect because data recovery advisor cannot diagnose and repair failures on a standby database; it does support failover to a standby database as a repair option, however.

Parallel Backup and Restore for Very Large Files

In earlier versions, the smallest piece of an Oracle Recovery Manager (Oracle RMAN) backup was an entire file. Because Oracle datafiles can be up to 128TB in size, it is not feasible to back up a very large file. In Oracle Database 11g, however, Oracle RMAN can break up a single large file into sections and back up and restore them independently. The SECTION SIZE parameter in the BACKUP command tells Oracle RMAN to create a backup set in which each backup piece contains blocks from one file section, enabling the backup of large files to be parallelized across multiple channels. Restoring a multisection backup in parallel is automatic.

In your database, the JAN_08_SALES tablespace contains a single 600MB datafile. Three system backup to tape (SBT) channels are configured, with the parallelism setting for the SBT device set to 3. The MAXSETSIZE is set to 600MB.

You issue the following Oracle RMAN command:

RMAN> BACKUP
SECTION SIZE 200M
TABLESPACE jan_08_sales; 

What is the outcome?

A. The command creates three backup sets, each containing one 200MB file section.
B. The command creates one backup set containing three 200MB backup pieces.
C. The command creates one backup set comprising one backup piece that contains three separate 200MB file sections.
D. The command generates an error, because MAXSETSIZE is larger than SECTION SIZE.
E. The command generates an error, because MAXPIECESIZE is not specified.

The correct answer is B. The command results in an Oracle RMAN backup set in which each backup piece contains a file section, which is a contiguous range of blocks in a datafile. Answers A and C are incorrect because a multisection backup set contains multiple backup pieces. Answer D is incorrect because MAXSETSIZE must be larger than SECTION SIZE. Answer E is incorrect because MAXPIECESIZE must not be specified when SECTION SIZE is specified, so no error is generated.

Oracle Flashback Transaction

Flashback transaction enables the changes made by a transaction to be undone, optionally undoing changes made by dependent transactions. This recovery option uses undo data to create and execute corresponding compensating transactions that make the affected database revert to its original state. The TRANSACTION_BACKOUT procedure analyzes transactional dependencies, performs data manipulation language (DML) operations, and generates reports. TRANSACTION_BACKOUT does not commit the DML operations it performs as part of the transaction backout, but it holds all the required locks on rows and tables, preventing other dependencies from entering the system. To make the transaction backout permanent, you must explicitly commit the transaction.

SCOTT executes the following commands on the REGIONS table in his schema:

 
SQL>INSERT INTO regions(region_id, region_name) VALUES (1, 'North'); 
SQL>COMMIT;
SQL>UPDATE regions SET region_name = 'South' WHERE region_id = 1; 
SQL>COMMIT;
SQL>DELETE FROM regions 
WHERE region_id=1;
SQL>COMMIT;

The UPDATE and DELETE transactions were a mistake, and you are asked to restore the original row details: "1, 'North'." You use the flashback transaction feature for this operation, and Figure 2 shows the transaction IDs available for flashback in Oracle Enterprise Manager. Which trans-action ID and which change recovery option must you select to complete the operation?

Figure 2: Using Oracle Enterprise Manager Database Control for flashback transaction

A. The first transaction ID with the NONCONFLICT ONLY recovery option
B. The first transaction ID with the NOCASCADE FORCE recovery option
C. The second transaction ID with the CASCADE recovery option
D. The third transaction ID with the NOCASCADE recovery option

The correct answer is C. It backs out specified transactions and all dependent transactions in a post-order fashion: children are backed out before the parents are backed out. Answer A is incorrect because the NONCONFLICT ONLY recovery option backs out changes to nonconflicting rows of the specified transactions. Answer B is incorrect because the NOCASCADE FORCE recovery option backs out specified transactions, ignoring dependent transactions. Answer D is incorrect because the NOCASCADE recovery option backs out specified transactions, which are expected to have no dependent transactions. (It would restore the row as "1, 'South'.")

Conclusion

This column focused on some of the new backup and recovery features introduced in Oracle Database 11g:

• By providing a centralized tool for automated data repair, data recovery advisor can reduce the mean time to recovery (MTTR) after a failure. Data recovery advisor improves the manageability and reliability of an Oracle Database instance.
• Backing up a file in separate sections can both improve performance and enable large-file backups to be restarted. Using the SECTION SIZE parameter in the BACKUP command, you can parallelize the backup-and-recovery operation across multiple channels.
• The flashback transaction feature increases availability during logical recovery, by easily and quickly backing out a specific transaction or a set of transactions, and their dependent transactions, with one command while the database remains online.

Sushma Jagannath (ocpexam_ww@oracle.com) is a certification exam development manager at Oracle. She has been with the company since 2000.