Hello All,
I just detected some corruption in my database after backup and started failing. After doing some checking, the table IDL_UB1$ is part fo the oracle data dictionary. I managed to fix one problem, however it doesn't see that anything was fixed.
Now I'm stuck with no with no useful old backup, and I cannot skip this table holds data object definitions. Can anybody advise what I can do. The database is operational, but I cannot execute a backup.
Thanks in advance
Jan S.
SQL> SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SYS',
OBJECT_NAME => 'IDL_UB1$',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;SQL> 2 3 4 5 6 7 8 9 10
11 /
number corrupt: 2
PL/SQL procedure successfully completed.
SQL> SELECT tablespace_name,owner,SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID = 1 AND 99913 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
TABLESPACE_NAME OWNER
------------------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYSTEM SYS
IDL_UB1$
TABLE
SQL> SET SERVEROUTPUT ON
SQL> DECLARE num_fix INT;
2 BEGIN
3 num_fix := 0;
4 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
5 SCHEMA_NAME => 'SYS',
6 OBJECT_NAME=> 'IDL_UB1$',
7 OBJECT_TYPE => dbms_repair.table_object,
8 REPAIR_TABLE_NAME => 'REPAIR_TABLE',
9 FIX_COUNT=> num_fix);
10 DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
11 END;
12 /
num fix: 0
PL/SQL procedure successfully completed.
SQL> SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT, CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION FROM REPAIR_TABLE;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
IDL_UB1$ 52561 6148 TRUE
mark block software corrupt
IDL_UB1$ 99913 6148 TRUE
mark block software corrupt
RMAN> validate datafile 1 check logical;
Starting validate at 23-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u02/oracle/oradata/INTM/system01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 FAILED 0 14751 111361 17567191
File Name: /u02/oracle/oradata/INTM/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 2 73188
Index 0 15480
Other 0 7941
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/intm/INTM/trace/INTM_ora_28393.trc for details
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 596
Finished validate at 23-JAN-14
RMAN> exit
Recovery Manager complete.
[oracle@ archivelog]$ dbv file=/u02/oracle/oradata/INTM/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Thu Jan 23 21:49:11 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u02/oracle/oradata/INTM/system01.dbf
Page 52561 is marked corrupt
Corrupt block relative dba: 0x0040cd51 (file 1, block 52561)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0040cd51
last change scn: 0x0000.00074138 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x41380602
check value in block header: 0xb13c
computed block checksum: 0x40
Page 99913 is marked corrupt
Corrupt block relative dba: 0x00418649 (file 1, block 99913)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x00418649
last change scn: 0x0000.000f0a68 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x0a680601
check value in block header: 0xbc13
computed block checksum: 0x40
DBVERIFY - Verification complete
Total Pages Examined : 111360
Total Pages Processed (Data) : 73186
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 15480
Total Pages Failing (Index): 0
Total Pages Processed (Other): 7941
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 14751
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 17567191 (0.17567191)
[oracle@ archivelog]$ sqlplus "/a sysdba"