DBA Hub

📋Steps in this guide1/8

Detect And Repair Corruption in an Oracle Database

Learn the different methods of detecting and repairing table block corruption.

oracle miscconfigurationintermediate
by OracleDba
33 views
1

RMAN (BACKUP VALIDATE, RESTORE VALIDATE, VALIDATE)

Oracle Recovery Manager (RMAN) can validate the database using the command. The process outputs the same information you would see during a backup, but no backup is created. Any block corruptions are visible in the view, as well as in the RMAN output. By default the command only checks for physical corruption. Add the clause to include checks for logical corruption. RMAN can validate the contents of backup files using the command. In a similar way to the command, the command mimics the process of a restore, without actually performing the restore. Prior to 11g, the straight command could only be used to validate backup related files. In Oracle 11g onward, the command can also validate datafiles, tablespaces or the whole database, so you can use it in place of the command. Any block corruptions are visible in the view. You can identify the objects containing a corrupt block using a query like this. More syntax examples can be found here .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

RMAN> RESTORE DATABASE VALIDATE;
RMAN> RESTORE ARCHIVELOG ALL VALIDATE;

RMAN> VALIDATE DATAFILE 1;
RMAN> VALIDATE DATAFILE '/u01/app/oracle/oradata/ORCL/system01.dbf';

RMAN> VALIDATE CHECK LOGICAL DATAFILE 1;
RMAN> VALIDATE CHECK LOGICAL DATAFILE '/u01/app/oracle/oradata/ORCL/system01.dbf';

RMAN> VALIDATE TABLESPACE users;
RMAN> VALIDATE CHECK LOGICAL TABLESPACE users;

RMAN> VALIDATE DATABASE;
RMAN> VALIDATE CHECK LOGICAL DATABASE;

COLUMN owner FORMAT A20
COLUMN segment_name FORMAT A30

SELECT DISTINCT owner, segment_name
FROM   v$database_block_corruption dbc
       JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;
2

Multitenant : RMAN VALIDATE

Many of the RMAN commands for the main instance can be targeted at a pluggable database (PDB) or a root container. For example, when connect to the root container you can do the following. If you are connected directly to the PDB you can issue the commands directly.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
rman target=/

# Everything.
VALIDATE DATABASE;
VALIDATE CHECK LOGICAL DATABASE;

# Just the root container.
VALIDATE DATABASE ROOT;
VALIDATE CHECK LOGICAL DATABASE ROOT;

# Just the specified PDB, or comma-separated list.
VALIDATE PLUGGABLE DATABASE pdb1;
VALIDATE CHECK LOGICAL PLUGGABLE DATABASE pdb1;

rman target=sys/SysPassword1@pdb1

# Just the specified PDB.
VALIDATE DATABASE;
VALIDATE CHECK LOGICAL DATABASE;
3

DBVerify

DBVerify is an external utility that allows validation of offline and online datafiles. In addition to offline datafiles it can be used to check the validity of backup datafiles. This utility is not usually used for controlfiles or redo logs, but in MOS Doc ID 1949795.1 there is an example of using it with controlfiles.

Code/Command (click line numbers to comment):

1
C:\>dbv file=C:\Oracle\oradata\TSH1\system01.dbf feedback=10000 blocksize=8192
4

ANALYZE .. VALIDATE STRUCTURE

The command can be used to verify each data block in the analyzed object. If any corruption is detected rows are added to the table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
-- Create the INVALID_ROWS table
SQL> @C:\Oracle\901\rdbms\admin\UTLVALID.SQL

-- Validate the table structure.
SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE;

-- Validate the table structure along with all it's indexes.
SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE CASCADE;

-- Validate the index structure.
SQL> ANALYZE INDEX scott.pk_emp VALIDATE STRUCTURE;
5

DB_BLOCK_CHECKING

When the parameter is set to Oracle performs a walk through of the data in the block to check it is self-consistent. Unfortunately block checking can add between 1 and 10% overhead to the server. Oracle recommend setting this parameter to if the overhead is acceptable. Allowable values include [OFF|FALSE], LOW, MEDIUM, [HIGH|TRUE]. Read the definitions here .
6

Block Media Recovery (BMR)

Block Media Recovery (BMR) allows specified blocks to be recovered without affecting the entire datafile. It is only intended for use where a known and limited number of blocks is affected. This results in a reduced mean time to recovery (MTTR) and higher availability as only the affected blocks are offline during the operation. BMR can only be performed via RMAN using the command. Block Media Recovery is an Enterprise Edition feature. Corrupt blocks can be identified using: - Error messages - The alert log - Trace files - commands - The dbverify utility - The & views list corrupt blocks in the backups, not the database itself. - The lists corrupt blocks in the database detected during a variety of RMAN operations. Recovered blocks will still be listed until the next backup is performed. Once detected, corrupt blocks can be recovered individually. Alternatively, the option can be used to recover all blocks listed in the view. This list can be limited using the option.

