Database-recovery policy and database mirroring

Registering log files

Basic principles
The order of registrations is strictly in the chronological order of the parallel transaction execution
The log file must be written before the database is written
Write log file operations: Log records that represent this modification
Write to log file
Write database operations: Write changes to the data in the database

Why write the log file first (the Write-ahead log)
Writing a database and writing a log file are two different operations
A failure may occur between these two operations
If the database modification is written first, and the change is not registered in the log file, then the modification cannot be resumed
If you write the log first, but do not modify the database, the log file recovery is only a more unnecessary undo operation, and does not affect the correctness of the database

Recovery of transactional failures

Transaction failure: The transaction is terminated before it runs to the normal termination point
Recovery method
The recovery subsystem should take advantage of the log file undo (undo) that the transaction has been modified by the database
Recovery of transaction failures is done automatically by the system, is transparent to the user and does not require user intervention

Recovery steps for a transactional failure

1. Reverse-Scan the file log (that is, the log file from the last forward scan) to find the update operation for the transaction.
2. Performs an inverse operation on the update operation of the transaction. Writes the pre-update value in the log record to the database.
   Insert operation, "value before Update" is empty, is equivalent to delete operation
   Delete operation, "updated value" is empty, is equivalent to insert operation
   If you modify the operation, it is equivalent to replacing the modified value with the pre-modified value
3. Continue to reverse scan the log file, look for other update operations for the transaction, and do the same.
4. This process continues until the start tag of the transaction is read, and the transaction recovery is complete.

Recovery of system failures

Reasons for the inconsistent state of the database caused by system failure
An update to the database that has not completed the transaction has been written to the database
Committed transaction updates to the database remain in the buffer before it is written to the database
Recovery method
1. Undo failure occurs when a transaction is not completed
2. Redo Completed transactions
System failure recovery is done automatically by the system at reboot, no user intervention required

1.正向扫描日志文件（即从头扫描日志文件）重做(REDO) 队列: 在故障发生前已经提交的事务这些事务既有BEGIN TRANSACTION记录，也有COMMIT记录撤销 (Undo)队列:故障发生时尚未完成的事务 这些事务只有BEGIN TRANSACTION记录，无相应的COMMIT记录

2. 3. 

Recovery of media failures

Recovery steps
1. Mount the latest Backup database copy (the most recent dump copy from the time of the failure), and restore the database to a consistent state at the time of the most recent dump.
For a database copy of a static dump, the database is in a consistent state after loading
For a database copy of a dynamic dump, you must also mount a copy of the log file at the time of the dump, taking advantage of the method of recovering the system failure (that is, Redo+undo) to restore the database to a consistent state.

1. Mount the relevant log file copy (a copy of the log file at the end of the dump), redo the completed transaction, and undo the unfinished transaction.
   The log files are scanned first to identify the committed and incomplete transactions at the time of the failure, and to record them into the redo queue and the undo queue, respectively.
   The log file is then scanned for redo processing of all transactions in the redo queue. Reverse-Scan the log file for undo processing of all transactions in the undo queue.
   
   Recovery of media failures requires DBA intervention
   DBA's work
   Reload the database copy of the most recent dump and the associated log file copies
   Perform system-provided restore commands
   The specific recovery operation is still done by the DBMS

Two questions
Searching the entire log will take a lot of time
Redo Processing: Re-executing, wasting a lot of time
Recovery technology with checkpoint (checkpoint)
Add checkpoint record in log file (checkpoint)
Add Restart File
The recovery subsystem dynamically maintains logs during logon log files
The contents of the checkpoint record
1. Establish a checklist of all the transactions being performed at the checkpoint point
2. Address of the most recent log record for these transactions
Re-start the contents of the file (Oracle control file)
Record the address of each checkpoint recorded in the log file

How to dynamically maintain log files

Periodically do the following: Establish checkpoints and save database state.
The specific steps are:
1. Write all the log records in the current log buffer to the log file on the disk
2. Write a checkpoint record in the log file
3. Write all data records of the current data buffer to the disk database
4. Write the address of the checkpoint record in the log file to a re-start file

