RMAN - ORA-19566: exceeded limit of 0 corrupt blocks for file
| Topic ID: 3620 | |
| Created By: | 2009-MAY-06 19:59:42 [Vitaliy] |
| Updated By: | 2009-AUG-06 20:06:47 [Vitaliy] |
| Status: | Open |
| Severity: | Normal |
| Read Only: | No |
|
10150
2009-MAY-06 19:59:42
|
||||
|
##
## Issue
##
You are running RMAN backup and it fails with the following error:
released channel: d1
released channel: d2
released channel: d3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 05/05/2009 23:16:15
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/czgddata/a_txn_data02.dbf
Checking the alert log reveals the block number and absolute file id
Tue May 5 23:15:57 2009
Error backing up file 401, block 257533: logical corruption
V$DATABASE_BLOCK_CORRUPTION shows NO blocks corruption
SQL> connect / as sysdba
Connected.
SQL> desc V$DATABASE_BLOCK_CORRUPTION
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
BLOCK# NUMBER
BLOCKS NUMBER
CORRUPTION_CHANGE# NUMBER
CORRUPTION_TYPE VARCHAR2(9)
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
SQL>
Attempt to "BLOCKRECOVER DATAFILE 401 BLOCK 257533;" fails with:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 05/06/2009 13:46:26
RMAN-06053: unable to perform media recovery because of missing log
even though all of your ARCHIVE LOGS are in place ...
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 05/06/2009 13:46:26
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 14739 lowscn 5965167488623 found to restore
...
...
...
RMAN> exit
dbv can "see" block corruption and reports "kdbchk: the amount of space used is not equal to block size":
$ dbv file=/u01/app/czgddata/a_txn_data02.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Wed May 6 15:04:58 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/czgddata/a_txn_data02.dbf
Block Checking: DBA = 1682173436, Block Type = KTB-managed data block
data header at 0x2a96f1c324
kdbchk: the amount of space used is not equal to block size
used=6993 fsc=0 avsp=894 dtl=7896
Page 257532 failed with check code 6110
Block Checking: DBA = 1682173437, Block Type = KTB-managed data block
data header at 0x2a96f1e324
kdbchk: the amount of space used is not equal to block size
used=1747 fsc=0 avsp=6140 dtl=7896
Page 257533 failed with check code 6110
DBVERIFY - Verification complete
Total Pages Examined : 268800
Total Pages Processed (Data) : 143349
Total Pages Failing (Data) : 2
Total Pages Processed (Index): 53408
Total Pages Failing (Index): 0
Total Pages Processed (Other): 17090
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 54953
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 3755753446 (1388.3755753446)
$
The block belongs to a real segment named CZ_DEVL_PROJECTS
SQL> connect / as sysdba
Connected.
SQL>
SQL>
SQL> select owner, segment_name, segment_type
from dba_extents
where file_id = 401
and 257532 between block_id and block_id + blocks - 1;
2 3 4
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
CZ
CZ_DEVL_PROJECTS
TABLE
SQL> SQL> SQL>
SQL>
SQL> select owner, segment_name, segment_type
from dba_extents
where file_id = 401
and 257533 between block_id and block_id + blocks - 1;
2 3 4
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
CZ
CZ_DEVL_PROJECTS
TABLE
Even though the block belongs to a table the data is "readable"
SQL> SQL>
SQL> select count(*) from CZ.CZ_DEVL_PROJECTS;
COUNT(*)
----------
312
But the "validate structure cascade" still fails ...
SQL> analyze table CZ.CZ_DEVL_PROJECTS validate structure cascade;
analyze table CZ.CZ_DEVL_PROJECTS validate structure cascade
*
ERROR at line 1:
ORA-01498: block check failure - see trace file
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
SQL>
Since we can read the data we can move the segment with corrupt block to "map it out" of the segment
SQL> alter table CZ.CZ_DEVL_PROJECTS move;
Table altered.
SQL> select count(*) from CZ.CZ_DEVL_PROJECTS;
COUNT(*)
----------
312
now validate structure cascade fails with unusable indexes error:
SQL> analyze table CZ.CZ_DEVL_PROJECTS validate structure cascade;
analyze table CZ.CZ_DEVL_PROJECTS validate structure cascade
*
ERROR at line 1:
ORA-01502: index 'CZ.CZ_DEVL_PROJECTS_N1' or partition of such index is in
unusable state
Rebuilding them solves that issue
SQL> select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online;'
2 from dba_indexes
3 where table_name = 'CZ_DEVL_PROJECTS';
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINE;'
--------------------------------------------------------------------------------
alter index CZ.CZ_DEVL_PROJECTS_N1 rebuild online;
alter index CZ.CZ_DEVL_PROJECTS_N2 rebuild online;
alter index CZ.CZ_DEVL_PROJECTS_U1 rebuild online;
alter index CZ.CZ_DEVL_PROJECTS_N3 rebuild online;
alter index CZ.CZ_DEVL_PROJECTS_N4 rebuild online;
alter index CZ.CZ_DEVL_PROJECTS_UPK rebuild online;
alter index SCOTT.CZ_DEVL_PROJECTS_N1 rebuild online;
alter index SCOTT.CZ_DEVL_PROJECTS_N2 rebuild online;
alter index SCOTT.CZ_DEVL_PROJECTS_U1 rebuild online;
alter index SCOTT.CZ_DEVL_PROJECTS_N3 rebuild online;
alter index SCOTT.CZ_DEVL_PROJECTS_N4 rebuild online;
alter index SCOTT.CZ_DEVL_PROJECTS_UPK rebuild online;
12 rows selected.
SQL>
SQL> alter index CZ.CZ_DEVL_PROJECTS_N1 rebuild online;
alter index CZ.CZ_DEVL_PROJECTS_N2 rebuild online;
alter index CZ.CZ_DEVL_PROJECTS_U1 rebuild online;
alter index CZ.CZ_DEVL_PROJECTS_N3 rebuild online;
alter index CZ.CZ_DEVL_PROJECTS_N4 rebuild online;
alter index CZ.CZ_DEVL_PROJECTS_UPK rebuild online;
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
SQL> analyze table CZ.CZ_DEVL_PROJECTS validate structure cascade;
Table analyzed.
But dbv still finds block corruption on the file
$ dbv file=/u01/app/czgddata/a_txn_data02.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Wed May 6 15:25:21 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/czgddata/a_txn_data02.dbf
Block Checking: DBA = 1682173437, Block Type = KTB-managed data block
data header at 0x2a96f1e324
kdbchk: the amount of space used is not equal to block size
used=1747 fsc=0 avsp=6140 dtl=7896
Page 257533 failed with check code 6110
DBVERIFY - Verification complete
Total Pages Examined : 268800
Total Pages Processed (Data) : 143355
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 53412
Total Pages Failing (Index): 0
Total Pages Processed (Other): 17192
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 54841
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 3755783668 (1388.3755783668)
$
Although now the corrupt block doesn't belong to any segment
SQL> select owner, segment_name, segment_type
from dba_extents
where file_id = 401
and 257533 between block_id and block_id + blocks - 1;
2 3 4
no rows selected
SQL> SQL>
##
## Steps below are based on Note:336133.1 How to Format Corrupted Block Not Part of Any Segment
##
Verify that corrupt block is in the FREE space
SQL> SQL> select * from dba_free_space where file_id= 401
and 257533 between block_id and block_id + blocks -1;
2
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
APPS_TS_TX_DATA 401 257529 131072 16
401
SQL>
Create a dummy table in the tablespace containing datafile which has the
corrupt block - and use nologging option to prevent redo records from being
generated:
SQL> create user TMPUSER identified by TMPUSER;
User created.
SQL> grant dba to TMPUSER;
Grant succeeded.
SQL> connect TMPUSER/TMPUSER
Connected.
SQL> connect TMPUSER/TMPUSER
Connected.
SQL> create table s (
n number,
c varchar2(4000)
) nologging tablespace APPS_TS_TX_DATA ;
2 3 4
Table created.
SQL>
SQL> select segment_name,tablespace_name from user_segments
where segment_name='S' ; 2
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
S
APPS_TS_TX_DATA
SQL>
Create Trigger On dummy table which throws exception once the corrupted block is reused
Connect as sys and create the following trigger:
SQL> connect / as sysdba
Connected.
SQL>
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL> CREATE OR REPLACE TRIGGER corrupt_trigger
2 AFTER INSERT ON TMPUSER.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) THEN
9 RAISE corrupt;
10 END IF;
11 EXCEPTION
12 WHEN corrupt THEN
13 RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
14 END;
15 /
Enter value for blocknumber: 257533
Trigger created.
SQL>
Note: It is advisable to ensure that AUTOEXTEND is OFF for the datafile, to prevent it from growing
SQL> alter database datafile 401 autoextend off;
Database altered.
SQL>
Allocate space to the table from the affected datafile
SQL> alter table TMPUSER.s
allocate extent (DATAFILE '/u01/app/czgddata/a_txn_data02.dbf' SIZE 1M);
2
Table altered.
SQL> alter table TMPUSER.s
allocate extent (DATAFILE '/u01/app/czgddata/a_txn_data02.dbf' SIZE 1M);
2
Table altered.
SQL> alter table TMPUSER.s
allocate extent (DATAFILE '/u01/app/czgddata/a_txn_data02.dbf' SIZE 1M);
2
Table altered.
SQL> alter table TMPUSER.s
allocate extent (DATAFILE '/u01/app/czgddata/a_txn_data02.dbf' SIZE 1M);
2
Table altered.
Keep allocating till the corrupted block is part of scott.s - check this with
the following query:
SQL> select segment_name, segment_type, owner
from dba_extents
where file_id = 401
and 257533 between block_id
and block_id + blocks -1 ;
2 3 4 5
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE OWNER
------------------ ------------------------------
S
TABLE TMPUSER
SQL>
Step 7 - Insert data into dummy table To format the block
The trigger will be fired for every row inserted into the table and an
exception with ORA-20000 will be produced as soon as it inserts the first row
into the corrupt block.
SQL> Begin
FOR i IN 1..1000000000 loop
for j IN 1..1000 loop
Insert into TMPUSER.s VALUES(i,'x');
end loop;
commit;
END LOOP;
END;
/
2 3 4 5 6 7 8 9 Begin
*
ERROR at line 1:
ORA-20000: Corrupt block has been formatted
ORA-06512: at "SYS.CORRUPT_TRIGGER", line 9
ORA-04088: error during execution of trigger 'SYS.CORRUPT_TRIGGER'
ORA-06512: at line 4
SQL> SQL> exit
re-run dbv [ still shows corruption on the same BLOCK # 257533 ]
$ dbv file=/u01/app/czgddata/a_txn_data02.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Wed May 6 15:37:49 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/czgddata/a_txn_data02.dbf
Block Checking: DBA = 1682173437, Block Type = KTB-managed data block
data header at 0x2a96f1e324
kdbchk: the amount of space used is not equal to block size
used=1747 fsc=0 avsp=6140 dtl=7896
Page 257533 failed with check code 6110
DBVERIFY - Verification complete
Total Pages Examined : 268800
Total Pages Processed (Data) : 143355
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 53412
Total Pages Failing (Index): 0
Total Pages Processed (Other): 17198
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 54835
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 3755812197 (1388.3755812197)
$
re-run insert/format loop
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 6 15:38:57 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect / as sysdba
Connected.
SQL> select segment_name, segment_type, owner
from dba_extents
where file_id = 401
and 257533 between block_id
and block_id + blocks -1 ;
2 3 4 5
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE OWNER
------------------ ------------------------------
S
TABLE TMPUSER
SQL> SQL>
SQL> SQL>
SQL> Begin
FOR i IN 1..1000000000 loop
for j IN 1..1000 loop
Insert into TMPUSER.s VALUES(i,'x');
end loop;
commit;
END LOOP;
END;
/
2 3 4 5 6 7 8 9 Begin
*
ERROR at line 1:
ORA-20000: Corrupt block has been formatted
ORA-06512: at "SYS.CORRUPT_TRIGGER", line 9
ORA-04088: error during execution of trigger 'SYS.CORRUPT_TRIGGER'
ORA-06512: at line 4
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FINALLY!
It looks like it definetely needed the COMMIT ...
$ dbv file=/u01/app/czgddata/a_txn_data02.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Wed May 6 15:41:26 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/czgddata/a_txn_data02.dbf
DBVERIFY - Verification complete
Total Pages Examined : 268800
Total Pages Processed (Data) : 143358
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 53411
Total Pages Failing (Index): 0
Total Pages Processed (Other): 17196
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 54835
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 3755812197 (1388.3755812197)
$
Step 8 - Verify for any corruption in datafile by running DBV and Rman backup
Run dbverify on the corrupt datafile. It will not show the block as corrupted.
RMAN backup will not report any error on this block.
$ dbv file=/u01/app/czgddata/a_txn_data02.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Wed May 6 15:43:29 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/czgddata/a_txn_data02.dbf
DBVERIFY - Verification complete
Total Pages Examined : 268800
Total Pages Processed (Data) : 143358
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 53411
Total Pages Failing (Index): 0
Total Pages Processed (Other): 17196
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 54835
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 3755825945 (1388.3755825945)
$
$ exit
logout
[root@confdevdb logs]#
[root@confdevdb logs]#
[root@confdevdb logs]#
[root@confdevdb logs]#
[root@confdevdb logs]#
[root@confdevdb logs]#
[root@confdevdb logs]# crontab -l
# Hotbackup - daily
00 23 * * * /var/backups/start_backup_daily
[root@confdevdb logs]# /var/backups/start_backup_daily
...
...
...
Recovery Manager complete.
-rw-r--r-- 1 oracle dba 0 May 6 16:00 /orarcv/confdevdb/CZGD/transfer_completed
-rw-r--r-- 1 oracle dba 0 May 6 15:44 /orarcv/confdevdb/CZGD/transfer_started
Wed May 6 16:00:37 PDT 2009: rman backup done!
##
## Update Aug 06 2009
##
To avoid this corruption in the first place make sure to set db_block_checking
## This is based on Bug 7662491
##
SQL> alter system set db_block_checking=MEDIUM scope=both;
System altered.
When db_block_checking is enabled the foreground process will fail with the following error and
this will prevent the database corruption:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [402], [259404], [6110], [], [], [], []
[edited by: Vitaliy at 20:06 (CST) on Aug. 06, 2009]