ROW CACHE LOCK导致数据库CPU高案例分析

近期整理案例,发现前不久同事反馈一个数据库服务器CPU居高不下,无法找到根因过来询问,详情如下:

EVENT                                      COUNT(*)
---------------------------------------- ----------
row cache lock                                 1007
                                                213
buffer busy waits                               165
log file switch (checkpoint incomplete)         137
free buffer waits                               114
db file async I/O submit                         75
log file sync                                    73
db file sequential read                          62
read by other session                            36
log file parallel write                          15
resmgr:cpu quantum                               15
enq: TX - row lock contention                    13
enq: HW - contention                              9
direct path read                                  8
enq: SQ - contention                              4
latch: undo global data                           3
write complete waits                              2
log file sequential read                          1
null event                                        1
db file scattered read                            1
Log archive I/O                                   1
LNS wait on SENDREQ                               1
enq: US - contention                              1

从ASH中看到,CPU高的时间段内,等待事件最多的为ROW CACHE LOCK,

TIME                              USER_ID SQL_ID        EVENT                                            P1
------------------------------ ---------- ------------- ---------------------------------------- ----------
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13

确认该时间段内的具体SQL,发现均存在SEQUENCE调用

SQL_ID          COUNT(*)
------------- ----------
7265mcg5kwyc3        357
11xs00r4qzb7n        341
c25ar03n0p5sz        277
f9ym730zzf1s2         12
9dnygr7asqmy9         12
dksd9txy8vuk6          6
9qzwmgp034kx2          2

SQL> SELECT DISTINCT sql_id,
                sql_text
FROM v$sql
WHERE sql_id IN ('dksd9txy8vuk6',
                 '11xs00r4qzb7n',
                 '9qzwmgp034kx2',
                 '9dnygr7asqmy9',
                 '7265mcg5kwyc3',
                 'c25ar03n0p5sz',
                 'f9ym730zzf1s2');

SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------------------------------------
9qzwmgp034kx2 INSERT INTO opfqresultdata VALUES(OPFQRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 )
f9ym730zzf1s2 select SUBSFLT_HISTORY_SEQUENCE_ID.nextval from dual
7265mcg5kwyc3 INSERT INTO pnrresultdata VALUES(PNRRESULTDATA_SEQUENCE_ID.NEXTVAL,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 )
c25ar03n0p5sz INSERT INTO etresultdata VALUES(ETRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 )
9dnygr7asqmy9 select SUBSFLTREQUEST_SEQUENCE_ID.nextval from dual
11xs00r4qzb7n INSERT INTO unknowdata VALUES(UNKNOWDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 )
dksd9txy8vuk6 INSERT INTO fltresultdata VALUES(FLTRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:1

SQL> select DISTINCT P1
FROM
  (SELECT to_char(SAMPLE_TIME,'YYYYMMDD hh24:mi:ss') time,
          USER_ID,
          SQL_ID,
          EVENT,
          P1
   FROM dba_hist_active_sess_history
   WHERE SAMPLE_TIME>to_date('20200929 01:00:00','YYYYMMDD hh24:mi:ss')
     AND SAMPLE_TIME<to_date('20200929 01:15:00','YYYYMMDD hh24:mi:ss')
     AND EVENT = 'row cache lock');


        P1
----------
        13  --正是dc_sequences

SQL> select parameter,gets,getmisses,MODIFICATIONS from v$rowcache where cache#=13;

PARAMETER                              GETS  GETMISSES MODIFICATIONS
-------------------------------- ---------- ---------- -------------
dc_sequences                      766929021       5065     766929020

发现此sequence中的cache size均为0,出现row cache lock与cpu高便不足为奇

将频繁调用的sequence cache size调整至500后,等待与cpu高的现象随之解决,不再复现。

SQL执行计划突变案例分析

前几日,同事发现一套库数据库的活动会话数激增,大量活动会话集中等待gc buffer busy acquire上,如下图所示

经过更一步的查看,发现这些等待均在sql_id:f7hnmtx8bd5q3上,利用如下sql进行查看发现:

SQL_TEXT如下:
SELECT *
FROM
  (SELECT p.*
   FROM t p,
        TD_PLAN_LEG d
   WHERE p.THR_DEP = d.THR_DEP
     AND p.THR_ARR = d.THR_ARR
     AND p.FLTNO = d._FLTNO
     AND pDATE = d.DATE
     AND (p.THR_DEP = :1
          OR p.THR_ARR = :2)
     AND nvl(d.STATUS_inner, ' ') != '??'
     AND d.LT_ID IS NULL
     AND nvl(d.STATUS_inner, ' ') != '??'
     AND p.OP_TM > :3
   ORDER BY p.OP_TM)
WHERE ROWNUM <= :4


SQL历史执行效率:
TIME      PLAN_HASH_VALUE EXECU_D        BG_D        DR_D      ET_D      CT_D IO_TIME CLUS_TIME AP_TIME CC_TIME GET_ONETIME   ROWS_PRO ROWS_ONETIME ET_MS_ONCE
------------ --------------- ------- ----------- ----------- --------- --------- ------- --------- ------- ------- ----------- ---------- ------------ ----------
2020092201        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092201        2495209816    3333  1351590946           0      2403      2409       0         0       0       0      405518        261            0        721
2020092202        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092202        2495209816    3238  1381884655           0      2449      2477       0         0       0       0      426771         82            0        756
2020092203        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092203        2495209816    3228  1380940555           0      2464      2475       0         0       0       0      427801         12            0        763
2020092204        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092204        2495209816    3249           0           0      2469      2486       0         0       0       0           0          0            0        760
2020092205        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092205        2495209816    3239  1401077673           0      2496      2510       0         0       0       0      432565          0            0        771
2020092206        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092206        2495209816    3240  1374013765           0      2449      2467       0         0       0       0      424078          0            0        756
2020092207        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092207        2495209816    3319           0           0      2584      2589       0         0       0       0           0        181            0        778
2020092208        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092208        2495209816    3656  1628720696           0      2827      2826       0         0       0       0      445493       1439            0        773
2020092209        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092209        2495209816    3238  1434887002           0      2610      2611       0         0       0       0      443140       2253            1        806
2020092210        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092210        2495209816    3240           0           0      2623      2629       0         0       0       0           0       2046            1        809
2020092211        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092211        2495209816    3240  1442329567           0      2672      2669       0         0       0       0      445163       2659            1        825
2020092212        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092212        2495209816    3240  1437033232           0      2676      2675       0         0       0       0      443529       2561            1        826
2020092213        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092213        2495209816    3222           0           0      2687      2689       0         0       0       0           0       2888            1        834
2020092214        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092214        2495209816    3240  1440743191           0      2706      2707       0         0       0       0      444674       3367            1        835
2020092215        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092215        2495209816    3243  1444795359           0      2747      2747       0         0       0       0      445512       2677            1        847
2020092216        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092216        2495209816    3243           0           0      2742      2749       0         0       0       0           0       2975            1        846
2020092217        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092217        2495209816    3256  1464276872           0      2774      2771       0         0       0       0      449716       2929            1        852
2020092218        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092218        2495209816    3240  1459846368           0      2802      2801       0         0       0       0      450570       3543            1        865
2020092219        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092219        2495209816    3240           0           0      2806      2806       0         0       0       0           0       3022            1        866
2020092220        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092220        2495209816    3221  1443354745           0      2819      2820       0         0       0       0      448108       2694            1        875
2020092221        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092221        2495209816    3239  1458055393           0      2891      2879       0         0       0       0      450156       2592            1        892
2020092222        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092222        2495209816    3240           0           0      2883      2887       0         0       0       0           0       2059            1        890
2020092223        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092223        2495209816    3240  1458681730           0      2926      2928       0         0       0       0      450210       1936            1        903
2020092300        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092300        2495209816    3238  1437599879           0      2925      2923       0         0       0       0      443978       1370            0        903
2020092301        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092301        2495209816    3251  1265725098           0      2330      2326       0         0       0       0      389334        695            0        717
2020092302        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092302        2495209816    3240  1322541308           0      2443      2447       0         0       0       0      408192        223            0        754
2020092303        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092303        2495209816    3240           0           0      2466      2471       0         0       0       0           0         41            0        761
2020092304        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092304        2495209816    3240  1309926437           0      2432      2431       0         0       0       0      404298          0            0        751
2020092305        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092305        2495209816    3250  1347086218           0      2466      2458       0         0       0       0      414488          0            0        759
2020092306        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092306        2495209816    3248  1327059467           0      2452      2452       0         0       0       0      408577          0            0        755
2020092307        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092307        2495209816    3242           0           0      2491      2488       0         0       0       0           0        194            0        768
2020092308        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092308        2495209816    3245  1405256862           0      2544      2538       0         0       0       0      433053       1625            1        784
2020092309        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092309        2495209816    3246  1421208667           0      2590      2579       0         0       0       0      437834       2518            1        798
2020092310        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092310        2495209816    3248           0           0      2619      2609       0         0       0       0           0       1961            1        806
2020092311        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092311        2495209816    3239  1426602319           0      2637      2632       0         0       0       0      440445       2673            1        814
2020092312        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092312        2495209816    3195  1403306564           0      2610      2603       0         0       0       0      439220       3029            1        817
2020092313        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092313        2495209816    3247           0           0      2698      2687       0         0       0       0           0       2976            1        831
2020092314        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092314        2495209816    3238  1418853144           0      2696      2690       0         0       0       0      438188       3496            1        833
2020092315        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092315        2495209816    3236  1443916772           0      2736      2728       0         0       0       0      446204       3600            1        846
2020092316        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092316        2495209816    3239           0           0      2731      2726       0         0       0       0           0       3630            1        843
2020092317        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092317        2495209816    3239  1435088513           0      2780      2768       0         0       0       0      443065       3446            1        858
2020092318        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092318        2495209816    3256  1422988985           0      2794      2782       0         0       0       0      437036       3043            1        858
2020092319        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092319        2495209816    3239           0           0      2816      2811       0         0       0       0           0       3108            1        869
2020092320        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092320        2495209816    3237  1414992685           0      2818      2813       0         0       0       0      437131       2993            1        870
2020092321        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092321        2495209816    3239  1450253448           0      2867      2859       0         0       0       0      447747       2686            1        885
2020092322        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092322        2495209816    3239           0           0      2866      2853       0         0       0       0           0       2661            1        885
2020092323        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092323        2495209816    3239  1440828417           0      2906      2889       0         0       0       0      444837       2007            1        897
2020092400        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092400        2495209816    3256  1405971757           0      2892      2884       0         0       0       0      431810       1569            0        888
2020092401        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092401        2495209816    3239   262264721           0      2332      2331       0         0       0       0       80971        570            0        720
2020092402        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092402        2495209816    3204  1328880815           0      2433      2428       0         0       0       0      414757        252            0        759
2020092403        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092403        2495209816    3240  1367144118           0      2459      2453       0         0       0       0      421958        101            0        759
2020092404        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092404        2495209816    3237  1318574101           0      2437      2432       0         0       0       0      407344         35            0        753
2020092405        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092405        2495209816    3220           0           0      2438      2435       0         0       0       0           0          0            0        757
2020092406        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092406        2495209816    3254  1346142138           0      2480      2469       0         0       0       0      413688          0            0        762
2020092407        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092407        2495209816    3238  1397020466           0      2503      2492       0         0       0       0      431445        160            0        773
2020092408        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092408        2495209816    3235           0           0      2529      2520       0         0       0       0           0       1401            0        782
2020092409        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092409        2495209816    3239  1424896212           0      2566      2556       0         0       0       0      439919       2275            1        792
2020092410        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092410        2495209816    3237  1421132007           0      2572      2569       0         0       0       0      439027       1956            1        795
2020092411        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092411        2495209816    3248           0           0      2629      2614       0         0       0       0           0       2913            1        809
2020092412        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092412        2495209816    3249  1448536971           0      2666      2651       0         0       0       0      445841       2722            1        820
2020092413        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092413        2495209816    3238  1437662381           0      2675      2668       0         0       0       0      443997       2445            1        826
2020092414        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092414        2495209816    3202           0           0      2680      2672       0         0       0       0           0       3238            1        837
2020092415        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092415        2495209816    3256  1450466091           0      2768      2758       0         0       0       0      445475       2920            1        850
2020092416        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092416        2495209816    3240  1450111226           0      2794      2779       0         0       0       0      447565       3109            1        862
2020092417        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092417        2495209816    3237           0           0      2824      2804       0         0       0       0           0       2818            1        873
2020092418        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092418        2495209816    3239  1424042026           0      2840      2831       0         0       0       0      439655       3346            1        877
2020092419        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092419        2495209816    3256  1435714284           0      2894      2885       0         0       0       0      440944       3120            1        889
2020092420        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092420        2495209816    3237           0           0      2914      2902       0         0       0       0           0       2788            1        900
2020092421        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092421        2495209816    3240  1433834204           0      2967      2946       0         0       0       0      442541       2857            1        916
2020092422        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092422        2495209816    3200  1413189546           0      2938      2924       0         0       0       0      441622       2346            1        918
2020092423        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092423        2495209816    3256           0           0      3032      3014       0         0       0       0           0       2108            1        931
2020092500        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092500        2495209816    3238  1417260989           0      3005      2982       0         0       0       0      437696       1358            0        928
2020092501        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092501        2495209816    3233  1296496694           0      2456      2444       0         0       0       0      401020        416            0        760
2020092502        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092502        2495209816    3220   473519624           0      2521      2518       0         0       0       0      147056        164            0        783
2020092503        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092503        2495209816    3257  1419336329           0      2546      2548       0         0       0       0      435780          9            0        782
2020092504        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092504        2495209816    3239  1410697902           0      2554      2548       0         0       0       0      435535          0            0        789
2020092505        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092505        2495209816    3239           0           0      2562      2548       0         0       0       0           0          0            0        791
2020092506        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092506        2495209816    3238  1422005253           0      2555      2551       0         0       0       0      439162          0            0        789
2020092507        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092507        2495209816    3244  1442245766           0      2591      2583       0         0       0       0      444589        276            0        799
2020092508        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092508        2495209816    3247           0           0      2642      2634       0         0       0       0           0       1679            1        814
2020092509        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092509        2495209816    3236  1467804298           0      2665      2656       0         0       0       0      453586       2599            1        824
2020092510        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092510        2495209816    3238  1462271228           0      2677      2670       0         0       0       0      451597       2009            1        827
2020092511        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092511        2495209816    3235           0           0      2720      2709       0         0       0       0           0       3010            1        841
2020092512        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092512        2495209816    3254  1465449842           0      2739      2723       0         0       0       0      450353       2555            1        842
2020092513        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092513        2495209816    3240  1459543406           0      2726      2709       0         0       0       0      450476       2664            1        841
2020092514        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092514        2495209816    3185           0           0      2702      2694       0         0       0       0           0       3784            1        848
2020092515        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092515        2495209816    3240  1447314954           0      2746      2742       0         0       0       0      446702       3116            1        848
2020092516        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092516        2495209816    3258  1475259620           0      2867      2821       0         0       0       0      452811       3183            1        880
2020092517        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092517        2495209816    3239           0           0      2841      2822       0         0       0       0           0       3168            1        877
2020092518        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092518        2495209816    3237  1455741217           0      2835      2815       0         0       0       0      449719       3258            1        876
2020092519        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092519        2495209816    3237  1456570263           0      2858      2846       0         0       0       0      449975       3135            1        883
2020092520        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092520        2495209816    3238           0           0      2881      2869       0         0       0       0           0       3033            1        890
2020092521        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092521        2495209816    3238  1464946512           0      2912      2898       0         0       0       0      452423       2918            1        899
2020092522        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092522        2495209816    3256  1468725511           0      2946      2929       0         0       0       0      451083       2386            1        905
2020092523        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092523        2495209816    3240           0           0      2948      2936       0         0       0       0           0       2258            1        910
2020092600        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092600        2495209816    3238  1437269657           0      2915      2904       0         0       0       0      443876       1648            1        900
2020092601        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092601        2495209816    3239  1358782568           0      2399      2387       0         0       0       0      419507        577            0        741
2020092602        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092602        2495209816    3240  1385828878           0      2525      2519       0         0       0       0      427725        158            0        779
2020092603        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092603        2495209816    3203  1391299844           0      2507      2503       0         0       0       0      434374         23            0        783
2020092604        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092604        2495209816    3237  1351047932           0      2500      2497       0         0       0       0      417377          0            0        772
2020092605        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092605        2495209816    3243           0           0      2529      2522       0         0       0       0           0          0            0        780
2020092606        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092606        2495209816    3235  1392017833           0      2526      2517       0         0       0       0      430299          0            0        781
2020092607        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092607        2495209816    3239  1424101671           0      2559      2544       0         0       0       0      439673        189            0        790
2020092608        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092608        2495209816    3239   496269954           0      2593      2583       0         0       0       0      153217       1619            0        800
2020092609        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092609        2495209816    3244  1473823605           0      2627      2617       0         0       0       0      454323       2200            1        810
2020092610        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092610        2495209816    3253  1473324036           0      2670      2658       0         0       0       0      452912       1995            1        821
2020092611        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092611        2495209816    3238           0           0      2716      2707       0         0       0       0           0       2491            1        839
2020092612        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092612        2495209816    3237  1477721737           0      2741      2733       0         0       0       0      456510       2460            1        847
2020092613        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092613        2495209816    3239  1483323345           0      2768      2755       0         0       0       0      457957       2757            1        855
2020092614        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092614        2495209816    3241  1487746111           0      2808      2792       0         0       0       0      459039       3145            1        866
2020092615        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092615        2495209816    3214  1478749632           0      2820      2809       0         0       0       0      460096       2790            1        878
2020092616        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092616        2495209816    3239           0           0      2869      2858       0         0       0       0           0       3045            1        886
2020092617        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092617        2495209816    3240  1483068741           0      2880      2871       0         0       0       0      457737       2691            1        889
2020092618        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092618        2495209816    3256  1496586513           0      2925      2913       0         0       0       0      459640       3351            1        898
2020092619        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092619        2495209816    3240   686804354           0      2943      2923       0         0       0       0      211977       2956            1        908
2020092620        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092620        2495209816    3239  1492171972           0      2955      2941       0         0       0       0      460689       2728            1        912
2020092621        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092621        2495209816    3242  1499007445           0      2971      2961       0         0       0       0      462371       2518            1        917
2020092622        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092622        2495209816    3254           0           0      2983      2974       0         0       0       0           0       2323            1        917
2020092623        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092623        2495209816    3201  1489464912           0      3019      3006       0         0       0       0      465312       1919            1        943
2020092700        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092700        2495209816    3239  1489900471           0      3025      3011       0         0       0       0      459988       1195            0        934
2020092701        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092701        2495209816    3240  1357716042           0      2434      2429       0         0       0       0      419048        521            0        751
2020092702        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092702        2495209816    3244  1448164591           0      2566      2560       0         0       0       0      446413        276            0        791
2020092703        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092703        2495209816    3252           0           0      2571      2559       0         0       0       0           0         72            0        791
2020092704        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092704        2495209816    3221  1417052962           0      2503      2501       0         0       0       0      439942          2            0        777
2020092705        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092705        2495209816    3240  1435598638           0      2533      2534       0         0       0       0      443086          0            0        782
2020092706        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092706        2495209816    3244           0           0      2527      2528       0         0       0       0           0          0            0        779
2020092707        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092707        2495209816    3254  1470802612           0      2605      2599       0         0       0       0      451998        204            0        801
2020092708        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092708        2495209816    3239  1466922394           0      2601      2589       0         0       0       0      452894       1735            1        803
2020092709        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092709        2495209816    3238  1462310461           0      2647      2640       0         0       0       0      451609       2688            1        818
2020092710        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092710        2495209816    3244  1460262764           0      2656      2651       0         0       0       0      450143       2056            1        819
2020092711        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092711        2495209816    3254  1485315112           0      2720      2708       0         0       0       0      456458       2956            1        836
2020092712        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092712        2495209816    3239  1475673310           0      2718      2710       0         0       0       0      455595       2987            1        839
2020092713        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092713        2495209816    3240  1480502924           0      2752      2741       0         0       0       0      456945       3107            1        849
2020092714        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092714        2495209816    3242           0           0      2770      2758       0         0       0       0           0       3774            1        855
2020092715        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092715        2495209816    3254  1485748247           0      2802      2783       0         0       0       0      456591       3499            1        861
2020092716        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092716        2495209816    3185           0           0      2774      2759       0         0       0       0           0       3173            1        871
2020092717        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092717        2495209816    3239  1488000864           0      2849      2839       0         0       0       0      459401       3113            1        880
2020092718        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092718        2495209816    3238  1480569006           0      2868      2860       0         0       0       0      457248       3294            1        886
2020092719        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092719        2495209816    3243  1479350901           0      2897      2886       0         0       0       0      456167       2813            1        893
2020092720        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092720        2495209816    3240  1466487471           0      2915      2902       0         0       0       0      452620       2953            1        900
2020092721        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092721        2495209816    3254  1473511907           0      2981      2966       0         0       0       0      452831       2821            1        916
2020092722        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092722        2495209816    3240           0           0      2987      2976       0         0       0       0           0       2468            1        922
2020092723        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092723        2495209816    3240  1485591039           0      3023      3010       0         0       0       0      458516       2491            1        933
2020092800        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092800        2495209816    3239  1492094762           0      3032      3023       0         0       0       0      460665       1508            0        936
2020092801        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092801        2495209816    3244  1381156470           0      2472      2464       0         0       0       0      425757        752            0        762
2020092802        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092802        2495209816    3251           0           0      2585      2581       0         0       0       0           0        265            0        795
2020092803        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092803        2495209816    3239  1465140224           0      2599      2588       0         0       0       0      452343         10            0        802
2020092804        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092804        2495209816    3190  1446640595           0      2553      2540       0         0       0       0      453492          0            0        800
2020092805        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092805        2495209816    3253           0           0      2607      2600       0         0       0       0           0          0            0        801
2020092806        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092806        2495209816    3240  1467000606           0      2597      2590       0         0       0       0      452778          0            0        802
2020092807        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092807        2495209816    3242  1472818082           0      2610      2604       0         0       0       0      454293        183            0        805
2020092808        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092808        2495209816    3236           0           0      2631      2616       0         0       0       0           0       1622            1        813
2020092809        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092809        2495209816    3240  1489167496           0      2670      2650       0         0       0       0      459620       2737            1        824
2020092810        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092810        2495209816    3239  1480634051           0      2677      2666       0         0       0       0      457127       2304            1        826
2020092811        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092811        2495209816    3240           0           0      2729      2716       0         0       0       0           0       3116            1        842
2020092812        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092812        2495209816    3244  1490574897           0      2738      2727       0         0       0       0      459487       2975            1        844
2020092813        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092813        2495209816    3239  1495331918           0      2775      2758       0         0       0       0      461665       3305            1        857
2020092814        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092814        2495209816    3253           0           0      2811      2791       0         0       0       0           0       3988            1        864
2020092815        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092815        2495209816    3239  1494368501           0      2810      2799       0         0       0       0      461367       3532            1        867
2020092816        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092816        2495209816    3208           0           0      2798      2786       0         0       0       0           0       3395            1        872
2020092817        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092817        2495209816    3253  1503663984           0      2890      2868       0         0       0       0      462239       3418            1        888
2020092818        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092818        2495209816    3239  1492963087           0      2880      2870       0         0       0       0      460933       3405            1        889
2020092819        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092819        2495209816    3239           0           0      2924      2914       0         0       0       0           0       3268            1        903
2020092820        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092820        2495209816    3243  1502574273           0      2932      2925       0         0       0       0      463328       3413            1        904
2020092821        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092821        2495209816    3254  1511131260           0      2960      2945       0         0       0       0      464392       2994            1        910
2020092822        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092822        2495209816    3240           0           0      2963      2950       0         0       0       0           0       2852            1        915
2020092823        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092823        2495209816    3240  1511966632           0      3007      2992       0         0       0       0      466656       2395            1        928
2020092900        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092900        2495209816    3206  1483716066           0      2963      2954       0         0       0       0      462794       1355            0        924
2020092901        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092901        2495209816    3254           0           0      2412      2406       0         0       0       0           0        406            0        741
2020092902        1636324798      99   352210787    13407233     21201      2845    7253      9775       0       6     3557685         37            0     214150
2020092902        2495209816    2178   981309957           0      1711      1707       0         0       0       0      450556         66            0        786
2020092903        1636324798     318  1160978121    38861241     60087     10011    3682     42585       0      31     3650875         56            0     188954
2020092903        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092904        1636324798     315  1138406066    37854487     59574     10038    3329     41809       0      29     3613988          0            0     189123
2020092904        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092905        1636324798     313  1143701796    38668999     59974     10059    3587     41987       0      33     3653999          0            0     191611
2020092905        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092906        1636324798     306           0    42268789     62487      9733    4179     44183       0      31           0          0            0     204205
2020092906        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092907        1636324798     323  1158824752    41644602     61905     10292    4001     43486       0      40     3587693        201            1     191657
2020092907        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092908        1636324798     309  1167837295    37852269     63066     10364    3967     44390       0      42     3779409       2124            7     204097
2020092908        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092909        1636324798     310  1118885638    36122238     63801      9831    4341     45415       0      46     3609309       3514           11     205811
2020092909        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092910        1636324798     275           0    33482927     65518      9199    9300     43109       0      48           0       3574           13     238246
2020092910        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092911        1636324798     235   915691746    71725197     66676      9162    7097     46978       0      44     3896561       4472           19     283727
2020092911        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092912        1636324798     257   942550994    60478871     65961      9210    6233     46797       0      42     3667514       5398           21     256657
2020092912        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092913        1636324798     163   646322619    45033189     68480      8699   10553     46613       0      86     3965169       4002           25     420122
2020092913        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092914        1636324798     178   655193101    34549897     67433      8435    9002     46876       0      78     3680860       4798           27     378839
2020092914        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092915        1636324798     162           0    42181497     69005      8512    9628     48119       0      73           0       5236           32     425955
2020092915        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092916        1636324798     180   659119160    37016987     67935      8320    8938     47657       0      74     3661773       6284           35     377415
2020092916        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092917        1636324798     178   668652544    36068743     66633      8204    8260     47129       0      66     3756475       4672           26     374341
2020092917        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092918        1636324798     182   690411953    47612967     68886      8662    9144     48329       0      64     3793472       6218           34     378496
2020092918        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092919        1636324798     180   716579287    38972462     68804      8613    8099     49062       0      68     3980996       5828           32     382243
2020092919        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092920        1636324798     198   725411011    42912011     66999      8657    8366     46635       0      72     3663692       4517           23     338377
2020092920        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092921        1636324798     182           0    56233403     68130      9001    9442     46655       0      79           0       3744           21     374340
2020092921        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092922        1636324798     200   761626806    56780398     67513      9089    8585     47243       0      60     3808134       3849           19     337564
2020092922        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092923        1636324798     217   785661905    49927309     66720      9066    7530     46950       0      59     3620562       3528           16     307464
2020092923        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093000        1636324798     203   827248817    65561662     67630      9780    8513     46470       0      70     4075117       2246           11     333155
2020093000        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093001        1636324798     325  1200248776   124828228     62141     10674    6906     40984       0      19     3693073        619            2     191202
2020093001        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093002        1636324798     310  1110431303    39739255     57335      9643    2766     40456       0      23     3582036        185            1     184951
2020093002        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093003        1636324798     320  1149651569    39694801     61120      9942    3466     43492       0      27     3592661         47            0     191000
2020093003        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093004        1636324798     327           0    40854731     62249     10587    3609     43766       0      31           0         21            0     190365
2020093004        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093005        1636324798     319  1125342936    38863217     59590     10107    3507     41533       0      32     3527721          0            0     186801
2020093005        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093006        1636324798     309  1145849658    38889950     62572     10367    3745     44353       0      34     3708251          0            0     202500
2020093006        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093007        1636324798     327  1171387941    39245953     63327     10553    3852     44758       0      40     3582226        241            1     193662
2020093007        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093008        1636324798     296           0    38143234     64608     10248    4218     45887       0      41           0       2377            8     218271
2020093008        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093009        1636324798     291  1070234826    36419287     65024      9485    4331     46952       0      42     3677783       3606           12     223449
2020093009        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093010        1636324798     297  1060264318    35236356     65779      9293    4500     47678       0      43     3569914       3473           12     221476
2020093010        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0

404 rows selected.

从DBA_HIST_SQLSTAT中看到,该SQL有两个执行计划:

PLAH_HASH_VALUE:(1) 1636324798 (2)2495209816其中2495209816效率相对较高,1636324798一直存在但是在9月29日之前并没有启用过,一直在用2495209816,直到2020092902(2点)开始启用,并且在2020092903(3点)完全使用。但是ORACLE为何突然使用了差的执行计划了?


Inst: 1   Child: 0    Plan hash value: 2495209816

                      --------------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                       | Name                         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
                      --------------------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                |                              |        |       |   372K(100)|          |       |       |          |
                      |*  1 |  COUNT STOPKEY                  |                              |        |       |            |          |       |       |          |
                      |   2 |   VIEW                          |                              |      1 |   176 |   372K  (1)| 01:14:32 |       |       |          |
                      |*  3 |    SORT ORDER BY STOPKEY        |                              |      1 |   125 |   372K  (1)| 01:14:32 |  1024 |  1024 |          |
                      |   4 |     NESTED LOOPS                |                              |      1 |   125 |   372K  (1)| 01:14:32 |       |       |          |
                      |   5 |      NESTED LOOPS               |                              |    120K|   125 |   372K  (1)| 01:14:32 |       |       |          |
                      |*  6 |       TABLE ACCESS FULL         | T1                           |    120K|  4482K| 10211   (1)| 00:02:03 |       |       |          |
                      |*  7 |       INDEX RANGE SCAN          | T_POSITIONS_KEY              |      1 |       |     3   (0)| 00:00:01 |       |       |          |
                      |*  8 |      TABLE ACCESS BY INDEX ROWID| T                            |      1 |    87 |     3   (0)| 00:00:01 |       |       |          |
                      --------------------------------------------------------------------------------------------------------------------------------------------

Inst: 1   Child: 38   Plan hash value: 1636324798

                      ----------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation               | Name               | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
                      ----------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT        |                    |        |       |       |  1039K(100)|          |       |       |          |
                      |*  1 |  COUNT STOPKEY          |                    |        |       |       |            |          |       |       |          |
                      |   2 |   VIEW                  |                    |    118K|    19M|       |  1039K  (1)| 03:27:50 |       |       |          |
                      |*  3 |    SORT ORDER BY STOPKEY|                    |    118K|    14M|    16M|  1039K  (1)| 03:27:50 |  1024 |  1024 |          |
                      |*  4 |     HASH JOIN           |                    |    118K|    14M|  6024K|  1035K  (1)| 03:27:10 |  9516K|  3276K|   10M (0)|
                      |*  5 |      TABLE ACCESS FULL  | T1                 |    123K|  4576K|       | 10211   (1)| 00:02:03 |       |       |          |
                      |*  6 |      TABLE ACCESS FULL  | T                  |    118K|     9M|       |  1024K  (1)| 03:24:57 |       |       |          |
                      ----------------------------------------------------------------------------------------------------------------------------------

经过分析发现,此SQL涉及对象在9月28日时触发了统计信息收集,并且因此游标滚动失效,9月29日零时进行了硬解析。因数据库中绑定变量窥探关闭(_optim_peek_user_binds,被设定为FALSE),无法”窥视“直方图,根据数据分布与bind值分析,因数据倾斜严重+关闭“窥探直方图”情况下被认为平均分布,优化器选择了选择了”CBO认为效率高,实际效率差”的执行计划。针对此情形,可以利用SQL_PROFILE进行绑定(借助coe_load_sql_profile.sql)脚本绑定后

数据库GC类等待消失,SQL效率恢复至毫秒级别。

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous报错修复

休假前,同事在异地恢复一套数据库时报错:RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous。以下内容为还原报错场景与修复手段:

ora11g@Travelskydba-ora dbs]$ rman target / catalog cata_user/oracle@ora977b

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 4 10:13:01 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD3 (not mounted)
connected to recovery catalog database

