标签云
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: 通信通道的文件结尾
标签归档:Oracle 恢复
使用bbed 修复I_OBJ4 index 报ORA-8102错误
数据库执行创建表操作报ORA-8102错误
SQL> startup ORACLE instance started. Total System Global Area 1570009088 bytes Fixed Size 2253584 bytes Variable Size 469765360 bytes Database Buffers 1090519040 bytes Redo Buffers 7471104 bytes Database mounted. Database opened. SQL> create table t1 as select * from dba_users; create table t1 as select * from dba_users * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 87404, file 1, block 97266 (2)
分析ORA-8102错误
SQL> col OBJECT_NAME for a30 SQL> select object_name,object_type from dba_objects where object_id=87404; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- I_OBJ4 INDEX SQL> select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t minus 2 3 select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1; DATAOBJ# TYPE# OWNER# ---------- ---------- ---------- 87420 0 0 SQL> select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1 2 minus 3 select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t 4 ; DATAOBJ# TYPE# OWNER# ---------- ---------- ---------- 87422 0 0 SQL> alter system dump datafile 1 block 97266; System altered. SQL> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_27037.trc SQL> ALTER SESSION SET EVENTS '802 trace name errorstack level 3'; Session altered. SQL> create table t1 as select * from dual; create table t1 as select * from dual * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 87404, file 1, block 97266 (2) SQL> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_27037.trc *** 2015-03-14 14:46:33.640 kdk key 8102.2: ncol: 4, len: 16 key: (16): 04 c3 09 4b 17 01 80 01 80 06 00 41 7f 25 00 28 mask: (4096): *** 2015-03-14 14:46:33.644 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0) ----- Error Stack Dump ----- ----- Current SQL Statement for this session (sql_id=4yyb4104skrwj) ----- update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11, oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
这里可以的出来由于obj$中的dataobj#为87422,而i_obj4中的dataobj#为87420,因此两者不一致。
另外通过相关trace发现,在创建表操作中会调用update obj$的一个递归操作,而该操作会更新dataobj#,但是由于该值在表和index中不匹配,因此出现ORA-08102导致创建表不成功
使用bbed 修复ORA-8102
[oracle@localhost ~]$ bbed blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/xifenfei/system01.dbf' Password: BBED: Release 2.0.0.0.0 - Limited Production on Sat Mar 14 14:55:22 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set block 97266 BLOCK# 97266 BBED> f /x 04c3 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 2714 to 3225 Dba:0x00000000 ------------------------------------------------------------------------ 04c3094a 5f02c115 01800600 417f2500 0f000204 c3094b14 02c11501 80060041 7f25000e 000204c3 094b1202 c1140180 0600417f 25000d00 0004c309 4b150180 <32 bytes per line> BBED> f File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 2733 to 3244 Dba:0x00000000 ------------------------------------------------------------------------ 04c3094b 1402c115 01800600 417f2500 0e000204 c3094b12 02c11401 80060041 7f25000d 000004c3 094b1501 80018006 00417f25 00280100 04c3094b 10018001 <32 bytes per line> BBED> f File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 2752 to 3263 Dba:0x00000000 ------------------------------------------------------------------------ 04c3094b 1202c114 01800600 417f2500 0d000004 c3094b15 01800180 0600417f 25002801 0004c309 4b100180 01800600 417f2500 28000004 c3094b08 02c10201 <32 bytes per line> BBED> f File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 2771 to 3282 Dba:0x00000000 ------------------------------------------------------------------------ 04c3094b 15018001 80060041 7f250028 010004c3 094b1001 80018006 00417f25 00280000 04c3094b 0802c102 01800600 417f2500 24000004 c3094b09 02c10201 <32 bytes per line> BBED> f File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 2789 to 3300 Dba:0x00000000 ------------------------------------------------------------------------ 04c3094b 10018001 80060041 7f250028 000004c3 094b0802 c1020180 0600417f 25002400 0004c309 4b0902c1 02018006 00417f25 00250000 04c3094b 0a02c103 <32 bytes per line> BBED> set count 32 COUNT 32 BBED> set offset 2771 OFFSET 2771 BBED> d File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 2771 to 2802 Dba:0x00000000 ------------------------------------------------------------------------ 04c3094b 15018001 80060041 7f250028 010004c3 094b1001 80018006 00417f25 <32 bytes per line> BBED> set offset +4 OFFSET 2775 BBED> d File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 2775 to 2806 Dba:0x00000000 ------------------------------------------------------------------------ 15018001 80060041 7f250028 010004c3 094b1001 80018006 00417f25 00280000 <32 bytes per line> BBED> m /x 17 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 2775 to 2806 Dba:0x00000000 ------------------------------------------------------------------------ 17018001 80060041 7f250028 010004c3 094b1001 80018006 00417f25 00280000 <32 bytes per line> BBED> sum apply Check value for File 0, Block 97266: current = 0x7955, required = 0x7955 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf BLOCK = 97266 Block 97266 is corrupt Corrupt block relative dba: 0x00417bf2 (file 0, block 97266) Fractured block found during verification Data in bad block: type: 6 format: 2 rdba: 0x00417bf2 last change scn: 0x0000.00102ed8 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x2ed80602 check value in block header: 0x7955 computed block checksum: 0x0 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 1 Total Blocks Influx : 2 Message 531 not found; product=RDBMS; facility=BBED BBED> set offset 8188 OFFSET 8188 BBED> d File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 0206d82e <32 bytes per line> BBED> m /x 01 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 0106d82e <32 bytes per line> BBED> sum Check value for File 0, Block 97266: current = 0x7955, required = 0x7956 BBED> sum apply Check value for File 0, Block 97266: current = 0x7956, required = 0x7956 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf BLOCK = 97266 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 1 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED
通过bbed修改i_obj4中的dataobj#值使之和obj$中对应值一致
验证确认ORA-8102被修复
SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1570009088 bytes Fixed Size 2253584 bytes Variable Size 469765360 bytes Database Buffers 1090519040 bytes Redo Buffers 7471104 bytes Database mounted. Database opened. SQL> create table t1 as select * from dual; Table created.
通过使用bbed修改index值后,ORA-8102问题解决,可以执行创建表操作
姊妹篇见:通过bbed修改obj$中dataobj$重现I_OBJ4索引报ORA-08102错误
11.1.0.7版本也会出现access$表丢失导致数据库无法启动
有网友咨询数据库启动报ora-01092:ORACLE 实例终止。强制断开连接,请求帮忙处理
数据库版本
Trace file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5648.trc Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Windows NT Version V6.1 Service Pack 1 CPU : 1 - type 8664, 1 Physical Cores Process Affinity : 0x0000000000000000 Memory (Avail/Total): Ph:7605M/10239M, Ph+PgF:11979M/20477M Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 18 Windows thread id: 5648, image: ORACLE.EXE (SHAD)
open数据库报ORA-01092: ORACLE 实例终止。强制断开连接
SQL> alter database open; alter database open * 第 1 行出现错误: ORA-01092: ORACLE 实例终止。强制断开连接
alert日志
Thread 1 opened at log sequence 1008 Current log# 3 seq# 1008 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set SMON: enabling cache recovery Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3964.trc: ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-00942: 表或视图不存在 Error 704 happened during db open, shutting down database USER (ospid: 3964): terminating the instance due to error 704 Instance terminated by USER, pid = 3964 ORA-1092 signalled during: ALTER DATABASE OPEN... ORA-1092 : opiodr aborting process unknown ospid (3384_3964)
做10046分析日志
PARSE ERROR #1:len=56 dep=1 uid=0 oct=3 lid=0 tim=1796038335 err=942 select order#,columns,types from access$ where d_obj#=:1 *** 2015-01-27 21:24:50.794 ----- Error Stack Dump ----- ORA-00604: 递归 SQL 级别 1 出现错误 ORA-00942: 表或视图不存在
通过这里可以知道数据库在启动的过程中由于无法访问access$表从而出现ORA-00942错误,又是由于该sql是数据库内部调用因为出现ORA-00604错误.
出现该错误的原因是由于:BUG:12733463 – ORA-704, ORA-604 AND ORA-942 ON TABLE ACCESS$ DURING STARTUP
官方提供方法
1. Shutdown (abort) the instance and clean up any OS structures used by the instance. Eg: Ensure there is no shared memory, semaphores etc.. left lying around 2. Retry the startup. 3. If the error persists try and recover the database or recover from a backup.
惜分飞处理方法
startup upgrade create table access$ ( d_obj# number not null, order# number not null, columns raw(126), types number not null) storage (initial 10k next 100k maxextents unlimited pctincrease 0) / create index i_access1 on access$(d_obj#, order#) storage (initial 10k next 100k maxextents unlimited pctincrease 0) /
以前类似文章:Oracle 异常恢复案例汇总
Oracle 12C的第一次异常恢复—文件头坏块
接到第一个使用Oracle 12C作为生产库的恢复救援.有两个业务数据文件报文件头损坏,其他数据文件全部是9月份的一次备份,在当前的条件下,希望我们能够帮他们恢复出来业务文件中的数据
数据库版本信息
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0
数据库故障
具体脚本请参考:数据库恢复检查脚本(Oracle Database Recovery Check)
控制文件信息
控制文件中关于数据文件信息
数据文件头信息
alert日志报错
Reading datafile '/app/oracle/oradata/freetouch/sales.dbf' for corruption at rdba: 0x00000001 (file 4, block 1) Reread (file 4, block 1) found same corrupt data (no logical check) Hex dump of (file 5, block 1) in trace file /app/oracle/diag/rdbms/valuenet/valuenet/trace/valuenet_ora_12384.trc Corrupt block relative dba: 0x00000001 (file 5, block 1) Fractured block found during kcvxfh v8 Data in bad block: type: 0 format: 2 rdba: 0x00000001 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000001 check value in block header: 0xa701 computed block checksum: 0x0 Reading datafile '/app/oracle/oradata/freetouch/drp_200200' for corruption at rdba: 0x00000001 (file 5, block 1) Reread (file 5, block 1) found same corrupt data (no logical check) Hex dump of (file 4, block 1) in trace file /app/oracle/diag/rdbms/valuenet/valuenet/trace/valuenet_ora_12384.trc Corrupt block relative dba: 0x00000001 (file 4, block 1) Fractured block found during kcvxfh v8 Data in bad block: type: 0 format: 2 rdba: 0x00000001 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000001 check value in block header: 0xa701 computed block checksum: 0x0
odu无法识别异常文件
[oracle@db odu]$ ./odu Oracle Data Unloader trial version 4.1.3 Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved. loading default config....... byte_order little block_size 8192 db_timezone -7 Invalid db timezone:-7 client_timezone 8 Invalid client timezone:8 asmfile_extract_path /home/oracle/hongye/odu/data data_path /home/oracle/hongye/odu/data lob_path /home/oracle/hongye/odu/data charset_name ZHS16GBK ncharset_name AL16UTF16 output_form dmp error at line 10. lob_storage infile clob_byte_order big trace_level 1 delimiter | unload_deleted yes file_header_offset 0 is_tru64 no record_row_addr no convert_clob_charset yes load config file 'config.txt' successful loading default asm disk file ...... can not open file 'asmdisk.txt', error message:No such file or directory. loading default control file ...... unknown file format '/app/oracle/oradata/freetouch/sales.dbf' unknown file format '/app/oracle/oradata/freetouch/drp_200200' ts# fn rfn bsize blocks bf offset filename ---- ---- ---- ----- -------- -- ------ -------------------------------------------- 1 1 1 8192 194560 N 0 /app/oracle/oradata/freetouch/system01.dbf 6 2 10 8192 45840 N 0 /app/oracle/oradata/freetouch/example01.dbf 1 3 3 8192 907520 N 0 /app/oracle/oradata/freetouch/sysaux01.dbf 4 1024 10 8192 0 N 0 /app/oracle/oradata/freetouch/sales.dbf 5 1024 9 8192 0 N 0 /app/oracle/oradata/freetouch/drp_200200 4 6 6 8192 128320 N 0 /app/oracle/oradata/freetouch/users01.dbf 7 7 7 8192 780288 N 0 /app/oracle/oradata/freetouch/undotbs03.dbf 11 8 8 8192 25600 N 0 /app/oracle/oradata/freetouch/indx01.dbf load control file 'control.txt' successful loading dictionary data......done loading scanned data......done
dul无法识别异常文件
[oracle@db dul]$ ./dul Data UnLoader: 10.2.0.5.32 - Internal Only - on Sun Nov 2 23:34:42 2014 with 64-bit io functions Copyright (c) 1994 2014 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only DUL: Warning: ulimit process stack size is only 33554432 Found db_id = 270587870 Found db_name = VALUENET DUL: Warning: Cannot verify file number for /app/oracle/oradata/freetouch/sales.dbf DUL: Warning: First four bytes(76 162 0 0) of block 2 are not the start of a proper data block header DUL: Warning: Block corruption or configuration error DUL: Warning: Check db_block_size and/or osd_file_leader_size and/or file offset DUL: Error: File Number can only be zero for Single Tablespace Datafiles DUL: Warning: Cannot verify file number for /app/oracle/oradata/freetouch/drp_200200 DUL: Warning: First four bytes(76 162 0 0) of block 2 are not the start of a proper data block header DUL: Warning: Block corruption or configuration error DUL: Warning: Check db_block_size and/or osd_file_leader_size and/or file offset DUL: Error: File Number can only be zero for Single Tablespace Datafiles DUL> show datafiles; ts# rf# start blocks offs open err file name 0 1 0 194561 0 1 0 /app/oracle/oradata/freetouch/system01.dbf 1 3 0 907521 0 1 0 /app/oracle/oradata/freetouch/sysaux01.dbf 4 6 0 128321 0 1 0 /app/oracle/oradata/freetouch/users01.dbf 7 7 0 780289 0 1 0 /app/oracle/oradata/freetouch/undotbs03.dbf 11 8 0 25601 0 1 0 /app/oracle/oradata/freetouch/indx01.dbf 6 10 0 45841 0 1 0 /app/oracle/oradata/freetouch/example01.dbf
该异常文件使用dul/odu均无法正常识别.证明文件头确实已经损坏
dbv 检测
[oracle@db trace]$ dbv file=/app/oracle/oradata/freetouch/drp_200200 DBVERIFY: Release 12.1.0.1.0 - Production on Sun Nov 2 14:08:34 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /app/oracle/oradata/freetouch/drp_200200 DBVERIFY - Verification complete Total Pages Examined : 194560 Total Pages Processed (Data) : 114596 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 26198 Total Pages Failing (Index): 0 Total Pages Processed (Other): 37787 Total Pages Processed (Seg) : 1 Total Pages Failing (Seg) : 0 Total Pages Empty : 15979 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 129603862 (0.129603862) [oracle@db ~]$ dbv file=/app/oracle/oradata/freetouch/sales.dbf DBVERIFY: Release 12.1.0.1.0 - Production on Sun Nov 2 23:12:05 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /app/oracle/oradata/freetouch/sales.dbf DBVERIFY - Verification complete Total Pages Examined : 655360 Total Pages Processed (Data) : 294938 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 233404 Total Pages Failing (Index): 0 Total Pages Processed (Lob) : 38 Total Pages Failing (Lob) : 0 Total Pages Processed (Other): 23252 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 103728 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 134665298 (0.134665298)
dbv检测结果无坏块,但是v$datafile_header和alert日志中报坏块,初步判断是由于该文件是bigfile,dbv未检测到文件头坏块,实际该该数据文件头损坏,其他block正常.所幸的是该库有9月份的rman备份(中间归档丢失),因此使用rman还原出来9月份的数据文件,然后使用dd拷贝两个 block(block 0和block 1)到异常文件.
[root@db freetouch]# dd if=/app1/oracle/oradata/freetouch/sales.dbf bs=8192 count=2 of=/tmp/odu/sales.2 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.0176368 s, 929 kB/s [root@db freetouch]# dd if=/tmp/odu/sales.2 of=/app/oracle/oradata/freetouch/sales.dbf bs=8192 count=2 conv=notrunc 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 6.4281e-05 s, 255 MB/s [root@db freetouch]# dd if=/app1/oracle/oradata/freetouch/drp_200200 bs=8192 count=2 of=/tmp/odu/drp_200200.2 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.0185934 s, 881 kB/s [root@db freetouch]# dd if=/tmp/odu/drp_200200.2 of=/app/oracle/oradata/freetouch/drp_200200 bs=8192 count=2 conv=notrunc 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 6.4419e-05 s, 254 MB/s
尝试恢复数据库
SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done SQL> recover database using backup controlfile; ORA-00283: recovery session canceled due to errors ORA-19909: datafile 4 belongs to an orphan incarnation ORA-01110: data file 4: '/app/oracle/oradata/freetouch/sales.dbf'
使用bbed修改相关文件头,然后继续恢复
具体见:bbed解决ORA-01190类似方法处理
SQL> recover database using backup controlfile; ORA-00279: change 129603904 generated at 11/02/2014 19:19:54 needed for thread 1 ORA-00289: suggestion : /app/oracle/recovery_area/VALUENET/archivelog/2014_11_02/o1_mf_1_1_%u_.arc ORA-00280: change 129603904 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel; ORA-00308: cannot open archived log 'cancel;' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [129603911], [0], [129603913], [29360256], [], [], [], [], [], [] Process ID: 19881 Session ID: 1 Serial number: 3
出现ORA-600[2662]错误,因为scn相差比较小,重启数据库机器,出现ORA-600[4194]错误
SQL> startup pfile='/tmp/pfile.txt' mount ORACLE instance started. Total System Global Area 2505338880 bytes Fixed Size 2291472 bytes Variable Size 973080816 bytes Database Buffers 1526726656 bytes Redo Buffers 3239936 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/app/oracle/oradata/freetouch/system01.dbf' SQL> recover database ; ORA-00283: recovery session canceled due to errors ORA-16433: The database or pluggable database must be opened in read/write mode.
重建控制文件后继续恢复
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [4194], [46], [19], [], [], [], [], [], [], [], [], [] Process ID: 20351 Session ID: 1 Serial number: 3
设置undo_management=MANUAL然后继续恢复
[oracle@db tmp]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sun Nov 2 19:29:45 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='/tmp/pfile.txt' ORACLE instance started. Total System Global Area 2505338880 bytes Fixed Size 2291472 bytes Variable Size 973080816 bytes Database Buffers 1526726656 bytes Redo Buffers 3239936 bytes Database mounted. Database opened.
这次的恢复也证明Oracle 12C确实有着越来越多的用户在使用.
发表在 非常规恢复
标签为 ORA-00283, ORA-19909, ORA-600 2662, ORA=600 4194, Oracle 12C 恢复, Oracle 恢复, 数据库恢复
评论关闭