CHAPTER 16User-Managed Backup and Recovery

Introduction to User Managed Backup and Recovery

• Backup and recover is one of the most critical skills a DBA must be proficient with.

• Oracle provides two general types of B&R tools:• Recovery Manager (RMAN)• User-managed

• RMAN is Oracle’s flagship B&R tool; automates most of the B&R tasks.

• However, understanding how user managed backups work lays the foundation for understanding RMAN and B&R internals.

• User managed backups require that the DBA manually perform tasks for B&R.

• Focus of this chapter is user-managed B&R

Why Learn about User-Managed B&R?

• You still find shops using user-managed B&R techniques. Therefore, you’re required to be knowledgeable about this technology.

• Manually executing a user-managed backup, restore, and recovery solidifies your knowledge of the Oracle B&R architecture. This helps immensely when you’re troubleshooting issues with any B&R tool and lays the foundation of core knowledge for key Oracle tools such as RMAN and Data Guard.

• You’ll more fully appreciate RMAN and the value of its features.

• Nightmarish database-recovery stories recounted by the old DBAs will now make sense.

Making a Cold Backup of a Noarchivelog-Mode Database• Determine where to copy the backup files and how much

space is required.• Determine the locations and names of the database files

to copy.• Shut down the database with the IMMEDIATE,

TRANSACTIONAL, or NORMAL clause.• Copy the files (identified in step 2) to the backup location

(determined in step 1).• Restart your database.

Restoring a Cold Backup in Noarchivelog Mode with Online-Redo Logs

• Shut down the instance.• Copy the datafiles, online-redo logs, temporary files, and

control files back from the backup.• Start up your database.

Restoring a Cold Backup in Noarchivelog Mode Without Online-Redo Logs

• Shut down the instance.• Copy the control files and datafiles back from the backup.• Start up the database in mount mode.• Open the database with the OPEN RESETLOGS clause.

Making a Cold Backup of an Archivelog-Mode Database1. Determine where to copy the backup files and how

much space is required.

2. Determine the locations and names of the database files to copy.

3. Shut down the database with IMMEDIATE, TRANSACTIONAL, or NORMAL .

4. Copy the files (identified in step 2) to the backup location (determined in step 1).

5. Restart your database.

Steps for Hot Backups1. Ensure that the database is in archivelog mode.

2. Determine where to copy the backup files.

3. Determine which files need to be backed up.

4. Note the maximum sequence number of the online-redo logs.

5. Alter the database/tablespace into backup mode.

6. Copy the datafiles with an OS utility.

7. Alter the database/tablespace out of backup mode.

8. Archive the current online-redo log, and note the maximum sequence number of the online-redo logs.

9. Back up the control file.

10. Back up any archive-redo logs generated during the backup.

Understanding the Split-Block Issue• When performing a Hot Backup, before datafiles can be

copied with an OS utility, the tablespace that contains the datafiles to be copied must be placed in backup mode.

• The issue is that Oracle is actively writing to datafiles while the datafiles are being copied.

• This means the datafiles are most likely corrupted during the copy.

• This is know as the fractured block or split block issue.

Understanding the Split-Block Issue

Oracle Normal Mode of Writing Redo

Oracle Writing Full Blocks to Redo Stream when Datafile in Backup Mode

Restore and Recovery from Datafiles Backed up via Hot Backups

Understanding the Need for Redo Generated During Backup

Understanding that Datafiles Are Updated

• Common misconception is that datafiles are not written to by database writer during a user-managed Hot Backup.

• Datafiles are written to.• Easy to prove.

Restoring and Recovering with the Database Offline1. Place your database in mount mode

2. Restore the datafile from the backup

3. Issue the appropriate RESTORE statement

4. Alter your database open

Restoring and Recovering with a Database Online1. Take the datafile offline

2. Restore the datafile from a backup

3. Recover the datafile

4. Alter the datafile online

Restoring One Damaged Control File When Multiplexed1. Shutdown the database

2. Copy an existing good control file to the location of the bad one

3. Startup your database

Restoring When All Control Files Are Damaged

1. Shut down the database.

2. Restore a control file from the backup.

3. Restore all datafiles from the backup.

4. Start the database in mount mode, and initiate database recovery using the RECOVER DATABASE USING BACKUP CONTROLFILE clause.

5. For a complete recovery, manually apply the redo contained in the online-redo logs.

6. Open the database with the RESETLOGS clause.

Reasons for Performing an Incomplete Recovery of an Archivelog-Mode Database

• You attempt to perform a complete recovery but are missing the required archive-redo logs or unarchived online-redo log information.

• You want to restore the database back to a point in time in the past just prior to an erroneous user error (such as deleted data, dropped table, and so on).

• You have a testing environment in which you have a baseline copy of the database. After the testing is finished, you want to reset the database back to baseline for another round of new testing.

Complete vs. Incomplete Recovery• Complete recovery means that you can recover all

committed transactions in the database prior to the media failure.

• Incomplete recovery means that cannot recover all committed transactions in the database.

• Complete recovery can be performed on a single datafile (if the media failure is isolated to a single datafile).

• Incomplete recovery must always be performed on all datafiles (that are online) in the database when the recovery process is started.

Types of Incomplete Recoveries• Cancel based• SCN based• Time based

• Examples:

SQL> recover database until cancel;

SQL> recover database until change 12345;

SQL> recover database until time '2010-10-21:02:00:00';

Steps for Incomplete Recovery

1. Shut down the database.

2. Restore all the datafiles from the backup.

3. Start the database in mount mode.

4. Apply redo (roll forward) to the desired point, and halt the recovery process (use cancel-, SCN-, or time-based recovery).

5. Open the database with the RESETLOGS clause.

Summary• User managed backups lay the foundation for understand

backup and recovery internals.• DBAs should be aware of how to backup and restore

using cold and hot backups.• Once you understand user managed backups you will be

in a much better position to learn and implement backups using RMAN.