RMAN> restore controlfile;

Starting restore at 04-SEP-20
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/04/2020 10:13:08
RMAN-12010: automatic channel allocation initialization failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous


SQL> select * from rc_database;

    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS_TIME
---------- ---------- ---------- -------- ----------------- -----------------
         1          2 1663399389 PROD3                    1 20200904 09:23:41
        49         50 1663401346 PROD3                    1 20200904 09:56:18
		

报错根因为此数据库在CATALOG库中注册了2次(期间重新建立过DB-create d atabase操作),所以在备份还原controfile时需要指定dbid:

RMAN> set dbid=1663401346       

executing command: SET DBID
database name is "PROD3" and DBID is 1663401346

RMAN> restore controlfile;

Starting restore at 04-SEP-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1
channel ORA_DISK_1: piece handle=/opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1 tag=TAG20200904T100809
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/ora11g/albert/controlfile1.ctl
output file name=/opt/app/ora11g/albert/controlfile2.ctl
Finished restore at 04-SEP-20

为了避免后续再次出现此类问题,可以将已经废弃的数据库dbid从catalog中unregister,避免每次set dbid操作

[ora11g@Travelskydba-ora app]$ rman target / catalog cata_user/oracle@travelskydba

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 4 10:35:56 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> set dbid=1663399389 

