Wednesday, September 7, 2011

Blocks Corrupted in system09.dbf After Fresh Installation of E-Business Suite R12.1.1

We have identified a block corruption in the 12.1.1 Fresh Installed Instance, This block corruption identified while running RMAN.

RMAN LOG:
=========


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 09/06/2011 19:51:02
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/db/apps_st/data/system09.dbf


SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
352 165657 1 0 ALL ZERO
352 165656 1 0 CORRUPT


SQL> select file_name,FILE_ID,TABLESPACE_NAME from dba_data_files where file_id=352;
FILE_NAME FILE_ID TABLESPACE_NAME
------------------------------------------------------------ ---------- ------------------------------
/u01/db/apps_st/data/system09.dbf 352 SYSTEM


DB Verify OUTPUT
================

dbv file=/u01/db/apps_st/data/system09.dbf feedback=1000
DBVERIFY - Verification complete
Total Pages Examined : 192128
Total Pages Processed (Data) : 53193
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 27615
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2081
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 82766
Total Pages Marked Corrupt : 26473
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3723391595 (1388.3723391595)



The Segments are Free segments - No need to Panic
=====================================

SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
2 , greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
3 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
4 5 - greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
6 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
, 'Free Block' description
26 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
AND f.block_id + f.blocks - 1 >= c.block#
30 31 order by file#, corr_start_block#;



OWNER SEGME SEGME PARTITION_NA FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
---------- ----- ----- ------------ ---------- ----------------- --------------- ---------------- --------------
352 165656 165656 1 Free Block
352 165657 165657 1 Free Block


SOLUTION TO FIX THE ISSUE
==========================


This is known issue in R12.1.1, The installation media of R12 has block corruption in it.

Ref NoteID: ID 840963.1

Have performed below actions to fix it.

SQL> select sum(bytes/1024/1024),sum(maxbytes/1024/1024) from dba_data_files where FILE_NAME like '%system09.dbf';
SUM(BYTES/1024/1024) SUM(MAXBYTES/1024/1024)
-------------------- -----------------------
                1501                       0


SQL>alter database datafile '/u01/db/apps_st/data/system09.dbf' resize 1000M;

Check for Corruption Now: NONE :)


$dbv file=/u01/db/apps_st/data/system09.dbf feedback=1000
DBVERIFY: Release 11.1.0.7.0 - Production on Wed Sep 7 08:41:53 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/db/apps_st/data/system09.dbf
................................................................................
................................................

DBVERIFY - Verification complete
Total Pages Examined         : 128000
Total Pages Processed (Data) : 53193
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 27615
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2081
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 45111
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3723488883 (1388.3723488883)

2 comments:

  1. thanks for share, I have same trouble but now it aready soloved with your note.

    ReplyDelete
  2. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete