Shulou(Shulou.com)06/01 Report--

Description:

Health Monitor is the various components introduced by 11g to check the database (including files, memory, transaction integrity,

Metadata and process usage). These inspectors will generate a report of the results of the inspection and recommendations for resolving the problem.

It can be performed when the database is offline (nomount) and online (open or mount), and health checks can be run in DB-online mode. Only redo integrity checks and db structural integrity checks need to be done in database offline mode.

Supported check entries:

SQL > select name,offline_capable,description,internal_check from v$hm_check NAME O DESCRIPTION Imuri -HM Test Check Y Check for health monitor functionality YDB Structure Integrity Check Y Checks integrity of all database files NCF Block Integrity Check Y Checks integrity of a control file block NData Block Integrity Check Y Checks integrity of a data file block NRedo Integrity Check Y Checks integrity of redo log content NLogical Block Check N Checks logical content of a block YTransaction Integrity Check N Checks a transaction for corruptions NUndo Segment Integrity Check N Checks integrity of an undo segment NNo Mount CF Check Y Checks control file in NOMOUNT mode YMount CF Check Y Checks control file in mount mode YCF Member Check Y Checks a multiplexed copy Of the control file YAll Datafiles Check Y Checks all datafiles in the database YSingle Datafile Check Y Checks a datafile YTablespace Check Check Y Checks a tablespace YLog Group Check Y Checks all members of a log group YLog Group Member Check Y Checks a particular member of a log group YArchived Log Check Y Checks an archived log YRedo Revalidation Check Y Checks redo log content YIO Revalidation Check Y Checks file accessibility YBlock IO Revalidation Check Y Checks file accessibility YTxn Revalidation Check N Revalidate corrupted transaction YFailure Simulation Check Y Creates dummy failures YDictionary Integrity Check N Checks dictionary integrity NASM Mount Check Y Diagnose mount failure YASM Allocation Check Y Diagnose allocation failure NASM Disk Visibility Check Y Diagnose add disk failure YASM File Busy Check Y Diagnose file drop failure YASM Toomanyoff Check Y Diagnose mount failed because there were too many offline disks YASM Insufficient Disks Check Y Diagnose mount failed because there were insufficient disks YASM Insufficient Mem Check Y Check to adjust memory on allocation failure Y30 rows selected.

Type of health check:

1.DB Structure Integrity Check-This check verifies the integrity of database files and reports failures if these files are inaccessible, corrupt or inconsistent. If the database is in mount or open mode, this check examines the log files and data files listed in the control file. If the database is in NOMOUNT mode, only the control file is checked.

-- mount\ open

The mode checks the log files and data files listed in the control file. In NOMOUNT mode, only the control file is checked.

2.Data Block Integrity Check-This check detects disk image block corruptions such as checksum failures, head/tail mismatch, and logical inconsistencies within the block. Most corruptions can be repaired using Block Media Recovery. Corrupted block information is also captured in the V$DATABASE_BLOCK_CORRUPTION view. This check does not detect inter-block or inter-segment corruption.

-- the damaged block information is captured and recorded in the V$DATABASE_BLOCK_CORRUPTION view, and inter-block or inter-segment damage is not detected.

3.Redo Integrity Check-This check scans the contents of the redo log for accessibility and corruption, as well as the archive logs, if available. The Redo Integrity Check reports failures such as archive log or redo corruption.

Online logs and archives will be checked.

4.Undo Segment Integrity Check-This check finds logical undo corruptions. After locating an undo corruption, this check uses PMON and SMON to try to recover the corrupted transaction. If this recovery fails, then Health Monitor stores information about the corruption in V$CORRUPT_XID_LIST. Most undo corruptions can be resolved by forcing a commit.

-- PMON and SMON attempt to recover a corrupted transaction and record it in the V$CORRUPT_XID_LIST view when the corrupted transaction cannot be recovered.

5.Transaction Integrity Check-This check is identical to the Undo Segment Integrity Check except that it checks only one specific transaction.

-- same as 4

6.Dictionary Integrity Check-This check examines the integrity of core dictionary objects, such as tab$ and col$. It performs the following operations:

1) Verifies the contents of dictionary entries for each dictionary object.

-validates the contents of the dictionary entry for each dictionary object.

2) Performs a cross-row level check, which verifies that logical constraints on rows in the dictionary are enforced.

Performs a cross-line level check that enforces logical constraints on rows in the validation dictionary.

3) Performs an object relationship check, which verifies that parent-child relationships between dictionary objects are enforced.

Performs an object relationship check and forces the parent-child relationship between validating dictionary objects.

Dictionaries involved:

The Dictionary Integrity Check operates on the following dictionary objects:

Tab$, clu$, fet$, uet$, seg$, undo$, ts$, file$, obj$, ind$, icol$, col$, user$, con$, cdef$, ccol$, bootstrap$, objauth$, ugroup$, tsq$, syn$, view$, typed_view$, superobj$, seq$, lob$, coltype$, subcoltype$, ntab$, refcon$, opqtype$, dependency$, access$, viewcon$, icoldep$, dual$, sysauth$, objpriv$, defrole$, and ecol$.