Code/Command (click line numbers to comment):

1
2
BLOCKRECOVER DATAFILE 3 BLOCK 121;
BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE - 7';
7

DBMS_REPAIR

Unlike the previous methods dicussed, the package allows you to detect and repair corruption. The process requires two administration tables to hold a list of corrupt blocks and index keys pointing to those blocks. These are created as follows. With the administration tables built we are able to check the table of interest using the procedure. Assuming the number of corrupt blocks is greater than 0 the and the columns of the REPAIR_TABLE can be used to get more information about the corruption. At this point the currupt blocks have been detected, but are not marked as corrupt. The procedure can be used to mark the blocks as corrupt, allowing them to be skipped by DML once the table is in the correct mode. Once the corrupt table blocks have been located and marked all indexes must be checked to see if any of their key entries point to a corrupt block. This is done using the procedure. If the orphan key count is greater than 0 the index should be rebuilt. The process of marking the table block as corrupt automatically removes it from the freelists. This can prevent freelist access to all blocks following the corrupt block. To correct this the freelists must be rebuilt using the procedure. The final step in the process is to make sure all DML statements ignore the data blocks marked as corrupt. This is done using the procedure. The column in the view indicates if this action has been successful. At this point the table can be used again but you will have to take steps to correct any data loss associated with the missing blocks.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
BEGIN
  DBMS_REPAIR.admin_tables (
    table_name => 'REPAIR_TABLE',
    table_type => DBMS_REPAIR.repair_table,
    action     => DBMS_REPAIR.create_action,
    tablespace => 'USERS');

  DBMS_REPAIR.admin_tables (
    table_name => 'ORPHAN_KEY_TABLE',
    table_type => DBMS_REPAIR.orphan_table,
    action     => DBMS_REPAIR.create_action,
    tablespace => 'USERS');
END;
/

SET SERVEROUTPUT ON
DECLARE
  v_num_corrupt INT;
BEGIN
  v_num_corrupt := 0;
  DBMS_REPAIR.check_object (
    schema_name       => 'SCOTT',
    object_name       => 'DEPT',
    repair_table_name => 'REPAIR_TABLE',
    corrupt_count     =>  v_num_corrupt);
  DBMS_OUTPUT.put_line('number corrupt: ' || TO_CHAR (v_num_corrupt));
END;
/

SET SERVEROUTPUT ON
DECLARE
  v_num_fix INT;
BEGIN
  v_num_fix := 0;
  DBMS_REPAIR.fix_corrupt_blocks (
    schema_name       => 'SCOTT',
    object_name       => 'DEPT',
    object_type       => Dbms_Repair.table_object,
    repair_table_name => 'REPAIR_TABLE',
    fix_count         => v_num_fix);
  DBMS_OUTPUT.put_line('num fix: ' || TO_CHAR(v_num_fix));
END;
/

SET SERVEROUTPUT ON
DECLARE
  v_num_orphans INT;
BEGIN
  v_num_orphans := 0;
  DBMS_REPAIR.dump_orphan_keys (
    schema_name       => 'SCOTT',
    object_name       => 'PK_DEPT',
    object_type       => DBMS_REPAIR.index_object,
    repair_table_name => 'REPAIR_TABLE',
    orphan_table_name => 'ORPHAN_KEY_TABLE',
    key_count         => v_num_orphans);
  DBMS_OUTPUT.put_line('orphan key count: ' || TO_CHAR(v_num_orphans));
END;
/

BEGIN
  DBMS_REPAIR.rebuild_freelists (
    schema_name => 'SCOTT',
    object_name => 'DEPT',
    object_type => DBMS_REPAIR.table_object);
END;
/

BEGIN
  DBMS_REPAIR.skip_corrupt_blocks (
    schema_name => 'SCOTT',
    object_name => 'DEPT',
    object_type => DBMS_REPAIR.table_object,
    flags       => DBMS_REPAIR.skip_flag);
END;
/
8

Other Repair Methods

Other methods to repair corruption include: - Full database recovery. - Individual datafile recovery. - Recreate the table using the command, taking care to avoid the corrupt blocks by retricting the where clause of the query. - Drop the table and restore it from a previous export. This may require some manual effort to replace missing data. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!