Oracle Faq

1) Check the db for corruption with RMAN

Start RMAN in nocatalog mode and connect to your database:

From the operating system prompt issue:

$ rman target / nocatalog

or

$ rman target sys/ nocatalog

From the RMAN> prompt issue the validate command with the “check logical” clause:

run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup check logical validate database;
release channel d1;
release channel d2;
}

Once the validate process is complete check the view v$database_block_corruption

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 2258619 1 336170157 LOGICAL

The above query gives the exact file#,block#, no of blocks corrupted and type of corruption.

Now to identify the corrupt segment. Use the following query.

SQL> select tablespace_name,segment_name, segment_type,owner from dba_extents where file_id=7 and 2258619 between
block_id and block_id+blocks-1;

TABLESPACE_NAME
——————————
SEGMENT_NAME
——————————————————————————–
SEGMENT_TYPE OWNER
—————— ——————————
CASH
CLM_LE_CASH_POS_MST
TABLE CMPCASHP

Now to confirm the corruption of particular table or index use the following command.

analyze the table with the cascade option to see if any error is reported

analyze table .cpay_instrument_gen_queue validate structure cascasde;

If errors are reported from step#2 above then analyze the table WITHOUT the cascade option

analyze table .cpay_instrument_gen_queue validate structure;

For e.g

SQL> analyze table cmpcashp.cpay_instrument_gen_queue validate structure cascade;
analyze table cmpcashp.cpay_instrument_gen_queue validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure – see trace file

SQL> analyze table cmpcashp.cpay_instrument_gen_queue validate structure;

Table analyzed.

The results from above query show we have a table / index cross reference failure. Additionally, the validate on just the table returned no errors so we know the problem is with one of the indexes

To resolve above issue , Use the following action plan.

1) Identify the all the indexes on the table.

select owner, index_name
from dba_indexes
where lower(table_name) = ‘cpay_instrument_gen_queue’

2) Analyze each of the indexes from step#1 above to see if we can identify which one is causing the
problem (I’m assuming there are more than one index)

analyze index . validate structure;

3) Capture the DDL for each index from step#1 (if you have a GUI such as TOAD you can get the DDL from there as well)

sql> connect owner / password (must connect as the index owner)
sql> set long 3500
sql> select dbms_metadata.get_ddl(‘INDEX’,’‘) from dual;

Note: Save the output to a file and ensure the lines have not wrapped at the wrong point

4) Drop / recreate any index that had errors from step#2 above
- – use the DDL from step#3 to recreate the index

5) If there were no errors reported in step #2 then drop / recreate each of the indexes
- – If step#2 did not identify the problem index then the only choice is to drop / recreate them all

6) After the indexes have been dropped / recreated then check the table again with analyze
validate cascade to ensure no errors

analyze table cmpcashp.cpay_instrument_gen_queue validate structure cascade