又一例system大量坏块恢复

Posted on 2020 年 03 月 12 日 by 惜分飞

有朋友找到我们,说数据库服务可以启动,但是无法登陆,类似报错

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故障恢复

Posted on 2020 年 03 月 10 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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文件被删除且部分被覆盖恢复案例

Posted on 2020 年 03 月 07 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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被覆盖,最终只能抱着试试看的态度最大限度恢复,属于比较侥幸的恢复成功,再次提醒各位:在数据被误删除之后,应该先保护现场(不要对其分区进行写操作),覆盖的越少,恢复的效果越好.

数字后缀名加密数据库恢复

Posted on 2020 年 03 月 06 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：数字后缀名加密数据库恢复

一个oracle dmp文件被加密破坏的case,加密提示如下

通过工具分析发现该文件前面1M被破坏

通过我们工具对头部损坏的1M数据进行特殊处理,数据直接使用imp命令导入

如果你有各种被类似病毒加密的数据库(oracle,sql server,mysql),我们可以提供专业的恢复支持,实现不给黑客交钱的情况下,数据几乎完美恢复
Tel/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com提供专业的恢复服务.

oracle dul 12 正式发布

Posted on 2020 年 02 月 27 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：oracle dul 12 正式发布

千呼万唤oracle官方dul工具终于发布了12版本,dul 11版本发布参见:oracle dul 11 正式发布

Data UnLoader: 12.0.0.0.5 - Internal Only - on Thu Feb 27 11:27:42 2020
with 64-bit io functions

Copyright (c) 1994 2019 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


Reading USER.dat 87 entries loaded
Reading OBJ.dat 72882 entries loaded and sorted 72882 entries
Reading TAB.dat 2810 entries loaded
Reading COL.dat 90151 entries loaded and sorted 90151 entries
Reading TABPART.dat 107 entries loaded and sorted 107 entries
Reading TABCOMPART.dat 0 entries loaded and sorted 0 entries
Reading TABSUBPART.dat 0 entries loaded and sorted 0 entries
Reading INDPART.dat 124 entries loaded and sorted 124 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 4695 entries loaded
Reading LOB.dat 883 entries loaded
Reading ICOL.dat 7430 entries loaded
Reading COLTYPE.dat 2203 entries loaded
Reading TYPE.dat 2779 entries loaded
Reading ATTRIBUTE.dat 10852 entries loaded
Reading COLLECTION.dat 960 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 11 entries loaded
Reading PROPS.dat 36 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
Found db_id = 3861844098
Found db_name = O11201GB
DUL>
  2  show datafiles;
ts# rf# start   blocks offs open  err file name
  0   1     0   103681    0    1    0 D:\app\XIFENFEI\oradata\o11201gbk/system01.dbf
DUL>

从Compatible参数上看,直接支持到oracle 18版本,具体后续测试