Chinaunix首页 | 论坛 | 博客
  • 博客访问: 5205296
  • 博文数量: 1004
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 13042
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(1004)

文章存档

2020年(1)

2019年(93)

2018年(208)

2017年(81)

2016年(49)

2015年(50)

2014年(170)

2013年(52)

2012年(177)

2011年(93)

2010年(30)

分类: Oracle

2011-12-27 20:25:10

环境:
OS:Linux Red Hat AS 5
DB Version:10GR2,数据库在非归档模式
 
步骤1:创建表空间
create tablespace tps_test
datafile '/u01/app/oracle/oradata/oracl/hxl01.dbf' size 16m reuse;
 
步骤2:创建用户
create user hxl identified by hxl default tablespace tps_test;
 
步骤3:授予权限给用户
grant create sequence to hxl;
grant create trigger  to hxl;
grant create cluster to hxl;
grant create procedure to hxl;
grant create type to hxl;
grant create session to hxl;
grant create operator to hxl;
grant create table to hxl;
grant create indextype to hxl;
alter user hxl quota unlimited on tps_test;
 
步骤4:创建表并写入数据
create table tb_test
(
 id number,
 createtime date default sysdate
);
 
declare
begin
 for i in 1 .. 1000 loop
   insert into tb_test(id) values(1);
   commit;
 end loop;
end;
 
步骤5:备份数据库
 
步骤6:关闭数据库
shutdown immediate
步骤7:将hxl01.dbf文件下载到本地使用ue修改后上传到服务器
找到需要修改的块
select
distinct
dbms_rowid.rowid_relative_fno(rowid) fileid,
dbms_rowid.rowid_block_number(rowid) bn
 from hxl.tb_test where id=1;
    FILEID         BN
---------- ----------
         5         12
         5         16
         5         15
         5         14
show parameters block_size; -- 块大小
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
 
SELECT 8*1024*12 FROM DUAL; -- 这里我们需要修改第12个快
98304
 
SELECT TO_CHAR(98304, 'XXXXXX') FROM DUAL;
  18000  -- 在ue里找到第12个快对应的地址,在后面修改几个值
 
步骤8:检查坏块
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/oracl/hxl01.dbf
Page 12 is marked corrupt
Corrupt block relative dba: 0x0140000c (file 5, block 12)
Bad header found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x23928978
 last change scn: 0x0000.00105544 seq: 0x1 flg: 0x04
 spare1: 0x55 spare2: 0x66 spare3: 0x0
 consistency value in tail: 0x28ee0601
 check value in block header: 0x51ef
 computed block checksum: 0xb059
步骤9:启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1220820 bytes
Variable Size 167776044 bytes
Database Buffers 432013312 bytes
Redo Buffers 7163904 bytes
Database mounted
Database opened
--这个时候不提示错误
步骤10:校验数据文件(数据库在非归档模式,不能在open状态下执行该命令)
RMAN> backup validate datafile 5; -- 这是一个在RMAN中执行的命令,用于检查数据库的逻辑错误和物理错误
Starting backup at 27-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/27/2011 09:48:59
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
 
步骤11:将数据库置于mount模式
shutdown immediate
startup mount
 
步骤12:在mount模式下校验数据文件
RMAN> backup validate datafile 5;
Starting backup at 27-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oradata/oracl/hxl01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 27-DEC-11
 
步骤13.查看alert.log文件可以发现有坏块
Hex dump of (file 5, block 1482) in trace file /u01/app/oracle/admin/oracl/udump/oracl_ora_5687.trc
Corrupt block relative dba: 0x0140000c (file 5, block 12)
Bad check value found during backing up datafile
Data in bad block:
 type: 0 format: 2 rdba: 0x000005ca
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000001
 check value in block header: 0xa2ca
 computed block checksum: 0x3620
Reread of blocknum=12, file=/u01/app/oracle/oradata/oracl/hxl01.dbf. found same corrupt data
Reread of blocknum=12, file=/u01/app/oracle/oradata/oracl/hxl01.dbf. found same corrupt data
Reread of blocknum=12, file=/u01/app/oracle/oradata/oracl/hxl01.dbf. found same corrupt data
Reread of blocknum=12, file=/u01/app/oracle/oradata/oracl/hxl01.dbf. found same corrupt data
Reread of blocknum=12, file=/u01/app/oracle/oradata/oracl/hxl01.dbf. found same corrupt data
 
步骤14:恢复坏块
blockrecover datafile 5 block 12 from backupset;
 
步骤15:再次检查数据文件,发现坏块已经恢复.
[oracle@hxl oracl]$ dbv file=/u01/app/oracle/oradata/oracl/hxl01.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Tue Dec 27 10:06:22 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/oracl/hxl01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 2048
Total Pages Processed (Data) : 5
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 11
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2032
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 1053395 (0.1053395)
 
备注:
数据库中有坏块的情况下备份会有错误:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c2 channel at 12/29/2011 03:17:08
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/oracl/hxl01.dbf
如下命令可以设置rman备份能够允许出现坏块的最大数.(尽量不要使用,我们最好确保备份的数据全是完好的)
set maxcorrupt for datafile 5 to 10;

通过块号找对应的segment

SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where
file_id =
AND between block_id and (block_id + blocks - 1)


阅读(3195) | 评论(0) | 转发(0) |
0

上一篇:表空间相关

下一篇:恢复损坏的数据文件

给主人留下些什么吧!~~