What are UNDO data?

Have you ever asked yourself what happens when you call rollback? Or why do you see only the changes before you commit, and not the other users? The same data must exist in two forms: the changed one and the original before the change. These original data (pre-modified) are needed in at least several situations:

  • When you call rollback – the original data must be restored.
  • When you disconnect a session without approval, the original data must be restored.
  • When other users are viewing the data that you are changing and you have not yet approved – they must see the original data.
  • When you restore a state of an object, such as a table to a point in time, the state of the object must be retrieved at the indicated time.

How does it all work out? Each time you perform any DML operation (DELETE, UPDATE, INSERT, MERGE), the original data state that you change is written as UNDO data. UNDO data is stored in the UNDO tablespace (which does not have to be called). There may be several tablespaces for UNDO data storage, but there may be only one in current use. In the undo table there is a form of data before the change with information about what data and when it came from.

At the end of the transaction, the data remains in UNDO, so we can use FLASHBACK and, for example, restore the table to a state from a certain time. How long does this data exist in the UNDO tablespace? This depends on the UNDO_RETENTION setting, the tablespace extensibility, and the warranty settings. We’ll do a little later.

 

UNDO data and REDO logs

You have to remember that UNDO data and archivelogs are two completely different things and serve different purposes. First of all, archivelogs are created based on repetitive log files – so in a sense, they contain a history of changes rather than data. Archivelogs can be created or not, depending on the ARCHIVELOG mode. The instance could not function without UNDO data. Without them, no DML operation would be possible. The purpose of applying UNDO and REDO data is also different. Archivelogs are primarily used to restore the database. They are read, and then the changes that are contained in them are applied to the database after the recovery of physical files from the backup.

Benefits of using FLASHBACK

With FLASHBACK, which relies on UNDO data, we are able to:

  • Play single objects – eg tables, without the need to play the entire database or tablespace.
  • Playback with FLASHBACK is much faster than backup playback
  • We do not need to disable the instance, nor the entire tablespace to replay the object.

 

All this implies – the practical use of the UNDO space

Flashback Query

Using data that is in the UNDO space, we can look at the state of the data from a specific time. It may be necessary to specify the time that you need to recreate the table after erroneous operations, or simply to compare states. Example:

  1. I check the employee’s salary with id 100 in the employee table in the user’s hr.
  2. I see that his current salary is 24,000, so I change it to 22000 and approve the change. For sure I check the changes have been made.

 

  1. I would like to now learn how these data looked like a few minutes ago. I’m looking at how they look now.
  2. Using the “as of timestamp” clause, I am able to view data from a specific time. To this point in time to which I refer, I use the to_timestamp function. The data shown is from UNDO.

 

The “as of timestamp” clause always appears after the name of the table to which the operation refers. All conditions follow.

I can now perform a flashback table to a point in time, and thus recover the state of the object at any given moment. But what if many changes have been made, and I do not know exactly when the data was in the desired state? I can test different versions of this data over time using the Flashback Versions Query.

Flashback Versions Query

As I mentioned earlier, flashback versions queries will allow you to compare different versions of the same data over time. Best if I show it by example. I check first how much the employee’s salary is at id 103.

 

Then I make a few changes to my salary, approving them. First Amendment:

 

Second Amendment:

 

To compare the same data over time I have to use the “versions of timestamp” clause just after the table name and indicate the time range that interests me. The first value must always be lower:

 

I also showed here a pseudo version_starttime column that tells me when this change was made. I also have the following columns:

  • versions_endtime – that is when this version of the data was valid
  • versions_startscn – scn while making the change
  • versions_endscn – last scn during this version of data
  • versions_operation – what operation data was changed

FLASHBACK table (restore table to point in time)

When I am able to determine what time I want to restore the object, I can use FLASHBACK to do this. I do not have to turn off the instance or the tablespace. Data is restored from the UNDO space.

The first thing I have to do is to enable “row movement” for the table that I am going to restore. This is done only once for the table. We do not have to do this before every restore.

 

Then we restore the table to the time we want to restore with the to_timestamp function. It does not have to be exactly the time of the release, which we can see using the “versions between” query. It is important to find time in which the version we are interested in. I restored the table to the state before all changes to the initial version:

 

Little review yet:

 

Of course, we can not go back far in the past. The condition of restoring a table to a given point in time is the existence of data from that period in the UNDO space. However, we will not return the table that was deleted. To do this we need to use a slightly different method, namely “Flashback before drop”

