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

Author | Zhang Weizhao, Yunhe Enmo technical expert, Oracle ACEA, engaged in database management since 2006, transferred to Oracle in 2009, engaged in many sets of TB provincial industrial and commercial, medical, transportation, social society, telecom operation and other database maintenance and optimization work, good at Oracle database performance problem analysis and solution, fault analysis, upgrade and migration. Personal blog: www.anbob.com

Case background

Some time ago, a friend encountered a problem, let me assist in the analysis, the phenomenon is a prefecture and city database and provincial database through DBLINK connection prompt ORA-600 2252, but other cities and provincial DBLINK is normal.

Case detail

Specific analysis, the errors are as follows:

-- SCN-15756464714

Sys@ANBOB > select current_scn,dbms_flashback.get_system_change_number scn from v$database

CURRENT_SCN SCN--15756464716 15756464716

-- confirm time, time zone select CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL

-- people at the scene sorted out the system time and SCN.DBa DBb of local (DBa) and remote (DBb) libraries

--

OS date: 20171204 15:50 20171204 15:48

Sysdate: 20171204.. 20171204.. scn: 15756464722 17117005290806DB ver: 11.2.0.1 11.2.0.3

OS Plat: Windows Aix

Note:

The SCN of the local library and the remote library is not of the same order of magnitude, with a difference of 1000 times. In fact, we can calculate the maximum allowable value of SCN according to the current time. The SCN of the remote library is much larger than the maximum allowable SCN of the local library (referred to as RSL, which will be supplemented below). For information about SCN, you can check MOS note.

-query the SCN Headroom of the remote database

SQL > select

2 version

3 to_char (SYSDATE, 'YYYY/MM/DD HH24:MI:SS') DATE_TIME

4 ((

5 (to_number (to_char (sysdate, 'YYYY'))-1988) * 12 * 31 * 24 * 60 * 60) +

6 ((to_number (to_char (sysdate, 'MM'))-1) * 31 * 24 * 60 * 60) +

7 ((to_number (to_char (sysdate, 'DD'))-1)) * 24 * 60 * 60) +

8 (to_number (to_char (sysdate, 'HH24')) * 60 * 60) +

9 (to_number (to_char (sysdate, 'MI')) * 60) +

10 (to_number (to_char (sysdate, 'SS')

11) * (16 * 1024))-dbms_flashback.get_system_change_number)

12 / (16 * 1024 * 60 * 60 * 24)

13) indicator

14 from v$instance

15

VERSION DATE_TIME INDICATOR

11.2.0.3.0 17:15:26 on 2017-12-04-959.18726

Note:

Oops!!! The above script is also more common from the official scnhealthcheck.sql. INDICATOR is the number of days from the SCN Headroom (ceiling), which is negative indicating that it has exceeded the ceiling. Of course, the SCN limit is decided not to exceed the ceiling and is not allowed. Could it be that there is something wrong with the remote database? Why can the libraries of other cities and cities be queried with this remote library? What is the reason for negative numbers?

The script above also needs to confirm another point for databases after 11.2.0.2, that is, the limit of 16K per second has been changed from 11G R2 (11.2.0.2) to 32K (I checked 11.2.0.3 11.2.0.4 12.2.0.1 is 32K by default), and there is a hidden parameter "_ max_reasonable_scn_rate" control. At the same time, you need to use the following SQL statement to actually confirm whether it is 16K or 32K (only meaningful for versions later than 11.2.0.2), because I found that some 11.2 databases still use 16K (maybe it is due to the direct upgrade of the lower version, maybe a PSU temporarily returns to the growth rate of 16K):

Sys@ANBOB_RMT > @ pd _ max_reasonable_scn_rate

Show all parameters and session values from Xeroksppic Xeroksppcv...

INDX I_HEX NAME VALUE DESCRIPTION

978 3D2 _ max_reasonable_scn_rate 32768 Max reasonable SCN rate

Sys@ANBOB_RMT > select decode (bitand (DI2FLAG,65536), 65536) using16 from x$kccdi2

U

-

N

Sys@ANBOB_RMT > select

Version

To_char (SYSDATE, 'YYYY/MM/DD HH24:MI:SS') DATE_TIME

(to_number (to_char (sysdate, 'YYYY'))-1988) * 12 * 31 * 24 * 60 * 60) +

