标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-01595 ORA-08103 ORA-600 2131 ORA-600 2662 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)
- 操作系统 (103)
- 数据库 (1,769)
- DB2 (22)
- MySQL (77)
- Oracle (1,610)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (166)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (69)
- Oracle Bug (8)
- Oracle RAC (54)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (592)
- Oracle安装升级 (97)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (86)
- PostgreSQL (30)
- pdu工具 (6)
- PostgreSQL恢复 (9)
- SQL Server (32)
- SQL Server恢复 (13)
- TimesTen (7)
- 达梦数据库 (3)
- 达梦恢复 (1)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (39)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (22)
-
最近发表
- 2025年的Oracle 8.0.5数据库恢复
- ORA-600 kokiasg1故障分析(obj$中核心字典序列全部被恶意删除)
- ORA-00756 ORA-10567故障数据0丢失恢复
- 数据库文件变成32k故障恢复
- tcp连接过多导致监听TNS-12532 TNS-12560 TNS-00502错误
- 文件系统格式化MySQL数据库恢复
- .sstop勒索加密数据库恢复
- 解决一次硬件恢复之后数据文件0kb的故障恢复case
- Error in invoking target ‘libasmclntsh19.ohso libasmperl19.ohso client_sharedlib’问题处理
- ORA-01171: datafile N going offline due to error advancing checkpoint
- linux环境oracle数据库被文件系统勒索加密为.babyk扩展名溯源
- ORA-600 ksvworkmsgalloc: bad reaper
- ORA-600 krccfl_chunk故障处理
- Oracle Recovery Tools恢复案例总结—202505
- ORA-600 kddummy_blkchk 数据库循环重启
- 记录一次asm disk加入到vg通过恢复直接open库的案例
- CHECKDB 发现了 N 个分配错误和 M 个一致性错误
- 达梦数据库dm.ctl文件异常恢复
- Oracle Recovery Tools修复ORA-00742、ORA-600 ktbair2: illegal inheritance故障
- 可能是 tempdb 空间用尽或某个系统表不一致故障处理
月归档:三月 2015
InMemory评估利器—Oracle Database InMemory Advisor
我想大家都可能有一个疑问,数据库从以前版本升级到12C,计划使用in-memory特性,那我怎么评估哪些表放进去合适,放进去后,整体性能又会提升多少,需要in-memory内存为多大?这些问题如果人工去判断不一定准确,而且可能有很多遗落,Oracle也考虑到了这一点,因此发布了Oracle Database InMemory Advisor,从而来比较简单的实现数据库使用In-Memory简单配置.Oracle Database InMemory Advisor主要通过DBMS_INMEMORY_ADVISOR包分析 Active Session History (ASH), Automatic Workload Repository (AWR) and SQL Tuning Sets (STS)信息进行分析,给出建议.
Oracle Database InMemory Advisor使用前提
1.数据库版本为11.2.0.3及其以上版本(compatibility>=11.2.0.3)
2.需要有Database Tuning pack liscense支持(仅仅是liscense,不是功能)
安装Oracle Database InMemory Advisor
[oracle@localhost xff]$ unzip imadvisor.zip Archive: imadvisor.zip inflating: instimadv.sql inflating: dbmsimadv.sql inflating: prvtimadv.plb inflating: dbmsimadvint.plb inflating: prvtimadvint.plb inflating: schmimadv.sql inflating: imadvisor_version.sql inflating: imadvisor_load_report_templates.sql inflating: imadvisor_clone_view.sql inflating: imadvisor_analyze_and_report.sql inflating: imadvisor_spool_debug.sql inflating: imadvisor_export.sql inflating: imadvisor_DataPump.sql inflating: imadvisor_awr_augment_export.sql inflating: imadvisor_awr_augment_import.sql inflating: imadvisor_awr_augment_tables.sql inflating: imadvisor_fetch_recommendations.sql [oracle@localhost xff]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 18 16:21:08 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @instimadv Welcome to the Oracle Database In-Memory Advisor (DBMS_INMEMORY_ADVISOR) installation. The Oracle Database In-Memory Advisor is licensed as part of the Oracle Tuning Pack. Do you currently have a valid Oracle Tuning Pack license with this database (Y/N)? Y DBMS_INMEMORY_ADVISOR uses Active Session History (ASH), Automatic Workload Repository (AWR) and optionally SQL Tuning Sets (STS) to determine which tables, partitions and subpartitions to place In Memory for optimized analytics processing performance. DBMS_INMEMORY_ADVISOR produces a recommendation report and a SQLPlus script to implement its recommendations. This installation script will create user IMADVISOR and add object definitions to the schema including the DBMS_INMEMORY_ADVISOR package. This installation script creates user IMADVISOR using the IDENTIFIED BY password method. If you prefer to use either the IDENTIFIED EXTERNALLY or IDENTIFIED GLOBALLY method, abort this installation by pressing ^C. Then create user IMADVISOR using your preferred method. Add no objects to the IMADVISOR schema. Then run this installation script again. These actions will be taken on the database to which you are currently connected. Please enter the connection ID for the current database? qsng This installation script creates a new Oracle database user and schema named IMADVISOR for the operation of DBMS_INMEMORY_ADVISOR... Please enter the password for user IMADVISOR? For confirmation, please re-enter the password for user IMADVISOR? Available tablespaces: TABLESPACE_NAME ------------------------------ FSDATA MYDATA SYSAUX SYSTEM TEMP UNDOTBS1 USERS Please enter the default tablespace name for user IMADVISOR? users The In-Memory Advisor uses the Oracle directory object IMADVISOR_DIRECTORY by default. If you wish to create the IMADVISOR_DIRECTORY object, please enter an OS host directory path for the IMADVISOR_DIRECTORY object. If not, please press ENTER to continue. ? /tmp/xff Connecting to IMADVISOR @ qsng.. Enter password: Connected. No errors. No errors. No errors. No errors. No errors. No errors. DBMS_INMEMORY_ADVISOR installation successful. Users who will use the DBMS_INMEMORY_ADVISOR package must be GRANTed EXECUTE on the DBMS_INMEMORY_ADVISOR package. Please enter a comma separated list of Oracle Database users to whom you wish EXECUTE on the DBMS_INMEMORY_ADVISOR package to be GRANTed? SYS GRANT EXECUTE ON dbms_inmemory_advisor TO SYS While logged in as IMADVISOR or with sufficient privileges, you can GRANT EXECUTE ON DBMS_INMEMORY_ADVISOR to additional users as needed. DBMS_INMEMORY_ADVISOR installation and setup complete. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Oracle Database InMemory Advisor生成报告
[oracle@localhost xff]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 18 16:30:57 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @imadvisor_analyze_and_report Specify the IM task name The IM Advisor generates a report as imadvisor_<taskname>.html file in the current working directory The sql file is generated as imadvisor_sql_<taskname>.sql Enter value for im_task_name: xifenfei IM Task name Specified: xifenfei Enter begin time for report: -- Valid input formats: -- To specify absolute begin time: -- [MM/DD[/YY]] HH24:MI[:SS] -- Examples: 02/23/03 14:30:15 -- 02/23 14:30:15 -- 14:30:15 -- 14:30 -- To specify relative begin time: (start with '-' sign) -- -[HH24:]MI -- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins) -- -25 (SYSDATE - 25 Mins) Enter value for begin_time: -3 Report begin time specified: -3 old 102: lbtime_in := nvl('&&begin_time', '-60'); new 102: lbtime_in := nvl('-3', '-60'); old 104: :btime := to_char( begin_time, '&&imadvisor_time_format' ); new 104: :btime := to_char( begin_time, 'YYYY-MON-DD HH24:MI:SS.FF' ); Enter duration in minutes starting from begin time: Defaults to SYSDATE - begin_time Enter value for duration: Report duration specified: old 8: begin_time := to_timestamp(:btime, '&&imadvisor_time_format'); new 8: begin_time := to_timestamp(:btime, 'YYYY-MON-DD HH24:MI:SS.FF'); old 18: duration := nvl('&&duration', since_begin_time); new 18: duration := nvl('', since_begin_time); old 29: :etime := to_char( end_time, '&&imadvisor_time_format' ); new 29: :etime := to_char( end_time, 'YYYY-MON-DD HH24:MI:SS.FF' ); Using 2015-MAR-18 16:28:22.000000000 as report begin time Using 2015-MAR-18 16:31:22.000000000 as report end time IM Advisor: Adding Statistics.. IMADVISOR: Finished Adding Statistics IMADVISOR: Finished Executing the task IM Advisor: Generating Recommendations.. imadvisor_xifenfei.html imadvisor_sql_xifenfei.html imadvisor_object_xifenfei.html imadvisor_xifenfei.sql 'Fetching recommendation files for task xifenfei' IM Advisor generated report in imadvisor_xifenfei.html IM Advisor genreated DDL script in imadvisor_xifenfei.sql SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@localhost xff]$ ls -ltr *xifenfei* -rw-r--r-- 1 oracle oinstall 887 Mar 18 16:33 imadvisor_xifenfei.sql -rw-r--r-- 1 oracle oinstall 30175 Mar 18 16:33 imadvisor_xifenfei.html -rw-r--r-- 1 oracle oinstall 13576 Mar 18 16:33 imadvisor_sql_xifenfei.html -rw-r--r-- 1 oracle oinstall 8931 Mar 18 16:33 imadvisor_object_xifenfei.html -rw-r--r-- 1 oracle oinstall 3405 Mar 18 16:33 imadvisor_auxiliary_xifenfei.html
这里输入的Task name为:xifenfei,Oracle Database InMemory Advisor结果
imadvisor_xifenfei.html是InMemory Advisor的一个整体描述
imadvisor_xifenfei.sql是InMemory Advisor生成的表级别的INMEMORY语句,可以直接通过@方式执行,或者修改后执行
imadvisor_sql_xifenfei.html主要是InMemory Advisor中关于sql的分析报告
imadvisor_object_xifenfei.html是InMemory Advisor中建议InMemory处理的对象分析报告
imadvisor_auxiliary_xifenfei.html 是一个辅助的总结
卸载Oracle Database InMemory Advisor
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 18 21:55:49 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> drop user imadvisor cascade; User dropped.
补充内容
1.在某些情况下,可能无法直接创建用户imadvisor,或者创建目录imadvisor_directory,可以通过类似命令创建,并修改instimadv.sql脚本屏蔽掉交互式安装
create user IMADVISOR identified by Oracle_123 DEFAULT TABLESPACE system; CREATE OR REPLACE DIRECTORY imadvisor_directory AS '/tmp/xff/txt'; GRANT READ, WRITE ON DIRECTORY imadvisor_directory TO IMADVISOR;
2.在执行@imadvisor_analyze_and_report生成报告,可能会遇到ORA-24817: Unable to allocate the given chunk for current lob operation,解决方案为:
1. Copy the original script to a new location to serve as a backup. 2. Edit the script imadvisor_fetch_recommendations.sql and change the line in the script: SET LONGCHUNKSIZE 2000000000 to SET LONGCHUNKSIZE 32767 3. Save and run the script.
参考文档
Oracle Database InMemory Advisor (Doc ID 1965343.1)
Using Inmemory Advisor Errors When Running Imadvisor_analyze_and_report (Doc ID 1987462.1)
分享I_OBJ4 ORA-8102故障恢复案例
在测试环境中对于OBJ$中i_obj4中出现ORA-8102进行了重新并恢复测试,认为自己已经比较清楚的掌握了I_OBJ4的ORA-8102问题处理,可是实际的一个案例,还是比较比实验中复杂,这里贴出来主要操作供大家参考,再次证明数据库恢复的场景不可大意,客户的故障只有你想不到的,没有遇不到的
通过bbed修改obj$中dataobj$重现I_OBJ4索引报ORA-08102错误
使用bbed 修复I_OBJ4 index 报ORA-8102
数据库创建表提示ORA-8102错误
SQL> startup ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size 2213976 bytes Variable Size 1.9327E+10 bytes Database Buffers 7516192768 bytes Redo Buffers 145174528 bytes Database mounted. Database opened. 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# 39, file 1, block 93842 (2)
分析ORA-08102错误
SQL> select object_name,object_type from dba_objects where object_id=39; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- I_OBJ4 INDEX 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# 39, file 1, block 93842 (2) SQL> select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t minus select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1; 2 3 DATAOBJ# TYPE# OWNER# ---------- ---------- ---------- 97109 0 0 SQL> select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1 minus select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t ; 2 3 4 DATAOBJ# TYPE# OWNER# ---------- ---------- ---------- 97094 0 0 SQL> SET LINES 122 COL INDEX_OWNER FOR A20 COL INDEX_NAME FOR A30 COL TABLE_OWNER FOR A20 COL COLUMN_NAME FOR A25 SELECT TABLE_OWNER,INDEX_NAME,COLUMN_NAME,COLUMN_POSITION FROM Dba_Ind_Columns WHERE table_name = upper('&TABLE_NAME') order by TABLE_OWNER,INDEX_OWNER,INDEX_NAME,COLUMN_POSITION and index_name='I_OBJ4'; SQL> SQL> SQL> SQL> SQL> 2 3 Enter value for table_name: OBJ$ old 3: WHERE table_name = upper('&TABLE_NAME') order by TABLE_OWNER,INDEX_OWNER,INDEX_NAME,COLUMN_POSITION new 3: WHERE table_name = upper('OBJ$') order by TABLE_OWNER,INDEX_OWNER,INDEX_NAME,COLUMN_POSITION TABLE_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION -------------------- ------------------------------ ------------------------- --------------- SYS I_OBJ4 DATAOBJ# 1 SYS I_OBJ4 TYPE# 2 SYS I_OBJ4 OWNER# 3 SQL> SELECT DATAOBJ# FROM OBJ$ WHERE OBJ#=97109; no rows selected SQL> SELECT DATAOBJ# FROM OBJ$ WHERE OBJ#=97094; DATAOBJ# ---------- 97094 SQL> select /*+ index(t i_obj4) */ rowid,DATAOBJ#,type#,owner# from obj$ t minus select /*+ full(t1) */ rowid,DATAOBJ#,type#,owner# from obj$ t1; 2 3 ROWID DATAOBJ# TYPE# OWNER# ------------------ ---------- ---------- ---------- AAAAASAABAAAADxAAb 97109 0 0 SQL> select /*+ full(t1) */ rowid,DATAOBJ#,type#,owner# from obj$ t1 minus select /*+ index(t i_obj4) */ rowid,DATAOBJ#,type#,owner# from obj$ t ; 2 3 4 ROWID DATAOBJ# TYPE# OWNER# ------------------ ---------- ---------- ---------- AAAAASAABAAAADxAAb 97094 0 0 SQL> select name,obj#,dataobj# from obj$ where rowid='AAAAASAABAAAADxAAb'; NAME OBJ# DATAOBJ# ------------------------------ ---------- ---------- _NEXT_OBJECT 1 97094
到此也比较清楚,rowid为AAAAASAABAAAADxAAb的dataobj#记录在obj$表中为97094而在I_OBJ4中记录为97109,因此两者不一致,从而出现ORA-8102错误
尝试bbed解决ORA-8102问题
尝试修改obj$和i_obj4中的dataobj#记录一致,这里修改obj$中的对应记录
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#, dbms_rowid.rowid_row_number(rowid) row# from obj$ where rowid='AAAAASAABAAAADxAAb' 2 3 4 / FILE# BLOCK# ROW# ---------- ---------- ---------- 1 241 27 SQL> select dump(97109,16) from dual; DUMP(97109,16) ---------------------- Typ=2 Len=4: c3,a,48,a SQL> select dump(97094,16) from dual; DUMP(97094,16) ----------------------- Typ=2 Len=4: c3,a,47,5f -bash-4.1$ bbed blocksize=8192 mode=edit filename=/u01/app/oracle/oradata/oa/system01.dbf Password: BBED: Release 2.0.0.0.0 - Limited Production on Sat Mar 14 19:30:18 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> show all FILE# 0 BLOCK# 1 OFFSET 0 DBA 0x00000000 (0 0,1) FILENAME /u01/app/oracle/oradata/oa/system01.dbf BIFILE bifile.bbd LISTFILE BLOCKSIZE 8192 MODE Edit EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 512 LOGFILE log.bbd SPOOL No BBED> set block 241 BLOCK# 241 BBED> map File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 241 Dba:0x00000000 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 48 bytes @20 struct kdbh, 14 bytes @68 struct kdbt[1], 4 bytes @82 sb2 kdbr[105] @86 ub1 freespace[87] @296 ub1 rowdata[7805] @383 ub4 tailchk @8188 BBED> p *kdbr[27] rowdata[0] ---------- ub1 rowdata[0] @383 0x2c BBED> x /rnnncnnncc rowdata[0] @383 ---------- flag@383: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@384: 0x00 cols@385: 18 col 0[2] @386: 1 col 1[4] @389: 97094 col 2[1] @394: 0 col 3[12] @396: _NEXT_OBJECT col 4[2] @409: 1 col 5[0] @412: *NULL* col 6[1] @413: 0 col 7[7] @415: xm....4 col 8[7] @423: xs....6 col 9[7] @431: xm....4 col 10[1] @439: . col 11[0] @441: *NULL* col 12[0] @442: *NULL* col 13[1] @443: . col 14[0] @445: *NULL* col 15[1] @446: . col 16[4] @448: ..8$ col 17[1] @453: . BBED> set count 32 COUNT 32 BBED> set offset 389 OFFSET 389 BBED> d File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 241 Offsets: 389 to 420 Dba:0x00000000 ------------------------------------------------------------------------ 04c30a47 5f01800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 6d080f01 <32 bytes per line> BBED> set offset +3 OFFSET 392 BBED> d File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 241 Offsets: 392 to 423 Dba:0x00000000 ------------------------------------------------------------------------ 475f0180 0c5f4e45 58545f4f 424a4543 5402c102 ff018007 786d080f 01113407 <32 bytes per line> BBED> m /x 480a File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 241 Offsets: 392 to 423 Dba:0x00000000 ------------------------------------------------------------------------ 480a0180 0c5f4e45 58545f4f 424a4543 5402c102 ff018007 786d080f 01113407 <32 bytes per line> BBED> p *kdbr[27] rowdata[0] ---------- ub1 rowdata[0] @383 0x2c BBED> x /rnnncnnncc rowdata[0] @383 ---------- flag@383: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@384: 0x00 cols@385: 18 col 0[2] @386: 1 col 1[4] @389: 97109 col 2[1] @394: 0 col 3[12] @396: _NEXT_OBJECT col 4[2] @409: 1 col 5[0] @412: *NULL* col 6[1] @413: 0 col 7[7] @415: xm....4 col 8[7] @423: xs....6 col 9[7] @431: xm....4 col 10[1] @439: . col 11[0] @441: *NULL* col 12[0] @442: *NULL* col 13[1] @443: . col 14[0] @445: *NULL* col 15[1] @446: . col 16[4] @448: ..8$ col 17[1] @453: . BBED> sum apply Check value for File 0, Block 241: current = 0x913d, required = 0x913d
验证bbed修改后效果
SQL> startup ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size 2213976 bytes Variable Size 1.9327E+10 bytes Database Buffers 7516192768 bytes Redo Buffers 145174528 bytes Database mounted. Database opened. SQL> select /*+ index(t i_obj4) */ rowid,DATAOBJ#,type#,owner# from obj$ t minus select /*+ full(t1) */ rowid,DATAOBJ#,type#,owner# from obj$ t1; 2 3 no rows selected SQL> select /*+ full(t1) */ rowid,DATAOBJ#,type#,owner# from obj$ t1 minus select /*+ index(t i_obj4) */ rowid,DATAOBJ#,type#,owner# from obj$ t ; 2 3 4 no rows selected SQL> ANALYZE TABLE sys.obj$ VALIDATE STRUCTURE CASCADE; ANALYZE TABLE sys.obj$ VALIDATE STRUCTURE CASCADE * ERROR at line 1: ORA-01499: table/index cross reference failure - see trace file SQL> create table t_xifenfei as select * from dual; create table t_xifenfei as select * from dual * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 39, file 1, block 93842 (2)
这里比较悲剧,我们看到i_obj4和obj$中对应记录已经一致(条数和数据值),但是依然不能执行创建表操作,依旧报ORA-8102错误.
进一步分析错误原因
SQL> ALTER SESSION SET EVENTS '802 trace name errorstack level 3'; Session altered. SQL> create table t as select * from dual; create table t as select * from dual * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 39, file 1, block 93842 (2) SQL> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6163.trc oer 8102.2 - obj# 39, rdba: 0x00416e92(afn 1, blk# 93842) kdk key 8102.2: ncol: 4, len: 16 key: (16): 04 c3 0a 48 0a 01 80 01 80 06 00 40 00 f1 00 1b --这里可以看出来,提示ORA-8102错误依旧在I_OBJ4,97109记录上 SQL> select max(dataobj#) from obj$; MAX(DATAOBJ#) ------------- 96815 SQL> select name,obj#,dataobj# from obj$ where rowid='AAAAASAABAAAADxAAb'; NAME OBJ# DATAOBJ# ------------------------------ ---------- ---------- _NEXT_OBJECT 1 97109 --这里很奇怪,通过rowid查询我们已经的出来在obj$中有dataobj#为97109,而通过max(dataobj#)只有96815 分析max(dataobj#)执行计划 SQL> SET AUTOT TRACE SQL> select max(dataobj#) from obj$; Execution Plan ---------------------------------------------------------- Plan hash value: 721075849 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 2 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| I_OBJ4 | 1 | 2 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 533 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --这里知晓,由于max(dataobj#)使用了INDEX FULL SCAN (MIN/MAX)执行计划,从而的出来最大值为96815, --而我们从ORA-8102错误中可以看到index中有dataobj#为97109,证明index中的链表可能出问题 --为什么怀疑是链表有问题呢?因为该index的ffs正常
尝试把ORA-8102报错标记坏块尝试
并且通过event和隐含参数屏蔽index坏块
-bash-4.1$ bbed blocksize=8192 mode=edit filename=/u01/app/oracle/oradata/oa/system01.dbf Password: BBED: Release 2.0.0.0.0 - Limited Production on Sat Mar 14 20:30:58 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set block 93842 BLOCK# 93842 BBED> set offset 8188 OFFSET 8188 BBED> d File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 93842 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 010675ad <32 bytes per line> BBED> m /x 02 File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 93842 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 020675ad <32 bytes per line> BBED> sum apply Check value for File 0, Block 93842: current = 0x9186, required = 0x9186 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/oa/system01.dbf BLOCK = 93842 Block 93842 is corrupt Corrupt block relative dba: 0x00416e92 (file 0, block 93842) Fractured block found during verification Data in bad block: type: 6 format: 2 rdba: 0x00416e92 last change scn: 0x0000.c007ad75 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xad750602 check value in block header: 0x9186 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 -bash-4.1$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 14 20:33:19 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size 2213976 bytes Variable Size 1.9327E+10 bytes Database Buffers 7516192768 bytes Redo Buffers 145174528 bytes Database mounted. Database opened. SQL> create table t as select * from dual; create table t as select * from dual * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01578: ORACLE data block corrupted (file # 1, block # 93842) ORA-01110: data file 1: '/u01/app/oracle/oradata/oa/system01.dbf'
通过这一步测试证明,在该ora-8102的错误中,通过坏块是无法解决绕过去该错误,只是把错误从ORA-8102转变为了ORA-1578
修复好制造坏块block
BBED> m /x 01 File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 93842 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 010675ad <32 bytes per line> BBED> sum apply Check value for File 0, Block 93842: current = 0x9185, required = 0x9185 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/oa/system01.dbf BLOCK = 93842 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 SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size 2213976 bytes Variable Size 1.9327E+10 bytes Database Buffers 7516192768 bytes Redo Buffers 145174528 bytes Database mounted. Database opened. 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# 39, file 1, block 93842 (2)
至此我们大体出来信息:
1. ORA-8102的是I_OBJ4中的_NEXT_OBJECT记录异常和该index链表异常
2. 通过bbed修改I_OBJ4和obj$中相应记录无法解决该问题,因为还有链表异常
3. 通过标记为坏块也无法绕过该问题
由于后续如果继续修复修复i_obj4可能工作量过大,而且可以也比较急,通过人工直接删除I_OBJ4数据字典记录,然后结合bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决处理实现完美恢复
使用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错误