Share the content if you found it is useful (You can share using 300 community websites) click "share" at the end of the post.

You are encouraged to leave a comment.








Tuesday, January 20, 2009

Block Media Corruption ... ? Solution other than RMAN ?

The DBMS_REPAIR package is used to work with corruption in the transaction layer and the data layer only (software corrupt blocks).
Blocks with physical corruption (ex. fractured block) are marked as the block is read into the buffer cache and DBMS_REPAIR ignores all
blocks marked corrupt.

The only block repair in the initial release of DBMS_REPAIR is to
*** the block software corrupt ***.

DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM must both be set to FALSE.


A backup of the file(s) with corruption should be made before using package.


Database Summary
===============

Consider a corrupt block exists in table T1.

SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NOT NULL NUMBER(38)
COL2 CHAR(512)


SQL> analyze table t1 validate structure;
analyze table t1 validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file

---> Note: In the trace file produced from the ANALYZE, it can be determined
--- that the corrupt block contains 3 rows of data (nrows = 3).
--- The leading lines of the trace file follows:

Dump file /home/oracle/product/9.2.0/admin/V920/udump/v920_ora_2835.trc
Oracle Enterprise Edition Release 9.2.0.5
With the Partitioning option

*** 1998.12.16.15.53.02.000
*** SESSION ID:(7.6) 1998.12.16.15.53.02.000
kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=32 ktbbhitc=1
Block header dump: 0x01800003
Object id on Block? Y
seg/obj: 0xb6d csc: 0x00.1cf5f itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0002.011.00000121 uba: 0x008018fb.0345.0d --U- 3 fsc
0x0000.0001cf60

data_block_dump
===============
tsiz: 0x7b8
hsiz: 0x18
pbl: 0x28088044
bdba: 0x01800003
flag=-----------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x19d
avsp=0x185
tosp=0x185
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x5ff
0x14:pri[1] offs=0x3a6
0x16:pri[2] offs=0x19d
block_row_dump:

[... remainder of file not included]

end_of_block_dump




DBMS_REPAIR.ADMIN_TABLES (repair and orphan key
================================================

ADMIN_TABLES provides administrative functions for repair and orphan key tables.


SQL> @adminCreate
SQL> connect sys/sys as sysdba
Connected.
SQL>
SQL> -- Repair Table
SQL>
SQL> declare
2 begin
3 -- Create repair table
4 dbms_repair.admin_tables (
5 -- table_name => 'REPAIR_TABLE',
6 table_type => dbms_repair.repair_table,
7 action => dbms_repair.create_action,
8 tablespace => 'USERS'); -- default TS of SYS if not specified
9 end;
10 /

PL/SQL procedure successfully completed.

SQL> select owner, object_name, object_type
2 from dba_objects
3 where object_name like '%REPAIR_TABLE';

OWNER OBJECT_NAME OBJECT_TYPE
------------------------------------------------------------------
SYS DBA_REPAIR_TABLE VIEW
SYS REPAIR_TABLE TABLE


SQL>
SQL> -- Orphan Key Table
SQL>
SQL> declare
2 begin
3 -- Create orphan key table
4 dbms_repair.admin_tables (
5 table_type => dbms_repair.orphan_table,
6 action => dbms_repair.create_action,
7 tablespace => 'USERS'); -- default TS of SYS if not specified
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> select owner, object_name, object_type
2 from dba_objects
3 where object_name like '%ORPHAN_KEY_TABLE';

OWNER OBJECT_NAME OBJECT_TYPE
------------------------------------------------------------------
SYS DBA_ORPHAN_KEY_TABLE VIEW
SYS ORPHAN_KEY_TABLE TABLE




DBMS_REPAIR.CHECK_OBJECT
=========================

CHECK_OBJECT procedure checks the specified object and populates the repair table with information about corruption and repair directive(s). Validation
consists of block checking all blocks in the object. All blocks previously marked corrupt will be skipped.

Note: In the initial release of DBMS_REPAIR the only repair is to mark the block as software corrupt.

SQL> @checkObject
SQL> set serveroutput on
SQL>
SQL> declare
2 rpr_count int;
3 begin
4 rpr_count := 0;
5 dbms_repair.check_object (
6 schema_name => 'SYSTEM',
7 object_name => 'T1',
8 repair_table_name => 'REPAIR_TABLE',
9 corrupt_count => rpr_count);
10 dbms_output.put_line('repair count: ' || to_char(rpr_count));
11 end;
12 /
repair count: 1

PL/SQL procedure successfully completed.

SQL> desc repair_table
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE

SQL> select object_name, block_id, corrupt_type, marked_corrupt,
2 corrupt_description, repair_description
3 from repair_table;

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
T1 3 1 FALSE
kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=32 ktbbhitc=1
mark block software corrupt




Data Extraction
===============

The repair table indicates that block 3 of file 6 is corrupt - but remember that this block has not yet been marked as corrupt, therefore now is the
time to extract any meaningful data. After the block is marked corrupt, the entire block must be skipped.


1. Determine the number of rows in the block from ALTER SYSTEM DUMP (nrows = 3).
2. Use the block dump to see if some data can be saved.

DBMS_REPAIR.FIX_CORRUPT_BLOCKS (ORA-1578)
============================================

FIX_CORRUPT_BLOCKS procedure fixes the corrupt blocks in the specified objects based on information in the repair table. After the block has been marked as
corrupt, an ORA-1578 results when a full table scan is performed.


SQL> declare
2 fix_count int;
3 begin
4 fix_count := 0;
5 dbms_repair.fix_corrupt_blocks (
6 schema_name => 'SYSTEM',
7 object_name => 'T1',
8 object_type => dbms_repair.table_object,
9 repair_table_name => 'REPAIR_TABLE',
10 fix_count => fix_count);
11 dbms_output.put_line('fix count: ' || to_char(fix_count));
12 end;
13 /
fix count: 1

PL/SQL procedure successfully completed.

SQL> select object_name, block_id, marked_corrupt
2 from repair_table;

OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
T1 3 TRUE

SQL> select * from system.t1;
select * from system.t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 3)
ORA-01110: data file 6: '/tmp/ts_corrupt.dbf'




