Home » RDBMS Server » Server Administration » ORA-01578: ORACLE data block corrupted (file # 2, % (Oracle 11g (11.1.0.6) - RHEL 5 )
ORA-01578: ORACLE data block corrupted (file # 2, % [message #565443] Tue, 04 September 2012 08:00 Go to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi,

I am having 3 oracle database instances running on 3 seperate Linux Node(RHEL Node).

Instance -1 named - DS
Instance -2 named - MIS
Instance -3 named - OAS

Among of these 3 nodes, we are facing Block Corruption issues with sysaux tablespace.

Error in Instance name DS is

Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_j000_655388.trc  (incident=300847):
ORA-01578: ORACLE data block corrupted (file # 2, block # 38428)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_j000_655388.trc:
ORA-20011: Approximate NDV failed: ORA-01578: ORACLE data block corrupted (file # 2, block # 38428)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_j000_655388.trc  (incident=300848):
ORA-01578: ORACLE data block corrupted (file # 2, block # 38428)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Sun May 03 14:04:50 2009
Sweep Incident[300848]: completed
Sweep Incident[300847]: completed
Sun May 03 14:44:19 2009
Thread 1 advanced to log sequence 7359
  Current log# 2 seq# 7359 mem# 0: /DSdb2/dslog2.dbf
Sun May 03 15:00:51 2009
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_m000_389150.trc  (incident=300934):
ORA-01578: ORACLE data block corrupted (file # 2, block # 14139)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_m000_389150.trc  (incident=300935):
ORA-01578: ORACLE data block corrupted (file # ORA-01578: ORACLE data block corrupted (file # 2, block # 14139)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
, block # )
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_m000_389150.trc  (incident=300936):
ORA-01578: ORACLE data block corrupted (file # 2, block # 16344)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_m000_389150.trc  (incident=300937):
ORA-01578: ORACLE data block corrupted (file # ORA-01578: ORACLE data block corrupted (file # 2, block # 16344)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
, block # )




Error in Instance name DS is

Errors in file /u01/home/dba/oracle/diag/rdbms/mis4db/MIS/trace/MIS_m000_2871384.trc  (incident=66120):
ORA-01578: ORACLE data block corrupted (file # 2, block # 15162)
ORA-01110: data file 2: '/MISdb/missysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /u01/home/dba/oracle/diag/rdbms/mis4db/MIS/incident/incdir_66120/MIS_m000_2871384_i66120.trc
Tue Apr 17 08:00:34 2012
Trace dumping is performing id=[cdmp_20120417080034]
Errors in file /u01/home/dba/oracle/diag/rdbms/mis4db/MIS/trace/MIS_m000_2871384.trc  (incident=66121):
ORA-01578: ORACLE data block corrupted (file # ORA-01578: ORACLE data block corrupted (file # 2, block # 15162)
ORA-01110: data file 2: '/MISdb/missysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
, block # )
Incident details in: /u01/home/dba/oracle/diag/rdbms/mis4db/MIS/incident/incdir_66121/MIS_m000_2871384_i66121.trc
Tue Apr 17 08:00:34 2012
Sweep Incident[66120]: completed


for this I googled and found some solution as oracle doc [430230.1] related to sysaux couruption.

After this again we are facing the same issue in sysaux tablespace.

Can someone tell me why this corruption is occuring and what we can do to resolve this.

thanks in Advance.

Pradeep



Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565447 is a reply to message #565443] Tue, 04 September 2012 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-26040: Data block was loaded using the NOLOGGING option\n
* Cause: Trying to access data in block that was loaded without
*        redo generation using the NOLOGGING/UNRECOVERABLE option
* Action: Drop the object containing the block.

What is the object?

Regards
Michel
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565512 is a reply to message #565447] Tue, 04 September 2012 23:51 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal,

Thanks for your reply.

When I am searching for the database object in which corruption is coming, mostly objects are sys owned and all are LOB segments.

If you require then I can provide you the exact segment name.


Regards

Pradeep
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565516 is a reply to message #565512] Wed, 05 September 2012 00:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, give the query and result that gives the corrupted objects.

Regards
Michel
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565518 is a reply to message #565516] Wed, 05 September 2012 00:37 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal,

As required by you, I am providing information related to latest corrupted block on a DS instance.

Wed Sep 05 06:00:40 2012
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_m000_3068024.trc  (incident=1354841):
ORA-01578: ORACLE data block corrupted (file # 2, block # 33663)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_m000_3068024.trc  (incident=1354842):
ORA-01578: ORACLE data block corrupted (file # ORA-01578: ORACLE data block corrupted (file # 2, block # 33663)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
, block # )
Wed Sep 05 06:00:40 2012
Sweep Incident[1354842]: completed
Sweep Incident[1354841]: completed



And object which is courrupted is as follows

SQL> l
  1  SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME
  2  FROM DBA_EXTENTS
  3  WHERE FILE_ID = 2 AND 33663 BETWEEN BLOCK_ID
  4* AND BLOCK_ID+BLOCKS -1
SQL> /

SEGMENT_TYPE       OWNER||'.'||SEGMENT_NAME
------------------ ----------------------------------------------------------------------------------------------------------------
LOBSEGMENT         SYS.SYS_LOB0000005979C00038$$


Content of the trace files(/u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_m000_3068024.trc)

Trace file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_m000_3068024.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/home/dba/oracle/product/11.1.0
System name:    AIX
Node name:      mt1be3a
Release:        3
Version:        5
Machine:        0002171AD300
Instance name: DS
Redo thread mounted by this instance: 1
Oracle process number: 54
Unix process pid: 3068024, image: oracle@mt1be3a (m000)


*** 2012-09-05 06:00:40.071
*** SESSION ID:(81.59543) 2012-09-05 06:00:40.071
*** CLIENT ID:() 2012-09-05 06:00:40.071
*** SERVICE NAME:(SYS$BACKGROUND) 2012-09-05 06:00:40.071
*** MODULE NAME:(MMON_SLAVE) 2012-09-05 06:00:40.071
*** ACTION NAME:(Auto-Flush Slave Action) 2012-09-05 06:00:40.071

DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
----- DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- (Action duration in csec: 0) -----
----- END DDE Actions Dump -----
DDE: Problem Key 'ORA 1578' was flood controlled (0x4) (incident: 1354841)
ORA-01578: ORACLE data block corrupted (file # 2, block # 33663)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Action (ID=34340888) was flood controlled by a FC Qualifier
*** KEWROCISTMTEXEC - encountered error: (ORA-01578: ORACLE data block corrupted (file # 2, block # 33663)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
)
  *** SQLSTR: total-len=1887, dump-len=240,
      STR={INSERT INTO wrh$_sql_plan sp         (snap_id, dbid, sql_id, plan_hash_value, id,          operation, options, object_node, object#, object_owner,          o
bject_name, object_alias, object_type,          optimizer, parent_id, depth, positi}

*** 2012-09-05 06:00:40.347
DDE: Problem Key 'ORA 1578' was flood controlled (0x4) (incident: 1354842)
ORA-01578: ORACLE data block corrupted (file # ORA-01578: ORACLE data block corrupted (file # 2, block # 33663)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
, block # )
Action (ID=34340888) was flood controlled by a FC Qualifier
*** KEWRAFM1: Error=13509 encountered by kewrfteh




thanks

Pradeep
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565519 is a reply to message #565518] Wed, 05 September 2012 00:42 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michal,

One more thing I want to tell you, which output I provided you this corruption is coming on the oracle instance running on AIX environment.

but the problem is same as in Linux env.


Regards.

Pradeep
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565522 is a reply to message #565519] Wed, 05 September 2012 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the LOB belongs to AWR (WRH$) table, you can empty the LOB.
I see only 2 fields WRH$_SQLTEXT.SQL_TEXT (too bad) and WRH$_SQL_PLAN.OTHER_XML (not so important).
I can't see when there are loaded with NOLOGGING option.

Regards
Michel
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565523 is a reply to message #565522] Wed, 05 September 2012 01:55 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michel,

Thanks for your reply

Quote:
I can't see when there are loaded with NOLOGGING option.


Kindly put more light on this ......


Regards

Pradeep

Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565524 is a reply to message #565523] Wed, 05 September 2012 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Kindly put more light on this .....

But I just said I can't (typo: "there" -> "they").

Regards
Michel
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565525 is a reply to message #565524] Wed, 05 September 2012 02:05 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
So should I convert database mode to logging mode or convert logging mode for this tablespace(sysaux)??

Thanks

Pradeep
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565528 is a reply to message #565525] Wed, 05 September 2012 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should investigate why and when they are loaded with NOLOGGING option.
Of course, you can also set FORCE LOGGING on this tablespace.

Regards
Michel
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565688 is a reply to message #565528] Thu, 06 September 2012 04:53 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi Michel,

I started force logging for the sysaux tablespace.

But After again I am getting the same corruption.

Thu Sep 06 11:38:31 2012
ALTER TABLESPACE SYSAUX force logging
Completed: ALTER TABLESPACE SYSAUX force logging
Thu Sep 06 11:43:13 2012
Errors in file /home/oracle/diag/rdbms/ds4db/DS/trace/DS_m001_7313.trc  (incident=49060):
ORA-01578: ORACLE data block corrupted (file # 2, block # 3380)
ORA-01110: data file 2: '/DSdb/1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /home/oracle/diag/rdbms/ds4db/DS/incident/incdir_49060/DS_m001_7313_i49060.trc
Thu Sep 06 11:43:29 2012
Non critical error ORA-48913 caught while writing to trace file "/home/oracle/diag/rdbms/ds4db/DS/incident/incdir_49060/DS_m001_7313_i49060.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached
Writing to the above trace file is disabled for now on...
Thu Sep 06 11:43:29 2012
Trace dumping is performing id=[cdmp_20120906114329]
Thu Sep 06 11:44:07 2012
Sweep [inc][49060]: completed
Sweep [inc2][49060]: completed



Regards

Pradeep

Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565692 is a reply to message #565688] Thu, 06 September 2012 05:12 Go to previous messageGo to next message
John Watson
Messages: 4683
Registered: January 2010
Location: Global Village
Senior Member
Pradeep, in your first post you gave the MOS article number that describes exactly what to do to fix this problem. Did you do it?
And in any case, one damaged block in a part of the AWR that you are not using is not a problem. Just ignore it.
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565701 is a reply to message #565692] Thu, 06 September 2012 05:58 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
hi John,

Yes John,

I have already performed the step given in MOS art - 430230.1.

Quote:
one damaged block in a part of the AWR that you are not using is not a problem. Just ignore it.


We are getting this is on most of instance not on the single one. So want to ask why this courrption is coming and what we can do to rosolve this.

Simply ignore or any solution.

Currently I am working on the same test instance according to Metalink ID --

ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors [ID 293515.1]

But when I am performing step-1 for fixing this I am facing error in pl/sql running.
Thu Sep 06 11:43:13 2012
Errors in file /home/oracle/diag/rdbms/ds4db/DS/trace/DS_m001_7313.trc  (incident=49060):
ORA-01578: ORACLE data block corrupted (file # 2, block # 3380)
ORA-01110: data file 2: '/DSdb/1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /home/oracle/diag/rdbms/ds4db/DS/incident/incdir_49060/DS_m001_7313_i49060.trc
Thu Sep 06 11:43:29 2012
Non critical error ORA-48913 caught while writing to trace file "/home/oracle/diag/rdbms/ds4db/DS/incident/incdir_49060/DS_m001_7313_i49060.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached
Writing to the above trace file is disabled for now on...
Thu Sep 06 11:43:29 2012
Trace dumping is performing id=[cdmp_20120906114329]
Thu Sep 06 11:44:07 2012
Sweep [inc][49060]: completed
Sweep [inc2][49060]: completed

[oracle@vmis2-mg2a trace]$ sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 6 12:33:04 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

column segment_name for a30
column segment_type for a30
column column_name for a30
SQL> set line 120
SQL>
SQL>
SQL>
SQL> select owner, segment_name, segment_type from dba_extents where file_id = 2 and 3380 between block_id and block_id + blocks - 1;

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------ ------------------------------
SYS                            SYS_LOB0000006062C00008$$      LOBSEGMENT

SQL> select table_name, column_name from   dba_lobs where  segment_name = 'SYS_LOB0000006062C00008$$' and    owner = 'SYS';

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
WRI$_DBU_FEATURE_USAGE         FEATURE_INFO

SQL> create table corrupted_data (corrupted_rowid rowid);

Table created.

SQL> select * from corrupted_data;

no rows selected

SQL> set concat off
SQL> declare
error_1578 exception;
  3  pragma exception_init(error_1578,-1578);
  4  n number;
  5  begin
for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop
  7  begin
  8  n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
  9  exception
when error_1578 then
 11  insert into corrupted_data values (cursor_lob.r);
 12  commit;
 13  end;
 14  end loop;
 15  end;
 16  /
Enter value for lob_column: undefine lob_column
Enter value for table_owner: SYS
Enter value for table_with_lob: WRI$_DBU_FEATURE_USAGE
old   6: for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop
new   6: for cursor_lob in (select rowid r, undefine lob_column  from SYS.WRI$_DBU_FEATURE_USAGE) loop
old   8: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
new   8: n:=dbms_lob.instr(cursor_lob.undefine lob_column ,hextoraw('889911')) ;
n:=dbms_lob.instr(cursor_lob.undefine lob_column ,hextoraw('889911')) ;
                                      *
ERROR at line 8:
ORA-06550: line 8, column 39:
PLS-00103: Encountered the symbol "LOB_COLUMN" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec as between from using || multiset member
submultiset
The symbol "." was substituted for "LOB_COLUMN" to continue.


Kindly help me to rectify this.


regards

Pradeep


Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565704 is a reply to message #565701] Thu, 06 September 2012 06:11 Go to previous messageGo to next message
John Watson
Messages: 4683
Registered: January 2010
Location: Global Village
Senior Member
You've messed up your use of & replacement variables, probably from trying to run the script more than once in one session. Just replace them in the script with the actual values.
But, as I said, I would ignore the problem. It doesn't matter.
Up to you.
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565717 is a reply to message #565704] Thu, 06 September 2012 07:00 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi,

Now I run this pl/sql on session and the output is nothing.

SQL> set concat off
declare
  2  error_1578 exception;
  3  pragma exception_init(error_1578,-1578);
  4  n number;
  5  begin
for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop
  7  begin
  8  n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
  9  exception
 10  when error_1578 then
 11  insert into corrupted_data values (cursor_lob.r);
commit;
end;
 14  end loop;
 15  end;
 16  /
Enter value for lob_column: FEATURE_INFO
Enter value for table_owner: SYS
Enter value for table_with_lob: WRI$_DBU_FEATURE_USAGE
old   6: for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop
new   6: for cursor_lob in (select rowid r, FEATURE_INFO from SYS.WRI$_DBU_FEATURE_USAGE) loop
old   8: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
new   8: n:=dbms_lob.instr(cursor_lob.FEATURE_INFO,hextoraw('889911')) ;

PL/SQL procedure successfully completed.

SQL> select * from corrupted_data;

no rows selected

SQL> select corrupted_rowid from corrupted_data;

no rows selected





Quote:

But, as I said, I would ignore the problem. It doesn't matter.
Up to you.


we are having alert on the database when I am getting error in alert log an alert is generated. So we can't ignore it.

There will be some solution.



Regards

Pradeep




Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565718 is a reply to message #565717] Thu, 06 September 2012 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There will be some solution.


Truncate the table.

Regards
Michel
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565719 is a reply to message #565717] Thu, 06 September 2012 07:10 Go to previous messageGo to next message
John Watson
Messages: 4683
Registered: January 2010
Location: Global Village
Senior Member
If your alert is raising an error when there is in fact nothing wrong, you should consider this a false positive and reconfigure your alert system to ignore it.

But if you really want to get rid of the LOB that covers the corrupted block, think about what that little script is doing. It is reading all the LOBs in the table, and writing out the rowid of the lob with the error. If you can't make the script work, I've already suggested that you edit it to replace the &s and &&s with hardcoded values. Why don't you do that? Or just write your own query that will do the same thing?
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565733 is a reply to message #565719] Thu, 06 September 2012 07:47 Go to previous messageGo to next message
pradies
Messages: 242
Registered: May 2008
Senior Member
Hi John,

As you told me to replace & with hardcore values I tried with the same but not any result.

SQL> set concat off
SQL> declare
error_1578 exception;
  3  pragma exception_init(error_1578,-1578);
  4  n number;
  5  begin
for cursor_lob in (select rowid r, FEATURE_INFO from SYS.WRI$_DBU_FEATURE_USAGE) loop
  7  begin
  8  n:=dbms_lob.instr(cursor_lob.FEATURE_INFO,hextoraw('889911')) ;
  9  exception
 10  when error_1578 then
insert into corrupted_data values (cursor_lob.r);
 12  commit;
end;
 14  end loop;
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL> select count(1) from corrupted_data;

  COUNT(1)
----------
         0

SQL>



Kindly suggest further what we can do .... is there not any solution other than truncating the object?? -- "AS Michel Said"


Regards

Pradeep
Re: ORA-01578: ORACLE data block corrupted (file # 2, % [message #565735 is a reply to message #565733] Thu, 06 September 2012 07:52 Go to previous message
John Watson
Messages: 4683
Registered: January 2010
Location: Global Village
Senior Member
So you can select the entire table without hitting a problem. What are you doing to get the supposed error?
Previous Topic: insufficient privileges for sys
Next Topic: CLOB size
Goto Forum:
  


Current Time: Thu Oct 23 03:03:33 CDT 2014

Total time taken to generate the page: 0.17486 seconds