executing command: SET DBID
database name is "PROD3" and DBID is 1663399389

RMAN> unregister database;

database name is "PROD3" and DBID is 1663399389

Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog

RMAN> 

SQL> select * from rc_database;

    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS_TIME
---------- ---------- ---------- -------- ----------------- -----------------
        49         50 1663401346 PROD3                    1 20200904 09:56:18

SQL> 

[ora11g@Travelskydba-ora dbs]$ rman target / catalog cata_user/oracle@travelskydba

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 4 10:37:32 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD3 (not mounted)
connected to recovery catalog database

RMAN> restore controlfile;

Starting restore at 04-SEP-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1
channel ORA_DISK_1: piece handle=/opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1 tag=TAG20200904T100809
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/ora11g/albert/controlfile1.ctl
output file name=/opt/app/ora11g/albert/controlfile2.ctl
Finished restore at 04-SEP-20

生产数据库查询与插入hang案例

前不久,生产Oracle数据库上出现一个比较紧急的案例,前台业务查询与插入操作全部夯死,涉及这些SQL的业务全部中断。接到电话后立即查看系统状态

此时系统中出现了latch: row cache objects ,library cache lock这些本不该在生产系统中出现的事件:
SQL> select event,count(*) from v$session where wait_class <> 'Idle' group by event order by 2 desc;

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
library cache lock                                                        2
SQL*Net message to client                                                 2
log file sync                                                             1
latch: row cache objects                                                  1



这些等待的Blocking_session均为空
SQL> select username,sid,serial#,sql_id,blocking_session,event from  v$session where wait_class <> 'Idle';

USERNAME                              SID    SERIAL# SQL_ID        BLOCKING_SESSION EVENT
------------------------------ ---------- ---------- ------------- ---------------- ----------------------------------------------------------------
Albert                               1015       3251 fdn44h4am80mb                  gc cr multi block request
Albert                               1184      54311 6sx33q2nq3c67                  library cache lock
Albert                               2738       4293 3ndnsw62wz2f1                  latch: row cache objects
Albert                               2957      17039                                reliable message
Albert                               3414      13501 7jj34snwajctj                  library cache lock
SYS                                  4192      29879 dh8qnqqr8jqyt                  SQL*Net message to client

此时需要找到Library cache lock等事件的阻塞源头,尽快恢复中断业务,可以利用如下代码进行确认追踪

