Redo log

Last updated

In the Oracle RDBMS environment, redo logs comprise files in a proprietary format which log a history of all changes made to the database. Each redo log file consists of redo records. A redo record, also called a redo entry, holds a group of change vectors, each of which describes or represents a change made to a single block in the database.

Contents

For example, if a user UPDATEs a salary-value in a table containing employee-related data, the DBMS generates a redo record containing change-vectors that describe changes to the data segment block for the table. And if the user then COMMITs the update, Oracle generates another redo record and assigns the change a "system change number" (SCN).

Whenever something changes in a datafile, Oracle records the change in the redo log. The name redo log indicates its purpose: If the database crashes, the RDBMS can redo (re-process) all changes on datafiles which will take the database data back to the state it was when the last redo record was written. DBAs use the views V$LOG, V$LOGFILE, V$LOG_HISTORY and V$THREAD to find information about the redo log of the database. Each redo log file belongs to exactly one group (of which at least two must exist). Exactly one of these groups is the CURRENT group (can be queried using the column status of v$log). Oracle uses that current group to write the redo log entries. When the group is full, a log switch occurs, making another group the current one. Each log switch causes checkpoint, however, the converse is not true: a checkpoint does not cause a redo log switch. One can also manually cause a redo-log switch using the ALTER SYSTEM SWITCH LOGFILE command.

Classification

Redo log files occur in two types: [1]

Usage

Before a user receives a "Commit complete" message, the system must first successfully write the new or changed data to a redo log file.

The RDBMS first writes all changes included in the transaction into the log buffer in the System Global Area (SGA). Using memory in this way for the initial capture aims to reduce disk IO. Of course, when a transaction commits, the redo log buffer must be flushed to disk, because otherwise the recovery for that commit could not be guaranteed. The LGWR (Log Writer) process does that flushing.

Having a redo log makes it possible to replay SQL statements. Before an Oracle database changes data in a datafile it writes changes to the redo log. If something happens to one of the datafiles, a recovery procedure can restore a backed-up datafile and then replay the redo written since backup-time; this brings the datafile to the state it had before it became unavailable. Standby databases in an Oracle Data Guard environment use the same technique: one database (the primary database) records all changes and sends them to the standby database(s). Each standby database applies (replays) the arrived redo, resulting in synchronization with the primary database. [5]

If a database crashes, the recovery process has to apply all transactions, both uncommitted as well as committed, to the data-files on disk, using the information in the redo log files. Oracle must re-do all redo-log transactions that have both a BEGIN and a COMMIT entry (roll forward), and it must undo all transactions that have a BEGIN entry but no COMMIT entry (roll back). [6] (Re-doing a transaction in this context simply means applying the information in the redo log files to the database; the system does not re-run the transaction itself.) The system thus re-creates committed transactions by applying the “after image” records in the redo log files to the database, and undoes incomplete transactions by using the "before image" records in the undo tablespace.

Change data capture can read the redo logs.

In Oracle Data Guard configurations, standby redo logs resemble their equivalent online redo logs, but serve to store redo data transmitted from a different database. [7]

Implications

Given the verbosity of the logging, Oracle Corporation provides methods for archiving redo logs (archive-logs), and this in turn can feed into data-backup scenarios and standby databases.

The existence of a detailed series of individually logged transactions and actions provides the basis of several data-management enhancements such as Oracle Flashback, log-mining and point-in-time recovery. The concept of a database incarnation [8] can influence the use of redo in database recovery.

For database tuning purposes, efficiently coping with redo logs requires plentiful and fast-access disk.

See also

Related Research Articles

A relational database is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relational database systems are equipped with the option of using SQL for querying and updating the database.

In computer science, ACID is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps. In the context of databases, a sequence of database operations that satisfies the ACID properties is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.

A database transaction symbolizes a unit of work, performed within a database management system against a database, that is treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database. Transactions in a database environment have two main purposes:

  1. To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure. For example: when execution prematurely and unexpectedly stops in which case many operations upon a database remain uncompleted, with unclear status.
  2. To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs' outcomes are possibly erroneous.

In computer science, Algorithms for Recovery and Isolation Exploiting Semantics, or ARIES is a recovery algorithm designed to work with a no-force, steal database approach; it is used by IBM Db2, Microsoft SQL Server and many other database systems. IBM Fellow Dr. C. Mohan is the primary inventor of the ARIES family of algorithms.

In the field of databases in computer science, a transaction log is a history of actions executed by a database management system used to guarantee ACID properties over crashes or hardware failures. Physically, a log is a file listing changes to the database, stored in a stable storage format.

In the context of SQL, data definition or data description language (DDL) is a syntax for creating and modifying database objects such as tables, indices, and users. DDL statements are similar to a computer programming language for defining data structures, especially database schemas. Common examples of DDL statements include CREATE, ALTER, and DROP.

Oracle TimesTen In-Memory Database is an in-memory, relational database management system with persistence and high availability. Originally designed and implemented at Hewlett-Packard labs in Palo Alto, California, TimesTen spun out into a separate startup in 1996 and was acquired by Oracle Corporation in 2005.

In database technologies, a rollback is an operation which returns the database to some previous state. Rollbacks are important for database integrity, because they mean that the database can be restored to a clean copy even after erroneous operations are performed. They are crucial for recovering from database server crashes; by rolling back any transaction which was active at the time of the crash, the database is restored to a consistent state.

MySQL Cluster is a technology providing shared-nothing clustering and auto-sharding for the MySQL database management system. It is designed to provide high availability and high throughput with low latency, while allowing for near linear scalability. MySQL Cluster is implemented through the NDB or NDBCLUSTER storage engine for MySQL.

