Shulou(Shulou.com)06/01 Report--

"Oracle-redo"

Redo log files (redo log file) are very important to the database. They are the transaction logs of the database.

The Oracle database maintains two types of redo log files: online redo log files (redo) and archived redo log files (archive log), (archived redo log files are actually copies of filled "old" online redo log files)

Both types of redo log files are used for recovery:

①: if the host where the database is located is powered down, causing the instance to fail, Oracle will use the online redo log to restore the system to the commit point just before the power outage

②: if the disk drive fails (this is a media failure), Oracle uses the archived redo log and the online redo log, as well as a previous backup, to restore the data originally on this drive to a certain point in time

Each Oracle database has at least two online redo logs, and each group has at least one member (redo log file). The online redo log group is used in a circular manner.

The log status of Redo log can be divided into four types:

Current: refers to the current log file, which is active and currently in use. Current log file is required for crash recovery.

ACTIVE: is an active non-current log, which may or may not have been archived, and will be used during Crash recovery

Inactive: inactive logs, which are no longer needed during instance recovery, but may be used during media recovery, and logs in this state may not be archived.

Unused: this log has never been written, and this type of log may have just been added to the database or reset after resetlogs.

"Oracle----undo"

REDO is to reimplement your operation, while UNDO, on the contrary, is to undo what you have done. For example, if you have a TRANSACTION execution that fails or you regret it, you need to use the ROLLBACK command to fall back to before the operation. Rollback is implemented at the logical level rather than the physical level, because in a multi-user system, the data structure, blocks and so on all change from time to time. For example, we INSERT a data, there is not enough space in the table, we extend a new EXTENT, our data is stored in this new EXTENT, and other users then insert data into this EXTENT, and at this time I think ROLLBACK, then it is obvious that it is physically impossible to undo this EXTENT. Because doing so will affect the operations of other users. So, ROLLBACK is a logical rollback, for example, for INSERT, then ROLLBACK is DELETE.

Note: redo is used to recover a transaction on failure, and undo is used to cancel the function of a statement or set of statements. Unlike redo, undo is stored in a special set of segments within the database, called undo segments

1 、 insert:

The first insert into t statement generates both redo and undo, which locks enough undo information to make insert hours, and redo information enough to make the insert happen again. We can see that the modified undo blocks, index blocks, and table data blocks are stored in the block buffer, all of which are protected by the corresponding entries in the redo log buffer.

2 、 update

Update generates more Undo than insert because update needs to save the image before the data is modified; the update statement is also placed in the redo log buffer

3 、 delete:

Delete generates undo, the block is modified, and the redo is sent to the redo log buffer

4 、 commit

When a transaction commits, Oracle flushes the redo log buffer to disk

Note:

Insert generates the least undo; (because Oracle only records a rowid)

Delete generates the most undo; (because Oracle must record the pre-delete image of the entire row in the undo segment)

(the maintenance of the index is also very high, for example, updating indexed columns will generate several times the undo)

The cost of commit mainly comes from two aspects:

First, the direct round-trip traffic between the client and the database will increase significantly.

Second: every time you submit, you must wait for redo to write to disk (in this case, a wait event for log file sync will occur)

When performing the commit, the remaining work is as follows:

1. Generate a SCN number for the transaction

2. LGWR writes all redo log entries not written to disk to disk, and records SCN to online redo log file.

3. Our return locks will be recorded in the v$lock, these locks will be released, and the sessions waiting in line for these locks will be awakened so that they can continue to complete their work.

4. If some of the blocks modified by the transaction are still in the buffer cache, Oracle will access and clean up in a fast mode

The main differences between redo and undo logs:

1. Undo log: cancel the impact of outstanding transactions during recovery and ignore transactions that have already been committed

2. Redo log: ignore outstanding transactions and redo changes to committed transactions.

3. Undo log: first write the modified data to disk-- write commit to disk

4. Redo log: write commit to disk first-- write modified data to disk

5. Undo log: data is required to be written to disk immediately after the transaction ends, which may increase the number of disk IO

6. Redo log: requires us to keep all modified blocks in the buffer before transaction commit and log record refresh, which may increase the average number of buffers required by the transaction.

"temporary watches and redo, undo"

Before 12C:

The data blocks of temporary tables do not generate redo, so the operation of temporary tables is not recoverable. However, temporary tables generate undo, and this undo is counted in the log, so temporary tables also generate some redo; temporary tables that can be constrained, or you can say that all temporary tables that are in a normal table can have

The DML operation of temporary table has the following characteristics:

1. Insert generates little or no undo redo

2. The update of the temporary table will generate about half of the redo of the permanent table update.

3. Delete generates as much redo on temporary tables as redo on permanent tables.

(temporary table delete generates a lot of redo, so to avoid delete operations, truncate; can use temporary tables for insert and select operations.)

After 12C:

From Oracle12C, we can put the undo of temporary table in temporary tablespace by setting temp_undo_enabled. Since any data change in temporary tablespace will not generate redo, when this parameter is set to TRUE, DML on any temporary table will produce little or no redo.

(the default value of temp_undo_enabled is FALSE, and if you change it to TRUE, the amount of redo generated by the temporary table is the same as before version 12C)

ORA-01555: snapshot too old:-this error is a very typical error: (it is considered to be a security error, the only effect is that the query that received this error cannot continue to be processed.)

The reason for this error:

1. The undo segment is too small to support the work performed on the system

2. The program praises commit for obtaining data.

3. Block cleanup

4. Commit is submitted too frequently

Reference for the solution:

1. With the appropriate setting parameter undo_retention (to be longer than the longest running transaction), we can determine the duration of the long-term query through v$undostat. In addition, please reserve enough space on the disk, because in order to ensure that the undo_retention,undo segment may also grow.

2. Increase or add more undo segments when using manual undo management (this method is not recommended, but automatic undo management is strongly recommended)

3. Reduce the running time of the query (tuning). Tuning sql statements,

4. Collect the statistical information of related objects.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.