DBMS_REPAIR.DUMP_ORPHAN_KEYS
==============================

DUMP_ORPHAN_KEYS reports on index entries that point to rows in corrupt data
blocks.

SQL> select index_name from dba_indexes
2 where table_name in (select distinct object_name from repair_table);

INDEX_NAME
------------------------------
T1_PK

SQL> @dumpOrphanKeys
SQL> set serveroutput on
SQL>
SQL> declare
2 key_count int;
3 begin
4 key_count := 0;
5 dbms_repair.dump_orphan_keys (
6 schema_name => 'SYSTEM',
7 object_name => 'T1_PK',
8 object_type => dbms_repair.index_object,
9 repair_table_name => 'REPAIR_TABLE',
10 orphan_table_name => 'ORPHAN_KEY_TABLE',
11 key_count => key_count);
12 dbms_output.put_line('orphan key count: ' || to_char(key_count));
13 end;
14 /
orphan key count: 3
PL/SQL procedure successfully completed.

SQL> desc orphan_key_table
Name Null? Type
----------------------------------------- -------- ----------------------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE

SQL> select index_name, count(*) from orphan_key_table
2 group by index_name;

INDEX_NAME COUNT(*)
------------------------------ ----------
T1_PK 3

Note: Index entry in the orphan key table implies that the index should be rebuilt to guarantee the a table probe and an index probe return the same
result set.

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
===============================

SKIP_CORRUPT_BLOCKS enables/disables the skipping of corrupt blocks during
index and table scans of a specified object.

Note: If an index and table are out of sync, then a SET TRANSACTION READ ONLY transaction may be inconsistent in situations where one query probes only
the index and then a subsequent query probes both the index and the table.
If the table block is marked corrupt, then the two queries will return different results.

Suggestion: If SKIP_CORRUPT_BLOCKS is enabled, then rebuild any indexes
identified in the orphan key table (or all index associated with object
if DUMP_ORPHAN_KEYS was omitted).

SQL> @skipCorruptBlocks
SQL> declare
2 begin
3 dbms_repair.skip_corrupt_blocks (
4 schema_name => 'SYSTEM',
5 object_name => 'T1',
6 object_type => dbms_repair.table_object,
7 flags => dbms_repair.skip_flag);
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> select table_name, skip_corrupt from dba_tables
2 where table_name = 'T1';

TABLE_NAME SKIP_COR
------------------------------ --------
T1 ENABLED

SQL> -- rows in corrupt block skipped, no errors on full table scan
SQL> select * from system.t1;

COL1 COL2
--------------------------------------------------------------------------------
4 dddd
5 eeee

--> Notice the pk index has not yet been corrected.

SQL> insert into system.t1 values (1,'aaaa');
insert into system.t1 values (1,'aaaa')
*
SQL> select * from system.t1 where col1 = 1;

no rows selected




DBMS_REPAIR.REBUILD_FREELISTS
===============================

REBUILD_FREELISTS rebuilds freelists for the specified object.

