Ask the Expert

Simulating restoration of tablespace using RMAN

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?

    Requires Free Membership to View

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
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:


This was first published in July 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: