Author: Rafael Nólio

Hi everybody,

Today we are going to discuss about FLASHBACK TABLE. As usual, first, I am tagging here the previous posts about Flashback Technology, so feel free to check it out if you want:

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

 

So let’s do it people.

More“Flashback – Part 5 (Flashback Table)”

Hi all,

If you have missed the previous Flashback posts, please go through these links to find it and read them if you feel like!

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

And now, we are half way there to the end of the Flashback posts, let’s see a little more about FLASHBACK TRANSACTION QUERY.

More“Flashback – Part 4 (Flashback Transaction Query)”

Hi Everyone,

Here we are to continue our Flashback Saga! If you lost our first 2 posts about that and are in the mood for a good reading, please go through the links below:

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

 

Today we are going to discuss Flashback Versions Query, which has a strong link with the previous post, the Flashback Query (AS OF). With this feature, we are able to verify all changes made  between 2 points in time in the past, using SCN or a Timestamp. Of course, the Flashback Versions Query will retrieve only the committed data. Just like Flashback Query, the Flashback Versions Query is undo-based, so make sure your undo Tablespace and undo retention period is good enough for you.

More“Flashback- Part 3 (Flashback Versions Query)”

Hey team,

This is the second part of our Flashback Tutorial and today we’re gonna talk about FLASHBACK QUERY. Please check below for the full serie:

Flashback – Part 1 (Flashback Drop)
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)

Let’s go:

FLASHBACK QUERY

In the last Flashback post, we learnt about restoring tables that were dropped from the database with the RecycleBin facility. But if you think about it, it’s way more likely that a table suffer an undesirable change, than actually be dropped. Example, when you UPDATE a table with values that are not correct, or delete values (and commit, of course), and so on, wouldn’t it be great if we could come back in the past and see how it was before the change? Thanks to the almighty Oracle Database we can! We can use the Flashback Query to see how a table was at a specific time in the past. And the best part of it, is if you are the owner of your table, you can do it yourself, no need to bother the DBA with that (definetely the best part), and you can correct your own mistakes. Also, please keep in mind that for FLASHBACK QUERY to work, we need to have our undo properly configured. To illustrate that, let’s see an example:

Let’s create our same old table:

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

Table created.

Then, let’s insert some values on it:

SQL> insert into grepora values ('value1', 'value2', 'value3');

1 row created.

SQL> insert into grepora values ('line2', 'line2', 'line2');

1 row created.

SQL> insert into grepora values ('line3', 'line3', 'line3');

1 row created.

SQL> insert into grepora values ('line4', 'line4', 'line4');

1 row created.

SQL> insert into grepora values ('line5', 'line5', 'line5');

1 row created.

SQL> commit;

See how the table is at the moment:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
value1 value2 value3
line2  line2  line2
line3  line3  line3
line4  line4  line4
line5  line5  line5

Get the SYSDATE, to know the exact date where you have this amount of data:

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> 
SQL> select sysdate from dual;

SYSDATE
-------------------
20/06/2016 16:36:07

Now, let’s make some “mistakes” here, try to change the content of the table, deleting and updating values:

SQL> delete from grepora where column1='line5';

1 row deleted.

SQL> update grepora set column1='line1', column2='line1', column3='line1' where column1='value1';

1 row updated.

SQL> commit;

Commit complete.

And see how the table is right now:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
line1  line1  line1
line2  line2  line2
line3  line3  line3
line4  line4  line4

Check that the content data of the table is different from the original version after our changes. How can we revert that if we didn’t know how it was before that?

We use the famous AS OF TIMESTAMP statement, which allow us to see the table in a different time in the past.

With the example below, check that after using the clause AS OF TIMESTAMP and using the date we caught before to DML our table, we can find the same previous data:

SQL> select * from grepora as of timestamp (to_timestamp('20/06/2016 16:36:07', 'dd/mm/yyyy hh24:mi:ss'));

COLUMN COLUMN COLUMN
------ ------ ------
value1 value2 value3
line2  line2  line2
line3  line3  line3
line4  line4  line4
line5  line5  line5

And the current version:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
line1  line1  line1
line2  line2  line2
line3  line3  line3
line4  line4  line4

With this feature, we can see how a table was “before the mistake” and do the proper actions to fix it.

I hope it was clear to everyone, if you have any doubt, please get in touch with GrepOra and we’ll be glad to help.

For the next post, we’ll be doing a test case for FLASHBACK VERSIONS QUERY! Stay Tuned!

Rafael.

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.