标签云
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,589)
- DB2 (22)
- MySQL (70)
- Oracle (1,459)
- 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备份恢复 (526)
- Oracle安装升级 (83)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (13)
- PostgreSQL恢复 (3)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- 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 19C 备库DML重定向—DML Redirection
- ORA-01595/ORA-600 4194处理
- 从ORA-00283 ORA-16433报错开始恢复
- 近期又遇到ORA-600 16703和ORA-702故障
- RECOVER_YOUR_DATA勒索恢复
- ORA-01033: ORACLE initialization or shutdown in progress 故障处理
- Oracle 19c/21c最新patch信息-202401
- 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
- ORA-600 kcrf_resilver_log_1故障处理
月归档:三月 2020
又一例system大量坏块恢复
有朋友找到我们,说数据库服务可以启动,但是无法登陆,类似报错
C:\Users\XIFENFEI>D:\app\XIFENFEI\product\11.2.0.1\dbhome_2\bin\sqlplus / as sys dba SQL*Plus: Release 11.2.0.1.0 Production on 星期四 3月 12 15:04:32 2020 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-01075: 您现在已登录 请输入用户名: ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 请输入用户名: ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus
通过分析发现数据库启动报错(未正常open成功)
C:\Users\XIFENFEI>D:\app\XIFENFEI\product\11.2.0.1\dbhome_2\bin\sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期四 3月 12 14:58:28 2020 Copyright (c) 1982, 2010, Oracle. All rights reserved. 已连接到空闲例程。 SQL> startup mount pfile='F:\pfile.txt'; ORACLE 例程已经启动。 Total System Global Area 3307048960 bytes Fixed Size 2180264 bytes Variable Size 1811942232 bytes Database Buffers 1476395008 bytes Redo Buffers 16531456 bytes 数据库装载完毕。 SQL> alter database open; alter database open * 第 1 行出现错误: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 48396) ORA-01110: 数据文件 1: 'F:\ORADATA\SYSTEM01.DBF'
数据库没有正常启动成功的原因是由于system文件有坏块导致,通过dbv检查system文件发现有大量连续坏块
DBVERIFY: Release 11.2.0.1.0 - Production on 星期四 3月 12 19:12:34 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. DBVERIFY - 开始验证: FILE = F:\ORADATA\SYSTEM01.DBF 页 48064 流入 - 很可能是介质损坏 Corrupt block relative dba: 0x0040bbc0 (file 1, block 48064) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0040bbc0 last change scn: 0x0000.0006f69c seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x43455474 check value in block header: 0x3893 computed block checksum: 0xadfa ………… 页 48412 标记为损坏 Corrupt block relative dba: 0x0040bd1c (file 1, block 48412) Bad header found during dbv: Data in bad block: type: 36 format: 2 rdba: 0x6dce856d last change scn: 0xfc44.d24c936f seq: 0xdc flg: 0x3b spare1: 0x9c spare2: 0x92 spare3: 0xcf67 consistency value in tail: 0x43455474 check value in block header: 0x2598 block checksum disabled DBVERIFY - 验证完成 检查的页总数: 249600 处理的页总数 (数据): 209467 失败的页总数 (数据): 0 处理的页总数 (索引): 13616 失败的页总数 (索引): 0 处理的页总数 (其他): 3369 处理的总页数 (段) : 1 失败的总页数 (段) : 0 空的页总数: 22799 标记为损坏的总页数: 349 流入的页总数: 1 加密的总页数 : 0 最高块 SCN : 84123103 (0.84123103)
数据库alert日志信息
Thu Mar 12 14:52:20 2020 SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK Hex dump of (file 1, block 48403) in trace file d:\app\xifenfei\diag\rdbms\qdbdc\o11201gbk\trace\o11201gbk_ora_9480.trc Corrupt block relative dba: 0x0040bd13 (file 1, block 48403) Bad header found during multiblock buffer read Data in bad block: type: 36 format: 2 rdba: 0x6dce856d last change scn: 0xfc44.d24c936f seq: 0xdc flg: 0x3b spare1: 0x9c spare2: 0x92 spare3: 0xcf67 consistency value in tail: 0x43455474 check value in block header: 0x2598 block checksum disabled Reading datafile 'F:\ORADATA\SYSTEM01.DBF' for corruption at rdba: 0x0040bd13 (file 1, block 48403) Reread (file 1, block 48403) found same corrupt data Hex dump of (file 1, block 48404) in trace file d:\app\xifenfei\diag\rdbms\qdbdc\o11201gbk\trace\o11201gbk_ora_9480.trc Corrupt block relative dba: 0x0040bd14 (file 1, block 48404) Corrupt Block Found Bad header found during multiblock buffer read TSN = 0, TSNAME = SYSTEM Data in bad block: RFN = 1, BLK = 48403, RDBA = 4242707 type: 36 format: 2 rdba: 0x6dce856d last change scn: 0xfc44.d24c936f seq: 0xdc flg: 0x3b spare1: 0x9c spare2: 0x92 spare3: 0xcf67 consistency value in tail: 0x43455474 check value in block header: 0x2598 block checksum disabled Reading datafile 'F:\ORADATA\SYSTEM01.DBF' for corruption at rdba: 0x0040bd14 (file 1, block 48404) Reread (file 1, block 48404) found same corrupt data Errors in file d:\app\xifenfei\diag\rdbms\qdbdc\o11201gbk\trace\o11201gbk_ora_9480.trc (incident=3784): ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 48404) ORA-01110: 数据文件 1: 'F:\ORADATA\SYSTEM01.DBF' Incident details in: d:\app\xifenfei\diag\rdbms\qdbdc\o11201gbk\incident\incdir_3784\o11201gbk_ora_9480_i3784.trc OBJN = 36, OBJD = 36, OBJECT = I_OBJ1, SUBOBJECT = SEGMENT OWNER = SYS, SEGMENT TYPE = Index Segment Corrupt Block Found TSN = 0, TSNAME = SYSTEM RFN = 1, BLK = 48404, RDBA = 4242708 OBJN = 36, OBJD = 36, OBJECT = I_OBJ1, SUBOBJECT = SEGMENT OWNER = SYS, SEGMENT TYPE = Index Segment Errors in file d:\app\xifenfei\diag\rdbms\qdbdc\o11201gbk\trace\o11201gbk_ora_9480.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 48404) ORA-01110: 数据文件 1: 'F:\ORADATA\SYSTEM01.DBF' Errors in file d:\app\xifenfei\diag\rdbms\qdbdc\o11201gbk\trace\o11201gbk_ora_9480.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 48404) ORA-01110: 数据文件 1: 'F:\ORADATA\SYSTEM01.DBF' Error 604 happened during db open, shutting down database USER (ospid: 9480): terminating the instance due to error 604
这里可以看出来数据库不能正常启动的原因,主要是由于I_OBJ1(obj$表的index)刚好被损坏,导致数据库无法,通过分析定位确定是如下sql导致启动失败
Dump continued from file: d:\app\xifenfei\diag\rdbms\qdbdc\o11201gbk\trace\o11201gbk_ora_9480.trc ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 48404) ORA-01110: 数据文件 1: 'F:\ORADATA\SYSTEM01.DBF' ========= Dump for incident 3784 (ORA 1578) ======== *** 2020-03-12 14:52:20.614 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=cq514nkrp38hv) ----- select distinct d.p_obj#,d.p_timestamp from sys.dependency$ d, obj$ o where d.p_obj#>=:1 and d.d_obj#=o.obj# and o.status not in (5,6)
通过对其i_obj1损坏block进行修复,数据库正启动成功
C:\Users\XIFENFEI>D:\app\XIFENFEI\product\11.2.0.1\dbhome_2\bin\sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期四 3月 12 15:05:48 2020 Copyright (c) 1982, 2010, Oracle. All rights reserved. 已连接到空闲例程。 SQL> startup pfile='f:/pfile.txt' mount; ORACLE 例程已经启动。 Total System Global Area 3307048960 bytes Fixed Size 2180264 bytes Variable Size 1811942232 bytes Database Buffers 1476395008 bytes Redo Buffers 16531456 bytes 数据库装载完毕。 SQL> alter database open; 数据库已更改。
尝试导出数据
C:\Windows\system32>exp system/oracle owner=gis_sys file=f:/gis_sys.dmp FEEDBACK =10000 COMPRESS=NO BUFFER=102400000 STATISTICS=none Export: Release 11.2.0.1.0 - Production on 星期四 3月 12 15:46:19 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 即将导出指定的用户... . 正在导出 pre-schema 过程对象和操作 . 正在导出用户 GIS_SYS 的外部函数库名 . 导出 PUBLIC 类型同义词 EXP-00008: 遇到 ORACLE 错误 604 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01410: 无效的 ROWID EXP-00000: 导出终止失败
分析trace文件
*** SESSION ID:(192.3) 2020-03-12 15:05:36.132 OBJD MISMATCH typ=6, seg.obj=18, diskobj=224, dsflg=100100, dsobj=18, tid=18, cls=1 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01410: 无效的 ROWID ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01410: 无效的 ROWID
由于obj id为18(obj$)的对象和对应的数据库中实际block存储的表的block为224(aud$)不匹配,从而出现该错误,通过分析是由于i_obj1记录错误导致该问题,通过修复该记录之后,数据实现完美导出.
类似system文件坏块案例有:
通过拷贝block实现system文件大量坏块恢复
记录一次system表空间坏块(ORA-01578)数据库恢复
SYSTEM表空间坏块恢复—C_TS#对象坏块恢复(file 1 block 60)
file 1 block 128 corrupted/坏块恢复—system rollback坏块修复
Oracle 19c故障恢复
有客户找到我们,他们的oracle 19c数据库由于异常断电,导致启动异常,经过一系列恢复之后,依旧无法解决问题,请求我们给予支持.通过我们的Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check),获取数据库当前信息如下:
数据库版本为19C并且安装了19.5.0.0.191015 (30125133)补丁
数据库使用pdb
数据库启动成功后,一会就crash掉
2020-03-10T01:44:41.018032+08:00 Pluggable database RACBAK opened read write 2020-03-10T01:44:41.018996+08:00 Pluggable database RAC opened read write 2020-03-10T01:44:51.244050+08:00 Completed: ALTER PLUGGABLE DATABASE ALL OPEN Starting background process CJQ0 Completed: ALTER DATABASE OPEN 2020-03-10T01:44:51.317085+08:00 CJQ0 started with pid=224, OS id=32581 2020-03-10T01:44:56.067043+08:00 Errors in file /opt/oracle/diag/rdbms/XFF/XFF/trace/XFF_j001_32588.trc (incident=1095281) (PDBNAME=RAC): ORA-00600: internal error code, arguments: [4193], [27733], [27754], [], [], [], [], [], [], [], [], [] RAC(4):Incident details in: /opt/oracle/diag/rdbms/XFF/XFF/incident/incdir_1095281/XFF_j001_32588_i1095281.trc RAC(4):Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. 2020-03-10T01:44:56.073112+08:00 RAC(4):***************************************************************** RAC(4):An internal routine has requested a dump of selected redo. RAC(4):This usually happens following a specific internal error, when RAC(4):analysis of the redo logs will help Oracle Support with the RAC(4):diagnosis. RAC(4):It is recommended that you retain all the redo logs generated (by RAC(4):all the instances) during the past 12 hours, in case additional RAC(4):redo dumps are required to help with the diagnosis. RAC(4):***************************************************************** 2020-03-10T01:44:56.079228+08:00 Errors in file /opt/oracle/diag/rdbms/XFF/XFF/trace/XFF_j002_32590.trc (incident=1095289) (PDBNAME=RAC): ORA-00600: internal error code, arguments: [4193], [2633], [2638], [], [], [], [], [], [], [], [], [] RAC(4):Incident details in: /opt/oracle/diag/rdbms/XFF/XFF/incident/incdir_1095289/XFF_j002_32590_i1095289.trc RAC(4):Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. 2020-03-10T01:44:56.085068+08:00 RAC(4):***************************************************************** RAC(4):An internal routine has requested a dump of selected redo. RAC(4):This usually happens following a specific internal error, when RAC(4):analysis of the redo logs will help Oracle Support with the RAC(4):diagnosis. RAC(4):It is recommended that you retain all the redo logs generated (by RAC(4):all the instances) during the past 12 hours, in case additional RAC(4):redo dumps are required to help with the diagnosis. RAC(4):***************************************************************** 2020-03-10T01:44:56.115765+08:00 Errors in file /opt/oracle/diag/rdbms/XFF/XFF/trace/XFF_j004_32594.trc (incident=1095305) (PDBNAME=RAC): ORA-00600: internal error code, arguments: [4193], [63532], [63537], [], [], [], [], [], [], [], [], [] RAC(4):Incident details in: /opt/oracle/diag/rdbms/XFF/XFF/incident/incdir_1095305/XFF_j004_32594_i1095305.trc RAC(4):Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. 2020-03-10T01:46:48.202213+08:00 RAC(4):Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0 RAC(4): Mem# 0: /opt/oracle/oradata/XFF/redo02.log RAC(4):Block recovery completed at rba 0.0.0, scn 0x0000000d3675e48e RAC(4):DDE: Problem Key 'ORA 600 [4193]' was completely flood controlled (0x6) Further messages for this problem key will be suppressed for up to 10 minutes 2020-03-10T01:46:48.384040+08:00 Errors in file /opt/oracle/diag/rdbms/XFF/XFF/trace/XFF_clmn_31741.trc: ORA-00600: internal error code, arguments: [4193], [27733], [27754], [], [], [], [], [], [], [], [], [] Errors in file /opt/oracle/diag/rdbms/XFF/XFF/trace/XFF_clmn_31741.trc (incident=1093505) (PDBNAME=CDB$ROOT): ORA-501 [] [] [] [] [] [] [] [] [] [] [] [] Incident details in: /opt/oracle/diag/rdbms/XFF/XFF/incident/incdir_1093505/XFF_clmn_31741_i1093505.trc 2020-03-10T01:46:49.264624+08:00 USER (ospid: 31741): terminating the instance due to ORA error 501 2020-03-10T01:46:49.280664+08:00 System state dump requested by (instance=1, osid=31741 (CLMN)), summary=[abnormal instance termination]. System State dumped to trace file /opt/oracle/diag/rdbms/XFF/XFF/trace/XFF_diag_31759.trc 2020-03-10T01:46:53.156926+08:00 ORA-00501: CLMN process terminated with error 2020-03-10T01:46:53.157103+08:00 Errors in file /opt/oracle/diag/rdbms/XFF/XFF/trace/XFF_diag_31759.trc: ORA-00501: CLMN process terminated with error 2020-03-10T01:46:53.157211+08:00 Dumping diagnostic data in directory=[cdmp_20200310014649], requested by (instance=1, osid=31741 (CLMN)), summary=[abnormal instance termination].
通过报错信息判断,数据库open之后(特别是pdb 4 open之后),开始报ORA-600 4193错误.然后由于CLMN进程异常,最后数据库crash.对于这类故障,因为使用的pdb,而且是由于pdb的undo异常导致数据库启动之后crash,可以通过对于pdb进行特殊处理,从而实现数据库启动之后不再crash.
oracle文件被删除且部分被覆盖恢复案例
有客户数据库异常,让我们对其进行分析,判断是否可以恢复,让客户通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)收集数据库信息,发现有三个数据文件异常
通过和客户确认,大概情况是这样的:由于/opt目录满了,客户把/opt/oracle整体迁移到/home目录中,然后通过link目录的方式实现迁移,但是在迁移之前把/home/oracle中的所有数据文件给rm掉了,然后把/opt中的数据拷贝到/home/中,在启动数据库的时候提示/home/oracle/JXWR.dbf文件丢失,然后又人工创建了一个该文件,从而出现了上述的三个文件异常(其实删除的数据库文件有十几个,涉及该库的有三个,客户只要恢复JXWR表空间数据即可).对于这种情况,有可能有覆盖的风险,让客户提供空间对现有/home 分区进行镜像,通过工具分析镜像文件
发现需要恢复文件大小/时间均不对,查看内容发现是oracle的审计trace,证明该文件对应的位置已经有覆盖,对于这样的情况,无法从os层面反删除进行恢复,考虑通过oracle碎片层面进行处理,对其分析发现大量block依旧存在(情况有点复杂,因为该目录涉及多个库,通过分析确认相关段为该数据库文件)
检查重组出来的数据文件效果
检查效果比较乐观,因为根据这样的情况,丢失15%左右的block算是非常理想的效果,然后通过oracle dul恢复客户需要的数据
完成数据库恢复任务.
这次的恢复效果不是太好主要就是由于客户删除文件之后,对被删除文件所在分区进行了大量写操作导致不少数据库block被覆盖,最终只能抱着试试看的态度最大限度恢复,属于比较侥幸的恢复成功,再次提醒各位:在数据被误删除之后,应该先保护现场(不要对其分区进行写操作),覆盖的越少,恢复的效果越好.
发表在 非常规恢复
评论关闭