Oracle Technical Track

How to recover a subset of an Oracle database

4 min read

Dec 9, 2013 12:00:00 AM

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; SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; 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. restore_db.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'; set until sequence 272151; ==> (Max sequence + 1) recover database skip forever tablespace ABC_IND_TS,ABC_DATA_TS,DEF_INDX_TS,DEF_REF_TS_01,DEF_REF_TS_01,ACCT_TAB_TS_01,ACCT_TAB_TS_02, ............................................................................................. ==> List of tablespaces other than 4 required TPG_DATA_TS_01,TPG_IND_TS_01,USERS,XDB; 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; 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.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

How to recover a subset of an Oracle database

Share this

Share this

More resources

Oracle E-Business Suite: Virtual host names

Oracle E-Business Suite and Java Web Start. Finally!

ORA-01206: File is not part of this database - wrong database id

Ready to unlock value from your data?