Recover datafile

Helpful queries

Find delete file id : select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files where FILE_NAME like '%dat_21%';
check status of file : select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files where FILE_ID='137';
check if any data segments in that datafile : select * from dba_segments where tablespace_name ='DATA' and header_file = 137 ;

if datafile is empty : ALTER DATAFILE '+DGROUP1/example_df3.f' offline drop ;

The ALTER DATABASE DATAFILE OFFLINE DROP command, is not meant

to allow you to remove a datafile. What the command really means is that you

are offlining the datafile with the intention of dropping the tablespace.

alternatively you can follow below notes and recreate and recover the datafile

How to Drop a Datafile From a Tablespace (Doc ID 111316.1)

PURPOSE
This note explains how a datafile can be removed from a database.

Since there can be confusion as to how a datafile can be dropped because of
the ALTER DATABASE DATAFILE OFFLINE DROP command, this note explains the
steps needed to delete a datafile and, in contrast, when the OFFLINE DROP
command is used.

SCOPE & APPLICATION
There are two situations where people may want to 'remove' a datafile from a
tablespace:

1. You have just mistakenly added a file to a tablespace, or perhaps you
made the file much larger than intended and now want to remove it.

2. You are involved in a recovery scenario and the database will not start
because a datafile is missing.

This article is meant to discuss situation 1 above. There are other
articles that discuss recovery scenarios where a database cannot be brought
online due to missing datafiles. Please see the 'Related Documents' section
at the bottom of this article.

Restrictions on Dropping Datafiles:

- Datafile Must be empty.
- Cannot be the first file in the tablespace. In such cases, drop the tablespace instead.
- Cannot be a datafile that is part of the system tablespace, even if it is not the first datafile of the system tablespace.
- Cannot be in a read-only tablespace.
- The datafile cannot be offline.

How to 'DROP' a Datafile from a Tablespace:
===========================================

Version 9.2 and earlier

Before we start with detailed explanations of the process involved, please note
that Oracle does not provide an interface for dropping datafiles in the same
way that you could drop a schema object such as a table, a view, a user, etc.
Once you make a datafile part of a tablespace, the datafile CANNOT be removed,
although we can use some workarounds.

Before performing certain operations such as taking tablespaces/datafiles
offline, and trying to drop them, ensure you have a full backup.

If the datafile you wish to remove is the only datafile in that tablespace,
simply drop the entire tablespace using:

DROP TABLESPACE INCLUDING CONTENTS;

You can confirm how many datafiles make up a tablespace by running the
following query:

select file_name, tablespace_name
from dba_data_files
where tablespace_name ='';

The DROP TABLESPACE command removes the tablespace, the datafile, and the
tablespace's contents from the data dictionary. Oracle will no longer have
access to ANY object that was contained in this tablespace. The physical
datafile must then be removed using an operating system command (Oracle NEVER
physically removes any datafiles). Depending on which platform you try this
on, you may not be able to physically delete the datafile until Oracle is
completely shut down. (For example, on Windows NT, you may have to shutdown
Oracle AND stop the associated service before the operating system will allow
you to delete the file - in some cases, file locks are still held by Oracle.)

If you have more than one datafile in the tablespace, and you do NOT need the
information contained in that tablespace, or if you can easily recreate the
information in this tablespace, then use the same command as above:

DROP TABLESPACE INCLUDING CONTENTS;

Again, this will remove the tablespace, the datafiles, and the tablespace's
contents from the data dictionary. Oracle will no longer have access to ANY
object that was contained in this tablespace. You can then use CREATE
TABLESPACE and re-import the appropriate objects back into the tablespace.

If you have more than one datafile in the tablespace and you wish to keep the
objects that reside in the other datafile(s) which are part of this tablespace,
then you must export all the objects inside the affected tablespace. Gather
information on the current datafiles within the tablespace by running this
query:

select file_name, tablespace_name
from dba_data_files
where tablespace_name ='';

Make sure you specify the tablespace name in capital letters.

In order to allow you to identify which objects are inside the affected
tablespace for the purposes of running your export, use the following query:

select owner,segment_name,segment_type
from dba_segments
where tablespace_name=''

Now, export all the objects that you wish to keep.

Once the export is done, issue the DROP TABLESPACE tablespace INCLUDING
CONTENTS.

Note that this PERMANENTLY removes all objects in this tablespace. Delete the
datafiles belonging to this tablespace using the operating system. (See the
comment above about possible problems in doing this.) Recreate the tablespace
with the datafile(s) desired, then import the objects into that tablespace.
(This may have to be done at the table level, depending on how the tablespace
was organized.)

NOTE:
The ALTER DATABASE DATAFILE OFFLINE DROP command, is not meant
to allow you to remove a datafile. What the command really means is that you
are offlining the datafile with the intention of dropping the tablespace.

If you are running in archivelog mode, you can also use:

ALTER DATABASE DATAFILE OFFLINE;

instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer
attempts to access it, but it is still considered part of that tablespace. This
datafile is marked only as offline in the controlfile and there is no SCN
comparison done between the controlfile and the datafile during startup (This
also allows you to startup a database with a non-critical datafile missing).
The entry for that datafile is not deleted from the controlfile to give us the
opportunity to recover that datafile.

