Fix Oracle Database Corrupt Blocks by Using RMAN

The following errors appear in alert log:

Thu Sep 23 20:06:34 2021
Hex dump of (file 23, block 774506) in trace file /u01/app/oracle/diag/rdbms/testdb/TETSDB/trace/TETSDB_ora_7558.trc
Corrupt block relative dba: 0x000bd16a (file 23, block 774506)
Bad check value found during backing up datafile
Data in bad block:
 type: 6 format: 2 rdba: 0x000bd16a
 last change scn: 0x071e.81e8b798 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb7980602
 check value in block header: 0xe202
 computed block checksum: 0x82fe
Reread of blocknum=774506, file=/u05/oradata/TETSDB/cdr_01.dbf. found same corrupt data
Reread of blocknum=774506, file=/u05/oradata/TETSDB/cdr_01.dbf. found same corrupt data
Reread of blocknum=774506, file=/u05/oradata/TETSDB/cdr_01.dbf. found same corrupt data
Reread of blocknum=774506, file=/u05/oradata/TETSDB/ctl_01.dbf. found same corrupt data
Reread of blocknum=774506, file=/u05/oradata/TETSDB/cdr_01.dbf. found same corrupt data

SOLUTION

The corrupted block can be fixed online without offline datafile or tablespace required.

double confirm the data corrution

Run dbverify utility

$dbv blocksize=8192 file=/u05/oradata/TETSDB/cdr_01.dbf
...
..
.
Corrupt block relative dba: 0x000bd16a (file 23, block 774506)
...
..
.
Total Pages Marked Corrupt : 1

Run RMAN utility

RMAN> connect target /
RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup validate check logical database;
close channel c1;
close channel c2;
close channel c3;
close channel c4
}

OR

Just check specified file and blocks:

RMAN> validate check logical datafile 23 block 774505 to 774507;


Check block corruption from database view

SQL> select * from  v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        23     774506          1                  0 CHECKSUM

Check the impacted object

  SQL> select 
              relative_fno, owner, segment_name, segment_type
         from
              dba_extents
        where
              file_id = 23
         and
              774506 between block_id and block_id + blocks - 1;
  
RELATIVE_FNO OWNER       SEGMENT_NAME       SEGMENT_TYPE
------------ ----------- ------------------ ------------------
        1024  TEST       CDR_IDX1        INDEX

fix the corruption

If it is an object like index, just rebuilt it even online rebuilding the index.

SQL> alter index TEST.CDR_IDX1 rebuild online parallel 8;

Index altered.

SQL>  alter index TEST.CDR_IDX1 parallel 1;

Index altered.
$ rman target / catalog rman/passwd@rman
connected to target database: TESTDB (DBID=1234567890)
connected to recovery catalog database

RMAN> run {
allocate channel ch1 TYPE 'SBT_TAPE';
SEND 'NSR_ENV=(NSR_SERVER=BKP_SERVER,NSR_CLIENT=DB_SERVER)';
BLOCKRECOVER DATAFILE 23 block 774506;
}
...
..
.
archived log for thread 1 with sequence 51992 is already on disk as file /fra/TESTDB/archivelog/2021_09_23/o1_mf_1_51992_jnrmdprf_.arc
archived log for thread 1 with sequence 51993 is already on disk as file /fra/TESTDB/archivelog/2021_09_23/o1_mf_1_51993_jnrpkppq_.arc
media recovery complete, elapsed time: 00:05:08
Finished recover at 23-SEP-21
released channel: ch1

RMAN>

Check the progressing:

SQL> select 
           username,sid, opname, target, sofar, totalwork, units, 
           to_char(start_time,'YYYYMMDD-HH24:MI:SS') StartTime, 
           time_remaining, message  
      from 
           V$SESSION_LONGOPS 
      where 
           time_remaining>60;

Finally check and confirm the corrupted block(s) have been fixed.

SQL> select * from  v$database_block_corruption;

no rows selected                                            

For some database versions, the record in v$database_block_corruption still exists , even there is no corrupted blocks.

To clear v$database_block_corruption orphan records:

RMAN> validate check logical datafile 23 block 774505to 774507;

Starting validate at 04-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=413 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00023 name=/u05/oradata/TESTDB/CDR_01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
23   OK     0              0            3               8013155974853
  File Name: /u05/oradata/TESTDB/CDR_01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              3
  Other      0              0

Finished validate at 04-OCT-21

The check v$database_block_corruption is cleared successfully.

SQL> select * from  v$database_block_corruption;

no rows selected

Long Running Operations Monitoring ( V$SESSION_LONGOPS ) in Oracle Database

List operations remaining time > 10 minutes :

SQL>select 
           username,sid, opname, target, sofar, totalwork, units, 
           to_char(start_time,'YYYYMMDD-HH24:MI:SS') StartTime, 
           time_remaining, message  
      from 
           V$SESSION_LONGOPS 
      where 
           time_remaining>600;


USERNAME     SID    OPNAME     TARGET     SOFAR   TOTALWORK  UNITS    STARTTIME         TIME_REMAINING MESSAGE

------------ ------ ---------- ---------- ------- ---------- ------- ----------------- -------------- ---------------------------------------------------------
 TETSUSER    5704   Table Scan TETSUSER.TEST  22414  39677      Blocks    20180920-10:55:49        3676  Table Scan:  JAMES.TEST: 22414 out  of 39677 Blocks done

How to Monitor DataPump

A couple of ways to monitor DataPump job

Get DataPump job details from database

SQL>select JOB_NAME,OPERATION,JOB_MODE,DEGREE,
           ATTACHED_SESSIONS,STATE 
     from dba_datapump_jobs

JOB_NAME             OPERATION JOB_MODE DEGREE ATTAC..TIONS STATE
-------------------- --------- -------- ------ ------------ ------
SYS_EXPORT_SCHEMA_01 EXPORT    SCHEMA   4       1          EXECUTING

Find job name from the DataPump log file

...
..
.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": directory= ..... PARALLEL=4

Query V$SESSION_LONGOPS

SQL> select inst_id, username,target_desc,sofar,
            totalwork,TIME_REMAINING 
      from gV$SESSION_LONGOPS 
     where TIME_REMAINING>1000;

INST_ID USERNAME TARGET_DESC SOFAR   TOTALWORK   TIME_REMAINING
------- -------- ----------- ------- ----------- --------------
 4      SYS                  248779  4294967296  28121732
 3      SYS                  467021  4294967296  15108237
 3      SYS      EXPORT      8824    21597       2335
 2      SYS                  313184  4294967296  22351992

Attach to the running DataPump job

$ expdp ATTACH=SYS_EXPORT_SCHEMA_01

Export> help
...
..
.
 ------------------------------------------------------------------------------
HELP
 Summarize interactive commands.

KILL_JOB
 Detach and delete job.

PARALLEL
 Change the number of active workers for current job.

REUSE_DUMPFILES
 Overwrite destination dump file if it exists [NO].

START_JOB
 Start or resume current job.
 Valid keyword values are: SKIP_CURRENT.

STATUS
 Frequency (secs) job status is to be monitored where
 the default [0] will show new status when available.

STOP_JOB
 Orderly shutdown of job execution and exits the client.
 Valid keyword values are: IMMEDIATE.

$status
...
..
.

Kill the running DataPump job

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes