Flashback Recovery

A new feature in Oracle 10g. With Oracle 10g, flashback database enables to rollback the database either in full restore or partially to a specific point-in-time. Flashback feature is an easy and fast way to restore the database to a previous state without hassle.

This feature in Oracle 9i was called a flashback query. Restore is dependant on the size of the undo tablespace retention period. The bigger the undo area the longer Oracle keeps data.

Basic implementation of flashback recovery.

1) Enable archivelog:

SQL> startup mount;
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17


2) Setting required parameters:

* mounted mode

SQL> alter system set db_flashback_retention_target=1200;

System altered.

SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0\flash
_recovery_area
db_recovery_file_dest_size big integer 2G

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

- db_flashback_retention_target = seconds the flashback data is kept.
- db_recovery_file_dest_size = size of the flashback data.
- db_recovery_file_dest = destination of the flashback data.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES


3) Verifying the flashback statistics:

SQL> desc v$flashback_database_log;
Name Null? Type
----------------------------------------- -------- ----------------------------
OLDEST_FLASHBACK_SCN NUMBER
OLDEST_FLASHBACK_TIME DATE
RETENTION_TARGET NUMBER
FLASHBACK_SIZE NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
0

4) Testing the flashback recovery:

creating a tablespace:
SQL> create tablespace test datafile
2 'd:\oracle\product\10.2.0\oradata\TIME\t1.dbf' size 1m
3 extent management local uniform size 100k;

Tablespace created

creating user:
SQL> create user flash identified by back
2 default tablespace test
3 quota 200k on test
4 temporary tablespace temp;

User created

SQL> grant connect, resource to flash;

Grant succeeded.







5) Creating a table:

SQL> conn flash/back
Connected.

SQL> create table time (start_tag NUMBER(5), end_tag NUMBER(5));

Table created.

SQL> insert into time VALUES ('1.001','1.010');

1 row created.

SQL> insert into time VALUES ('1.003','1.010');

1 row created.

SQL> commit;

Commit complete.

SQL> drop table time;

Table dropped.

SQL> desc time;
ERROR:
ORA-04043: object time does not exist


6) Running flashback on table level:

SQL> flashback table "TIME" to before drop;

Flashback complete.

SQL> desc time;
Name Null? Type
----------------------------------------- -------- ----------------------------
START_TAG NUMBER(5)
END_TAG NUMBER(5)

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
144445440


7) Running flashback database recovery:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 71304572 bytes
Database Buffers 88080384 bytes
Redo Buffers 7139328 bytes
Database mounted.

SQL> flashback database to timestamp to_timestamp('23-08-2007 01:59', 'DD-MM-YYYY HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select username from user_users where username='FLASH';

no rows selected

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
0