Recovering Lost Datafile with no Backup(Oracle)

Lesson 2	Recovering a lost Datafile with no Backup
Objective	Demonstrate how to recover a Lost Datafile with no backup

Recovering a Lost Datafile with no Backup

Scenario

Suppose one of your junior DBAs created a tablespace named STUDENT two days ago and forgot to include this tablespace in your backup strategy. This tablespace contains important user data. A media failure occurs and the disk that holds the datafile belonging to the tablespace STUDENT is permanently damaged. After some investigation, you find that all the archived logs for the past 2 days are intact and that the datafile is not a system or rollback segment datafile.
Question: What should you do next?

Solution

After some research, you determine that since you have all the archive logs generated since the lost datafile was created, it is possible to recover the datafile, even though it has no backup.
The technique involves creating a replacement in an empty datafile using information stored in the control file. From the control file you get the size and name of the lost datafile using the command

ALTER DATABASE CREATE DATAFILE <name stored in controlfile> 
AS <name of replacement file>

Note that the size is not specified--this information is taken from the control file. The CREATE command produces an empty datafile of the appropriate size, which can then be recovered from the archive logs.
This method allows you to recover the datafile just prior to the time of the media failure. If your database is still open when the damage occurs, you must act quickly to bring the damaged datafile offline to prevent any new checkpoint from attempting to write to a file that does not exist.
The simulation below walks you through the steps of performing a datafile recovery with no backup on the damaged STUDENT tablespace. You may also preview all steps.

Recover Datafile with no backup
View the Code below if you want to view all of the lines of code in one screen.

C:\ORANT> SET ORACLE_SID=ORCL
C:\ORANT> SVRMGR30
Oracle Server Manager Release 3.0.5.0.0 - Production

(c) Copyright 2007, Oracle Corporation. All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.5.1.0 - Production

SVRMGR> CONNECT INTERNAL
Password:
Connected.
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> ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' OFFLINE;
Statement processed.
SVRMGR> ALTER DATABASE OPEN;
Statement processed.
SVRMGR> SELECT * FROM V$RECOVER_FILE;
FILE#   ONLINE  ERROR              CHANGE#    TIME
-----   ------- ------------------ -------    ---------
    5   OFFLINE FILE NOT FOUND           0
1 row selected.
SVRMGR> ALTER DATABASE CREATE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' 
AS 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA';
Statement processed. 
SVRMGR> SELECT * FROM V$RECOVER_FILE;
FILE#   ONLINE  ERROR              CHANGE#    TIME
-----   ------- ------------------ --------   ------------
    5   OFFLINE        5621029    11-JAN-2000
1 row selected.
SVRMGR> RECOVER DATAFILE 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA' 
UNTIL TIME '2000-01-18:12:00:00';
ORA-00279: change 5621029 generated at 01/11/00 16:03:17 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC442.1
ORA-00280: change 5621029 for thread 1 is in sequence #442
 Specify log: {
 <RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
ORA-00279: change 5641036 generated at 01/12/00 11:45:17 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC443.1
ORA-00280: change 5641036 for thread 1 is in sequence #443
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC442.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 5661054 generated at 01/12/00 19:31:49 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC444.1
ORA-00280: change 5661054 for thread 1 is in sequence #444
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC443.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 5681062 generated at 01/13/00 19:24:40 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC445.1
ORA-00280: change 5681062 for thread 1 is in sequence #445
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC444.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 5701070 generated at 01/17/00 10:46:41 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC446.1
ORA-00280: change 5701070 for thread 1 is in sequence #446
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC445.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 5721078 generated at 01/17/00 12:26:16 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC447.1
ORA-00280: change 5721078 for thread 1 is in sequence #447
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC446.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 5741086 generated at 01/18/00 11:04:32 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC448.1
ORA-00280: change 5741086 for thread 1 is in sequence #448
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC447.1' no longer needed for this recovery
 Log applied.
Media recovery complete.
SVRMGR> ALTER DATABASE DATAFILE 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA' ONLINE;
Statement processed.
SVRMGR>

The next lesson demonstrates how to handle recovery of a file in backup mode.