The recovery subsystem can establish checkpoints on a regular or irregular basis to save the state of the database.
Regular
Follow a predetermined interval, such as creating a checkpoint every hour
Non-recurring
Follow some rules, such as log files are written half full to establish a checkpoint

Use of checkpoint methods to improve recovery efficiency
When a transaction T is committed before a checkpoint
In Oracle, the changes made to the database by transaction t have been written to the log file but may not have been written to the database
Oracle writes to the database at the time this checkpoint is established!
There is no need to perform a redo operation on the transaction T while the recovery process is in progress

Recovery strategy using checkpoints

T1: Submit before Checkpoint
T2: Start execution before checkpoint, submit before point of failure after checkpoint
T3: Start execution before checkpoint, not completed at point of failure
T4: Start execution after checkpoint, commit before the point of failure
T5: Start execution after checkpoint, not yet completed at point of failure
Recovery strategy:
T3 and T5 are not completed at the time of the failure, so they are revoked.
T2 and T4 are submitted after checkpoints, and their modifications to the database may still be in the buffer in the event of a failure and have not been written to the database, so redo
T1 is committed before the checkpoint, so redo operation is not required

<T1 start><T1, A, 0, ><T1 commit><T2 start><T2, B, 0, ><T3 start>                   <T3, C, 0, ><T3, C, ten, ><checkpoint{t2,t3}><T4 start><T2 commit><T4, A, ten, ><T5 start><T4, D, 0, ><T4 commit>

   1.从重新开始文件中（Oracle控制文件）找到最后一个检查点记录在日志文件中的地址，由该地址在日志文件中找到最后一个检查点记录

2.由该检查点记录得到检查点建立时刻所有正在执行的事务清单ACTIVE-LIST={T2,T3}建立两个事务队列UNDO-LIST REDO-LIST 把ACTIVE-LIST暂时放入UNDO-LIST队列={T2,T3}，REDO队列暂为空。

3.从检查点开始正向扫描日志文件，直到日志文件结束如有新开始的事务Ti，把Ti暂时放入UNDO-LIST队列={T2,T3,T4,T5}如有提交的事务Tj，把Tj从UNDO-LIST队列移到REDO-LIST队列={T2,T4}4.对UNDO-LIST中的每个事务{T3,T5}执行UNDO操作    对REDO-LIST中的每个事务{T2,T4}执行REDO操作

Database Mirroring

The DBMS automatically copies the entire database or key data from it to another disk
DBMS automatically guarantees consistency between mirrored data and the primary database
Whenever the primary database is updated, the DBMS automatically copies the updated data past

If no failure occurs
Can be used for concurrent operations
One user adds exclusive lock modification data to the data, and other users can read the data on the mirrored database without waiting for the user to release the lock

Replicating data frequently can reduce system performance
In real-world applications, users often choose to mirror only critical data and log files instead of mirroring the entire database.

If the database contains only the results of a successful transaction submission, it says that the database is in a consistent state! Ensuring data consistency is the most basic requirement for a database.
A transaction is a logical unit of work for a database
DBMS guarantees atomicity, consistency, isolation, and persistence of everything in the system

The DBMS must recover from a transaction failure, system failure, and media failure
The most frequently used technology in recovery: Database dumps and enlistment log files
Fundamentals of Recovery: rebuilding databases with redundant data stored in Backup replicas, log files, and database mirroring

Common recovery Techniques
Recovery of transactional failures
UNDO
Recovery of system failures
UNDO + REDO
Recovery of media failures
Re-equip and return to consistency status + REDO

Technologies to improve recovery efficiency
Checkpoint Technology
Can improve the efficiency of system failure recovery
Can improve the efficiency of media failure recovery to a certain extent by using the dynamic Transfer Reserve component
Mirroring Technology
Mirroring technology can improve recovery efficiency of media failure

Database-recovery policy and database mirroring