Flashback Database

 

Oracle Flashback database is one of the jazziest features of Oracle10g database. As a starter, it has a cool name - Flashback. In addition, Flashback is a powerful word. It sounds like a Hollywood or Bollywood flick theme. It implies turning back the time or looking in the rearview mirror. Thanks to Oracle for lightening up tedious work that goes into database administration. And thanks to Oracle for providing a feature as powerful as the word itself.

Oracle flashback database feature lets you unwind the database easily and quickly to a previous point-in-time. There is no need to restore the backups and perform time-consuming incomplete media recovery. You just need few flashback commands, and commands are simple.

But note that flashback database command, as name suggests, operates at the database level. You will flashback the whole database, not just few schemas or just few tables.

As shown below, You can flashback database with or without FLASHBACK mode which is activated through ALTER DATABASE FLASHBACK ON command when the database is in MOUNT mode.

Flashback database with FLASHBACK mode

• You must turn on FLASHBACK when database is in MOUNT mode.
• Flashback Recovery Window is specified by db_flashback_retention_target init.ora parameter. Please note that this parameter specifies a potential target. Your actual mileage may vary depending upon disk space available in the flash recovery area.
• Flashback to any point-in-time or SCN within the range specified by db_flashback_retention_target.
• No need to create normal restore points, but should be created as aliases to represent SCN or time.

Flashback database without FLASHBACK mode

• Flashback database to a known baseline, also known as pre-defined guaranteed restore point.
• You must explicitly define guaranteed restore points.
• Oracle does generate flashback logs, but you can only flashback to a pre-defined guaranteed restore point. You need to make sure that you have enough disk space to store flashback logs that Oracle would generate.

My databases are not in FLASHBACK mode, so I can't perform media recovery using the flashback feature, but I usually define guaranteed restore points when applications or data upgrade operations have potential of failures, and may require rollback.

Let's look at the examples to understand Oracle Flashback features

Example 1 - Flashback database without FLASHBACK mode

Step 1: Define initialization Parameters

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      e:\oraflash                   
db_recovery_file_dest_size           big integer 19531250K                     

SQL> show parameter db_flashback_retention_target

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

Step 2: Create Guaranteed Restore Points

One important point to note is that the first guarantee restore point must be created when the database is in MOUNT mode. Subsequent mount points can be created while DB is open.

SQL> create restore point test guarantee flashback database ;
create restore point test guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'TEST'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when
flashback database is off.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> create restore point test guarantee flashback database ;

Restore point created.

SQL> alter database open ;

Database altered.

SQL> create restore point test1 guarantee flashback database ;

Restore point created.

SQL> select * from v$restore_point ;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE                              
---------- --------------------- --- ------------                              
TIME                                                                           
---------------------------------------------------------------------------    
NAME                                                                           
--------------------------------------------------------------------------------
 114645657                     2 YES            0                              
13-FEB-11 12.16.58.000000000 PM                                                
TEST                                                                           
                                                                               
 114645903                     2 YES      8192000                              
13-FEB-11 12.17.39.000000000 PM                                                
TEST1                                                                          

                                                                            
Step 3: Flashback Database to a Guaranteed Restore Point

Two noteworthy points are that database must be in MOUNT mode in order to flashback and database must be opened withh RESETLOGS.

SQL> flashback database to restore point test1 ;
flashback database to restore point test1
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> flashback database to restore point test1 ;

Flashback complete.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs ;

Database altered.

Example 2 - Flashback database with FLASHBACK mode

Step 1: Define initialization Parameters

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      e:\oraflash                   
db_recovery_file_dest_size           big integer 19531250K                     

SQL> show parameter db_flashback_retention_target

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

Step 2: Create Normal Restore Points

SQL> connect / as sysdba

SQL> create restore point example_normal_restore_point ;

Restore point created.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> flashback database to restore point example_normal_restore_point ;
flashback database to restore point example_normal_restore_point
*
ERROR at line 1:
ORA-38782: cannot flashback database to non-guaranteed restore point
'EXAMPLE_NORMAL_RESTORE_POINT'

SQL> drop restore point example_normal_restore_point ;

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> alter database flashback on ;

Database altered.

SQL> alter database open ;

Database altered.

SQL> create restore point example_normal_restore_point ;

Restore point created.

Step 3: Flashback Database

SQL> flashback database to restore point example_normal_restore_point ;
flashback database to restore point example_normal_restore_point
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> flashback database to restore point example_normal_restore_point ;

Flashback complete.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs ;

Database altered.

Watson, what were you thinking?

