Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to understand that the Oracle archive log is much smaller than the online redo log". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1: check the parameter ARCHIVE_LAG_TARGET

The ARCHIVE_LAG_TARGET parameter can set a time to specify that the database is forced to be archived by Log Switch through a time limit. If this parameter is set too small, it may cause the online redo log to switch before it is full, which may cause the archive log to be much smaller than the online redo log (redo log).

SQL > show parameter archive_lag_target; NAME TYPE VALUE-archive_lag_target integer 0 SQL >

If the parameter archive_lag_target is 0, you can exclude this factor.

2: check whether there is the possibility of artificially switching redo log.

Some commands can cause the redo log to switch, as shown below

SQL > alter system archivelog current; # archiving command will also cause log switching SQL > alter system switch logfile; # directly switch log group RMAN > backup archivelog all; RMAN > backup database plus archivelog; SELECT TO_CHAR (FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS'), BLOCKS * BLOCK_SIZE / 1024 / 1024, COMPRESSED FROM V$ARCHIVED_LOG

The screenshot of the following case is shown below. From the screenshot, the size of the archive log hovers around 31m. In addition, you can see that the archive log compression option is not enabled (ORACLE does not support archive log compression, as explained later). As can be seen from the law of archive log size, this is not caused by a redo log switch command.

3: some Bug causes, as shown in the following metalink documents:

BUG 9272059-REDOLOG SWITCH AT 1 8 OF SIZE DUE TO CMT CPU'S BUG 10354739-REDOLOGSIZE NOT COMPLETLY USED BUG 12317474-FREQUENT REDOLOG SWITCHES GENERATING SMALL SIZED ARCHIVELOGS BUG 5450861-ARCHIVELOGS ARE GENERATED WITH A SMALLER SIZE THAN THE REDOLOG FILES BUG 7016254-DECREASE CONTROL FILE ENQUEUE WAIT AT LOG SWITCH

4: it is related to the number of CPU, CPU_COUNT, log_buffer and redo log size.

The size of the archive log is the amount of real online log files used, that is, the size of the content written in it before the online log files are switched. In order to achieve better parallelism, reduce conflicts, improve concurrency, and reduce redo allocation latch waiting, ORACLE divides redo buffer into several small buffer, each small buffer is called strand. Each CPU is divided into one strand per 16, and each redo buffer is allocated independently from the redo buffer and redo log. When this redo buffer is used up, the redo log is written and the same amount of space is allocated from the redo log. If the free space cannot be allocated, the log is switched, regardless of whether the other strand is finished or not.

As shown above, if CPU_COUNT is 112, then 112 CPU_COUNT is 16-7, then both redo buffer and redo log can be divided into seven parts.

SQL > select 112.0 from dual; 112.0 redo log size 16-7 SQL > select 341655552 1024 SQL 7 from dual;-log buffer 341655552 SQL 1024 SQL > select 200 from dual; 7 from dual;-redo log size 200 SQL >

When the size of the log buffer is 325.828125m (341655552), divided into seven strands (strand), each strand is still 325.828125M/7=46.546875M. When the size of the redo log is 200m, the space in the redo log is evenly distributed according to the number of strand, that is, each 200M/7=28.5714286M.

In this way, when the content in each strand is written to around 28m, the log will be switched instead of 46m. It is equivalent to that part of the space in log buffer is wasted. So the archive log you see is basically about 30m in size (one of them (strand) 28.6 plus other shares is also partially written, so the size of the archive log is a fluctuating range)

For the analysis of other special scenarios, you can refer to the article "the size of archived logs is much smaller than that of online logs [1]." Of course, the analysis process of this article also ignores the fact that other stocks also have some data. This requires special attention.

If you are not very clear about this mechanism, the blog linked above is no longer accessible. Here are some of my excerpts to facilitate your in-depth understanding:

For example, if the number of CPU is 64, there will be 64 strand.

Example 1): when both the size of log buffer and the size of redo log file are 256m, each strand is 256M/4=64M. When each redo log file is enabled, four 64m corresponding to the log buffer are pre-allocated to the size in the redo log file, as shown in the figure:

Because both the size of log buffer and the size of redo log file are 256m, redo log file has no unallocated space left.

The redo generated by each process will be assigned to one of the strand on log buffer, and a single process can only correspond to one strand, so when there is a lot of redo generated by only certain processes (for example, in extreme cases, only one process) in the database, one of the strand will quickly fill up, such as strand 1 in the figure:

When it is full, LGWR will write the contents of strand 1 in log buffer to redo log file and try to allocate a new 64m space from redo log file. If it is found that it is gone, all the contents of strand will be written to the log and log switching will be made.

