Sev1: bad data or deleted rows in Autonomous Database — point-in-time clone, Flashback and copy back

#Symptoms

• A release or script ran DELETE, UPDATE without a proper WHERE, or dropped a table.
• Users report missing or wrong records from a known time.

#1. Stop the damage

• Disable the job or app feature that is writing bad data.
• Note the exact time (UTC) before the damage started. Everything below depends on it.

#2. Small and recent: use Flashback in place

If undo retention still covers the time, query the past directly:

SELECT COUNT(*) FROM orders AS OF TIMESTAMP TO_TIMESTAMP('2026-10-31 08:10:00','YYYY-MM-DD HH24:MI:SS');

Copy missing rows back:

INSERT INTO orders
SELECT * FROM orders AS OF TIMESTAMP TO_TIMESTAMP('2026-10-31 08:10:00','YYYY-MM-DD HH24:MI:SS') o
WHERE NOT EXISTS (SELECT 1 FROM orders c WHERE c.order_id = o.order_id);
COMMIT;

A dropped table may still be in the recycle bin:

SELECT object_name, original_name, droptime FROM user_recyclebin;
FLASHBACK TABLE orders TO BEFORE DROP;

If you get ORA-01555 snapshot too old or ORA-08180, the time is outside retention. Go to step 3.

#3. Larger damage: point-in-time clone

Create a clone of the database as it was before the damage. Production keeps running.

oci db autonomous-database create-from-backup-timestamp \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..aaaa \
  --timestamp "2026-10-31T08:10:00.000Z" \
  --clone-type FULL \
  --compartment-id $COMP \
  --db-name ORDERSPIT \
  --display-name orders-pit-inc12345 \
  --admin-password '<strong-password>' \
  --compute-model ECPU --compute-count 4 \
  --wait-for-state AVAILABLE

Parameter names can vary slightly between CLI versions; run oci db autonomous-database create-from-backup-timestamp --help to confirm on your build.

Caution

Don't use "restore" on the production database for a partial data problem. A restore rewinds everything, losing all good transactions after that time. Clone, then copy back only what you need.

#4. Copy the right data back

Create a database link from production to the clone (or export/import with Data Pump for large sets):

-- On production, as ADMIN; the clone's wallet must be in the credential/directory setup
BEGIN
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name       => 'PITLINK',
    hostname           => '<clone-host>.adb.eu-frankfurt-1.oraclecloud.com',
    port               => 1522,
    service_name       => '<clone-service>_high.adb.oraclecloud.com',
    ssl_server_cert_dn => '<cert-dn>',
    credential_name    => 'PIT_CRED',
    directory_name     => 'DATA_PUMP_DIR');
END;
/

Then compare and repair:

INSERT INTO orders
SELECT * FROM orders@PITLINK p
WHERE NOT EXISTS (SELECT 1 FROM orders c WHERE c.order_id = p.order_id);

For updated rows, MERGE from the clone for the affected keys. Have the app owner validate counts before and after.

#5. Clean up

Drop the database link and terminate the clone once the business signs off, to stop paying for it:

oci db autonomous-database delete --autonomous-database-id <clone-ocid> --force

#Prevent it next time

• Require WHERE-clause review for data-fix scripts; run them first on a refreshable clone.
• Keep backup retention aligned to how long it takes your business to notice bad data.
• Use separate users for apps and admins; restrict DROP and bulk DELETE rights.
• Consider Flashback Time Travel on critical tables for long-range history.