Recovery From Loss Of Datafile For Which No Backup Is Available
Posted by Gavin Soorma on July 2, 2009
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.
