Physical/Logical Health Monitor check (Oracle 19c)

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