Flashback before drop

This functionality is only available from version 10g. So if you happen to delete a table in 9 or an older version, unfortunately, you will need to restore the database to a point in time. As an administrator, you can, of course, prevent the restoration of deleted tables:

alter system set recyclebin = off scope = spfile;

After you set this parameter, you must restart the instance so that the new settings are included. Of course you can also restore it:

alter system set recycle bin = on scope = spfile;

You do not have to turn it on if you have not turned it off before because this parameter is enabled by default.

Let’s take a closer look at the possibilities we have. First, we delete the table:

 

We can now look into our dumpster from the perspective of the user deleting the object:

 

We see that there is a deleted table in the dump. In the user_recyclebin dictionary, we only see objects that have been deleted by us. The administrator can view all deleted objects in the dba_recyclebin dictionary:

 

If the object is in a dumpster, you can restore it:

 

But if we created a new object with the same name as this one, or simply wanted to recreate the object with a new name, we could add a “rename to” clause:

 

You can delete them permanently for specific objects. So they can not be restored to flashback. We will then add the “purge” clause to the deletion:

 

The object is not visible this time in the trash and cannot be played.

If you would like to permanently remove the previously scraped object ie delete it from the dumpster:

 

You can use the “purge table” clause:

 

 

The way of performing restoration after deletion is slightly different than when you restore to a point in time. In the case of deletion, the object does not go to the UNDO space. Just the place that it occupied in your tablespace is labeled as “writeable”. The data from the deleted table really still exists. The table in the table is overwritten when there is no free space in the tablespace, and the tablespace itself will not be able to expand. In practice, this means that such deleted tables will be restored for a very long period, regardless of UNDO_RETENTION settings and UNDO space properties.

Sometimes restoring individual objects, unfortunately, is not enough, we need to restore the database to a point in time …

FLASHBACK database (Restoring a base to a point in time using FLASHBACK)

Remember that restoring a database using FLASHBACK is a completely different mechanism than restoring a database using backup and archivelogs. This is also true of archivelogs, but you do not need to restore files from the backup. Data is restored from the so-called. FLASHBACK LOGIC to the state before the moment we restore, and then the changes to the point we point out are from archivelogs. Of course, alternatively, you can use regular restore to a point in time, but if you have the choice of backing up a week and then archiving logs or flashing logs and reading the minimum number of archivelogs, then speed comparison will have obvious effects. To restore the database using flashback, we need to have ARCHIVELOG mode enabled.

 

We need to perform a pre-configuration that is required to restore the database from the flashback. The configuration is performed in MOUNT EXCLUSIVE mode. Flashbacks will be in the FRA, they will be overwritten. We need to set a time to go back. This is not a rigid value, just an orientation. This time is determined by the parameter db_flashback_retention_target giving the time in minutes. I set up 1440 minutes a day. The next step is to enable the production of flashback logs with  alter database flashback on;  Whether the database generates flashback logs or not, we can verify by checking the flashback_on parameter from the v $ database dictionary. We can also disable the production of flashback logs by  alter database flashback off (also in mount exclusive mode).

 

Configuration behind us, so let’s test. I change only one table and then restore the entire database using flashback, but this is only for testing purposes – remember we can restore single objects, there is no need to restore the entire database.

I logged in as an HR user and check my current earnings status in the company:

 

So I make a change in salary for all employees:

 

I will restore the database using FLASHBACK. To do this, I have to be in MOUNT EXCLUSIVE mode. After that, I have to open the RESETLOGS database:

 

I’m checking back after my data has returned to its original state:

 

 

It is ok. Now dear reader, try to recreate the database to the same point using the “traditional” methods ie. Restore, recover and compare execution times 🙂

Although apparently, this method seems to be great, remember that it also has its limitations. Since flashback logs are cyclically overwritten, the ability to return to a point in time is strictly dependent on whether the logs have already been swapped or not. You might want to have points for which you can always return, even after a long time. You can simply keep the archives back and archivelogs needed to be restored to the point in time, but this also works with Guaranteed Restore Points.

Guaranteed Restore Point

Using this functionality, you can create a point where you can always return regardless of the settings of the db_flashback_retention_target parameter. It works just like “save” in games. The database writes to you at your designated time. If necessary, you can always return to this point.

 

You create a restore point at a normally open and running instance. You can create many of them. If necessary, playback is very fast. You only point to what restore point you want to restore.

 

