MySQL HeatWave on OCI: recover from a dropped table or bad UPDATE with point-in-time restore

#When to use this runbook

Someone ran DROP TABLE, TRUNCATE, or an UPDATE/DELETE without a WHERE clause on a MySQL HeatWave DB system. The DB system itself is healthy. Only the data is wrong.

A HeatWave restore never overwrites the running DB system. It always creates a new DB system from a backup or from a point in time. The recovery has three steps:

1. Restore to a new DB system at a point just before the mistake.
2. Copy the affected tables or rows from the restored system back to production.
3. Delete the restored system.

This keeps production online the whole time. If the whole database is beyond repair, you can repoint the application to the restored system instead.

Caution

Don't "fix" the data by hand on production while the restore runs. Every extra write makes the later merge harder. If the app keeps writing to the damaged table, consider stopping the writing job first.

#1. Set variables and check what you can restore

C=ocid1.compartment.oc1..xxxx
DB=ocid1.mysqldbsystem.oc1..xxxx

oci mysql db-system get --db-system-id $DB \
  --query 'data.{name:"display-name", state:"lifecycle-state", version:"mysql-version", shape:"shape-name", storage:"data-storage-size-in-gbs", subnet:"subnet-id", ad:"availability-domain", backup:"backup-policy", pitr:"point-in-time-recovery-details"}'

Check two things in the output:

