Recovering with a Backup Control File

A control file is one of the most critical structures in an Oracle database. It records the physical layout of the database: datafiles, redo log files, tablespaces, and the current checkpoint information. When a control file is lost, corrupted, or missing information (for example, about a dropped tablespace that you must restore), recovery can only succeed if you have a usable backup of that control file.

This lesson shows how to recover a database using a backup control file, focusing on a dropped tablespace scenario. The techniques apply to modern Oracle releases (such as 11g through 23c), even though the sample environment uses older tools and paths. In current environments, you would typically use Recovery Manager (RMAN) to restore control files and datafiles, but the underlying concepts are the same.

When you use a backup control file

A backup control file is needed in situations such as:

• All current control files are lost or corrupted.
  You must restore a control file from backup (for example, with RESTORE CONTROLFILE FROM AUTOBACKUP in RMAN) and then recover the database.
• The current control file no longer knows about objects you need to restore.
  For example, a tablespace was dropped and the control file no longer contains its datafile entries, but you have a database backup from before the drop. A backup control file from that time still contains the missing metadata.

In both cases, you recover by applying redo to a backup control file, using the USING BACKUP CONTROLFILE clause. This almost always results in an incomplete recovery and requires opening the database with RESETLOGS.

Scenario: recovering a dropped STUDENT tablespace

DROP TABLESPACE STUDENT INCLUDING CONTENTS;

High-level recovery plan

Before diving into the commands, it helps to summarize the plan:

1. Protect the database by preventing further user access.
2. Gather information you may need during recovery: current log sequence, datafile names, and the exact time the tablespace was dropped.
3. Shut down the database and back up the current (damaged or incomplete) control file so you can return to this state if necessary.
4. Restore datafiles and a backup control file taken before the tablespace was dropped.
5. Mount the database and perform an incomplete recovery using the backup control file.
6. Open the database with RESETLOGS and verify that the tablespace and its objects have been recovered.
7. Perform a fresh whole database backup after the RESETLOGS.

Step 1: Restrict user access

ALTER SYSTEM ENABLE RESTRICTED SESSION;

Step 2: Identify the current log sequence

Query the V$LOG view to identify the current log sequence number:

SQL> SELECT * FROM V$LOG;

GROUP#  SEQUENCE#  BYTES     MEMBERS  ARC  STATUS    FIRST_TIME
------  ---------  -------   -------  ---  --------  -------------------
     1         74  1048576        1   NO   INACTIVE  21-FEB-00 14:04:04
     2         75  1048576        1   NO   CURRENT   21-FEB-00 14:09:00
...

From this output you note that the current log sequence number is 75. You will use this information during recovery to verify that all required redo has been applied.

Step 3: Locate the datafiles for the dropped tablespace

Next, you need the exact filenames and paths of the datafiles that belonged to the STUDENT tablespace:

SQL> SELECT tablespace_name, file_name
  2  FROM dba_data_files
  3  WHERE tablespace_name = 'STUDENT';

TABLESPACE_NAME  FILE_NAME
---------------  ----------------------------------------
STUDENT          C:\ORANT\DATABASE\STUDENT.ORA

1 row selected.

This tells you which datafile must be restored from backup along with the control file.

Step 4: Confirm when the tablespace was dropped

Use the database alert log to find the exact time when the STUDENT tablespace was dropped:

...
Mon Feb 21 14:04:13 2000
Drop tablespace student including contents
...

This timestamp will be used in the UNTIL TIME recovery clause. In this example, you plan to recover to 2000-02-21:14:04:00, just before the drop.

Step 5: Prepare for incomplete recovery

Performing recovery with a backup control file

A backup control file is typically created with a command such as:

ALTER DATABASE BACKUP CONTROLFILE TO 'file_name';
-- or
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

When you recover using a backup control file, Oracle does not trust the redo log history in that file, so you must always use the USING BACKUP CONTROLFILE clause and then open the database with RESETLOGS.

Detailed simulation of backup control file recovery

1. Connect as SYSDBA and confirm that the STUDENT tablespace has been dropped:

   SQL> SELECT tablespace_name FROM dba_tablespaces;

2. Shut down the database cleanly:

   SQL> SHUTDOWN IMMEDIATE;

3. At the operating system level, back up the current control file. In the example environment:

   SQL> HOST
   
   C:\orant> COPY C:\ORANT\DATABASE\CTL1ORCL.ORA C:\ORANT\DATBASEX
           1 file(s) copied.

4. Restore the datafiles and control file from the most recent backup taken before the drop (in this example, a backup from Feb. 13, 2000). A batch file such as DO_RESTORE_DCFILE.BAT might perform this for you:

   C:\orant> DO_RESTORE_DCFILE
   C:\orant\databaseX\CTL1ORCL.ORA
   C:\orant\databaseX\LOG1ORCL.ORA
   C:\orant\databaseX\LOG2ORCL.ORA
   C:\orant\databaseX\LOG3ORCL.ORA
   C:\orant\databaseX\LOG4ORCL.ORA
   C:\orant\databaseX\OEM.ORA
   C:\orant\databaseX\RBS1ORCL.ORA
   C:\orant\databaseX\STUDENT.ORA
   C:\orant\databaseX\SYS1ORCL.ORA
   C:\orant\databaseX\TMP1ORCL.ORA
   C:\orant\databaseX\USER_TEMP.ORA
   C:\orant\databaseX\USR1ORCL.ORA
                12 file(s) copied.

5. Return to Server Manager or SQL*Plus and mount the database using the restored control file:

   SQL> STARTUP MOUNT;

6. Begin incomplete recovery using the timestamp from the alert log and the USING BACKUP CONTROLFILE clause:

   SQL> RECOVER DATABASE UNTIL TIME '2000-02-21:14:04:00'
     2  USING BACKUP CONTROLFILE;

   Oracle requests archived redo logs as needed, for example:

   ORA-00279: change 6262125 generated at 02/13/00 20:21:24 needed for thread 1
   ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC480.1
   ORA-00280: change 6262125 for thread 1 is in sequence #480
   
   Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
   AUTO
   Log applied.
   ...
   Media recovery complete.

7. When media recovery completes, open the database with RESETLOGS to synchronize the datafiles with the backup control file and redo:

   SQL> ALTER DATABASE OPEN RESETLOGS;
   Statement processed.

8. Verify that the STUDENT tablespace has been recovered:

   SQL> SELECT tablespace_name FROM dba_tablespaces;
   
   TABLESPACE_NAME
   ------------------------------
   SYSTEM
   USER_DATA
   ROLLBACK_DATA
   TEMPORARY_DATA
   STUDENT
   USER_TEMP
   OEM
   
   7 rows selected.

After the recovery

Once the recovery is complete and the database is open with RESETLOGS, you should:

• Perform a full closed database backup to establish a new baseline for future recoveries.
• Ask users to re-enter any transactions that occurred after the recovery point (in this example, the ten minutes between the tablespace drop and when you began recovery).
• Review your backup and recovery strategy, ensuring that control files and archived redo logs are backed up regularly and stored safely (RMAN is the preferred tool in modern environments).

In the next lesson, you will learn how to perform a recovery when the current online redo log is lost.