Home » RDBMS Server » Backup & Recovery » Encounter ORA-19566 and ORA-19599 during scheduled backup (merged) (11.2.0.4 SE, Redhat 7.0)
Encounter ORA-19566 and ORA-19599 during scheduled backup (merged) [message #652824] Mon, 20 June 2016 09:16 Go to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear all,

I encounter the following in the backup log:

in the backup database log:

RMAN-03009: failure of backup command on c1 channel at 06/13/2016 05:50:20
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/munchen/indx401.dbf

in the archived backup log

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c1 channel at 06/13/2016 22:06:35
ORA-19599: block number 498575 is corrupt in archived log /u06/app/oracle/fast_recovery_area/MUNCHEN/archivelog/2016_06_13/o1_mf_1_6981_coxk2g4o_.arc


I have run validation of both database and archivelog

3> CONFIGURE CONTROLFILE AUTOBACKUP ON;
4> RUN{
13> VALIDATE DATABASE PLUS ARCHIVELOG CHECK LOGICAL;
14> }

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/diag/rdbms/munchen/munchen/trace/munchen_ora_31523.trc for details


have found out what is the corrupted block


SYS@munchen>select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO                   
---------- ---------- ---------- ------------------ ---------                   
        64    3293942         14                  0 ALL ZERO

have found out which tablespace


SYS@munchen>SELECT tablespace_name, file_name, dbc.file# FROM dba_data_files df JOIN v$database_block_corruption dbc
  2  ON dbc.file#= df.file_id;

TABLE FILE_NAME                                     FILE#                       
----- --------------------------------------------- -----                       
INDX2 /u01/app/oracle/oradata/munchen/indx401.dbf      64


Q1: So what should I do next to recover the index file as well as corrupted archivelog?

while it is easy to understand disk /u01 has problems, is there any problems with /u06 where archivelog resides?

what causes
 ORA-19599: block number 498575 is corrupt in archived log /u06/app/oracle/fast_recovery_area/MUNCHEN/archivelog/2016_06_13/o1_mf_1_6981_coxk2g4o_.arc


Q2 Is it the underlying disk /u06 or the index files that is causing archivelog to be corrupted?

Q3 how do know which SCN can I recover to?

Q4 also in the long run, should i change the problematic disk as soon as possible?

thanks a lot in advance!
Re: Encounter ORA-19566 and ORA-19599 during scheduled backup [message #652825 is a reply to message #652824] Mon, 20 June 2016 09:27 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
1. You need to make a backup. Do it in a RUN block, with
SET MAXCORRUPT FOR DATAFILE '/u01/app/oracle/oradata/munchen/indx401.dbf' TO 100;
A hundred is just a guess, you'll have to see if it is high enough for the backup to proceed. You need to do this now. Right now! Do not read more until you have dome it.

2. So, have you done the backup?

3. You can do nothing about the damaged archive logfile, but you may be able to repair the damaged datafile(s) with

RECOVER CORRUPTION LIST;

try it - you have nothing to lose.

4. Assuming that (1) succeeds and that (3) does not, you'll need to identify the object sitting on the damaged block. If it is an index, drop it and recreate, and you should be OK.
What to do next after formatting corrupt block not part of any segment still leaves corrupted blocks [message #653286 is a reply to message #652824] Fri, 01 July 2016 00:40 Go to previous messageGo to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear all,

I encounter the following in a scheduled backup again

RMAN-03009: failure of backup command on c1 channel at 06/29/2016 02:42:08
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/munchen/indx401.dbf'


I've followed the following MOS: How to Format Corrupted Block Not Part of Any Segment (Doc ID 336133.1)
query:

SELECT file_id, 
       status, 
       autoextensible, 
       relative_fno, 
       tablespace_name 
FROM   dba_data_files 
WHERE  file_name = '/u01/app/oracle/oradata/munchen/indx401.dbf';
results as follow
SYS@munchen>SELECT file_id, status, autoextensible,relative_fno, tablespace_name FROM dba_data_files WHERE file_name='/u01/app/oracle/oradata/munchen/indx401.dbf';

   FILE_ID STATUS    AUT RELATIVE_FNO TABLESPACE_NAME                           
---------- --------- --- ------------ ------------------------------            
        44 AVAILABLE NO            44 INDX4

query
SELECT * 
FROM   v$database_block_corruption; 

result

SYS@munchen>select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO                   
---------- ---------- ---------- ------------------ ---------                   
        44    3293942         16                  0 ALL ZERO

initally 16 blocks corrupted
query


SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
ORDER BY file#, corr_start_block#;


result


SYS@munchen>SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2  , greatest(e.block_id, c.block#) corr_start_block#
  3  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5  - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6  , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1
 10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13  , header_block corr_start_block#
 14  , header_block corr_end_block#
 15  , 1 blocks_corrupted
 16  , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22  , greatest(f.block_id, c.block#) corr_start_block#
 23  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 24  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 26  , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1
 30  AND f.block_id + f.blocks - 1 >= c.block#
 31  ORDER BY file#, corr_start_block#;

OWNER                          SEGMENT_TYPE       SEGMENT_NAME                   PARTITION_NAME                      FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION               
------------------------------ ------------------ ------------------------------ ------------------------------ ---------- ----------------- --------------- ---------------- --------------            
                                                                                                                        44           3293942         3293957               16 Free Block

i've created a table as highlighted in step 5


OLAF@munchen>create table s (
  2  	    n number,
  3  	    c varchar2(4000)
  4  	  ) nologging tablespace INDX4 pctfree 99;

Table created.

OLAF@munchen>
OLAF@munchen>select segment_name,tablespace_name from user_segments where segment_name='S';

SEGMENT_NAME                                                                    
--------------------------------------------------------------------------------
TABLESPACE_NAME                                                                 
------------------------------                                                  
S                                                                               
INDX4

next I follow step 6 to 7

SYS@munchen>CREATE OR REPLACE TRIGGER corrupt_trigger
  2    AFTER INSERT ON olaf.s
  3    REFERENCING OLD AS p_old NEW AS new_p
  4    FOR EACH ROW
  5  DECLARE
  6    corrupt EXCEPTION;
  7  BEGIN
  8    IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
  9   and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
 10  	  RAISE corrupt;
 11    END IF;
 12  EXCEPTION
 13    WHEN corrupt THEN
 14  	  RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
 15  END;
 16  /
Enter value for blocknumber: 3293942
old   8:   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
new   8:   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=3293942)
Enter value for filenumber: 44
old   9:  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
new   9:  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=44) THEN

Trigger created.

SYS@munchen>
SYS@munchen>Select BYTES from dba_free_space where file_id=44 and 3293942 between block_id and block_id + blocks -1;

     BYTES                                                                      
----------                                                                      
  67108864                                                                      

SYS@munchen>
SYS@munchen>BEGIN
  2  	     for i in 1..1000000 loop
  3  EXECUTE IMMEDIATE 'alter table olaf.s allocate extent (DATAFILE '||'''/u01/app/oracle/oradata/munchen/indx401.dbf''' ||'SIZE 67108864) ';
  4  end loop;
  5  end ;
  6  /
BEGIN
*
ERROR at line 1:
ORA-01653: unable to extend table OLAF.S by 128 in tablespace INDX4 
ORA-06512: at line 3 


SYS@munchen>
SYS@munchen>select segment_name, segment_type, owner
  2  	    from dba_extents
  3  	   where file_id = 44
  4  	     and 3293942 between block_id
  5  		 and block_id + blocks -1;

SEGMENT_NAME                                                                    
--------------------------------------------------------------------------------
SEGMENT_TYPE       OWNER                                                        
------------------ ------------------------------                               
S                                                                               
TABLE              OLAF                                                        
                                                                                

SYS@munchen>
SYS@munchen>SET ECHO OFF


next step 8

SYS@munchen>
SYS@munchen>BEGIN
  2  FOR i IN 1..1000000000 LOOP
  3  INSERT /*+ APPEND */ INTO olaf.s select i, lpad('REFORMAT',3092, 'R') from dual;
  4  commit ;
  5  END LOOP;
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-20000: Corrupt block has been formatted 
ORA-06512: at "SYS.CORRUPT_TRIGGER", line 10 
ORA-04088: error during execution of trigger 'SYS.CORRUPT_TRIGGER' 
ORA-06512: at line 3

Step 9

14> validate datafile '/u01/app/oracle/oradata/munchen/indx401.dbf' check logical;
15> }
16> 
17> #delete noprompt obsolete;
18> 
19> #SPOOL LOG OFF;
20> 
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

allocated channel: c1
channel c1: SID=296 device type=DISK

Starting validate at 2016-06-30 06:15:44
channel c1: starting validation of datafile
channel c1: specifying datafile(s) for validation
input datafile file number=00044 name=/u01/app/oracle/oradata/munchen/indx401.dbf
channel c1: validation complete, elapsed time: 00:08:46
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
44   FAILED 0              424481       4194303         154224497 
  File Name: /u01/app/oracle/oradata/munchen/indx401.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1582337         
  Index      0              2181377         
  Other      6              6108            

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/diag/rdbms/munchen/munchen/trace/munchen_ora_14091.trc for details
Finished validate at 2016-06-30 06:24:30
released channel: c1
still have 6 blocks corrupted
How should I proceed from here since there's still corrupted blocks? MOS did not address this issue.

many thanks in advance!
Encounter ORA-19566 and ORA-19599 during scheduled backup (merged) [message #653289 is a reply to message #653286] Fri, 01 July 2016 00:55 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
I've merged your topics. It would have been polite to reply to my previous suggestion, rather than just creating a new topic.

The routine you have followed creates an object over the corrupted blocks and therefore attempts to format them. It will not succeed unless the damage is purely logical.

You could try using the DBMS_REPAIR procedure to mark the blocks as corrupt. That will prevent Oracle from attempting to use them. However, you do not actually need to do anything more now: you have created an object on the blocks, so as long as you never attempt to use the object, your problem is solved. All you now need to do is SET MAXCORRUPT in your backup scripts, as I told you to do previously.
Re: Encounter ORA-19566 and ORA-19599 during scheduled backup (merged) [message #653313 is a reply to message #652824] Fri, 01 July 2016 06:36 Go to previous message
EdStevens
Messages: 848
Registered: September 2013
Senior Member
Quote:
Q4 also in the long run, should i change the problematic disk as soon as possible?


Obviously. Especially now that you see this is a recurring problem.
Previous Topic: Which Full Backup can I most afford to delete?
Next Topic: rman snapshot controlfile
Goto Forum:
  


Current Time: Mon Nov 20 18:49:21 CST 2017

Total time taken to generate the page: 0.04413 seconds