诊断 Oracle 数据库问题

诊断 e Oracle 数据库问题

show parameter max_dump_file_size

NAME TYPE VALUE

max_dump_file_size string UNLIMITED

第 23 页

转储文件大小限制

select internal_metric_name from v$alert_types;

INTERNAL_METRIC_NAME

Quota

Data_Object

Rollback_Segment

Rollback_Segment

pctUsed

bytesFree

Tablespace

Tablespace

...

INTERNAL_METRIC_NAME

Service_Up

Service_Down

Service_Member_Up

Service_Member_Down

Service_Preconnect_Up

Service_Preconnect_Down

已选择 149 行。

服务器告警的指标

select reason,object_type,object_name from dba_outstanding_alerts;

SQL> select reason,object_type,object_name from dba_outstanding_alerts;

未选定行

未处理的服务器告警

…

检测与恢复数据块

相关参数

DB_BLOCK_CHECKSUM

show parameter db_block_checksum

NAME TYPE VALUE

db_block_checksum string TRUE

db_block_checksum 值为 TRUE 时，写入块时计算校验值并包含在块头，读取时验证校验值；值为 FALSE 时无操作

注：SYSTEM 表空间始终会启用数据块检查

DB_BLOCK_CHECKING

show parameter db_block_checking

NAME TYPE VALUE

db_block_checking string FALSE

db_block_checking 值为 TRUE 时，每次访问缓冲区中数据块时检查一致性（CPU 代价不小，一般不推荐）

检测与恢复数据块

模拟数据块损坏

create tablespace tbs datafile 'E:\OracleFiles\DatabaseFiles\WWW\TBS01.DBF' size 1M;

create table users tablespace tbs as select * from dba_users;

insert into users select * from users;

commit;

alter system checkpoint;

select count(*) from users;

COUNT(*)

46

alter tablespace tbs offline;

用十六进制编辑器修改一些字符

注：这里检查点为了把数据写入，而在线离线为了能使数据块错误浮现

alter tablespace tbs online;

第 24 页

select count(*) from users;

SQL> select count(*) from users;

select count(*) from users

*

第 1 行出现错误:

ORA-01578: ORACLE 数据块损坏 (文件号 11, 块号 12)

ORA-01110: 数据文件 11: 'E:\ORACLEFILES\DATABASEFILES\WWW\TBS01.DBF'

数据块校验失败

Hex dump of (file 11, block 12) in trace file c:\oracle\product\10.2.0\admin\www\udump\www_ora_1256.trc

Corrupt block relative dba: 0x02c0000c (file 11, block 12)

Bad check value found during buffer read

Data in bad block:

type: 6 format: 2 rdba: 0x02c0000c

last change scn: 0x0000.00138635 seq: 0x2 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x86350602

check value in block header: 0x892b

computed block checksum: 0xc51

Reread of rdba: 0x02c0000c (file 11, block 12) found same corrupted data

Thu Jun 26 10:50:51 2008

Corrupt Block Found

TSN = 20, TSNAME = TBS

RFN = 11, BLK = 12, RDBA = 46137356

OBJN = 52156, OBJD = 52156, OBJECT = USERS, SUBOBJECT =

SEGMENT OWNER = TEST, SEGMENT TYPE = Table Segment

alert 文件包含的出错信息（包含了表空间名、文件号、块号、对象名、对象类型等信息）