联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在上一篇中说到:模拟极端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来跳过坏块,达到拯救数据的目的