News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Recovery From Loss Of Datafile For Which No Backup Is Available

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 3 Comments

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.

 

3 Comments

name1
  • Jan 27 2010
You gave the heading as "Recovery From Loss Of Datafile For Which No Backup Is Available" and you use RMAN to recover the datafile.
Gavin Soorma
  • Jan 27 2010
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.
Remigiusz Boguszewicz
  • Jun 8 2010
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 Reply

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