1. Home
  2. Knowledge Base
  3. Backup and Recovery
  4. Recovery From Loss Of Datafile For Which No Backup Is Available
Searching...

Recovery From Loss Of Datafile For Which No Backup Is Available

SCENARIO – 10g Database Loss of datafile which has not been backed up.

CREATE NEW TABLESPACE TEST

SQL> create tablespace test datafile ‘/u02/oradata/testdb/test.dbf’ size 10m;

Tablespace created.

TAKE A BACKUP OF THE DATABASE AT THIS POINT >>>>>>>>>

ADD DATAFILE TO TEST TABLESPACE

SQL> alter tablespace test add datafile ‘/u02/oradata/testdb/test01.dbf’ size 10m;

Tablespace altered.

SIMULATE FAILURE BY REMOVING DATAFILES FOR TEST TABLESPACE FROM DISK

Note: The tablespace TEST has two datafiles, but only one has been backed up at this point in time

testdb:/u02/oradata/testdb> rm test*
testdb:/u02/oradata/testdb> sql

SQL> alter tablespace test offline immediate;

Tablespace altered.

RESTORE DATAFILE 5; – The datafile which was backed up.

testdb:/u02/oradata/testdb> rman target / catalog rman11p/xxx@rcatp

Recovery Manager: Release 11.1.0.6.0 – Production on Thu May 14 09:19:28 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=2469983515)
connected to recovery catalog database

RMAN> restore datafile 5;

Starting restore at 14/MAY/09
starting full resync of recovery catalog
full resync complete
Finished restore at 14/MAY/09

RMAN> restore tablespace test;

Starting restore at 14/MAY/09
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_DISK_1

creating datafile file number=6 name=/u02/oradata/testdb/test01.dbf >>> In 10g, Oracle creates the missing datafile .
skipping datafile 5; already restored to file /u02/oradata/testdb/test.dbf
Finished restore at 14/MAY/09

RECOVER TABLESPACE TEST

SQL> recover tablespace test;
Media recovery complete.

SQL> alter tablespace test online;
Tablespace altered.

The following two tabs change content below.

Gavin Soorma

Latest posts by Gavin Soorma (see all)

Updated on June 2, 2021

Was this article helpful?

Yes No

About The Author

Gavin Soorma

Gavin Soorma has not written a bio yet38

Related Articles

Comments

  1. You gave the heading as “Recovery From Loss Of Datafile For Which No Backup Is Available” and you use RMAN to recover the datafile.

  2. If you see I am showing how to recover from a case where you DO NOT have a BACKUP of a datafile which is lost, but you are still able to recreate and recover that datafile using RMAN – it does this by using the information stored in the online as well as archived redo log files. This scenario deals with a situation where you hacve just added a datafile and before you could take a backup you are faced with a media failure.

  3. Hello,

    there is a similar procedure available that does not involve RMAN:

    > alter tablespace USERS add datafile ‘/PERF/u02/oradata/PERF/users02.dbf’ size 10M;

    See what objects are in newly added datafile
    > column FILE_NAME format a50
    select FILE_ID, FILE_NAME, ONLINE_STATUS, STATUS from dba_data_files;
    FILE_ID FILE_NAME
    ———- ————————————————–
    1 /PERF/u02/oradata/PERF/system01.dbf
    2 /PERF/u02/oradata/PERF/undotbs01.dbf
    3 /PERF/u02/oradata/PERF/sysaux01.dbf
    4 /PERF/u02/oradata/PERF/users01.dbf
    5 /PERF/u02/oradata/PERF/users02.dbf

    Now we remove the recently added file:
    $ rm /PERF/u02/oradata/PERF/users02.dbf

    > startup mount
    > alter database datafile 5 offline;
    > alter database open;
    > alter database create datafile ‘/PERF/u02/oradata/PERF/users02.dbf’;
    > recover datafile ‘/PERF/u02/oradata/PERF/users02.dbf’;
    auto
    > alter database datafile ‘/PERF/u02/oradata/PERF/users02.dbf’ online;

    Done.

    Greetings
    Remigiusz Boguszewicz

Leave a Comment