RDBMS-- FLASHBACK FEATURE -- a DEMO , Guaranteed Restore Points with or without Flashback Logging + Prereqs and Restrictions

Today, I will give you some key information about Oracle's Flashback technology. So, this post will be completely related with the database tier.
Flashback is not a new technology, I know.. It was introcuded in Oracle Database 10.1, however; it still saves our days, sometimes.

It has also some important restrictions and basically it has two modes. 1) the things we can do when the flashback logging is enabled 2) the things we can do when flashback logging is disabled.
Also, the disk consumption and the performance overhead changes according to the way we choose to use the flashback database feature.

The thing that made me write this post is, an upcoming upgrade operation (applications upgrade) in a mission critical Oracle RAC database.

The database size is too huge, so it not efficient to back up this database just before the upgrade ( both from rman perspective and storage perspective).
In addition, even if we can create a backup, the restore time is estimated to be very long and it can not be accepted by the business.

So in this situation, what we recommend? The flashback database feature.
The business and application guys just want to have the ability to restore the database to a specific point before the upgrade.. (in case of a failure)
So in this situation, what we recommend? A guranteed restore point "without" enabling the flashback logging. (Note that, flashback logging is enabled by the command -> alter database flashback on)

These recommendations are caused by the following facts;

• Flasback database feature is not only used for traditional recovery, but it can also be used when we need quick recovery. (scenarios like  database upgrades, application deployments and testing  when test databases must be quickly created and re-created)
• A guaranteed restore point ensures that you can use Flashback Database to rewind a database to its state at the restore point SCN, even if the generation of flashback logs is disabled.
• Flashback Database is much faster than point-in-time recovery because it does not require restoring datafiles from backup and requires applying fewer changes from the archived redo logs.

I will demostrate the things that I just mention above, but first; let's see the prereqs and restrictions of the Flashback feature, as well as,  those 2 different configurations that can be made for getting benefit the Flashback feature -- "GURANTEED RESTORE POINT without Enabling Flashback logging" vs "FLASHBACK LOGGING (or GURANTEED RESTORE POINT  with Flashback logging)" .
( I categorized them as 2, but they can be categorized into more categories if desired/needed)

PREREQS, RESTRICTIONS AND THE 2 TYPES OF USAGE OF FLASHBACK FEATURE

Let's start with the prerequisites.. I will just give a quick list of prereqs here..

• The database should be in archivelog mode.
• We must use the FRA. (db_recovery_file_dest and db_recovery_file_dest_size).. Because Flashback logs (.flb files) are created here.
• If needed, flashback logging must be enabled using "alter database flashback on" command , when the database is in mount mode.
• Our database must be Enterprise Edition.

Okay.. Let's continue with the Restrictions part;

• Flashback feature can not be used with the Standard Edition Oracle Databases.

I mean, flashback logs can be created  (tested with a guaranteed restore point), but we just can't restore our database to the restore points..

SQL> flashback database to restore point ERMAN_TEST;
flashback database to restore point ERMAN_TEST
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

Similarly, if we try to enable flashback logging  on a Standard Edition Oracle Database, we end up with this ->

In mount mode;

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

• If we don't enable flashback logging, then the first guaranteed restore point must be created in the mount state. Else, we get the following error;

SQL>CREATE RESTORE POINT ERMAN_TEST GUARANTEE FLASHBACK DATABASE
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'ERMAN_TEST'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when
flashback database is off.

Lastly, there is a list of restrictions, that I want to give;

• We cannot flash back to an SCN ahead of the current SCN
• When we restore using flashack, or database must be opened with RESETLOGS
• We just can not use Flashback Database to undo a shrink data file operation. (shrinking a data file or dropping a tablespace can prevent flashing back the affected data files)
• If we use Flashback Database to flashback our database to a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation.
• In order to flashback the database, it must be in mount state, else we get the following error -> ORA-38757: Database must be mounted and not open to FLASHBACK.

GURANTEED RESTORE POINT without Enabling Flashback logging vs FLASHBACK LOGGING (or GURANTEED RESTORE POINT with Enabling Flashback logging) :

In this subsection, I will give you the difference between a "flashback logging enabled Oracle Database" and "flashback logging disabled Oracle Database"

First of all, we can use the guaranteed restore point in any mode , both when flashback logging is enabled and when flashback logging is disabled.

The main difference is, without flashback logging, the modified blocks are only saved one time.

