Detect And Repair Corruption in an Oracle Database

By Posted on


Home » Articles » Misc » Right here

Hit upon And Restore Corruption in an Oracle Database

Oracle supplies plenty of modes to stumble on and service corruption inside of datafiles.

RMAN (BACKUP VALIDATE, RESTORE VALIDATE, VALIDATE)

Oracle Healing Supervisor (RMAN) can validate the database the use of the BACKUP VALIDATE command.

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

The method outputs the similar knowledge you may see throughout a spare, however deny spare is created. Any prevent corruptions are perceptible within the V$DATABASE_BLOCK_CORRUPTION view, in addition to within the RMAN output.

Through default the command most effective exams for bodily corruption. Upload the CHECK LOGICAL clause to incorporate exams for logical corruption.

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

RMAN can validate the contents of spare information the use of the RESTORE VALIDATE command.

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

In a related solution to the BACKUP VALIDATE command, the RESTORE VALIDATE command mimics the method of a repair, with out in reality acting the repair.

Previous to 11g, the instantly VALIDATE command may just most effective be worn to validate spare alike information. In Oracle 11g onward, the VALIDATE command too can validate datafiles, tablespaces or the entire database, so you’ll worth it in playground of the BACKUP VALIDATE command.

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 customers;
RMAN> VALIDATE CHECK LOGICAL TABLESPACE customers;

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

Any prevent corruptions are perceptible within the V$DATABASE_BLOCK_CORRUPTION view. You’ll establish the gadgets containing a corrupt prevent the use of a question like this.

COLUMN proprietor FORMAT A20
COLUMN segment_name FORMAT A30

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

Extra syntax examples may also be discovered here.

Multitenant : RMAN VALIDATE

Lots of the RMAN instructions for the primary example may also be centered at a pluggable database (PDB) or a root container. As an example, when attach to the basis container you’ll do please see.

rman goal=/

# The entirety.
VALIDATE DATABASE;
VALIDATE CHECK LOGICAL DATABASE;

# Simply the basis container.
VALIDATE DATABASE ROOT;
VALIDATE CHECK LOGICAL DATABASE ROOT;

# Simply the required PDB, or comma-separated listing.
VALIDATE PLUGGABLE DATABASE pdb1;
VALIDATE CHECK LOGICAL PLUGGABLE DATABASE pdb1;

In case you are hooked up at once to the PDB you’ll factor the instructions at once.

rman goal=sys/SysPassword1@pdb1

# Simply the required PDB.
VALIDATE DATABASE;
VALIDATE CHECK LOGICAL DATABASE;

DBVerify

DBVerify is an exterior usefulness that permits validation of offline and on-line datafiles. Along with offline datafiles it may be worn to test the validity of spare datafiles.

C:\>dbv report=C:\Oracle\oradata\TSH1\system01.dbf comments=10000 blocksize=8192

This usefulness isn’t in most cases worn for controlfiles or redo planks, however in MOS Doc ID 1949795.1 there’s an instance of the use of it with controlfiles.

ANALYZE .. VALIDATE STRUCTURE

The ANALYZE command may also be worn to ensure every information prevent within the analyzed object. If any corruption is detected rows are added to the INVALID_ROWS desk.

-- Assemble the INVALID_ROWS desk
SQL> @C:\Oracle\901\rdbms\admin\UTLVALID.SQL

-- Validate the desk construction.
SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE;

-- Validate the desk construction along side all it's indexes.
SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE CASCADE;

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

DB_BLOCK_CHECKING

When the DB_BLOCK_CHECKING parameter is about to [TRUE|HIGH] Oracle plays a travel via of the information within the prevent to test it’s self-consistent. Sadly prevent checking can upload between 1 and 10% overhead to the server. Oracle counsel surroundings this parameter to [TRUE|HIGH] if the overhead is suitable.

Allowable values come with [OFF|FALSE], LOW, MEDIUM, [HIGH|TRUE]. Learn the definitions here.

Prohibit Media Healing (BMR)

