11gR2 Flashback & restore point

Turn on flashback in 11gR2 database and create a restore point.

Stop you Ebiz apps if any:( Not needed but to have clean one before we start our testing)

adstpall.sh:Exiting with status 0



SQL> alter database flashback on;

Database altered.

SQL> SELECT flashback_on, log_mode FROM v$database;

FLASHBACK_ON LOG_MODE
------------------ ------------
YES ARCHIVELOG

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

no rows selected

SQL> show parameter flashback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440

change the retention period to 24 hrs ---

SQL> alter system set db_flashback_retention_target=86400 scope=both sid='*';

System altered.

SQL> show parameter flashback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 86400


SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

no rows selected

create a retore point here:

SQL> create restore point benchmark_10202011;

Restore point created.


SQL> set linesize 121
col name format a15
col time format a32SQL> SQL>
SQL>
SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------- ---------- -------------------------------- --------------------- --- ------------
BENCHMARK_10202 1.2476E+13 20-OCT-11 02.54.56.000000000 PM 2 NO 0
011

BENCHMARK_10202 1.2476E+13 20-OCT-11 02.54.56.000000000 PM 2 NO 0
011


SQL> alter session set nls_date_format='mm/dd/yyy hh24:mi:ss';

Session altered.

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------- ---------- -------------------------------- --------------------- --- ------------
BENCHMARK_10202 1.2476E+13 20-OCT-11 02.54.56.000000000 PM 2 NO 0
011

BENCHMARK_10202 1.2476E+13 20-OCT-11 02.54.56.000000000 PM 2 NO 0
011


SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.


SQL> drop restore point benchmark_10202011;

Restore point dropped.

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

no rows selected

SQL> create restore point benchmark_QC_10202011 guarantee flashback database;

Restore point created.

SQL> col name format a25
SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
------------------------- ---------- -------------------------------- --------------------- --- ------------
BENCHMARK_QC_10202011 1.2476E+13 20-OCT-11 03.00.59.000000000 PM 2 YES 67125248



-- Going back to the defined restore point

SQL>conn / as sysdba

SQL>shutdown immediate;

SQL>startup mount;

SQL>flashback database to restore point BENCHMARK_QC_10202011;

SQL>alter database open resetlogs;

gud luck.