How to recover a subset of an Oracle database

Posted in: Technical Track

Today’s blog post will discuss how to recover a subset of an Oracle database. Many of you would have come across different recovery scenarios, but I’ll be talking about a very different one that happened to me. The following are the details after receiving a call from a client, and checking the environment myself.

i) This is a data warehouse production database, which is sized around 5TB, running on 10gR2 version on AIX platform.

ii) Weekly level 0 and frequent archive log compressed RMAN tape backup(Netbackup) is configured for this database.

iii) Client DBA confirmed that the level 0 backup was executed successfully. and started his scheduled purge activity for older partitions. He accidentally deleted the partitions that belong to year 2011 instead of requested deletion for year 2010 partitions for a table (due to typo on notepad using copy/paste of earlier commands.)

v) He found the issue after executing the required sql script by verifying the log file. He decided not to delete the  associated datafiles that belonged to year 2011 tablespace from DB and OS level.

vi) The range partitions are used, based on monthly data, and dedicated tablespaces are used for a year partition data. There are more than 200 tablespaces, spread across multiple partitioned tables.

vii) There was no recycle bin (flashback drop) feature enabled and no flashback feature is configured. This database also doesn’t have any standby databases configured.

viii) The logical backup for this database has never happened, but block change tracking was enabled for this database.

So, I recently got completed level 0 tape backup only.These older partitions are useful only during month end reporting. The traditional method of restoring the entire database would be time prone and also with the need of 5TB additional storage.

Thanks to RMAN feature (Ref MOS Note:223543.1), we can restore only subset of database. Hence suggested client that I would  create a test database with only required tablespaces. From the existing datafiles (tablespace) at OS level, I found the approximate size of deleted partition tablespace was around 300G. Along with required SYSTEM/SYSSAUX/UNDO tablespaces, the required disk space for this test database was around 420G.

Client SA team created a new test server identifical with the existing server.Most of the required additional mount points were created as soft links on the existing disk space of 600G(we need space for archivelogs too). After cloning the existing oracle home to new server, I used the following steps to complete this recovery activity.

1. Logged into DW production database(db name: test – as usual) and identified the required tablespaces are ‘SYSTEM’,’SYSAUX’,’UNDOTBS’ and ‘TEST_DETAIL_2011_TS’. Also identified the associated datafiles numbers. For eg. file_id’s 1,2,3,149,163,164,149,106,107,108,109,110,181,189 and 192.

2. Connected to rman recovery catalog schema(rman_cat) from this database and Identified the same TAG value used for all level 0 backup files,I consider the TAG value as “TEST_FULLDB_THU151013”. Also identified the require Media for these datafiles and controlfiles as HT0008,HT0014 and HT0015.Updated storage team to keep these Media into tape drive till this activity completion.

3. Logged into test database server and started the instance using nomount state with parameter values large_pool_size=500M and job_queue_processes=0.

4. Connected to RMAN utility with recovery catalog schema to restore the control files first.

RMAN> run
allocate channel t1 type ‘sbt_tape’;
send ‘NB_ORA_client=test’;
restore controlfile from tag ‘TEST_FULLDB_THU151013’;
release channel t1;

5. Mounted the database using sqlplus utility and disabled the block change tracking feature.

SQL> alter database mount;

6. Connected to RMAN utility again with recovery caatlog schema to restore the required tablespaces.

RMAN> run
allocate channel t1 type ‘sbt_tape’;
allocate channel t2 type ‘sbt_tape’;
allocate channel t3 type ‘sbt_tape’;
send ‘NB_ORA_client=test’;
restore tablespace system,undotbs1,sysaux,TEST_DETAIL_2011_TS from tag ‘TEST_FULLDB_THU151013’;
sql “alter database datafile 1,2,3,149,163,164,149,106,107,108,109,110,181,189,192 online”;
release channel t1;
release channel t2;
release channel t3;

7. Gathered the last archivelog sequence backed up on this level 0 backup from RMAN utility.For eg 272150.

8. Created rman command file named recover_db.rman as the list of tablespaces to be skipped was huge. Used  ‘set until sequence’ clause to restore the required archive logs and recover the database. Here is the syntax used.

allocate channel t1 type ‘sbt_tape’;
allocate channel t2 type ‘sbt_tape’;
allocate channel t3 type ‘sbt_tape’;
send ‘NB_ORA_client=test’;
set until sequence 272151; ==> (Max sequence + 1)
recover database skip forever tablespace
………………………………………………………………………………… ==> List of tablespaces other than 4 required
release channel t1;
release channel t2;
release channel t3;

9. Executed this command file using RMAN utility with recovery catalog schema.
RMAN> @restore_db.rman

10. Used sqlplus utility and opened the database using resetlogs option. Then converted this test database to run on noarchivelog mode.
SQL> recover database using backup controlfile until cancel;
SQL> alter database open resetlogs;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;

11. Confirmed the deleted partitions now exists with data on test database.
SQL> select count(1) from <table> partition(<part name>);

12. Handed over this test database and client DBA exported the partitions for year 2011 and imported into the production DW database.

So here we created a test database with the size of 420G instead of a whole database sized 5TB, which definitely saved time and space. Though there are much easier options available in any common production database environment (such as using restore from recyclebin or flashback standby database to point in time,) this method was really helpful when I couldn’t use those options.


Interested in working with Suresh? Schedule a tech call.

About the Author

Yet another Mechanical Engineer, who likes Oracle Database.

9 Comments. Leave new

Nice article!
I hope the DBA used transportable tablespaces instead of regular import though ;-)
Thinking of doing something similar to speed up the recovery of a 4 TB large database.
The idea is to first recover the “active” partitions, and while the database is open recover the “historical” partitions.
This should speed up a future recovery drastically (of course hopefully we’ll never need it ;-) ).
But I wasn’t sure if it was possible to restore only a couple of partitions of a table.


Good article indeed !!


thank you very much….very useful


Wonderful such a nice post


Hi ,
Thanks for your page, My requirement is we have read only TBS for history data & read-write TBS for active data . To achieve faster restore, we want to do below steps
1. restore RW TBS quickly
2. make DB up & online
3. restore RO TBS when DB is online with no downtime.

is that 3rd step “restore RO TBS when DB is online with no downtime.” is possible ?




I’m not sure why you disabled block chang tracking ?


After our partial recovery, it still keeps looking for a datafile, which is not even included in restore & was offline dropped.
SQL> /
alter database open
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: ‘+DATA/TSTR/datafile/undotbs1.275.1020626477’
Process ID: 48452
Session ID: 674 Serial number: 46986

Kumar Ramalingam
March 9, 2021 5:14 pm

Wow! Exactly what I was looking for a week ago. 7TB database but I needed to go thru only one tablespace!! If I have questions, can I still put it here? Is it monitored by Suresh?

Suresh Karthikeyan
March 9, 2021 11:06 pm

You’re welcome to post your questions.


Leave a Reply

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