标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
分类目录归档:非常规恢复
oracle drop tablespace 恢复最后一招
客户由于不太熟悉oracle数据库,加入错误的数据文件到一个业务表空间,然后经过一系列操作,最终结果是做了drop tablespace xxx including contents and datafiles操作,导致表空间被删除,而且该数据库未做任何备份和归档.通过检查操作系统和数据库alert日志,确认文件已经从os层面彻底删除
对于这种情况,如果立即保护现场,然后通过反删除软件进行恢复,运气好还可以恢复出来被删除的数据文件,然后再通过dul之类的工具恢复其中数据,这个客户库一直没有关闭,而且尝试各种工具恢复,解决均没有正常恢复出来被删除的几个数据文件.对于这种情况,正常os层面的方法肯定无法恢复了,尝试使用基于block层面技术进行扫描磁盘恢复,结果发现运气还不错,绝大部分block都被找到,参考类似恢复方法:Oracle 数据文件大小为0kb或者文件丢失恢复通过类似分析,找出来绝大多数没有覆盖的block,恢复出来被删除的含数据的file 18,20,21,并通过检测整体恢复效果如下
通过dul工具结合客户提供的表定语以及获取到大表id信息,相互关联,快速恢复客户绝大多数数据,最大限度挽回客户损失.
对于oracle 删除表空间之类的操作,我们可以做到block层面深入恢复,理论上只要你被删除的数据文件在磁盘上还有一个block没有被覆盖,我们都可以把里面的数据恢复出来,最大限度的减少因为这种误操作而引起的损失.如果有类似需求无法自行解决,可以联系我们进行最大限度、最快速度的抢救数据.
电话/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
asm disk被分区,格式化为ext4恢复
有客户因为没有认识到linux中的磁盘被asm使用,对其进行分区并且做成了ext4的文件系统,从history中获取客户操作命令
600 fdisk -l 601 fdisk /dev/sdb 602 mkfs ext4 /dev/sdb1 603 fdisk -l 604 mkfs -t ext4 /dev/sdb1 605 cd / 606 mkdir u01 607 mount /dev/sdb1 /u01 608 df -h
确认磁盘情况,确认sdb直接被asm磁盘使用(asmdisk1)
[grid@racdb3 trace]$ ls -l /dev/asm* brw-rw---- 1 grid asmadmin 8, 16 Sep 30 14:34 /dev/asmdisk1 [grid@racdb3 trace]$ ls -l /dev/sd* brw-rw---- 1 root disk 8, 0 Jul 27 2021 /dev/sda brw-rw---- 1 root disk 8, 1 Jul 27 2021 /dev/sda1 brw-rw---- 1 root disk 8, 2 Jul 27 2021 /dev/sda2 brw-rw---- 1 root disk 8, 16 Sep 30 11:23 /dev/sdb brw-rw---- 1 root disk 8, 17 Sep 30 11:23 /dev/sdb1 brw-rw---- 1 root disk 8, 32 Jul 27 2021 /dev/sdc
asm日志报错
Fri Sep 30 11:31:41 2022 NOTE: SMON starting instance recovery for group DATA domain 1 (mounted) NOTE: SMON skipping disk 0 - no header NOTE: cache initiating offline of disk 0 group DATA NOTE: process _smon_+asm3 (2989) initiating offline of disk 0.3915953109 (DATA_0000) with mask 0x7e in group 1 NOTE: initiating PST update: grp = 1, dsk = 0/0xe968b3d5, mask = 0x6a, op = clear Fri Sep 30 11:31:41 2022 GMON updating disk modes for group 1 at 4 for pid 17, osid 2989 ERROR: Disk 0 cannot be offlined, since diskgroup has external redundancy. ERROR: too many offline disks in PST (grp 1) Fri Sep 30 11:31:41 2022 NOTE: cache dismounting (not clean) group 1/0x34F84324 (DATA) WARNING: Offline for disk DATA_0000 in mode 0x7f failed. Fri Sep 30 11:31:41 2022 NOTE: halting all I/Os to diskgroup 1 (DATA) ERROR: No disks with F1X0 found on disk group DATA NOTE: aborting instance recovery of domain 1 due to diskgroup dismount NOTE: SMON skipping lock domain (1) validation because diskgroup being dismounted
数据库日志报错
Fri Sep 30 11:31:44 2022 Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_lmon_26356.trc: ORA-00202: control file: '+DATA/xifenfei/controlfile/current.256.968794097' ORA-15078: ASM diskgroup was forcibly dismounted Fri Sep 30 11:31:45 2022 Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_ckpt_26388.trc: ORA-00206: error in writing (block 5, # blocks 1) of control file ORA-00202: control file: '+DATA/xifenfei/controlfile/current.257.968794097' ORA-15078: ASM diskgroup was forcibly dismounted ORA-15078: ASM diskgroup was forcibly dismounted ORA-00206: error in writing (block 5, # blocks 1) of control file ORA-00202: control file: '+DATA/xifenfei/controlfile/current.256.968794097' ORA-15078: ASM diskgroup was forcibly dismounted ORA-15078: ASM diskgroup was forcibly dismounted Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_ckpt_26388.trc: ORA-00221: error on write to control file ORA-00206: error in writing (block 5, # blocks 1) of control file ORA-00202: control file: '+DATA/xifenfei/controlfile/current.257.968794097' ORA-15078: ASM diskgroup was forcibly dismounted ORA-15078: ASM diskgroup was forcibly dismounted ORA-00206: error in writing (block 5, # blocks 1) of control file ORA-00202: control file: '+DATA/xifenfei/controlfile/current.256.968794097' ORA-15078: ASM diskgroup was forcibly dismounted ORA-15078: ASM diskgroup was forcibly dismounted CKPT (ospid: 26388): terminating the instance due to error 221
通过kfed 查看asm disk被破坏情况
[root@racdb3 scsi_host]# kfed read /dev/asmdisk1 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 7F4FAAD45400 00000000 00000000 00000000 00000000 [................] Repeat 26 times 7F4FAAD455B0 00000000 00000000 45C222C8 01000000 [.........".E....] 7F4FAAD455C0 FE830001 003FFFFF E9D60000 0000FFFF [......?.........] 7F4FAAD455D0 00000000 00000000 00000000 00000000 [................] Repeat 1 times 7F4FAAD455F0 00000000 00000000 00000000 AA550000 [..............U.] 7F4FAAD45600 00000000 00000000 00000000 00000000 [................] Repeat 223 times KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0] [root@racdb3 scsi_host]# kfed read /dev/asmdisk1 aun=2 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 7F64E77A0400 00000000 00000000 00000000 00000000 [................] Repeat 223 times 7F64E77A1200 000081F9 000181F9 000281F9 000381F9 [................] 7F64E77A1210 000481F9 000C81F9 000D81F9 001881F9 [................] 7F64E77A1220 002881F9 003E81F9 007981F9 00AB81F9 [..(...>...y.....] 7F64E77A1230 013881F9 016C81F9 044581F9 04B081F9 [..8...l...E.....] 7F64E77A1240 061A81F9 0CD081F9 1E8481F9 00000000 [................] 7F64E77A1250 00000000 00000000 00000000 00000000 [................] Repeat 26 times KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0] [root@racdb3 scsi_host]# kfed read /dev/asmdisk1 aun=3 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 7F8D101FF400 00000000 00000000 00000000 00000000 [................] Repeat 223 times 7F8D10200200 000082F9 000182F9 000282F9 000382F9 [................] 7F8D10200210 000482F9 000C82F9 000D82F9 001882F9 [................] 7F8D10200220 002882F9 003E82F9 007982F9 00AB82F9 [..(...>...y.....] 7F8D10200230 013882F9 016C82F9 044582F9 04B082F9 [..8...l...E.....] 7F8D10200240 061A82F9 0CD082F9 1E8482F9 00000000 [................] 7F8D10200250 00000000 00000000 00000000 00000000 [................] Repeat 26 times KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0] [root@racdb3 scsi_host]# kfed read /dev/asmdisk1 aun=4 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 7F142949C400 00000000 00000000 00000000 00000000 [................] Repeat 223 times 7F142949D200 000083F9 000183F9 000283F9 000383F9 [................] 7F142949D210 000483F9 000C83F9 000D83F9 001883F9 [................] 7F142949D220 002883F9 003E83F9 007983F9 00AB83F9 [..(...>...y.....] 7F142949D230 013883F9 016C83F9 044583F9 04B083F9 [..8...l...E.....] 7F142949D240 061A83F9 0CD083F9 1E8483F9 00000000 [................] 7F142949D250 00000000 00000000 00000000 00000000 [................] Repeat 26 times KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0] [root@racdb3 scsi_host]# kfed read /dev/asmdisk1 aun=5 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 7F0615CF6400 00000000 00000000 00000000 00000000 [................] Repeat 255 times KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]
磁盘前几个au被破坏严重.而且相关的备份block都已经损坏,基于这种情况,直接参考:
asm磁盘dd破坏恢复
asm disk header 彻底损坏恢复
asm disk 磁盘部分被清空恢复
通过底层恢复出来相关数据文件,并检测正常
进一步通过au分配列表获恢复redo,ctl等文件
H:\TEMP\asm-ext4\other>dir 驱动器 H 中的卷是 SSD-SX 卷的序列号是 84EB-F434 H:\TEMP\asm-ext4\other 的目录 2022-09-30 21:52 25,165,824 256.dd 2022-09-30 21:52 25,165,824 257.dd 2022-09-30 23:52 52,429,312 258.dd.1 2022-09-30 23:54 52,429,312 259.dd.1 2022-09-30 23:55 52,429,312 260.dd.1 2022-09-30 23:55 52,429,312 261.dd.1 2022-09-30 23:56 52,429,312 270.dd.1 2022-09-30 23:57 52,429,312 271.dd.1 2022-09-30 23:57 52,429,312 272.dd.1 2022-09-30 23:57 52,429,312 273.dd.1 2022-09-30 23:58 52,429,312 274.dd.1 2022-10-01 00:01 52,429,312 275.dd.1 2022-10-01 00:00 52,429,312 276.dd.1 2022-10-01 00:00 52,429,312 277.dd.1 2022-10-01 00:00 52,429,312 278.dd.1 2022-09-30 23:59 52,429,312 279.dd.1 2022-09-30 23:59 52,429,312 280.dd.1 2022-09-30 23:59 52,429,312 281.dd.1
在另外的新机器上尝试恢复库
[oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 1 10:18:58 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 1519898624 bytes Fixed Size 2253464 bytes Variable Size 939527528 bytes Database Buffers 570425344 bytes Redo Buffers 7692288 bytes ORA-00227: corrupt block detected in control file: (block 8, # blocks 1) ORA-00202: control file: '/oradata/256.dd'
控制文件损坏,重建ctl
SQL> CREATE CONTROLFILE REUSE DATABASE "xifenfei" NORESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 226 7 LOGFILE 8 group 7 '/oradata/270.dd.1' size 50M, 9 group 8 '/oradata/272.dd.1' size 50M, 10 group 5 '/oradata/274.dd.1' size 50M, 11 group 6 '/oradata/276.dd.1' size 50M, 12 group 3 '/oradata/278.dd.1' size 50M, 13 group 4 '/oradata/280.dd.1' size 50M, 14 group 1 '/oradata/258.dd.1' size 50M, 15 group 2 '/oradata/260.dd.1' size 50M 16 DATAFILE 17 '/oradata/1', 18 '/oradata/2', 19 '/oradata/3', 20 '/oradata/4', 21 '/oradata/5', 22 '/oradata/6', 23 '/oradata/7', 24 '/oradata/8', 25 '/oradata/9', 26 '/oradata/10', 27 '/oradata/11' 28 CHARACTER SET ZHS16GBK 29 ; Control file created.
尝试open库,报ORA-600 kqfidps_update_stats:2,ORA-600 4194等错误
SQL> recover database; Media recovery complete. SQL> alter database open ; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [kqfidps_update_stats:2], [0x7FFCCBEB3EC0], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [4193], [19319], [l.ok
解决该异常,open数据库成功
SQL> startup mount pfile='/tmp/pfile'; ORACLE instance started. Total System Global Area 1519898624 bytes Fixed Size 2253464 bytes Variable Size 939527528 bytes Database Buffers 570425344 bytes Redo Buffers 7692288 bytes Database mounted. SQL> alter database open; Database altered.
导出数据库,遭遇个别表如下ORA-08103和ORA-01555两种错误,这种是由于个别block在做成文件系统的时候被损坏,底层恢复的时候block被置空导致,对其异常表进行单独处理即可
. . 正在导出表 ALBUM EXP-00056: 遇到 ORACLE 错误 8103 ORA-08103: 对象不再存在 . . 正在导出表 M_PUSH_CONTENT EXP-00056: 遇到 ORACLE 错误 1555 ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小 ORA-22924: 快照太旧
通过上述操作,实现客户数据的恢复,最大限度挽回客户损坏,再次提醒对于asm disk进行了误操作,建议第一时间保护现场(不要有任何的写入操作,可以最大限度恢复数据)
ORA-01558: out of transaction ID’s in rollback segment SYSTEM
有客户数据库启动报ORA-01558: out of transaction ID’s in rollback segment SYSTEM无法open成功,而且对应的备库也无法正常open(报同样错误)
对ORA-01558错误分析
[oracle@iZbp1hx0enix3hix1kvyrxZ ~]$ oerr ora 1558 01558, 00000, "out of transaction ID's in rollback segment %s" // *Cause: All the available transaction id's have been used // *Action: Shutdown the instance and restart using other rollback segment(s), // then drop the rollback segment that has no more transaction id's.
官方对于该错误的解释为由于回滚段的事务ID被使用完,无法再分配从而报ORA-01558,解决方法是重启库使用其他回滚段,并删除这个异常回滚段.对于此次故障的报错,这个方法原则上行不通,因为异常的是SYSTEM里面的rollback回滚段(也就是我们通常说的系统回滚段).通过alert日志分析客户这个故障的前因后果。
正常运行的库,突然报该错误
Fri Jun 03 19:37:36 2022 Thread 1 advanced to log sequence 2766146 (LGWR switch) Current log# 3 seq# 2766146 morcl# 0: /oradata/orclr/group_3.310.962631159 Fri Jun 03 19:37:36 2022 LNS: Standby redo logfile selected for thread 1 sequence 2766146 for destination LOG_ARCHIVE_DEST_2 Fri Jun 03 19:37:37 2022 Archived Log entry 3560157 added for thread 1 sequence 2766145 ID 0xefc56f40 dest 1: Fri Jun 03 19:37:58 2022 Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Fri Jun 03 19:38:13 2022 Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM
这个错误一直报了很长时间,等到有时间窗口,客户安排重启操作
Mon Sep 26 14:10:12 2022 Shutting down instance (immediate) Shutting down instance: further logons disabled License high water mark = 205 All dispatchers and shared servers shutdown Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM ALTER DATABASE CLOSE NORMAL ORA-1558 signalled during: ALTER DATABASE CLOSE NORMAL... Mon Sep 26 14:10:22 2022 Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Mon Sep 26 14:10:33 2022 Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Mon Sep 26 14:10:43 2022 Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Mon Sep 26 14:10:53 2022 Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_smon_9974.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Mon Sep 26 14:10:55 2022 Shutting down instance (abort) License high water mark = 205 USER (ospid: 25049): terminating the instance Instance terminated by USER, pid = 25049
正常shutdown immediate执行失败,直接abort方式关闭库,然后尝试重启库
Mon Sep 26 14:12:16 2022 ARC3 started with pid=44, OS id=25978 ARC1: Archival started ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Thread 1 opened at log sequence 2782827 Current log# 1 seq# 2782827 morcl# 0: /oradata/orclr/group_1.296.962631151 Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Sep 26 14:12:16 2022 SMON: enabling cache recovery Mon Sep 26 14:12:16 2022 NSA2 started with pid=46, OS id=25982 Archived Log entry 3593518 added for thread 1 sequence 2782826 ID 0xefc56f40 dest 1: ARC3: Standby redo logfile selected for thread 1 sequence 2782826 for destination LOG_ARCHIVE_DEST_2 Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_ora_25879.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Errors in file /u01/app/oracle/diag/rdbms/orclr/orclr/trace/orclr_ora_25879.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Error 604 happened during db open, shutting down database USER (ospid: 25879): terminating the instance due to error 604 Instance terminated by USER, pid = 25879 ORA-1092 signalled during: ALTER DATABASE OPEN... opiodr aborting process unknown ospid (25879) as a result of ORA-1092 Mon Sep 26 14:12:17 2022 ORA-1092 : opitsk aborting process
数据库重启失败,报ORA-604和ORA-01558错误.
对于这种情况,对于数据库启动过程进行跟踪确认在UPDATE UNDO$的时候无法分配事务,导致无法继续
PARSING IN CURSOR #139696084476000 len=160 dep=1 uid=0 oct=6 lid=0 tim=1664353484223278 hv=1292341136 ad='6ff1ffa8' sqlid='8vyjutx6hg3wh' update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8, scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1 END OF STMT PARSE #139696084476000:c=5998,e=10708,p=7,cr=53,cu=0,mis=1,r=0,dep=1,og=3,plh=0,tim=1664353484223277 BINDS #139696084476000: Bind#0 oacdty=01 mxl=32(21) mxlc=00 mal=00 scl=00 pre=00 oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0 kxsbbbfp=6ff376da bln=32 avl=21 flg=09 value="_SYSSMU14_3733658264$" Bind#1 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d7a68 bln=24 avl=02 flg=05 value=15 Bind#2 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d7a38 bln=24 avl=03 flg=05 value=336 Bind#3 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d7a00 bln=24 avl=02 flg=05 value=5 Bind#4 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d79d0 bln=24 avl=02 flg=05 value=1 Bind#5 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d79a0 bln=24 avl=03 flg=05 value=5747 Bind#6 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d7970 bln=24 avl=04 flg=05 value=22103 Bind#7 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d7938 bln=24 avl=06 flg=05 value=1026171661 Bind#8 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d7680 bln=24 avl=03 flg=05 value=3399 Bind#9 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d7650 bln=24 avl=02 flg=05 value=2 Bind#10 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d7620 bln=24 avl=02 flg=05 value=5 Bind#11 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d75f0 bln=24 avl=02 flg=05 value=2 Bind#12 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f0d877d7a98 bln=22 avl=02 flg=05 value=14 EXEC #139696084476000:c=1000,e=1713,p=0,cr=1,cu=2,mis=1,r=0,dep=1,og=3,plh=3078630091,tim=1664353484225072 ERROR #139696084476000:err=1558 tim=1664353484225094 STAT #139696084476000 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE UNDO$ (cr=0 pr=0 pw=0 time=5 us)' STAT #139696084476000 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=0 pw=0 time=6 us)' KQRCMT: Write failed with error=604 po=0x6ff375d0 cid=3 diagnostics : cid=3 hash=f2114ab9 flag=2a ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM
既然报了事务ID不足,那对block进行分析,确实几乎都达到了数据库设计的理论最大值
index state cflags wrap# uel scn dba parent-xid nub stmt_num ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0xfffe 0x0026 0x111f.4d598aae 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x01 9 0x00 0xfffe 0x0054 0x111f.4d598a63 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x02 9 0x00 0xfffe 0x001a 0x111f.4d598a77 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x03 9 0x00 0xfffe 0x005c 0x111f.4d598a7d 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x04 9 0x00 0xfffe 0x003d 0x111f.4d598a4d 0x00400185 0x0000.000.00000000 0x00000001 0x00000000 0x05 9 0x00 0xfffe 0x0061 0x111f.4d598a74 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x06 9 0x00 0xfffe 0x0002 0x111f.4d598a76 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x07 9 0x00 0xfffe 0x001b 0x111f.4d598a90 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x08 9 0x00 0xfffe 0x0011 0x111f.4d598a8c 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x09 9 0x00 0xfffe 0x0042 0x111f.4d598a6e 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x0a 9 0x00 0xfffe 0x0003 0x111f.4d598a7c 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x0b 9 0x00 0xfffe 0x000a 0x111f.4d598a7b 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x0c 9 0x00 0xfffe 0x0005 0x111f.4d598a73 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x0d 9 0x00 0xfffe 0x005d 0x111f.4d598a87 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x0e 9 0x00 0xfffe 0x0050 0x111f.4d598a68 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x0f 9 0x00 0xfffe 0x0047 0x111f.4d598a6a 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x10 9 0x00 0xfffe 0x0033 0x111f.4d598a95 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x11 9 0x00 0xfffe 0x0031 0x111f.4d598a8d 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x12 9 0x00 0xfffe 0x0020 0x111f.4d598a81 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x13 9 0x00 0xfffe 0x0014 0x111f.4d598a85 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x14 9 0x00 0xfffe 0x000d 0x111f.4d598a86 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x15 9 0x00 0xfffe 0x0013 0x111f.4d598a83 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x16 9 0x00 0xfffe 0x003b 0x111f.4d598aa4 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x17 9 0x00 0xfffe 0x005b 0x111f.4d598a70 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x18 9 0x00 0xfffe 0x001f 0x111f.4d598a8a 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x19 9 0x00 0xfffe 0x0010 0x111f.4d598a94 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x1a 9 0x00 0xfffe 0x001c 0x111f.4d598a79 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x1b 9 0x00 0xfffe 0x0027 0x111f.4d598a91 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x1c 9 0x00 0xfffe 0x000b 0x111f.4d598a7a 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x1d 9 0x00 0xfffe 0x0012 0x111f.4d598a80 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x1e 9 0x00 0xfffe 0x0045 0x111f.4d598aba 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x1f 9 0x00 0xfffe 0x0008 0x111f.4d598a8b 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x20 9 0x00 0xfffe 0x0015 0x111f.4d598a82 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x21 9 0x00 0xfffe 0x0038 0x111f.4d598ab2 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x22 9 0x00 0xfffd 0x003e 0x111f.4d598ab4 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x23 9 0x00 0xfffe 0x0028 0x111f.4d598a9e 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x24 9 0x00 0xfffd 0x0060 0x111f.4d598a5e 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x25 9 0x00 0xfffe 0x0021 0x111f.4d598ab0 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x26 9 0x00 0xfffe 0x0025 0x111f.4d598aaf 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x27 9 0x00 0xfffe 0x0019 0x111f.4d598a93 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x28 9 0x00 0xfffe 0x0046 0x111f.4d598a9f 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x29 9 0x00 0xfffe 0x0023 0x111f.4d598a9d 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x2a 9 0x00 0xfffe 0x002c 0x111f.4d598a98 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x2b 9 0x00 0xfffe 0x0040 0x111f.4d598aa7 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x2c 9 0x00 0xfffe 0x0030 0x111f.4d598a99 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x2d 9 0x00 0xfffe 0x003c 0x111f.4d598aab 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x2e 9 0x00 0xfffe 0x0056 0x111f.4d598abf 0x00400189 0x0000.000.00000000 0x00000001 0x00000000 0x2f 9 0x00 0xfffe 0x0037 0x111f.4d598aa2 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x30 9 0x00 0xfffe 0x0034 0x111f.4d598a9a 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x31 9 0x00 0xfffe 0x0007 0x111f.4d598a8f 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x32 9 0x00 0xfffe 0x001e 0x111f.4d598ab9 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x33 9 0x00 0xfffe 0x002a 0x111f.4d598a97 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x34 9 0x00 0xfffe 0x0029 0x111f.4d598a9c 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x35 9 0x00 0xfffd 0x005f 0x111f.4d598a5b 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x36 9 0x00 0xfffe 0x0049 0x111f.4d598ac6 0x00400189 0x0000.000.00000000 0x00000001 0x00000000 0x37 9 0x00 0xfffe 0x0016 0x111f.4d598aa3 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x38 9 0x00 0xfffe 0x0022 0x111f.4d598ab3 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x39 9 0x00 0xfffd 0x0058 0x111f.4d598a52 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x3a 9 0x00 0xfffd 0x0048 0x111f.4d598a57 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x3b 9 0x00 0xfffe 0x002b 0x111f.4d598aa5 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x3c 9 0x00 0xfffe 0x0000 0x111f.4d598aac 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x3d 9 0x00 0xfffd 0x0043 0x111f.4d598a4f 0x00400185 0x0000.000.00000000 0x00000001 0x00000000 0x3e 9 0x00 0xfffe 0x003f 0x111f.4d598ab6 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x3f 9 0x00 0xfffe 0x0032 0x111f.4d598ab7 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x40 9 0x00 0xfffe 0x005a 0x111f.4d598aa8 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x41 9 0x00 0xfffe 0x004b 0x111f.4d598abc 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x42 9 0x00 0xfffd 0x0017 0x111f.4d598a6f 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x43 9 0x00 0xfffd 0x004d 0x111f.4d598a50 0x00400185 0x0000.000.00000000 0x00000001 0x00000000 0x44 9 0x00 0xfffe 0x005e 0x111f.4d598ac2 0x00400189 0x0000.000.00000000 0x00000001 0x00000000 0x45 9 0x00 0xfffe 0x0041 0x111f.4d598abb 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x46 9 0x00 0xfffe 0x002f 0x111f.4d598aa0 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x47 9 0x00 0xfffd 0x0059 0x111f.4d598a6b 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x48 9 0x00 0xfffd 0x0052 0x111f.4d598a58 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x49 9 0x00 0xfffe 0xffff 0x111f.4d598ac7 0x00400189 0x0000.000.00000000 0x00000001 0x00000000 0x4a 9 0x00 0xfffd 0x003a 0x111f.4d598a56 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x4b 9 0x00 0xfffe 0x002e 0x111f.4d598abe 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x4c 9 0x00 0xfffd 0x0024 0x111f.4d598a5d 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x4d 9 0x00 0xfffd 0x0039 0x111f.4d598a51 0x00400185 0x0000.000.00000000 0x00000001 0x00000000 0x4e 9 0x00 0xfffd 0x0001 0x111f.4d598a62 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x4f 9 0x00 0xfffd 0x000e 0x111f.4d598a66 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x50 9 0x00 0xfffd 0x000f 0x111f.4d598a69 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x51 9 0x00 0xfffe 0x0036 0x111f.4d598ac5 0x00400189 0x0000.000.00000000 0x00000001 0x00000000 0x52 9 0x00 0xfffd 0x0035 0x111f.4d598a59 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x53 9 0x00 0xfffd 0x004a 0x111f.4d598a55 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x54 9 0x00 0xfffd 0x0055 0x111f.4d598a64 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x55 9 0x00 0xfffd 0x004f 0x111f.4d598a65 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x56 9 0x00 0xfffe 0x0044 0x111f.4d598ac1 0x00400189 0x0000.000.00000000 0x00000001 0x00000000 0x57 9 0x00 0xfffd 0x004e 0x111f.4d598a60 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x58 9 0x00 0xfffd 0x0053 0x111f.4d598a53 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x59 9 0x00 0xfffd 0x0009 0x111f.4d598a6c 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x5a 9 0x00 0xfffd 0x002d 0x111f.4d598aa9 0x00400188 0x0000.000.00000000 0x00000001 0x00000000 0x5b 9 0x00 0xfffd 0x000c 0x111f.4d598a71 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x5c 9 0x00 0xfffd 0x001d 0x111f.4d598a7f 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x5d 9 0x00 0xfffd 0x0018 0x111f.4d598a88 0x00400187 0x0000.000.00000000 0x00000001 0x00000000 0x5e 9 0x00 0xfffe 0x0051 0x111f.4d598ac3 0x00400189 0x0000.000.00000000 0x00000001 0x00000000 0x5f 9 0x00 0xfffd 0x004c 0x111f.4d598a5c 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x60 9 0x00 0xfffd 0x0057 0x111f.4d598a5f 0x00400186 0x0000.000.00000000 0x00000001 0x00000000 0x61 9 0x00 0xfffd 0x0006 0x111f.4d598a75 0x00400187 0x0000.000.00000000 0x00000001 0x00000000
通过bbed修改相关值,规避掉此类问题
m /x 6c000000 offset 6513 m /x 7c000000 offset 6752 m /x 8c000000 offset 7048