In computer science and data management, a commit is the making of a set of tentative changes permanent, marking the end of a transaction and providing Durability to ACID transactions. A commit is an act of committing. The record of commits is called the commit log.

Extensible Storage Engine (ESE), also known as JET Blue, is an ISAM data storage technology from Microsoft. ESE is the core of Microsoft Exchange Server, Active Directory, and Windows Search. It's also used by a number of Windows components including Windows Update client and Help and Support Center. Its purpose is to allow applications to store and retrieve data via indexed and sequential access.

The Access Database Engine is a database engine on which several Microsoft products have been built. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.

Multi-master replication is a method of database replication which allows data to be stored by a group of computers, and updated by any member of the group. All members are responsive to client data queries. The multi-master replication system is responsible for propagating the data modifications made by each member to the rest of the group and resolving any conflicts that might arise between concurrent changes made by different members.

In online transaction processing (OLTP), information systems typically facilitate and manage transaction-oriented applications. This is contrasted with online analytical processing.

In the database management systems developed by the Oracle Corporation, the System Global Area (SGA) forms the part of the system memory (RAM) shared by all the processes belonging to a single Oracle database instance. The SGA contains all information necessary for the instance operation.

In database computing, Oracle Real Application Clusters (RAC) — an option for the Oracle Database software produced by Oracle Corporation and introduced in 2001 with Oracle9i — provides software for clustering and high availability in Oracle database environments. Oracle Corporation includes RAC with the Enterprise Edition, provided the nodes are clustered using Oracle Clusterware.

The software which Oracle Corporation markets as Oracle Data Guard forms an extension to the Oracle relational database management system (RDBMS). It aids in establishing and maintaining secondary standby databases as alternative/supplementary repositories to production primary databases.

<span class="mw-page-title-main">Virtuoso Universal Server</span> Computer software

Virtuoso Universal Server is a middleware and database engine hybrid that combines the functionality of a traditional relational database management system (RDBMS), object–relational database (ORDBMS), virtual database, RDF, XML, free-text, web application server and file server functionality in a single system. Rather than have dedicated servers for each of the aforementioned functionality realms, Virtuoso is a "universal server"; it enables a single multithreaded server process that implements multiple protocols. The free and open source edition of Virtuoso Universal Server is also known as OpenLink Virtuoso. The software has been developed by OpenLink Software with Kingsley Uyi Idehen and Orri Erling as the chief software architects.

In Oracle databases, Flashback tools allow administrators and users to view and manipulate past states of an instance's data without (destructively) recovering to a fixed point in time.

<span class="mw-page-title-main">Oracle NoSQL Database</span>

Oracle NoSQL Database is a NoSQL-type distributed key-value database from Oracle Corporation. It provides transactional semantics for data manipulation, horizontal scalability, and simple administration and monitoring.

References

  1. Kyte, Thomas; Kuhn, Darl (2014-11-10). Expert Oracle Database Architecture. Expert's voice in Oracle (3 ed.). Apress (published 2014). p. 9. ISBN   9781430262992 . Retrieved 2015-02-19. I've referred to two types of redo log file: online and archived.
  2. Bach, Martin (2013-11-23). Expert Consolidation in Oracle Database 12c. SpringerLink : Bücher. Apress (published 2013). p. 318. ISBN   9781430244288 . Retrieved 2015-07-12. Standby redo logs (SRL) on the disaster recovery site act as the counterpart to the primary database's online redo logs (ORL) and allow the remote site to receive redo more efficiently.
  3. Fogel, Steve (May 2006). "Oracle Database Administrator's Guide, 10g Release 2 (10.2)". docs.oracle.com. Oracle. Retrieved 2015-02-19. The current redo log is always online, unlike archived copies of a redo log. Therefore, the online redo log is usually referred to as simply the redo log.
  4. Ries, Steve (2013-02-22). Oca Oracle Database 11g Database Administration I: A Real-World Certification Guide. Packt Publishing Ltd (published 2013). ISBN   9781849687317 . Retrieved 2015-02-19. [...] when a log switch occurs, the contents of the current redo log are written out to an archived redo log by the ARCn process. These logs are also referred to as offline redo logs or simply archive logs.
  5. Liu, Henry H. (2011-11-22). Oracle Database Performance and Scalability: A Quantitative Approach. Quantitative Software Engineering Series. Vol. 12. John Wiley & Sons (published 2011). pp. 238–239. ISBN   9781118056998 . Retrieved 2015-02-19. Primary and physical standby databases are synchronized through a service called Redo Apply, which recovers the redo data from the primary database and applies the redo to the standby database. [...] Synchronization between the primary and [logical] standby databases is achieved through a service named SQL Apply, which transforms the redo data from the primary database into SQL statements and then executes the SQL statements on the standby database.
  6. Greenwald, Rick; Stackowiak, Robert; Stern, Jonathan (2013-09-06). Oracle Essentials: Oracle Database 12c (5 ed.). O'Reilly Media, Inc. (published 2013). ISBN   9781449343170 . Retrieved 2015-02-19. Instance recovery has two phases: roll forward and roll back.
  7. Schupmann, Vivian (2008). "Oracle Data Guard: Concepts and Administration: 10g Release 2 (10.2)". Oracle. Retrieved 2015-02-19. A standby redo log is similar to an online redo log, except that a standby redo log is used to store redo data received from another database.
  8. Bach, Martin (2013-11-23). Expert Consolidation in Oracle Database 12c. SpringerLink : Bücher. Apress (published 2013). p. 378. ISBN   9781430244288 . Retrieved 2015-02-04. An incarnation as per the Oracle documentation is a separate version of the database.