--启用10231内部事件
alter system set events='10231 trace name context forever,level 10';
--关闭10231内部事件
alter system set events='10231 trace name context off';
测试设置10231事件后是否可以逻辑导出:
[oracle@JY-DB01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 14:01:43 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> alter system set events='10231 trace name context forever,level 10';
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@JY-DB01 ~]$ exp jingyu/jingyu tables=t_part file=t_part.dmp log=exp_t_part.log
Export: Release 11.2.0.4.0 - Production on Tue Jan 19 14:01:57 2016
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, Automatic Storage Management, 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_PART
. . exporting partition P20150101 0 rows exported
. . exporting partition P20150102 10000 rows exported
. . exporting partition P20150103 19823 rows exported
Export terminated successfully without warnings.
--成功导出后记得要关闭10231内部事件
alter system set events='10231 trace name context off';
20000 - 19823 = 177行,也就是说该数据块损坏直接导致了177行数据丢失。不过还好,保住了大部分数据。
SQL> select count(1) from t_part;
select count(1) from t_part
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)
ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'
SQL> alter system set events='10231 trace name context forever,level 10';
System altered.
SQL> select count(1) from t_part;
COUNT(1)
----------
29823
SQL> create table temp_t_part_20150103 as select * from t_part partition(P20150103);
Table created.
SQL> alter system set events='10231 trace name context off';
System altered.
SQL> select count(1) from t_part partition(P20150103);
select count(1) from t_part partition(P20150103)
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)
ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'
SQL> select count(1) from temp_t_part_20150103;
COUNT(1)
----------
19823