I am appalled at your answer in the final Jeopardy on Tuesday evening. You needed to name a US city that had two airports. I know you could produce such a list in nanosecond. You needed to further narrow it down by a war hero. You might have needed few extra microseconds, but that wasn't difficult. But you screwed up. You know and I know that there are only handful of US cities with two airports.

For God's sake, you were being ably supported by an army of 90 IBM Power750 series servers running AIX operating system. Each Power750 server had four Power7 processor chips, each with eight cores. That comes to 2,880 Power7 cores. It is equivalent to 2,880 powerful computer running in parallel. Each core was running at a speed of 3.5 Ghz. You had 16 Terabytes of memory. IBM had spent millions and millions on the design of your brain known as DeepQA software. It was a sheer exhibition of astounding computing power in front of the whole world. You should have aced this question. How could you screw this up?

Had Toronto been a city within any other part of the world than our neighbor Canada, our country would have been accused of violating geographic border of a sovereign nation. CIA might have been accused of conspiring and instigating a revolt in Toronto, Ontario. President Obama would have received a phone call from a foreign leader asking for a formal apology on your behalf. It could have started a war of words among politicians and foreign officials. What were you thinking? Toronto a US city?

Someone feared that your gaffe was a result of a hack attempt against our intellectual icon. Someone feared that hackers from Russia or China were just having fun. But not a chance. Your algorithms have been guarded dearly by IBM. We can't blame it on hacking. Admit it, it was a giant mistake.

I am afraid you will be a butt of joke for years to come like Dan Quale once was for his infamous gaffe. I don't think you are capable of comprehending sarcasm, but I can't resist the temptation of misspelling Dan's last name. Jay Leno, David Letterman and other stand-up comedians are going to have field days for years to come. SNL would have skits just based on your giant mistake. Even Alex Trebek couldn't resist mentioning your gaffe in his opening remarks on Wednesday. You have inspired thousands of bloggers, like me, to pen about your achievements and your mistakes.

You have inspired and given birth to a new expression "I am just a silicon!" This expression sounds very similar to "I am only human! Correct? We use this expression quite often. You better start using "I am just a silicon!" to condone and rationalize your mistakes. Like we are error-prone and susceptible to mistakes, you are equally error-prone and susceptible to the blunders. You are bound to commit blunders, but don't be ashamed because "You are just a silicon!" We are even.

Your stumble is of enormous proportion with potential to cause irreparable damage to the reputation of your descendants, but I think you will be OK. You know that humans  have bad memory, very bad memory. Our brain occupies very small area. It doesn't need a building like your brain does. You had 16 Terabytes of memory in our brain. We don't even know how much memory we have in our brain and we suffer from disease like amnesia. We forget things very easily. We hear tons of promises from political leaders every day, but we simply forget them the very next day. We are hoping you would help expose those leaders who don't deliver on those lofty and rosy promises. At least, this is your punishment for your gaffe on the national stage - remind us of broken promises.

I don't know why IBM didn't conceive you like a human. You don't have a face and a body. You are simply a flat screen. On Wednesday, Ken was trying to embrace you and congratulate you on your triumph against humans. I don't think that was his intention. He was just trying to read the label off of your flat screen to make sure that it was not "Made in China." All our flat screen TVs are made in China.

I am glad that you don't have a face. Otherwise Ken or Brad would have simply knocked you down. You might have noticed that Ken was desperate to question answers, but you were a lot faster. We humans do engage in extra-curricular activities including violence. If you want to see how we knock down our competitors, just watch few NHL games. I am so sad we won't have a chance to get in your face and complain. You are the smartest kid on the block. You are going to make us feel inferior, but we can't punch you in your belly. World is not fair.

Do you know that IBM had managed to bestow upon you all the advantages, and I might add unfair, so that you would win the Jeopardy championship? Ken and Brad had only brain each. You had 2,880 brains! IBM would have added more brains in a heartbeat if IBM thought you could have been defeated. Jeopardy didn't allow Ken and Brad to team up. Otherwise, they could have defeated you on Wednesday.

You also had a significant advantage, and unfair, with your buzzer. You were relying on an pneumatic buzzer. Ken and Brad were relying on their hands to press a buzzer. Hands, being a mechanical device, operate at a considerably lesser speed than your circuit. You may not be the smartest. You should have buzzed in your questions like Brad and Ken did. I know my fellow humans are very smart. They had their answers at the same nanosecond that you had yours. They simply couldn't buzz their answers in as fast as you were able to. You had an advantage. I thought machines were not capable of cheating, but you did. You will do well in this human world.

You almost ruined the most promising career for yourself one could ever imagine in his or her wildest ream. But I think you will be fine. You were just amazing. I don't have words to describe your achievements. What we have seen on Jeopardy is an evolution and a revolution. You put on a great show on earth. Your future is as bright and huge as this universe.