分类目录归档：数据库

ORA-01578坏块解决(2)

发表于 2011 年 8 月 16 日由惜分飞

ORA-01578坏块解决(1)续集
如果在坏块之前，有rman备份，可以使用rman的备份来进行恢复，确保数据不会被丢失

1、使用rman进行恢复
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sun Aug 14 22:21:13 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TEST (DBID=2056006906)

RMAN> blockrecover datafile 6 block 1477;

Starting blockrecover at 2011-08-14 22:21:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /tmp/0fmk0ii5_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/tmp/0fmk0ii5_1_1 tag=TAG20110814T213357
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished blockrecover at 2011-08-14 22:21:23

2、检查坏块是否被恢复
RMAN> backup check logical validate datafile 6;

Starting backup at 2011-08-14 22:22:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-08-14 22:22:12

RMAN> exit

Recovery Manager complete.

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:17 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file#,block#,blocks from v$database_block_corruption;

no rows selected

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:38 2011

DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY – Verification complete

Total Pages Examined : 2560
Total Pages Processed (Data) : 1372
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 48
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1140
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 1256690 (0.1256690)

3、验证数据是否正确
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:34:18 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from t_rep;

COUNT(*)
———-
49857

和ORA-01578坏块解决(1)中的模拟环境比较，数据恢复正确，坏块问题解决

发表在 ORA-xxxxx, Oracle备份恢复 | 标签为 ORA-01578 | 评论关闭

ORA-01578坏块解决(1)

发表于 2011 年 8 月 15 日由惜分飞

一、创建测试表

SQL> create table t_rep as

2 select * from all_objects;

Table created.

SQL> select count(*) from t_rep;

COUNT(*)

———-

49857

二、使用bbed修改数据块

具体见bbed破坏数据文件

三、错误现象

1、sqlplus窗口

SQL> select count(*) from t_rep;

select count(*) from t_rep

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 1477)

ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’

2、alert.log文件中

Sun Aug 14 22:01:14 2011

Hex dump of (file 6, block 1477) in trace file /opt/oracle/admin/test/udump/test_ora_10785.trc

Corrupt block relative dba: 0x018005c5 (file 6, block 1477)

Bad check value found during buffer read

Data in bad block:

type: 6 format: 2 rdba: 0x018005c5

last change scn: 0×0000.001328ef seq: 0×2 flg: 0×04

spare1: 0×0 spare2: 0×0 spare3: 0×0

consistency value in tail: 0x28ef0602

check value in block header: 0×493

computed block checksum: 0x44b9

Reread of rdba: 0x018005c5 (file 6, block 1477) found same corrupted data

Sun Aug 14 22:01:15 2011

Corrupt Block Found

TSN = 6, TSNAME = XFF

RFN = 6, BLK = 1477, RDBA = 25167301

OBJN = 52727, OBJD = 52728, OBJECT = T_REP, SUBOBJECT =

SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment

四、验证是否真的坏块

1、dbv验证

[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:08:37 2011

DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf

Page 1477 is marked corrupt

Corrupt block relative dba: 0x018005c5 (file 6, block 1477)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x018005c5

last change scn: 0×0000.001328ef seq: 0×2 flg: 0×04

spare1: 0×0 spare2: 0×0 spare3: 0×0

consistency value in tail: 0x28ef0602

check value in block header: 0×493

computed block checksum: 0x44b9

DBVERIFY – Verification complete

Total Pages Examined : 2560

Total Pages Processed (Data) : 1371

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 48

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 1140

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Highest block SCN : 1256043 (0.1256043)

2、rman验证

RMAN> backup check logical validate datafile 6;

Starting backup at 2011-08-14 22:09:51

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2011-08-14 22:09:53

RMAN> exit

Recovery Manager complete.

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:10:00 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file#,block#,blocks from v$database_block_corruption;SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);

FILE# BLOCK# BLOCKS

———- ———- ———-

6 1477 1

五、跳过坏块读取其他数据

SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);

PL/SQL procedure successfully completed.

SQL> select skip_corrupt from dba_tables where table_name=’T_REP’;

SKIP_COR

——–

ENABLED

SQL> select count(*) from t_rep;

COUNT(*)

———-

49794

说明：数据发生丢失6号文件的1477块中的数据丢失

发表在 ORA-xxxxx, Oracle备份恢复 | 标签为 ORA-01578 | 一条评论

物化视图on prebuilt table

发表于 2011 年 8 月 14 日由惜分飞

1、ORA-12059
执行语句：
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
AS
SELECT * FROM SCOTT.emp

错误提示：
ORA-12059: prebuilt table “CHF”.”MV_T2″ does not exist

错误原因：
物化视图对应的表不存在（物化视图需要和表同名，结构相同）

解决方法：
CREATE TABLE mv_t2 AS
SELECT * FROM scott.emp WHERE 1=0;

2、ORA-23413
执行语句：
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on demand
AS
SELECT * FROM SCOTT.emp

错误提示：
ORA-23413: table “SCOTT”.”EMP” does not have a materialized view log

错误原因：
使用fast模式刷新物化视图，需要有物化视图日志

解决方法：
1）创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON scott.emp ;
2）刷新模式改为force，其实实质是采用了complete刷新模式

3、ORA-12058
执行语句（mv log是基于rowid，表无主键）
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on DEMAND
WITH ROWID
AS
SELECT * FROM SCOTT.emp

错误提示：
ORA-12058: materialized view cannot use prebuilt table

错误原因：
fast刷新模式不能基于rowid进行

解决方法：
1）删除mv log，表添加主键，采用基于主键模式重新建mv log和物化视图
DROP MATERIALIZED VIEW mv_t1;
ALTER TABLE mv_t2 ADD PRIMARY KEY (EMPNO);
CREATE MATERIALIZED VIEW LOG ON scott.emp;
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on DEMAND
–WITH ROWID
AS
SELECT * FROM SCOTT.emp;

2）刷新模式改为force，其实实质是采用了complete刷新模式

4、总结
1）在执行创建物化视图之前，需要先创建同名、同结构表
2）如果使用fast模式刷新（疑惑在线重定义可以通过rowid实现，为什么直接通过物化视图就不可以），物化视图同表需要主键，需要物化视图日志
3）通过drop mv，发现同名表还存在，和on prebuilt table本质区别

发表在 ORA-xxxxx | 评论关闭

17813235971

QQ 咨询

分类目录归档：数据库

ORA-01578坏块解决(2)

ORA-01578坏块解决(1)

物化视图on prebuilt table

文章分类

最近发表

分类目录归档：数据库

ORA-01578坏块解决(2)

ORA-01578坏块解决(1)

物化视图on prebuilt table

标签云

文章分类

最近发表