"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 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> 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 1980Flashback 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.
