This blog has moved here.

Sunday, November 15, 2009

High Availability Guaranteed Restore Points

I like the flashback database feature introduced by Oracle 10g and especially the guaranteed restore points. We intended to use it on our 10g production database to create guaranteed restore points before major upgrades but without enabling flashback logging because we wanted to affect at least as possible the whole database performance. What really bothered me at the time was the fact that I couldn't create my first guaranteed restore point if the database was open which, in my opinion, affects the high availability goal. The solution to this was to already have at least one guaranteed restore point before creating the next ones with the database open, but this always felt like an ugly workaround.
So, the question is: what do you choose: the overhead of always having the flashback logging enabled or the downtime produced by the creation of a guaranteed restore point? Hmmm...
As many Oracle new features they seem to be a little bit unpolished when just launched but they tend to become better an better. The same here. I've just had a nice surprise to see that in 11gR2 (I don't know if it's also in R1) you can create guaranteed restore points with the database open, without flashback logging enabled and without any previous guaranteed restore points:

SQL> select name from v$restore_point;

no rows selected

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> create restore point before_upgrade guarantee flashback database;

Restore point created.

SQL> select name from v$restore_point;

NAME
---------------------------------------------------
BEFORE_UPGRADE


Nice and good to know.

No comments: