Healthcheck Sample Report

/live_board/v2/downloads?uid=c688938b-3324-41ce-94ec-c45494ccd7af&redirect=true

Health Check Example Report Oracle Database and infrastructure health assessment

SYSTEM OVERVIEW

Database Name Database Version

DB1 12.1.0.2.0

DB2 12.1.0.2.0

DB3 12.1.0.2.0

DB4 12.1.0.2.0

DB5 12.1.0.2.0

DB6 12.1.0.2.0

DB7 12.1.0.2.0

DB8 12.1.0.2.0

DB9 12.1.0.2.0

DB10 12.1.0.2.0

DB11 12.1.0.2.0

DB12 12.1.0.2.0

DB13 12.1.0.2.0

DB14 12.1.0.2.0

DB15 12.1.0.2.0

DB16 12.1.0.2.0

DB17 12.1.0.2.0

DB18 12.1.0.2.0

DB19 12.1.0.2.0

DB20 12.1.0.2.0

Databases analyzed

Hardware Environment

Server Type CPU Memory Disk Flash Network

Exadata X7-2 Half Rack

4x Database servers, 192 cores

8x 24-core Xeon 8160 processors (2.1 GHz)

384 GB (default) to 1.5 TB (max)

4x 600 GB 10,000 RPM disks (Hot-Swappable) –

Expandable to 8 None

2x 10 Gb copper Ethernet ports (client) OR 2 x10/25 Gb optical Ethernet port 1x 1/10 Gb copper Ethernet port (mgmt) 2x 10/25 Gb

optical Ethernet ports (client) 2x QDR (40 Gb)

InfiniBand ports 1x ILOM Ethernet port 4x 10 Gb

copper (client – optional)

Storage Server HC

2x 10-core Xeon 4114 processors (2.2 GHz)

192 GB 12x 10 TB 7,200 RPM

disks 4x 6.4 TB NVMe PCIe 3.0

Flash cards 2x QDR (40 Gb) InfiniBand ports

1x ILOM Ethernet port Storage Server EF 192 GB None

8x 6.4 TB NVMe PCIe 3.0 Flash cards

Data collected

AWR Miner reports for each database. Exachk reports for all 4 nodes.

Issues reported

Performance issues for all nodes during high workload activity.

ARCHITECTURE OVERVIEW

Standby DR – node 4

Cascade standby

Site B Site C

Main production environment

4-node RAC

4-node RAC Single instance

Test databases on VMsFleet of dev databases in CDBs

X86 individual physical hosts

VMWare ESXi Exadata

Enterprise Manager

Goldengate

Backup Storage

RMAN tablespace

RMAN incremental + 2-day cumulative

Datapump export – monthly partitions

X86 hosts

X86 individual physical hosts

Prod DBs DB Links

DBA

Clones

Automated full RMAN backup and restore on clones

Partitioning

AWR

Site A

FINDINGS Resource Utilization Solution

CPU Memory I/O Disk Space Hardware overview

80% in peak 35% average

712GB out of 1024GB total.

71% Memory used.

104.210 read Iops(Peak) 25.013 write Iops(Peak)

1031.2MB/s read MB(Peak)

241MB/s write MB(Peak)

375TB out of 750TB total.

50% Disk space used.

High CPU, memory and IO usage. Disk space almost full.

It is recommended to upgrade to Exadata X9

Wait Event Activity Performance analysis

Database instance Wait event type Instance-level Hardware-level

Instance01 21% Application wait – enq: TX – row lock contention Investigate application wait Doc ID

132146.1 Exadata

Instance02 13% Commit wait – Log file sync Investigate application wait Doc ID

132146.1 Exadata

Instance03 40% Commit wait – Log file sync Investigate application wait Doc ID

132146.1 Exadata

Instance04 51% Concurrency wait - cursor: pin S wait on X Investigate application wait Doc ID

132146.1 Exadata

Database version and configuration observations MAA Alignment

Database instance Observation Instance-level Hardware-level

Instance01 Database version outdated: 11.2.0.4 Upgrade database version Exadata

Instance02 Database version outdated: 12.1.0.2 Upgrade database version Exadata

Instance03 Flashback database enabled constantly Disable Flashback Exadata

Reference: https://www.oracle.com/us/assets/lifetime-support-technology-069183.pdf

2 0

0 9

2 0

1 0

2 0

1 1

2 0

1 2

2 0

1 3

