5 Replies Latest reply on Jul 27, 2012 10:29 AM by Mark willium

    Corrupt logical block

    user2015671
      I want to check logical block corruption and repair it with dbms_repair package.
      For that first i need to corrupt block logically.

      How to corrupt oracle database table block logically? Is there any procedure to do that?

      Thanks,
        • 1. Re: Corrupt logical block
          Helios-GunesEROL
          Hi;

          1. What is db version?

          Please see:
          http://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmsynta010.htm
          http://docs.oracle.com/cd/B10501_01/server.920/a96521/repair.htm
          http://docs.oracle.com/cd/B10501_01/server.920/a96521/repair.htm
          http://dbarajabaskar.blogspot.com/2011/04/block-corruption-in-oracle.html

          Regard
          Helios
          • 2. Re: Corrupt logical block
            Fran
            how to corrupt a block:
            http://dbaspot.com/oracle-server/64448-how-corrupt-oracle-block.html

            how to detect/repair:
            http://www.oracle.com/technetwork/database/focus-areas/availability/maa-datacorruption-bestpractices-396464.pdf
            http://docs.oracle.com/cd/B28359_01/server.111/b28310/repair004.htm
            • 3. Re: Corrupt logical block
              Azar
              http://kamranagayev.com/2010/03/18/rman-video-tutorial-series-performing-block-media-recovery-with-rman/
              • 4. Re: Corrupt logical block
                user2015671
                Hello,

                Thanks for you resposes.

                I tried to corrupt datablock by editing datafile and remove some data and add some data.

                1. Execurted below rman commad

                RMAN> backup validate check logical database;

                Output show block is corrupted.

                Below query is also listing corrupted blocks.

                SQL> select * from v$database_block_corruption;


                But when i am checking corruption with dbms_repair packges it doesn't list any corruption.

                SET SERVEROUTPUT ON
                DECLARE num_corrupt INT;
                BEGIN
                num_corrupt := 0;
                DBMS_REPAIR.CHECK_OBJECT (
                SCHEMA_NAME => 'TEST',
                OBJECT_NAME => 'TEST1',
                REPAIR_TABLE_NAME => 'REPAIR_TABLE',
                CORRUPT_COUNT => num_corrupt);
                DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
                END;
                /

                Please let me know any other scenarios to corrupt LOGICAL block.
                Thanks,
                • 5. Re: Corrupt logical block
                  Mark willium
                  Other scenario to corrupt LOGICAL block.

                  First of all take your tablespace offline.
                  Choose any hex editor software to make changes in your datafile
                  Now open your datafile using your hex editor software
                  Change any record entry and close your hex editor
                  Set tablespace online
                  You have no any problem to bring to datafile online because file header was not modified
                  Oracle only complains when you try to access corrupt block.

                  Note: Don't try it on your production database & applied for Oracle 10g (Release 2)

                  For Oracle 11g:
                  http://www.stellaroracledatabaserecovery.com/blog/identifying-and-fixing-corrupt-data-blocks-in-oracle-11g-database-using-dbms_repair-procedures/

                  Edited by: 948836 on Jul 27, 2012 3:29 AM