Saturday, February 14, 2009

Oracle flashback feature

Oracle flashback feature:
In ORACLE 10G, a new feature introduced named as FLASHBACK.actually in ORACLE 9i, also there was a feature called FLASHBACK QUERIES. Dont get confused about it.... think like the full featured FLASHBACK is from 10G. in 9i, it had only one option available compared to 10G flashback options.In 10G, FLASHBACK refers as one tool which supports different types of data recovery

flashback features:

i) flashback a database to prior to present time

ii) flashback a table to prior to present time
iii) flashback/rollback a drop table statement(means rollback a table which is dropped)


i) FLASHBACK DATABASE:
earlier till 9i, if we need to go to previous time of a database then we need to do incomplete recovery(DBPITR). That is restore a backup previous to the time you want to recover and apply archived logs till the time you want to recover, then open the database with resetlogs option.10G onwards, we can flashback our database to a previous point in time. Flashback feature uses flashback logs(additional logs to be created for this feature) to do the flashback. Flashback logs will maintain the block changes information and this will be refered to , when you want to flashback your database.Oracle will read the flashback logs and undo the changed blocks to roll it backward.

Note: The entire database will be rolled back. you cannot perform flashback on individual tables or tablespaces.


ii) FLASHBACK a table data to prior time:
this was introduced in Oracle9i as flashback query feature. Remember, read consistancy....snapshot too old error .we can have read consistency till the data was not committed by another transaction.If transaction gets rollback the undo will be applied again. If commited you will see the newest data. Till the transaction going on you can see the read consistent data from undo. And so, When the change is committed, the need for the past image is gone, but it’s not discarded till there is a need of undo space for other transactions.So, the concept of flashback a table is why not to use the undo to get a table to a previous stage.yes we can do that.but only till the undo is available.
and we can resize the undo also depending on how much back image of a table i want.........

iii) Rollback/restore a dropped table:
we know DDL statements connot be rollback. Means if you drop a table,that is gone. But this works only till oracle 9i.
in 10G,you can restore a table which was dropped by a ddl commands.


Check whether flashback is enabled or not in oracle 10G:

SQL> select flashback_on from v$database;


FLASHBACK_ON

------------------
YES

If it shows no means flashback is not enabled

How to enable flashback in oracle 10G

step 1: Database should be in archive log mode.(HOW TO ENABLE /DISABLE ARCHIVE LOG)
step 2: FLASH RECOVERY AREA must be configured.(HOW TO CONFIGURE FLASH RECOVERY AREA)
step 3:
sql> conn sys/**@lion as sysdba
sql>startup mount
sql>alter database flashback on;
sql> alter database open;

Note: enabling flashback generates flashbacks logs which tracks the changes to data blocks. Flash recovery area is the only place where it stores flashback logs. So, flash recovery area must to be configured.Secondly, having only flashback logs will not help to flashback database ,it needs the archive logs to be applied to undo the blocks.so,the database must be in archive log mode.Remember one more thing, sizing the flash recovery area is very much important.
if you size it inadequetly it may end up having no space in FRA, so,database may hang,or will not startup and you may receive the following errors :(because it may be the location for your rman backup storage and archived logs also. so, no space here means you are in trouble)

Check the errors you may receive if flashback recovery area lack of space:
1) You see a message in the alert log similar to the following:
Can not open flashback thread because there is no more space in flash recovery area

2)If the database has aborted earlier because of flashback errors and you attempt to

start it, you get the following error:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

we will talk about how we can solve this issues in separate discussion. Let us come back to the discussion here.

Using flashback:
HOW to flashback a DATABASE



Other available options:

What should be the size of FRA(flash recovery area)
How to disable flashback

2 comments:

Anonymous said...

Nice fill someone in on and this fill someone in on helped me alot in my college assignement. Thanks you for your information.

Anonymous said...

Great thanks for you effort,
Eng \Mohmed Amin