Oracle Recovery Manager and Amazon RDS snapshots - Oracle to Aurora MySQL Migration Playbook
Oracle usageMySQL usageSummary

Oracle Recovery Manager and Amazon RDS snapshots

With AWS DMS, you can migrate data from Oracle databases by using Oracle Recovery Manager (RMAN) backup sets or Amazon RDS snapshots. Oracle Recovery Manager is a utility for backing up, restoring, and recovering Oracle databases. Amazon RDS snapshots capture the entire database instance, including transaction logs, at a specific point in time.

Feature compatibility AWS SCT / AWS DMS automation level AWS SCT action code index Key differences

Three star feature compatibility

N/A

N/A

Storage-level backup managed by Amazon RDS.

Oracle usage

Oracle Recovery Manager (RMAN) is a primary backup and recovery tool in Oracle. It provides its own scripting syntax and can be used to take full or incremental backups of an Oracle database. The following list identifies the types of backups.

  • Full RMAN backup — Creates a full backup of an entire database or individual Oracle data files. For example, a level 0 full backup.

  • Differential incremental RMAN backup — Performs a backup of all database blocks that have changed from the previous level 0 or 1 backup.

  • Cumulative incremental RMAN backup — Perform a backup all of blocks that have changed from the previous level 0 backup.

RMAN supports online backups of an Oracle database if it has been configured to run in Archived Log Mode.

RMAN backs up the following files:

  • Database data files.

  • Database control file.

  • Database parameter file.

  • Database Archived Redo Logs.

Examples

Use the RMAN CLI to connect to an Oracle database.

export ORACLE_SID=ORCL
rman target=/

Perform a full backup of the database and the database archived redo logs.

BACKUP DATABASE PLUS ARCHIVELOG;

Perform an incremental level 0 or level 1 backup of the database.

BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP INCREMENTAL LEVEL 1 DATABASE;

Restore a database.

RUN {
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}

Restore a specific pluggable database (Oracle 12c).

RUN {
ALTER PLUGGABLE DATABASE pdbA, pdbB CLOSE;
RESTORE PLUGGABLE DATABASE pdbA, pdbB;
RECOVER PLUGGABLE DATABASE pdbA, pdbB;
ALTER PLUGGABLE DATABASE pdbA, pdbB OPEN;
}

Restore a database to a specific point in time.

RUN {
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SET UNTIL TIME "TO_DATE('20-SEP-2017 21:30:00','DD-MON-YYYY HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

List all current database backups created with RMAN.

LIST BACKUP OF DATABASE;

For more information, see Backup and Recovery User Guide 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 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.

Note

In Amazon Relational Database Service (Amazon RDS) for MySQL version 8.0.21, you can turn on or off the redo logging option using the ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG syntax. This functionality is intended for loading data into a new MySQL instance. Turning off the redo logging option helps speed up data loading by avoiding redo log writes. The new INNODB_REDO_LOG_ENABLE privilege permits turning on and turning off the redo logging option. The new Innodb_redo_log_enabled status variable permits monitoring redo logging status. For more information, see Disabling Redo Logging in the MySQL documentation.

Examples

For examples, see MySQL Snapshots.

Summary

Description Oracle Amazon Aurora

Scheduled backups

Create the DBMS_SCHEDULER job that will run your RMAN script on a scheduled basis.

Automatic

Manual full database backups

 
BACKUP DATABASE PLUS ARCHIVELOG;

Use Amazon RDS dashboard or the AWS CLI command to take a snapshot on the cluster.

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

Restore database

 
RUN
{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}

Create new cluster from a cluster snapshot.

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

Add a new instance to the new/restored cluster.

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

Incremental differential

 
BACKUP INCREMENTAL LEVEL 0
DATABASE;
BACKUP INCREMENTAL LEVEL 1
DATABASE;

N/A

Incremental cumulative

 
BACKUP INCREMENTAL LEVEL 0
CUMULATIVE DATABASE;
BACKUP INCREMENTAL LEVEL 1
CUMULATIVE DATABASE;

N/A

Restore a database to a specific point in time

 
RUN {
  SHUTDOWN IMMEDIATE;
  STARTUP MOUNT;
  SET UNTIL TIME "TO_DATE(
    '19-SEP-2017 23:45:00',
    'DD-MON-YYYY HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE
  OPEN RESETLOGS;
}

Create a new cluster from a cluster snapshot by given custom time to restore.

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 new or restored cluster.

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

Backup database archive logs

 
BACKUP ARCHIVELOG ALL;

N/A

Delete old database archive logs

 
CROSSCHECK BACKUP;
DELETE EXPIRED BACKUP;

N/A

Restore a single pluggable database (12c)

 
RUN {
  ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE;
  RESTORE PLUGGABLE DATABASE pdb1, pdb2;
  RECOVER PLUGGABLE DATABASE pdb1, pdb2;
  ALTER PLUGGABLE DATABASE pdb1, pdb2
  OPEN;
}

Create new cluster from a cluster snapshot.

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

Add a new instance to the new or restored cluster.

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

Use mysqldump and mysql to copy the database to the original instance.

mysqldump --column-statistics=0
    DATABASE_TO_RESTORE
    -h RESTORED_INSTANCE_ENDPOINT
    -P 3306 -u USER_NAME
    -p > /local_path/backup-file.sql
mysql DB_NAME
    -h MYSQL_INSTANCE_ENDPOINT
    -P 3306 -u USER_NAME
    -p < /local_path/backup-file.sql
Note

In Amazon RDS for MySQL version 8.0, make sure that the column_statistics flag set to 0 if you use binaries when running the mysqldump.

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