Menu Bar

Saturday, November 24, 2012

FLASHBACK Technology in Oracle Databases (Part 2)


"By the way, did I mention it also travels in time?" - The Ninth Doctor, referring to the TARDIS
Last time we talked about using Flashback technology to run queries against our database as it looked in the recent past.

This can certainly be useful, but what if it just isn't enough. What if we need to get back a table we just accidentally dropped, or recover a data screw up so bad that recovery is our only option.

There are other aspects to Flashback technology that let us do just that.

What we looked at last time would be covered under the topic sub-titles "Flashback Query" and "Flashback Version Query". What other options do we have available to us?

Looking into the heart of our TARDIS (refer to Part 1 for the introduction of the Oracle TARDIS), we find the following options awaiting us:

  • Flashback Database - a physical level restore that restores the entire database to a specific point in time using flashback logs rather than doing a database recovery and rolling archive logs forward
  • Flashback Table - recover a table to a point in time
  • Flashback Drop - restores an accidentally dropped table as well as all related indexes, triggers and constraints
  • Flashback Transaction - rollback a single transaction and optionally, all transactions dependent upon the rolled back transaction
  • Flashback Transaction Query - see all the changes made by a specific transaction

Today we'll be talking about the first three on that list (we'll deal with Flashback Transaction in part 3 of the series).

Flashback Database

Let's start with Flashback Database. Much like a database recovery, Flashback Database is a physical level recovery, much like a database point in time recovery, up to and including the OPEN RESETLOGS at the end, but usually much faster because the initial recovery of the data files from backup isn't needed.

In order to use Flashback Database, the Fast Recovery Area needs to be configured in the database that is a candidate for Flashback.

This means that the db_recovery_file_dest and db_recovery_file_dest_size initialization parameters need to be set. Furthermore, in order to enable Flashback Database, db_flashback_retention_target also needs to be set. This parameter the number of minutes we can go back into the past and perform a Flashback Database.

Once this is configured, we must also make sure to issue ALTER DATABASE FLASHBACK ON.

What this will do is to start writing images of data blocks to the fast recovery area. The flashback logs are written sequentially and often in bulk. The database automatically creates, deletes and resizes these logs as needed. These logs are not archived.

The only thing that the DBA really needs to know about for the flashback logs is that they exist (from a sizing of the fast recovery area point of view) and that they are adding a performance overhead to the database.

The database also needs to be in Archivelog Mode for this functionality to work.

When doing a Flashback Database, the database uses information in these logs, as well as some information from archive logs, in order to perform the requested operation. It is because of this that you can't enable flashback after a failure and expect that you can flashback your database to before the failure.

Similar to the syntax used in Flashback Query, there are several options you can apply to Flashback Database:

  • TO SCN
  • TO SEQUENCE
  • TO TIMESTAMP
  • TO RESTORE POINT

Each of these also supports a TO BEFORE construct as well. Doing FLASHBACK TO will recover the database right up until the parameter specified. Doing FLASHBACK TO BEFORE will recover the database right up until immediately before the parameter specified.

Demo Time

First things first, let's check that our database is set up correctly:


SQL> show parameter db_recovery

NAME                        TYPE        VALUE                          
--------------------------- ----------- ------------------------------ 
db_recovery_file_dest       string      c:\oracle\flash_recovery_area  
db_recovery_file_dest_size  big integer 4977M                          

SQL> show parameter flashback;

NAME                          TYPE        VALUE
----------------------------- ----------- -----
db_flashback_retention_target integer     1440

SQL> select flashback_on from v$database;

FLASHBACK_ON                                               
------------------                                               
NO                                                               

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

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

SQL> select instance_name, archiver from v$instance;

INSTANCE_NAME    ARCHIVE                              
---------------- -------                              
demo             STARTED

Next, let's check the status of the database as we left it after Part 1, then add a new table and populate it with some related data.


SQL> conn demo/demo
Connected.
SQL> alter session set nls_date_format = 'YYYY-MM-DD:HH24:MI:SS';

Session altered.

SQL> select table_name from user_tables;

TABLE_NAME                                                    
---------------                                               
PREZ                                                          

SQL> desc prez
 Name                                      Null?    Type 
------------------------------------------ -------- -----------
 NAME                                      VARCHAR2(25)
 ELECTED                                   VARCHAR2(4)

SQL> select * from prez;

