OlegON
Дата: 21.11.2009 15:47:14
Подарили мне тут базу, в принципе, привычно убитая. Бекапов нет, не в архивлоге (была). Пытаюсь привести в чувство. Не могу понять, что же внутри побилось и как лечить?
D:\ORACLE\ORADATA\YUBILEY>dbv file=SYSTEM01.DBF
DBVERIFY: Release 10.2.0.4.0 - Production on Sat Nov 21 15:32:50 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = SYSTEM01.DBF
Page 11080 is influx - most likely media corrupt
Corrupt block relative dba: 0x00402b48 (file 1, block 11080)
Fractured block found during dbv:
Data in bad block:
type: 0 format: 2 rdba: 0x00002b48
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x22151604
check value in block header: 0x8c48
computed block checksum: 0xfb16
DBVERIFY - Verification complete
Total Pages Examined : 70400
Total Pages Processed (Data) : 44615
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 8026
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1650
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 16108
Total Pages Marked Corrupt : 1
Total Pages Influx : 1
Highest block SCN : 135808262 (0.135808262)
D:\ORACLE\ORADATA\YUBILEY>rman target / nocatalog
Recovery Manager: Release 10.2.0.4.0 - Production on Sat Nov 21 15:33:23 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: YUBILEY (DBID=2528078198)
using target database control file instead of recovery catalog
RMAN> backup validate check logical database;
Starting backup at 21-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=138 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=D:\ORACLE\ORADATA\YUBILEY\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\YUBILEY\INDX01.DBF
input datafile fno=00005 name=D:\ORACLE\ORADATA\YUBILEY\USERS01.DBF
input datafile fno=00001 name=D:\ORACLE\ORADATA\YUBILEY\SYSTEM01.DBF
input datafile fno=00006 name=D:\ORACLE\ORADATA\YUBILEY\SINHRA.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\YUBILEY\SYSAUX01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 21-NOV-09
RMAN> backup database;
Starting backup at 21-NOV-09
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=00002 name=D:\ORACLE\ORADATA\YUBILEY\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\YUBILEY\INDX01.DBF
input datafile fno=00005 name=D:\ORACLE\ORADATA\YUBILEY\USERS01.DBF
input datafile fno=00001 name=D:\ORACLE\ORADATA\YUBILEY\SYSTEM01.DBF
input datafile fno=00006 name=D:\ORACLE\ORADATA\YUBILEY\SINHRA.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\YUBILEY\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 21-NOV-09
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/21/2009 15:38:13
ORA-19566: exceeded limit of 0 corrupt blocks for file D:\ORACLE\ORADATA\YUBILEY\SYSTEM01.DBF
--------alert
Sat Nov 21 15:38:10 2009
Hex dump of (file 1, block 11080) in trace file c:\oracle\product\10.2.0\admin\yubiley\udump\yubiley_ora_5888.trc
Corrupt block relative dba: 0x00402b48 (file 1, block 11080)
Fractured block found during backing up datafile
Data in bad block:
type: 0 format: 2 rdba: 0x00002b48
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x22151604
check value in block header: 0x8c48
computed block checksum: 0xfb16
Reread of blocknum=11080, file=D:\ORACLE\ORADATA\YUBILEY\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=11080, file=D:\ORACLE\ORADATA\YUBILEY\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=11080, file=D:\ORACLE\ORADATA\YUBILEY\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=11080, file=D:\ORACLE\ORADATA\YUBILEY\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=11080, file=D:\ORACLE\ORADATA\YUBILEY\SYSTEM01.DBF. found same corrupt data
------------
SQL> SELECT tablespace_name, segment_type, owner, segment_name
2 FROM dba_extents
3 WHERE file_id = &AFN
4 and &BL between block_id AND block_id + blocks - 1
5 ;
Enter value for afn: 1
old 3: WHERE file_id = &AFN
new 3: WHERE file_id = 1
Enter value for bl: 11080
old 4: and &BL between block_id AND block_id + blocks - 1
new 4: and 11080 between block_id AND block_id + blocks - 1
no rows selected
SQL> SELECT owner, segment_name, segment_type, partition_name
2 FROM dba_segments
3 WHERE header_file = &AFN
4 and header_block = &BL
5 ;
Enter value for afn: 1
old 3: WHERE header_file = &AFN
new 3: WHERE header_file = 1
Enter value for bl: 11080
old 4: and header_block = &BL
new 4: and header_block = 11080
no rows selected
SQL> select * from v$database_block_corruption;
no rows selected
есть какие-то идеи?
OlegON
Дата: 21.11.2009 17:01:56
Помог
select
ds.owner, ds.segment_name, ds.partition_name, ds.segment_type,
ds.tablespace_name,
e.ktfbueextno, f.file#, e.ktfbuebno,
e.ktfbueblks * ds.blocksize, e.ktfbueblks, e.ktfbuefno
from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f
where e.ktfbuesegfno = ds.relative_fno
and e.ktfbuesegbno = ds.header_block
and e.ktfbuesegtsn = ds.tablespace_id
and e.ktfbuesegtsn = f.ts#
and e.ktfbuefno = f.relfile#
and f.file$=1
and 11080 between e.ktfbuebno and e.ktfbuebno + e.ktfbueblks - 1;
вывел PK, выключил/включил констрейнт - наступило счастье.