Basis and others: Note 584548 - Unusually high number of redo logs

Summary

Symptom

The system generates an unusually high number of redo logs, which cannot be explained by the R/3 load. If you do not recognize this situation in time, the archive directory may fill up and the system may shut down because the archiver is stuck.

Other terms

Redo log, frequency

Reason and Prerequisites

Redo log information is always written if data is changed in the database. Depending on the application, the redo log frequency may vary. For example, more redo log data is accumulated during peak times in dialog mode or during a batch job involving many changes than during times of low R/3 activity.

If the redo log frequency increases significantly from one minute to the next and remains at a high level for a long period without an application-related explanation, the following causes are possible:

1. The tablespaces are in backup mode

2. Oracle 9.2.0.3 or lower: Bug with parallel rollback

3. Oracle 8.1.7 (8.1.7.3 or lower): Bug with index-cluster-DML

4. There is an application-related malfunction

5. There are inconsistencies in the Oracle dictionary

6. Materialized views are refreshed

7. An error occurs when you delete DDLOG

8. You create, rebuild or coalesce indexes

9. Table conversions (for example, as part of transports)

10. There is a tablespace overflow or a MAXEXTENTS error

11. ASSM, Oracle 9.2.0.6 / 9.2.0.7: Oracle bug

12. Advanced Compression (Oracle 11g or higher)

13. Supplemental Logging Solution

1. During an online backup, you must convert tablespaces to backup mode before you save them to disk. If you then make changes in one of these tablespaces, the system not only writes the change to the redo log, but also the entire related 8K block. As a result, the volume of redo log data increases significantly. If an online backup terminates prematurely and the tablespaces are no longer taken from the backup mode, more redologs are constantly written. You should therefore check, according to Note 4162, whether there are still any tablespaces in backup mode and, if necessary, set them to "End backup".

              If the increased redo log generation only occurs during an online backup, you should check whether you can only set the tablespaces you have just saved to backup mode rather than all tablespaces from start to finish. This means that the complete blocks must only ever be recorded for individual data files in the redo logs. If, when you use BACKINT, you want to set to backup mode only those tablespaces of the data file to be saved at that moment, you can convert the BR*TOOLS parameter BACKUP_DEV_TYPE from UTIL_FILE to UTIL_FILE_ONLINE.

2. If the fast_start_parallel_rollback parameter is set to "true", this may lead to an increased redo log frequency due to an Oracle bug. See Note 581359 for information.

3. In very rare cases, the bug from Note 515762 may occur, resulting in an increased occurrence of redo logs.

4. In many cases, an exceptionally high occurrence of redo logs may be caused by an R/3 application that is running incorrectly. Therefore, you should check the following points:

a) Are (long) INSERT-, UPDATE- or DELETE operations repeatedly displayed on the same tables in transaction SM66?

b) Does the syslog (SM21) display rollbacks at intervals of one minute? If so, check what the associated work process is doing.

c) Are there frequently executed INSERT, UPDATE or DELETE operations in the SQL cache (ST04 -> Detailed Analysis Menu -> SQL request) that change a large number of entries?

d) Do the times of the log switches mostly follow a particular rule (for example, always at intervals of five minutes or a multiple of five minutes)?

              If you can answer "Yes" to any of these questions, you should check the triggering program (for example, using Transaction SM66) more closely. If you suspect that a malfunction has occurred (such as an endless loop, with alternating large-scale INSERTs and DELETEs on a table and so on), check whether the initializing program is being used correctly or whether you can implement another solution (for example, less regular scheduling, changed configuration).

              You can use the following SQL statement to determine the 20 INSERT/UPDATE/DELETE statements that were used to process the largest number of data records up to now, and thereby largely contributed to the redo log load:

SELECT * FROM
(SELECT SUBSTR(SQL_TEXT, 1, 64), ROWS_PROCESSED
FROM V$SQL
WHERE ( UPPER(SQL_TEXT) LIKE 'INSERT %' OR
UPPER(SQL_TEXT) LIKE 'UPDATE %' OR
UPPER(SQL_TEXT) LIKE 'DELETE %' ) AND
ROWS_PROCESSED > 0
ORDER BY ROWS_PROCESSED DESC)
WHERE ROWNUM <= 20;

              In addition, you can determine the segments that involve most changes as follows:

SELECT * FROM
( SELECT
    SUBSTR(OBJECT_NAME, 1, 30) SEGMENT,
    SUBSTR(STATISTIC_NAME, 1, 20) STATISTIC_NAME,
    VALUE
  FROM V$SEGMENT_STATISTICS
  WHERE STATISTIC_NAME IN ('db block changes', 'physical writes')
  ORDER BY VALUE DESC )
WHERE ROWNUM <=20;

              If you notice that an AutoABAP started under the SAPSYS user (report RSCORE00) tries unsuccessfully to write entries into the SNAP table every five minutes, you can solve the problem by deleting the triggering coreinfo files in the work directory.

              If you use DELETE instead of DROP PARTITION (SAP Note 1816370), this may cause a high redo log rate in the BW environment.

              If you cannot find a solution and the problem concerns a function of the SAP standard system, open a message with SAP.

5. A high occurrence of redo logs may also be caused by an inconsistency in the Oracle dictionary. This happens particularly if large-scale redo logs are still written even after you stop the R/3 System and restart Oracle.

              In addition, you find more active transactions under V$ROLLSTAT (XACTS column) than in V$TRANSACTION.

              If this scenario applies to you, open a message with SAP to eliminate the inconsistency.

6. Under certain circumstances, a significant amount of redo log information is written when you refresh materialized views. See also Note 741478.

              are logged in the syslog. If so, check the developer trace of the relevant work process (transaction ST11) for the triggering error. Rollback segment errors such as ORA-01562 often occur when you access the DDLOG table. In this case, the problem can be solved as follows:

Stop all R/3 instances.

Since DDLOG contains data for the buffer synchronization, do not hesitate to empty the table if R/3 is stopped:

sqlplus <sapuser>/<password>
TRUNCATE TABLE DDLOG;

Now restart the R/3 instances.

8. When you create indexes using create, rebuild or coalesce, all index data is logged in the redo logs. Therefore, check whether such activities (for example, as part of regular index defragmentation) are responsible for the high quantity of redo logs. If this is the case, you can consider using NOLOGGING. As a result, redo log information no longer accrues. However, in the case of a recovery that goes beyond the period of setup, the index is damaged and must be set up again after this. If FORCE LOGGING was activated at database level, NOLOGGING has no effect.

9. If changes are made to table definitions as part of transports (for example, if additional columns are added), this is often associated with a significant increase in the number of Redo logs. The RDDEXECL job runs in this case. Therefore, check whether transports and long-running RDDEXECL executions occurred during the period in question.

10. If you cannot successfully complete a change activity that was executed cyclically due to an error on Oracle, such as a tablespace overflow or reaching the MAXEXTENTS limit, and if the changes therefore keep being rolled back, this can lead to a significant increase in the redo log volumes. Therefore, check the syslog and the Oracle-Alert log to see if these error messages are accumulating and remove the problem that triggers them. See also Notes 3155 and 533455.

11. Use ASSM and Oracle 9.2.0.6 or 9.2.0.7, see Note 912918.

12. Using the OLTP table compression as part of the advanced compression option (Oracle 11g or higher) causes a considerable increase of the redo log volume. If required, change-intensive tables should not be compressed to reduce the burden on the Oracle log writer (LGWR).

13. The activation of supplemental logging at table level or database level also causes the redo log volume to increase and therefore, should only be used where it is technically required.

Header Data


Release Status:	Released for Customer
Released on:	13.08.2013 17:36:39
Master Language:	German
Priority:	Recommendations/additional info
Category:	Help for error analysis
Primary Component:	BC-DB-ORA Oracle