((to_number (to_char (sysdate, 'MM'))-1) * 31 * 24 * 60 * 60) +

((to_number (to_char (sysdate, 'DD'))-1)) * 24 * 60 * 60) +

(to_number (to_char (sysdate, 'HH24')) * 60 * 60) +

(to_number (to_char (sysdate, 'MI')) * 60) +

(to_number (to_char (sysdate, 'SS') * (32 * 1024))-dbms_flashback.get_system_change_number) / (32 * 1024 * 60 * 60 * 24)) indicatorfrom v$instance

VERSION DATE_TIME INDICATOR

11.2.0.3.0 17:35:26 on 2017-12-04 5087.41908 now summarize this question:

Is the local library a frequency limit of 11.2.0.1 scn or 16K?

The remote library is 11.2.0.3, and it is confirmed from x$kccdi2 that the currently used frequency limit is 32K

The current SCN of the remote library has exceeded the upper limit of 16K allowed for the local library, so using DBLINK to synchronize the SCN will result in ora-600 [2252]

Remote library query ceiling needs to be modified 16 for 32 in the script

Other cities can access the remote database because their database is later than 11.2.0.2 and also uses the 32K limit.

The growth rate of SCN has accelerated, and if 32k uses the total scn limit of 6bytes, it will not be available for 500 years, so big scn has been added to 8bytes from 12.2. And for SCN propagation jump in version 12.2, two views have been added to locate the source, and you can use DBA_EXTERNAL_SCN_ACTIVITY DBA_DB_LINK_SOURCES and DBA_DB_LINK connection. Parameters related to controlling SCN are provided in PSU after January 2012 or in some versions of 11G:

SCN rejected due to request for high SCN increment (controlled by _ external_scn_rejection_threshold_hours) limit the maximum usage and retention time

SCN rejected due to request in certain DELTA of changes (controlled by _ external_scn_rejection_delta_threshold_minutes) limits the maximum number of changes at a time. If the request exceeds, it will fail. Prompt ORA-19706.

Write ALERT LOG when the growth of SCN accepted but with a warning (controlled by _ external_scn_logging_threshold_seconds) exceeds a certain threshold.

Knowledge points related to SCN:

SCN is an one-way growing "clock" of Oracle databases, widely used in database consistency recovery and distributed transactions (such as dblink)

SCN consists of two parts: wrap.base, which occupies 8bytes in the database, reserves 2bytes before 12c R2, is a number of Integer type of 6bytes (48bit), [16bit SCN Wrap]. [32bit SCN Base], introduces big scn from 12c R2, enables the original reserved 2bytes, and increases the total length from 2 ^ 48 to 2 ^ 64.

In order to limit the unlimited growth of SCN, a soft limit of the maximum allowable SCN (Maximum Reasonable SCN) at the current time point, Reasonable SCN Limit for short, is designed at the code level of the program. This value is the maximum growth rate of RSL= (from January 1, 1988 to the current time) * 24 * 3600 * per second calculated by an I formula. It should be noted that it is not a simple subtraction between the current time and the 1988-1-1 time point. Perhaps because of the simplicity of the calculation, each month is calculated on the basis of 31 days, as can be seen from the script provided in MOS above. The maximum allowable growth rate per second before 11.2.0.2 is 16384 (16K) 11.2.0.2 and later is 32768 (32K), with hidden parameter _ max_reasonable_scn_rate control

SCN Headroom is the most important check item, and the value is the difference between the maximum allowed SCN at the current point in time and the current database SCN. To facilitate reading in "days", the number of SCN Headroom days = (Maximum Reasonable SCN-Current SCN) / (maximum growth rate allowed per second) / 3600pm 24

The abnormal growth of SCN is usually that the historical changes of DBLINK, artificially pushed SCN and oracle bug,SCN can be obtained from V$ARCHIVED_LOG, and the SCN of the last 5 days can also be obtained from smon_scn_time. The growth of the database itself can be obtained from the change of 'calls to kcmgas'' from dba_hist_sysstat, the propagation of SCN through DBLINK can be controlled by the parameters mentioned above, and the new view provided in 12c

Database 11.2.0.2 and later allow 32K growth rate by default, so a larger SCN will be generated as in this case, which means that 11.2.0.2 may no longer be able to pass DBLINK with lower versions of databases or databases using 16K growth rates.

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.