Incomplete Recovery   «Prev  Next»

Lesson 8 Recovering with backup control file
Objective Demonstrate how to use a backup control file to recover.

Recovering With Backup Control File

A backup control file is essential for recovering both tablespaces and databases in Oracle, but its role differs in each scenario:
  1. Tablespace Recovery:
    • Purpose: Recover a dropped or lost tablespace that's no longer recorded in the current control file.
    • Steps:
      1. Restore a backup control file that holds information about the missing tablespace.
      2. Use `RECOVER TABLESPACE` to recover the tablespace's datafiles.
      3. Rename the restored control file to avoid conflicts.
  2. Database Recovery:
    • Purpose: Recover the entire database when all existing control files are lost or damaged.
    • Steps:
      1. Restore a backup control file.
      2. Mount the database using the restored control file.
      3. Initiate database recovery using `RECOVER DATABASE`.
      4. Open the database with `RESETLOGS` to create a new database incarnation.

Key Points:
  • Control File Importance: It contains critical metadata about the database structure, making it crucial for recovery operations.
  • Regular Backups: Back up the control file frequently, along with datafiles and archived redo logs, to ensure successful recovery.
  • Multiple Control Files: Oracle recommends maintaining multiple control files for redundancy and protection against loss.
  • Recovery Manager (RMAN): Use RMAN for simplified backup and recovery management.


Demonstrate how to use Backup Control File to recover

Suppose the current time is 2:14 PM on 21-JAN-2000. Your DBA in training has told you that he removed the tablespace that contains the CUSTOMERS table about 10 minutes ago via the 
DROP TABLESPACE STUDENT INCLUDING CONTENTS;

statement. There were a lot of customer records being updated two hours ago. In addition, you have a prudent backup strategy and the database backups are done every night. What can you do to get the tablespace and the table back with minimum data loss?
Solution
A backup control file recovery is the appropriate approach. Since the current control file no longer contains the information about the dropped tablespace, you must use last night's backup, which contains the tablespace's datafile(s). This is the only way to get the tablespace back. To prevent any further user access, you immediately put the database in restricted mode via the 
ALTER SYSTEM ENABLE RESTRICTED SESSION

statement. At the same time, you inform users to keep the records of the data entered in the last 10 minutes.
Since the current control file is going to be replaced, you must collect some information about the database structure in case it is required in the recovery process. View the Code below to see the statment you need to issue. 

SQL> select * from V$log;
GROUP#...SEQUENCE# BYTES    MEMBERS  ARC STATUS ... FIRST_TIME
------ --------- -------  -------  --- --------  ---------------          
     1...   74 1048576        1   NO INACTIVE ...21-FEB-00:14:04
     2...   75 1048576        1   NO  CURRENT ...21-FEB-00:14:09
     
......

From this view, you get the current log sequence number 75. Next you obtain the name and location of the file that contains the tablespace. View the Code below. 

SQL> select tablespace_name, file_name from dba_data_files 
     2> where tablespace_name = 'STUDENT';
TABLESPACE_NAME FILE_NAME
--------------- ----------------------------------------
STUDENT  C:\ORANT\DATABASE\STUDENT.ORA
1 row selected.
SQL>
Then you go to the alert log to confirm the time the tablespace was dropped. 
...
Mon Feb 21 14:04:13 2000
Drop tablespace student including contents
...

Now the database is ready for you to start incomplete recovery.
The following simulation walks you though the steps of performing a backup control file recovery.

Performing Backup Control File Recovery

The 
USING BACKUP CONTROLFILE