2 0

1 4

2 0

1 5

2 0

1 6

2 0

1 7

2 0

1 8

2 0

1 9

2 0

2 1

2 0

2 2

2 0

2 3

2 0

2 4

2 0

2 5

2 0

2 6

2 0

2 7

Oracle 18 (12.2.0.2)

EXTENDED

Waived EXTENDEDOracle 11.2

Oracle 12.1

Oracle 12.2.0.1

Oracle 19 (12.2.0.3)

Paid Extended SupportPremier Support Waived Extended Support

MARKET DRIVEN

Market Driven Support

Current Version

▼

ORACLE DATABASE VERSION STATUS

MARKET DRIVEN

https://www.oracle.com/us/assets/lifetime-support-technology-069183.pdf

NOTABLE EVENTS

Instance01 Instance02 Instance03

Date Date Date

12.03.2023 17:00

Event type Event type Event type

Application spike – row lock contention

Instance04 Instance05 Instance06

Date Date Date

12.03.2023 17:00

Event type Event type Event type

Application spike – row lock contention

Wait Wait

Instance07 Instance08 Instance09

Date Date Date

Event type Event type Event type

RECOMMENDATIONS

Component Recommendation

Instance01 Investigate application wait

Host01 Check parameter X

Hardware Check network card X

Host02 Monitor CPU activity

Instance02 Investigate log file sync

All Databases

Check database version

Instance01 etc

Instance01 Investigate application wait

CPU UTILIZATION

8.4 8.5

13.2

8.4 8.4 7.8

11.8

7.7

27.8

35.3 35.1

61.2

35 35

56.8

71.6

15.1

60.7

17.3 17.1

22.4

17.3 17.3

43.6 41.7

12.2

47.1

80 s x

id w

p 1

s x

id w

p 1

s x

id w

p 1

s x

id w

p 1

s x

id w

p 1

s x

m d

b 0

2 p

s x

m d

b 0

2 p

s x

id w

p 2

s x

m d

b 0

1 p

FILENPRDSAS_AML IDWP WFPRD MSTPRD INFRAP FCPRD TWP EDWPRD

LOW HIGH Average

HOST04HOST03

HOST02HOST01

DATABASE CPU UTILIZATION HOST CPU UTILIZATION

CPU UTILIZATION

MAX AVE MIN Instance Name /

Number

87.38 (23/02/28 23:14) 45.36 1.5 (23/03/03 13:32) A#1

91 (23/02/23 13:59) 31.31 0.25 (23/02/26 15:30) A#1

150.69 (23/02/28 11:45) 27.96 4.38 (23/03/10 01:00) A#1

94.42 (23/02/21 20:30) 27.72 0.92 (23/02/25 08:15) B#1

698.96 (23/03/14 15:44) 26.41 3.19 (23/02/26 15:45) C#1

112.3 (23/02/23 16:14) 24.68 0.1 (23/03/04 11:15) D#1

88.88 (23/02/22 18:00) 23.36 0.88 (23/02/24 02:29) E#1

91.38 (23/02/28 14:00) 20.23 0.25 (23/03/02 18:00) F#1

64.53 (23/03/14 05:59) 19.79 3.28 (23/02/19 04:44) G#1

28 (23/03/04 20:45) 17.67 14.63 (23/02/18 10:45) H#1

92.63 (23/03/09 21:59) 17.27 0.38 (23/03/01 18:29) I#1

57.83 (23/03/01 07:30) 17.24 0.12 (23/03/04 18:44) J#2

56.75 (23/03/07 16:00) 15.59 8 (23/03/09 02:59) K#1

82.67 (23/02/21 18:59) 14.28 3.83 (23/02/18 01:59) L#1

85.5 (23/03/01 06:00) 14.09 0.25 (23/03/06 00:00) M#1

54.87 (23/03/01 09:45) 13.59 0.47 (23/02/20 03:00) N#1

64.75 (23/03/05 06:45) 12.87 0 (23/02/18 13:44) O#1

86 (23/03/15 00:00) 12.86 0.38 (23/02/25 05:30) P#1

90.2 (23/02/18 06:30) 12.16 0.71 (23/03/12 21:30) Q#1

89.83 (23/03/09 09:59) 11.99 0.25 (23/03/03 14:44) R#1

43.8 (23/03/09 21:01) 11.52 1.52 (23/03/12 08:00) S#1

