Oracle Flashback Database and MySQL Snapshots

Oracle Usage

Oracle Flashback Database is a special mechanism built into Oracle databases that helps protect against human errors by providing capabilities to revert the entire database back to a previous point in time using SQL commands. Flashback database implements a self-logging mechanism that captures all changes applied to a database and to data. Essentially, it stores previous versions of database modifications in the configured database Fast Recovery Area.

When you use an Oracle flashback database, you can choose to restore an entire database to either a user-created restore point, a timestamp value, or to a specific System Change Number (SCN).

Examples

Create a database restore point to which you can flashback a database.

CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;

Flashback a database to a previously created restore point.

shutdown immediate;
startup mount;
flashback database to restore point before_update;

Flashback a database to a specific time.

shutdown immediate;
startup mount;
FLASHBACK DATABASE TO TIME "TO_DATE('01/01/2017','MM/DD/YY')";

For more information, see FLASHBACK DATABASE in the Oracle documentation.

MySQL Usage

Snapshots are the primary backup mechanism for Amazon Aurora databases. They are extremely fast and nonintrusive. You can take snapshots using the Amazon RDS Management Console or the Amazon CLI. Unlike RMAN, there is no need for incremental backups. You can choose to restore your database to the exact time when a snapshot was taken or to any other point in time.

Amazon Aurora provides the following types of backups:

When you use Aurora MySQL 5.6 compatible, you can turn on the Aurora Backtrack feature. This feature is equivalent to Flashback Database option in Oracle.

This option applies to newly created MySQL-compatible Aurora database clusters and to MySQL-compatible clusters that have been restored from a backup. Make sure that you opt-in when you create or restore a cluster; you can’t turn this option on for a running cluster.

To backtrack your database using Amazon CLI, use the following example. This example demonstrates how to backtrack to instance for one day or 86,400 seconds:

aws rds modify-db-cluster
--db-cluster-identifier sample-cluster
--backtrack-window 86400

To monitor the backtrack operation, use the following example:

aws rds describe-db-cluster-backtracks
--db-cluster-identifier sample-cluster

Examples

The following steps to enable Aurora automatic backups and configure the backup retention window as part of the database creation process. This process is equivalent to setting the Oracle RMAN backup retention policy using the configure retention policy to recovery window of X days command.

The following table identifies the default automatic backup time for each region.

Use the following steps to perform a manual snapshot backup of an Aurora database. This process is equivalent to creating a full Oracle RMAN backup (BACKUP DATABASE PLUS ARCHIVELOG).

Use the following steps to restore an Aurora database from a snapshot. This process is similar to the Oracle RMAN commands RESTORE DATABASE and RECOVER DATABASE. However, instead of running in place, restoring an Aurora database creates a new cluster.

Use the following steps to restore an Aurora MySQL database backup to a specific point in time. This process is similar to running the Oracle RMAN command SET UNTIL TIME "TO_DATE('XXX')" before running RESTORE DATABASE and RECOVER DATABASE.

Amazon CLI Backup and Restore Operations

In addition to using the Amazon web console to backup and restore an Aurora instance snapshot, you can also use the Amazon CLI to perform the same actions. The CLI is especially useful for migrating existing automated Oracle RMAN scripts to an Amazon environment. The following list highlights some CLI operations:

aws rds describe-db-cluster-snapshots

aws rds create-db-cluster-snapshot
    --db-cluster-snapshot-identifier Snapshot_name
    --db-cluster-identifier Cluster_Name

aws rds restore-db-cluster-from-snapshot
    --db-cluster-identifier NewCluster
    --snapshot-identifier SnapshotToRestore
    --engine aurora-mysql

aws rds create-db-instance
    --region us-east-1
    --db-subnet-group default
    --engine aurora-mysql
    --db-cluster-identifier NewCluster
    --db-instance-identifier newinstance-nodeA
    --db-instance-class db.r4.large

aws rds restore-db-cluster-to-point-in-time
    --db-cluster-identifier clusternamerestore
    --source-db-cluster-identifier clustername
    --restore-to-time 2017-09-19T23:45:00.000Z

aws rds create-db-instance
    --region us-east-1
    --db-subnet-group default
    --engine aurora-mysql
    --db-cluster-identifier clustername-restore
    --db-instance-identifier newinstance-nodeA
    --db-instance-class db.r4.large

Summary

CREATE RESTORE POINT
  before_update GUARANTEE
  FLASHBACK DATABASE;

aws rds create-db-cluster-snapshot
  --db-cluster-snapshotidentifier Snapshot_name
  --db-cluster-identifier Cluster_Name

ALTER SYSTEM SET
  db_flashback_retention_target=2880;

shutdown immediate;
startup mount;
flashback database to
  restore point before_update;

aws rds restore-db-cluster-from-snapshot
    --db-cluster-identifier NewCluster
    --snapshot-identifier SnapshotToRestore
    --engine aurora-mysql

aws rds create-db-instance
    --region useast-1
    --db-subnet-group default
    --engine aurora-mysql
    --db-cluster-identifier clustername-restore
    --db-instanceidentifier newinstance-nodeA
    --dbinstance-class db.r4.large

shutdown immediate;
startup mount;
FLASHBACK DATABASE TO TIME
  "TO_DATE ('01/01/2017','MM/DD/YY')";

aws rds modify-db-cluster
--db-clusteridentifier sample-cluster
--backtrack-window 86400

For more information, see mysqldump — A Database Backup Program in the MySQL documentation, rds in the CLI Command Reference and Restoring a DB instance to a specified time and Restoring from a DB snapshot in the Amazon RDS user guide.