MySQL HeatWave Service – Comprehensive Review

A set of 70 Q&A flashcards covering architecture, provisioning, high availability, backups, replication, migration, read replicas, and exam-level details for Oracle MySQL HeatWave Service.

What is the main advantage of Oracle HeatWave Service compared with traditional MySQL deployments?

It combines transactional and analytical processing in a single MySQL-compatible database, eliminating ETL and delivering extreme performance.

Which engine in HeatWave Service executes analytical queries?

The HeatWave analytics engine.

Which HeatWave solution allows querying data that lives in Object Storage?

HeatWave Lakehouse.

What does HeatWave AutoML provide?

An automated pipeline for creating, training, evaluating, and explaining MySQL-integrated machine-learning models.

What Oracle Cloud Infrastructure element is a localized geographic area containing one or more Availability Domains?

A Region.

How many Fault Domains exist within each Availability Domain in OCI?

Three.

Why would you use OCI compartments?

To organize and isolate resources across the tenancy for security and billing.

What is a Virtual Cloud Network (VCN)?

A region-level, software-defined private network in OCI that contains subnets, route tables, and security lists.

Give two differences between public and private OCI subnets.

Public subnets can have public IPs and allow ingress from the internet; private subnets have no public IPs and block internet ingress.

Which OCI component manages user authentication and authorization for HeatWave resources?

OCI Identity and Access Management (IAM) policies.

List the four core resources that must exist before creating a HeatWave DB System.

Tenancy, Compartment, IAM policy (mysql-family), and a VCN with an appropriate subnet.

What two hardware characteristics does an OCI DB System shape define?

Number of CPUs and amount of Memory.

What two types of encryption protect data in a HeatWave DB System?

Data-in-transit encryption (TLS) and data-at-rest encryption (OCI Block Storage encryption).

How many MySQL instances make up a High Availability (HA) DB System?

Three—one primary and two secondary instances.

What automatic process promotes a secondary instance if the primary fails in an HA system?

Automatic failover via MySQL Group Replication.

Before enabling High Availability, which two prerequisites must be met for the database tables?

All tables must have primary keys and crash recovery must be enabled.

Name two limitations of HeatWave High Availability.

Only the primary instance can be directly accessed, and enabling/disabling HA during a HeatWave load or reload can cause HA failure.

Which HeatWave component stores a persistent copy of in-memory data for fast reloads after failure?

The HeatWave Storage Layer.

What information is found on the Connections tab of a DB System details page?

The private IP address and the listening ports (MySQL and X Protocol) of the DB System endpoint.

Which two credentials are mandatory when connecting to a DB System with MySQL Shell?

A valid database user name/password and the DB System’s private IP address.

After selecting “Stop” on an active DB System, what state color indicates the instance is updating toward inactive?

Yellow.

What are the default delete settings for a DB System (delete protection and retain automatic backups)?

Delete protection OFF and retain automatic backups OFF.

Distinguish between full and incremental HeatWave backups.

Full backups include all data and are slower/larger; incremental backups include only changes since the last backup and are faster/smaller.

How long can you retain a manual HeatWave backup?

Between 1 and 365 days.

What feature lets you restore a HeatWave DB System to the exact moment before an error occurred?

Point-in-Time Recovery (PITR).

When restoring from a backup, what happens to the admin credentials?

They remain the same as in the source DB System.

Which two backup types should users avoid deleting manually?

Operator backups and final backups still within retention.

HeatWave inbound replication currently supports which replication mode only?

Single-source, row-based, asynchronous replication.

Name two common causes of inbound replication channel state 'Needs Attention'.

Connection errors (e.g., error 2003) and purged binary log files (error 13114).

What OCI object represents the configuration for inbound replication from a source to a HeatWave DB System?

A replication Channel.

In a replication channel, what SSL mode provides encrypted connections without validating certificates?

REQUIRED.

Which MySQL server setting must be ON for reliable GTID-based replication?

gtidmode=ON with enforcegtid_consistency=ON.

Why create a dedicated replication user on the source database?

To limit privileges, restrict allowed hosts, and store credentials separate from application users.

What OCI network component allows a DB System in one VCN to reach a source in another VCN in the same region?

A Local Peering Gateway (LPG).

