Flashback Database setup in Oracle

Enable Flash back on :

Starting from Oracle 11g R2 we don't have to bounce the Database for these effects to get affected. If you are using pre 11G R2, you have to re bounce your system for these settings to get affected.

[oracle@Linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 4 10:25:05 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


Lets check if Flash back is enabled or not :

SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON       STATUS
------------------ ------------
NO                 OPEN

Since it is not enabled. Lets turn flash back ON:

SQL> alter database flashback on;

Database altered.


SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON       STATUS
------------------ ------------
YES                OPEN

Confirm the size of DB_RECOVERY_FILE_DEST_SIZE (Better keep it at least 30GB+ until it is Dev or Test environment)

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 20000M

I want to set it to 30GB:

SQL> alter system set db_recovery_file_dest_size=30G  ;

System altered.

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 30G

Lets verify the location of recovery area. This is where the flashback logs are stored:

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 30G

You can reset the location by simple command :

 alter system set db_recovery_file_dest= '/u01/app/oracle/flash_recovery/oraflash';


 Lets confirm the retention period :

SQL> show parameter db_flashback_retention_target

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

You can Change the retention period :

sql > alter system set db_flashback_retention_target=2880;  -- 2days

Note : This retention period is in minutes so 1440/60 = 24 hours. 




If you have a standby Database configured:
Activating flashback logging on the standby database Flashback operations, such as activating or accessing restore points, can only be undertaken in the MOUNT stage of the database. An active recovery will also first have to be canceled.


Manual method First cancel the recovery on the standby database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Then activate Flashback:

 ALTER DATABASE FLASHBACK ON;

Then restart the recovery: 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Deactivating To deactivate flashback mode, use the command:

 ALTER DATABASE FLASHBACK OFF; 

Recover data using Flashback Query

How to restore the old data using flashback query
My intention is , I want to get back past data of database after erroneously updated and committed.

We know that committed data can never be flashed back. But with 10g new flashback feature we can get back past data even they are committed. 

Before proceed ensure that,

•The UNDO_RETENTION initialization parameter is set to a value so that you can back your data far in the past that you might want to query.

•UNDO_MANAGEMENT is set to AUTO.

•In your UNDO TABLESPACE you have enough space.

With an example I will demonstrate the whole procedure.

1)I have created a table named test_flash_table with column name and salary.

SQL> create table test_flash_table(name varchar2(10), salary number);
Table created.

SQL> insert into test_flash_table values('ABCD',10);
1 row created.

SQL> commit;
Commit complete.

The table contains one row.

2)I erroneously updated column salary of Arju and commited data.

SQL> update test_flash_table set salary=20 where name='ABCD';
1 row updated.

SQL> commit;
Commit complete.

3)After some moments I found that I have made wrong update. Now be sure to query. Also select that time SCN by TIMESTAMP_TO_SCN.

SQL> select name, salary,systimestamp, TIMESTAMP_TO_SCN(SYSTIMESTAMP-interval '8' minute) SCN from test_flash_table as of timestamp (SYSTIMESTAMP-interval '8' Minute);

NAME SALARY SYSTIMESTAMP SCN
---------- ---------- ---------------------------------------- ----------
ABCD 10 29-APR-12 12.34.03.452330 AM -04:00 869222

4)Now update the data based on the SCN.

SQL> update test_flash_table set salary=(select salary from test_flash_table as of scn 869222 where name='ABCD') where name='ABCD';
1 row updated.

SQL> select * from test_flash_table where name='Arju';
NAME SALARY
---------- ----------
ABCD 10