实战演练丨SCN太大引发ORA-600[2252]

作者 | 张维照，云和恩墨技术专家，Oracle ACEA，2006年起从事数据库管理工作，2009年转Oracle，从事过多套TB级省级工商、医疗、交通、人社、电信运营等数据库维护优化工作，擅长Oracle数据库性能问题的分析与解决、故障分析、升级迁移。个人博客：www.anbob.com

案例背景

案例详情

---------------------------SCN----------15756464714
sys@ANBOB>select current_scn,dbms_flashback.get_system_change_number scn from v$database;
CURRENT_SCN                  SCN-------------------- --------------------15756464716          15756464716
-- 确认时间,时区select CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;
-- 现场的人整理了本地(DBa)、远程(DBb)库的系统时间和SCN.

DBa          DBb
--------      ----------
OS date: 20171204 15:50        20171204 15:48

sysdate: 20171204 ..        20171204 ..

scn: 15756464722        17117005290806

DB ver: 11.2.0.1            11.2.0.3
OS Plat: Windows            Aix

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        2017/12/04 17:15:26 -959.18726

sys@ANBOB_RMT>@pd _max_reasonable_scn_rate
Show all parameters and session values from x$ksppi/x$ksppcv...
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,'Y','N') 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        2017/12/04 17:35:26 5087.41908