Flashback Management for Reconstructing the Database Image

Security, integrity, and data consistency are integral parts of relational database systems. Transaction management allows you to transfer the database from one consistent image to another. It must be ensured that any change in the confirmed transaction is consistent, satisfying all integrity rules and constraints. Some rules do not need to be applied immediately during the operation’s execution typically related to referential integrity. However, to reach COMMIT (transaction approval), all data constraints must be passed.

In this chapter, we will look at how transaction logs can be used to retrieve historical versions of objects. We looked at the Flashback data archive in Chapter 10. Now, we will discuss Flashback technology, with which it is possible to reconstruct states that were valid in the past. Flashback operations can be applied at multiple levels, from the whole database to attributes defined by the queries...

Transaction as a change vector source

A database transaction is the main unit of work in the database system that makes the database consistent and independent of other transactions by securing a high level of parallelism. By getting any data failures or invalid operations, the transaction ensures that the changes covered by it can be removed, making the database consistent and valid again. The transaction is commonly formed of multiple data operations, which are, however, treated as atomic. Thus, all the changes are either accepted or the whole transaction is refused by rolling back the changes.

A database transaction is encapsulated by four rules – atomicity, consistency, isolation, and durability (ACID).

The atomicity of the transaction ensures that either the whole transaction is approved or it is refused completely. Consistency ensures that integrity constraints are successfully passed before transaction approval – for example, there is no character string...

Transaction log structure and log types

Transaction logs describe the operations that are executed inside the transaction. Each log consists of the transaction position reference, the System Change Number (SCN), the individually performed operation, and the change vector. From this perspective, transaction logs can be divided into two groups – UNDO and REDO structures. An UNDO log stream stores the original data to allow the system to refuse transactions and revert individual operations to their original state. It is physically stored in the UNDO tablespace in the database. On the other hand, the REDO log stream is stored primarily in memory and then copied to the Online REDO log files, which are crucial to the Oracle database. This allows us to replay the approved transaction to get the state of the database just before the database instance or media failure. The Log Writer (LGWR) background process operates transaction log management.

Both streams are periodically overwritten...

Reconstructing states using Flashback technology

Flashback technology can be considered a very important rescue layer. It has surely happened to you that you forgot the Where condition while defining the Update command, or you simply changed the data the wrong way. You might have wanted to delete archive data or move it to the data warehouse and you accidentally deleted the production data that is still needed. Yes, these could cause a huge problem, even up to the level of losing your job. Surely, even just reading this gives you chills, right?

In this section, we will show you that despite finding yourself in such complicated situations, there is still a way out without causing permanent data loss. We will deal with the Flashback technology at various precision levels. FLASHBACK DATABASE reverts the whole database to the required point. It requires the MOUNT mode of the database, so the database is not available during this operation. We will also discuss the techniques of how...

Summary

This chapter dealt with Flashback technology. While it wasn't intended to cover temporality and monitor object states over time, it offered techniques to get the state at a defined point in the past. The Flashback operation can be done for the whole database or even a particular table, or the historical data image can be obtained by the query itself. The Select statement provides the most powerful solution from a temporal management point of view because the stored data is not changed – just the transaction logs are applied to construct the result set. As stated, the Flashback execution is based on Online and Archive transaction logs, by which the historical state is reconstructed. The availability of the logs is crucial for the activity and reliability of the solution. Regarding performance, Flashback commonly provides a more powerful solution compared to restore and recovery. On the other hand, a precise temporal model can ensure significant performance benefits...

Questions

Answer the following questions to test your knowledge of this chapter:

1. Which transaction property ensures access just to confirmed data changes (assuming the transaction has already reached COMMIT)?
   1. Atomicity
   2. Consistency
   3. Isolation
   4. Durability
2. Which attribute signifies the transaction time reflection for the Flashback data reconstruction?
   1. UNDO
   2. REDO
   3. SCN
   4. LGWR
3. Which state of the database must be applied to perform FLASHBACK DATABASE?
   1. NOMOUNT
   2. MOUNT
   3. OPEN
   4. RESETLOGS
4. Which statement gets you the table Tab content as it existed at the SCN 37787875368953?
   1. select * from Tab AS OF SCN 37787875368953;
   2. select * from Tab AS OF TIMESTAMP 37787875368953;
   3. select * from Tab FLASHBACK to 37787875368953;
   4. select * from Tab where SCN=37787875368953;

Further reading

To learn more about the topics that were covered in this chapter, take a look at the following resources:

• Pro Oracle Database 18c Administration (Manage and Safeguard Your Organization’s Data), by Michelle Malcher and Darl Kuhn. It deals with database system administration. Chapter 19 focuses on the RMAN restore and recovery operations, complete and incomplete recovery, as well as flashing back a table or whole database.
• Oracle Database 12c – Oracle RMAN Backup and Recovery, by Robert G. Freeman and Matthew Hart. Part 1 deals with the base elements of RMAN, Part 2 highlights the configuration, backup, and recovery essentials, and Part 3 focuses on maintenance and administration tasks. RMAN as a highly-available architecture is discussed in Part 4. Chapter 16 directly covers the Flashback technologies by emphasizing possible user errors. Among the stated Flashback types, it also deals with flashing back the whole transaction and flashing back...