Fault analysis of Oracle Redo log log Group

Database platform: SunOS 5.8 generic_108528-23 sun4u SPARC sunw,ultra-enterprise

Database version: 8.1.5.0.0

Database symptoms: Database response is slow, application requests cannot be returned, business operations are stalled, and DBA intervention is required to diagnose and troubleshoot problems.

1. Log in to the database for inspection

First we log in to the database to check for symptoms.

After checking, all redo log groups for the data block are active except current:

Oracle:/oracle/oracle8>sqlplus "/As SYSDBA"
Sql*plus:release 8.1.5.0.0-production on Thu June 23 18:56:06 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0-production
With the partitioning and Java options
Pl/sql Release 8.1.5.0.0-production
Sql> select * from V$log;
group# thread# sequence# BYTES members ARC STATUS first_change# First_tim
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 520403 31457280 1 NO ACTIVE 1.3861E+10 23-jun-05
2 1 520404 31457280 1 NO ACTIVE 1.3861E+10 23-jun-05
3 1 520405 31457280 1 NO ACTIVE 1.3861E+10 23-jun-05
4 1 520406 31457280 1 NO current 1.3861E+10 23-jun-05
5 1 520398 31457280 1 NO ACTIVE 1.3860E+10 23-jun-05
6 1 520399 31457280 1 NO ACTIVE 1.3860E+10 23-jun-05
7 1 520400 104857600 1 NO ACTIVE 1.3860E+10 23-jun-05
8 1 520401 104857600 1 NO ACTIVE 1.3860E+10 23-jun-05
9 1 520402 104857600 1 NO ACTIVE 1.3861E+10 23-jun-05
9 rows selected.
Sql>/
group# thread# sequence# BYTES members ARC STATUS first_change# First_tim
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 520403 31457280 1 NO ACTIVE 1.3861E+10 23-jun-05
2 1 520404 31457280 1 NO ACTIVE 1.3861E+10 23-jun-05
3 1 520405 31457280 1 NO ACTIVE 1.3861E+10 23-jun-05
4 1 520406 31457280 1 NO current 1.3861E+10 23-jun-05
5 1 520398 31457280 1 NO ACTIVE 1.3860E+10 23-jun-05
6 1 520399 31457280 1 NO ACTIVE 1.3860E+10 23-jun-05
7 1 520400 104857600 1 NO ACTIVE 1.3860E+10 23-jun-05
8 1 520401 104857600 1 NO ACTIVE 1.3860E+10 23-jun-05
9 1 520402 104857600 1 NO ACTIVE 1.3861E+10 23-jun-05
9 rows selected.

We know that when a log switch occurs in a database (log switch), Oracle triggers a checkpoint (Checkpoint), and the checkpoint process (Checkpoint process,ckpt) notifies DBWR. Writer) process to perform a write operation. Log files cannot be overwritten or reused until the dirty data in buffer cache (dirty buffer) is protected by the log file and is not written back to the disk.

If the database is unusually busy, or if DBWR is written too slowly, the checkpoint may not be completed and Oracle has run out of all the log files. In this case, the database log cannot be generated and the entire database is paused, and the following information is logged in the log file:

Mon Jan 23 16:11:39 2006Thread 1 cannot allocate new log,
sequence 5871Checkpoint not complete　Current log# 2 seq# 5870 mem# 0:
+ORADG/danaly/onlinelog/group_2.260.600173851　
Current log# 2 seq# 5870 mem# 1:
+ORADG/danaly/onlinelog/group_2.261.600173853Checking the v$session_wait view, we can see many of the sessions in the log file switch (checkpoint incomplete) waiting.