DB - Flashback Database

EnglishVersion

This post is about using flashback technology for recover a database, using as example, how to recover a table after truncate.

Truncate is used to clean the tables, it also cleans the table segments.
To recover trucated data requires to .. recover the database.

Important note : flashback database is a must in case of failover in data guard operations.

Flashback requirements

It have some requirements for flashback database
- The DB needs to be in archive log mode -the traditional archive redo logs
- Feature flashback ON - it will activate the stream the logs / flashback logs

For all this work the database needs to be mounted in a consitant maner
To help with demo I will also create a RESTORE POINT with GUARANTEE FLASHBACK DATABASE

-- As '/ as sysdba',

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 499125840 bytes
Database Buffers 343932928 bytes
Redo Buffers 5132288 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open ;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> CREATE RESTORE POINT before_truncate GUARANTEE FLASHBACK DATABASE;
Restore point created.

Truncate the Table

I will connect as HR demo user and reuse the table regions_4_query created for the DB - Flashback Query post

-- As 'developer',

SQL> conn hr/hr
Connected.

SQL> select * from hr.regions_4_query;

REGION_ID REGION_NAME
---------- -------------------------
2 Americas
3 Asia
4 Middle East and Africa
1 Europe

SQL> truncate table regions_4_query;
Table truncated.

SQL> select * from regions_4_query;
no rows selected

Recover the Table's Data

After truncate the data - the developer noticed was not great idea .. he needs the data back
As DBA it has two options the tradional restore/recover or the flashback the database.
In bellow in this post are the commands for the second option

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Database mounted.

SQL> flashback database to restore point BEFORE_TRUNCATE;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

SQL> select * from hr.regions_4_query;

REGION_ID REGION_NAME
---------- -------------------------
2 Americas
3 Asia
4 Middle East and Africa
1 Europe

Thank you for reading.
hope this post was helpful. Rogerio