Loading....

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.

Please follow and like us:

Last Update: July 2, 2009  

July 2, 2009 145 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , ,

3 thoughts on “Recovery From Loss Of Datafile For Which No Backup Is Available

  1. name1

    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. Gavin Soorma

    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. Remigiusz Boguszewicz

    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

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?