Best way to restore database back to previous point in time

From: <patrick.gunia_at_googlemail.com>
Date: Thu, 20 Mar 2014 04:34:48 -0700 (PDT)
Message-ID: <ddb0f9cb-d40f-44aa-b640-efa4b2efe368_at_googlegroups.com>



Hi everybody,

I have a question concerning the best way to restore a database to a previous point in time. My concrete intention is a little bit different from the usual backup-purpose. As Im working on performance testing Id like to have a fixed backup (or several backups) which all have a fixed state in time. I load such a state, run my benchmarks (which alter the database) and afterwards roll the database back to the state before I started my test. So when I restore my database I explicitely dont want any recovery (because I dont want the database to return to the current state). This is how my RMAN-script currently looks like:

 RUN {
  # shutdown and remount database to prepare for backup   SHUTDOWN IMMEDIATE
  STARTUP MOUNT
  # allocate output stream
  ALLOCATE CHANNEL ch1 TYPE DISK FORMAT 'F:\oraBackup\FullBackupNoPreload';      

  # backup complete database
  BACKUP DATABASE PLUS ARCHIVELOG;
  BACKUP CURRENT CONTROLFILE;
  BACKUP SPFILE;
  # free outputstream
  RELEASE CHANNEL ch1;
    }
    EXIT; And here is the RMAN-restore script Im using:

 RUN {
# shutdown and mount database to prepare for restore / recovery

	  SHUTDOWN IMMEDIATE;
	  STARTUP MOUNT;


# restore and recover database
# restore using the tag-name of the backup-file => tag name can be retrieved
# using rman>list backup;

# Empty Setup, clean database
RESTORE DATABASE FROM TAG 'TAG20140319T155921';
# RESTORE CONTROLFILE FROM 'F:\oraBackup\FULLBACKUPNOPRELOAD';
# RESTORE TABLESPACE t01 FROM TAG 'TAG20140319T155921';
# RESTORE TABLESPACE t02 FROM TAG 'TAG20140319T155921';
# RESTORE TABLESPACE t03 FROM TAG 'TAG20140319T155921';

# Revocer uses the stored transactions and trys to put the database back into the current state
# so usually we dont want to do that
# RECOVER DATABASE;

# open the database not resetting logs (they should be valid and thus reusable)
ALTER DATABASE OPEN RESETLOGS; SHUTDOWN IMMEDIATE; STARTUP;

    }
    EXIT; I tried both, restoring the whole database using RESTORE DATABASE and restoring only the user tablespaces which contain my userdata. What I get are either files which need media recovery, a state which is the current one (because it recovers) or other errors...does anyone of you can give me some advice, what I need to change, to make this work? Thanks in advance! Received on Thu Mar 20 2014 - 12:34:48 CET

Original text of this message