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  |
pradies
Messages: 250
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 #565518 is a reply to message #565516] |
Wed, 05 September 2012 00:37  |
pradies
Messages: 250
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 #565688 is a reply to message #565528] |
Thu, 06 September 2012 04:53 |
pradies
Messages: 250
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 #565701 is a reply to message #565692] |
Thu, 06 September 2012 05:58 |
pradies
Messages: 250
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 #565717 is a reply to message #565704] |
Thu, 06 September 2012 07:00 |
pradies
Messages: 250
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 #565719 is a reply to message #565717] |
Thu, 06 September 2012 07:10 |
John Watson
Messages: 8960
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 |
pradies
Messages: 250
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
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Dec 01 14:22:34 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
