Oracle Flashback database

Enable flashback:

SQL> select name,open_mode,flashback_on from v$database;

NAME                    OPEN_MODE          FLASHBACK_ON

---------------------------------------- -------------------- ------------------

ORCL                    READ WRITE          NO

SQL> alter database flashback on;

Database altered.

SQL> select name,open_mode,flashback_on from v$database;

NAME                    OPEN_MODE          FLASHBACK_ON

---------------------------------------- -------------------- ------------------

ORCL                    READ WRITE          YES

SQL> alter database add supplemental log data;

Database altered.

SQL> col name for a30

SQL> select * from v$restore_point;

no rows selected

SQL> create restore point before_truncate guarantee flashback database;

Restore point created.

SQL> select * from v$restore_point;

      SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME

---------- --------------------- --- ------------ ---------------------------------------------------------------------------

RESTORE_POINT_TIME                               PRE NAME

--------------------------------------------------------------------------- --- ------------------------------

  1095503              3 YES      8192000 05-AUG-15 02.30.32.000000000 PM

                                      YES BEFORE_TRUNCATE

SQL> select count(*) from hr.job_history;

 COUNT(*)

----------

   10

SQL> truncate table hr.job_history;

Table truncated.

SQL> select count(*) from hr.job_history;

 COUNT(*)

----------

    0

SQL> flashback database to restore point before_truncate;

flashback database to restore point before_truncate

*

ERROR at line 1:

ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  430075904 bytes

Fixed Size           2214056 bytes

Variable Size         406849368 bytes

Database Buffers       16777216 bytes

Redo Buffers           4235264 bytes

Database mounted.

SQL> flashback database to restore point before_truncate;

Flashback complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from hr.job_history;

 COUNT(*)

----------

   10