Physical/Logical Health Monitor check (Oracle 19c)

By | |

This tutorial lists the commands to check the health of Oracle database for physical and logical corruptions within RMAN commands

  • Check for physical corruptions
  • Check for logical corruptions

Check for physical corruptions

RMAN> validate database;
 Starting validate at 20-MAY-23
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=887 device type=DISK
 channel ORA_DISK_1: starting validation of datafile
 channel ORA_DISK_1: specifying datafile(s) for validation
 input datafile file number=00001 name=/data/wadodb/system01.dbf
 input datafile file number=00004 name=/data/wadodb/undotbs01.dbf
 input datafile file number=00003 name=/data/wadodb/sysaux01.dbf
 input datafile file number=00002 name=/data/wadodb/asyw_tbs01.dbf
 input datafile file number=00005 name=/data/wadodb/users02.dbf
 input datafile file number=00008 name=/data/wadodb/WADODB/datafile/o1_mf_wado_tbs_l5skfdoz_.dbf
 input datafile file number=00009 name=/data/wadodb/WADODB/datafile/o1_mf_wado_tbs_l5sldkgh_.dbf
 input datafile file number=00007 name=/data/wadodb/users01.dbf
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
 List of Datafiles
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 1    OK     0              17512        151044          3612548
   File Name: /data/wadodb/system01.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              82990
   Index      0              13765
   Other      0              36773
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 2    OK     0              12670        12800           2145157
   File Name: /data/wadodb/asyw_tbs01.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              0
   Index      0              0
   Other      0              130
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 3    OK     0              21839        85891           3612648
   File Name: /data/wadodb/sysaux01.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              9901
   Index      0              6609
   Other      0              47411
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 4    OK     0              385          87040           3612648
   File Name: /data/wadodb/undotbs01.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              0
   Index      0              0
   Other      0              86655
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 5    OK     0              12673        12800           2144213
   File Name: /data/wadodb/users02.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              0
   Index      0              0
   Other      0              127
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 7    OK     0              93           641             2161287
   File Name: /data/wadodb/users01.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              65
   Index      0              15
   Other      0              467
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 8    OK     0              12673        12800           2166823
   File Name: /data/wadodb/WADODB/datafile/o1_mf_wado_tbs_l5skfdoz_.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              0
   Index      0              0
   Other      0              127
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 9    OK     0              12673        12800           2167528
   File Name: /data/wadodb/WADODB/datafile/o1_mf_wado_tbs_l5sldkgh_.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              0
   Index      0              0
   Other      0              127
 channel ORA_DISK_1: starting validation of datafile
 channel ORA_DISK_1: specifying datafile(s) for validation
 including current control file for validation
 including current SPFILE in backup set
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 List of Control File and SPFILE
 File Type    Status Blocks Failing Blocks Examined
 
 SPFILE       OK     0              2
 Control File OK     0              664
 Finished validate at 20-MAY-23
 RMAN>

Check for logical corruptions:

RMAN> validate check logical database;
 Starting validate at 20-MAY-23
 using channel ORA_DISK_1
 channel ORA_DISK_1: starting validation of datafile
 channel ORA_DISK_1: specifying datafile(s) for validation
 input datafile file number=00001 name=/data/wadodb/system01.dbf
 input datafile file number=00004 name=/data/wadodb/undotbs01.dbf
 input datafile file number=00003 name=/data/wadodb/sysaux01.dbf
 input datafile file number=00002 name=/data/wadodb/asyw_tbs01.dbf
 input datafile file number=00005 name=/data/wadodb/users02.dbf
 input datafile file number=00008 name=/data/wadodb/WADODB/datafile/o1_mf_wado_tbs_l5skfdoz_.dbf
 input datafile file number=00009 name=/data/wadodb/WADODB/datafile/o1_mf_wado_tbs_l5sldkgh_.dbf
 input datafile file number=00007 name=/data/wadodb/users01.dbf
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
 List of Datafiles
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 1    OK     0              17512        151044          3612548
   File Name: /data/wadodb/system01.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              82990
   Index      0              13765
   Other      0              36773
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 2    OK     0              12670        12800           2145157
   File Name: /data/wadodb/asyw_tbs01.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              0
   Index      0              0
   Other      0              130
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 3    OK     0              21839        85891           3612710
   File Name: /data/wadodb/sysaux01.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              9901
   Index      0              6609
   Other      0              47411
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 4    OK     0              385          87040           3612710
   File Name: /data/wadodb/undotbs01.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              0
   Index      0              0
   Other      0              86655
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 5    OK     0              12673        12800           2144213
   File Name: /data/wadodb/users02.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              0
   Index      0              0
   Other      0              127
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 7    OK     0              93           641             2161287
   File Name: /data/wadodb/users01.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              65
   Index      0              15
   Other      0              467
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 8    OK     0              12673        12800           2166823
   File Name: /data/wadodb/WADODB/datafile/o1_mf_wado_tbs_l5skfdoz_.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              0
   Index      0              0
   Other      0              127
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 9    OK     0              12673        12800           2167528
   File Name: /data/wadodb/WADODB/datafile/o1_mf_wado_tbs_l5sldkgh_.dbf
   Block Type Blocks Failing Blocks Processed
 
 Data       0              0
   Index      0              0
   Other      0              127
 channel ORA_DISK_1: starting validation of datafile
 channel ORA_DISK_1: specifying datafile(s) for validation
 including current control file for validation
 including current SPFILE in backup set
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 List of Control File and SPFILE
 File Type    Status Blocks Failing Blocks Examined
 
 SPFILE       OK     0              2
 Control File OK     0              664
 Finished validate at 20-MAY-23
 RMAN>

Enjoy!

Bookmark the permalink.
Loading Facebook Comments ...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.