SQL> select /*+ ordered */
  2  w1.sid waiting_session,
  3  h1.sid holding_session,
  4  w.kgllktype lock_or_pin,
  5  w.kgllkhdl address,
  6  decode(h.kgllkmod,
  7          0,
  8          'None',
  9          1,
 10          'Null',
 11          2,
 12          'Share',
 13          3,
 14          'Exclusive',
 15          'Unknown') mode_held,
 16  decode(w.kgllkreq,
 17          0,
 18          'None',
 19          1,
 20          'Null',
 21          2,
 22          'Share',
 23          3,
 24          'Exclusive',
 25          'Unknown') mode_requested
 26    from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
 27  where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and
 28         ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and
 29         (((w.kgllkmod = 0) or (w.kgllkmod = 1)) and
 30         ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
 31     and w.kgllktype = h.kgllktype
 32     and w.kgllkhdl = h.kgllkhdl
 33     and w.kgllkuse = w1.saddr
 34     and h.kgllkuse = h1.saddr;

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS          MODE_HELD MODE_REQU
--------------- --------------- ---- ---------------- --------- ---------
           3414            2738 Lock 0000001FDF10B328 Share     Exclusive
           1184            2738 Lock 0000001FDF10B328 Share     Share

可以从上述结果中看到,2738会话持有Hanle address “0000001FDF10B328”的独占锁:Exclusive,所以应急杀掉2738会话,KILL SESSION会话后,故障恢复。

SQL> r
  1* select username,sid,serial#,sql_id,blocking_session,event from  v$session where wait_class <> 'Idle'

USERNAME                              SID    SERIAL# SQL_ID        BLOCKING_SESSION EVENT
------------------------------ ---------- ---------- ------------- ---------------- ----------------------------------------------------------------
Albert                                   1015       3251 35gvvuy7b2p2s                  db file scattered read
Albert                                   1184      54311 6sx33q2nq3c67                  SQL*Net more data from client
Albert                                   2957      17041 6sx33q2nq3c67                  SQL*Net more data from client
Albert                                 4192      29961 dh8qnqqr8jqyt                  SQL*Net message to client

事后根因分析,为何在业务高峰期间会有library cache lock长时间占有不释放的情况?

确认ASH时间点后,VTERM\GS02是受害者,基本大部分会话都是被2738这个会话阻塞
SQL> select --to_char(sample_time, 'yyyymmdd hh24:mi:ss') sample_time,
  2   event,
  3   sql_id,
  4   program,
  5   machine,
  6   sql_opname,
  7   BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
  8   count(*)
  9    from v$active_session_history a
 10   where to_char(sample_time, 'yyyymmdd hh24:mi:ss') between
 11         '20200501 00:00:00' and '20200501 10:44:00'
 12     and event = 'library cache lock'
 13   group by --to_char(sample_time, 'yyyymmdd hh24:mi:ss'),
 14            event,
 15            sql_id,
 16            program,
 17            machine,
 18            BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
 19            sql_opname
 20   order by 7
 21  ;

EVENT                                                            SQL_ID        PROGRAM                                          MACHINE                                                          SQL_OPNAME                                                       BLOCKING_INST_ID||'_'||BLOCKING_SESSION                                            COUNT(*)
---------------------------------------------------------------- ------------- ------------------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
library cache lock                                               0uujs8nqcm145 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                         7  
library cache lock                                               49qykykfhwqtm plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                        22
library cache lock                                               973s831gy6jk4 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                        38
library cache lock                                                             sqlldr@hostname (TNS V1-V3)                      hostname                                                                                                                          _                                                                                        58
library cache lock                                               g639trfsrp9m5 plsqldev.exe                                     VTERM\Travelskydba01                                             PL/SQL EXECUTE                                                   _                                                                                        62
library cache lock                                               3y3gt31q23uk1 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                        65
library cache lock                                               6bwmj9n38kjp6 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                       494
library cache lock                                               973s831gy6jk4 plsqldev.exe                                     VTERM\Travelskydba01                                             SELECT                                                           _                                                                                      1475
library cache lock                                               7jj34snwajctj detrreceipt@hostname2 (TNS V1-V3)                hostname2                                                        INSERT                                                           1_2738                                                                                 2419
library cache lock                                               6sx33q2nq3c67 sqlldr@hostname (TNS V1-V3)                      hostname                                                         INSERT                                                           _                                                                                      2419
library cache lock                                               7jj34snwajctj detrreceipt@hostname2 (TNS V1-V3)                hostname2                                                        INSERT                                                           _                                                                                      2515
library cache lock                                               6sx33q2nq3c67 sqlldr@hostname (TNS V1-V3)                      hostname                                                         INSERT                                                           1_2738                                                                                10760

12 rows selected

ASH中后续并没有记录2738这个会话再等latch: row cache objects,但是抓到了其他的进程信息,例如PROGRAM/MACHINE,猜测故障当时此会话已经不是ACTIVE

ASH中并没有记录2738这个会话再等latch: row cache objects
SQL> select --to_char(sample_time, 'yyyymmdd hh24:mi:ss') sample_time,
  2   event,
  3   sql_id,
  4   program,
  5   machine,
  6   sql_opname,
  7   BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
  8   count(*)
  9    from ash_20200501 a
 10   where to_char(sample_time, 'yyyymmdd hh24:mi:ss') between
 11         '20200430 00:00:00' and '20200501 10:44:00'
 12     and session_id=2738 and program ='plsqldev.exe'
 13   group by --to_char(sample_time, 'yyyymmdd hh24:mi:ss'),
 14            event,
 15            sql_id,
 16            program,
 17            machine,
 18            BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
 19            sql_opname
 20   order by 1
 21  ;

EVENT                                                            SQL_ID        PROGRAM                                          MACHINE                                                          SQL_OPNAME                                                       BLOCKING_INST_ID||'_'||BLOCKING_SESSION                                            COUNT(*)
---------------------------------------------------------------- ------------- ------------------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
                                                                 3ndnsw62wz2f1 plsqldev.exe                                     VTERM\Travelskydba01                                                    SELECT                                                           _                                                                            58210
                                                                 3ndnsw62wz2f1 plsqldev.exe                                     VTERM\Travelskydba01                                                                                                                     _                                                                             6

SQL> 

2738这个会话最早在4月30日18点20开始出现,一直持续到故障发生时(故障发生时间:20200501 10:00左右)

738这个会话最早在4月30日18点20开始出现,一直持续到故障发生时:
SQL> select rownum, a.*
  2    from (select to_char(sample_time, 'yyyymmdd hh24:mi:ss') sample_time,
  3                 event,
  4                 sql_id,
  5                 program,
  6                 machine,
  7                 sql_opname,
  8                 BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
  9                 count(*)
 10            from ash_20200501 a
 11           where to_char(sample_time, 'yyyymmdd hh24:mi:ss') between
 12                 '20200430 00:00:00' and '20200501 10:44:00'
 13             and session_id = 2738
 14             and program = 'plsqldev.exe'
 15           group by to_char(sample_time, 'yyyymmdd hh24:mi:ss'),
 16                    event,
 17                    sql_id,
 18                    program,
 19                    machine,
 20                    BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
 21                    sql_opname
 22           order by 1) a where rownum <2
 23  ;

    ROWNUM SAMPLE_TIME       EVENT                                                            SQL_ID        PROGRAM                                          MACHINE                                                          SQL_OPNAME                                                       BLOCKING_INST_ID||'_'||BLOCKING_SESSION                                            COUNT(*)
---------- ----------------- ---------------------------------------------------------------- ------------- ------------------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
         1 20200430 18:20:47                                                                  3ndnsw62wz2f1 plsqldev.exe                                     VTERM\Travelskydba01                                                                                                                       _                                                                                         1
继续阅读

RAC集群节点关闭长时间Waiting for ASM to shutdown处理过程与案例分析

5月1日晚,接到值班同事求助电话,RAC集群重启节点后出现监听无法注册SERVICE的情况,应用连接在数据库实例重启后无法在维护节点上连接。

放下电话登录到系统中查看CRS状态出现如下错误

CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors。

CRS此时已经处于非正常状态,listener/service都是以资源的方式注册在CRSD中,所以出现SERVICE的异常现象是符合情理的。随后我准备重启CRS的时候,在stop的过程中却出现了长时间hang的情况,等待Waiting for ASM to shutdown。

[root@travelskydba-rac grid]# cd bin
[root@travelskydba-rac bin]# ./crsctl stat res tt
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.
[root@travelskydba-rac bin]# ./crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.
[root@travelskydba-rac bin]# ./crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.crsd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.crsd' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.crf' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.ctssd' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.evmd' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.asm' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.mdnsd' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.crf' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.evmd' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'travelskydba-rac' succeeded

CRS-2675: Stop of 'ora.asm' on 'travelskydba-rac' failed  ---卡在这里很长时间,大概有8分钟左右
CRS-2679: Attempting to clean 'ora.asm' on 'travelskydba-rac'
CRS-2681: Clean of 'ora.asm' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.cssd' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.gipcd' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.drivers.acfs' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'travelskydba-rac' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'travelskydba-rac' has completed
CRS-4133: Oracle High Availability Services has been stopped.
2020-05-01 22:37:28.633:
[ctssd(2029)]CRS-2405:The Cluster Time Synchronization Service on host travelskydba-rac is shutdown by user
[client(89699)]CRS-10001:01-May-20 22:37 ACFS-9290: Waiting for ASM to shutdown
.... --省略重复输出
[client(124967)]CRS-10001:01-May-20 22:46 ACFS-9290: Waiting for ASM to shutdown.
[client(125047)]CRS-10001:01-May-20 22:46 ACFS-9290: Waiting for ASM to shutdown.
[client(125174)]CRS-10001:01-May-20 22:46 ACFS-9290: Waiting for ASM to shutdown.
[client(125491)]CRS-10001:01-May-20 22:47 ACFS-9290: Waiting for ASM to shutdown.
[client(125548)]CRS-10001:01-May-20 22:47 ACFS-9290: Waiting for ASM to shutdown.
[client(125614)]CRS-10001:01-May-20 22:47 ACFS-9290: Waiting for ASM to shutdown.
2020-05-01 22:47:28.727:
[/opt/app/11.2.0/grid/bin/oraagent.bin(759)]CRS-5818:Aborted command 'stop' for resource 'ora.asm'. Details at (:CRSAGF00113:) {0:0:52684} in /opt/app/11.2.0/grid/log/travelskydba-rac/agent/ohasd/oraagent_grid//oraagent_grid.log.
2020-05-01 22:47:30.730:
[ohasd(196541)]CRS-2757:Command 'Stop' timed out waiting for response from the resource 'ora.asm'. Details at (:CRSPE00111:) {0:0:52684} in /opt/app/11.2.0/grid/log/travelskydba-rac/ohasd/ohasd.log.
2020-05-01 22:47:33.404:
[cssd(872)]CRS-1603:CSSD on node travelskydba-rac shutdown by user.
2020-05-01 22:47:33.513:
[ohasd(196541)]CRS-2767:Resource state recovery not attempted for 'ora.cssdmonitor' as its target state is OFFLINE
2020-05-01 22:47:33.609:
[cssd(872)]CRS-1660:The CSS daemon shutdown has completed
2020-05-01 22:47:35.756:
[gpnpd(790)]CRS-2329:GPNPD on node travelskydba-rac shutdown.

DB alert日志:
NOTE: ASMB terminating
Errors in file /opt/app/ora11g/diag/rdbms/Albert/Albert1/trace/Albert1_asmb_188518.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 1828 Serial number: 22601
Errors in file /opt/app/ora11g/diag/rdbms/Albert/Albert1/trace/Albert1_asmb_188518.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 1828 Serial number: 22601
ASMB (ospid: 188518): terminating the instance due to error 15064
Fri May 01 22:47:30 2020
System state dump requested by (instance=1, osid=188518 (ASMB)), summary=[abnormal instance termination].
System State dumped to trace file /opt/app/ora11g/diag/rdbms/Albert/Albert1/trace/Albert1_diag_188401_20200501224730.trc
Dumping diagnostic data in directory=[cdmp_20200501224730], requested by (instance=1, osid=188518 (ASMB)), summary=[abnormal instance termination].
Instance terminated by ASMB, pid = 188518

节点2:集群日志:
2020-05-01 22:47:33.505:
[cssd(133163)]CRS-1625:Node travelskydba-rac, number 1, was shut down
2020-05-01 22:47:33.517:
[cssd(133163)]CRS-1601:CSSD Reconfiguration complete. Active nodes are travelskydba2-rac travelskydba3-rac .


节点3集群日志:
2020-05-01 22:47:33.505:
[cssd(141351)]CRS-1625:Node travelskydba-rac, number 1, was shut down
2020-05-01 22:47:33.517:
[cssd(141351)]CRS-1601:CSSD Reconfiguration complete. Active nodes are tr730e67-rac travelskydba3-rac .

也就是说节点1集群完全停下来,耗时22:37-22:47 10分钟,hang期间,让值班员联系硬件重启服务器,但是在硬件重启操作前,CRS却停下来了。

保险起见,还是让硬件重启一下服务器(因21点左右,硬件团队同事刚刚对此节点集群的私网心跳线进行更换)。

继续阅读

ORA-15028: ASM file ‘+DG_ARC***’ not dropped; currently being accessed 案例

昨日,接到硬件团队同事电话,告知一个数据库出现内存耗尽,需要重启。查询了一下配置,确认此数据库database_role为 physical standby,随后硬件团队同事进行了服务器重启。

服务器重启过后,启动数据库,但是数据库登录依然缓慢,因为我们配置了broker,会自动启用实时应用:

alter database recover managed standby database using current logfile disconnect from session;

但是奇怪的是,数据库并没有应用日志,一直在处于waiting for log的状态,等待的日志文件已经在备库本地,无奈之下,手工注册日志后数据库进行了日志应用,但是很块MRP0进程继续处于 waiting for log的状态 ,再次注册日志却抛出了

ORA-00311: cannot read header from archiveed log
ORA-27072: File I/O error

看来这次故障并不是简单的内存耗尽,严重怀疑是存储链路层面出现了问题。向硬件团队反馈后,准备断掉主备库DataGuard关系,等存储问题恢复后进行重构。

将DataGuard断掉后,验证一下主库归档备份的状态,发现了更加奇怪的现象:

    RECID OPERATION                         STATUS                  START_TIME          END_TIME            OBJECT_TYPE
---------- --------------------------------- ----------------------- ------------------- ------------------- -------------	
     54558 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:30:37 2020-04-16 23:30:50 ARCHIVELOG
     54556 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:19:42 2020-04-16 23:19:57 ARCHIVELOG
     54554 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:15:10 2020-04-16 23:15:24 ARCHIVELOG
     54552 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 22:58:50 2020-04-16 22:59:12 ARCHIVELOG
     54550 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 21:58:51 2020-04-16 21:59:08 ARCHIVELOG
     54548 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 20:58:51 2020-04-16 20:59:04 ARCHIVELOG
     54546 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 19:58:51 2020-04-16 19:59:04 ARCHIVELOG


input archived log thread=1 sequence=54511 RECID=217898 STAMP=1037920783
input archived log thread=2 sequence=54482 RECID=217899 STAMP=1037920784
channel ch00: starting piece 1 at 16-APR-20
channel ch00: finished piece 1 at 16-APR-20
piece handle=arch_Travelskydba_std_27847_1_1037920788 tag=TAG20200416T231948 comment=API Version 2.0,MMS Version 5.0.0.0
channel ch00: backup set complete, elapsed time: 00:00:07
channel ch00: deleting archived log(s)
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54501.352.1037918509 RECID=217870 STAMP=1037918508
RMAN-08118: WARNING: could not delete the following archived redo log
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54501.352.1037918509 thread=1 sequence=54501
error from target database:
ORA-15028: ASM file '+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54501.352.1037918509' not dropped; currently being accessed

archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54502.355.1037918877 RECID=217873 STAMP=1037918877
RMAN-08118: WARNING: could not delete the following archived redo log
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54502.355.1037918877 thread=1 sequence=54502
error from target database:
ORA-15028: ASM file '+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54502.355.1037918877' not dropped; currently being accessed

archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_2_seq_54474.308.1037918875 RECID=217872 STAMP=1037918875
RMAN-08118: WARNING: could not delete the following archived redo log
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_2_seq_54474.308.1037918875 thread=2 sequence=54474
error from target database:
ORA-15028: ASM file '+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_2_seq_54474.308.1037918875' not dropped; currently being accessed

archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_2_seq_54481.388.1037920773 RECID=217897 STAMP=1037920772
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54510.318.1037920771 RECID=217896 STAMP=1037920770
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54511.332.1037920783 RECID=217898 STAMP=1037920783
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_2_seq_54482.321.1037920785 RECID=217899 STAMP=1037920784
Finished backup at 16-APR-20

released channel: ch00	

备份日志中出现了ORA-15028错误, error from target database:
ORA-15028: ASM file not dropped; currently being accessed , 可是主备库关系已经断开,备库理论上不需要主库的归档日志并且主库归档进程也是正常的。

再次登录备库,找到了异常的跟因:备库已经shutdown complete,但是操作系统中PMON与arch进程却没有终止(即使用操作系统kill -9也无果)。

随后将备库操作系统关机,再次执行主库的归档备份后, ORA-15028 错误不再出现,备份成功完成

     RECID OPERATION                         STATUS                  START_TIME          END_TIME            OBJECT_TYPE
---------- --------------------------------- ----------------------- ------------------- ------------------- -------------
     54560 BACKUP                            COMPLETED               2020-04-16 23:32:37 2020-04-16 23:32:50 ARCHIVELOG
     54558 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:30:37 2020-04-16 23:30:50 ARCHIVELOG
     54556 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:19:42 2020-04-16 23:19:57 ARCHIVELOG
     54554 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:15:10 2020-04-16 23:15:24 ARCHIVELOG
     54552 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 22:58:50 2020-04-16 22:59:12 ARCHIVELOG

随后在Oracle Support上搜寻了一下这个错误,文档 Doc ID 1466848.1 进行了非常详细的描述,对应本次的故障案例,备库ARCH进程夯死,传输接收时出现异常,从而主库没有及时的释放archive log的lock,RMAN备份archivelog后无法删除文件。

ORACLE Flashback+DataGuard进行数据恢复

上周,在公司中心范围内进行了一次技术分享,主要阐述一下Datagurd技术的使用范围,其中涉及一些Flashback技术的应用:我们主要利用Flashback闪回特性进行应用人员的误操作,进行恢复。

实际操作流程如下:

主库:Travelskydba            
备库:Travelskydba_std

主库:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba                   READ WRITE           PRIMARY          YES NO


备库:	
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES

SQL> show parameter db_flashback_retention_target   

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

主库执行事务:
SQL> create user albert identified by albert;

User created.

SQL> grant connect,resource to albert;

Grant succeeded.

SQL> conn albert/albert
Connected.

SQL> create table Travelskydba (id1 number,id2 number,id3 number);

Table created.

SQL> insert into Travelskydba values(1,2,3);

1 row created.

SQL> insert into Travelskydba values(4,5,6);

1 row created.

SQL> commit;

Commit complete.

主库:
SQL>  select * from albert.Travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3
         4          5          6

SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   20619438


--开启实时应用的备库已经完成redo应用,数据已经保持与主库同步了:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES

SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         4          5          6

有时我们接收到应用人员的电话,数据误删除了,或者对数据的调研不足,导致实时应用的数据当做历史数据删除掉了,请求DBA需要进行数据恢复,此时我们可以利用DataGuard 备库已经开启的flashback特性进行数据恢复。

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES   
SQL> select OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'yyyymmdd hh24:mi:ss') time,RETENTION_TARGET,FLASHBACK_SIZE,ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;

