Shulou(Shulou.com)06/01 Report--

Data migration is the daily work of DBA. I believe many people already know the corresponding methods and commands like the back of their hand. A successful data migration process not only refers to restoring data from database A backup to database B, but also ensures the integrity of data and the availability of services before and after migration.

Recently, after making the data migration from stand-alone to cluster for customers, it is found that the online redo logs of the cluster are switched frequently, resulting in a large number of archived logs, causing a lot of pressure on the server. This paper is the process of analyzing and dealing with the above problems.

Find a problem

1. Frequent log archiving

After the migration is complete, you need to take a deep look at the cluster for a period of time. Through the v$archived_log view, by analyzing the archiving of the database history, we can find the business activities of the whole library.

Looking at the figure above, it is not difficult to find that there is an obvious change point before and after the migration (June 15). The daily log archiving frequency has changed from more than 100 times to more than 400 times. In this case, either the system traffic is really large, or there is a problem with the configuration of the database users who move in.

two。 Business confirmation

After communicating with the operation and maintenance personnel who newly moved into the system, it is confirmed that although there are a large number of users of the system, they all take too many actions to query, so they should not bring such a large number of logs. Because there are other systems in the cluster, it cannot be judged directly that it is the problem of the new system. Assuming that what OPS says is true, then the key point of the problem is to find the operation statements that generate a large number of logs, and then find the corresponding application, and then confirm whether the archiving situation is normal.

Analysis of problems

1. find by hard and thorough search

Frequent log archiving indicates that online redo logs switch frequently, which is generally due to the generation of a large number of redo. Here the generation of redo is checked through awr.

Details of log archiving within a day

Here is the awr report for cluster 2 nodes between 10:00 and 11:00 on June 18.

Node 1:

Looking at the figure above, we can see that the redo generation rate of node 1 is about 3.38MB/S within 1 hour, so there is about 11.88GB in one hour.

Node 2:

Looking at the figure above, we can see that the redo generation rate of node 2 is about 0.26MB/S within 1 hour, so there is about 0.9GB in one hour.

By querying the v$archived_log view, it is calculated by classification that the size of the archive log generated between 10:00 and 11:00 is about 12.3GB, which is very close to the value 12.78GB calculated from the awr report, indicating that the above two awr reports are highly referable.

two。 track down sb . by following clues

Now that it is confirmed that frequent archiving is caused by a large number of redo, you need to look at the cause of the block change (sql) within the problem time interval, which can be found in the "Segments by DB Blocks Changes" section of the awr report:

Node 1:

Node 2:

From the above two screenshots, it can be found that the data blocks of three tables (US***42, US***39, US***06) under the user YK***FT name are frequently operated, and this user is the new database user who moved into the system.

To learn more about what has been done with the three tables, you can search the table names in the awr report to find the relevant sql statements.

As you can see from the figure above, 60 update operations have been performed on each of the three tables within 1 hour. The specific sql statements are as follows:

Notice a number 60 here, which looks like a scheduled task, and the first thing that comes to mind is job. After querying, it is found that there is indeed a job under the yk***ft user, and it happens to be executed every minute!

A closer look at the stored procedure executed by job shows that it is the above three statements:

Through the analysis of the three tables US***42, US***39, US***06 and the where statement in update, it is found that the three update statements are very problematic. The amount of data that conforms to where is large, and it only increases but does not decrease, so it must be adjusted.

Solve the problem

1. Business situation reconfirmation

According to the previous clues, confirm the business function of job (24) with the operation and maintenance staff, and the feedback is that there was a requirement to write the value of field A to field B on a regular basis, which is no longer needed and can be deleted.

two。 Adjust and observe

Disable job

Although the business confirmation can be deleted, to be on the safe side, job (24) is disabled here, which is done by calling dbms_job.broken.

Observe redo

The adjusted awr report for cluster 2 nodes between 10:00 and 11:00 on June 20 is selected here.

Node 1:

Node 2:

According to the awr report of node 1 and node 2 at the same time, the redo generation rate has been greatly reduced. By observing the generation of archived logs, it is found that the frequency of archiving has also decreased.

Summary and improvement

After reviewing the discovery, analysis and solution process of the whole problem, we can find that there are no technical difficulties, and the cause of the problem is mainly in business communication. Before migration, it is best to confirm with the application administrator the characteristics of the business, including the pressure of the existing business, the performance bottlenecks that have been found, and all kinds of database objects (indexes, views, stored procedures, functions, triggers, etc.) that are no longer needed, and take measures in advance to ensure the successful completion of data migration.

Other high-quality topics Docker operation practice (3): Docker operation details Docker operation practice (2): Docker installation and architecture introduction Docker operation practice (1): what is the nature of the container? Where does the container come from? How to use sqlplus for batch automatic release of Oracle database with complex business, huge data and wide application? Understand the solution to distributed transactions!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.