Recovering With Backup Control File

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?

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.

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

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

...
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.
You may also preview all the steps.
Performing Recovery with Backup Control File
If you want to view all the lines in the recovery process, click the View Code link below.

SVRMGR> SELECT TABLESPACE_NAME 
FROM DBA_TABLESPACES TABLESPACE_NAME
------------------------------
SYSTEM
USER_DATA
ROLLBACK_DATA
TEMPORARY_DATA
USER_TEMP
OEM
6 rows selected.
SVRMGR> SHUTDOWN IMMEDIATE 
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> 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 
SVRMGR> 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.

SVRMGR> 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.
SVRMGR> ALTER DATABASE OPEN RESETLOGS; 
Statement processed.

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

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