Best way to restore database back to previous point in time
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 I´m working on performance testing I´d 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 don´t want any recovery (because I don´t 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 I´m 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