So, with flashback logging, modified blocks are saved every time, in every modification. (note that, flashback logging is enabled using "alter database flashback on")

This is because, if we enable flashback logging to be able to restore any SCN. (according to our flashback retention).

But if we don't enable flashback logging and create a guarenteed restore point, we can only restore to the SCN of the time that we created that guaranteed restore point.

Consider a scenario where the business and application guys want to do an upgrade and they want us to have the ability to restore the database to a specific point before the upgrade (in case their upgrade fails), then we use guaranteed restore point without flashback logging. (ofcourse we can also enable flashback logging, but it is unnecessary in this case)

But, if the business and application guys want us to have the ability to restore our database to any point of time between the start time of the upgrade and the end time of the upgrade, we enable flashback logging and create one or more guaranteed restore points and configure our flashback retention policies.

One last important info before continuing;

Each update will generate REDO and UNDO and each block in the UNDO tablespace that is used for the first time, will require flashback data to be written.  So with this in mind, we can say that; if our database is 100 Gb then potentially, the flashback data where only a guaranteed restore point is used, should be at most 100 Gb.

DEMO:

Let's make a demo and see if it is working :)

In this DEMO, I m doing the things below;

• Create a table named T.
• Load 100000 row into table T using a loop.
• Create a Guaranteed restore point in mount mode.
• Update those 100000 rows using a loop
• Restore the database to the Guaranteed restore point
• Drop te restore point.

I'm creating the restore point just before the table update, because I want to get back to the point where the update is not executed yet. 

• First, I start my database and create the table T.

SQL> startup
ORACLE instance started.
Database mounted.
Database opened.

SQL> create table t ( x int, y char(50) );

Table created.

• Then I load 100000 rows into the table T.

SQL> begin
for i in 1 .. 100000
loop
insert into t values ( i, 'x' );
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.
SQL> exit

• I check my FRA to see if there is any flashback logs created at this point. As I didn't enable Flashback logging and as I didn't create any Restore point yet, I don't expect to see any flashback logs there..

[oracle@prelive flashback]$ ls -lrth
total 0

• Then I put my database into mount mode and  create my guaranteed restore point as follows;(remember : Creating the first guaranteed restore point requires mount mode when flashback database is off)

SQL> shu immediaTE;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Database mounted.

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

• Now I check to see any flasback logs are created and I see a file is initialized..

SQL> !ls -lrt
total 15268
-rw-rw---- 1 oracle dba 15613952 Aug 15 15:11 o1_mf_ds5s89wn_.flb

• Next, I update all the rows in table T as follows.. I'm doing this to show you the generated flashback logs as a result of this update. 

SQL> alter database open; (remember, my db was in mount mode)
Database altered.

SQL> begin
for i in 1 .. 100000
loop
update t set y='y' where x=i;
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.

• Now I check the generated flashback logs both from DB and from the filesystem.

SQL> select flashback_size/1024/1024 MB from v$flashback_database_log;

MB
----------
44.6484375

SQL> !ls -lrth
total 45M
-rw-rw---- 1 oracle dba 15M Aug 15 15:15 o1_mf_ds5s89wn_.flb
-rw-rw---- 1 oracle dba 15M Aug 15 15:19 o1_mf_ds5shl52_.flb
-rw-rw---- 1 oracle dba 15M Aug 15 16:12 o1_mf_ds5spgkt_.flb

As you see 45 MB flashback log is created .. (table size was approxiametly 10-15 GB) .. This is because of  modified blocks + Undo data..

• Now, I put my database to the mount mode and flashback my database to my guaranteed restore point.

SQL> shu immediatE;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Database mounted.

SQL> flashback database to restore point ERMAN_TEST;

Flashback complete.

• I successfully issued my flashback command and now, it is time to open the database, but database must be opened using the resetlogs.. (Else , I get "ORA-01589: must use RESETLOGS or NORESETLOGS option for database open" )

SQL> alter database open resetlogs;
Database altered.

• Now I check table T and see if my update is reverted back.

SQL> select * from t where y='y';
no rows selected

Yes. As if it never happened :)

• Lastly, I drop my restore point and see the relevant flashback logs are cleared from Flash Recovery Area.

SQL> drop restore point ERMAN_TEST;
Restore point dropped.

SQL> exit

[oracle@prelive flashback]$ ls -lrt

total 0 ---CLEAN!

That's all :) Nice feature and it is working like charm :)