在上一篇中说到:模拟极端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来跳过坏块,达到拯救数据的目的
