​[20200108]在线建立索引失败分析.txt

  • lfree

    2020-01-08 09:48:38
  • Oracle
  • 原创

[20200108]在线建立索引失败分析.txt

--//昨天上午使用online方式建立一个索引。遇到如下问题：
--//我是以sys用户登录在服务器登录执行的，报如下错误:
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01031: insufficient privileges
ORA-06512: at line 13
ORA-00604: error occurred at recursive SQL level 2
ORA-01031: insufficient privileges
ORA-06512: at line 13
--//alert.log
Tue Jan 07 10:57:25 2020
online index (re)build cleanup: objn=151619 maxretry=2000 forever=0

--//索引已经建立完成，为什么SYS_JOURNAL_151619 IOT表还在呢？
--//真心讲我惊出一身汗......不过半个小时后我检查发现SYS_JOURNAL_151619 IOT已经删除,我建立的索引也一起消失(删除).
--//不过我已经大概猜测问题在那里,一定是安装的所谓防水墙导致的问题.
--//很久以前我就在生产系统建立一个触发器避免开发drop与truncate表，链接如下：
--// http://blog.itpub.net/267265/viewspace-1802941/ => [20150918]禁止用户truncate以及drop表.txt
--//当时我就忽略2种情况，1.物化视图 2.在线重建索引。
--//可以猜测我前面的问题一定是该防水墙忘记考虑在线重建索引的特殊情况有关，要建立一张IOT表。

1.环境:
> @ ver1
PORT_STRING         VERSION        BANNER
------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试分析：
--//模拟测试，其它环境没有安装该产品，只能找一个"无用"的小表测试看看。
> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       208       8695 64142                    DEDICATED 83783     1200         70 alter system kill session '208,8695' immediate;

> @ 10046on 12
old   1: alter session set events '10046 trace name context forever, level &1'
new   1: alter session set events '10046 trace name context forever, level 12'

Session altered.

> CREATE INDEX XXXXXX_YYY.i_a_ZLXMID ON XXXXXX_YYY.A (ZLXMID) online;
CREATE INDEX XXXXXX_YYY.i_a_ZLXMID ON XXXXXX_YYY.A (ZLXMID) online
                                                 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01031: insufficient privileges
ORA-06512: at line 13
ORA-00604: error occurred at recursive SQL level 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-01031: insufficient privileges
ORA-06512: at line 13

> @ 10046off
Session altered.

2.查看转储文件：
--//alert.log报如下错误：
Wed Jan 08 08:48:03 2020
online index (re)build cleanup: objn=151625 maxretry=2000 forever=0

--//检查转储：
$ grep ^ERROR  /u01/app/oracle/diag/rdbms/zzzz/zzzz1/trace/zzzz1_ora_83783.trc
ERROR #140011057988888:err=1031 tim=1578444483672570
ERROR #140011057973304:err=1031 tim=1578444483672776
ERROR #140011057112832:err=604 tim=1578444483672960
ERROR #140011057597456:err=1031 tim=1578444483698394
ERROR #140011057973304:err=1031 tim=1578444483698569
ERROR #140011057112832:err=604 tim=1578444483698701
ERROR #140011057121184:err=604 tim=1578444483699455


--//检索#140011057121184,对应执行语句。
=====================
PARSING IN CURSOR #140011057121184 len=66 dep=0 uid=5 oct=9 lid=5 tim=1578444482548443 hv=241921903 ad='1090dd180' sqlid='az8dh0w76qvvg'
CREATE INDEX XXXXXX_YYY.i_a_ZLXMID ON XXXXXX_YYY.A (ZLXMID) online
END OF STMT
PARSE #140011057121184:c=4000,e=4217,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=1,plh=1230848158,tim=1578444482548442
=====================
...

--//检索#140011057112832,注意看下划线,对应online建表时的建立的IOT表的drop：
=====================
PARSING IN CURSOR #140011057112832 len=50 dep=1 uid=0 oct=12 lid=0 tim=1578444482883130 hv=1150550614 ad='7f56dd7f1b58' sqlid='a94zrq12980kq'
drop table "XXXXXX_YYY"."SYS_JOURNAL_151625" purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
END OF STMT
PARSE #140011057112832:c=0,e=288,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1578444482883129
=====================
...

--//检索#140011057112832.执行的应该是下划线内容。继续反推。
=====================
PARSING IN CURSOR #140011057973304 len=442 dep=2 uid=131 oct=47 lid=131 tim=1578444483665920 hv=3999344136 ad='1328adbc70' sqlid='14tjukmr629h8'
declare
  -- local variables here
     PRIVS_ERROR exception;         --raise error,if rule exception,will trigger privs_error
     pragma exception_init(PRIVS_ERROR,-1031);
begin
   if upper(ora_dict_obj_name) in('SCHEDULER$_PROGRAM_ARG','SCHEDULER$_JOB_ARG') then
      null;
   else
      execute immediate 'begin topacl.opacl; end;';
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   end if;
exception
   when PRIVS_ERROR then
     raise;
   when others then
     null;
