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

Today, I will talk to you about what SCN, ORA-19706 errors and _ external_scn_rejection_threshold_hours parameters are, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

If the following SCN-related information appears in the database alert log:

An ORA-19706: invalid SCN error occurred in the application.

Appears in the alert log similar to:

Wed May 30 15:09:57 2012

Advanced SCN by 68093 minutes worth to 0 × 0ba9.4111a520, by distributed transaction remote logon, remote DB:xxxx.

Client info: DB logon user xxxx, machine xxxx, program oracle@xxxx (J001), and OS user oracle

Such a warning.

Appears in the alert log similar to:

Wed May 30 12:02:00 2012

Rejected the attempt to advance SCN over limit by 166hours worth to 0 × 0ba9.3caec689, by distributed transaction remote logon, remote DB: xxxx.

Client info: DB logon user xxxx, machine xxxx, program oracle@xxxx (J000), and OS user oracle

Such an error message.

Appears in the alert log similar to:

Sat Mar 17 05:57:45 2012

ALTER DATABASE OPEN

* * *

Warning: The SCN headroom for this database is only 38 days!

* * *

This kind of information.

Other warnings that appear in alert are also mentioned in the MOS document "ORA-19706 and Related Alert Log Messages [ID 1393360.1]":

Warning-High Database SCN: Current SCN value is 0 × 0b7b.0008e40b, threshold SCN value is 0 × 0b75.055dc000, If you have not previously reported this warning on this database, please notify Oracle Support so that additional diagnosis can be performed.

WARNING: This patch can not take full effect until this RAC database

Has been completely shutdown and restarted again.Oracle recommends that it is done at the earliest convenience.

The reasons are:

If the above phenomena are only warnings or application-level errors, and the scope of impact is limited, then unfortunately, if the RECO process encounters SCN problems when resuming distributed transactions, it may cause the database to crash, for example:

View plaincopy

To clipboardprint?

Wed May 30 14:44:02 2012

Errors in file / oracle/admin/miboss/bdump/xxxx_reco_225864.trc:

ORA-19706: invalid SCN

Wed May 30 14:44:02 2012

Errors in file / oracle/admin/miboss/bdump/xxxx_reco_225864.trc:

ORA-00600: internal error code, arguments: [18348], [0x000000000], [485331304561], []

.

RECO: terminating instance due to error 476

Intance terminated by RECO, pid s = 225864

So how did the CPU or PSU patch released in January 2012 change the database in terms of SCN processing? Does this change do any harm to the database? Is the above prompt even caused by the BUG of this patch?

To answer these questions, you have to start with SCN. SCN can be said to be the foundation of Oracle, but it is also a very important thing. It is an one-way growth "clock", which is widely used in database recovery, transaction ACID, consistency reading and distributed transactions. So in addition to these, SCN has the following points of knowledge:

Internal storage of SCN: within Oracle, SCN is divided into two parts of storage, called scn wrap and scn base respectively. In fact, the length of SCN is 48 bits, which means it is actually a 48-bit integer. It's just that in the early years, it was usually only 32-bit or even 16-bit data, so it was artificially divided into low 32-bit (scn base) and high 16-bit (scn wrap). Why not design it as 64-bit, which may feel that 48-bit is long enough and to save two bytes of space:). So SCN, a 48-bit integer, the maximum is 2 ^ 48 (2 to the 48th power).

281 trillion, 281474976710656), which is a big number.