Restoring to this point is also done in MOUNT EXCLUSIVE mode. After all, we open the RESETLOGS database.

Flashback has another interesting property. Allows you to go back to RESETLOGS:

 

To make the world not too beautiful and admin too easy remember also restrictions. You will not be able to restore the flashback database if:

  • The control file was played
  • Deleted tablespace
  • Data file has been reduced

You may have been wondering, for example, how long the data is stored in the UNDO space, how far we can go in the past and whether we can influence it. Your curiosity will be covered by the UNDO Data Management chapter

UNDO Data Management

How long does UNDO data hold? How far can we go in the past to see the historical state of the table? It depends strictly on the size of the UNDO space. Keep in mind that UNDO data is generated all the time, with every operation affecting the data. At some point, the space that these data can take up ends. And what’s next? There may be two things – or the space will expand to not overwrite older UNDO data, or the older data will be overwritten. What happens depends on us, or more precisely, from our UNDO space settings. For large DML operations such as DELETE, UPDATE (INTERT uses very little UNDO space), an UNDO space is needed to accommodate the original data at least until the commit time. This is due to the fact that the user can always withdraw the transaction, or session will be aborted. In both cases, you need to restore the original data from the UNDO space to the original location. If there is a situation where the space needed for UNDO data is larger than the available UNDO space, and the UNDO space itself can not expand, the user will see the error “ORA-01650 Unable to extend rollback segment”. If we were to use a flashback table or flashback query and the data needed for this operation would no longer be available in the UNDO space, we would see the message “ORA-01555 snapshot too old”. Generally, Oracle databases can have many UNDO spaces, but only one can write. If there is a situation where the space needed for UNDO data is larger than the available UNDO space, and the UNDO space itself can not expand, the user will see the error “ORA-01650 Unable to extend rollback segment”. If we were to use a flashback table or flashback query and the data needed for this operation would no longer be available in the UNDO space, we would see the message “ORA-01555 snapshot too old”. Generally, Oracle databases can have many UNDO spaces, but only one can write. If there is a situation where the space needed for UNDO data is larger than the available UNDO space, and the UNDO space itself can not expand, the user will see the error “ORA-01650 Unable to extend rollback segment”. If we were to use a flashback table or flashback query and the data needed for this operation would no longer be available in the UNDO space, we would see the message “ORA-01555 snapshot too old”. Generally, Oracle databases can have many UNDO spaces, but only one can write. If we were to use a flashback table or flashback query and the data needed for this operation would no longer be available in the UNDO space, we would see the message “ORA-01555 snapshot too old”. Generally, Oracle databases can have many UNDO spaces, but only one can write. If we were to use a flashback table or flashback query and the data needed for this operation would no longer be available in the UNDO space, we would see the message “ORA-01555 snapshot too old”. Generally, Oracle databases can have many UNDO spaces, but only one can write.

UNDO_RETENTION parameter

 

The UNDO_RETENTION parameter specifies in the seconds the minimum UNDO data retention time (900 by default). The system will attempt to store the UNDO approved transaction data for the specified time. They may need the data they need to restore objects using FLASHBACK or use a flashback query. As administrators we have to make sure that this place is sufficient. You may also find that the UNDO data of approved transactions will not be stored for the time we have designated. This will happen if the space in the UNDO space will need another transaction. Once the time has expired, the UNDO data is not deleted. So if the database does not produce a lot of UNDO data, it may prove that the data will be stored much longer.

Oracle will strive for even the cost of undo approved transactions, providing enough room for current transactions. If for an ongoing operation, such as UPDATE or DELETE, there would be no space in the UNDO space, it would have to be interrupted. This behavior can be influenced by the RETENTION GUARANTEE parameter.

RETENTION GUARANTEE

If we want to ensure that the validated undo space is maintained by the undo_retention specified by us in time, we change the retention property of the currently used undo space to guarantee.

 

However, we must take into account that in case there is no space in the UNDO space for the operation, it may be interrupted. In this configuration, the priority is to maintain the undo data. This state can be restored to the initial:

 

If necessary, we can increase the UNDO tablespace with the RESIZE command, or add additional data files to it. The alternative is to create a new UNDO space with greater capacity:

 

We have a lot of recapture space:

 

 

However, only one can be used.

To start using the newly created recursive space, we need to change the undo_tablespace parameter.

 

Checking the current value of this parameter, even in the v $ parameter dictionary, we will find out which recurrence space is currently used.