Oracle

Oracle

Posts 1-4 of 4
  • Roger Sprenger
    Roger Sprenger
    The company name is only visible to registered members.
    RMAN: Corrupt block in snapshot control file
    Hallo,

    Wir nutzen RMAN mit Oracle 10.2.0.4 für das tägliche Backup.
    Seit ein (Lese) Fehler auf dem verwendeten SAN-Laufwerk auftrat (der mittlerweile behoben ist),
    haben wir folgende Fehler (RMAN-03002 / ORA-19599) während des Backup:

    Logging vom RMAN:
    Starting backup at 08-SEP-11
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    input datafile fno=00005 name=D:\DATABASES\DFT1\DFTTSP1.DBF
    input datafile fno=00004 name=D:\DATABASES\DFT1\DFTIDX1.DBF
    input datafile fno=00003 name=D:\DATABASES\DFT1\SYSAUX01.DBF
    input datafile fno=00001 name=D:\DATABASES\DFT1\SYSTEM01.DBF
    input datafile fno=00002 name=D:\DATABASES\DFT1\UNDOTBS01.DBF
    channel ORA_DISK_1: starting piece 1 at 08-SEP-11
    channel ORA_DISK_1: finished piece 1 at 08-SEP-11
    piece handle=D:\BACKUPRMAN\BPMM1A5V_1_1.ORA tag=TAG20110908T104935 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    including current control file in backupset
    including current SPFILE in backupset
    channel ORA_DISK_1: starting piece 1 at 08-SEP-11
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of backup plus archivelog command at 09/08/2011 10:49:53
    ORA-19599: block number 461 is corrupt in control file C:\SNAP_TRGT.CTL

    Recovery Manager complete.


    Diese Parameter haben wir benutzt:
    CONFIGURE channel device type disk maxpiecesize 2g;
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\BackupRMAN\%F.ora';
    sql "alter database backup controlfile to trace as ''D:\BackupRMAN\ctl.trc '' reuse";
    sql "alter database backup controlfile to ''D:\BackupRMAN\controlfilebck.ora '' reuse";
    sql "create pfile=''D:\BackupRMAN\pfile.ora'' from spfile";
    backup database filesperset 6 format 'D:\BackupRMAN\%U.ora' plus archivelog delete input format 'D:\BackupRMAN\%U.ora';
    delete force noprompt obsolete;


    So weit wir verstanden haben, benötigt RMAN während des Backups eine konsistente Kopie des Controlfiles (snapshot controlfile).
    Wir haben auch schon einen anderen Pfad (C:\SNAP_TRGT.CTL) für das 'snapshot controlfile' angegeben,
    jedoch ohne Erfolg (Die neue Kopie hat den korrupten Block an der selber Stelle).
    Deshalb gehen wir davon aus, das das Controlfile selbst kaputt ist.

    "Alter database backup controlfile" funktioniert ohne Probleme

    Irgendwelche Ideen hierfür?

    vielen Dank im Voraus
    • 08 Sep 2011, 12:24 pm
  • Praveen Bachu
    Praveen Bachu
    The company name is only visible to registered members.
    Re: RMAN: Corrupt block in snapshot control file
    Hallo,

    ich hoffe, dass diese Informationen hilft, um die Probleme mit Blocks zu verstehen.

    "How to perform Block Media Recovery using RMAN"

    SAN unterstuezt man mit Block Level Storage und ich hoffe, SAN Admin etwas helfen kann.

    Am Ende sind die relevante Unterlagen fuer diese Probleme.

    It is quite often that you get blocks in a data file physically corrupt. As a proactive DBA you should always be ready for such kind of things. e.g. if you have proper backups of the data files you can recover the corrupt block from those backups. IF you have user managed backups, you can recover the whole data file and get the corrupted block recovered with it. If you have an RMAN backup, you can use a powerful RMAN feature called BMR (BLOCK MEDIA RECOVERY). Using BMR you can tell RMAN an individual block to recover from a backup instead of recovering the whole data file. However, there may exist scenarios where you don't have a backup at all. You may have lost it, got it deleted or may be the backup got corrupted itself. This article demonstrate how to repair a corrupt block to make the objects usable, that are using that block.

    Bear in mind that repairing a corrupt block using DBMS_REPAIR, does not restore data in those blocks. It is just a mean of marking those blocks as corrupt blocks and skip them when data in the read from the object containing corrupt blocks. The data in the corrupt blocks id lost forever.

    Lets first get a block corrupted and see its effects on the objects using that block.

    hier ist Beispiel:

    create tablespace ts_corrupt datafile '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf' size 10m autoextend on next 5m extent management local segment space management auto / Tablespace created. create user usr_corrupt identified by usr_corrupt default tablespace ts_corrupt quota unlimited on ts_corrupt / User created. grant connect , resource to usr_corrupt / Grant succeeded. conn usr_corrupt/usr_corrupt Connected. create table t_corrupt as select * from all_objects / Table created. create index tc_idx on t_corrupt (object_name) / Index created. SQL> exit

    We have a tablespace ts_corrupt, which has a table named t_corrupt owned by the user usr_corrupt and there is an index on one of the columns in the table. Lets see where in the data file this table is sitting.

    $ sqlplus / as sysdba select segment_name , header_file , header_block from dba_segments where segment_name = 'T_CORRUPT' and owner = 'USR_CORRUPT'; SEGMENT_NAME HEADER_FILE HEADER_BLOCK ---------------------------- ----------- ------------ T_CORRUPT 6 11 SQL> exit

    The header of the table is in block 11, so if the block 12 or so are corrupted we will get an error when we try to select data from the table. Lets corrupt block 12 using the "dd" command in Linux.
    DISCLAIMER: The dd command given below is just for learning purposes and should only be used on testing systems. I will not take any responsibility of any consequences or loss of data caused by this command.

    $ cd /d01/apps/oradata/oraxpo $ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=12 << EOF > Make it Corrupt. > EOF 0+1 records in 0+1 records out 17 bytes (17 B) copied, 8.9e-05 seconds, 191 kB/s $ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=13 << EOF > Make it Corrupt. > EOF 0+1 records in 0+1 records out 17 bytes (17 B) copied, 0.0002 seconds, 85 kB/s $ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=14 << EOF > Make it Corrupt. > EOF 0+1 records in 0+1 records out 17 bytes (17 B) copied, 5.6e-05 seconds, 304 kB/s

    We have injected our crap into the block # 12,13 and 14 of the datafile "/d01/apps/oradata/oraxpo/ts_corrupt01.dbf" using "dd" command. After this command successfully executed these blocks are now corrupt.

    $ sqlplus / as sysdba SQL> alter system flush buffer_cache; System altered. /* We may need to flush the buffer_cache because if the block 12 is in the buffer_cache already it will not be read from the data file. */ SQL> conn usr_corrupt/usr_corrupt Connected. SQL> select * from t_corrupt; select * from t_corrupt * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 12) ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf' SQL> exit

    The error that we get due to a block corruption is very clear. It will tell you the file and block#.

    Repairing a block corruption starts with creating two admin tables "REPAIR_TABLE" and "ORPHAN_TABLE".

    REPAIR_TABLE:
    This table is filled with the information about the corrupt blocks by the DBMS_REPAIR.CHECK_OBJECT procedure.
    ORPHAN_KEY_TABLE:
    This table is filled with the information about any orphan keys in indexes due to corrupt blocks in the table by the DBMS_REPAIR.DUMP_ORPHAN_KEYS procedure.

    DBMS_REPAIR.ADMIN_TABLES()
    performs actions (create,drop,purge) with special tables which are later on used for the corrupt block repair.

    table_name
    => Name of the table to process.
    table_type
    => Type of the table being processed. e.g. DBMS_REPAIR.REPAIR_TABLE or DBMS_REPAIR.ORPHAN_TABLE
    action
    => the action to perform with the table mentioned in the first argument. e.g. DBMS_REPAIR.CREATE_ACTION, DBMS_REPAIR.DROP_ACTION or DBMS_REPAIR.PURGE_ACTION.
    tablespace
    => The tablespace to use when creating the special table.

    $ sqlplus / as sysdba

    BEGIN
    DBMS_REPAIR.ADMIN_TABLES
    (
    TABLE_NAME => 'REPAIR_TABLE',
    TABLE_TYPE => DBMS_REPAIR.REPAIR_TABLE,
    ACTION => DBMS_REPAIR.CREATE_ACTION,
    TABLESPACE => 'USERS'
    );
    END;
    /

    PL/SQL procedure successfully completed.

    -- We just created the REPAIR_TABLE in the USERS tablespace.

    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


    BEGIN
    DBMS_REPAIR.ADMIN_TABLES
    (
    TABLE_NAME => 'ORPHAN_KEY_TABLE',
    TABLE_TYPE => DBMS_REPAIR.ORPHAN_TABLE,
    ACTION => DBMS_REPAIR.CREATE_ACTION,
    TABLESPACE => 'USERS'
    );
    END;
    /

    PL/SQL procedure successfully completed.

    -- We just created the ORPHAN_KEY_TABLE in the USERS tablespace.

    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

    DBMS_REPAIR.CHECK_OBJECT()
    checks an objects for possible corruptions and report the corruptions into the REPAIR_TABLE.

    schema_name
    => Owner of the object to check.
    object_name
    => Name of the object to check.
    repair_table_name
    => Name of the REPAIR_TABLE.
    corrupt_count
    => An OUT parameter where the number of corrupt blocks are reported.

    SET SERVEROUTPUT ON
    DECLARE
    vCorruptBlocks INT := 0;
    BEGIN
    DBMS_REPAIR.CHECK_OBJECT
    (
    SCHEMA_NAME => 'USR_CORRUPT',
    OBJECT_NAME => 'T_CORRUPT',
    REPAIR_TABLE_NAME => 'REPAIR_TABLE',
    CORRUPT_COUNT => vCorruptBlocks
    );
    DBMS_OUTPUT.PUT_LINE('Number of blocks corrupted: ' || TO_CHAR (vCorruptBlocks));
    END;
    /
    Number of blocks corrupted: 3

    PL/SQL procedure successfully completed.

    -- The procedure found three blocks corrupt and has filled the information in REPAIR_TABLE.

    column object_name format a10
    column repair_description format a28
    set lines 10000
    SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT, REPAIR_DESCRIPTION
    FROM REPAIR_TABLE;

    OBJECT_NAM BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION
    ---------- ---------- ------------ ---------- ----------------------------
    T_CORRUPT 12 6148 TRUE mark block software corrupt
    T_CORRUPT 13 6148 TRUE mark block software corrupt
    T_CORRUPT 14 6148 TRUE mark block software corrupt

    As you may see in the output above the MARKED_CORRUPT column shows TRUE for all three corruptions. It seems that CHECK_OBJECT procedure is capable of marking the corrupt blocks in the scenario we have demonstrated. However, if the MARKED_CORRUPT column shows FALSE for some corrupted blocks, you may use DBMS_REPAIR.FIX_CORRUPT_BLOCKS procedure to mark the blocks as corrupt.

    DBMS_REPAIR.FIX_CORRUPT_BLOCKS()
    fix any corrupted blocks in specified object by marking them as software corrupt.

    schema_name
    => Owner of the object to fix.
    object_name
    => Name of the object to fix.
    object_type
    => Type of the object to fix.
    repair_table_name
    => Name of the REPAIR_TABLE.
    fix_count
    => An OUT parameter where the number of blocks fixed are reported.

    SET SERVEROUTPUT ON
    DECLARE
    vBlocksFixed INT := 0;
    BEGIN
    DBMS_REPAIR.FIX_CORRUPT_BLOCKS
    (
    SCHEMA_NAME => 'USR_CORRUPT',
    OBJECT_NAME=> 'T_CORRUPT',
    OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
    REPAIR_TABLE_NAME => 'REPAIR_TABLE',
    FIX_COUNT => vBlocksFixed
    );
    DBMS_OUTPUT.PUT_LINE('Number of blocks fixed: ' || TO_CHAR(vBlocksFixed));
    END;
    /
    Number of blocks fixed: 0

    PL/SQL procedure successfully completed.

    Number of blocks fixed are 0 in my case as the corrupt blocks are already marked by the CHECK_OBJECT procedure.

    If the corrupted object was a table, then it may be very possible that there was an index on the table which had entries of data that is corrupt in the table now. Those entries are orphan now.

    DBMS_REPAIR.DUMP_ORPHAN_KEYS()
    dump any orphan keys in indexes caused by the corruption in a table.

    schema_name
    => Owner of the object to process.
    object_name
    => Name of the object to process.
    object_type
    => Type of the object to process. e.g. DBMS_REPAIR.INDEX_OBJECT.
    repair_table_name
    => Name of the REPAIR_TABLE.
    orphan_table_name
    => Name of the ORPHAN_TABLE.
    key_count
    => An OUT parameter where the number of orphan keys dumped are reported.

    SET SERVEROUTPUT ON
    DECLARE
    vOrphanKeys INT := 0;
    BEGIN
    DBMS_REPAIR.DUMP_ORPHAN_KEYS
    (
    SCHEMA_NAME => 'USR_CORRUPT',
    OBJECT_NAME => 'TC_IDX',
    OBJECT_TYPE => DBMS_REPAIR.INDEX_OBJECT,
    REPAIR_TABLE_NAME => 'REPAIR_TABLE',
    ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
    KEY_COUNT => vOrphanKeys
    );
    DBMS_OUTPUT.PUT_LINE('Number of orphan keys: ' || TO_CHAR(vOrphanKeys));
    END;
    /
    Number of orphan keys: 241

    PL/SQL procedure successfully completed.

    -- 241 Orphan keys are dumped to the ORPHAN_TABLE.

    Even after the corrupt blocks are marked corrupt, you will not be able to access them unless you set the object to skip any corrupted blocks using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS.

    SQL> select * from usr_corrupt.t_corrupt;
    select * from usr_corrupt.t_corrupt
    *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 6, block # 12)
    ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf'

    SQL> select * from usr_corrupt.t_corrupt where object_name = 'DUAL';
    select * from usr_corrupt.t_corrupt where object_name = 'DUAL'
    *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 6, block # 12)
    ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf'

    DBMS_REPAIR.SKIP_CORRUPT_BLOCKS()
    skip any corrupted blocks in specified object when data is read from it.

    schema_name
    => Owner of the object to process.
    object_name
    => Name of the object to process.
    object_type
    => Type of the object to process.
    flags
    => What to do with the object. e.g. DBMS_REPAIR.SKIP_FLAG or DBMS_REPAIR.NOSKIP_FLAG

    BEGIN
    DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
    (
    SCHEMA_NAME => 'USR_CORRUPT',
    OBJECT_NAME => 'T_CORRUPT',
    OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
    FLAGS => DBMS_REPAIR.SKIP_FLAG
    );
    END;
    /

    PL/SQL procedure successfully completed.

    Now lets see if our repair works or not.

    -- Rebuild the index to be used with the object repaired.

    SQL> ALTER INDEX USR_CORRUPT.TC_IDX REBUILD;

    Index altered.

    select owner , table_name , skip_corrupt
    from dba_tables
    where table_name = 'T_CORRUPT';

    OWNER TABLE_NAME SKIP_COR
    ------------------------------ ------------------------------ --------
    USR_CORRUPT T_CORRUPT ENABLED

    /*
    The SKIP_CORRUPT column in dba_tables shows whether corrupt blocks are skipped
    or not on reading data from the object.
    */

    SQL> select count(*) from usr_corrupt.t_corrupt;

    COUNT(*)
    ----------
    40692

    SQL> select * from usr_corrupt.t_corrupt where object_name = 'DUAL';

    no rows selected

    -- The row where "object_name='DUAL'" is lost.

    hier sind die relevante Unterlagen, die man auf der Oracle Website koennen findet:

    Using DBMS_REPAIR to Repair Data Block Corruption
    http://download.oracle.com/docs/cd/B19306_01/server.102/b142...
    This post was modified on 10 Sep 2011 at 07:11 pm.
    • 10 Sep 2011, 7:07 pm
  • Mathias Zarick
    Mathias Zarick
    The company name is only visible to registered members.
    Re: RMAN: Corrupt block in snapshot control file
    Hallo Roger,

    die Korruption ist ziemlich sicher im Controlfile selbst. Erstelle es mit dem "create controlfile" statement neu. Syntax dafür bekommst du durch ein "alter database backup controlfile to trace as '/my/file';". Anschließend solltest du deine alten Backupinfos wieder neu im controlfile registrieren mit rman catalog befehl, was nicht unbedingt nötig ist, wenn du einen RMAN Katalog verwendest.
    Hoffe das hilft Mathias
    • 12 Sep 2011, 07:26 am
  • Tarik Artun
    Tarik Artun
    The company name is only visible to registered members.
    Re: RMAN: Corrupt block in snapshot control file
    Ihren eigenen SnapShot sollten Sie wieder deaktivieren mit:

    change controlfilecopy 'C:\SNAP_TRGT.CTL' unavailable;

    dann nochmal recovern!
    This post was modified on 16 Sep 2011 at 09:19 am.
    • 16 Sep 2011, 09:19 am