Which command in MySQL Shell exports all schemas, users, and privileges for migration?

util.dumpInstance().

What does the ocimds=true option do during a MySQL Shell dump?

Performs HeatWave compatibility checks and comments out unsupported clauses.

Explain the purpose of the dump utility option createinvisiblepks.

Adds an invisible primary key to tables lacking one, ensuring HA compatibility.

During a dry run of util.dumpInstance, are any dump files created?

No; it only prints the actions that would be performed.

Which two compatibility modifications ensure non-InnoDB tables are accepted by HeatWave?

forceinnodb and striptablespaces.

What is the maximum number of read replicas you can attach to a single DB System?

Eighteen.

What OCI resource is automatically provisioned when the first read replica is created?

A Read Replica Load Balancer.

Give two use cases for HeatWave read replicas.

Offloading read-heavy workloads (scaling reads) and running business reporting without impacting the primary DB System.

How is maintenance performed on read replicas of an HA DB System?

Via rolling upgrades—one read replica is upgraded at a time, then the HA cluster.

Which two methods can clients use to reach a read replica endpoint securely from on-premises?

Site-to-Site VPN or FastConnect to the VCN.

What OCI console color indicates that a DB System has failed or cannot be created?

Red.

Which DB System operation can you perform while the instance is STOPPED?

Start, Restart, or Delete the DB System.

What channel operation removes replication settings and relay logs on an inactive channel?

RESET.

Which MySQL error code signals that a replica cannot connect to the source (connection refused)?

Error 2003 (HY000).

To fix purged binary log problems, which GTID variable must be updated on the replica?

gtid_purged.

What does lowercasetable_names default to on a HeatWave DB System?

0 (case-sensitive identifiers).

Which three actions does RESET REPLICA ALL perform on a MySQL replica?

Clears replication metadata, deletes relay logs, and removes source host/port and replication user credentials.

When replicating outbound from HeatWave to another MySQL server, which MySQL option must be enabled for fine-grained privilege revocation?

partial_revokes=ON.

What MySQL Shell utility imports dump files into a HeatWave DB System?

util.loadDump().

Which util.loadDump option updates the gtid_purged value on import?

updateGtidSet (e.g., 'append' or 'replace').

For migration, why might you specify a prefix in the dump outputURL when writing to Object Storage?

To organize dump files within the bucket under a common directory path.

Name two modifications that strip unsupported metadata from object DDL when exporting for HeatWave.

stripdefiners and striprestricted_grants.

What HeatWave Autopilot feature optimizes resource usage at scale?

Automatic performance tuning and lifecycle operations for the DB Service.

Which SQL routine is used in HeatWave AutoML to create a new machine-learning model?

ML_TRAIN.

In HeatWave AutoML, which model type would you choose to predict loan defaults (yes/no)?

Classification.

Which HeatWave architecture guarantees zero data loss with automatic failover?

High Availability (HA) DB System.

Which statement immediately kills a connection with ID 123 on a HeatWave DB System?

KILL 123; (executed as an administrative SQL statement).

What two parameters must match between a DB System and its customer configuration when updating settings?

The DB System’s shape and the configuration’s shape.

Which network rule is required on a bastion subnet to allow SSH access?

A stateful ingress rule permitting TCP port 22.

When creating a HeatWave cluster, which two settings must you supply?

The node shape and the number of nodes.

What prerequisite must be enabled before you can use Point-in-Time Recovery?

Automatic backups must be enabled on the DB System.

Which HeatWave Service feature is NOT fully managed and must be configured by the user?

Inbound replication.

How can you view the detailed optimizer decision process to learn why a query was not off-loaded to HeatWave?

Enable and inspect OPTIMIZER_TRACE output.

Which load balancer bandwidth limit currently applies to read-replica traffic?

Up to 8 Gbps.

Which subnet type (public or private) can host a HeatWave DB System?

Either; both public and private subnets are supported.

How do you initiate a manual switchover in an HA DB System from the OCI Console?

Open the DB System details page and choose Switchover, then select the desired Availability Domain.

What two attributes can you still edit after saving a HeatWave customer configuration?

The configuration’s name and description.

Which prerequisite shape requirement exists for creating read replicas?

The source DB System must use a shape with at least 4 OCPUs (or 8 ECPUs).