Maximum Reasonable SCN: at the current point in time, the maximum allowed SCN value of SCN is (or the maximum possible). Also known as Reasonable SCN Limit, or RSL for short. This value is a limit to prevent the SCN of the database from increasing indefinitely, even reaching the maximum value of SCN. This value is approximately calculated by a formula: (current time-January 1, 1988) * the maximum possible growth rate of 24*3600*SCN per second. The result of the current time minus January 1, 1988 is the number of days, with 24 representing 24 hours a day and 3600 representing 3600 seconds an hour. However, the "current time-January 1988" part of this formula is not a direct subtraction of two times, but is calculated on the basis of 31 days a month (perhaps to simplify the calculation, so it may have to be calculated frequently within Oracle. This calculation method can be seen in the scnhealthcheck.sql file obtained after installing patch 13498243, "Installing."

Executing and Interpreting output from the "scnhealthcheck.sql" script. [ID 1393363.1] "this MOS document explains the use of the script and the interpretation of the results, but it is actually clearer to look directly at the script code. So what is the maximum possible growth rate of SCN per second? this has something to do with the Oracle version, which is 16384 (16K) before 11.2.0.2 and 32768 (32K) after 11.2.0.2. In version 11.2.0.2, there is an implicit parameter, _ max_reasonable_scn_rate, whose default value is 32768 (it is not recommended to adjust this value). If you press the maximum value of 16K, SCN will grow to the maximum, which will take more than 500 years.

SCN Headroom: this refers to the difference between Maximum Reasonable SCN and the current database SCN. In alert, it is usually in terms of "days", which is just to make it easy for people to read. Number of days = (Maximum Reasonable SCN-Current SCN) / 16384 + 3600 + 24. This value means that at each growth rate of SCN, how many days will it take to reach Maximum Reasonable SCN. But in fact, even if so, it will not reach Maximum Reasonable.

SCN, because the Maximum Reasonable SCN also increases by that time (the longer it takes), to reach Maximum Reasonable SCN, it must be twice the maximum possible rate of SCN.

Abnormal growth of SCN: generally speaking, the maximum allowable 16K/32K growth rate per second is sufficient, but excessive SCN growth due to BUG or artificial adjustments cannot be ruled out. Especially the latter, such as the database is forced to open by special means, and the SCN is incremented by hand. At the same time, the SCN of Oracle will be spread through db link. If library A connects to library B through db link, if the SCN of library An is higher than the SCN of library B, then library B will increment SCN to the same as library A, whereas if the SCN of library An is lower than the SCN of library B, then the SCN of library A will be incremented to the same as the SCN of library B. That is, when it comes to db

Multiple libraries operated by link that synchronize SCN to the largest SCN of these libraries.

So, if the growth of SCN is caused by the operation of the database itself rather than through db link synchronization, how to judge its growth rate, which can be obtained by the system statistics "calls to kcmgas" and "DEBUG calls to kcmgas". Kcmgas means get and advance SCN, which means getting and incrementing the SCN.

When two libraries conduct distributed transactions through db link, it is assumed that the SCN value of library B is higher than that of library A, so it is necessary to increase the SCN of library B to library A, but if the SCN of library B is too high, so that after synchronization to library A, library A faces the risk of too little Headroom, then library A will refuse to synchronize SCN, and an ORA-19706: Invalid SCN error will be reported at this time. Distributed transactions, or operations through db link, fail, even through query operations through db link. There is obviously a threshold that if incrementing SCN makes Headroom too small to what value, will you refuse to increment (synchronize) SCN? For now, it looks like this: if you patch CPU or PSU in January 2012, 11.2.0.2 or later, it will be 24 hours per day, while in other versions it will be 31 days or 744 hours. After patching, it can be adjusted by the implicit parameter _ external_scn_rejection_threshold_hours. In the case of no patch, this parameter is set to 0, and the actual minimum is 1 hour. Due to Oracle

9.2.0.8 without the latest patch set, it will not have this parameter, and the default value will be 1 hour. Note that this is a static parameter. So an important change in the January 2012 CPU or PSU patch is the addition of the _ external_scn_rejection_threshold_hours parameter, while increasing the Headroom threshold for databases below 11.2.0.2. The impact of this is that ORA-19706 is more likely to make errors. The solution is to set the implicit parameter _ external_scn_rejection_threshold_hours to a small value, and the recommended value is 24, that is, 1 day. From the literal meaning of the parameter name _ external_scn_rejection_threshold_hours combined with its function, it can be said that this parameter is the threshold of "reject external SCN". It has no effect on the SCN increment generated by the database itself.

Although in version 11.2.0.2 and later, the default maximum possible SCN growth rate per second is 32K, which makes the Maximum Reasonable SCN larger, that is, its SCN can grow to a higher value. That is, it may prevent db link connections between the 11.2.0.2 library and the earlier version of the database. Or 11.2.0.2 libraries cannot db link with libraries at 16K rates (such as adjusting the value of the _ max_reasonable_scn_rate parameter).

Now is the time to answer the following questions:

How exactly did the CPU or PSU patch released after January 2012 change the database in terms of SCN processing? The answer is: the _ external_scn_rejection_threshold_hours parameter has been added. The default value for this parameter is 24 for 11.2.0.2 and above, and 744 for other versions. This increases the threshold of Headroom for databases below 11.2.0.2.

Does this change do any harm to the database? The answer is: in a large enterprise environment with many systems, db

Link is widely used, and even some databases that are not easy to control are connected to key systems through db link. In such an environment, excessive SCN spreads to critical systems, and if the system has this patch, its Headroom threshold becomes larger, then ORA-19706 errors are more likely to occur. Systems that rely heavily on db link may lead to business system problems, or even library outages in serious cases. However, this problem can be solved by setting the implicit parameter _ external_scn_rejection_threshold_hours. So, if you installed the CPU or PSU patch of January 2012, please set this parameter to the recommended value of 24 as soon as possible, and in extreme cases you can set it to 1.

Are those prompts or warnings in alert caused by BUG? The answer is: these prompts or warnings are not caused by BUG. It just reminds you that your SCN is growing too high, or that your Headroom is small (which may be a reminder when the Headroom is less than 62 days). Actually, according to the MOS document "System Change Number (SCN), Headroom, Security and Patch Information [ID"

1376995.1], this patch fixes some BUG related to SCN. If you have to say BUG, you can reluctantly think that the default value of the parameter _ external_scn_rejection_threshold_hours added after the patch is installed is too large. Bug 13554409-Fix for bug 13554409 [ID 13554409.8] refers to this problem. However, this issue was fixed in the CPU or PSU patch in April 2012.