alternative recovers by applying the redo log in a backup control file.
Backup control file: A backup of the control file generated as the result of using the alter database backup controlfile to 'file_name' command or the alter database backup control file to trace command.
Performing a backup control file recovery is a complicated process. You may need to check the status of the files you need to recover with data dictionary views from time to time.
  1. Shut down the database using the IMMEDIATE or NORMAL option.
  2. Perform a backup of the current control file.
  3. Restore all datafiles and control files from the backup that was made when the tablespace existed.
  4. Mount the database.
  5. Put any offline datafiles online because an offline datafile may not be recovered after an incomplete recovery. You can check this with V$RECOVER_FILE.
  6. Perform the recovery by issuing the RECOVER DATABASE UNTIL TIME `<TIME>' | CANCEL | CHANGE <CHANGE_NUMBER> USING BACKUP CONTROLFILE; statement.
  7. In order to synchronize datafiles with the control file and redo logs, open the database with the RESETLOGS option.
  8. Make sure that the removed objects are back.
  9. Perform a whole closed database backup and notify users that they need to re-enter the data that was lost after the specified recovery time.

Performing Recovery with Backup Control File

  1. Suppose you have connected as sysdba to the database ORCL. You are told that the tablespace STUDENT is dropped. To make sure this tablespace is not there, type SELECT TABLESPACE_NAME FROM DBA_TABLESPACES at the prompt and press Enter.
  2. To start the recovery process, first shutdown the database. Type SHUTDOWN IMMEDIATE at the prompt and press Enter.
  3. Now you need to make a backup of the current control file in your operating system. To access the operating system, type HOST at the prompt and press Enter.
  4. To make a backup of the current control file, type COPY C:\ORANT\DATABASE\CT11ORCL.ORA C:\ORANT\DATABASEX at the prompt and press Enter.
  5. Suppose you have a batch file, do_restore_dcfile.bat, to help you restore all the datafiles and the control file from the most recent backup (There was one made on Feb. 13, 2000, before the tablespace was dropped). Type DO_RESTORE_DCFILE at the prompt and press Enter.
  6. To access the Server Manager, type EXIT and press Enter.
  7. Now you go to your alert log file and you find that the tablespace was removed at 2000-02-21:14:04:13. To start the recovery process, type STARTUP MOUNT at the prompt and press Enter.
  8. To start the recovery, type RECOVER DATABASE UNTIL TIME `2000-02-21:14:04:00'USING BACKUP CONTROLFILE; at the prompt and press Enter.
  9. To continue the recovery process, type AUTO at the cursor and press Enter.
  10. To synchronize datafiles with the control file and redo log files, type ALTER DATABASE OPEN RESETLOGS; at the prompt and press Enter.
  11. To make sure the tablespace STUDENT is recovered, query the DBA_TABLESPACES again. Type SELECT TABLESPACE_NAME FROM DBA_TABLESPACES; at the prompt and press Enter.
  12. You have successfully performed the recovery with a backup control file. Do not forget to make a whole database backup after each incomplete recovery. This is the end the Simulation.


If you want to view all the lines in the recovery process, click the View Code link below. 
SQL> SELECT TABLESPACE_NAME 
FROM DBA_TABLESPACES TABLESPACE_NAME
------------------------------
SYSTEM
USER_DATA
ROLLBACK_DATA
TEMPORARY_DATA
USER_TEMP
OEM
6 rows selected.
SQL> SHUTDOWN IMMEDIATE 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST
Microsoft(R) Windows NT(TM)
(C) Copyright 1985-1996 Microsoft Corp.

C:\orant\> COPY C:\ORANT\DATABASE\CT11ORCL.ORA C:\ORANT\DATABASEX 
        1 file(s) copied.

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.
C:\orant>EXIT 
SQL> STARTUP MOUNT ORACLE instance started.
Total System Global Area                    11710464 bytes
Fixed Size                                     49152 bytes
Variable Size                               11177984 bytes
Database Buffers                              409600 bytes
Redo Buffers                                   73728 bytes
Database mounted.


SQL> RECOVER DATABASE UNTIL TIME `2000-02-21:14:04:00'
       > USING BACKUP CONTROLFILE;
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.
ORA-00279: change 6262131 generated at 02/21/00 14:03:36 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC481.1
ORA-00280: change 6262131 for thread 1 is in sequence #481
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC480.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 6262132 generated at 02/21/00 14:03:48 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC482.1
ORA-00280: change 6262132 for thread 1 is in sequence #482
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC481.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 6262133 generated at 02/21/00 14:04:04 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC483.1
ORA-00280: change 6262133 for thread 1 is in sequence #483
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC482.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 6262134 generated at 02/21/00 14:04:12 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC484.1
ORA-00280: change 6262134 for thread 1 is in sequence #484
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC483.1' no longer needed for this recovery
 Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN RESETLOGS; 
Statement processed.


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

The next lesson demonstrates how to perform a recovery if the current redo log is lost.

SEMrush Software