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  |
 |
juniordbanewbie
Messages: 250
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  |
John Watson
Messages: 8962
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 |
|
juniordbanewbie
Messages: 250
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';
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
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
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
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
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 |
John Watson
Messages: 8962
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 02:41:47 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
