I just finished teaching a DBA class and surprisingly, few of the participants , despite having experience in the database administration, hadn’t really touched Flashback technology in a great detail. Though it’s not a new topic but I thought, let me write about it to make sure that more DBA’s are aware of this.
Biggest issues in the database administration is to perform recovery of the database when there is an issue. But many times, the issues which really need a DBA to perform the recovery are very less. Most of the issues, that are considered to be a DBA’s task, actually can be done much easily if the user or developer can do it by herself. It’s not that the DBA can’t do the recovery or shouldn’t, but the main thing is that it’s going to cause more issues in some cases if a DBA will perform a recovery invoving the whole database. And some times, it’s not possible to use the standard restore and recovery technique as well. That’s where Flashback tech comes in handy.
Flashback is based on Undo data(most of the time except in Flashback Database). Basic purpose of Flashback is to perform the rollback of the operation using Undo data of that operation. This needs very less efforts from the user(except the required privileges). One can use this Flashback even to just query the data as well if recovery is not what one is looking for.
In this post, we shall look at Flashback query to see how the old data comes from the undo images. Flashback query is based on either the Timestamp or the SCN value that the user must know. In this post, we shall use SCN to show the example. In the real world, knowing the timing is more better or suitable.
Let’s create a table.
Let’s enter some data in this table.
Now we have the data. Let’s check the SCN before we are going to change this data in a logical wrong manner.
So now, we shall check the SCN and as you can see we have wrongly updated the table.
Now using Flashback query, we can see the old value of this table.
So we can see that using the Undo data, we can see the old data. Do note, that we haven’t changed anything. It’s just the old data that’s queried and shown to you, the user.
In the next post, we shall see what else Flashback can do for the user.
Hope that helps.