• backup-policy.pitr-policy.is-enabled: is PITR on?
• point-in-time-recovery-details: shows time-earliest-recovery-point and time-latest-recovery-point. Your target time must fall between them. (Field names follow the API model PointInTimeRecoveryDetails; check your CLI version's output if they differ.)

List the backups too. You'll need them if PITR is off:

oci mysql backup list -c $C --db-system-id $DB --lifecycle-state ACTIVE --all \
  --query 'data[].{name:"display-name", id:id, type:"backup-type", created:"time-created", how:"creation-type"}' \
  --output table

#2. Pin down the time of the mistake

You need a timestamp in UTC that is before the bad statement. Sources, in order of usefulness:

• Application or job logs, or the deployment pipeline that ran the migration.
• The slow query log or general log, if you enabled them in the DB system's configuration.
• The person who ran it. Ask early and write it down.

You can also confirm it from the binary log if your account has the replication privileges needed to read it remotely (check the grants on your admin user):

SHOW BINARY LOGS;

mysqlbinlog --read-from-remote-server --host=10.0.2.15 --user=admin -p \
  --base64-output=DECODE-ROWS --verbose binlog.000123 | grep -n -B5 -i "DROP TABLE"

Pick a recovery point a minute or two before the event. A point that is a little too early loses a few minutes of good writes for the affected tables. A point that is too late restores the damage.

Tip

Before you start the restore, record the latest good row in the damaged table on production (for example, SELECT MAX(id), MAX(updated_at)). It tells you later which rows were written after the recovery point and must not be overwritten.

#3. Restore to a new DB system

Restore into the same VCN, so production can reach the restored system for the copy. Use the same shape (or larger) and at least the same storage.

#Point in time (PITR enabled)

oci mysql db-system create -c $C \
  --display-name "prod-mysql-restore-$(date +%Y%m%d%H%M)" \
  --shape-name "MySQL.4" \
  --subnet-id ocid1.subnet.oc1..xxxx \
  --availability-domain "Uocm:EU-FRANKFURT-1-AD-1" \
  --data-storage-size-in-gbs 200 \
  --source '{"sourceType":"PITR","dbSystemId":"'"$DB"'","recoveryPoint":"2026-10-31T08:42:00.000Z"}' \
  --wait-for-state ACTIVE --wait-for-state FAILED --max-wait-seconds 7200

If you leave out recoveryPoint, the restore goes to the latest available point. That is not what you want after a bad change.

#From a backup (PITR disabled)

oci mysql db-system create -c $C \
  --display-name "prod-mysql-restore" \
  --shape-name "MySQL.4" \
  --subnet-id ocid1.subnet.oc1..xxxx \
  --availability-domain "Uocm:EU-FRANKFURT-1-AD-1" \
  --data-storage-size-in-gbs 200 \
  --source '{"sourceType":"BACKUP","backupId":"ocid1.mysqlbackup.oc1..xxxx"}' \
  --wait-for-state ACTIVE --wait-for-state FAILED --max-wait-seconds 7200

Notes:

• Shape names vary by region and generation. List the valid ones with oci mysql shape list -c $C.
• The restored system keeps the source's admin credentials and MySQL version by default.
• Restore time depends on data size and, for PITR, on how many binary logs must be replayed. A failed backup in the chain makes it longer.
• Restores from a DB system with a HeatWave cluster bring back InnoDB data only. Add a HeatWave cluster to the restored system only if you need to run analytics on it.

If the create fails, check the work request:

oci mysql work-request list -c $C --query 'data[0:5].{op:"operation-type", status:status, id:id}' --output table

#4. Verify the restored data

Connect to the restored system's private IP and check the affected objects:

SELECT COUNT(*), MAX(id), MAX(updated_at) FROM appdb.orders;
SHOW CREATE TABLE appdb.orders\G

The table should exist (for a drop) or hold the pre-change values (for a bad update).

#5. Copy the data back to production

#A dropped or truncated table: dump and load with MySQL Shell

// mysqlsh admin@<restored-ip> --js
util.dumpTables("appdb", ["orders"], "/backup/orders_pitr", {threads: 4})

// mysqlsh admin@<production-ip> --js
// Loads into a side schema first, so nothing on production is overwritten
util.loadDump("/backup/orders_pitr", {schema: "appdb_recovered", threads: 4})

Then swap or merge on production:

-- Dropped table: move it back into place
RENAME TABLE appdb_recovered.orders TO appdb.orders;

#A bad UPDATE or DELETE: merge only the affected rows

Load the restored copy into appdb_recovered as shown above. Then repair production from it:

-- Re-insert deleted rows
INSERT INTO appdb.orders
SELECT r.* FROM appdb_recovered.orders r
LEFT JOIN appdb.orders p ON p.id = r.id
WHERE p.id IS NULL;

-- Undo a bad UPDATE of one column, only for rows not changed since the recovery point
UPDATE appdb.orders p
JOIN appdb_recovered.orders r ON r.id = p.id
SET p.status = r.status
WHERE p.updated_at <= '2026-10-31 08:42:00';

Warning

Run the merge in a transaction and check the row counts before COMMIT. Rows that the application changed legitimately after the recovery point must not be overwritten. Agree on the rule with the application owner.

#6. Clean up

When the business confirms the data, drop the side schema and delete the restored DB system so you stop paying for it:

oci mysql db-system delete --db-system-id ocid1.mysqldbsystem.oc1..restored --force

If the restored system has delete protection on, turn it off first in the console or with oci mysql db-system update.

#Prevent it next time

Enable PITR and set a sensible retention and backup window. Get the exact JSON skeleton for your CLI version with oci mysql db-system update --generate-param-json-input backup-policy:

oci mysql db-system update --db-system-id $DB \
  --backup-policy '{"isEnabled":true,"retentionInDays":14,"windowStartTime":"02:00","pitrPolicy":{"isEnabled":true}}'

• Manual backup before risky changes: take one right before schema migrations or bulk fixes. Manual backups can be kept longer than automatic ones:

  oci mysql backup create --db-system-id $DB --backup-type FULL \
    --display-name "pre-release-2026-10-31" --retention-in-days 30
  

• Cross-region copy for regional DR: oci mysql backup copy --compartment-id $C --source-backup-id <backup-ocid> --source-region eu-frankfurt-1, run against the destination region. This needs the MYSQL_BACKUP_COPY permission, which the mysql-family resource type does not include.

• Deletion policy: enable delete protection and keep automatic backups plus a final backup when a DB system is deleted.

• Practice: run a PITR restore into a test subnet once a quarter and time it. That number is your real RTO.

• Least privilege: application accounts should not hold DROP on production schemas. Run migrations under a separate account.