OLDEST_FLASHBACK_SCN TIME              RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ----------------- ---------------- -------------- ------------------------
            20611974 20200325 12:16:12             1440      629145600               1323294720


如果数据量较少,主库可以利用DBLINK 进行数据恢复:
SQL> show user
USER is "ALBERT"
SQL> insert into travelskydba select * from albert.travelskydba@TRAVELSKYDBA_LINK where id1=1;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3
         4          5          6


--数据恢复后,备库可以继续进行实时应用		 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1543505184 bytes
Database Buffers         2550136832 bytes
Redo Buffers               12857344 bytes
Database mounted.
Database opened.		

--备库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and  THREAD# <> 0
  2  /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1       6942          1             5            5

SQL> /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1       6942          1             5            5
RFS       CLOSING      UNKNOWN           1       6944          1             0            0
RFS       OPENING      UNKNOWN           1       6943          0             0            0
RFS       OPENING      UNKNOWN           1       6942          0             0            0
RFS       OPENING      UNKNOWN           1       6946          0             0            0
RFS       OPENING      UNKNOWN           1       6948          0             0            0

6 rows selected.
 
 
--主库:
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and  THREAD# <> 0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6950          5             0            0

SQL> /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6950          6             0            0

SQL> /
/
PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6950          7             0            0

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/archive_Travelskydba/Traveskydba
Oldest online log sequence     6948
Next log sequence to archive   6950
Current log sequence           6950

