Performing Backup and Recovery Flashcards

A database running in ARCHIVELOG mode experiences a media failure, losing a single data file belonging to a non-system, non-undo tablespace. The DBA needs to perform a complete recovery of the affected tablespace while the rest of the database remains online. Which sequence of RMAN commands is the correct way to accomplish this?

Answer: ALTER TABLESPACE users OFFLINE; RESTORE TABLESPACE users; RECOVER TABLESPACE users; ALTER TABLESPACE users ONLINE;

To recover a non-system tablespace while the database is open, the correct procedure is to first take the tablespace offline. Then, restore the affected data files for that tablespace and recover them using archived redo logs. Finally, bring the tablespace back online. Shutting down the entire database is unnecessary for a non-system tablespace recovery.

What is the primary advantage of operating an Oracle database in ARCHIVELOG mode?

Answer: It enables online ('hot') backups and supports point-in-time recovery.

The main purpose of ARCHIVELOG mode is to save filled online redo log files before they are overwritten. This archive of all transactions enables two critical capabilities: performing backups while the database is open and running (online/hot backups) and recovering the database to any specific point in time since the last valid backup, thus preventing data loss from media failure.

A DBA is configuring a database and wants to centralize the storage of backup and recovery-related files, allowing Oracle to manage their lifecycle automatically. Which of the following file types would be stored in the Fast Recovery Area (FRA) by default?

Answer: Archived redo logs, RMAN backupsets, and flashback logs.

The Fast Recovery Area (FRA) is a unified storage location for recovery-related files. Oracle automatically manages the files within it. Common files stored in the FRA include RMAN backups (backupsets and image copies), archived redo logs, control file autobackups, and flashback logs.

A backup strategy involves an RMAN incremental level 0 backup on Sunday. On Monday and Tuesday, RMAN incremental level 1 differential backups are performed. If a media failure occurs on Wednesday morning, which backups are essential to restore and recover the database to its most recent state?

Answer: The level 0 backup from Sunday, the level 1 backup from Monday, and the level 1 backup from Tuesday.

A differential level 1 backup includes all blocks changed since the most recent incremental backup at either level 1 or level 0. To recover using this strategy, you must apply the base level 0 backup, followed by every subsequent level 1 differential backup in sequence. Therefore, the Sunday level 0, Monday level 1, and Tuesday level 1 backups are all required.

A junior developer accidentally executes a `DROP TABLE` command on a critical application table. The DBA needs to recover the table as quickly as possible with minimal impact on other database operations. Which Oracle feature is specifically designed for this scenario?

Answer: Flashback Drop.

Flashback Drop is the feature designed to reverse the effects of a `DROP TABLE` statement. It retrieves the table and its dependent objects from the Recycle Bin. Flashback Table is used to revert a table's data to a previous point in time but cannot be used if the table has been dropped. RMAN PITR and Flashback Database are much heavier operations that affect more than just the single dropped table.

After a database failure, a DBA uses the Data Recovery Advisor (DRA) through RMAN. What is the primary function of the `ADVISE FAILURE` command?

Answer: It analyzes detected failures and generates one or more recommended repair scripts.

After using `LIST FAILURE` to see detected problems, the `ADVISE FAILURE` command is used to analyze those failures. The Data Recovery Advisor then determines the optimal repair strategy and presents it as a script of RMAN commands that the DBA can review and then execute using the `REPAIR FAILURE` command.