坏块oracle实验,坏块模拟实验

SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcldg/test.dbf' size 1m;

Tablespace created.

SQL> create user test identified by test default tablespace test;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table test1(id number,name varchar2(100));

Table created.

SQL> declare

2 begin

3 for i in 1..100 loop

4 insert into test1 values(i,'liming'||i);

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

一直搞到

ERROR at line 1:

ORA-01653: unable to extend table TEST.TEST2 by 8 in tablespace TEST

ORA-06512: at line 4

SQL> select count(1) from test1;

COUNT(1)

----------

40002

关掉数据库，破坏数据文件。

[oracle@lmsingledg orcldg]$ cd /u01/app/oracle/oradata/orcldg/

[oracle@lmsingledg orcldg]$ vi test.dbf

眼花，随便找一个不是数据的乱码，删掉。

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 989858976 bytes

Database Buffers 603979776 bytes

Redo Buffers 7319552 bytes

Database mounted.

Database opened.

SQL> conn test/test

select * from test1;

ERROR:

ORA-01578: ORACLE data block corrupted (file # 5, block # 13)

ORA-01110: data file 5: '/u01/app/oracle/oradata/orcldg/test.dbf'

750 rows selected.

SQL> SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 5 and 13 between block_id AND block_id + blocks - 1 ;

TABLESPACE_NAME SEGMENT_TYPE OWNER

------------------------------ ------------------ ------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

TEST TABLE TEST

TEST1

果断看备份。

RMAN> list backup of datafile 5;

using target database control file instead of recovery catalog

specification does not match any backup in the repository

这时候导出数据：

[oracle@lmsingledg ~]$ exp test/test tables=test1 file=/home/oracle/test3.dmp

Export: Release 11.2.0.4.0 - Production on Thu Oct 22 22:34:17 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table TEST1

EXP-00056: ORACLE error 1578 encountered

ORA-01578: ORACLE data block corrupted (file # 5, block # 13)

ORA-01110: data file 5: '/u01/app/oracle/oradata/orcldg/test.dbf'

Export terminated successfully with warnings.

[oracle@lmsingledg ~]$

设置内部事件，导出的时候跳出这些块。

SQL> ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10' ;

System altered.

[oracle@lmsingledg ~]$ exp test/test tables=test1 file=/home/oracle/test2.dmp

Export: Release 11.2.0.4.0 - Production on Thu Oct 22 22:35:44 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table TEST1 756 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

就导出了756条数据。

最后，取消设置

alter system set events='10231 trace name context off';

损失了40002-756条数据。

把表drop了，重新建表，插数。

所以说，备份是必须的，多么痛的领悟呀。