NAME                      ELEC                     
------------------------- ----                                               
Barack Obama              2008                     
George W. Bush            2013                     
Bill Clinton              2013                                           
George H. W. Bush         2013                     
Ronald Reagan             2013                                           

SQL> select sysdate from dual;

SYSDATE                                                           
-------------------                   
2012-11-24:22:02:36                                               

SQL> create table viceprez (name varchar2(25), elected varchar2(4));

Table created.

SQL> insert into viceprez values ('Joe Biden', '2008');

1 row created.

SQL> insert into viceprez values ('Dick Cheney', '2000');

1 row created.

SQL> insert into viceprez values ('Al Gore', '1992');

1 row created.

SQL> insert into viceprez values ('Dan Quayle', '1988');

1 row created.

SQL> insert into viceprez values ('George H. W. Bush', '1980');

1 row created.

SQL> select * from viceprez;

NAME                      ELEC                                    
------------------------- ----                                           
Joe Biden                 2008                     
Dick Cheney               2000                     
Al Gore                   1992                     
Dan Quayle                1988                     
George H. W. Bush         1980                     

SQL> select sysdate from dual;

SYSDATE                                                                      
-------------------                                                          
2012-11-24:22:09:07                                                          

SQL> quit

Note the time stamp here. At the time that the timestamp is shown the rows have not been committed to the database. The commit does not happen until we quit from SQL*Plus, where the commit happens implicitly.

So, a user gets into a database and starts deleting rows. They suddenly realize that they are in prod and not in test!

The DBA comes to the rescue (after first realizing that he must shut down the database in order to perform the flashback).

I've also included listings here showing the current incarnation of the database from RMAN between steps.


C:\>rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DEMO     3543868088       CURRENT 1          19-NOV-12

RMAN>quit

C:\> sqlplus demo/demo
Connected.

SQL> delete from viceprez where elected< 2000;

3 rows deleted.

SQL> delete from prez where elected = 2013;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008

SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Joe Biden                 2008
Dick Cheney               2000

SQL>connect / as sysdba
Connected.


SQL> flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS');
flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS')
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK. 

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

SQL> quit

C:\> sqlplus / as sysdba

SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes                                       
Fixed Size                  1375792 bytes                                       
Variable Size             415236560 bytes                                       
Database Buffers          113246208 bytes                                       
Redo Buffers                5804032 bytes                                       
Database mounted.
SQL> flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> connect demo/demo
Connected.

SQL> select * from prez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Barack Obama              2008                                                  
George W. Bush            2013                                                  
Bill Clinton              2013                                                  
George H. W. Bush         2013                                                  
Ronald Reagan             2013                                                  

SQL> select * from viceprez;

no rows selected

SQL> quit

C:\>rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DEMO     3543868088       PARENT  1          19-NOV-12
2       2       DEMO     3543868088       CURRENT 849611     24-NOV-12

RMAN>quit

This is where the earlier comment about the placement of the commit came into play (and also ties neatly into my earlier post this month about Transaction Isolation Levels in Oracle). Because the changes had not been committed at the timestamp specified, when the flashback database was performed, those rows are missing from the resultant database.

Let's assume that we now go back and recreate the data in the VICEPREZ table and that our same user goes and deletes the exact same records again. We'll pick it up at the delete and the subsequent rescue by the DBA:


SQL> select sysdate from dual;

SYSDATE                                                                         
-------------------                                                             
2012-11-24:22:22:11                                                             

SQL> delete from viceprez where elected < '2000';

3 rows deleted.

SQL> delete from prez where elected = '2013';

4 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008

SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Joe Biden                 2008
Dick Cheney               2000

SQL> connect / as sysdba
Connected.

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

