Note_Tech

All technological notes.

Project maintained by simonangel-fong Hosted on GitHub Pages — Theme by mattgraham

Recovery - Block Corruption & Block Media Recovery

Back

Block Corruption

ORA-01578 error

Corruption Handling

Parameters to Detect Corruption

Parameter Use
DB_BLOCK_CHECKING Prevent memory and data corruption
DB_BLOCK_CHECKSUM Detect I/O storage, disk corruption

Recommended generic block-corruption parameters:

Block Media Recovery

-- Recover a single block
RECOVER DATAFILE 6 BLOCK 3;

-- Recover multiple blocks in multiple data files
RECOVER
DATAFILE 2 BLOCK 43
DATAFILE 2 BLOCK 79
DATAFILE 6 BLOCK 183;

-- Recover all blocks logged in V$DATABASE_BLOCK_CORRUPTION
RECOVER CORRUPTION LIST;

Example:

ORA-01578: ORACLE data block corrupted (file # 7, block # 3)
ORA-01110: data file 7: '/oracle/oradata/orcl/tools01l.dbf’
ORA-01578: ORACLE data block corrupted (file # 2, block # 235)
ORA-01110: data file 2: '/oracle/oradata/orcl/undotbsOl.dbf’'

RECOVER
DATAFILE 7 BLOCK 3
DATAFILE 2 BLOCK 235;

Prerequisites

Recovering Individual Blocks

Lab: Block Media Recovery

Setup Environment

sqlplus / as sysdba

ALTER SESSION SET container=orclpdb;
show con_name
ALTER DATABASE open;
show pdbs

-- CLEANUP from previous run
DROP USER bc CASCADE;
DROP TABLESPACE bctbs INCLUDING CONTENTS AND DATAFILES;

-- Create tablespace
CREATE TABLESPACE bctbs
DATAFILE '/u01/app/oracle/oradata/ORCL/orclpdb/bctbs01.dbf'
SIZE 10M REUSE
SEGMENT SPACE MANAGEMENT MANUAL;

-- Create user
CREATE USER bc IDENTIFIED BY pass4BC
DEFAULT TABLESPACE bctbs
QUOTA UNLIMITED ON bctbs;

GRANT CREATE SESSION TO bc;

-- create table and populate
-- be sure table is at least 2 blocks long
CREATE TABLE bc.bccopy
TABLESPACE bctbs
AS SELECT * FROM HR.EMPLOYEES;

INSERT INTO bc.bccopy
SELECT * FROM bc.bccopy;

INSERT INTO bc.bccopy
SELECT * FROM bc.bccopy;

--  confirm table has been created
SELECT salary
FROM bc.bccopy
WHERE rownum = 1;

lab_block_corrupt

rman target "'sys@orclpdb as sysdba'"
-- backup tbsp
BACKUP AS COPY TABLESPACE bctbs;

EXIT

lab_block_corrupt

sqlplus / as sysdba

ALTER SESSION SET container=orclpdb;
show con_name
UPDATE bc.bccopy SET salary = salary+1;
COMMIT;

-- confirm updated
SELECT salary
FROM bc.bccopy
WHERE rownum = 1;

lab_block_corrupt

sqlplus / as sysdba

ALTER SESSION SET container=orclpdb;
-- Find the file and block numbers
SELECT DISTINCT
   MIN(DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) as FILE_NO,
   MIN(DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)) as BLOCK_NO
FROM bc.bccopy;
-- FILE_NO  BLOCK_NO
-- 108	    129

ALTER SYSTEM FLUSH BUFFER_CACHE;

lab_block_corrupt

# linux bash command
# Corrupts 2 consecutive blocks.
# make sure the table is in 2 consequetive blocks
# the location of data file
FILE='/u01/app/oracle/oradata/ORCL/orclpdb/bctbs01.dbf'
# block_no
BLOCK=129
# the block size
BLOCKSIZE=8196

# write the content to the block
dd of=$FILE bs=$BLOCKSIZE conv=notrunc seek=$BLOCK <<EOF
the content is written to block
EOF

dd of=$FILE bs=$BLOCKSIZE conv=notrunc seek=`expr 1 + $BLOCK` <<EOF
the content is written to the next block
EOF

lab_block_corrupt

SELECT *
FROM bc.bccopy;

/*
ORA-01578: ORACLE data block corrupted (file # 106, block # 129)
ORA-01110: data file 106: '/u01/app/oracle/oradata/ORCL/orclpdb/bctbs01.dbf'
01578. 00000 -  "ORACLE data block corrupted (file # %s, block # %s)"
*Cause:    The data block indicated was corrupt. This was a physical
           corruption, also called a media corruption. The cause is unknown
           but is most likely external to the database. If ORA-26040 is also
           signaled, the corruption is due to NOLOGGING or UNRECOVERABLE
           operations.
*Action:   The general method to repair a media corrupt block is to restore
           a backup and recover the backup. For databases in ARCHIVELOG
           mode, use block media recovery or media recovery. In some situations,
           you can also drop the segment and re-create it. For example, you can
           drop an index and re-create the index
*/

lab_block_corrupt

Query Block Corruption

SELECT *
FROM V$DATABASE_BLOCK_CORRUPTION;
-- 108	129	2	0	CHECKSUM	3

lab_block_corrupt

rman target "'/ as sysbackup'"

-- Check failures
LIST FAILURE;
-- view suggested repair strategy.
ADVISE FAILURE;

-- preview the recovery script
REPAIR FAILURE PREVIEW;
-- # block media recovery for multiple blocks
-- recover datafile 108 block 129 to 130;

-- recover the corrupted blocks.
REPAIR FAILURE;
-- Confirm the recovery
LIST FAILURE;

EXIT

lab_block_corrupt lab_block_corrupt lab_block_corrupt lab_block_corrupt lab_block_corrupt

$ sqlplus / as sysdba

alter session set container = orclpdb;
-- query after recovery
SELECT salary
FROM BC.BCCOPY
WHERE rownum = 1;

exit

lab_block_corrupt

Clean up

rman target "'sys@orclpdb as sysdba'"
DELETE NOPROMPT copy of tablespace bctbs;
exit;

lab_block_corrupt

sqlplus / as sysdba

ALTER SESSION SET container=orclpdb;
DROP USER bc CASCADE;
DROP TABLESPACE bctbs INCLUDING CONTENTS AND DATAFILES;

EXIT;

Best Practice: Proactive Checks

Lab: Validate the database

rman target /

-- alidate the database
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

LIST BACKUP;
-- no backup was created

lab_validate

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

lab_validate

TOP