Handling Method for Rapid growth of oracle archive logs

Handling Method for Rapid growth of oracle archive logs

Oracle archive logs are generally generated by dml statements, so the increase speed should be too frequent by dml.

First, query the generation of the following daily archives:

Select trunc (FIRST_TIME) "TIME ",
SUM (BLOCK_SIZE * BLOCKS)/1024/1024/1024 "SIZE (GB )"
From v $ ARCHIVED_LOG
Group by trunc (FIRST_TIME );

In this way, the exception occurred when archiving started during that time period.

You can use either of the following methods to analyze archiving logs:

Method 1: Use logminer

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 a data dictionary file.

2. Specify the log file to be analyzed
Exec sys. dbms_logmnr.add_logfile (logfilename => '/archivelog/node/410771348_78810967000.arc', options => dbms_logmnr.new );

3. Use a local online data dictionary to analyze archived logs
Exec sys. dbms_logmnr.start_logmnr (options => sys. dbms_logmnr.dict_from_online_catalog );

4. query the archived log content, such as the Schema that calculates the maximum modification volume.
Select seg_owner, count (*) from v $ logmnr_contents group by seg_owner;
Select count (1), substr (SQL _redo,) from v $ logmnr_contents group by substr (SQL _redo,) order by count (1) desc;

5. Add other log files
Exec sys. dbms_logmnr.add_logfile (logfilename => '/archivelog/node/415773333_78810967000.arc ');
Exec sys. dbms_logmnr.add_logfile (logfilename => '/archivelog/node1/415773334_788000067000.arc ');
6. End Analysis of archived logs
Exec sys. dbms_logmnr.end_logmnr;

Method 2: Use toad to view log information

Find logminer in the database and put the logs to be analyzed. The detailed execution records of the database are displayed, and the archived statements are displayed intuitively.

After finding out the statement, I will discuss with the developer whether it can reduce the execution frequency of some dml statements.