1 Reply Latest reply on Oct 3, 2013 4:14 PM by gottikere

    NEED TO RECOVER A DATABASE USING RMAN with CONTROL FILE AND NO RMAN CATALOG, DISK FAILURE..

    Ran G

      Hello All,

       

      The disk failure caused our production data on the disk to be resotred with the backup data available and recovered through RMAN with cotrolfile , and no catalog DB is configured.

       

      I had the restored the spfile and control file then recovered the database,

       

       

      startup nomount;


      RESTORE SPFILE FROM ' path '  ;

      Shutdown immediate;

      startup nomount

      Restore controfile from autobackup;

       

      restore database;

       

      [AT POINT , A MESSAGE PROMPTED LIKE " failur of restored command - some targets not found"  (thinking may be few archives are not found, i proceeded to incomeplete recovery of DB) ]


      recover database;

       

      Finished reocvery .

       

       

      Now in the Grid control i see that 60 blocks of a particular datafile are corrupted and needs recovery. Do i need to get the data file resotred again and recover it or any simple way to recover this data file

      .

       

       

      When i perform the block recovery , it says recovery failed and when i run the data file recovery it succeeds. Please provide you inputs to recover the database as it is production BI database and pretty critical to our client.

      Thanks for your valuable time in advance.

       

      Regards,

      Ran G.

        • 1. Re: NEED TO RECOVER A DATABASE USING RMAN with CONTROL FILE AND NO RMAN CATALOG, DISK FAILURE..
          gottikere

          These is a common problem if the object are created due to NOLOGGIN option. If you check most of the object which are facing block corruption is indexes .

           

          Use the below query to check the objects :

           

          It will map each block from v$database_block_corruption to either a segment or if the block is free.

           

          $ sqlplus / as sysdba

          set pagesize 2000

          set linesize 250

          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#;


          Below oracle support note will help you :


          ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution (Doc ID 794505.1)

          The Gains and Pains of Nologging Operations (Doc ID 290161.1)


          SQL> select d.NAME as DBF_NAME, t.NAME as TS_NAME, d.UNRECOVERABLE_CHANGE# as NOLOG_CHNG#, to_char(d.UNRECOVERABLE_TIME, 'Dy DD-Mon-YYYY HH24:MI:SS') as NOLOG_TIME from V$DATAFILE d join V$TABLESPACE t on d.TS# = t.TS# order by t.NAME;


          Thanks,

          gssdba.wordpress.com