New functionality was added with the release of version 10.1 and higher

You can now specify drop tablespace inlcluding contents AND DATAFILES
Refer to Oracle® Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01
Chapter 8 managing tablespaces for more detailed explination

Starting with version 10.2 and higher

You can now alter tablespace drop datafile (except first datafile
of a tablespace)

Refer to the following Oracle Documentation for more details regarding this operation:

For Oracle 10g Release 2:
Oracle® Database Administrator's Guide 10g Release 2 (10.2)Part Number B14231-02 Chapter 9: Dropping Datafiles.

For Oracle 11g:
Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-04 Chapter 12: Dropping Datafiles.

If you do not wish to follow any of these procedures, there are other things
that can be done besides dropping the tablespace.

- If the reason you wanted to drop the file is because you mistakenly created
the file of the wrong size, then consider using the RESIZE command.
See 'Related Documents' below.

- If you really added the datafile by mistake, and Oracle has not yet allocated
any space within this datafile, then you can use ALTER DATABASE DATAFILE
RESIZE; command to make the file smaller than 5 Oracle blocks. If
the datafile is resized to smaller than 5 oracle blocks, then it will never
be considered for extent allocation. At some later date, the tablespace can
be rebuilt to exclude the incorrect datafile.

Recover A Lost Datafile With No Backup (Doc ID 1060605.6)

Problem Description: 
==================== 
 
You have inadvertantly lost a datafile at the OS level and there are no current 
backups. 
You are in archivelog mode.
You have ALL Archivelogs available since the datafile was created initially (creation date). 


Problem Explanation: 
==================== 

Since there are no backups, the database cannot be opened without this file 
unless dropped and the tablespace dropped.  If this is an important file and 
tablespace, this is not a valid option.

 
Problem References: 
=================== 

Oracle 7 Backup and Recovery Workshop Student Guide, Failure Scenario 14 


Search Words: 
============= 
 
ORA-1110, lost datafile, file not found.



Solution Description: 
===================== 
 
This files have to be recreated and recovered. Do the following:
 
1) Go to svrmgrl and connect internal.

2) SVRMGR>shutdown immediate. (If this hangs, issue shutdown abort)

3) SVRMGR>startup mount 

4) SVRMGR> select * from v$recover_file;


  SAMPLE:

  FILE#      ONLINE  ERROR              CHANGE#    TIME                
  ---------- ------- ------------------ ---------- --------------------   
  11 OFFLINE FILE NOT FOUND              0 01/01/88 00:00:00   

  (Noting the file number that was reported in the error)


5) SVRMGR> select * from v$datafile where FILE#=11;

  SAMPLE:

  FILE#      STATUS  ENABLED    CHECKPOINT BYTES      CREATE_BYT NAME             
  ---------- ------- ---------- ---------- ---------- ---------- --------
  11 RECOVER READ WRITE 4.9392E+12          0      10240 /tmp/sample.dbf

  (Note the status is RECOVER and the CREATE_BYTE size)
  (Note the NAME)


6) Recreate the datafile.

	SVRMGR> alter database create datafile '/tmp/sample.dbf'
		as '/tmp/sample.dbf' size 10240 reuse.

	(Note that the file "created" and the file created "as" are
	 the same file. The "size" needs to be the same size as it
	 was when it was created.)

7) Check to see that it was successful.

	SVRMGR> select * from v$datafile where FILE#=11;

8) Bring the file online.

	SVRMGR> alter database datafile '/tmp/sample.dbf' online;

9) Recover the datafile.

	SVRMGR> Recover database;

Note: During recovery, all archived redo logs written to since the original 
datafile was created must be applied to the new, empty version of the 
lost datafile." 


10) Enjoy!!

	SVRMGR> alter database open;


Solution Explanation: 
===================== 
 
Recreating the file and recovering it rewrites it to the OS and brings it up to 
date.   

 
Solution References: 
==================== 

Oracle 7 Backup and Recovery Workshop Student Guide, Failure Scenario 14

 How to Recover a Database Having Added a Datafile Since Last Backup (Doc ID 29430.1)
NOTE: In the images and/or the document content below, the user information and environment 
data used represents fictitious data from the Oracle sample schema(s), Public Documentation 
delivered with an Oracle database product or other training material.  Any similarity to actual 
environments, actual persons, living or dead, is purely coincidental and not intended in any manner.



HOW TO RECOVER A DATABASE HAVING ADDED A DATAFILE SINCE THE LAST BACKUP
-----------------------------------------------------------------------

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> '/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> '/oracle/dbs/testtbs.dbf'
            3> as
            4> '/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 : /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 and above)
   =======================================================================

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

 NOTE : 'svrmgrl' has been replaced by SQL*Plus starting from Oracle8i
        So the 'SVRMGR>' prompt is than replaced by 'SQL>'

 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 : /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: '/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> '/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 : /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: '/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 '/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 : /oracle/dbs/arch/arch000084.arc
    ORA-00280: Change 6763 for thread 1 is in sequence #84
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    /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 favor 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.
   
-------------------------------------------------------------------------------

Oracle Technologies Explored

Search This Blog