标签云
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,597)
- 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 (17)
- PostgreSQL恢复 (5)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- 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 19C 备库DML重定向—DML Redirection
月归档:十二月 2015
type为TEMPORARY,name为file#.block#对象重现和清理
在数据库的使用中你可能注意到了,在某些情况下,可能出现某些segment的type为TEMPORARY,名字为file#.block#的格式的对象.这里我通过试验重现该对象并且给出清理方法
创建表空间/用户
SQL> create tablespace test datafile 2 '/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf' 3 size 128M autoextend on; Tablespace created. SQL> create user chf identified by oracle; User created. SQL> grant dba to chf; Grant succeeded.
创建模拟对象
本次通过一个index对象经过一些列操作使其变为我们需要的TEMPORARY对象
SQL> conn chf/oracle Connected. SQL> create table t_xifenfei tablespace test 2 as select * from dba_objects; Table created. SQL> create index idx_t_xifenfei on t_xifenfei(object_id) tablespace test; Index created. SQL> select header_file,header_block from DBA_SEGMENTS 2 where segment_name='IDX_T_XIFENFEI' AND OWNER='CHF'; HEADER_FILE HEADER_BLOCK ----------- ------------ 5 267
破坏segment header
这里通过dd 把该block重置为空块,然后rman检查坏块,证明我们处理正常把index的segment header弄成了空块
SQL> select name from v$datafile where file#=5; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf SQL> conn / as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. [oracle@xffdbrh5 ~]$ dd if=/dev/zero >of=/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf >bs=8192 count=1 seek=267 conv=notrunc 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 0.000100548 seconds, 81.5 MB/s [oracle@xffdbrh5 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 21 16:30:56 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 901775360 bytes Fixed Size 2024944 bytes Variable Size 138414608 bytes Database Buffers 759169024 bytes Redo Buffers 2166784 bytes Database mounted. Database opened. [oracle@xffdbrh5 ~]$ rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Mon Dec 21 16:33:00 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORA10G (DBID=4205072872) RMAN> backup validate check logical datafile 5; Starting backup at 21-DEC-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=144 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00005 name=/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 21-DEC-15 RMAN> exit Recovery Manager complete. [oracle@xffdbrh5 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 21 16:33:50 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options SQL> select * from V$DATABASE_BLOCK_CORRUPTION ; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 5 267 1 0 ALL ZERO
index segment header异常
index异常后,通过rebuild报ora-08103错误
SQL> alter index chf.idx_t_xifenfei rebuild; alter index chf.idx_t_xifenfei rebuild * ERROR at line 1: ORA-08103: object no longer exists
重现TEMPORARY对象
通过删除index,然后发现了我们久违的TEMPORARY类型的对象出现
SQL> drop index chf.idx_t_xifenfei; Index dropped. SQL> col SEGMENT_NAME for a20 SQL> select segment_name,segment_type from user_segments; SEGMENT_NAME SEGMENT_TYPE -------------------- ------------------ T_XIFENFEI TABLE 5.267 TEMPORARY
清理TEMPORARY对象
SQL> CONN / AS SYSDBA Connected. SQL> exec dbms_space_admin.segment_corrupt('TEST',5,267); PL/SQL procedure successfully completed. SQL> exec dbms_space_admin.segment_drop_corrupt('TEST',5,267); PL/SQL procedure successfully completed. SQL> select segment_name,segment_type from DBA_SEGMENTS WHERE OWNER='CHF'; SEGMENT_NAME SEGMENT_TYPE -------------------- ------------------ T_XIFENFEI TABLE
通过试验证明:当segment header异常,并且删除该对象,就会出现type为TEMPORARY,名字为file#.block#的格式的对象.我们可以通过dbms_space_admin包处理该类异常对象,让他们彻底从数据库中清除掉
oracle asm disk格式化恢复—格式化为ntfs文件系统
接到网友请求,由于操作人员粗心把asm disk的磁盘映射到另外的机器上,并且格式化为了win ntfs文件系统,导致asm 磁盘组异常,数据库无法使用
asm 日志报ORA-27072错
Mon Nov 30 12:00:13 2015 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27070: async read/write failed OSD-04008: WriteFile() 失败, 无法写入文件 O/S-Error: (OS 21) 设备未就绪。 WARNING: IO Failed. group:1 disk(number.incarnation):0.0xf0f0bbfb disk_path:\\.\ORCLDISKDATA0 AU:1 disk_offset(bytes):2093056 io_size:4096 operation:Write type:synchronous result:I/O error process_id:868 WARNING: disk 0.4042308603 (DATA_0000) not responding to heart beat ERROR: too many offline disks in PST (grp 1) WARNING: Disk DATA_0000 in mode 0x7f will be taken offline Mon Nov 30 12:00:13 2015 NOTE: process 576:37952 initiating offline of disk 0.4042308603 (DATA_0000) with mask 0x7e in group 1 WARNING: Disk DATA_0000 in mode 0x7f is now being taken offline NOTE: initiating PST update: grp = 1, dsk = 0/0xf0f0bbfb, mode = 0x15 kfdp_updateDsk(): 5 kfdp_updateDskBg(): 5 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):1.0xf0f0bbfc disk_path:\\.\ORCLDISKDATA1 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):1.0xf0f0bbfc disk_path:\\.\ORCLDISKDATA1 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):2.0xf0f0bbfd disk_path:\\.\ORCLDISKDATA2 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):2.0xf0f0bbfd disk_path:\\.\ORCLDISKDATA2 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):3.0xf0f0bbfe disk_path:\\.\ORCLDISKDATA3 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):3.0xf0f0bbfe disk_path:\\.\ORCLDISKDATA3 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):4.0xf0f0bbff disk_path:\\.\ORCLDISKDATA4 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):4.0xf0f0bbff disk_path:\\.\ORCLDISKDATA4 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):6.0xf0f0bc01 disk_path:\\.\ORCLDISKDATA6 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):6.0xf0f0bc01 disk_path:\\.\ORCLDISKDATA6 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):7.0xf0f0bc02 disk_path:\\.\ORCLDISKDATA7 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc: ORA-27072: File I/O error WARNING: IO Failed. group:1 disk(number.incarnation):7.0xf0f0bc02 disk_path:\\.\ORCLDISKDATA7 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous result:I/O error process_id:868 ERROR: no PST quorum in group: required 1, found 0 WARNING: Disk DATA_0000 in mode 0x7f offline aborted Mon Nov 30 12:00:14 2015 SQL> alter diskgroup DATA dismount force /* ASM SERVER */ NOTE: cache dismounting (not clean) group 1/0xBB404B03 (DATA) Mon Nov 30 12:00:14 2015 NOTE: halting all I/Os to diskgroup DATA Mon Nov 30 12:00:14 2015 NOTE: LGWR doing non-clean dismount of group 1 (DATA) NOTE: LGWR sync ABA=367.7265 last written ABA 367.7265 NOTE: cache dismounted group 1/0xBB404B03 (DATA) kfdp_dismount(): 6 kfdp_dismountBg(): 6 NOTE: De-assigning number (1,0) from disk (\\.\ORCLDISKDATA0) NOTE: De-assigning number (1,1) from disk (\\.\ORCLDISKDATA1) NOTE: De-assigning number (1,2) from disk (\\.\ORCLDISKDATA2) NOTE: De-assigning number (1,3) from disk (\\.\ORCLDISKDATA3) NOTE: De-assigning number (1,4) from disk (\\.\ORCLDISKDATA4) NOTE: De-assigning number (1,5) from disk (\\.\ORCLDISKDATA5) NOTE: De-assigning number (1,6) from disk (\\.\ORCLDISKDATA6) NOTE: De-assigning number (1,7) from disk (\\.\ORCLDISKDATA7) SUCCESS: diskgroup DATA was dismounted NOTE: cache deleting context for group DATA 1/-1153414397 SUCCESS: alter diskgroup DATA dismount force /* ASM SERVER */ ERROR: PST-initiated MANDATORY DISMOUNT of group DATA
这里的asm日志很明显由于asm disk无法正常访问,报ORA-27072错误,磁盘组强制dismount.
分析磁盘情况
通过与客户沟通,确定从I到O本为asm disk 被格式化为了NTFS文件系统的磁盘,结合asmtool分析可以发现还有一个asm disk没有格式化掉,该磁盘组中一个共有8个磁盘格式化掉了7个.
通过kfed分析磁盘信息
C:\Users\Administrator>kfed read '\\.\J:' kfbh.endian: 235 ; 0x000: 0xeb kfbh.hard: 82 ; 0x001: 0x52 kfbh.type: 144 ; 0x002: *** Unknown Enum *** kfbh.datfmt: 78 ; 0x003: 0x4e kfbh.block.blk: 542328404 ; 0x004: T=0 NUMB=0x20534654 kfbh.block.obj: 2105376 ; 0x008: TYPE=0x0 NUMB=0x2020 kfbh.check: 2050 ; 0x00c: 0x00000802 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 63488 ; 0x014: 0x0000f800 kfbh.spare1: 16711743 ; 0x018: 0x00ff003f kfbh.spare2: 2048 ; 0x01c: 0x00000800 ERROR!!!, failed to get the oracore error message C:\Users\Administrator>kfed read '\\.\J:' blkn=2 kfbh.endian: 70 ; 0x000: 0x46 kfbh.hard: 73 ; 0x001: 0x49 kfbh.type: 76 ; 0x002: *** Unknown Enum *** kfbh.datfmt: 69 ; 0x003: 0x45 kfbh.block.blk: 196656 ; 0x004: T=0 NUMB=0x30030 kfbh.block.obj: 33563364 ; 0x008: TYPE=0x0 NUMB=0x22e4 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 65537 ; 0x010: 0x00010001 kfbh.fcn.wrap: 65592 ; 0x014: 0x00010038 kfbh.spare1: 416 ; 0x018: 0x000001a0 kfbh.spare2: 1024 ; 0x01c: 0x00000400 ERROR!!!, failed to get the oracore error message C:\Users\Administrator>kfed read '\\.\J:' blkn=256 kfbh.endian: 1 ; 0x000: 0x01 kfbh.hard: 130 ; 0x001: 0x82 kfbh.type: 13 ; 0x002: KFBTYP_PST_NONE kfbh.datfmt: 1 ; 0x003: 0x01 kfbh.block.blk: 2147483648 ; 0x004: T=1 NUMB=0x0 kfbh.block.obj: 2147483654 ; 0x008: TYPE=0x8 NUMB=0x6 kfbh.check: 17662471 ; 0x00c: 0x010d8207 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 ERROR!!!, failed to get the oracore error message C:\Users\Administrator>kfed read '\\.\J:' blkn=510 kfbh.endian: 1 ; 0x000: 0x01 kfbh.hard: 130 ; 0x001: 0x82 kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD kfbh.datfmt: 1 ; 0x003: 0x01 kfbh.block.blk: 254 ; 0x004: T=0 NUMB=0xfe kfbh.block.obj: 2147483654 ; 0x008: TYPE=0x8 NUMB=0x6 kfbh.check: 717599272 ; 0x00c: 0x2ac5b228 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 kfdhdb.driver.provstr: ORCLDISKDATA6 ; 0x000: length=13 kfdhdb.driver.reserved[0]: 1096040772 ; 0x008: 0x41544144 kfdhdb.driver.reserved[1]: 54 ; 0x00c: 0x00000036 kfdhdb.driver.reserved[2]: 0 ; 0x010: 0x00000000 kfdhdb.driver.reserved[3]: 0 ; 0x014: 0x00000000 kfdhdb.driver.reserved[4]: 0 ; 0x018: 0x00000000 kfdhdb.driver.reserved[5]: 0 ; 0x01c: 0x00000000 …………
通过分析,可以确定asm disk的备份block没有被覆盖,原则上可以通过备份block实现磁盘组恢复,从而减小了恢复难度
kfed恢复磁盘头
C:\Users\Administrator> kfed repair '\\.\J:' C:\Users\Administrator>kfed read '\\.\J:' kfbh.endian: 1 ; 0x000: 0x01 kfbh.hard: 130 ; 0x001: 0x82 kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD kfbh.datfmt: 1 ; 0x003: 0x01 kfbh.block.blk: 254 ; 0x004: T=0 NUMB=0xfe kfbh.block.obj: 2147483654 ; 0x008: TYPE=0x8 NUMB=0x6 kfbh.check: 717599272 ; 0x00c: 0x2ac5b228 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 kfdhdb.driver.provstr: ORCLDISKDATA6 ; 0x000: length=13 kfdhdb.driver.reserved[0]: 1096040772 ; 0x008: 0x41544144 kfdhdb.driver.reserved[1]: 54 ; 0x00c: 0x00000036 kfdhdb.driver.reserved[2]: 0 ; 0x010: 0x00000000 kfdhdb.driver.reserved[3]: 0 ; 0x014: 0x00000000 kfdhdb.driver.reserved[4]: 0 ; 0x018: 0x00000000 kfdhdb.driver.reserved[5]: 0 ; 0x01c: 0x00000000 …………
确定asm disk相关信息
对于7个被格式化的磁盘都进行类似处理之后,通过工具看到相关磁盘信息如下
恢复处理
根据ntfs的文件系统分布,我们可以知道,虽然asm disk header备份block正常,但是asm disk中间部分依旧有不少au会被破坏
这样的情况,不合适直接使用工具拷贝出来datafile(由于可能记录block的字典正好被覆盖,导致拷贝出来的文件异常,在恢复过程中我们也做了试验小文件拷贝ok,大文件拷贝然后使用dbv检测有很多坏块),我们采用工具(asm disk header 彻底损坏恢复)从底层扫描直接重组出来asm disk中的数据文件,然后结合拷贝出来的控制文件,redo文件,参数文件,然后通过重命名相关路径,然后直接open数据库
Q:\>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1月 22 16:08:18 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set pages 1000 SQL> col name for a100 SQL> set lines 150 SQL> select file#,name from v$datafile; FILE# NAME ---------- -------------------------------------------------------------------- 1 +DATA/vspdb/datafile/system.256.778520603 2 +DATA/vspdb/datafile/sysaux.257.778520603 3 +DATA/vspdb/datafile/undotbs1.258.778520603 4 +DATA/vspdb/datafile/users.259.778520603 5 +DATA/vspdb/datafile/vsp_tbs.293.779926097 ………… 147 +DATA/vspdb/datafile/index_dg.418.864665747 148 +DATA/vspdb/datafile/data_dg.419.864667053 149 +DATA/vspdb/datafile/vsp_mm_tbs.420.890410367 150 +DATA/vspdb/datafile/vsp_mm_tbs.421.890410457 SQL> select member from v$logfile; MEMBER ------------------------------------------------------------------------------------- +DATA/vspdb/onlinelog/group_7.263.862676593 +DATA/vspdb/onlinelog/group_7.262.862676601 +DATA/vspdb/onlinelog/group_4.410.862652291 +DATA/vspdb/onlinelog/group_4.411.862652307 +DATA/vspdb/onlinelog/group_5.412.862653715 +DATA/vspdb/onlinelog/group_5.413.862653727 +DATA/vspdb/onlinelog/group_6.414.862676425 +DATA/vspdb/onlinelog/group_6.415.862676433 重命名数据文件和redo文件,open数据库 SQL> recover database; 完成介质恢复。 SQL> alter database open; 数据库已更改。 已用时间: 00: 00: 04.51
由于部分block被覆盖,使用空块代替,导致数据访问到该block就会出现ora-8103(模拟普通ORA-08103并解决,模拟极端ORA-08103并解决)错误,对于该种对象,最简单处理方法就是直接通过dul抽出来数据然后truncate table重新导入数据,当然如果你想彻底安全逻辑方式重建库最靠谱
如果您遇到此类情况,无法解决请联系我们,提供专业ORACLE数据库恢复技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
ORA-10562 故障恢复—allow 1 corruption
朋友数据库由于存储变动,导致数据库瞬间hang住,然后直接crash,之后无法正常启动,请求技术支持.
数据库报ORA-00600[2131]错误
不能mount,可以通过重建控制文件解决
Mon Nov 30 20:35:38 2015 alter database mount Mon Nov 30 20:35:38 2015 NOTE: Loaded library: System Mon Nov 30 20:35:38 2015 SUCCESS: diskgroup DATADG was mounted Mon Nov 30 20:35:38 2015 NOTE: dependency between database xifenfei and diskgroup resource ora.DATADG.dg is established Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_26450.trc (incident=3032256): ORA-00600: internal error code, arguments: [2131], [33], [32], [], [], [], [], [], [], [], [], [] ORA-600 signalled during: alter database mount...
尝试recover数据库
Mon Nov 30 20:45:53 2015 ALTER DATABASE RECOVER database Media Recovery Start started logmerger process Parallel Media Recovery started with 80 slaves Mon Nov 30 20:45:56 2015 Recovery of Online Redo Log: Thread 2 Group 11 Seq 617 Reading mem 0 Mem# 0: +DATADG/xifenfei/redo011.log Recovery of Online Redo Log: Thread 1 Group 4 Seq 5410 Reading mem 0 Mem# 0: +DATADG/xifenfei/redo04.log Recovery of Online Redo Log: Thread 1 Group 5 Seq 5411 Reading mem 0 Mem# 0: +DATADG/xifenfei/redo05.log Mon Nov 30 20:46:07 2015 Recovery of Online Redo Log: Thread 1 Group 6 Seq 5412 Reading mem 0 Mem# 0: +DATADG/xifenfei/redo06.log Mon Nov 30 20:46:13 2015 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xC] [PC:0x95FB502, kdxlin()+4088] [flags: 0x0, count: 1] Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_pr13_30480.trc (incident=3032568): ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4088] [SIGSEGV] [ADDR:0xC] [PC:0x95FB502] [Address not mapped to object] [] Mon Nov 30 20:46:17 2015 Sweep [inc][3032568]: completed Sweep [inc2][3032568]: completed Mon Nov 30 20:46:31 2015 Slave exiting with ORA-10562 exception Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_pr13_30480.trc: ORA-10562: Error occurred while applying redo to data block (file# 2, block# 165054) ORA-10564: tablespace SYSAUX ORA-01110: 数据文件 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 271 ORA-00607: 当更改数据块时出现内部错误 ORA-00602: 内部编程异常错误 ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4088] [SIGSEGV] [ADDR:0xC] [PC:0x95FB502] [Address not mapped to object] [] Mon Nov 30 20:46:31 2015 Recovery Slave PR13 previously exited with exception 10562 Mon Nov 30 20:46:33 2015 Checker run found 28 new persistent data failures Mon Nov 30 20:46:35 2015 Media Recovery failed with error 448 Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_pr00_30400.trc: ORA-00283: 恢复会话因错误而取消 ORA-00448: 后台进程正常结束 ORA-10562 signalled during: ALTER DATABASE RECOVER database ...
通过这里可以看到,由于在recover 操作之时,由于某种原因redo的数据无法apply到file 2 block 165054中,导致数据库recover database失败.
按照数据文件recover操作
SQL> recover datafile 1; Media recovery complete. SQL> recover datafile 3,4,5,6,7; Media recovery complete. SQL> recover datafile 8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28; Media recovery complete. SQL> recover datafile 2; ORA-00283: recovery session canceled due to errors ORA-10562: Error occurred while applying redo to data block (file# 2, block# 165054) ORA-10564: tablespace SYSAUX ORA-01110: data file 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 271 ORA-00607: Internal error occurred while making a change to a data block ORA-00602: internal programming exception ORA-07445: exception encountered: core dump [kdxlin()+4088] [SIGSEGV] [ADDR:0xC] [PC:0x95FB502] [Address not mapped to object] []
错误提示和recover database一样,那我们只能让恢复跳过该block继续恢复,因为根据经验判断data object# 271不是系统核心对象,不会影响数据库的启动
跳过坏块继续恢复
SQL> recover datafile 2 allow 1 corruption; ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [3020], [2], [69793], [8458401], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 2, block# 69793, file offset is 571744256 bytes) ORA-10564: tablespace SYSAUX ORA-01110: data file 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 272 SQL> recover datafile 2 allow 1 corruption; Media recovery complete. SQL> alter database open; Database altered.
出现了ORA-600[3020] 继续跳过坏块,然后数据库顺利open,别忘记加tempfile
处理异常对象
SQL> select object_name,object_type from dba_objects where data_object_id in(272,271); OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE ------------------- SMON_SCN_TIME_TIM_IDX INDEX SMON_SCN_TIME_SCN_IDX INDEX SQL> select index_name from dba_indexes where table_name='SMON_SCN_TIME'; INDEX_NAME ------------------------------ SMON_SCN_TIME_TIM_IDX SMON_SCN_TIME_SCN_IDX SQL> set pages 1000 SQL> set long 1000 SQL> Select dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM DUAL ; DBMS_METADATA.GET_DDL('TABLE','SMON_SCN_TIME','SYS') -------------------------------------------------------------------------------- CREATE TABLE "SYS"."SMON_SCN_TIME" ( "THREAD" NUMBER, "TIME_MP" NUMBER, "TIME_DP" DATE, "SCN_WRP" NUMBER, "SCN_BAS" NUMBER, "NUM_MAPPINGS" NUMBER, "TIM_SCN_MAP" RAW(1200), "SCN" NUMBER DEFAULT 0, "ORIG_THREAD" NUMBER DEFAULT 0 /* for downgrade */ ) CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX" ("THREAD") SQL> analyze table smon_scn_time validate structure cascade online; analyze table smon_scn_time validate structure cascade online * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 2, block # 165054) ORA-01110: data file 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867' SQL> truncate CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX"; Cluster truncated.
关于SMON_SCN_TIME部分处理,可以参考:关于SMON_SCN_TIME若干问题说明.至此数据库基本上恢复完成,而且运气非常好,恢复的非常完美,数据实现0丢失.