45.96 (23/02/20 23:07) 11.3 0.18 (23/03/03 13:07) TDA#1

85.67 (23/03/16 09:00) 10.35 1.17 (23/03/04 13:00) DEGC#1

TOP 3 CPU consuming databases

MEMORY UTILIZATION SGA USAGE

PGA USAGE

HOST MEMORY

Overall Host memory utilization can be seen on the graphic below:

Reserved

– 5%

Memory Used –

27%

Available

Memory; 68%

MEMORY ADVISOR RECOMMENDATIONS

Consider increasing SGA with ~10GB on DB1 for a 32%

decrease in Physical Reads.

+32% +45% +61% +17%

Consider increasing SGA with ~4GB on DB2 and PGA with ~0.8GB for a

10% and 35% Physical Reads and MB Read/Written to disk.

Consider increasing SGA with ~10GB on DB3 for a 60-61% decrease in Physical Reads.

Consider increasing PGA with ~1.1GB on DB4 for a 17% increase

in MB R/W performance.

Database Name Database Size (GB)

INSTANCE01 910.1

INSTANCE02 618.15

INSTANCE03 336.66

INSTANCE04 206.76

INSTANCE05 756.31

INSTANCE06 810.51

INSTANCE07 515.66

INSTANCE08 971.38

INSTANCE09 241.13

INSTANCE10 333.09

INSTANCE11 862.11

*for the databases analyzed – not total on the machine

5065,26GB

910.1

618.15

336.66

206.76

756.31

810.51

515.66

971.38

241.13

333.09

862.11

200

400

600

800

1000

1200

DATABASE SPACE UTILIZATION

IO UTILIZATION – Iops

TOP WAIT EVENTS

ACTIVE SESSIONS – MAIN WAIT EVENT CLASSES Wait event

class Description

Administrative Waits resulting from DBA commands that cause users to wait (for example, an index rebuild)

Application Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)

CPU Time spent on transaction processing. This is not an indication of performance degradation or a general issue. The more time spent on CPU, the better.

Cluster Waits related to Oracle Real Application Clusters resources (for example, global cache resources such as 'gc cr block busy')

Commit This wait class only comprises one wait event - wait for redo log write confirmation after a commit (that is, 'log file sync')

Concurrency Waits for internal database resources (for example, latches)

Configuration Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size)

Network Waits related to network messaging (for example, 'SQL*Net more data to dblink')

Other Waits which should not typically occur on a system (for example, 'wait for EMON to spawn')

Scheduler Resource Manager related waits (for example, 'resmgr: become active')

System I/O Waits for background process I/O (for example, DBWR wait for 'db file parallel write')

User I/O Waits for user I/O (for example 'db file sequential read')

Instance01

WAIT EVENT INFORMATION

Enq: TX - row lock contention

This wait event can occur for several reasons.

• If one user is wanting to update or delete a row or rows that another session is modifying. The session holding the lock will release it when it performs a COMMIT or ROLLBACK.

• If a session is waiting due to potential duplicates in a UNIQUE index. If two sessions try to insert the same key value, the second session has to wait to see if an ORA-0001 should be raised or not. The session holding the lock will release it when it performs a COMMIT or ROLLBACK.

• If a session is waiting due to a shared bitmap index fragment. Bitmap indexes index key values and a range of rowids. Each entry in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock.

Finding Locks and Lock Holders

Query V$LOCK to find the sessions holding the lock. For every session waiting for the event enqueue, there is a row in V$LOCK with REQUEST <> 0. Use one of the following two queries to find

the sessions holding the locks and waiting for the locks. If there are enqueue waits, you can see these using the following statement:

SELECT * FROM V$LOCK WHERE request > 0;

To show only holders and waiters for locks being waited on, use the following:

SELECT DECODE(request,0,'Holder: ','Waiter: ') ||

sid sess, id1, id2, lmode, request, type

FROM V$LOCK

WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE

request > 0)

ORDER BY id1, request;

For more information on how to resolve and prevent this wait event in the future, please consult My Oracle Support Doc ID 1476298.1.

Instances affected

Instance01, etc.

The TX Lock "Transaction Enqueue" is used to maintain the integrity of a transaction while it is executing preventing other sessions from modifying the same data at the same time. If contention is occurring, 'enq: TX - row lock contention' is likely to become a significant component of the DB time and affect the performance of other sessions. Once you have established that you have high waits for 'enq: TX - row lock contention', the next stage is to identify the objects and the SQL involved.

