Re: ORA-01110

Hi, Hieraklion,

Thank you very much. It worked very well. So now I recovered my database by using your tips. Thanks again!

Steve

Hieraklion wrote:

> "Bonjour" from Paris,
>
> I assume the question is :
>
> How to recover a database having added a datafile since the last backup
> ?
>
> CASE NUMBER ONE : In archive mode (from the Oracle support : note
> 29430.1). I tested successfully this method in 805 :
>
> This bulletin outlines the steps required in performing database
> recovery
> having added a datafile to the database since the last backup was
> taken.
> Section A is applicable to Oracle release 7.x. Section B applies only
> to
> Oracle releases 7.3.x and above.
>
> PLEASE READ THROUGH ALL STEPS AND WARNINGS BEFORE ATTEMPTING TO USE
> THIS
> BULLETIN.
>
> A. Current controlfile, backup of datafile exists (Oracle release 7.x)
> ===================================================================
>
> 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).
>
> 1. Restore ONLY the datafiles (those that have been lost or damaged)
> from the
> last hot or cold backup. The current online redo logs and control
> file(s)
> must be intact.
>
> 2. Mount the database
>
> 3. Create a new datafile using the 'ALTER DATABASE CREATE DATAFILE'
> command.
>
> a. The datafile can be created with the same name as the original
> file. For example,
>
> 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)
> =============================================================
>
> 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).
>
> 1. Restore the datafiles (those that have been lost or damaged) from
> the
> last hot or cold backup. Also restore the old copy of the
> controlfile.
> The current online redo logs must be intact.
>
> 2. Mount the database
>
> 3. Start media recovery, specifying backup controlfile
>
> 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:
> ======
>
> a) These techniques can be used whether the database was closed either
> cleanly or uncleanly (aborted).
>
> b) If the database is recovered using an incomplete recovery technique
> (either
> time-based, cancel-based, or change-based), and is recovered to a
> point in
> time before the datafile was originally created, any references to
> that
> datafile will be removed from the database when the database is
> opened.
>
> Oracle handles this situation as follows:
>
> - The 'alter database create datafile....' command creates a
> reference in
> the controlfile for the datafile.
> - Incomplete recovery terminates before applying redo that would
> create a
> corresponding row for the datafile in the file$ dictionary table.
> - When the database is opened, Oracle detects an inconsistency
> between file$
> 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.
>
> CASE NUMBER TWO : in no archive mode.
>
> See note (c) from the previous note. If you can not have the right
> conditions, i assume the only solution is to use the 'OFFLINE DROP'
> option. Your datafile is lost.
>
> "Au revoir" from Paris
> Hieraklion
>
> Steven Shen a écrit :
>
> > Our tools01.dbf file was missing. The existing backup is older than
> > database. How can I get current tools01.dbf file back? Thanks!
Received on Tue Apr 24 2001 - 10:35:04 CDT