Database and database Learning

Database and database Learning
Register log files

Basic Principles
The registration order is strictly in the time order of parallel transaction execution
The log file must be written before the database
Write a log file: indicates the modified log record.
Write to log file
Database write operation: write data modifications to the database

Why Write The Write-Ahead Log first)
Writing a database and writing a log file are two different operations.
A fault may occur between the two operations.
If the database modification is first written, but the modification is not registered in the log file, the modification cannot be restored later.
If a log is written but the database is not modified, the UNDO operation is only performed once when the log file is restored, without affecting the database correctness.

Recovery of transaction faults

Transaction fault: the transaction is terminated before running to the normal termination point
Recovery Method
The recovery subsystem should use the log file to UNDO the changes that have been made to the database by this transaction.
The recovery of transaction faults is automatically completed by the system and transparent to users without user intervention.

Transaction fault recovery procedure system fault recovery

Causes of Database Inconsistency caused by system faults
The update of the database to which the transaction is not completed has been written to the database.
The committed transaction updates to the database remain in the buffer before it can be written to the database.
Recovery Method
1. Unfinished transactions when an Undo fault occurs
2. Redo completed transactions
System fault recovery is automatically completed when the system restarts without user intervention.

1. REDO queue: transactions committed before a fault occurs. These transactions include both the begin transaction record and the COMMIT record Undo) queue: transactions that are not completed when a fault occurs. These transactions only have a begin transaction record and no corresponding COMMIT record.

2. undo is performed on UNDO queue transactions to process log files scanned in reverse mode, perform a inverse operation on the update operation of each UNDO transaction to write the "pre-update value" in the log into the database. 3. redo is performed on REDO queue transactions to process forward scanning log files. re-register each REDO transaction to write the "updated value" in the log records to the database.

Media fault recovery

Recovery steps
1. Load the latest backup database copy (the closest to the time when the fault occurred) to restore the database to the consistent state of the Last dump.
For database copies of static dump, the database is in a consistent state after being loaded.
For a copy of a dynamically dump database, a copy of the log file at the dump time must be loaded at the same time to restore the database to a consistent state by using the system fault recovery method (REDO + UNDO.

Two Problems
Searching the entire log takes a lot of time
REDO processing: Re-execution wastes a lot of time
Checkpoint Recovery Technology
Add a checkpoint record to the log file)
Add a file to start again
The recovery subsystem dynamically maintains logs during logon log files
Checkpoint Record Content
1. Create a list of all ongoing transactions at the checkpoint.
2. The address of the last log record of these transactions
Restart the content of the file (Oracle control file)
Record the address of each checkpoint record in the log file

How to dynamically maintain log files

Periodically perform the following operations: Create a checkpoint and save the database status.
The procedure is as follows:
1. Write all the logs 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 database on the disk.
4. Write the address of the checkpoint record in the log file to a new start file.

The recovery subsystem can establish checkpoints on a regular or irregular basis to save the database status.
Regular
A checkpoint is created every one hour at a scheduled interval.
Irregular
Follow certain rules, such as creating a checkpoint when the log file is half filled

The checkpoint method can improve the recovery efficiency.
When transaction T is committed before a checkpoint
In Oracle, the changes made by transaction T to the database have been written to the log file, but may not have been written to the database
Oracle writes data to the database at the time of creation of this checkpoint!
There is no need to perform REDO operations on the transaction T during recovery.

Use the checkpoint recovery policy

T1: submitted before the checkpoint
T2: run the task before the checkpoint and submit the task before the fault point after the checkpoint.
T3: Execution started before the checkpoint, not completed at the fault point
T4: run after the checkpoint and submit it before the fault point.
T5: Execution starts after the checkpoint and is not completed at the fault point.
Recovery Policy:
T3 and T5 are uncompleted when a fault occurs, so they are revoked.
T2 and T4 are submitted after the checkpoint. The changes they make to the database may still be in the buffer zone when a fault occurs and have not been written to the database. Therefore, REDO is required.
T1 has been submitted before the checkpoint, so you do not have to perform the REDO operation

<T1 start><T1, A, 0, 10><T1 commit><T2 start><T2, B, 0, 10><T3 start>                   <T3, C, 0, 10><T3, C, 10, 20><checkpoint{T2,T3}><T4 start><T2 commit><T4, A, 10, 20><T5 start><T4, D, 0, 10><T4 commit>

1. Find the address of the last checkpoint recorded in the log file from the restart file (Oracle control file), and find the last checkpoint record in the log file.

2. the checkpoint records the LIST of all ongoing transactions at the checkpoint creation time. ACTIVE-LIST = {T2, t3} creates two transaction Queues: UNDO-list redo-LIST. The ACTIVE-LIST is temporarily put into the UNDO-LIST queue = {T2, T3}. The REDO queue is empty.

3. scan the log file forward from the checkpoint until the log file ends. If there is a new transaction Ti, put Ti into the UNDO-LIST queue {T2, T3, T4, t5} If a committed transaction Tj exists, move Tj from the UNDO-LIST queue to the REDO-LIST queue = {T2, T4} 4. perform the UNDO operation on each transaction {T3, T5} in the UNDO-LIST and perform the REDO operation on each transaction {T2, T4} in the REDO-LIST.

Database image

DBMS automatically copies the entire database or key data to another disk.
DBMS automatically ensures the consistency between the image data and the master database
When the primary database is updated, the DBMS automatically copies the updated data.

No fault
Can be used for concurrent operations
A user adds an exclusive lock to the data to modify the data. Other users can read the data in the image database without waiting for the user to release the lock.

Frequent Data Replication naturally reduces system operation efficiency
In practical applications, users usually only select images for key data and log files, rather than images for the entire database.

If the database only contains the results of successful transaction commit, the database is in a consistent state! Ensuring data consistency is the most basic requirement for databases.
Transaction is the logical unit of work of the database
DBMS ensures the atomicity, consistency, isolation and durability of all transactions in the system

DBMS must recover transaction, system, and media faults
The most frequently used technologies in recovery: Database dumping and registration log files
Basic Principle of Restoration: uses redundant data stored in backup copies, log files, and database images to reconstruct the database

Common Recovery Technologies
Recovery of transaction faults
UNDO
System fault recovery
UNDO + REDO
Media fault recovery
Reload the backup and restore it to the consistent state + REDO

Technologies that improve recovery efficiency
Checkpoint Technology
Improves system fault recovery efficiency
To some extent, it can improve the efficiency of media fault recovery using dynamic dumping backup
Image Technology
Image technology can improve the recovery efficiency of media faults