Document 62354.1 TX Transaction locks - Example wait scenarios

Document 1946502.1 Resolving Issues Where 'enq: TX - contention' Waits are Occurring

Document 1966048.1 WAITEVENT: "enq: TX - row lock contention" Reference Note

Document 197057.1 TX Lock "Transaction Enqueue“

Document 1392319.1 Primary Note: Locks, Enqueues and Deadlocks

Reference notes

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=434802490519860&parent=EXTERNAL_SEARCH&sourceId=TROUBLESHOOTING&id=1476298.1&_afrWindowMode=0&_adf.ctrl-state=18n0aknrda_58 https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1476298.1&id=62354.1 https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1476298.1&id=1946502.1 https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1476298.1&id=1966048.1 https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1476298.1&id=197057.1 https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1476298.1&id=1392319.1

ORACHK RESULTS - DATABASE Status Type Message Status On

FAIL SQL Check Table AUD$[FGA_LOG$] should use Automatic Segment Space Management All Databases

WARN SQL Check Consider investigating the sessions that are currently waiting and take necessary action All Databases

WARN SQL Check Consider adding more redo log groups or increase the size of redo logs All Databases

WARN SQL Check Consider increasing the value of the session_cached_cursors database parameter All Databases

WARN SQL Check Consider investigating changes to the schema objects such as DDLs or new object creation All Databases

WARN Patch Check Oracle patch 31211220 is not applied on RDBMS_HOME All Homes

WARN Patch Check Oracle patch 32043701 is not applied on RDBMS_HOME All Homes

WARN Patch Check Perl Patch 33912872 is not found in 19c RDBMS_HOME. All Homes

WARN OS Check OS Kernel Parameter tcp_smallest_anon_port is not set to recommended value All Database Servers

WARN OS Check OS Kernel Parameter udp_smallest_anon_port is not set to recommended value All Database Servers

WARN Patch Check Oracle patch 28907129 is not applied on RDBMS_HOME All Homes

WARN SQL Check Duplicate objects were found in the SYS and SYSTEM schemas All Databases

WARN Patch Check Oracle patch 29259068 is not applied on RDBMS_HOME All Homes

WARN Patch Check Oracle patch 26749785 is not applied on RDBMS_HOME All Homes

WARN SQL Check Consider setting the value of the parameter _cursor_obsolete_threshold to 1024 for Non-Multitenant environment which is the appropriate recommended value All Databases

WARN OS Check OSWatcher is not running as is recommended. All Database Servers

WARN SQL Check One or more redo log groups are not multiplexed All Databases

WARN OS Check Kernel Parameter MAX-SHM-IDS NOT Configured According to Recommendation All Database Servers

WARN OS Check Kernel Parameter MAX-SEM-NSEMS NOT Configured According to Recommendation All Database Servers

WARN OS Check Kernel Parameter MAX-SEM-IDS NOT Configured According to Recommendation All Database Servers

WARN OS Check Oracle database software owner soft stack shell limit is not configured according to recommendation All Database Servers

WARN SQL Check Consider unsetting database Parameter DB_FILE_MULTIBLOCK_READ_COUNT All Databases

WARN SQL Check There are some application objects with STALE statistics All Databases

ORACHK RESULTS - MAA Outage Type Status Type Message Status On Details

SOFTWARE MAINTENANCE BEST PRACTICES

FAIL

•Proactive hardware and software maintenance helps avoid critical issues and helps maintain the highest stability and availability of your system. By running the latest version of exachk manually or via Enterprise Manager, automatic detection occurs for the following: Software version mismatches on the system. •Known critical issue exposure for your specific environment. •Software releases that are older than recommended versions. 1.Furthermore, the suggested "Recommended Versions" can be leveraged when planning for your next planned maintenance window. Note that not all Exadata Software components need to be upgraded during one planned maintenance window; however it is advised to maintain a regular maintenance schedule. The recommended frequency is 3 to 12 months depending on security and business requirements. Oracle recommends patching and upgrading in the following order: Grid Infrastructure Software and Oracle Database Software. Grid Infrastructure should always be equal to or higher than the highest Oracle Database Software version. 2.Exadata Database Server Software. For Exadata Database Server Software upgrades, run and evaluate exachk and dbnodeupdate precheck outputs. 3.Exadata Storage Server Software. For Exadata Storage Server Software upgrades, run and evaluate exachk and patchmgr precheck outputs. 4.InfiniBand Switch Software. For InfiniBand Switch Software upgrades, run and evaluate exachk and patchmgr precheck outputs.

