1.模拟表、及表空间 SQL> create tablespace test datafile '/u01/app/oracle/oradata/DBdb/test01.dbf' size 30m;
Tablespace created.
SQL> create table bbed tablespace test as select * from dba_tables;
Table created.
SQL> select file_id,tablespace_name,file_name,status from dba_data_files where tablespace_name='TEST';
FILE_ID TABLESPACE_NAME FILE_NAME STATUS ---------- ------------------------------ ------------------------------------------------------------ --------- 8 TEST /u01/app/oracle/oradata/DBdb/test01.dbf AVAILABLE SQL> select count(*) from bbed;
COUNT(*) ---------- 2877
SQL> col segment for a10 SQL> select segment_name,file_id,block_id from dba_extents where segment_name='BBED';
BBED> revert All changes made in this session will be rolled back. Proceed? (Y/N) y Reverted file '/u01/app/oracle/oradata/DBdb/test01.dbf', block 1 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Block 144 is corrupt Corrupt block relative dba: 0x02000090 (file 0, block 144) Bad header found during verification Data in bad block: type: 3 format: 0 rdba: 0x02000090 last change scn: 0x0000.003a6987 seq: 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x69872002 check value in block header: 0xcc16 computed block checksum: 0x4a23
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 : 0 Total Blocks Marked Corrupt : 1 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED
DBVERIFY: Release 11.2.0.4.0 - Production on Thu Feb 1 13:09:03 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/DBdb/test01.dbf Page 144 is marked corrupt Corrupt block relative dba: 0x02000090 (file 8, block 144) Bad header found during dbv: Data in bad block: type: 3 format: 0 rdba: 0x02000090 last change scn: 0x0000.003a6987 seq: 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x69872002 check value in block header: 0xcc16 computed block checksum: 0x4a23
DBVERIFY - Verification complete
Total Pages Examined : 3840 Total Pages Processed (Data) : 100 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 135 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 3604 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 3828105 (0.3828105) [oracle@wang lib]$
3. 数据块损坏，需要恢复。 3.1 在这种情况下，如果有备份，需要从备份中恢复；如果没有备份，那么坏块部分的数据库就要丢失了。检查损坏的对象： SELECT owner, segment_name, segment_type, relative_fno, tablespace_name FROM dba_extents WHERE file_id = &AFN and &BL between block_id AND block_id + blocks - 1;
SQL> SELECT owner, segment_name, segment_type, relative_fno, tablespace_name 2 FROM dba_extents 3 WHERE file_id = &AFN 4 and &BL between block_id AND block_id + blocks - 1; Enter value for afn: 8 old 3: WHERE file_id = &AFN new 3: WHERE file_id = 8 Enter value for bl: 144 old 4: and &BL between block_id AND block_id + blocks - 1 new 4: and 144 between block_id AND block_id + blocks - 1
Export: Release 11.2.0.4.0 - Production on Thu Feb 1 13:38:13 2018
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 ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ... Current user changed to SYS . . exporting table BBED 2877 rows exported Export terminated successfully without warnings. [oracle@wang ~]$
--删除表 SQL> drop table bbed purge;
Table dropped.
SQL>
--执行导入： imp \'\/ as sysdba\' file=bbed.dmp log=bbedimp.log TABLES=bbed
[oracle@wang ~]$ imp \'\/ as sysdba\' file=bbed.dmp log=bbedimp.log TABLES=bbed
Import: Release 11.2.0.4.0 - Production on Thu Feb 1 13:49:48 2018
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 file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by SYSTEM, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing SYSTEM's objects into SYS . importing SYS's objects into SYS . . importing table "BBED" 2877 rows imported Import terminated successfully without warnings. [oracle@wang ~]$
--查询： SQL> select count(*) from bbed;
COUNT(*) ---------- 2877
3.4 取消10231事件： SQL> alter system set events='10231 trace name context off';
System altered.
3.5 验证，不存在坏块： RMAN> validate datafile 8;
Starting validate at 01-FEB-18 using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00008 name=/u01/app/oracle/oradata/DBdb/test01.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 8 OK 0 3601 3840 3830998 File Name: /u01/app/oracle/oradata/DBdb/test01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 103 Index 0 0 Other 0 136
