Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: recovery stops due to missing datafile
"Bonjour" from Paris,
You can try this :
A valid (either hot or cold) backup of the datafiles exists, except for the datafile created since the backup was taken. The current controlfile exists. The database is in archivelog mode (see note (c) at bottom of page).
SQLDBA> alter database create datafile 2> '/dev1/oracle/dbs/testtbs.dbf'; Statement processed. b. The datafile can be created with a different filename to the original. This option might be chosen if the original file was lost due to disk failure and the failed disk was still unavailable; the new file would then be created on a different device. For example, SQLDBA> alter database create datafile 2> '/dev1/oracle/dbs/testtbs.dbf' 3> as 4> '/dev2/oracle/dbs/testtbs.dbf'; Statement processed. The above command creates a new datafile on the dev2 device. The file is created using information, stored in the control file, from the original file. The command implicitly renames the filename in the control file. NOTE: IT IS VERY IMPORTANT TO SPECIFY THE CORRECT FILENAME WHEN RECREATING THE LOST DATAFILE. IF YOU SPECIFY AN EXISTING ORACLE DATAFILE, THAT DATAFILE WILL BE INITIALISED AND WILL ITSELF REQUIRE RECOVERY.
4. Recover the database.
SQLDBA> recover database ORA-00279: Change 6677 generated at 06/03/97 15:20:24 needed for thread 1 ORA-00289: Suggestion : /dev1/oracle/dbs/arch/arch000074.arc ORA-00280: Change 6677 for thread 1 is in sequence #74 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} At this point the recovery procedure will wait for the user to supply the information requested regarding the name and location of the archived log files. For example, entering AUTO directs Oracle to apply the suggested redo log and any others that it requires to recover the datafiles. Applying suggested logfile... Log applied.
:
:
<Application of further redo logs>
:
:
Media recovery complete.
5. Open the database
SQLDBA> alter database open; Statement processed.
B. Old controlfile, no backup of datafile (Oracle release 7.3.x). I tested successfully on 8.0.5.
A valid (either hot or cold) backup of the datafiles exists, except for the datafile created since the backup was taken. The controlfile is a backup from before the creation of the new datafile. The database is in archivelog mode (see note (c) at bottom of page).
SVRMGR> recover database using backup controlfile ORA-00279: Change 6677 generated at 06/03/97 15:20:24 needed for thread 1 ORA-00289: Suggestion : /dev1/oracle/dbs/arch/arch000074.arc ORA-00280: Change 6677 for thread 1 is in sequence #74 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} At this point, apply the archived logs as requested. Eventually Oracle will encounter redo to be applied to the non-existent datafile. The recovery session will exit with the following message, and will return the user to the Server Manager prompt: ORA-00283: Recovery session canceled due to errors ORA-01244: unnamed datafile(s) added to controlfile by media recovery ORA-01110: data file 5: '/dev1/oracle/dbs/testtbs.dbf' 4. Recreate the missing datafile. To do this, select the relevant filename from v$datafile: SVRMGR> select name from v$datafile where file#=5; NAME ------------------------------------------------------- UNNAMED0005 Now recreate the file: SVRMGR> alter database create datafile 2> 'UNNAMED0005' 3> as 4> '/dev1/oracle/dbs/testtbs.dbf';
5. Restart recovery
SVRMGR> recover database using backup controlfile ORA-00279: Change 6747 generated at 09/24/97 16:57:18 needed for thread 1 ORA-00289: Suggestion : /dev1/oracle/dbs/arch/arch000079.arc ORA-00280: Change 6747 for thread 1 is in sequence #79 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} Apply archived logs as requested. Prior to Oracle8, recovery must apply the complete log which was current at the time of the datafile creation (in the above example, this would be log sequence 79). A recovery to a point in time before the end of this log would result in errors: ORA-01196: file 1 is inconsistent due to a failed media recovery session ORA-01110: data file 1: '/dev1/oracle/dbs/systbs.dbf' If this happens, re-recover the database and ensure that the complete log is applied (plus any further redo if required). This limitation does not exist from Oracle 8.0+. Eventually, Oracle will request the archived log corresponding to the current online log. It does this because the (backup) controlfile has no knowledge of the current log sequence. If an attempt is made to apply the suggested log, the recovery session will exit with the following message: ORA-00308: cannot open archived log '/dev1/oracle/dbs/arch/arch000084.arc' ORA-07360: sfifi: stat error, unable to obtain information about file. SVR4 Error: 2: No such file or directory At this stage, simply restart the recovery session and apply the current online log. The best way to do this is to try applying the online redo logs one by one until Oracle completes media recovery: SVRMGR> recover database using backup controlfile ORA-00279: Change 6763 generated at 09/24/97 16:57:59 needed for thread 1 ORA-00289: Suggestion : /dev1/oracle/dbs/arch/arch000084.arc ORA-00280: Change 6763 for thread 1 is in sequence #84 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /dev1/oracle/dbs/log2.dbf Log applied. Media recovery complete.
6. Open the database
SVRMGR> alter database open resetlogs;
The resetlogs option must be chosen to resynchronize the controlfile.
NOTES:
Oracle handles this situation as follows:
and the controlfile and resolves in favour of file$, deleting the entry from the controlfile.
c) It may be possible to recover the datafile using this technique even if the
database is not in archivelog mode. However, this relies on the required redo being available in the online redo logs.
Good luck ...
"Au revoir" from Paris
Christophe Brault
DBA Oracle
Received on Wed Jun 06 2001 - 07:03:44 CDT