oracle坏块分物理坏块和逻辑坏块，产生物理坏块的原因大部分是因为机器硬件问题，逻辑坏块的产生原因一般是由于oracle软件的bug或者未打上oracle的补丁
可以使用下面的来发现oracle坏块：
1：analyze
2: dbverify
3. db_block_checking
4. dbms_repair
rowid是一个伪列，是用来确保表中行的唯一性，它并不能指示出行的物理位置，但可以用来定位行。
rowid为18位64进制，A-Z代表0-25；a-z代表26-51,0-9代表52-61，+号代表62，\代表63前6+3位代表表空间对应的数据文件，10-15位代表块号，16-18位代表行号
模拟物理坏块
SQL> select substr(rowid,10,6),dbms_rowid.rowid_block_number(rowid) as block_head
2 from hr.employees where rownum<5;
SUBSTR(ROWID,10,6) BLOCK_HEAD
------------------------ ----------
AAAABY 88
AAAABY 88
AAAABY 88
AAAABY 88
SQL> select header_block,segment_name from dba_segments s
2 where s.segment_name='EMPLOYEES' and s.owner='HR';
HEADER_BLOCK SEGMENT_NAME
------------ ----------------------
83 EMPLOYEES
SQL> select table_name,tablespace_name from dba_tables t
2 where t.table_name='EMPLOYEES' and t.owner='HR';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMPLOYEES EXAMPLE
SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';
FILE_NAME
-------------------------------------
/u01/app/oradata/ora10g/example01.dbf
[oracle@rhel6 ~]$ dd of=/u01/app/oradata/ora10g/example01.dbf bs=8192 conv=notrunc seek=83<
> EOF
0+1 records in
0+1 records out
22 bytes (22 B) copied, 0.00979513 s, 2.2 kB/s
SQL> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 83)
ORA-01110: data file 5: '/u01/app/oradata/ora10g/example01.dbf'
exp导出将会报错
[oracle@rhel6 ~]$ exp userid=hr/hr table=employees
LRM-00101: unknown parameter name 'table'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
[oracle@rhel6 ~]$ exp userid=hr/hr table=employees file=emp.dump
LRM-00101: unknown parameter name 'table'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
[oracle@rhel6 ~]$ exp userid=hr/hr tables=employees file=emp.dump
Export: Release 10.2.0.1.0 - Production on Thu Jul 21 14:06:52 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in UTF8 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table EMPLOYEES
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 5, block # 83)
ORA-01110: data file 5: '/u01/app/oradata/ora10g/example01.dbf'
Export terminated successfully with warnings.
expdp命令对于物理坏块可以导出表结构，无法导出数据
[oracle@rhel6 01]$ expdp hr/hr directory=tmp01 dumpfile=emp.dmp tables=employees
Export: Release 10.2.0.1.0 - 64bit Production on Thursday, 21 July, 2011 14:16:51
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.
OCP043第七讲Dealing with Database Corruption(一)
| 评论 |
|
|