Oracle Flashback Table and Amazon Aurora PostgreSQL snapshots

With AWS DMS, you can migrate databases between different database platforms or versions by capturing consistent data snapshots from the source database and applying them to the target database. Oracle Flashback Table and Amazon Aurora PostgreSQL snapshots provide point-in-time backups of the source database, enabling migration with minimal downtime.

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
	N/A	N/A	Storage level backup managed by Amazon RDS.

Oracle usage

Oracle Flashback Table is a data protection feature used to undo changes to a table and rewind it to a previous state (not from backup). While Flashback table operations are running, the affected tables are locked, but the rest of the database remains available.

If the structure of a table has been changed since the point of restore, the FLASHBACK will fail. Row movement must be enabled.

The data to restore must be found in the undo (dba must manage the size and retention). A table can be restored to an System Change Number (SCN), Restore Point, or Timestamp.

Examples

Flashback a table using SCN (query V$DATABASE to obtain the SCN).

SELECT CURRENT_SCN FROM V$DATABASE;
FLASHBACK TABLE employees TO SCN 3254648;

Flashback a table using a Restore Point (query V$RESTORE_POINT to obtain restore points).

SELECT NAME, SCN, TIME FROM V$RESTORE_POINT;
FLASHBACK TABLE employees TO RESTORE POINT employees_year_update;

Flashback a table using a Timestamp (query V$PARAMETER to obtain the undo_retention value).

SELECT NAME, VALUE/60 MINUTES_RETAINED
FROM V$PARAMETER
WHERE NAME = 'undo_retention';
FLASHBACK TABLE employees TO
TIMESTAMP TO_TIMESTAMP('2017-09-21 09:30:00', 'YYYY-MM-DD HH:MI:SS');

For more information, see Backup and Recovery User Guide in the Oracle documentation.

PostgreSQL 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 AWS 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:

• Automated Backups — Always enabled on Amazon Aurora. They do not impact database performance.

• Manual Backups — You can create a snapshot at any time. There is no performance impact when taking snapshots of an Aurora database. Restoring data from snapshots requires creation of a new instance. Up to 100 manual snapshots are supported for each database.

Examples

For examples, see PostgreSQL Amazon Aurora Snapshots.

Summary

Description	Oracle	Amazon Aurora
Create a restore point	CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;	aws rds create-db-cluster-snapshot --db-cluster-snapshotidentifier Snapshot_name --db-cluster-identifier Cluster_Name
Configure flashback retention period	ALTER SYSTEM SET db_flashback_retention_target=2880;	Configure the Backup retention window setting using the AWS management console or AWS CLI.
Flashback table to a previous restore point	shutdown immediate; startup mount; flashback database to restore point before_update;	Create new cluster from a snapshot. aws rds restore-db-cluster-from-snapshot --db-cluster-identifier NewCluster --snapshot-identifier SnapshotToRestore --engine aurora-postgresql Add new instance to the cluster. aws rds create-db-instance --region us-east-1 --db-subnetgroup default --engine aurora-postgresql --db-cluster-identifier clustername-restore --db-instance-identifier newinstance-nodeA --db-instance-class db.r4.large Use pg_dump and pg_restore to copy the table from the restored instance to the original instance.
Flashback table to a previous point in time	shutdown immediate; startup mount; FLASHBACK DATABASE TO TIME "TO_DATE ('01/01/2017','MM/DD/YY')";	Create a new cluster from a snapshot and provide a specific point in time. aws rds restore-db-cluster-to-point-in-time --db-cluster-identifier clustername-restore --source-db-cluster-identifier clustername --restore-to-time 2017-09-19T23:45:00.000Z Add a new instance to the cluster: aws rds create-db-instance --region us-east-1 --db-subnetgroup default --engine aurora-postgresql --db-cluster-identifier clustername-restore --db-instance-identifier newinstance-nodeA --db-instance-class db.r4.large Use pg_dump and pg_restore to copy the table from the restored instance to the original instance.

For more information, see 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.