上述情形适用于误删除数据量较小的情况,如果遇到误删除的数据较多,利用DB_LINK的方式效率相对是比较低下的,我们需要将备库打开至read write mode,将数据导入导出,从而达到进行数据恢复的目的

--备库:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba  Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES



SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

主库误删除用户:
SQL> drop user albert cascade;

User dropped.

备库:
SQL> select * from albert.travelskydba;
select * from albert.travelskydba
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> create restore point tra guarantee flashback database; --创建resotre point

Restore point created.

SQL>  flashback database to scn 20668180;

Flashback complete.


SQL>  ALTER DATABASE ACTIVATE STANDBY DATABASE;--将备库打开至可读可写状态

Database altered.

SQL> alter database open;

Database altered.


SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  READ WRITE           PRIMARY          YES YES


SQL> select * from albert.travelskydba; --数据恢复完成,将此用户导出,导入恢复至主库

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3
		 
SQL> create directory tra_dir as '/home/ora11g/';

Directory created.

SQL> 
SQL> grant read,write,execute on directory tra_dir to public;

Grant succeeded.


[ora11g@albert ]$ expdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=expdp_20200325.log 

Export: Release 11.2.0.4.0 - Production on Wed Mar 25 09:59:48 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=expdp_20200325.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ALBERT"."TRAVELSKYDBA"                     5.812 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/ora11g/albert_dmp.20200325
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 25 10:00:08 2020 elapsed 0 00:00:17

[ora11g@albert ~]$ scp albert_dmp.20200325 ******:/home/ora11g
The authenticity of host '*******' can't be established.
RSA key fingerprint is 45:b9:52:12:86:55:8d:e5:33:8b:b8:59:02:68:81:ff.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '******' (RSA) to the list of known hosts.
ora11g@******'s password: 
albert_dmp.20200325                                                                                                                                     100%  180KB 180.0KB/s   00:00 


--导入主库:

SQL> create directory tra_dir as '/home/ora11g/';

Directory created.

SQL> grant read,write,execute on directory tra_dir to public;

Grant succeeded.


[ora11g@vm-kvm17146-ora ~]$ impdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=impdp_20200325.log 

Import: Release 11.2.0.4.0 - Production on Wed Mar 25 20:28:48 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=impdp_20200325.log 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ALBERT"."TRAVELSKYDBA"                     5.812 KB       1 rows
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Mar 25 20:28:59 2020 elapsed 0 00:00:04

主库数据恢复:
SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

主库数据已经恢复,但是我们现在要继续处理备库,让备库继续进行实时应用

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount; 
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> flashback database to restore point tra; --闪回至restore point

Flashback complete.


SQL> alter database convert to physical standby; 更新control_file,database_role为physical standby

Database altered.


SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES



SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


主库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6972         90             0            0

SQL> alter system switch logfile;

System altered.

SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0
  2  ;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6973          4             0            0


备库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
RFS       IDLE         LGWR              1       6973          8             0            0
MRP0      APPLYING_LOG N/A               1       6973          8             5            5


SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

我们还可以利用Oracle snapshot方式进行备库的read write模式打开

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba  Travelskydba                   READ WRITE           PRIMARY          YES NO                    20755536


SQL>  select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3



--备库:

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES                   20755688

SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3


--主库误删除数据:


SQL> drop user albert cascade;

User dropped.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba                   READ WRITE           PRIMARY          YES NO                    20755761

   

--备库:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES                   20755770
   

SQL> select * from albert.travelskydba;
select * from albert.travelskydba
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

准备恢复数据:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> show parameter broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /oracle/oradata_Travelskydba/Travelskydba/dataguardconfig/dg_config0
                                                 1.ora
dg_broker_config_file2               string      /oracle/oradata_Travelskydba/Travelskydba/dataguardconfig/dg_config0
                                                 2.ora
dg_broker_start                      boolean     TRUE
SQL> alter system set dg_broker_start=false;

System altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> flashback database to scn 20755688;

Flashback complete.

SQL> alter database open;

Database altered.

SQL>  select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> 
SQL> alter database convert to snapshot standby;

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              SNAPSHOT STANDBY YES YES                          0


SQL> alter database open; 

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba  Travelskydba_std                  READ WRITE           SNAPSHOT STANDBY YES YES                   20755867


SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

[ora11g@albert ~]$  expdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200326 logfile=expdp_20200326.log 

Export: Release 11.2.0.4.0 - Production on Thu Mar 26 01:12:23 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200326 logfile=expdp_20200326.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ALBERT"."TRAVELSKYDBA"                     5.812 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/ora11g/albert_dmp.20200326
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Mar 26 01:12:44 2020 elapsed 0 00:00:18

--数据导入过程省略--

--备库恢复至standby模式:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20755688  --之前闪回的SCN点,flashback database to scn 20755688;




SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20757332


SQL> /

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20757332


SQL> alter database open; 

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20757498 

主库:
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1         19        206             0            0

SQL> 
SQL> alter system switch logfile;

System altered.

SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1         20          6             0            0

备库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1         19        142             5            5
RFS       IDLE         LGWR              1         19        142             0            0

SQL> /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1         20         55             5            5
RFS       IDLE         LGWR              1         20         55             0            0

ORA-01196/ORA-01110特殊恢复案例一则

前不久,我们一套生产数据库的dataguard的备库出现实时应用过慢,数据库服务器IOWAIT高的情况,为了避免主库归档日志出现大量挤压,所以值班同事临时应急将主备库关系配置断掉,后续再进行DataGuard重构。除此之外,我们准备将备库打开进行一系列测试,试图探寻数据库服务器IOWAIT高的原因并解决DataGuard延迟的问题。

第二天,同事尝试打开备库,但是报了ORA-01196与错误

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'

此错误因为备库还未将redo log与archive恢复至一致状态,数据库此时处于不一致状态是无法打开的。但是,备库所有的归档日志已经被删除,此时数据库无法进行一个完全恢复

SQL> set line 1000 pagesize 200
SQL> select name,open_mode,CONTROLFILE_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN,DATABASE_ROLE from V$database;

NAME      OPEN_MODE            CONTROLFILE_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN DATABASE_ROLE
--------- -------------------- ------------------- ------------------ ----------- ----------------
Travelskydba  MOUNTED                          7669047             966955     7669046 PHYSICAL STANDBY

SQL>  ALTER DATABASE ACTIVATE STANDBY DATABASE;
 ALTER DATABASE ACTIVATE STANDBY DATABASE
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'

此时,我准备重建控制文件,进行不完全恢复操作

QL> alter database backup controlfile to trace as '/home/ora11g/albert.ctl';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> 

albert.ctl文件内容:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA1459T" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo11.log',
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo12.log'
  ) SIZE 300M BLOCKSIZE 512,
  GROUP 2 (
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo21.log',
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo22.log'
  ) SIZE 300M BLOCKSIZE 512,
  GROUP 3 (
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo31.log',
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo32.log'
  ) SIZE 300M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 4 '/oracle/redo_Travelskydba1/Travelskydba_DG/standby_redo04.log'  SIZE 300M BLOCKSIZE 512,
--   GROUP 5 '/oracle/redo_Travelskydba1/Travelskydba_DG/standby_redo05.log'  SIZE 300M BLOCKSIZE 512
DATAFILE
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/sysaux01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/undotbs01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users02.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users03.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users04.dbf'
CHARACTER SET AL32UTF8
;

