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；
