Q

Simulating restoration of tablespace using RMAN

I want to simulate a restoration of a tablespace on a separate database. I do not have a separate instance nor do I have resources to make it available. Hence I plan to test the restoration of the tablespace on the RMAN database and after ensuring that the tablespace is restored, I will drop it. How do I go about it?

This Content Component encountered an error
I am not a DBA but rather a Unix guy. I have implemented Oracle 10.2g RAC on two Sun systems over Sun cluster. The DB is on RAW volumes within a shared Vxvm DG. I have a third system which is my Veritas NBU server and my RMAN catalog server. The RMAN DB is filesystem-based, meaning all DB-related files are on a filesystem. My RAC database is in archivelog mode.

I take online full backups every night and only on Fridays do I take a cold backup. I also take online archivelog backups every six hours through RMAN and use "DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES."

All my backups are successful. I can guarantee this due to the fact that I always log all the RMAN backups and go through them for any errors.

Now the question is: I want to simulate a restoration of a tablespace on a separate database. I do not have a separate instance nor do I have resources to make it available. Hence I plan to test the restoration of the tablespace on the RMAN database and after ensuring that the tablespace is restored, I will drop it. How do I go about it? I am sure I can do this through RMAN. But how?

Set up a new test tablespace in your current database to perform your test on -- that way, if you have any difficulties, you won't lose anything of value. So first, let's create the tablespace:
CREATE TABLESPACE test_ts DATAFILE '/directory/test_ts01.dbf';

Now, let's create a simple table in that tablespace:

CREATE TABLE system.db_objects
TABLESPACE test_ts
AS SELECT * FROM dba_objects;

After this is done, create your backups. To test the restore, shut down the instances and use an OS command to remove the datafile belonging to the tablespace. The instance will not start due to the missing datafile. Use RMAN to restore the tablespace. Provided you have configured automatic channels in RMAN, use the following RMAN script:

RUN {
   STARTUP MOUNT;
   RESTORE DATABASE;
   RECOVER DATABASE;
   ALTER DATABASE OPEN;
   }
This was first published in July 2006

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close