Component Host/Location Found version Recommended versions Status

DATABASE SERVER Database Home INSTANCE01:/oracle19c 19.0.0.0.0 19.17.0.0.221018 19 RU is older than recommended.

Outage Type Status Type Message Status On Details

DATABASE FAILURE PREVENTION BEST PRACTICES

WARN

Oracle database can be configured with best practices that are applicable to all Oracle databases, including single-instance, Oracle RAC databases, Oracle RAC One Node databases, and the primary and standby databases in Oracle Data Guard or Oracle Golden Gate configurations.

Key HA Benefits:

(1) Improved recoverability (2) Improved stability

WARN SQL Check Database has one or more dictionary managed tablespace All Databases

WARN SQL Check Some tablespaces are not using Automatic segment storage management All Databases

WARN SQL Parameter Check fast_start_mttr_target has NOT been changed from default All Instances

WARN Database Check Redo log files should be appropriately sized All Databases

EXADATA SYSTEM COMPARISON

CURRENT INFRASTRUCTURE

EXADATA ESTIMATE

PEAK CPU

MEMORY UTILIZATION

IO UTILIZATION

WAIT EVENT PREVENTION

SYSTEM HEALTH

20%

100%50%

72%

27%27%

50%

75%

100%

ORACLE EXADATA HARDWARE SPECIFICATIONS

Other benefits include extra space for consolidation, use of RAC, in-memory options, flash storage, all Exadata features(storage indexes, offloading, etc.) faster network, easier administration, automatic management, and many more.

Exadata Maximum Availability Architecture (MAA) Blueprint for HA: Designed/Tested Against Failure Scenarios

LAN

Within Exadata: Full Fault Tolerance

Redundant Software

Active clusters, Disk/flash mirroring

Redundant Hardware

Servers, Disks, Flash, Network, Power

Redo-based change

replication with data

consistency checking

Local Standby for HA Failover

Redundant Systems Redundant Databases

Within a Site: Local Data Guard

Online patching, reconfiguration,

expansion

WAN

Across Sites: Data Guard for DR

Fastest RAC Instance and Node Failure Recovery | Fastest Backup - RMAN Offload to Storage Deep ASM Integration | Fastest Data Guard Redo Apply | Complete Failure Testing with Shortest Brownouts

Ref. oracle.com/goto/maa

Remote Standby for Disaster Recovery

Redundant Systems Redundant Databases

RECOVERY APPLIANCE Incremental forever back-up with near-zero data loss

End-to-End Oracle Recovery Validation Near Zero Data Loss for DR

Day 1 Full

Day 2 Changes

Day N Changes Virtual Full Backup

EM Real-Time Protection Status & Space Monitoring

Day 1 StateDay 2 StateDay N State

Databases

Transactional Block Changes

No More Full Backups, Incremental Forever

Oracle DB 12c-21c on Any Platform

Cloud Storage

Remote Replica

Tape

Scale out & Lifecycle

Data protection

Oracle Maximum Availability Architecture (MAA) Standardized Reference Architectures for Never-Down Deployments

Reference architectures

Deployment choices

HA features, configuration and operational practices

Customer insights and expert recommendations

Production site Replicated site

Replication

Generic Systems Engineered Systems BaseDB, ExaDB/ExaCC Autonomous DB

Flashback RMAN + ZDLRA

Continuous availability

Application Continuity

Edition-based Redefinition

Active replication

Active Data Guard

RAC ShardingFPP

24/7

GoldenGate

Online Redefinition

Zero Downtime Migration (ZDM) B ro

n z e

S il

v e

r G

o ld

P la

ti n

u m

Introduction Slide 1: Health Check Example Report

System Overview Slide 2 Slide 3

Findings Slide 4 Slide 5 Slide 6 Slide 7

Analysis Slide 8 Slide 9 Slide 10 Slide 11 Slide 12 Slide 13 Slide 14 Slide 15 Slide 16 Slide 17 Slide 18

Solutions Slide 19 Slide 20: Exadata Maximum Availability Architecture (MAA) Slide 21: RECOVERY APPLIANCE

Best practices Slide 22: Oracle Maximum Availability Architecture (MAA)

Healthcheck Sample Report

Item Type: pdf