oracle ora 08103,模拟普通ORA-08103并解决 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429...-优快云博客

在上一篇中说到:模拟极端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

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

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来跳过坏块,达到拯救数据的目的