Lesson 8 | Recovering with backup control file |
Objective | Demonstrate how to use a backup control file to recover. |
Recovering With Backup Control File
Scenario
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.
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>
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.
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.