Prohibit Media Healing (BMR) lets in specified blocks to be recovered with out affecting all the datafile. It is just supposed for worth the place a identified and restricted selection of blocks is affected. This ends up in a discounted cruel generation to fix (MTTR) and better availability as most effective the affected blocks are offline throughout the operation. BMR can most effective be carried out by way of RMAN the use of the BLOCKRECOVER command.

Prohibit Media Healing is an Undertaking Version attribute.

Corrupt blocks may also be known the use of:

  • Error messages
  • The alert plank
  • Hint information
  • ANALYZE [TABLE | INDEX] instructions
  • The dbverify usefulness
  • The V$BACKUP_CORRUPTION & V$COPY_CORRUPTION perspectives listing corrupt blocks within the backups, no longer the database itself.
  • The V$DATABASE_BLOCK_CORRUPTION lists corrupt blocks within the database detected throughout quite a few RMAN operations. Recovered blocks will nonetheless be indexed till the later spare is carried out.

As soon as detected, corrupt blocks may also be recovered personally. On the other hand, the CORRUPTION LIST choice may also be worn to recuperate all blocks indexed within the V$DATABASE_BLOCK_CORRUPTION view. This listing may also be restricted the use of the UNTIL choice.

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

DBMS_REPAIR

Not like the former modes dicussed, the DBMS_REPAIR bundle permits you to stumble on and service corruption.
The method calls for two management tables to secure an inventory of corrupt blocks and index keys pointing to these blocks. Those are
created as follows.

BEGIN
  DBMS_REPAIR.admin_tables (
    table_name => 'REPAIR_TABLE',
    table_type => DBMS_REPAIR.repair_table,
    motion     => DBMS_REPAIR.create_action,
    tablespace => 'USERS');

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

With the management tables constructed we’re ready to test the desk of pastime the use of the CHECK_OBJECT process.

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('quantity corrupt: ' || TO_CHAR (v_num_corrupt));
END;
/

Assuming the selection of corrupt blocks is larger than 0 the CORRUPTION_DESCRIPTION and the REPAIR_DESCRIPTION
columns of the REPAIR_TABLE may also be worn to get extra details about the corruption.

At this level the currupt blocks were detected, however aren’t marked as corrupt. The FIX_CORRUPT_BLOCKS process can
be worn to mark the blocks as corrupt, permitting them to be skipped by means of DML as soon as the desk is in the proper method.

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 healing: ' || TO_CHAR(v_num_fix));
END;
/

As soon as the corrupt desk blocks were situated and marked all indexes will have to be checked to peer if any in their key
entries level to a corrupt prevent. That is carried out the use of the DUMP_ORPHAN_KEYS process.

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 depend: ' || TO_CHAR(v_num_orphans));
END;
/

If the orphan key depend is larger than 0 the index must be rebuilt.

The method of marking the desk prevent as corrupt routinely eliminates it from the freelists. It will ban freelist get entry to to
all blocks following the corrupt prevent. To right kind this the freelists will have to be rebuilt the use of the REBUILD_FREELISTS
process.

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

The general step within the procedure is to put together positive all DML statements forget about the information blocks marked as corrupt. That is carried out
the use of the SKIP_CORRUPT_BLOCKS process.

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

The SKIP_CORRUPT column within the DBA_TABLES view signifies if this motion has been a success.

At this level the desk may also be worn once more however you’ll have to jerk steps to right kind any information loss related to the lacking blocks.

Alternative Restore Forms

Alternative modes to fix corruption come with:

  • Complete database fix.
  • Person datafile fix.
  • Recreate the desk the use of the CREATE TABLE .. AS SELECT command, taking assist to steer clear of the corrupt blocks by means of retricting the the place clause of the question.
  • Let fall the desk and repair it from a prior export. This will require some guide struggle to switch lacking information.

For more info see:

Hope this is helping. Regards Tim…

Back to the Top.

Related posts:

Leave a Reply

Your email address will not be published. Required fields are marked *