Recovery during Open Database Backup Failure

Recovering a database from a failure during an open database backup requires careful execution of certain steps to ensure data integrity and system availability. Here’s how you can approach this situation in Oracle 12c:

1. Assess the Extent of the Failure:
   • Determine the nature and scope of the failure. Identify whether it's a physical or logical corruption, and which files are affected.
2. Ensure Database is in ARCHIVELOG Mode:
   • Confirm that the database is running in ARCHIVELOG mode, as open database backups require archiving of the redo logs to protect against data loss.
3. Terminate Unsuccessful Backup Processes:
   • If the backup process is still running but has encountered errors, terminate it properly to avoid further complications.
4. Archive Current Redo Logs (if possible):
   • Manually switch the online redo logs and archive the current logs to ensure you have the most recent transactions.
   
   

   ALTER SYSTEM ARCHIVE LOG CURRENT;
   

5. Perform Necessary Restorations:
   • If data files have been damaged or lost, restore them from the most recent backup. If a control file is affected, restore it from the backup as well.
6. Apply Archive Logs:
   • Once the necessary files are restored, recover the database by applying the archived redo logs. You can do this by issuing the following command:
   
   

   RECOVER DATABASE USING BACKUP CONTROLFILE;
   

7. Open the Database with RESETLOGS:
   • After all the archive logs have been applied, and the database is consistent, open the database with the RESETLOGS option. This creates a new incarnation of the database, resetting the log sequence number to 1.
   
   

   ALTER DATABASE OPEN RESETLOGS;
   

8. Check for Logical Consistency:
   • Perform a thorough check of the database for logical consistency. You can use tools like DBVERIFY or ANALYZE statements to validate the structure and integrity of the database.
9. Backup the Database:
   • After recovery and consistency checks are completed, take a full backup of the database. This serves as a new baseline for future recoveries and should be part of any good backup strategy.

During this recovery process, you should carefully monitor the alert log and look for any errors or warnings that may indicate underlying issues that need to be addressed. Remember, the specific commands and procedures may vary depending on the exact configuration of your Oracle environment and the nature of the backup failure. Always refer to Oracle's documentation and your organization's recovery procedures when dealing with database recovery scenarios.

I am working as an Oracle DBA using Oracle 12c and my 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 the process is not successful. 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.

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.

SQL> 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-2020
         6 NOT ACTIVE            0
         7 NOT ACTIVE            0 
7 rows selected.
SQL>

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:

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

SQL> 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.
SQL> 

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.

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