数据文件坏块的制造和恢复

最新推荐文章于 2024-11-05 14:22:47 发布

csdn15515085103

最新推荐文章于 2024-11-05 14:22:47 发布

阅读量86

点赞数

文章标签：数据库操作系统

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

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

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

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.

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

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7319461/viewspace-964731/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7319461/viewspace-964731/