Oracle redo complexity-oracle Core Technology Reading Note 3, redo-oracle

Oracle redo complexity-oracle Core Technology Reading Note 3, redo-oracle

I. Overview

We know that in oracle, Every modification to a piece of data generates a redo (that is, redo, which records the modified content ). The purpose is to back up the modified data for future redo. Now there is a problem. Redo logs are generated when data is modified in oracle. These redo logs are stored in a redo log buffer. If you modify data in multiple calls at the same time, you need to write content to the redo log buffer, and there is a competition for the same memory area. If there is competition, there will be overhead. This article will introduce how to minimize this overhead in oracle.

Ii. Problem Overview

Oracle constantly modifies data and generates redo logs continuously. Oracle first writes these logs to the redo log buffer (a memory area), and then calls the LGWR process to write these logs to the online log files on the disk (generally three, A 50 m image ). After an online log file is fully written, switch to another log file. If the archive mode is enabled for oracle, a copy of the last fully written log file will be copied when the log file is switched, which is called an archive log file, save it to one place (maybe another server to prevent unexpected occurrence and facilitate instance recovery or media recovery ). It seems that everything is perfect, and the problem lies in writing logs to the redo log buffer. If the concurrency is large and a lot of data is modified in every response, therefore, competition for redo log buffers becomes extremely fierce. It is very likely that the cpu will spend a lot of time on latch spin (that is, it takes up the cpu time, but the cpu has nothing to do ).

Iii. Solutions

Problem 1: A session constantly modifies data and needs to constantly write content to the redo buffer (redo log buffer, that is to say, we need to constantly obtain redo allocation latch (a lock that protects the redo log buffer and controls concurrency ). Efficiency is greatly affected, especially when high concurrency occurs, you may not be able to get the lock.

Can I generate all the redo first and then obtain a redo allocation latch Lock? The answer is yes. After the 10 GB version of oracle, a private redo buffer is provided, that is, a private redo buffer is allocated for each transaction. You should first generate all the redo logs here. When a transaction is committed, obtain a redo allocation latch Lock and copy the content in the private redo to the public redo buffer.

Extended Question 2: if the data is constantly modified, the undo data block will be generated continuously, and the change of the undo data block will also generate the redo buffer. These must be paired with the redo buffer that describes data changes, and must be written to the redo log file at the same time. The reason is as follows: when a dirty data block is written into the disk data block, you must first write the redo records corresponding to the changed redo record and the undo data block that records the old data to the redo log file on the disk. Otherwise, Data Consistency cannot be ensured. Before introducing private redo, oracle treats the undo block before the description data block changes as a general data block for processing. The undo block is changed, and a redo is generated and output to the redo log file. The previous mechanism has the following problems: if another session needs to search for old data in the undo block, but the undo block has been refreshed and output to the disk, it needs to be retrieved from the disk, there is a certain I/O overhead. At the same time, after private redo is introduced, the redo and undo that describe a data change cannot refresh the output to the redo log file together.

Therefore, oracle introduces an IMU mechanism, that is, a private memory area is opened for a transaction in the memory area, which is used to store redo records that describe undo data block changes. If a transaction modifies 10 data records, 10 undo data records are generated, and 10 redo records describing undo changes are generated and stored in IMU. In addition, 10 redo records describing data changes are stored in private redo records. There are a total of 20 redo records. If the transaction is committed, the 20 redo records will be merged into one redo record and copied from IMU and private redo to the public redo buffer. Of course, if the transaction has not been committed, but the dbwn process needs to refresh three of the dirty data to the disk, the three redo records are obtained from imu and private redo respectively. A total of six redo records are merged into one redo record, and then copied to the public redo buffer to refresh the output to the disk. After redo refresh is successfully output to the disk, dbwn can refresh the dirty data and output it to the disk.

For this reason, if a transaction modifies a lot of data, the oracle operations are as follows:

1. Obtain the pair of private memory structure (that is, private redo: used to store the change of description data blocks and IMU: used to store the change of description corresponding to the undo data block) and start the transaction.

2. Modify the data and mark each affected data block (to indicate "having a private memory structure"), but it does not actually change the data.

3. Write each restored Change vector (that is, the description of undo data changes) to the IMU pool.

4. Write each redo Change vector (that is, the description of data block changes) to the private redo area.

5. Merge the vectors of the two memory structures into a redo change record.

6. Copy the redo change record to the redo log buffer (Public redo buffer) and change the data block. (In this case, the transaction has not ended, and it is impossible for the dbwn process to write dirty data to the disk, because the data block is changed at the end)

How to change the complexity of oracle Database User passwords

Oracle Password Complexity settings (Oracle_Password_Complexity)

I. Oracle_Password_Complexity:

SQL> alter system set resource_limit = true;

SQL> @ $ ORACLE_HOME/RDBMS/ADMIN/utlpwdmg. SQL → [verify_function | verify_function_11G]

SQL> alter profile default limit password_verify_function verify_function;

# Cancel the Oracle Password Complexity check:
SQL> alter profile default limit password_verify_function null;

SQL> SELECT profile, resource_type, resource_name, limit FROM dba_profiles WHERE resource_type = 'Password' AND profile = 'default ';

1. FAILED_LOGIN_ATTEMPTS: the user is locked after the logon attempt fails n times.

2. PASSWORD_LOCK_TIME: the number of failed logon attempts reaches the specified number of times. The user lock duration, in the unit of "Day.

3. PASSWORD_LIFE_TIME: the lifecycle of the user password.

4. PASSWORD_GRACE_TIME: indicates the number of days that a user's password can be used after its life cycle is exceeded. A prompt indicating that the password is about to expire is displayed during the logon period.

5. PASSWORD_REUSE_TIME: specifies the number of days before the password cannot be reused.

6. PASSWORD_REUSE_MAX: the number of times that the password must be changed before it reaches the time specified by PASSWORD_REUSE_TIME.

For example, if PASSWORD_REUSE_TIME = 30 and PASSWORD_REUSE_MAX = 10, you can reuse the password after 30 days. The password must be changed more than 10 times.

7. PASSWORD_VERIFY_FUNCTION: Oracle allows passing complex PL/SQL password verification scripts as parameters to PASSWORD_VERIFY_FUNCTION. In addition, it provides a default script, but users can create their own verification rules or use third-party software for verification.

8. Password Verify Function:

When you create a password verify function for verifying the user password, this function can verify the following password characteristics:

1. The minimum number of characters for the password.

2. The characters that the password must contain, such as when a password shoshould contain a specific number of numeric, alphabetic or... The remaining full text>

The oracle password must contain at least a few characters.

You need to check the configuration.
Modify the PASS_MIN_LEN value in/etc/login. defs. For example, the minimum password length is 8:
PASS_MIN_LEN 8
In this way, the password cannot be set if the length of the entered password is less than 8.