Backup Options   «Prev  Next»
Lesson 3Recovery during an open database backup failure
ObjectiveDescribe how to recover during an open database backup failure.

Scenario


Your training DBA is performing an open database backup. The application system crashes and brings down the database in the middle of a tablespace backup. After the media failure is corrected, he tries to start the database but fails. He realizes that the database fails to open because some datafiles are still in "hot backup" mode with the header "frozen" and cannot be synchronized with the database. He knows he should take the affected tablespace offline in order to open the database and issue the ALTER DATABASE ... END BACKUP; statement to end the backup.
However, he is faced with a dilemma: the command ALTER TABLESPACE <tablespace_name> OFFLINE; cannot be used until the database is open and the database will not open unless the affected datafiles are synchronized or offline.
In addition, the statement ALTER DATABASE ... END BACKUP; cannot be performed on an offline tablespace. In a panic, he comes to you. What can you do to solve this problem?
A backup file is not valid before the operating system finishes the backup process.
Such a backup copy cannot be used to recover the database. You must perform another backup after your database returns to normal.

Solution

As an experienced DBA, you know you have two choices: 1) Perform a complete database recovery; or 2) Find some other way to synchronize the files. If you decide on the first choice, recovering an open database, initially closed is the appropriate method to use for this scenario. Because the first method is time-consuming, you decide to try the second option. Now you need to find out if the affected file needs a recovery or was simply left in "hot backup" mode. You query the data dictionary view, V$BACKUP.
View the code below and compare your result.
SVRMGR> SELECT * FROM V$BACKUP;
FILE#      STATUS             CHANGE#    TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE                  0 
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 ACTIVE                5621029 18-JAN-00
         6 NOT ACTIVE                  0
         7 NOT ACTIVE                  0 
7 rows selected.
SVRMGR>

The result shows that datafile #5 is in the "hot backup" mode. Now, all you need to do is unfreeze the header of datafile #5 with the following statement:

SVRMGR> ALTER DATABASE DATAFILE 5 END BACKUP;
Statement processed.
SVRMGR>

Again, you check to see if the problem with datafile #5 is corrected by querying the view V$BACKUP:

View the code below.
SVRMGR> SELECT * FROM V$BACKUP;
FILE#      STATUS             CHANGE#    TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE                  0
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0
         7 NOT ACTIVE                  0
7 rows selected.
SVRMGR> 

The header for datafile #5 is no longer "frozen". The datafile will be synchronized with the database and you can now open the database.
The next lesson demonstrates how to clear corrupted online redo logs.

Infrequent Recovery Situations - Quiz

Click the Quiz link below to review your understanding of performing recovery in infrequent situations.
Infrequent Recovery Situations - Quiz