This may cause redo log file to write only one strand, and the rest is almost empty, and the resulting archive log will only be close to 64m instead of 256m. When the CPU_COUNT is large, the difference is even larger.

Example 2): when the size of log buffer is 256m and the size of redo log file is 1G, each strand is still 256M/4=64M. When each redo log file is enabled, four 64m corresponding to the log buffer are pre-allocated to the size in the redo log file, as shown in the figure:

At this point, there is still unallocated space left in the redo log file for 1G-256M=768M.

If strand 1 is full, LGWR writes the contents of strand 1 in log buffer to redo log file, tries to allocate a new 64m space from redo log file, and then keeps writing down. Picture

Until there is no more allocable space in the redo log file, all the contents of the strand are written to the log and the log is switched.

Example 3): when the size of log buffer is 256m and the size of redo log file is 100m, each strand is still 256M/4=64M. But the space in the redo log file is evenly distributed according to the number of strand, that is, each 100M/4=25M.

In this way, when the content in each strand is written to 25m, the log will be switched instead of 64m. It is equivalent to that part of the space in log buffer is wasted.

5: check whether archive log compression is enabled

The purpose of this feature is to compress the archive before it is transferred remotely or stored to disk, in order to reduce the time and disk space consumed by the archive log transfer. You can check using the following script.

SELECT NAME, ARCHIVELOG_COMPRESSION FROM SELECT NAME database; SELECT TO_CHAR (FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS'), BLOCKS * BLOCK_SIZE / 1024 / 1024, COMPRESSED FROM Venture ARCHIVEDLOG; SQL > SELECT NAME, 2 ARCHIVELOG_COMPRESSION 3 FROM database; NAME ARCHIVEL-- GSPP DISABLED

At first, it is estimated that many people will be confused by this. In fact, ORACLE 10g and 11g do not support archive log compression, and there is no clear official documentation. In fact, archive log compression was originally a new feature introduced by the ORACLE 10g plan, but unfortunately this plan was abandoned and ORACLE 11g does not support it.

Archive compression was a planned new feature for 10G, but unfortunately it was withdrawn and it is still not available in 11g .This feature is expected in future releases

Finally, you can go to metalink to see Archived redolog is (significant) smaller than the redologfile. (document ID 1356604.1) this article, the official document is the official document, the most comprehensive description of the reasons why archived logs are smaller than logs.

Archived redolog is (significant) smaller than the redologfile. (document ID 1356604.1)

There are 2 possible causes for this: 1. Documented and designed behaviour due to explicit forcing an archive creation before the redolog file is full SQL > alter system switch logfile; SQL > alter system archivelog current; RMAN > backup archivelog all; RMAN > backup database plus archivelog; ARCHIVE_LAG_TARGET: limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after the specified amount of time elapses. You can see this aswell in RAC with an idle/low-load instance. > 2. Undocumented, but designed behaviour: BUG 9272059-REDOLOG SWITCH AT 1 OF SIZE DUE TO CMT CPU'S BUG 8 OF SIZE DUE TO CMT CPU'S BUG 10354739-REDOLOGSIZE NOT COMPLETLY USED BUG 12317474-FREQUENT REDOLOG SWITCHES GENERATING SMALL SIZED ARCHIVELOGS BUG 5450861-ARCHIVELOGS ARE GENERATED WITH A SMALLER SIZE THAN THE REDOLOG FILES BUG 7016254-DECREASE CONTROL FILE ENQUEUE WAIT AT LOG SWITCH Explanation: As per Bug: 5450861 (closed as'Not a Bug'): * The archive logs do not have to be even in size. This was decided a very long time ago, when blank padding the archive logs was stopped, for a very good reason-in order to save disk space. * The log switch does not occur when a redo log file is 100% full. There is an internal algorithm that determines the log switch moment. This also has a very good reason-doing the log switch at the last moment could incur performance problems (for various reasons, out of the scope of this note). As a result, after the log switch occurs, the archivers are copying only the actual information from the redo log files. Since the redo logs are not 100% full after the log switch and the archive logs are not blank padded after the copy operation has finished, this results in uneven, smaller files than the original redo log files. There are a number of factors which combine to determine the log switch frequency. These are the most relevant factors in this case: a) RDBMS parameter LOG_BUFFER_SIZE If this is not explicitly set by the DBA then we use a default; at instance startup the RDBMS calculates the number of shared redo strands as ncpus/16, and the size of each strand is 128Kb * ncpus (where ncpus is the number of CPUs in the system). The log buffer size is the number of stands multiplied by the strand size. The calculated or specified size is rounded up to a multiple of the granule size of a memory segment in the SGA. For 11.2 if SGA size > = 128GB then granule size is 512MB 64GB

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.