SQL> declare
2 begin
3 dbms_repair.rebuild_freelists (
4 schema_name => 'SYSTEM',
5 object_name => 'T1',
6 object_type => dbms_repair.table_object);
7 end;
8 /

PL/SQL procedure successfully completed.

Rebuild Index
=============

Note: Every index identified in the orphan key table should be rebuilt to
ensure consistent results.

SQL> alter index system.t1_pk rebuild online;

Index altered.

SQL> insert into system.t1 values (1, 'aaaa');

1 row created.

SQL> select * from system.t1;

COL1 COL2
--------------------------------------------------------------------------------
4 dddd
5 eeee
1 aaaa

Note - The above insert statement was used to provide a simple example.
This is the perfect world - we know the data that was lost. The temporary
table (temp_t1) should also be used to include all rows extracted from
the corrupt block.

Conclusion
==========

At this point the table T1 is available but data loss was incurred. In general,data loss must be seriously considered before using the DBMS_REPAIR package for
mining the index segment and/or table block dumps is very complicated and logical inconsistencies may be introduced. In the initial release, the only
repair affected by DBMS_REPAIR is to mark the block as software corrupt.

No comments:

Related Posts Plugin for WordPress, Blogger...

Let us be Friends...

Share |

Popular Posts

Labels

11.5.1. to 11.5.5 Cloning Procedure. 11g 11i 11i and R12 11i Autoconfig 11i Cloning 12.1.1 SSL 2 Node RAC Adconfig.sh errors out adgennls.pl adop Advanced Replication Apache version Apex Apex for Oracle database Apex Installation Apex Installation on Oracle E Business Suite. 11i with Apex Apex on 11i Apex on Oracle 11i Apex402 apexins.sql APPL_TOP Character Application Express Application server Issue APPLSYS password Apps Password Asynchronous Replication Autoconfig Autoconfig Managed Beehive Bit of operating system Block block media corrupt BPEL Central Inventory Character Set Conversion Cloning of 11i Cloning of Oracle Applications Clusterware Concurrent CPU 2011 CPU 2012 April CPU 2012 January. CPU 2012 October CRS custom top Custom Top Creation Database . Database Query Dataguard DBMS_REPAIR Demantra . Domain Name E Business Suite Migration EBS 12.2 EPM exp/imp Oracle Applications Flashback table flows_020200 FND_TOP/resource FNDCPASS FNDCPUCF Forms server upgrade Forms startup FRM-92101 Fusion Middleware Hostname Hot Backup Hot cloning HotBackup Hotbackup Cloning of Oracle Apps hrglobal.drv. NLS Saudi Arabian HRMS hyperion Import and Export Oracle Applications Install Oracle Installation of R12 Installation Steps integration Oracle EBS Issues Database issues Oracle EBS Issues with Upgrade 12.1.1 Java Mission Control Java Upgrade java/sql/SavePoint JInitiator Junk Characters Language Translation Linux Linux Migration listener trace Listner version Local Inventory Login Page Master-Master Replication Memory Tuning Multiple Language NLS Non-Autoconfig Enabled OBIEE 11g Installation OBIEE Issues OIM Opatch version OpenSSL ora-01031 Oracle oracle 11g Oracle Announcement Oracle Application servers Oracle Application services Oracle Applications Oracle Applications 11i oracle applications 12.2 Oracle Applications Cloning Oracle Applications Upgrade Oracle Applications. Oracle Apps cloning Oracle BPEL oracle certifications oracle database Oracle Database RMAN Oracle Database upgradation Oracle DB oracle E Business suite 12.2 Oracle EBS oracle EPM Oracle Forms Logo oracle Fusion Applications Oracle Inventory oracle news Oracle OUI Oracle R12 oracle Sun Solaris Oracle Tuning Oracle Virtual Box Oracle Webgate Oracle WMA configuration orcladmin password for Oracle Applications Pasta Configuration pasta.cfg patch PDF Performance Physical Standby Platform migration Printer Configuration Profile Option R12 RAC Rapid Install Rapidwiz Real Application Clusters 10g Recovery RMAN RMAN Recovery SA Gosi Service Oriented Architecture SOA SQLTXPLAIN SSL Configuration step-by-step upgrade Sun solaris Swap Tablespace Trace Enable Trace file Translation Synchronization Patch transport tablespace tuning Tuning SGA uifont.ali Underscore Universal Content Management updates.oracle.com Upgradation of 12.0.5 to 12.1.1 Upgrade Upgrade 11g Upgrade Apps Upgrade DB Upgrade to 11g UTF8 version of oracle applications versions Web server upgrade weblogic windows workflow version XML Publisher version