Questions on Backup and Recovery Enhancements

Reference: http://www.iselfschooling.com/mcd12_ora10g/Oracle10g_Answers.htm

Q: What is the Flash Recovery Area?
A: It is a unified storage location for all recovery-related files and activities in an Oracle Database. It includes Control File, Archived Log Files, Flashback Logs, Control File Autobackups, Data Files, and RMAN files.

Q: How do you define a Flash Recovery Area?
A: To define a Flash Recovery Area set the following Oracle Initialization Parameters.
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 100G;
SQL> ALTER SYSTEM SET db_recovery_file_dest = ‘/u10/oradata/school’;

Q: How do you use the V$RECOVERY_FILE_DEST view to display information regarding the flash recovery area?
A:
SQL> SELECT name, space_limit, space_used,
space_reclaimable, number_of_files
FROM v$recovery_file_dest;

Q: How can you display warning messages?
A:
SQL> SELECT object_type, message_type,
message_level, reason, suggested_action
FROM dba_outstanding_alerts;

Q: How do you backup the Flash Recovery Area?
A:
RMAN> BACKUP RECOVERY FILES;
The files on disk that have not previously been backed up will be backed up. They are full and incremental backup sets, control file auto-backups, archive logs, and datafile copies.

Q: How to use the best practice to use Oracle Managed File (OMF) to let Oracle database to create and manage the underlying operating system files of a database?
A:
SQL> ALTER SYSTEM SET
db_create_file_dest = ‘/u03/oradata/school’;
SQL> ALTER SYSTEM SET
db_create_online_dest_1 = ‘/u04/oradata/school’;


Q: How to enable Fast Incremental Backup to backup only those data blocks that have changed?
A:
SQL> ALTER DATABASE enable BLOCK CHANGE TRACKING;

Q: How do you monitor block change tracking?
A:
SQL> SELECT filename, status, bytes
FROM v$block_change_tracking;
It shows where the block change-tracking file is located, the status of it and the size.

Q: How do you use the V$BACKUP_DATAFILE view to display how effective the block change tracking is in minimizing the incremental backup I/O?
A:
SQL> SELECT file#, AVG(datafile_blocks), AVG(blocks_read),
AVG (blocks_read/datafile_blocks), AVG(blocks)
FROM v$backup_datafile
WHERE used_change_tracking = ‘YES’ AND incremental_level > 0
GROUP BY file#;
If the AVG (blocks_read/datafile_blocks) column is high then you may have to decrease the time between the incremental backups.

Q: How do you backup the entire database?
A:
RMAN> BACKUP DATABASE;

Q: How do you backup an individual tablespaces?
A:
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> BACKUP TABLESPACE system;

Q: How do you backup datafiles and control files?
A:
RMAN> BACKUP DATAFILE 3;
RMAN> BACKUP CURRENT CONTROLFILE;

Q: Use a fast recovery without restoring all backups from their backup location to the location specified in the controlfile.
A:
RMAN> SWITCH DATABASE TO COPY;
RMAN will adjust the control file so that the data files point to the backup file location and then starts recovery.

Q: How can you begin and end backup on the database level?
A:
SQL> ALTER DATABASE BEGIN BACKUP;
Copy all the datafiles…
SQL> ALTER DATABASE END BACKUP;

Q: How do you set the flash recovery area?
A:
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 100G;
SQL> ALTER SYSTEM SET db_recovery_file_dest = ‘/u10/oradata/school’;

Q: How do you gather information regarding the flash recovery area?
A:
SQL> SELECT name, space_limit, space_used,
space_reclaimable, number_of_files
FROM v$recovery_file_dest;