TSPITR is a concept that has been around for many years (i think since 8i) however it was very much a manual task.
This has improved greatly over time and has been fully integrated into rman to allow an automatic recovery of a tablespace to an earlier point in time using the transportable tablespace feature.
So by simply running a command similar to the one below directly in the database in question
recover tablespace TESTTBS until time "to_date('11-jan-2014 16:45:02','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '/oracle/common/oradata/AUX';
We can restore just that tablespace back to an earlier point in time without affecting the rest of the database - this is very neat but shouldn't be directly possible in a single database - and indeed it isn't - rman is just being very clever.
What actually happens is that a brand new database is created automatically (same DB_NAME but different sid/db_unique_name) - a cut down version of the original database is restored to the correct point in time (by cut down i mean just system/sysaux/undo and the target tablespace - everything else is skipped).
Once this other database is at the correct point, an automatic transportable tablespace is done to copy the file and the metadata from the new temporary database to the original database.
This does of course have all the restictions of a normal transportable tablespace operation (mainly the fact that the tablespace has to be self contained - which is pretty obvious - otherwise it won't be in sync with other objects in other tablespace that it relates to).
The command does a huge amount of work and its quite impressive to scroll back through the command output and see what is actually did.
However in the case where I've just had to use this there was a bug and I would imagine it's quite common. If you have a database with a lot of stuff in it then the default memory sizes for the temporary instance are not big enough and the defaults have to be increased. This can't be done in the command line but is done by creating a parameter file in a default location with changed settings.
In my case the shared pool was too small so i added this to this file
$ORACLE_HOME/rdbms/admin/params_auxinst.ora
shared_pool_size=1G
I then redid the operation but it failed with this
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/18/2014 09:46:45
RMAN-04014: startup failed: ORA-01078: failure in processing system parameters
RMAN-04017: startup error description: ORA-00821: Specified value of sga_target 288M is too small, needs to be at least 1152M
so it took my shared pool size but that then conflicted with sga_target
i then also added
sga_target=2G
re-ran everything again and this time it all worked fine.
Comments
Post a Comment