Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, August 11, 2015

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

No comments: