转载朋友改写《前世今生的轮回》为《专一爱》的歌词
前世今生的轮回
=================================
第1章：相遇
——————————————
挥手之间，我就爱上了你
把这份爱牢牢的，放在心里
海誓山盟，的爱，也给了你
把你的样子留在了，脑海里
时间飞逝，爱的那么甜蜜
鱼和猫眯，从此也不再对立
感动上天，与你相偎相依
今生的相遇，不，会，轻，言，分离
第2章：相识
——————————————
前世的轮回，注定了爱你
奈何桥上等着你
梦婆的汤里，留下了回忆
下辈子你能否记的起
心中的秘密，注定是天意
就像牛郎和织女
好好的爱你，乞求天的旨意
我们这辈子要在一起
第3章：相谈
——————————————
一世的情缘，我拥有了你，
把这份甜蜜好好的，去珍，惜
沧海桑田，的爱带给了你，
把你的纯洁，留给了你自己
时间飞逝，爱的那么彻底
老鼠大米推了世俗，在，一起
感动上天，与你经历风雨
今生的相遇，不会，随便，放弃
第4章：相思
——————————————
爱你在心里，没人能代替
把你拥在我怀里
感受你的气息，体会你的爱意
真的真的不能没有你
想你在梦里，念你在心底
失去你，我不愿意
把你的甜蜜，抓在我的手里
会用一生好好的陪你
第5章：相知
——————————————
挥手之间，我们在了一起
彼此的分离有了，心的相聚
罗曼蒂克，式的爱情洗礼
把我们心真正，牵随，在一起
时间飞逝，爱的那么清晰
蜜蜂与蜂蜜，永远不会离弃
感动上天，死心塌地爱你
今生的相遇，注定了，我，爱，你
第6章：相别
——————————————
今世的轮回，爱了你无悔
下辈子还要相会
相思的美味，梦里的相对
来世要紧紧，的跟随
蝴蝶空中飞，述说的很美
感动了月老，的体会
来生的相会，红线来牵随
第7章：结局
————————
生生世世与你一起轮回
生生世世与你一起轮回
专一爱
======================================
第1章：相遇
——————————————
那天下午，我们不期而遇
听这个暖洋洋的，浪漫歌曲
点点滴滴，回忆，那么有趣
把这个午后打扮成，文艺剧
三生石上，刻着你的名字
千山过尽，有缘千里来相聚
红尘擦肩，有你无所畏惧
今生的相遇，你，是，我，的，伴侣
第2章：相识
——————————————
时间的左右，再次的相遇
沧海水边听一曲
巫山的云上，写下了一句
这辈子我要以身相许
难得有情郎，注定是伴侣
就像牛郎和织女
好好的爱你，送你春光和煦
月上了梢头相思屡屡
第3章：相谈
——————————————
风云的流转，白头不相离，
把这份甜蜜好好的，去继，续
惊世不凡，的情无需根据，
幸福里享受，化作相思一缕
日影如飞，带着太多情趣，
此情绵绵守候在你，人，生旅
花香满园，拥抱旷世细雨
风情的款款，他日，还再，相聚
第4章：相思
——————————————
微笑里感动，寄卿又一曲
不问曲终人散聚
感受你的气息，体会你的爱意
真的真的不能没有你
必经的路上，幸福和情趣
遍地是，情思缕缕
多年的以后，还要与你耳语
会用一生好好的陪你
第5章：相知
——————————————
这天下午，我们谈着嫁娶
彼此的心中有了，点点犹豫
习惯想念，你的字字句句
把甜蜜事刻在，这首，老歌曲
人约黄昏，声音那么犹豫
人情和情人，奇妙感觉些许
天长地久，死心塌地赢取
今生的相遇，全部是，你，步，履
第6章：相别
——————————————
相思无尽处，突然的离去
生活也变得无趣，
一世的光阴，何日再相聚
留下这片片，的思虑
朦胧的月光，低叹着情绪
守候着往日，的歌曲
往后的岁月，注定难继续
第7章：结局
——————————————
倦鸟回巢伴着衣衫褴褛
倦鸟回巢伴着衣衫褴褛
Author Archives: 惜分飞
ORA-00600[kccpb_sanity_check_2]
alert日志出现如下错误
数据库在mount的时候，因为出现ORA-00600[kccpb_sanity_check_2]错误导致数据库不能正常被mount成功
--sqlplus中报错 SQL> startup nomount; ORACLE instance started. Total System Global Area 4294967296 bytes Fixed Size 2273256 bytes Variable Size 1486573592 bytes Database Buffers 2801795072 bytes Redo Buffers 4325376 bytes SQL> alter database mount; alter database mount * ERROR at line 1: ORA-600: internal error code, arguments: [kccpb_sanity_check_2], [2825], [2824], [0x000000000], [], [], [], [] --alert日志 Mon Sep 24 16:35:37 2012 ALTER DATABASE MOUNT Mon Sep 24 16:35:41 2012 Errors in file /opt/app/oracle/admin/lhgk/udump/lhgk_ora_17034.trc: ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [2825], [2824], [0x000000000], [], [], [], [] Mon Sep 24 16:35:41 2012 ORA-600 signalled during: ALTER DATABASE MOUNT...
错误原因
ORA-600 [kccpb_sanity_check_2] indicates that the seq# of the last read block is higher than the seq# of the control file header block. This is indication of the lost write of the header block during commit of the previous cf transaction.
解决方法
1) restore a backup of a controlfile and recover OR 2) recreate the controlfile OR 3) restore the database from last good backup and recover NOTE: If you do not have any special backup of control file to restore and you are using Multiple Control File copies in your pfile/init.ora/spfile you can attempt to mount the database using each control file one by one. If you are able to mount the database with any of these control file copies you can then issue 'alter database backup controlfile to trace' to recreate controlfile.
在win中运行bbed程序
学习ORACLE三剑客:互联网,ORACLE资料,交流.今天在朋友的帮助下,了结了一个心结:在WIN平台中完美的使用bbed(8i/9i部分版本/10g 11g不支持),今天在朋友的帮助下解决了该问题
9i中bbed相关程序
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\XIFENFEI>cd E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win
C:\Users\XIFENFEI>e:
E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>dir
驱动器 E 中的卷没有标签。
卷的序列号是 000C-3B41
E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win 的目录
2012/09/24 19:45 <DIR> .
2012/09/24 19:45 <DIR> ..
2006/07/29 13:33 147,728 bbed.exe
2006/06/28 11:32 20,752 heteroxa9.dll
2004/10/26 16:35 49,152 kpp95rdr.dll
2004/10/26 16:35 45,056 kpp97rdr.dll
2012/09/24 19:49 250 log.bbd
2006/07/29 02:08 229,648 njssl9.dll
2006/06/28 11:33 61,712 ocijdbc9.dll
2002/04/26 16:22 733,184 oip9.dll
2006/07/29 14:02 1,589,520 oraclient9.dll
2006/07/29 15:25 602,384 oracommon9.dll
2006/06/28 09:54 651,536 ORACORE9.DLL
2006/06/27 08:44 1,736,976 oractxx9.dll
2006/07/29 14:02 2,691,344 orageneric9.dll
2006/07/29 13:56 24,576 oraimr9.dll
2006/07/29 15:19 3,014,656 orajox9.dll
2006/06/28 03:09 155,920 oraldapclnt9.dll
2006/06/28 03:09 24,848 oraldapjclnt9.dll
2006/07/29 02:18 741,648 oran9.dll
2006/07/29 02:10 41,232 oranad9.dll
2006/07/29 02:06 33,040 oranbeq9.dll
2006/07/29 02:08 24,848 ORANCDS9.DLL
2006/07/29 02:07 98,576 orancrypt9.dll
2006/07/29 02:07 33,040 ORANGSS9.DLL
2006/07/29 02:07 24,848 oranhost9.dll
2006/07/29 02:06 33,040 oranipc9.dll
2006/07/29 02:08 176,400 oranjni9.dll
2006/07/29 02:07 213,264 ORANK59.DLL
2006/07/29 02:06 180,496 oranl9.dll
2006/07/29 02:07 74,000 oranldap9.dll
2006/07/29 02:08 28,944 oranldapj9.dll
2006/06/28 06:32 499,984 ORANLS9.DLL
2006/07/29 02:06 33,040 orannmp9.dll
2006/07/29 02:07 53,520 orannts9.dll
2006/07/29 02:07 28,944 orannzentr9.dll
2006/07/29 02:08 28,944 ORANNZMCS9.DLL
2006/07/29 02:07 586,000 orannzsbb9.dll
2006/07/29 02:07 24,848 oranoname9.dll
2006/07/29 02:08 57,616 oranoncj9.dll
2006/07/29 02:07 45,328 ORANRAD9.DLL
2006/07/29 02:06 225,552 oranro9.dll
2006/07/29 02:08 41,232 oransgr9.dll
2006/07/29 02:06 49,424 orantcp9.dll
2006/07/29 02:07 53,520 ORANTCPS9.DLL
2006/07/29 02:07 28,944 orantns9.dll
2006/07/29 13:58 57,784 oraobjop9.dll
2006/07/29 14:58 311,568 oraocci9.dll
2006/07/29 14:02 24,848 oraodm9.dll
2006/06/26 19:19 2,314,240 oraolapapi9.dll
2006/07/29 13:58 57,784 oraolapop9.dll
2006/07/18 08:53 377,104 oraordim9.dll
2006/07/29 12:39 61,712 ORAPLC9.DLL
2006/07/29 12:40 909,584 ORAPLP9.DLL
2006/07/29 12:39 3,281,168 ORAPLS9.DLL
2006/07/29 13:58 57,784 ORAPRTOP9.DLL
2003/01/22 18:59 20,480 orarac9.dll
2002/04/26 17:18 28,944 ORASLAX9.DLL
2006/06/28 06:33 69,904 ORASNLS9.DLL
2006/06/28 16:04 483,600 ORASQL9.DLL
2002/04/29 14:04 246,032 oratrace9.dll
2002/04/29 14:04 86,288 oratracepls9.dll
2006/06/28 06:32 69,904 ORAUNLS9.DLL
2006/07/29 14:02 24,848 oravsn9.dll
2005/03/29 17:17 24,576 orawsec9.dll
2006/07/29 14:02 24,848 orawtc9.dll
2001/04/16 19:19 191,248 orawwg9.dll
2006/06/27 01:50 520,464 ORAXML9.DLL
2006/06/27 01:50 41,232 ORAXMLG9.DLL
2006/06/27 01:50 172,304 ORAXSD9.DLL
2010/06/29 05:38 181,560 vspp97.dll
2010/06/29 05:38 83,256 vsqp9.dll
2010/06/29 05:38 177,464 vsw97.dll
72 个文件 25,234,522 字节
2 个目录 58,747,236,352 可用字节
执行bbed程序报错
E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>bbed Message 112 not found; No message file for product=RDBMS, facility=BBED BBED-00113: file not found
分析原因:因为在11g中ORACLE并没有在?/RDBMS/MESG/中提供bbedus.msb程序,导致bbed运行时候无法显示相关提示信息,从而出现类此该错误
解决方法:在11g环境中需要从10g库中拷贝bbedus.msb文件放置到?/RDBMS/MESG/中即可
运行bbed程序
E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Sep 24 19:45:40 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info all;
File# Name Size(blks)
----- ---- ----------
BBED> show all;
FILE# 0
BLOCK# 1
OFFSET 0
DBA 0x00000000 (0 0,1)
FILENAME
BIFILE bifile.bbd
LISTFILE
BLOCKSIZE 2048
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
模拟普通ORA-08103并解决
在上一篇中说到:模拟极端ORA-08103并解决,不能通过修改成坏块来解决,这里演示了是一个普通的数据块出现异常,然后通过bbed修改为坏块通过dbms_repair来解决该故障,补充说明:在11.2.0.3.3的库中,使用该方法不能重现该错误,而是直接提示ORA-01578,证明ORACLE的新版本在这一方面进行了改进
创建测试表
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table t_xifenfei as
2 select * from dba_objects where rownum<3000;
Table created.
SQL> SELECT owner, segment_name, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
2 FROM dba_extents
3 WHERE segment_name='T_XIFENFEI' AND owner='CHF';
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ --------------- ---------- ---------- ---------- ----------
CHF T_XIFENFEI 0 4 57 8
CHF T_XIFENFEI 1 4 65 8
CHF T_XIFENFEI 2 4 73 8
CHF T_XIFENFEI 3 4 81 8
CHF T_XIFENFEI 4 4 89 8
CHF T_XIFENFEI 5 4 97 8
6 rows selected.
SQL> SELECT DISTINCT dbms_rowid.rowid_block_number(rowid) blk#,
2 dbms_rowid.rowid_relative_fno(rowid) file#
3 FROM t_xifenfei
4 ORDER BY 2,1;
BLK# FILE#
---------- ----------
60 4
61 4
62 4
63 4
64 4
65 4
66 4
67 4
68 4
69 4
70 4
BLK# FILE#
---------- ----------
71 4
72 4
74 4
75 4
76 4
77 4
78 4
79 4
80 4
81 4
82 4
BLK# FILE#
---------- ----------
83 4
84 4
85 4
86 4
87 4
88 4
90 4
91 4
92 4
93 4
94 4
BLK# FILE#
---------- ----------
95 4
96 4
97 4
98 4
37 rows selected.
模拟ORA-08103
SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
--破坏数据快(其实就是清空一个数据块block 95,注意dd和实际数据的block对应关系相差1)
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users01.dbf bs=8192 seek=95 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000187113 seconds, 43.8 MB/s
SQL> STARTUP
ORACLE instance started.
Total System Global Area 318767104 bytes
Fixed Size 1267236 bytes
Variable Size 104860124 bytes
Database Buffers 205520896 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI;
SELECT COUNT(*) FROM CHF.T_XIFENFEI
*
ERROR at line 1:
ORA-08103: object no longer exists
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_xifenfei file=/tmp/t_xifenfei.dmp
Export: Release 10.2.0.4.0 - Production on Fri Jan 13 22:09:43 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T_XIFENFEI
EXP-00056: ORACLE error 8103 encountered
ORA-08103: object no longer exists
Export terminated successfully with warnings.
[oracle@xifenfei ~]$ expdp chf/xifenfei tables=t_xifenfei dumpfile=t_xifenfei.dmp
Export: Release 10.2.0.4.0 - Production on Friday, 13 January, 2012 22:10:26
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01": chf/******** tables=t_xifenfei dumpfile=t_xifenfei.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "CHF"."T_XIFENFEI" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
/u01/oracle/oracle/product/10.2.0/db_1/rdbms/log/t_xifenfei.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:10:50
测试证明当出现ORA-08103的时候,全表扫描,exp,expdp均不能正常工作
找到出现ORA-08103数据块
SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> alter session set events '8103 trace name errorstack level 3';
Session altered.
SQL> alter session set events '10236 trace name context forever, level 1';
Session altered.
SQL> alter session set tracefile_identifier='ORA8103';
Session altered.
SQL> select * from chf.t_xifenfei;
……………………
ERROR:
ORA-08103: object no longer exists
2700 rows selected.
--在trace文件结尾发现如下记录,表示读到这个数据块时发生错误
KTRVAC: path typ=0, rdba=100005f
SQL> select to_number('100005f','xxxxxxxxxxxxx') from dual;
TO_NUMBER('100005F','XXXXXXXXXXXXX')
------------------------------------
16777311
SQL> select
2 dbms_utility.data_block_address_file(16777311) FILE_NO,
3 dbms_utility.data_block_address_block(16777311) BLOCK_NO
4 from dual;
FILE_NO BLOCK_NO
---------- ----------
4 95
bbed继续破坏异常块
BBED> set filename '/u01/oracle/oradata/XFF/users01.dbf'
FILENAME /u01/oracle/oradata/XFF/users01.dbf
BBED> set block 95
BLOCK# 95
BBED> map
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 95 Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (00)
BBED> set count 32
COUNT 32
BBED> d
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 95 Offsets: 0 to 31 Dba:0x00000000
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
--这个就是和11gr2的区别,在11g中使用该方法来模拟ORA-08103,直接提示坏块,从而不会出现ORA-08103
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/XFF/users01.dbf
BLOCK = 95
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 : 1
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
BBED> d offset 8180
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 95 Offsets: 8180 to 8191 Dba:0x00000000
------------------------------------------------------------------------
00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 01010101 offset 8188
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
--修改sumcheck
BBED> m /x 01010101 offset 8188
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 95 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
01010101
<32 bytes per line>
BBED> sum
Check value for File 0, Block 95:
current = 0x0000, required = 0x0000
测试修改为坏块效果
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 95)
ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
2918
通过让ORA-08103对应的块变为真正的坏块,然后使用dbms_repair或者event来跳过坏块,达到拯救数据的目的
模拟极端ORA-08103并解决
ORA-08103错误在数据库日程运维和异常恢复中都可能遇到,出现该错误的原因很多,有很多情况下(模拟普通ORA-08103并解决),直接通过修改块使其在数据库查询的时候表标志为坏块,然后使用event或者dbms_repair包来标志该块,然后跳过就可以解决该问题,但是有些时候,遇到极端情况,该方法会失效,需要借助极端工具来处理该极端问题.
分析表相关EXTENT
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t_xifenfei as
2 select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
COUNT(*)
----------
74504
SQL> select data_object_id,object_id from dba_objects where object_name='T_XIFENFEI';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
75592 75592
SQL> select file_id,block_id,block_id+blocks-1
2 from dba_extents
3 where segment_name ='T_XIFENFEI' AND owner='CHF';
FILE_ID BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
4 680 687
4 688 695
4 696 703
4 704 711 <---注意
4 712 719
4 720 727
4 728 735
…………
24 rows selected.
SQL> Select segment_name,header_file,header_blocK
2 from dba_segments where
3 segment_name in ('T_XIFENFEI') and owner='CHF'
4 ;
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
T_XIFENFEI 4 682
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 682;
System altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28933.trc
--trace文件
Dump of memory from 0xB6CFD600 to 0xB6CFF600
B6CFD600 0000A223 010002AA 000DB4EA 04010000 [#...............]
B6CFD610 00008ECF 00000000 00000000 00000000 [................]
B6CFD620 00000000 00000018 00000480 00000A9C [................]
B6CFD630 00000017 0000003F 00000080 010007BF [....?...........]
B6CFD640 00000000 00000017 00000000 0000043F [............?...]
B6CFD650 00000000 00000000 00000000 00000017 [................]
B6CFD660 0000003F 00000080 010007BF 00000000 [?...............]
B6CFD670 00000017 00000000 0000043F 01000780 [........?.......]
B6CFD680 01000780 00000000 00000000 00000000 [................]
B6CFD690 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
B6CFD6D0 00000001 00002000 00000000 00001434 [..... ......4...]
B6CFD6E0 00000000 010002A9 00000001 01000781 [................]
B6CFD6F0 010002A9 00000000 00000000 00000000 [................]
B6CFD700 00000000 00000000 00000018 00000000 [................]
B6CFD710 00012748 10000000 010002A8 00000008 [H'..............]
B6CFD720 010002B0 00000008 010002B8 00000008 [................]
B6CFD730 010002C0 00000008 010002C8 00000008 [................] <----dump中找到下面值
……
Extent Map
-----------------------------------------------------------------
0x010002a8 length: 8
0x010002b0 length: 8
0x010002b8 length: 8
0x010002c0 length: 8 <-----选择第四个exent
0x010002c8 length: 8
…………
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x010002a8 Data dba: 0x010002ab
Extent 1 : L1 dba: 0x010002a8 Data dba: 0x010002b0
Extent 2 : L1 dba: 0x010002b8 Data dba: 0x010002b9
Extent 3 : L1 dba: 0x010002b8 Data dba: 0x010002c0 <---同上
Extent 4 : L1 dba: 0x010002c8 Data dba: 0x010002c9
Extent 5 : L1 dba: 0x010002c8 Data dba: 0x010002d0
…………
--------------------------------------------------------
--确定Extent 3的记录在file 4 block 682 的偏移量为304
------省略了相同部分B6CFD
SQL> SELECT TO_NUMBER('730','XXXXX') FROM DUAL;
TO_NUMBER('730','XXXXX')
------------------------
1840
SQL> SELECT TO_NUMBER('600','XXX') FROM DUAL;
TO_NUMBER('600','XXX')
----------------------
1536
SQL> SELECT 1840-1536 FROM DUAL;
1840-1536
----------
304
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
这里我们假设修改SEGMENT HEADER中关于EXTENT 3(从0开始计数)的映射地址,从而来使得该表在查询的时候出现ORA-08103错误
bbed修改相关值
[oracle@xifenfei ~]$ bbed parfile=bbed.par
BBED> SET MODE EDIT
MODE Edit
BBED> INFO
File# Name Size(blks)
----- ---- ----------
4 /u01/oracle/oradata/ora11g/users01.dbf 0
BBED> SET FILE 4 BLOCK 682
FILE# 4
BLOCK# 682
BBED> D
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 682 Offsets: 0 to 511 Dba:0x010002aa
------------------------------------------------------------------------
23a20000 aa020001 eab40d00 00000104 cf8e0000 …………
<32 bytes per line>
BBED> m /x 1100
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 682 Offsets: 0 to 511 Dba:0x010002aa
------------------------------------------------------------------------
11000000 aa020001 eab40d00 00000104 cf8e0000 …………
<32 bytes per line>
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 682 Offsets: 0 to 511 Dba:0x010002aa
------------------------------------------------------------------------
11000000 aa020001 eab40d00 00000104 cf8e0000 …………
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 682:
current = 0x2cfd, required = 0x2cfd
重现ORA-08103
SQL> startup
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 239078260 bytes
Database Buffers 67108864 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
*
ERROR at line 1:
ORA-08103: object no longer exists
定位坏块位置
SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> alter session set events '8103 trace name errorstack level 3';
Session altered.
SQL> alter session set events '10236 trace name context forever, level 1';
Session altered.
SQL> alter session set tracefile_identifier='ORA8103';
Session altered.
SQL> select * from chf.t_xifenfei;
select * from chf.t_xifenfei
*
ERROR at line 1:
ORA-08103: object no longer exists
--trace文件关键内容
block_row_dump:
tab 0, row 0, @0x1f70
tl: 48 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 9] 44 49 43 54 2e 42 41 53 45
col 1: [ 1] 32
col 2: [32]
…………
end_of_block_dump
The buffer with tsn: 0 rdba: 0x00400321 has already been dumped
The buffer with tsn: 4 rdba: 0x010002aa was pinned,
but could not be dumped
SQL> Select to_number('010002aa','xxxxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('010002AA','XXXXXXXXXXXXXXXXXX')
------------------------------------------
16777898
SQL> select
2 dbms_utility.data_block_address_file(16777898) FILE_NO,
3 dbms_utility.data_block_address_block(16777898) BLOCK_NO
4 from dual;
FILE_NO BLOCK_NO
---------- ----------
4 682
检查坏块
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jan 13 18:03:13 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf
Block Checking: DBA = 16777898, Block Type = Unlimited data segment header with flg blks
Incorrect total map count: 24
Page 682 failed with check code 17006
DBVERIFY - Verification complete
Total Pages Examined : 2240
Total Pages Processed (Data) : 1421
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 199
Total Pages Failing (Index): 0
Total Pages Processed (Other): 229
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 391
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 898278 (0.898278)
--对应块
SQL> select
2 dbms_utility.data_block_address_file(16777898) FILE_NO,
3 dbms_utility.data_block_address_block(16777898) BLOCK_NO
4 from dual;
FILE_NO BLOCK_NO
---------- ----------
4 682
通过这里dbv检查发现,该数据库已经是坏块了,也就是说,网上流传的人工标志为坏块的方法在此处行不通,这里我们考虑使用dul等类此工具挖取数据,来拯救数据
dul和odu常规操作
DUL> unload table chf.t_xifenfei; . unloading table T_XIFENFEI DUL: Warning: Using data objno from segment header( 16779199) instead of expected id (75592) from dictionary DUL: Error: No entry in control file for block: ts# = 4 rfile# = 0 block# = 1087 DUL: Error: While processing unknown file block# 1087 DUL: Error: Could not read/parse data block ODU> unload table chf.t_xifenfei Unloading table: T_XIFENFEI,object ID: 75592 Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0) corrupted block 0 rows unloaded
通过上面的测试证明,在该中情况下(SEGMENT HEADER)出现异常时,dul/odu均不能使用最常规的方法挖的数据
扫描数据文件方式挖
因为odu在这个方面的操作人性化于dul,所以只用odu进行相关测试,dul肯定能过实现相同功能
ODU> SCAN EXTENT TABLESPACE 4 PARALLEL 2 scan extent start: 2012-9-23 2:47:51 scanning extent... scanning extent finished. scan extent completed: 2012-9-23 2:47:51 ODU> unload table chf.t_xifenfei object 75592 Unloading table: T_XIFENFEI,object ID: 75592 Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0) 74504 rows unloaded
试验证明通过odu扫描表空间/数据文件找回来所有的数据,然后truncate掉问题表,导入该数据库,问题可以得意顺利解决
