Wednesday, May 22, 2013

Oracle 11GR2 Recover Tablespace To Point In Time

After a change in an Oracle 11gr2 database we got the call if we could rewind the database to the time before the change.
No problem we had made a guaranteed restore point. Oeps one problem this a an database with more the 1 application schema in it. A flashback to the restore point would flashback the whole database.
That was no option. So Oracle of course have a solution recover a tablespace to a point in time.
and here's what i did

First as Oracle create an auxiliary database automaticaly i had to find a file system where the auxiliary database will reside. As that is the place where the system,sysaux,undo wil be restored i needed a file system with 300 gyg free space.
I created a new directory called /eco2mig/new.

now i could start the rman command to start the restore
RMAN> recover tablespace ANCD,ANCX until time "to_date('2013-04-25 23:00:00','YYYY-MM-DD HH24:MI:SS')" auxiliary destination '/eco2mig/new';
Oracle will create it's own spfile with a sga_target from 280M witch could cause a problem and that's the ORA-04030: out of process memory when trying to allocate. So it better to create a pfile yourself

As you see i use now the set auxiliary instance parameter file
recover tablespace are they 2 tablespace i want to restore to April 25 23:00
They will be restored at there original location.
At the auxiliary destination is where the system,sysaux and undo tablespaces will reside.
After completion everything will be removed in the auxiliary destination.