Run the health check manually

The DBMS_HM.RUN_CHECK package is required:

Https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_hm.htm#ARPLS144

DBMS_HM.RUN_CHECK (check_name IN VARCHAR2,-- > the name of the check to be invoked, viewed from the V$HM_CHECK and V$HM_CHECK_PARAM views, SELECT name FROM V$HM_CHECK WHERE INTERNAL_CHECK ='N' retrieves the list of checks that can be run manually by the user; run_name IN VARCHAR2: = NULL,-- > the name of the check to run, but not specified. Timeout IN NUMBER: = NULL,-- > the maximum time (in seconds) to run, but not specified. Input_params IN VARCHAR2: = NULL);-- > consists of name and value pairs, separated by the special character';'. The specific value is determined by the v$hm_check_param and v$hm_check view.

1) run DBMS_HM.RUN_CHECK

BEGIN DBMS_HM.RUN_CHECK ('Dictionary Integrity Check',' Dict_Check'); END; /

Usage reference: https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_hm.htm#ARPLS66419

2) View the report in SQLPLUS

SQL > SET LONG 100000SQL > SET LONGCHUNKSIZE 1000SQL > SET PAGESIZE 1000SQL > SET LINESIZE 512SQL > SELECT DBMS_HM.GET_RUN_REPORT ('Dict_Check') FROM DUAL DBMS_HM.GET_RUN_REPORT ('DICTCHECK')-Basic Run Information Run Name: Dict Check Run Id: 55001 Check Name: Dictionary Integrity Check Mode : MANUAL Status: COMPLETED Start Time: 2019-07-03 009 Error Encountered: 44.365864 + 08:00 End Time: 2019-07-03 09 Error Encountered: 04.668621 + 08:00 Error Encountered: 0 Number of Incidents Created: 0Input Paramters for the Run TABLE_NAME=ALL_CORE_ TABLES CHECK_MASK=ALLvRun Findings And Recommendations Finding Finding Name: Dictionary Inconsistency Finding ID: 55002 Type: FAILURE Status: OPEN Priority: CRITICAL Message: SQL dictionary health check: analyzetime for object new than sysdate 12 on object TAB$ failed Message: Damaged rowid is AAAAACAABAAAACRAAC-description: Object SYS.UNDO$ is referenced Finding Finding Name: Dictionary Inconsistency Finding ID: 55005 Type: FAILURE Status : OPEN Priority: CRITICAL Message: SQL dictionary health check: analyzetime for object new than sysdate 12 on object TAB$ failed Message: Damaged rowid is AAAAACAABAAAACVAAA-description: Object SYS.SEQ$ is referenced

3) View the report in adrci

ADRCI: Release 11.2.0.4.0-Production on Wed Jul 3 09:52:43 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.ADR base = "/ u01/app/oracle" adrci > show hm_runADR Home = / u01applicoracleUniple, diagrammogram, Tnslsnr, ProdbActionlistenerRandProdbAcer, listenerRose. * HM RUN RECORD 130 million dollars * RUN_ID 56581 RUN_NAME Dict_Check CHECK_NAME Dictionary Integrity Check NAME_ID 24 MODE 0 START_TIME 2019-07-03 09Viru 58 purl 40.350313 + 08:00 RESUME_TIME END_TIME 2019-07-03 09 FLAGS 58 SRC_INCIDENT_ID 56.425829 + 08:00 MODIFIED_TIME 2019-07-03 09 FLAGS 5 SRC_INCIDENT_ID 0 NUM_INCIDENTS 0 ERR_NUMBER 0 REPORT_FILE adrci > create report hm_run Dict_Checkadrci > show report hm_run Dict_Check...adrci > show hm_run***HM RUN RECORD 130 * * RUN_ID 56581 RUN_NAME Dict_Check CHECK_NAME Dictionary Integrity Check NAME_ID 24 MODE 0 START_TIME 2019-07-03 09 0 START_TIME 58 MODIFIED_TIME 40.350313 + 08:00 RESUME_TIME END_TIME 2019-07-03 09 59 TIMEOUT 56.425829 + 08:00 MODIFIED_TIME 2019-07-03 09 TIMEOUT 0 FLAGS 0 STATUS 5 SRC_INCIDENT_ID 0 NUM_INCIDENTS 0 ERR_NUMBER 0 REPORT_FILE / u01/app/oracle/diag/rdbms/PROD/PROD/hm/HMREPORT_Dict_Check.hm REPORT_FILE has been generated Xml format.

Other:

The check parameters may be different for each type, such as block integrity checks:

Begin DBMS_HM.RUN_CHECK ('Data Block Integrity Check',' f7 blockblockcheck check, input_params = > 'BLC_DF_NUM=7;BLC_BL_NUM=100'); end;/

Specific parameters:

SELECT a.* FROM v$hm_check_param a, v$hm_check b WHERE a.check_id = b.id AND b.name = 'Data Block Integrity Check'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.