Total System Global Area  535662592 bytes                                       
Fixed Size                  1375792 bytes                                       
Variable Size             415236560 bytes                                       
Database Buffers          113246208 bytes                                       
Redo Buffers                5804032 bytes                                       
Database mounted.
SQL> flashback database to timestamp to_timestamp('2012-11-24:22:22:11','YYYY-MM-DD:HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> connect demo/demo
Connected.

SQL> select * from prez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Barack Obama              2008                                                  
George W. Bush            2013                                                  
Bill Clinton              2013                                                  
George H. W. Bush         2013                                                  
Ronald Reagan             2013                                                  

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

SQL> quit

C:\>rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DEMO     3543868088       PARENT  1          19-NOV-12
2       2       DEMO     3543868088       PARENT  849611     24-NOV-12
3       3       DEMO     3543868088       CURRENT 849931     24-NOV-12

RMAN>

The DBA is the hero of the hour, having managed to successfully recover the database back to pre-delete state without the large time investment of a complete database restore.

Of course, such functionality needs to be used with great care in a production environment to make sure that the rest of the data you lose isn't more of a pain than the data you've already lost.

One scenario that immediately comes to mind for the use of something like this would be in a large Enterprise application where Payroll is one of the subsystems in the database. The developers in this theoretical shop are in the process of making major functional changes to the code. They want to test on as close to live data as possible so they request a refresh of the test database.

Being a large Enterprise level database, it takes 2-3 hours to restore the test server from backup. The developers run their test payroll but it didn't work. They need another fresh copy of the production database in order to run their test again.

Before Flashback Database, another 2-3 hours would be wasted waiting for the refresh to complete.

If the DBA setup the test environment for Flashback and let the developers do their testing, if they needed a fresh copy of the database in test, a quick Flashback Database later and they have a fresh copy of the data in a fraction of the time.

Flashback Table

Our next scenario is for Flashback Table. With this feature you can flashback just one table in the database to a particular SCN, TIMESTAMP or RESTORE POINT. Creating a restore point is as simple as:


SQL> create restore point flashback_table;

Restore point created.

Again, let's assume that someone has run rampant over some table in the database. The table is stand alone, with no other dependencies on other objects, so it has been determined that it's safe to roll this one table back to a point in time in the past.

Demo Time


SQL> conn demo/demo
Connected.
SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Joe Biden                 2008
Dick Cheney               2000
Al Gore                   1992
Dan Quayle                1988
George H. W. Bush         1980

SQL> select sysdate from dual;

SYSDATE            
-------------------
2012-11-24:22:32:14

SQL> delete from viceprez where elected = '2008';

1 row deleted.

SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Dick Cheney               2000
Al Gore                   1992
Dan Quayle                1988
George H. W. Bush         1980

SQL> flashback table viceprez to restore point flashback_table;
flashback table viceprez to restore point flashback_table
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled 

What does that mean? Row movement is a setting that tells Oracle it's OK to generate new ROWID's for rows being recovered in this manner (since there's no guarantee that the old ROWID is still unique).

Fortunately, this is a setting that can be enabled after the fact!


SQL> alter table viceprez enable row movement;

Table altered.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

SQL> flashback table viceprez to restore point flashback_table;

Flashback complete.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

Flashback Drop

For our last scenario, we're going to look at recovering a table that has been dropped.

In this scenario (ignoring the fact that a user shouldn't have drop rights on a table anyway), a user inserts a row into the table, realizes their error and attempts to remove it. However, instead of removing the row, they drop the table in error.

The DBA puts on his red cape and once again comes to the rescue!

Demo Time


SQL> insert into viceprez values ('The Man in the Moon', '2013');

1 row created.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  
The Man in the Moon       2013                                                  

6 rows selected.

SQL> drop table viceprez;

Table dropped.

SQL> select * from viceprez;
select * from viceprez
              *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> select table_name from user_tables;

TABLE_NAME                                                                      
------------------------------                                                  
PREZ                                                                            

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME          
---------------- ------------------------------ ------------ -------------------
VICEPREZ         BIN$bsEpu+DLQZWxoPngfpkZJQ==$0 TABLE        2012-11-24:22:40:18

SQL> flashback table viceprez to before drop;

Flashback complete.

SQL> show recyclebin;
SQL> select table_name from user_tables;

TABLE_NAME                                                                      
------------------------------                                                  
PREZ                                                                            
VICEPREZ                                                                        

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  
The Man in the Moon       2013                                                  

6 rows selected.

SQL> delete from viceprez where elected = '2013';

1 row deleted.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

Conclusion

Used with caution, the above Flashback options could save you hours of time in the event that you accidentally drop the wrong table, or have the need to perform repetitive testing on a test copy of the database where the testing causes mass updates to the data (such as in a payroll update).

There are many more options to these commands than what we've talked about here. For full information, please refer to Chapter 18 of the Oracle 11g Database Backup and Recovery User's Guide.

Next Time

Next time, we'll dig a little into Flashback Transaction and Flashback Transaction Query.