Search ideas and cases of large amount of Oracle redo log

problem ： In the process of database operation and maintenance , You will often encounter a database system REDO The production has soared , It may lead to insufficient archive space and database HANG live 、DG The standby database has a large delay in receiving application archive logs 、RMAN Insufficient backup space failed 、REDO LOG The system is slow when switching frequency ;

analysis ： Generally speaking , such REDO The common reason for the explosion of production is to import data 、 Large table indexing 、 The big table SHRIK/MOVE Defragmentation 、 Business exceptions ( As normal 1 Hours 10 Time of DML Now? 1 Hours 1000 Time )、 New business programs that can manipulate a lot of data 、OGG And other logical replication tools have opened a large number of additional LOG etc. ;
screening ：
In determining REDO LOG After a specific period of time with a large amount of production , You can start further investigation ; For import and export classes , It's convenient to check , What is happening directly through OS The session information in the process or database can be checked . Index large tables 、 Defragmentation class , Through a database session or ASH/AWR The data in can also be checked out quickly ; In case of abnormal business or new business , Through a database session or ASH/AWR The data can be used to check the bound variables SQL, Those that do not use bound variables are more difficult to check ;

REDO The mass production of data corresponds to the change of data block (block change), If the above actions cannot quickly find the corresponding SQL; Can pass AWR or AWR The base table can be found for a period of time block change The most SEGMENT, adopt ASH The base table contains this SEGMENT Of SQL sentence ( It is possible to use no bound variables and execute fast at a single time ASH No grab ）, Or by LOGMNR Mining archived logs ( The query contains this in the generated table SEGMENT Name DML sentence ), In extreme cases, the archive log can also be archived DUMP, Analyze through the action of archiving logs (), Find out what OBJECT And increase / Delete / Change which action leads to a large number of Archives .
relevant MOS Documents have ：

Document:832504.1 - Excessive Archives / Redo Logs Generation due to AWR / ASH - Troubleshooting
Document:167492.1 - How to Find Sessions Generating Lots of Redo
Document:300395.1 - How To Determine The Cause Of Lots Of Redo Generation Using LogMiner
Document:199298.1 - Diagnosing excessive redo generation
Document:69739.1 - How to Turn Archiving ON and OFF in Oracle RDBMS

Case study 1-20190710：

1. find REDO A time period with a large amount of production 
 First, query the hourly data of the database REDO Amount of production , According to the results of the last three days REDO The statistical value of production can be found 11-13 Point production is the largest .

2. Search generates REDO A lot of SQL Or table 
 First of all, clarify the of the database REDO When the data changes, it will generate , It can be simply considered that the data segment in the table has a write action (SQL The data changes corresponding to the addition, deletion and modification of the table need to be realized by writing the data segments in the table ); The query will not produce REDO. Therefore, focus on the investigation of those involved in addition, deletion and modification SQL Statement and the table in front of the written amount .

3. Troubleshooting process 
 By inquiring 10-13 The table with the largest amount of data change in the point database （ Judge by the amount of writing , Writing is the change of corresponding data , Change will produce REDO LOG, The query will not produce REDO LOG）;
 At the same time, by comparing the normal period (2019/7/10 11-13 spot ) And the problem period (2019/7/11 11-13 spot ) The database of AWR Performance report , By comparing the amount of data written and the corresponding table information with large amount of data written , Can be related to the problem period 10-13 Point out the table with the largest change to verify each other .
SQL Some of the queries were not found , The reason might be SQL It executes faster and does not use bound variables , In this case AWR The report is the same as obtained SQL The top ranked summary data , Single SQL You can't catch fast execution .

4. Investigation results 
 Through the above troubleshooting ideas and methods, the following information is available ：
 Use the found REDO The period with the largest production 11-13 Point for example ： Two tables ABC.AAA_DTL ABC.AAA_HIS The data write of accounts for... Of the write volume of the whole database during this period 62.5%.
 from 2019/7/10 11-13 Point and 2019/7/11 11-13 Point database AWR Performance report comparison ,2019/7/10 11-13 No write to these two tables was found at the point .
7/11 Japan  13-18 Look at the data REDO Production has decreased , At this time, there are only tables ABC.AAA_DTL The write of is large , Take up the total 25%.
5.  Suggestions for follow-up treatment 
 It is recommended to communicate two tables with the application development department ABC.AAA_DTL ABC.AAA_HIS Whether the business logic involved has changed or added accordingly .
6. User feedback , Check with the development department , It has been confirmed that there is a business exception after cutover , Post processing database REDO LOG The production returns to normal 

Case study 2：
After database migration , A business database REDO It produces a lot , Cause backup and DG All have problems ; Since the original database environment is not archived, there is no problem ; The new environment archiving mode also has DG, This leads to the normal backup and maintenance of the database DG Synchronization problems . After troubleshooting, you can intuitively find the corresponding table and related SQL sentence , Corresponding SQL Hourly execution 30 Remaining times .
Subsequently, the business development department carries out high-speed evaluation of the program , Reduce the data synchronization frequency , relevant SQL The execution frequency is one third of the original , Produced REDO The amount is still large , But within the acceptable range .