The principle of transaction and database recovery The logical unit of data storage is the data block, and the logical unit of data manipulation is the transaction. A transaction is a user-defined set of sequence of operations, consisting of one or more related SQL statements, which is the basic logical unit of a database application. Transaction management technology mainly includes database recovery technology and concurrency control technology. The process logic of the transaction 1, the server process picks up the SQL statement for the transaction, and then examines the shared pool to see if the shared SQL area of the SQL statement is included. 2, if any, checks whether the user has permission to access the data involved, and, if so, uses the shared SQL zone to process the SQL statement. If no shared SQL area is found, a new SQL area is assigned to parse and process the SQL statement. If there is no permission, the return prompt does not have sufficient permissions. 3, locks the operation object involved in the SQL statement, 4, the server process operates the data in the SGA, or reads the winning data from the data file into the SGA and then operates the data. 5, at the appropriate time, the LGWR background process writes the statement-buffered redo log to the online redo log file, and the DBWR background process writes the modified chunks of data in the cache to the data file. 6, generating an incremental SCN for the transaction. The SCN is written to the control file, the header of the data file, the header of the data block, and the redo record. The SCN determines the consistent state of the database at a point in time for concurrency control and database recovery. The 7, LGWR background process writes all remaining, buffered redo logs and current SCN to the online redo log file. 8, frees the system resources used by each SQL statement in the transaction, and unlocks the manipulated objects involved, 9 if the transaction is successful, returns a successful prompt, or returns an error prompt; 10, at some appropriate time, The DBWR background process writes the changed chunks (dirty chunks) that remain in the data cache to the data file. Transaction Control basic statement and function 1, submit transaction (commit)  2, ROLLBACK transaction &NBSP (rollback) 3, set savepoint ( savepoint) 4, fallback to save points (Rolbackto s) AvePoint) 5, set transaction properties (settransaction) &NB Sp;6, setting the test timing for deferred constraints (setconstrants) Transaction commit tasks: 1, Automatically generates an incremented SCN for the transaction. The 2, LGWR background process writes all the remaining buffered redo logs and the current SCN to the online redo log file. 3, frees the system resources that each SQL statement in the transaction occupies, and unlocks the objects involved. 4, returns the corresponding prompt code information to the user. 5, marks the transaction as completed. 6, at some appropriate time, the DBWR background process writes the changed chunks of data that remain in the data cache to the data file. Transaction fallback tasks performed: 1, undo all changes that have been made. Read from the generated undo message. If the insert performs a delete operation, the insert operation is performed if it is deleted. If it is a change, change it to the original data.  2, frees system resources that are consumed by each SQL statement in the transaction, and unlocks locks on the operands involved. 3, return the corresponding prompt code information to the user. 4, marks the transaction as completed. Save points SavePoint is some intermediate flag in a transaction that divides a large transaction into a few short sections. This enables fallback operations for partial transactions. Partial transaction fallback because you have a save pointTo implement partial transaction fallback. Syntax: rollback to savepoint spname or ROLLBACK to spname; the task performed by a partial transaction fallback operation: 1, undo changes made by all statements after the savepoint, but retain the changes before the save point.  2, frees the system resources that are consumed by the individual SQL statements after the savepoint, and unlocks the operation objects involved, but retains the system resources and the locks on the operands involved that were consumed by the individual SQL statements before the savepoint. 3, returns a success prompt code message to the user that is rolled back to the savepoint. 4, the user can continue to execute the current transaction. Transaction Properties 1, read and write; set TRANSACTION Read write The default setting for transactions; 2, read-only; set Transactionread only; Execution in this state: SELECT, Locktable, SET ROLE, ALTER system/session; 3, isolation level a, Read committed: SET TRANSACTION IsolationLevel Read committed The data that is manipulated by each DML statement in the transaction, Is the data that has been submitted before the statement begins. It provides statement-level consistency. Dirty reads do not occur at this isolation level, but may result in non-repeatable reads and Phantom reads (because at this isolation level, Oracle does not prohibit other transactions from manipulating the data of the current transaction (increment, delete, change)). Is the default isolation level. b, serialization: SET TRANSACTION isolation Level SERIALIZABLE; The data that is manipulated by each DML statement in a transaction is the data that was committed before the start of the transaction, and the DML statement can be executed to change the data in the database and to see the result of the change. Dirty reads, non-repeatable reads, and phantom phenomena do not occur at this isolation level (read only also does not have dirty reads, non-repeatable reads, phantoms). It provides transactional-level consistency. Timing of delayed constraint testing You can use the SET CONSTRAINTS statement to specify the timing of a test for a deferred constraint that is involved in a transaction. Syntax: setconstraints {constraint_name1 [, constraint_name2]...| all} {immediate| DEFERRED}; immediate Represents an immediate checksum after each DML statement, deferred means verifying the state and nature of the; transaction at the time the transaction is committed The different states that a transaction experiences from start to finish have: transaction initial state, active state, failed state, abort state, commit status. 4 Large Features: 1, atomicity (atomicity) that is, a transaction is an indivisible logical unit, and all operations in a transaction either succeed or fail.  2, Consistency (consistency) The result of a statement, a transactional operation, must be that all data in the database is in a logically consistent state.  3, Isolation (isolation) that is, the execution of a transaction, can not be disturbed by other transactions. That is, the operation within one transaction and the data used are isolated to other transactions, and can not interfere with each other in concurrently executing transactions. Only the user of the transaction can see the data being modified before committing, while the user of the other transaction can only see the data before the modification. isolation resolves: dirty reads (one transaction reads to another transaction, not yet committed, changed data); non-repeatable read (when one transaction reads some data, another transaction modifies the data and commits it.) When this data is read again, it is found that the data has been modified. Phantom (when one transaction reads some data, another transaction inserts or deletes some rows that match the query criteria, and when the query is queried again, it finds that the records that meet the query criteria are more or less.) 4, persistence (durability) that is, once a transaction is successfully committed, its modifications to the data in the database are permanently preserved. oracle is used to saveThe information in the undo segment provides a consistent view of the statement or transaction. All uncommitted transactions and data before the most recently committed transaction modification are saved in the Undo segment as "Pre-image". Read conformance Implementation mechanism (execution principle) when the: query statement enters the execution state, Oracle assigns it a current SCN. When the query statement searches the data buffer for the required block of data, only the data blocks that are less than or equal to the SCN are read. If the desired data block's SCN is larger than the SCN, then the query statement will get the original version of the corresponding block from the undo segment, and the SCN of that version also needs to be less than or equal to the SCN, so that the query statement will only return data that was submitted before it was started by comparing the data block SCN. Ensures that dirty blocks generated by uncommitted transactions are not read, and that data blocks that are committed after the query statement begins execution are not read. Transaction and database recovery principles The basic unit of database recovery is transaction. The recovery mechanism of a database consists of a recovery subsystem and a specific data structure. The basic principle of recoverability is to repeatedly store data, i.e. data redundancy . data dumps manually fire the use of utilities to export or copy parts or entire databases to a specified drive letter or to a saved process on the media. Files obtained after being dumped are referred to as backups or replicas. by dump when the state is divided into: static dump and dynamic dump static dump: Dump without a transaction running, that is, the database is in a consistent state operation. The resulting copy must be consistent. However, the availability of the database is reduced. Because no transactions can be run during a dump. Dynamic Dump: You can perform a dump operation without waiting for the transaction to stop. The resulting copy does not guarantee consistency. A copy of the dump plus the transaction log file after the dump is able to restore the data to a consistent state at a certain point in time. by dump: Mass dump and incremental dump Mass dump: The entire database will be dumped every time. Incremental dumps: data that has been modified since the last dump is dumped at each time. Log files are files that are used to record transactions outside of a data file for modifications to the database. The data file records the result of the operation. The log file records the transaction and its operations. The following important information is generally logged in the log file: 1, the identity of the transaction. (start tag, end tag, or unique identification number automatically generated); 2, type of operation (add, delete, change), 3, Action object,  4, pre-modified data (block) (empty for insert); 5, modified data (block) (for deletion, this is empty) types of; failures and recovery strategies Fault classification: Transaction failure, system failure, media failure basic strategy for recovering a database: Periodic dump DATABASE, registration date in file, Use different methods for different failures, consider the impact of data loss on the business. Transaction failure and recovery strategy Transaction failure is the failure of a thing to be aborted before it runs to its normal end. Recovery method is automatically restored by Oracle system. That is, the reverse scan day in the file, looking for a modified operation for the failed transaction. The modification of the transaction performs an inverse operation until the start tag of the transaction. system failure and recovery strategy refers to any event that causes the DBMS to stop functioning, making it necessary to restart the DBMS. Recovery method is automatically restored by Oracle system. That is, a forward scan log file, looking for transactions that have been committed before the failure occurred, marking their transactions as a redo (redo) queue, while looking for transactions that have not been completed at the time of the failure, marking their transactions as undo queues, and then redo each transaction in the redo queue (that is, forward scan log files , re-executes the operation that is enlisted in the log file for each transaction that needs to be redo, and then writes the result of the operation to the data file. Finally, each transaction in the undo queue is undone (that is, a reverse scan of the log file, which reverses the individual modifications of each transaction. ）。 Media failure and recovery strategy media failure refers to hardware failure (disk damage, circuit failure, etc.). Recovery method is to reinstall the database first, then redo all completed transactions (that is, 1, load the most recent dump copy from the time of the failure. In the case of a dynamic dump, you also need to mount a copy of the log file at the start of the dump, taking advantage of the method of recovering the system failure, so that you can revert to the database consistency state at the beginning of the dump. 2. Load a copy of the log file closest to the time of the failure, and restore the database to the consistent state when the log file was dumped, using the method of recovering the system failure. 3. Rerun the transaction from the most recent dump log file to any modified data at the time of the failure. To restore the database to a consistent state at the point of failure). Database recovery technology with checkpoint   a database recovery technique with checkpoints is the addition of a new class of records (Checkpoint Records) to the log file, and an additional restart file. The DBMS dynamically maintains the log file, mainly doing the following: 1, temporarily aborting the execution of the existing transaction,  2, writes the log records in the current log buffer in memory to the log file; 3, Writes the data in the current data buffer in memory to the data file; 4, writes the checkpoint record in the log file (content: All the transactions that are executing when a checkpoint event occurs; The address of the most recent log record for these transactions;), And writes the checkpoint record in the log file to the restart file, 5, and restarts the existing transaction. Database recovery steps with checkpoints; 1, finds the last checkpoint record in the log file from the restart file, and then finds the last checkpoint record in the log file; 2, The checkpoint records the list of all the transactions that are executing at the checkpoint time, starting at the checkpoint, 3 the log file, and placing it in the undo queue if there is a newly started transaction (that is, a transaction that has not been committed at the checkpoint, or that was started after the checkpoint). If the new transaction has been committed before the failure occurs, it is placed in the redo queue from the revocation queue. 4, the address of the most recent log record of the transaction is recorded by the checkpoint, thus obtaining the contents of the log record, 5, and performing an undo operation on the transaction in the undo queue to perform a redo operation on the transaction in the redo queue, by logging the contents. oracle database backup and Recovery overview Physical backup (physical Backups): The files that will be backed up are data files, log files, Initialize the parameter file, redo log file, control file, etc. from one location to another. logical backup (Logical Backups): Use a tool or command such as a data pump (EXPDP) to read the data in the database and then write to a file. Database recovery is divided into: Database repair, database recovery Database restore: Refers to the use of backup files to replace the damaged files. Database Recovery (database recovery): Re-establish lost data using redo log files or data files. In addition to a media failure that requires DBA involvement in the repair and recovery of the database, ORACLE automates crash recovery (crash recovery) and routine recovery (instance revovery) in the event of a transaction failure or system failure in oracle.
