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

The following error occurred in the database

Analysis:

When the database switches logs, all private strand must be flushed to the current log before continuing. This information indicates that we have not fully written all the redo information to the log when we tried to switch. This is somewhat similar to "checkpoint not complete", except that it only refers to the redo that is being written to the log. Logs cannot be switched until all redo are written.

Private Strands is unique to 10gR2 and is used to deal with redo's latch (redo allocation latch). Is a mechanism that allows processes to write redo to redo buffer cache more efficiently using multiple allocation latch, which is related to the log_parallelism parameter that appears in 9i. The concept of Strand is proposed to ensure that the redo generation rate of the instance is optimal, and to ensure that the number of strand can be dynamically adjusted to compensate when some kind of redo contention occurs. The number of strand initially allocated depends on the number of CPU, with at least two strand, one of which is used for redo generation of active.

For large oltp systems, the amount of redo generated is very large, so when the foreground process encounters redo contention, these strand are activated. Shared strand always coexists with multiple private strand. There are some major changes to the redo (and undo) mechanism of Oracle 10g in order to reduce contention. This mechanism no longer records redo in real time, but first records it in a private area and flush to redo log buffer when commit. After the introduction of this new mechanism, once the user process applies to the private strand,redo and is no longer saved to the pga, the redo copy latch process is no longer needed.

If the new transaction fails to apply for the redo allocation latch of private strand, it will continue to follow the old redo buffer mechanism and write the request to shared strand. For this new mechanism, when redo is written out to logfile, LGWR needs to write out the contents of shared strand and private strand. When redo flush occurs, the redo allocation latch of all public strands needs to be obtained, the redo copy latch of all public strands needs to be checked, and all private strands that contains active transactions needs to be held.

In fact, this phenomenon can also be ignored, unless there is a significant time difference between "cannot allocate new log" information and "advanced to log sequence" information.

If you want to avoid Private strand flush not complete events in alert.log, you can do so by increasing the value of the parameter db_writer_processes, because DBWn triggers LGWR to write redo to logfile, and if there are multiple DBWn processes writing together, you can speed up redo buffer cache writing to redo logfile.

Resolve:

You can modify it using the following command:

SQL > alter system set db_writer_processes=4 scope=spfile;-- this parameter is static and takes effect after restarting the database

Note that the number of DBWR processes should be equal to the number of logical CPU. In addition, when oracle finds that a DB_WRITER_PROCESS cannot complete its work, it automatically increases the number of DB_WRITER_PROCESS, provided that the maximum allowable value has been set in the initialization parameters.

If the system supports AIO (disk_async_io=true), it is generally not necessary to set up multiple dbwr or io slaves.

It is recommended to use DB_WRITER_PROCESSES if there is more than one cpu, because there is no need to simulate asynchronous mode in this case, but be aware that the number of processes cannot be greater than the number of cpu. When there is only one cpu, it is recommended to use DBWR_IO_SLAVES to simulate asynchronous mode in order to improve database performance.

If there is an obvious time interval between "cannot allocate new log" and "advanced to log sequence", you should consider adding db_writer_processes

The mos documentation recommends adding db_write_processes to increase the write-out rate of dirty blocks by increasing db_write_processes. Personally, I think the relationship with io should be

Maximum。 There are also parts of the bug that cause the prompt to be thrown. Increase the redo group and increase the size of redo file, thus reducing the number of log switch, the effect may be better.

There is also a message that "cannot allocate new log" appears.

This is a common mistake. Generally speaking, log groups are switched when the log is full, which triggers a checkpoint,DBWR to write dirty blocks in memory to the data file. As long as the log group is not finished, the log group will not be released. If archiving mode is turned on, it will also be accompanied by the ARCH write archiving process. If the redolog is generated too fast, when the CPK or archiving is not completed, and the LGWR has already filled the rest of the log group and is about to write redolog to the current log group, a conflict will occur and the database will be suspended. And will always write an error message similar to the above in alert.log.

Analyze the reasons:

The server has three log groups G 1, G 2, G 3. When G1 is finished, write to G2, which requires archiving and checkpoint. Then the other two log groups continue to write. When both G2 and G3 are written, you have to write to G1, but the problem is that G1 has not yet completed the archiving and checkpoint operations. So then the police will be called.

Solution:

Add a few more log groups, and each log group has a little more space, which slows down time and leaves G1 plenty of time to complete archiving and checkpoint tasks. There will be no misreporting.

Procedure:

First check the log group status of the database

View online log groups: SQL > select * from v$log

View the members of the log group: SQL > select * from v$logfile

View the specific status of the log group: SQL > select group#,sequence#,bytes,members,status from v$log

GROUP# SEQUENCE# BYTES MEMBERS STATUS

1 28825 52428800 1 INACTIVE

2 28826 52428800 1 ACTIVE

3 28827 52428800 1 CURRENT

CURRENT: indicates the current log.

INACTIVE: dirty data has been written to the data block. This state can be drop.

ACTIVE: dirty data has not been written to the data block.

The log is only 50m too small.

Expand the log group size

SQL > alter database add logfile group 4 ('/ u01ax size size 500m)

Database altered.

SQL > alter database add logfile group 5 ('/ u01ax size) size 500m

Database altered.

SQL > alter database add logfile group 6 ('/ u01ax size size 500m)

Database altered.

Toggle log group

SQL > alter system switch logfile

System altered.

SQL > alter system switch logfile

System altered.

Note: the difference between alter system switch logfile and alter system archive log current.

Alter system switch logfile is switch logfile without waiting for the archive to be completed. If database does not have archive log mode enabled. Then there is no doubt about using this switch. In addition, log switching is also performed for the current instance in single instance database and RAC mode.

Alter system archive log current, on the other hand, needs to wait for the archiving to complete before switch logfile. Log switching is performed on all of these instances.

Overall, in the automatic archiving library, the results of the two commands are almost the same. The difference is that alter system archive log current takes longer than alter system switch logfile.

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.