A case of Oracle database failure

The following error occurred in the database

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/89/C6/wKioL1gcObWztHE_AADGHTpt7Vs609.jpg "title=" 1112. JPG "alt=" wkiol1gcobwzthe_aadghtpt7vs609.jpg "/>

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 completely written all the redo information to the log when we try to switch. This is somewhat similar to "Checkpoint not complete", but it only involves the redo that is being written to the log. The log cannot be toggled until all redo are written.

Private strands is 10gr2 only, it is used to process redo latch (redo allocation latch). is a mechanism that allows a process to more efficiently write redo to redo buffer cache using multiple allocation latch, which is related to the log_parallelism parameters that appear in 9i. The concept of strand is to ensure that the redo generation rate of an instance is optimal and that the number of strand can be dynamically adjusted to compensate for certain redo contention. The number of strand initially allocated depends on the number of CPUs, with a minimum of two strand, one strand for active redo generation.

For large OLTP systems, the redo generates very large amounts, so the strand is activated when the current process encounters redo contention. Shared strand always coexist with multiple private strand. Oracle 10g's Redo (and undo) mechanism has some significant changes to reduce contention. This mechanism no longer records redo in real time, but is recorded in a private area and flush into redo log buffer when commit. Once this new mechanism is introduced, the process of redo copy latch is no longer required once the user process requests that the private Strand,redo no longer be saved to the PGA.

If the new transaction does not apply to the private strand redo allocation latch, it will continue to follow the old redo buffer mechanism and request to be written to the shared strand. For this new mechanism, LGWR needs to write the contents of the shared strand and private strand when the redo is written to logfile. When redo flush occurs, all public strands redo allocation latch need to be fetched, all public strands redo copy latch need to be checked, all private st containing the active transaction Rands need to be held.

In fact, this phenomenon can be ignored unless there is a significant difference between the "Cannot allocate new log" message and the "advanced to log Sequence" message.

If you want to avoid the private strand flush not complete event in Alert.log, you can do so by increasing the value of the parameter db_writer_processes. Because DBWN will trigger LGWR to write redo to logfile, if there are multiple DBWN processes written together, you can speed up redo buffer cache write redo logfile.

Solve:

You can use the following command to modify:

Sql> alter system set DB_WRITER_PROCESSES=4 Scope=spfile; --When the parameter is static, the database must be restarted after it takes effect

Note that the number of DBWR processes should be equal to the number of logical CPUs. Additionally, when Oracle discovers that a db_writer_process cannot complete its work, it automatically increases its number if the maximum allowable value has been set in the initialization parameter.

If the system supports AIO (Disk_async_io=true), it is generally not necessary to set up multi-DBWR or IO slaves.

It is recommended to use db_writer_processes in cases where there are multiple CPUs, because there is no need to emulate asynchronous mode, but be aware that the number of processes cannot be greater than the number of CPUs. In the case of only one CPU, it is recommended to use Dbwr_io_slaves to emulate the asynchronous pattern in order to improve database performance.

If there is a significant time interval between "Cannot allocate new log" and "advanced to log Sequence", you should consider increasing the db_writer_processes

Max. There are also partial bugs that cause the hint to be thrown. Add Red O Group and increase the size of the redo file, thereby reducing the number of log switch, may effect

And there's this "cannot allocate new log" message.

can also

is a more common mistake. This is usually because the log group is toggled when the log is full and is triggered onceCheckpoint,DBWRThe dirty blocks in memory are written to the data file, and the log group is not freed as long as the write is not completed. If the archive mode is turned on, it will be accompanied byARCHthe process of writing an archive. IfRedo Logproduced too fast, whenCPKor the archive has not been completed,LGWRThe rest of the log groups have been filled, and the current log group is writtenRedolog, there will be a conflict at this time,Database will be suspended. And will always write a similar error message to the Alert.log.

Analysis Reason:
The server has three log groups G1, G2, G3. When G1 finished, to write to the G2, this time G1 to archive, but also to carry out checkpoint. Then the other two log groups continue to write. When G2 and G3 all finished, but also to write to G1, but the problem came, G1 has not finished archiving and checkpoint operations. So this will be the police.
Workaround:
Add a few more log groups, and each log group is a bit larger, so you can delay time and leave G1 sufficient time to complete the archive and checkpoint tasks. There will be no error.

This article is from the "Hollows Jie Sun" blog, be sure to keep this source http://xjsunjie.blog.51cto.com/999372/1869485

A case of Oracle database failure