學習blockrecover

備份users表空間
RMAN> run {
2> backup datafile 4;
3> }

Starting backup at 04-APR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/u01/app/oracle/oradata/db10g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-APR-09
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/04/2009 10:18:50
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/db10g/users01.dbf
由於以前毀壞過user表空間上的資料塊，在備份資料檔案的時候出現上述問題
Corrupt block relative dba: 0x0100840c (file 4, block 33804)
Bad header found during backing up datafile
Data in bad block:
type: 255 format: 7 rdba: 0x00800011
last change scn: 0x0000.004a496b seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x496b2002
check value in block header: 0x66bc
computed block checksum: 0x0
Reread of blocknum=33804, file=/u01/app/oracle/oradata/db10g/users01.dbf. found same corrupt data
Reread of blocknum=33804, file=/u01/app/oracle/oradata/db10g/users01.dbf. found same corrupt data
Reread of blocknum=33804, file=/u01/app/oracle/oradata/db10g/users01.dbf. found same corrupt data
Reread of blocknum=33804, file=/u01/app/oracle/oradata/db10g/users01.dbf. found same corrupt data
Reread of blocknum=33804, file=/u01/app/oracle/oradata/db10g/users01.dbf. found same corrupt data
最簡單的辦法，把這個資料檔案用滿

SQL> insert into test select * from test;
insert into test select * from test
*
ERROR at line 1:
ORA-01653: unable to extend table APPS.TEST by 128 in tablespace USERS

select file_id, block_id from dba_extents where segment_name = 'TEST' AND OWNER='APPS';

4 33609
4 33617
4 33625
4 33633
4 33641
4 33649
4 33657
4 33665
4 33673
4 33681
4 33689
4 33697
4 33705
4 33713
4 33721
4 33729
4 33801
4 33929
4 34057
4 34185
4 34313

[oracle@server ~]$ dbv file=/u01/app/oracle/oradata/db10g/users01.dbf

Total Pages Examined : 34560
Total Pages Processed (Data) : 30193
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2198
Total Pages Failing (Index): 0
Total Pages Processed (Other): 580
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1589
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 5623745 (0.5623745)



bbed parfile=par.bbd blocksize=8192

copy file 4 block 33617 to file 4 block 33609
copy file 4 block 33617 to file 4 block 33642
copy file 4 block 33617 to file 4 block 33643
copy file 4 block 33617 to file 4 block 33673
sum apply


Page 33609 is marked corrupt
Corrupt block relative dba: 0x01008349 (file 4, block 33609)
Bad header found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01008351
last change scn: 0x0000.00552bc0 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x2bc00602
check value in block header: 0x8608
computed block checksum: 0x0

Page 33673 is marked corrupt
Corrupt block relative dba: 0x01008389 (file 4, block 33673)
Bad header found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01008351
last change scn: 0x0000.00552bc0 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x2bc00602
check value in block header: 0x8608
computed block checksum: 0x0



DBVERIFY - Verification complete

Total Pages Examined : 34560
Total Pages Processed (Data) : 30193
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2198
Total Pages Failing (Index): 0
Total Pages Processed (Other): 578
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1589
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 5623868 (0.5623868)

SQL> alter system flush buffer_cache;

System altered.

SQL> select /*+full(a)*/ count(*) from apps.test a;
select /*+full(a)*/ count(*) from apps.test a
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 33642)
ORA-01110: data file 4: '/u01/app/oracle/oradata/db10g/users01.dbf'

RMAN> run {blockrecover datafile 4 block 33642;}

Starting blockrecover at 04-APR-09
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/dbs/03kbka5b_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/03kbka5b_1_1 tag=TAG20090404T102547
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:16

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

Finished blockrecover at 04-APR-09

RMAN> run {blockrecover datafile 4 block 33643;}

Starting blockrecover at 04-APR-09
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/dbs/03kbka5b_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/03kbka5b_1_1 tag=TAG20090404T102547
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:15

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

Finished blockrecover at 04-APR-09


SQL> select /*+full(a)*/ count(*) from apps.test a;
select /*+full(a)*/ count(*) from apps.test a
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 33642)
ORA-01110: data file 4: '/u01/app/oracle/oradata/db10g/users01.dbf'


SQL> select /*+full(a)*/ count(*) from apps.test a;
select /*+full(a)*/ count(*) from apps.test a
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 33643)
ORA-01110: data file 4: '/u01/app/oracle/oradata/db10g/users01.dbf'

SQL> select /*+full(a)*/ count(*) from apps.test a;

COUNT(*)
----------
31280

[@more@]