Guarantee restore point Oracle 11gr2

As of 11g we can create a restore point in the database
without having to set the database in flasback mode.

This can be very usefull during a change.
You create the restore poin before starting the change.
If for some reason you must rollback you can use
the restore point to flashback the database.
Which is much, much quicker then a restore of the database.

You have 2 choices a normal restore point or a guarantee
restore point.
If you use a normal restore point you can rewind the database
based on the value of the parameter db_flashback _retention_target.
deafult is 1 day.

A guaranteed restore point will never go out of the flashback area and
does not use the parameter db_flashback _retention_target.
It will never be deleted even if there is space pressure in the flashback area.
You must manually delete the guaranteed restore point to reclaim space in the
flashback area.

To create a normal restore point, you must have either
SELECT ANY DICTIONARY or FLASHBACK ANY TABLE privilege.
To create a guaranteed restore point, you must have the
SYSDBA system privileges.
To view or use a restore point, you must have the
SELECT ANY DICTIONARY or FLASHBACK ANY TABLE
system privilege or the SELECT_CATALOG_ROLE role.

oke lets start with creating a guaranteed restore point.

SQL> conn / as sysdba
SQL> create restore point demo1 guarantee flashback database;
Restore point created.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,SCN from v$restore_point
NAME       GUARANTEE                            SCN
---------- --------- ------------------------------
DEMO1      YES                          21093820677
SQL> @demo1.sql
select NAME,FLASHBACK_ON from v$database
NAME       FLASHBACK_ON
---------- ------------------------------------------------------
ODBA6      RESTORE POINT ONLY
select * from v$flashback_database_logfile
Session altered.

Log No Thread No Seq No NAME                                                                    Size (KB)             First Chg No FIRST_TIME
------ --------- ------ -------------------------------------------------- ------------------------------ ------------------------ --------------------------
     1         1      1 +DGOFRA1/odba6_01/flashback/log_1.1066.809360253                        268435456           21,093,820,661 06 MAR 2013 14:17:34
     2         1      1 +DGOFRA1/odba6_01/flashback/log_2.1005.809360257                        268435456                        0
     3         2      1 +DGOFRA1/odba6_01/flashback/log_3.1025.809360259                        268435456           21,093,820,671 06 MAR 2013 14:17:40
     4         2      1 +DGOFRA1/odba6_01/flashback/log_4.472.809360263                         268435456                        0

The flashback logs are created now by the background process RVWR
and are restored in the flashback area.

Now we make some changes to the database

 SQL> @demo3.sql
Table created.

Index created.
select count(*) from aap
                      COUNT(*)
------------------------------
                        100000

Now lets flasback the database to before the change

 SQL> flashback database to restore point demo1;
flashback database to restore point demo1
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
as you see the database must be in mount state.
let stop the database
SQL> !srvctl stop database -d ODBA6_01

start 1 instance srvctl start instance
SQL> !srvctl start instance -d ODBA6_01 -i ODBA61 -o mount
SQL> conn / as sysdba

Now flashback database to restore point demo1
SQL> flashback database to restore point demo1;
Flashback complete.
SQL> alter database open resetlogs;
start the seconde instance.
SQL !srvctl start instance -d ODBA6_01 -i ODBA62
SQL!srvctl status database -d odba6_01
Instance ODBA61 is running on node test2001
Instance ODBA62 is running on node test2002

SQL> select count(*) from aap;
select count(*) from aap
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

We flashback the database as it was before the change.

Now we have to drop the restore point as this is a guaranteed restore point
you must delete or otherwise it will exsist forever.

SQL> drop restore point demo1;
Restore point dropped.

You can also use rman for the whole process if you want.

RMAN> connect target /
connected to target database: ODBA6 (DBID=459400142)
RMAN> create restore point demo1 guarantee flashback database;
using target database control file instead of recovery catalog
RMAN> list restore point all;
SCN              RSP Time            Type       Time                Name
---------------- ------------------- ---------- ------------------- ----
21093823902                          GUARANTEED 06-03-2013:14:37:46 DEMO1

We do the change again and then the same when using
sqlplus to flashback the database it must be in the mount state;

$ srvctl stop database -d ODBA6_01

start 1 instance srvctl start instance
$srvctl start instance -d ODBA6_01 -i ODBA61 -o mount

RMAN> connect target /
RMAN> flashback database to restore point DEMO1;
Starting flashback at 04-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11instance=ODBA61 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished flashback at  04-MAR-13
RMAN> alter database open resetlogs;
database opened
RMAN> list restore point all;
SCN              RSP Time            Type       Time                Name
---------------- ------------------- ---------- ------------------- ----
21093823902                          GUARANTEED 06-03-2013:14:37:46 DEMO1
oke now we drop the restore point.

RMAN> drop restore point demo1
Thats it.


.