資料檔案壞塊的製造和恢復

1. 建立測試用的BLOCK表空間
SQL> create tablespace block
2 datafile 'D:ORACLEPRODUCT10.2.0ORADATAALEXBLOCK.DBF'
3 size 1M;

Tablespace created.

2. 建立測試使用者
SQL> create user alex
2 identified by alex
3 default tablespace block
4 temporary tablespace temp;

User created.

SQL> alter user alex quota unlimited on block;

User altered.

SQL> grant connect,resource,dba to alex;

Grant succeeded.

[@more@]3. 插入資料
SQL> conn alex/alex
Connected.
SQL> create table t as select * from dba_users;

Table created.

SQL> insert into t select * from t;

22 rows created.

SQL> /

44 rows created.

SQL> /

88 rows created.

SQL> /

176 rows created.

SQL> /

352 rows created.

SQL> /

704 rows created.

SQL> /

1408 rows created.

SQL> /

2816 rows created.

SQL> /
insert into t select * from t
*
ERROR at line 1:
ORA-01653: unable to extend table ALEX.T by 8 in tablespace BLOCK

SQL> commit;

Commit complete.

4. 做一次完全checkpoint，寫入資料檔案
SQL> alter system checkpoint;

System altered.

SQL> select count(*) from t;

COUNT(*)
----------
5632

5. 關閉資料庫
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

6. 用Ultredit編輯資料檔案，隨便更改幾個字元.

7. 開啟資料庫
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.

8. 檢視錶T，出現壞塊
SQL> select count(*) from alex.t;
select count(*) from alex.t
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 59)
ORA-01110: data file 5: 'D:ORACLEPRODUCT10.2.0ORADATAALEXBLOCK.DBF'

9. 用dbv驗證
C:>dbv file=D:oracleproduct10.2.0oradataalexlock.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on 星期二 8月 21 10:27:18 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = D:oracleproduct10.2.0oradataalexlock.dbf
Page 59 is marked corrupt
Corrupt block relative dba: 0x0140003b (file 5, block 59)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0140003b
last change scn: 0x0000.00086fcd seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x6fcd0601
check value in block header: 0xa55
computed block checksum: 0x5003

DBVERIFY - Verification complete

Total Pages Examined : 128
Total Pages Processed (Data) : 109
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 18
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 552917 (0.552917)

10. 檢視出現壞塊的物件
SQL> SELECT tablespace_name, segment_type, owner, segment_name
2 FROM dba_extents
3 WHERE file_id = 5
4 and 59 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME SEGMENT_TYPE OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
BLOCK TABLE ALEX
T

11. 嘗試exp出現壞塊的表T，出錯
C:>exp alex/alex file=t.dmp tables=t

Export: Release 10.2.0.1.0 - Production on 星期二 8月 21 10:29:00 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table T
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 5, block # 59)
ORA-01110: data file 5: 'D:ORACLEPRODUCT10.2.0ORADATAALEXBLOCK.DBF'
Export terminated successfully with warnings.

12. 啟動內建事件10231 skip corrupted blocks on _table_scans_
SQL> ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10';

System altered.

13. 再次EXP，成功
C:>exp alex/alex file=d: .dmp tables=t

Export: Release 10.2.0.1.0 - Production on 星期二 8月 21 10:33:35 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table T 5565 rows exported
Export terminated successfully without warnings.

C:>exit

14. 利用dmp檔案，恢復T表
SQL> drop table alex.t;

Table dropped.

SQL> $
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版權所有 1985-2000 Microsoft Corp.

C:>imp alex/alex file=d: .dmp fromuser=alex touser=alex

Import: Release 10.2.0.1.0 - Production on 星期二 8月 21 10:34:40 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ALEX's objects into ALEX
. . importing table "T" 5565 rows imported
Import terminated successfully without warnings.

C:>exit

15. 檢查T表，發現存在資料丟失
SQL> select count(*) from alex.t;

COUNT(*)
----------
5565

16. 關閉10231事件
SQL> alter session set events '10231 trace name context off';

Session altered.