More on Flashback Technology - for Database, Table and Drop
Published:
Browse All Articles > More on Flashback Technology - for Database, Table and Drop
Introduction
In my first article, I talked about using Flashback technology to run queries against our database as it looked in the recent past.(This article builds on information presented in the previous one, and it is recommended to the reader that you read that article before continuing here.)
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". These were introduced in Oracle 9i. What other options do we have available to us as we get to more recent versions of the software?
Looking into the heart of our TARDIS (refer to the previous article for the bad "joke" that is my introduction of the Oracle TARDIS), we find the following options awaiting us, as of Oracle 10g:
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
In this article, I will be talking about the first three on that list.
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
It should be noted that it is not a guarantee of data availability; it is a target!
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 Archive Log 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.
In the listings below, I've edited out most of the copyright and version notices that come out of SQL*Plus when you start a new session.
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 STARTEDNext, 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> quitSo, 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.
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> 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 CURRENT 1 19-NOV-12
RMAN>quit
C:\> sqlplus / as sysdba
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>quitLet'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>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.
The other thing that a DBA needs to be aware of is the performance overhead that comes with having flashback mode on all the time, as well as the extra disk space required in the Fast Recovery Area.
One scenario that immediately comes to mind for the use of something like this would be in a large Enterprise application where something like 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 doesn'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 ready for them 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.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 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.
Readers could also refer to Oracle Support Master Note For Oracle Flashback Technologies (Doc ID 1138253.1) for a whole lot more information on Oracle Flashback Technology.
This article was originally published on my blog: http://www.dba-in-exile.com/2012/11/flashback-technology-in-oracle_24.html
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)