end ddltrigger;
END OF STMT
PARSE #140011057973304:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=1578444483665919
=====================

--//检索#140011057597456.
=====================
PARSING IN CURSOR #140011057597456 len=24 dep=3 uid=131 oct=47 lid=131 tim=1578444483696683 hv=3607719169 ad='bc6b6ae8' sqlid='5rnd92rbhkv81'
begin topacl.opacl; end;
~~~~~~~~~~~~~~~~~~~~~~~~~~
END OF STMT
PARSE #140011057597456:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=0,tim=1578444483696682
.....

--//检索#140011057112832
=====================
PARSING IN CURSOR #140011057112832 len=51 dep=1 uid=0 oct=3 lid=0 tim=1578444482553998 hv=1523794037 ad='a9c9c4a0' sqlid='b1wc53ddd6h3p'
select audit$,options from procedure$ where obj#=:1
END OF STMT
PARSE #140011057112832:c=0,e=146,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1578444482553996
=====================

--//检索#140011057973304,看上面已有。

--//检索#140011057988888
=====================
PARSING IN CURSOR #140011057988888 len=24 dep=3 uid=131 oct=47 lid=131 tim=1578444483666187 hv=3607719169 ad='bc6b6ae8' sqlid='5rnd92rbhkv81'
begin topacl.opacl; end;
END OF STMT
PARSE #140011057988888:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=0,tim=1578444483666186
=====================

--//最后定位在这里,实际上看这段代码就能定位问题，：
=====================
PARSING IN CURSOR #140011029948560 len=264 dep=4 uid=131 oct=47 lid=131 tim=1578444483672031 hv=3329427850 ad='1c3004660' sqlid='fnvv73z3762ca'
begin
              if ( NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'),'127.0.0.1') in ('192.168.xxx.xxx','192.168.yyy.yyy' ....) ) then
                 topacl.evalresult:=true;
              else
                 topacl.evalresult:=false;
              end if;
            end;
END OF STMT
PARSE #140011029948560:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=4,og=1,plh=0,tim=1578444483672030
EXEC #140011029948560:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=1,dep=4,og=1,plh=0,tim=1578444483672137
CLOSE #140011029948560:c=0,e=1,dep=4,type=3,tim=1578444483672170
CLOSE #140011057124224:c=0,e=2,dep=4,type=3,tim=1578444483672216
EXEC #140011057988888:c=4000,e=6315,p=5,cr=13,cu=0,mis=0,r=0,dep=3,og=1,plh=0,tim=1578444483672539
ERROR #140011057988888:err=1031 tim=1578444483672570
CLOSE #140011057988888:c=0,e=13,dep=3,type=0,tim=1578444483672716
EXEC #140011057973304:c=4000,e=6656,p=5,cr=13,cu=2,mis=0,r=0,dep=2,og=1,plh=0,tim=1578444483672758
ERROR #140011057973304:err=1031 tim=1578444483672776
CLOSE #140011057973304:c=0,e=20,dep=2,type=1,tim=1578444483672860
EXEC #140011057112832:c=682897,e=789725,p=79,cr=1533,cu=44,mis=0,r=0,dep=1,og=4,plh=0,tim=1578444483672905
ERROR #140011057112832:err=604 tim=1578444483672960
CLOSE #140011057112832:c=0,e=3,dep=1,type=0,tim=1578444483673629
online index (re)build cleanup: objn=151625 maxretry=2000 forever=0
=====================

> select NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'),'127.0.0.1') from dual ;
NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'),'127.0.0.1')
----------------------------------------------------
127.0.0.1

--//仅仅IP在特定范围内的主机才可能执行这样的操作，昏竟然把127.0.0.1排除在外，而且开发明显忽略一个因素online建立索引结束要有1个drop的操作。
--//也就是这样的维护操作仅仅特定的IP地址发起才能完成。

--//实际上如果这时执行删除索引操作会报：
ORA-08104: this index object 151625 is being online built or rebuilt。

--//等一段时间SYS_JOURNAL_151625 IOT表就删除了，对应建立的索引也消失了。

3.找一个在特定范围内IP的主机测试：
> CREATE INDEX XXXXXX_YYY.i_a_ZLXMID ON XXXXXX_YYY.A (ZLXMID) online;
Index created.

> drop index XXXXXX_YYY.i_a_ZLXMID ;
Index dropped.

--//完全没有问题。这样的防水墙有点那个，特定的IP如果是内部人员很容易获得,仅仅一个IP再知道口令，就可以做....
--//很明显这样的安全产品测试非常不严谨，纯粹骗人的东西。

--//如果没有网络连接，无法干活了吗？我们自己不懂，实施人员不会不知道吗？

--//顺便说一下上的这个东西，CPU消耗很大，我们这里符合至少增加40%，如果你的应用是频繁登录与退出，消耗更大。

请使用浏览器的分享功能分享到微信等