标签云
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 2131 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)
- 操作系统 (102)
- 数据库 (1,655)
- DB2 (22)
- MySQL (71)
- Oracle (1,519)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (553)
- Oracle安装升级 (90)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (76)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
- 如何修改集群的公网信息(包括 VIP) (Doc ID 1674442.1)
- 如何在 oracle 集群环境下修改私网信息 (Doc ID 2103317.1)
- ORA-600 [kcvfdb_pdb_set_clean_scn: cleanckpt] 相关bug
- ORA-600 krhpfh_03-1210故障处理
- 19c库启动报ORA-600 kcbzib_kcrsds_1
- DBMS_SESSION.set_context提示ORA-01031问题解决
- redo写丢失导致ORA-600 kcrf_resilver_log_1故障
- 硬件故障导致ORA-01242 ORA-01122等错误
- 200T 数据库非归档无备份恢复
- 利用flashback快速恢复failover 的备库
- [comingback2022@cock.li].eking和[tsai.shen@mailfence.com].faust扩展名勒索病毒数据库可以完美恢复
- opatch auto 出现unable to get oracle owner for 错误
- Oracle 23ai 表和视图的列最多支持到4096个
- 断电引起redo和数据文件不一致故障恢复
- ORA-03113: 通信通道的文件结尾
标签归档:system 坏块
又一例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坏块修复
通过拷贝block实现system文件大量坏块恢复
有朋友找到我,他有客户库大量坏块,需要我们提供支持,因为这个库里面含有大量的存储过程,包等,要求数据要直接导出,不能使用工具挖.
dbv检查system大量坏块
DBVERIFY: Release 11.2.0.4.0 - Production on 星期二 11月 22 17:17:51 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - 开始验证: FILE = h:\oracle\system01.dbf 页 4543 流入 - 很可能是介质损坏 Corrupt block relative dba: 0x004011bf (file 1, block 4543) Fractured block found during dbv: Data in bad block: type: 0 format: 2 rdba: 0x004011bf last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x7641344a check value in block header: 0xb6ff computed block checksum: 0x797 页 4544 标记为损坏 Corrupt block relative dba: 0x004011c0 (file 1, block 4544) Bad header found during dbv: Data in bad block: type: 71 format: 3 rdba: 0x754e362f last change scn: 0x7a37.6d424862 seq: 0x39 flg: 0x32 spare1: 0x35 spare2: 0x32 spare3: 0x3931 consistency value in tail: 0x7638356c check value in block header: 0x4856 block checksum disabled ………… 页 4613 标记为损坏 Corrupt block relative dba: 0x00401205 (file 1, block 4613) Bad header found during dbv: Data in bad block: type: 97 format: 7 rdba: 0x79634449 last change scn: 0x4364.77426a4c seq: 0x41 flg: 0x35 spare1: 0x34 spare2: 0x36 spare3: 0x7734 consistency value in tail: 0x505a4550 check value in block header: 0x434d computed block checksum: 0x6f3f 页 4614 标记为损坏 Corrupt block relative dba: 0x00401206 (file 1, block 4614) Completely zero block found during dbv: ………… 页 5125 标记为损坏 Corrupt block relative dba: 0x00401405 (file 1, block 5125) Completely zero block found during dbv: DBVERIFY - 验证完成 检查的页总数: 124160 处理的页总数 (数据): 90745 失败的页总数 (数据): 0 处理的页总数 (索引): 14417 失败的页总数 (索引): 0 处理的页总数 (其他): 3323 处理的总页数 (段) : 1 失败的总页数 (段) : 0 空的页总数: 15092 标记为损坏的总页数: 583 流入的页总数: 5 加密的总页数 : 0 最高块 SCN : 1417256245 (2.1417256245)
这里比较明显,一共583个坏块,而且是连续坏块(5125-4543+1)
尝试启动数据库
--直接尝试打开数据库 SQL> RECOVER DATABASE; 完成介质恢复。 SQL> alter database open; alter database open * 第 1 行出现错误: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-01578: ORACLE data block corrupted (file # 1, block # 4575) ORA-01110: data file 1: 'H:\ORACLE\SYSTEM01.DBF' 进程 ID: 2572 会话 ID: 85 序列号: 1 --跳过坏块event打开库 SQL> startup mount pfile='h:/oracle/pfile.txt' ORACLE 例程已经启动。 Total System Global Area 2137886720 bytes Fixed Size 2282944 bytes Variable Size 520096320 bytes Database Buffers 1610612736 bytes Redo Buffers 4894720 bytes 数据库装载完毕。 SQL> show parameter event; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ event string 43810 trace name context forev er, level 10, 10231 trace name context forever, level 10, 10 232 trace name context forever , level 10, 10233 trace name c ontext forever, level 10, 1004 1 trace name context forever, level 10 xml_db_events string enable SQL> alter database open; alter database open * 第 1 行出现错误: ORA-01113: 文件 1 需要介质恢复 ORA-01110: 数据文件 1: 'H:\ORACLE\SYSTEM01.DBF' SQL> recover database; 完成介质恢复。 SQL> alter database open; alter database open * 第 1 行出现错误: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [kokiasg1], [], [], [], [], [], [], [], [], [], [], [] 进程 ID: 9316 会话 ID: 4 序列号: 3 --upgrade方式打开数据库 SQL> startup mount pfile='h:/oracle/pfile.txt' ORACLE 例程已经启动。 Total System Global Area 2137886720 bytes Fixed Size 2282944 bytes Variable Size 520096320 bytes Database Buffers 1610612736 bytes Redo Buffers 4894720 bytes 数据库装载完毕。 SQL> alter database open upgrade 2 ; alter database open upgrade * 第 1 行出现错误: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [kokiasg1], [], [], [], [], [], [], [], [], [], [], [] 进程 ID: 7976 会话 ID: 4 序列号: 3
至此数据库在这种坏块情况下,正常打开相当渺茫,因为报错的这些block 都是非常靠前的,也就是说这个里面很多块在数据库创建好的时候就已经在了(特别是通过模板创建的数据库,这些部分很可能都是固定的),考虑使用其他库的block来替代这些坏块,然后尝试打开库
修复坏块
[oracle@app101-20 ~]$ dd if=/Data/oracle/oradata/txlhdb/system01.dbf of=/tmp/1.dbf skip=4543 bs=8192 count=583 583+0 records in 583+0 records out 4775936 bytes (4.8 MB) copied, 0.0533578 s, 89.5 MB/s H:\oracle>dd if=d:/temp/1.dbf of=h:\oracle\system01.dbf seek=4543 bs=8192 count=583 conv=notrun rawwrite dd for windows version 0.6beta3. Written by John Newbigin <jn@it.swin.edu.au> This program is covered by terms of the GPL Version 2. notrun 583+0 records in 583+0 records out H:\oracle>dbv file=system01.dbf DBVERIFY: Release 11.2.0.4.0 - Production on 星期二 11月 22 20:17:51 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - 开始验证: FILE = H:\ORACLE\SYSTEM01.DBF DBVERIFY - 验证完成 检查的页总数: 124160 处理的页总数 (数据): 90761 失败的页总数 (数据): 0 处理的页总数 (索引): 14479 失败的页总数 (索引): 0 处理的页总数 (其他): 3393 处理的总页数 (段) : 1 失败的总页数 (段) : 0 空的页总数: 15527 标记为损坏的总页数: 0 流入的页总数: 0 加密的总页数 : 0 最高块 SCN : 295310052 (11.295310052)
再次尝试打开数据库
C:\Users\XIFENFEI>sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on 星期二 11月 22 20:18:19 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. 已连接到空闲例程。 SQL> startup mount pfile='h:/oracle/pfile.txt'; ORACLE 例程已经启动。 Total System Global Area 2137886720 bytes Fixed Size 2282944 bytes Variable Size 520096320 bytes Database Buffers 1610612736 bytes Redo Buffers 4894720 bytes 数据库装载完毕。 SQL> recover database; 完成介质恢复。 SQL> alter database open; 数据库已更改。
后续错误ORA-04023处理
C:\Users\XIFENFEI>exp "'/ as sysdba'" owner=XIFENFEI file=d:/full_xff.dmp lo g=d:/full_xff.log FEEDBACK=10000 COMPRESS=NO BUFFER=102400000 STATISTICS=none Export: Release 11.2.0.4.0 - Production on 星期二 11月 22 20:20:27 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options EXP-00056: 遇到 ORACLE 错误 4023 ORA-04023: 无法验证或授权对象 SELECT xdb_uid FROM SYS.EXU9XDBUID EXP-00000: 导出终止失败
数据库所有视图无法查询,通过直接对基表user$,obj$,view$等表查询出来视图信息,然后直接编译,然后数据可以完美导出,完成本次恢复
SQL> select 'alter view '||b.name||'.'||c.name||' compile;' 2 from view$ a,user$ b,obj$ c 3 where a.obj#=c.obj# 4 and c.owner#=b.user#;