Flashback- Part 1 (Flashback Drop)

Hi everyone!

This is the first post of a serie. Check below for the other articles in this serie:

Flashback – Part 2 (Flashback Query)
Flashback – Part 3 (Flashback Versions Query)
Flashback – Part 4 (Flashback Transaction Query)
Flashback – Part 5 (Flashback Table)
Flashback – Part 6 (Flashback Database)
Flashback – Part 7 (Flashback Data Archive)

Flashback is a technology that becomes handy to the DBA when you need to recover the database from logical issues, and it is considered a great feature to use for recovery scenarios, besides RMAN. Comparing with Recovery Manager (RMAN), Flashback is way simpler mode to recover from logical issues (end users, most of the time), when RMAN is better for physical issues. These issues can be like:

• DELETE operation with a wrong WHERE clause;
• A table mistakenly DROPPED;
• Wrong UPDATE commands;
• Flashback the whole database, to a time in the the past.

And so on… The scenarios are plenty. So in order to understand each of them better, we’ll explain in details, separately, in different posts, so we don’t get tired of reading that much 🙂

The Flashback Types are:

1. Flashback Drop
2. Flashback Query
3. Flashback Versions Query
4. Flashback Transactions Query
5. Flashback Table
6. Flashback Database
7. Flashback Data Archive

For this Part 1, we’ll discuss about item 1 only, and in the next posts we will continue this saga!

Most of the flashback operations are undo-based, so its up to the DBA to set up a good retention based on his own environment. The steps are:

• Create the UNDO tablespace
• Set the undo_retention good enough for your needs
• Configure the tablespace to be auto-extend

Okay then, enough with the talking and let’s go right to the point.

FLASHBACK DROP

To perform Flashback Drop operations, we must have the RecycleBin enabled on the database. To make sure that your RecycleBin is enable, you can check as:

SQL> show parameter recyclebin

NAME      TYPE  VALUE
----------------------- ----------- ------------------------------
recyclebin      string  on

This feature allow us to restore a table that was accidentally dropped, using the RecybleBin as a source. RecybleBin is basically where your tables and associated objects (such as indexes, constraints, triggers, etc…) are sent when they are dropped (yes, they are still in the database somehow, even if you have dropped them). The Flashback Drop is capable of restoring dropped tables based on the RecycleBin. Ok GrepOra, but for how long will we gonna have the dropped objects available on the RecycleBin? They remain available until someone purge it explicitly or due to space pressure.

Here is an example of FLASHBACK DROP operation:

Create table:

SQL> CREATE TABLE grepora
( column1 VARCHAR2(30),
 column2 VARCHAR2(40),
 4 column3 VARCHAR2(20) )
 5 TABLESPACE users;

Table created.

Then drop the table:

SQL> drop table grepora;

Table dropped.

Check in the RecycleBin, with the following command, the dropped table:

SQL> select original_name, object_name, type, droptime from user_recyclebin where original_name='GREPORA';

ORIGINAL_N OBJECT_NAME   TYPE DROPTIME
---------- ------------------------------ ------ -------------------
GREPORA    BIN$NRwjojcna3XgUzvONgooCA==$0 TABLE  2016-06-12:16:06:01

Please, have a look at the OBJECT_NAME column, which now it contains the current name of the dropped table in the database, and the column ORIGINAL_NAME shows the name as it was before the drop. This happens because we can have an object with the same name created and dropped different times, so we can have all its versions available in case we need a specific one.

To prove this is real, we can simply query the dropped table using the RecycleBin’s name:

SQL> select count(*) from "BIN$NRwjojcna3XgUzvONgooCA==$0";

  COUNT(*)
----------
0

Now we have to actually use the flashback command to restore the dropped table and make it available again with the right name. To do that, we have some different ways.

Note: In case we have different versions of the table with the same name on the RecycleBin, Oracle will always choose the most recent one. If you want to restore an older version, you should use the OBJECT_NAME for the operation. 

Examples:

SQL> flashback table grepora to before drop;

Flashback complete.

SQL> select count(*) from grepora;

  COUNT(*)
---------
0

In the example above, we have successfully restored the GREPORA table using its ORIGINAL_NAME.  But what if we had different versions of the same table? 

First, let’s drop the table that we have restored, and check it on the RecycleBin.

SQL> drop table grepora;

Table dropped.

SQL> select original_name, object_name, type, droptime from user_recyclebin where original_name='GREPORA';

ORIGINAL_N OBJECT_NAME   TYPE DROPTIME
---------- ------------------------------ ------ -------------------
GREPORA    BIN$NRxYdbc4hpjgUzvONgrFng==$0 TABLE  2016-06-12:16:20:48

Create the table again, using the same DDL, and then drop it:

SQL> CREATE TABLE grepora
( column1          VARCHAR2(30),
  column2        VARCHAR2(40),
  column3          VARCHAR2(20) )
  5  TABLESPACE users;

Table created.

SQL> drop table grepora;

Table dropped.

Check the RecycleBin. We will find the two versions of our table, in different times.

SQL> select original_name, object_name, type, droptime from user_recyclebin where original_name='GREPORA';

ORIGINAL_N OBJECT_NAME   TYPE DROPTIME
---------- ------------------------------ ------ -------------------
GREPORA    BIN$NRxYdbc4hpjgUzvONgrFng==$0 TABLE  2016-06-12:16:20:48
GREPORA    BIN$NRxYdbc5hpjgUzvONgrFng==$0 TABLE  2016-06-12:16:21:41

Check that the ORIGINAL_NAME for both lines are the same. Now we can flashback any version of the same table, using the OBJECT_NAME:

SQL> flashback table "BIN$NRxYdbc4hpjgUzvONgrFng==$0" to before drop;

Flashback complete.

As we still have the other table and want to restore it as well, we obviously cannot have the same name for both of them, so we can restore it with the RENAME TO clause:

SQL> flashback table "BIN$NRxYdbc5hpjgUzvONgrFng==$0" to before drop rename to grepora_2;

Flashback complete.

And now we have both versions available to the database:

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
GREPORA_2
GREPORA

Please stay tuned for the next Flashback Posts upcoming! We’ll cover it all. I hope it was all clear to everyone. Thanks for reading and have a wonderful week!

Rafael.