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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top