Frequently generate archived log processing reports

Frequently generate archived log processing reports

On October 27, 2017, the customer database suddenly generated a large number of archived logs frequently, resulting in the database hang living, affecting on-site production! Since oracle archive logs are generally generated by dml statements, increasing too fast should be because dml is too frequent.

First of all, query the following hourly connection log switching frequency:

Select to_char (first_time,'yyyy-mm-ddhh34') first_time,count (*)

From v$log_history

WHERE TO_CHAR (FIRST_TIME, 'yyyymmdd')

Group by to_char (first_time,'yyyy-mm-ddhh34') order by FIRST_TIME desc

You can see that the database switches online logs more than 650 times per hour, and the size of each connection log is 50m. When the switch occurs, the online log is written to the archive log, so the archive log increases rapidly. More than 30 gigabytes of archive logs are generated in an hour.

First of all, through the AWR report, you can see that the main load of the database is to generate a large number of redo logs:

The key is to find the appropriate sql statement to determine what happens to the problem. So we use the logminer tool to analyze the corresponding archive log to find the corresponding sql statement:

View the path and name of the archive log file on disk:

Path view statement: show parameter db_recovery!

Locate the archive log in the appropriate directory:

1, use scripts to create related packages

@ $ORACLE_HOME/rdbms/admin/dbmslm.sql

@ $ORACLE_HOME/rdbms/admin/dbmslmd.sql

The first script is used to create the DBMS_LOGMNR package, which is used to analyze log files.

The second script is used to create the DBMS_LOGMNR_D package, which is used to create data dictionary files.

two。 Specify the log file to analyze

Exec sys.dbms_logmnr.add_logfile (logfilename= >'+ recoverydata/orcl/adchivelog/2017_10_27/thread_2_seq_148046.384.958475205',options= > dbms_logmnr.new)

3. Analyze archive logs using a local online data dictionary

Exec sys.dbms_logmnr.start_logmnr (options= > sys.dbms_logmnr.dict_from_online_catalog)

4. Query the contents of the analyzed archive log, such as the Schema that counts the maximum modification.

Select seg_owner,count (*) fromv$logmnr_contents group by seg_owner

Select count (1), substr (sql_redo,1,30) fromv$logmnr_contents group by substr (sql_redo,1,30) order by count (1) desc

From the figure above, you can see a large number of rollback operations in the database. Rollback is generally caused by the failure of the database DML statement, so check the trace log and find that there are a lot of error messages in the database! As shown in the following figure:

ORA-1654: unable to extend indexMES.PK_BBM_TRACE_INFO by 128 in tablespace MESDB_BASE_DATA01

ORA-1654: unable to extend indexMES.PK_BBM_TRACE_INFO by 1024 in tablespace MESDB_BASE_DATA01

ORA-1654: unable to extend indexMES.PK_BBM_TRACE_INFO by 128 in tablespace MESDB_BASE_DATA01

ORA-1654: unable to extend indexMES.PK_BBM_TRACE_INFO by 1024 in tablespace MESDB_BASE_DATA01

ORA-1654: unable to extend indexMES.PK_BBM_TRACE_INFO by 128 in tablespace MESDB_BASE_DATA01

ORA-1654: unable to extend indexMES.PK_BBM_TRACE_INFO by 1024 in tablespace MESDB_BASE_DATA01

ORA-1654: unable to extend indexMES.PK_BBM_TRACE_INFO by 128 in tablespace MESDB_BASE_DATA01

ORA-1654: unable to extend indexMES.PK_BBM_TRACE_INFO by 1024 in tablespace MESDB_BASE_DATA01

The error report has been clearly stated because the index cannot be expanded in the tablespace! Query the corresponding table space, found that the table space is only 2G size, increase the table space to 30G, and set the table space to grow automatically, the maximum infinite!

After the change, the switching frequency of querying the online log again is as follows:

At this point, it is found that the size of the query archive log has not increased.