In the end, let's interpret some information in the alert log:

Information:

Wed May 30 15:09:53 2012

Completed crash recovery at

Thread 1: logseq 3059, block 19516, scn 12754630269552

2120 data blocks read, 2120

Data blocks written, 19513 redo blocks read

... ..

Wed May 30 15:09:57 2012

Advanced SCN by 68093 minutes worth to 0 × 0ba9.4111a520, by distributed transaction remote logon, remote DB:xxxx.

Client info: DB logon user xxxx, machine xxxx, program oracle@xxxx (J001), and OS user oracle

Here, SCN increments (jumps) 68098 minutes, and the incremented SCN is 0 × 0ba9.4111a520. Note that the minute calculation here is based on the maximum possible SCN growth rate of 16K per second. Let's do the math:

0 × 0ba94111a520 is converted into decimal 12821569053984.

In the alert log, this information is when the database is first opened, so the scn when crash recovery is completed can be used as an approximate current SCN, with a value of 12754630269552:

(12821569053984-12754630269552) / 16384Universe 68093.65278320313

Here the value of 16384 is the maximum possible growth rate of SCN per second, and you can see that the calculation results are very close.

Let's calculate the headroom of this SCN again:

View plaincopy

To clipboardprint?

You can see that the result is 24 days, and because the value of the _ external_scn_rejection_threshold_hours parameter is 24, that is, 1 day, although there is such a big jump, SCN still grows successfully.

SQL > select

2 ((

3 ((to_number (to_char (cur_date,'YYYY'))-1988) * 12 "31" 24 "60") +

4 ((to_number (to_char (cur_date,'MM'))-1) * 31 / 24 / 60 / 60) +

5 ((to_number (to_char (cur_date,'DD'))-1)) * 24060) +

6 (to_number (to_char (cur_date,'HH24')) * 60mm 60) +

7 (to_number (to_char (cur_date,'MI')) * 60) +

8 (to_number (to_char (cur_date,'SS')

9) * (16024))-12821569053984)

10 / (16,1024,60,24)

11) headroom

12 from (select to_date ('2012-05-30 15 hh34:mi:ss' 09 hh34:mi:ss' 57) cur_date from dual)

HEADROOM

-

24.1496113

Information:

Wed May 30 12:02:00 2012

Rejected the attempt to advance SCN over limit by 166hours worth to 0 × 0ba9.3caec689, by distributed transaction remote logon, remote DB: xxxx.

Client info: DB logon user xxxx, machine xxxx, program oracle@xxxx (J000), and OS user oracle

In this message, the increase in SCN caused by db link is rejected. Calculate the headroom of this SCN:

The conversion from 0 × 0ba93caec689 to 10 is 12821495465609.

The current time is 2012-05-30 12:02:00

View plaincopy

To clipboardprint?

Since the value of the _ external_scn_rejection_threshold_hours parameter at this time is 744, or 31 days, the calculated headroom is within this threshold, so it refuses to increase the SCN.

(31-24.0710752) * 240166.2941952, exactly 166hours.

SQL > select

2 ((

3 ((to_number (to_char (cur_date,'YYYY'))-1988) * 12 "31" 24 "60") +

4 ((to_number (to_char (cur_date,'MM'))-1) * 31 / 24 / 60 / 60) +

5 ((to_number (to_char (cur_date,'DD'))-1)) * 24060) +

6 (to_number (to_char (cur_date,'HH24')) * 60mm 60) +

7 (to_number (to_char (cur_date,'MI')) * 60) +

8 (to_number (to_char (cur_date,'SS')

9) * (16024))-12821495465609)

10 / (16,1024,60,24)

11) headroom

12 from (select to_date ('2012-05-30 12 hh34:mi:ss' 02ju 02jue 00mm hh34:mi:ss') cur_date from dual)

HEADROOM

-

24.0710752

-- update on 2012 Universe 2 Muhami

In fact, there will be the following changes after the CPU or PSU patch in January 2012:

The implication of _ minimum_giga_scn is gone, but it's a pity that this sharp weapon is added manually to SCN.

11.2.0.2 and later, the maximum speed of 32K SCN was adjusted back to 16K rate. You can use the following SQL to get the result: view plaincopy

To clipboardprint?

The above SQL result only makes sense in version 11.2.0.2 and above, and the result is Y, indicating that a rate of 16K is used, otherwise a rate of 32K is used.

SQL> select decode (bitand (DI2FLAG,65536), 65536) using16

2 from x$kccdi2

U

-

Y

Some of the parameters covered in this article, and some of SCN's algorithms, may vary greatly from version to version or patch.

After reading the above, do you have any further understanding of SCN, ORA-19706 errors, and what the _ external_scn_rejection_threshold_hours parameter is? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.