[ora11g@ALBERT ~]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 28 12:36:00 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> @albert.ctl
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes

Control file created.  --控制文件重建完毕

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7669047 generated at 11/04/2019 23:00:11 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata_Travelskydba1/Travelskydba_DG/1_247_1017488189.dbf
ORA-00280: change 7669047 for thread 1 is in sequence #247


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'


ORA-01112: media recovery not started

SQL> alter database open resetlogs;  --仍然无法打开数据库
alter database open resetlogs
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'

修改数据库alter system set “_allow_resetlogs_corruption” =true scope=spfile;参数,再次进行不完全恢复(此操作会有严重丢失数据风险,生产系统请慎用)

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes
SQL> alter system set "_allow_resetlogs_corruption" =true scope=spfile;

System altered.
SQL> shutdown abort;
ORACLE instance shut down.

SQL> @albert.ctl
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes

Control file created.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7669047 generated at 11/04/2019 23:00:11 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata_Travelskydba1/Travelskydba_DG/1_247_1017488189.dbf
ORA-00280: change 7669047 for thread 1 is in sequence #247


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [7669055], [0],
[7996264], [4194545], [], [], [], [], [], []
Process ID: 12086
Session ID: 1705 Serial number: 3

此时出现ORA-00600 2262错误,此时距离成功已经不远,只需要推进SCN即可,
SQL> shutdown abort;
ORACLE instance shut down.
SQL> @albert.ctl
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes

Control file created.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7779049 generated at 03/28/2020 12:54:12 needed for thread 1
ORA-00289: suggestion : /oracle/oradata_Travelskydba1/Travelskydba_DG/1_2_1036241636.dbf
ORA-00280: change 7779049 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'


ORA-01112: media recovery not started


SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SQL>  oradebug poke 0x06001AE70 4 8279047 
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER:  [06001AE70, 06001AE74) = 007E5407

SQL> alter database open resetlogs;  --数据库成功打开

Database altered.


SQL> set line 1000 pagesize 200    
SQL> select name,open_mode,CONTROLFILE_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN,DATABASE_ROLE from V$database;

NAME      OPEN_MODE            CONTROLFILE_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN DATABASE_ROLE
--------- -------------------- ------------------- ------------------ ----------- ----------------
Travelskydba  READ WRITE                       8280898            8279049     8280903 PRIMARY

主库 drop tablespace后备库存储空间不释放案例一则

上周,应用人员误操作在平台上tablespace name 打错名字,从而想删除表空间进行重新建立。重建后此数据库备库触发了文件系统满的报警,备库文件系统使用率已经为100%,但是数据库值班人员观察到备库之前建立错误的数据文件已经不在,为何文件系统存储中文件却没有删掉?之前怎么没有出现如此的情况?

我随后做了一个实验给他,原因是在drop tablespace时,没有加上including contents and datafiles

--主库:
SQL> select username,count(*) from gv$session group by username order by 2 desc;

USERNAME                         COUNT(*)
------------------------------ ----------
                                      174
ZABBIX                                  6
SYS                                     5
DBSNMP                                  4
PUBLIC                                  1

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create tablespace albert datafile '+DG_DATA' size 1g;

Tablespace created.


SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT --刚刚创建的表空间

     FILE# NAME
---------- -----------------------------------------------------------------
         1 +DG_DATA/Travelskydba/datafile/system.259.1002299725
         2 +DG_DATA/Travelskydba/datafile/sysaux.260.1002299741
         3 +DG_DATA/Travelskydba/datafile/undotbs1.261.1002299753
         4 +DG_DATA/Travelskydba/datafile/undotbs2.263.1002299769
         5 +DG_DATA/Travelskydba/datafile/users.264.1002299781
         6 +DG_DATA/Travelskydba/datafile/tbs_test_1025_1.269.1022603323
         7 +DG_DATA/Travelskydba/datafile/tbs_test_1025_2.270.1022603429
         8 +DG_DATA/Travelskydba/datafile/albert.271.1035217783


--备库:
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT


SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
         8 /oracle/oradata_Travelskydba/Travelskydba/albert.271.1035217783

8 rows selected.

一、加入including contents and datafiles

--主库删除:
SQL> drop tablespace albert including contents and datafiles;

Tablespace dropped.



--备库确认,正常删除掉
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429

7 rows selected.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2

8 rows selected.


total 68780320
drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:35 system.259.1002299725
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:35 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:35 stdctl01.ctl

此时文件已经正常删除掉。


二、主库不加including contents and datafiles;

--主库创建文件:
SQL> create tablespace albert_leung datafile '+DG_DATA' size 2g;

Tablespace created.

--备库确认:

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
         8 /oracle/oradata_Travelskydba/Travelskydba/albert_leung.271.1035218283

8 rows selected.

SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT_LEUNG


drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:38 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  2147491840 Mar 16 16:38 albert_leung.271.1035218283
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:38 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:38 system.259.1002299725
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:38 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:38 stdctl01.ctl


--主库删除文件:
SQL> drop tablespace albert_leung;

Tablespace dropped.

--备库数据字典:

SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2

8 rows selected

SQL> select FILE#,NAME from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429

--可以看到,备库中的数据字典中已经删除掉,也就是说controlfile中该记录已经被删除

--但是观察到,备库文件系统,物理的数据文件还在,并没有删除。
[ora11g@vm-vmw45661-ora Travelskydba]$ ls -l
total 68780320
-rw-r----- 1 ora11g dba  2147491840 Mar 16 16:38 albert_leung.271.1035218283   --主库CHECKPOINT后不更新
drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:43 stdctl01.ctl
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:43 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:41 system.259.1002299725
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
[ora11g@vm-vmw45661-ora Travelskydba]$ 

--此时,可以使用操作系统命令rm删除掉文件系统中命名错误的数据文件,因为controlfile中已经没有此记录,所以不会涉及数据库的实例恢复。

这个实验,没有加上including contents and datafiles 我只是回答了他第一个问题,但是第二个问题:“为什么之前我删除tablespace的时候,也没有加,怎么就没事呢?

根因在于Oracle Managed Files,这次出问题的此套备库没有使用OMF特性,也就是drop tablespace后,Oracle不会帮你自动的去清理物理文件,只会删除controlfile中的记录,但是之前同事所提到的没事的状况,是因为备库为RAC+ASM架构,默认采用了OMF管理数据文件,所以及时没有添加 including contents and datafiles 也会帮你自动清理文件。

官方原文如下:

1、 Unlike files that are not managed by the database, when an Oracle managed data file or temp file is dropped, the filename is removed from the control file and the file is automatically deleted from the file 

2、 When recovery of a standby database encounters redo for the deletion of a tablespace, it deletes any Oracle managed data files in the local file system. Note that this is independent of the INCLUDING DATAFILES option issued at the primary database.

数据库性能问题导致应用单次SQL效率10ms变为10s

昨日,同事发来一则微信反馈前一天晚应用SQL查询缓慢,原本10ms内能完成的查询SQL在一时间段内:19号22:10到22:45突然执行效率下降到10分钟,然而在22:45后自动恢复正常,想分析一下根因。数据库为一套2节点RAC,Linux -x86 数据库版本为11.2.0.4。

有了明确的时间段,登录到数据库内看看到底这个期间产生了什么性能问题?

数据库DB_TIME、活动会话数与应用反馈的时间基本吻合,从22点10分开始,数据库中的Active_Session数量开始飙升,并且数据库活动会话基本都被Library Cache Lock与Cursor Pin S wait on X等待事件所阻塞。

故障时间段内Top Event百分比

从ASH中看,活动会话最早被节点1的4327会话所阻塞,并且等待的是LIBRARY CACHE LOCK,随后,开始出现sql尝试软解析,遭遇Cursor pin s wait on x,且被节点1的2060会话所阻塞

我们看一下4237与2060在故障期间在干什么?

4327这个会话为oracle auto_task的sql auto tunning advisor job,并且他持有了Library Cache Lock Handle “0x1f9bc9bb98″导致了会话3877想以Mode=3(X)独占模式持有Library Cache Lock不成功,被阻塞。
2060为正常的应用会话,并且同样等待LIBRARY CACHE LOCK,没有阻塞者,这是一个奇怪的现象

但是2060会话在systemstate dump的trace文件中找到阻塞者,同样被4327会话阻塞

从相同时间段的systemstate dump trace文件中看到,2060会话在等待library cache lock,申请的同样是0x1f9bc9bb98的Library Cache Lock Handle (Mode=2(s) 共享模式),虽然没有Dump出2060的阻塞者,但是从申请的Handle上可以推断,都是被4327会话sql auto tunning advisor job阻塞。

分析结论: 此时间段确实为oracle autotask作业的时间窗口,但是job会话并不会长时间持有Library Cache Lock,这个会话当时在等待db file scattered read,数据库当时物理读非常高并且io链路上没有明显异常,异常点1:此JOB作业长时间持有LIBRARY CACHE LOCK不释放且JOB涉及的SQL造成物理读很高,异常点2:内存lock与Mutex争用又会导致cpu Idle低,从而进一步导致io无法调度完成,造成会话JOB工作效率下降,从而没有及时的释放LIBRARY CACHE LOCK的可能性会加大,这些都是非常不正常的行为,所以我决定disable sql tunning advisor功能,规避此问题再次发生。