Read an Excerpt
Chapter 5: Recovery Principles
To understand recovery principles and strategies, you need to understand the underlying data structures used in recovery. This chapter is divided into three sections. First, we define the fundamental data structures of the Oracle RDBMS, followed by a detailed discussion of some of the basic concepts that relate to recovery. An overview of the contents of the control file, log files, and data files is given. Later, we shift our focus to the various recovery options provided by Oracle. We discuss the three main options of recovery-database, tablespace, and data file recovery. in addition to learning the syntax, you will also learn when and how to apply different recovery procedures, depending on the kind of failure. A section is dedicated to the Oracle Recovery Manager that is available with Oracle8.The final section is on failure analysis. In this section, we first discuss a survey that was done with several Oracle customers regarding system outages. The results show the Mean Time Between Failures (MTBF) of various systems and the Mean Time to Recover (MTTR) when a failure occurs. When a production or a development database goes down, Oracle customers usually call Oracle Worldwide Customer Support and open a priority 1 Technical Assistance Request (TAR). An analysis was done on a sample of priority 1 TARs that shows how the databases are recovered (i.e., what kind of recovery method was chosen). The results of this analysis are given in detail. Based on this information and the real-life experience that we have gained while dealing with mission-critical applications, some recommendations are made on how to plan for a disaster recovery site.
Definitions and Internal Recovery Concepts
The following definitions introduce some fundamental data structures that are used in recovery. Each definition is also followed by a discussion or an example to make the concepts clear.Redo Generation and Estimation
As mentioned in Chapter 2, the redo log files contain changes made to the database. in this section, we will discuss some of the basic concepts, such as change vectors and redo records, that relate to redo. Some SQL scripts are also provided that help you estimate the amount of redo generated at your site. This is very important, because when you design a backup procedure to back up the archived redo log files, the frequency of this backup depends on it.
Change Vector
A change vector describes A single change made to any single block in the database. Among other information, the change vector contains a version number, the operation code of the transaction, and the address of the data block that this change belongs to. The version number is copied from the data block when the change vector is constructed. During recovery, Oracle reads the change vector and applies the change to the appropriate data block. When a change vector is applied to the data block, the data block's version number is incremented by one.
NOTE: A data block could belong to a data segment, an index segment, or a rollback segment in the database. Redo is not generated for temporary segments.Redo Record and Its Contents
A redo record is a group of change vectors describing a single atomic change to the database. Some transactions may generate multiple redo records, and each redo record can have a collection of change vectors. Recovery guarantees that all or none of the change vectors of a redo record are applied, no matter what type of system failure occurs. In other words, a transaction is the unit of recovery, so, as a unit, all changes are either applied or not applied. To illustrate the creation of change vectors and redo records, consider the following example transaction, which updates one record in the EMP table:
SQL> update emp set empno=1234 where empno = 9999;
When this UPDATE statement is executed, the sequence of operations is as follows:
- 1. Change vectors of the redo record are generated.
- 2. The redo record is saved in a redo log buffer (which eventually gets flushed to the redo log file on disk).
- 3. The change vectors are applied to the data blocks.
- 2. The redo record is saved in a redo log buffer (which eventually gets flushed to the redo log file on disk).
- 1. The transaction has to write an undo entry to the transaction table of the rollback segment (refer to Chapter 2 for the contents of a rollback segment). Since the transaction table is also another block in the database, entering an undo entry would modify this block and thus generate redo. So, the first change vector of the redo record contains the change for the transaction table.
- 2. Next, the old value of EMPNO (which is 9999) has to be stored in a block within the rollback segment. This is another modification to a block within the database and therefore generates redo. So, the second change vector contains redo for the undo block.
- 3. The last and most obvious change is the change to the data block where the EMPNO value is changed to 1234. So, the third change vector is the redo for the data block.
- 2. Next, the old value of EMPNO (which is 9999) has to be stored in a block within the rollback segment. This is another modification to a block within the database and therefore generates redo. So, the second change vector contains redo for the undo block.
To summarize, the redo record for this transaction contains three change vectors:
- Change to the transaction table of the rollback segment
- Change to the rollback segment data block
- Change to the data segment block belonging to the EMP table