Physical migration of Oracle databases to Amazon RDS Custom using Data Guard
This blog post was reviewed and updated May 2022.
In the first post of this series, we showed you how to perform a physical migration of an Oracle database into Amazon RDS Custom using the RMAN duplicate tool. In this post, we walk through the steps to migrate Oracle databases into Amazon RDS Custom using Oracle Data Guard with minimum downtime.
We perform the following steps on the primary database instance (
- Check to make sure the primary database in running in archive log mode and
- Create the user
- Create the user
- Perform an RMAN online backup of the primary database, including data files, archive logs, and a control file for standby.
- Create a parameter file.
- Create a password file.
- Copy the backup sets, parameters file, and password file to the S3 bucket.
We perform the following steps on the RDS Custom standby instance:
- Copy all the files from the Amazon Simple Storage Service (Amazon S3) bucket
rds-rmanbackupto the standby instance.
- Pause automation on the standby DB instance.
- Drop the
ORCLempty database on Amazon RDS Custom.
- Remove the
- Edit the parameter file on the standby instance.
- Create two subdirectories.
- Create the server parameter file from the parameter file.
Finally, we perform the following steps to perform the migration:
- Configure Oracle listeners on both instances.
tnsnames.oraon both instances.
- Perform restore and recovery on the standby instance by starting it in
- Update the symbolic links on the standby instance.
- Start the Oracle Data Guard broker on both instances.
- Enable Oracle Data Guard on the primary instance, then add the standby instance to the configuration.
fal_clienton the standby instance.
- Update the
local_listenerparameter on both instances.
- Configure the standby instance redo logs on both instances.
- Recover the standby instance.
- Perform the manual switchover.
- Resume automation on the RDS Custom instance.
This post assumes you have the following setup:
- The primary instance is on Amazon Elastic Compute Cloud (Amazon EC2) with Oracle Linux, and the primary and standby database software versions are same.
- The solution is applicable when the primary database instance is on premises as long as you have appropriate network connectivity between the on-premises network and Amazon Virtual Private Cloud (Amazon VPC).
- The primary database DB name is
Complete the following prerequisite steps before starting your migration:
- Create an RDS Custom for Oracle DB instance named
ORCL(the same as the primary database name). For instructions, see Working with Amazon RDS Custom.
- Once you have the RDS Custom for Oracle DB instance up & running, get user creation script for RDSADMIN user and master user. Use the below instructions to extract the definition of RDSADMIN and master user and we will use this script at a later stage to provision these users on the primary database instance.
- Find the master username which needs to be recreated (Master username is under configuration tab of RDS console) If the master username is different from ADMIN, please replace the keyword ADMIN with correct username in the below line in the script.
- Copy below script as
recreate_custom_resources_input.sqlin a working directory and execute it. This will create a SQL script for recreating RDSADMIN and RDS Custom master user, tablespaces and profile which can be run at a later stage on the primary instance.
- Configure the AWS Command Line Interface (AWS CLI) on both instances. For instructions, see Getting started with the AWS CLI.
- Make sure you have network connectivity between the primary and RDS Custom database servers and DB port 1521 is open for communication between the two.
Primary DB instance steps
In this section, we walk through the steps to perform on the primary DB instance (
Confirm the primary database is running in archive log mode and FORCE_LOGGING is enabled
Run the following sql queries to validate:
Create the user RDS_DATAGUARD on the primary instance
We create a dedicated user for Oracle Data Guard replication. In this example, the name of the user is
RDS_DATAGUARD. We recommend using the same user name because Amazon RDS Custom uses
RDS_DATAGUARD for replication.
Create RDSADMIN and Master user on the primary instance
RDSADMIN is a monitoring and orchestrator database user in the RDS Custom DB instance. This user must exist in Amazon RDS Custom for Oracle after the Data Guard switchover, so you must create it on the primary database instance.
- Login to the database and alter the default profile as below:
- Execute script
recreate_custom_resources_output.sqlwhich was created as an output file on the RDS Custom instance in the pre-requisites section to create RDSADMIN and master user, tablespaces and profile.
DBSNMPuser profiles to
Perform an RMAN online backup of the primary database
In this example, we use
/rdsdbdata/backup as the backup file location.
- Create the directory
- Backup the data files and archive logs:
- Backup the control file for standby:
Create a parameter file on the primary instance
- Login to the database as sysdba and create pfile.
- Validate the pfile creation by listing the file:
Create a password file on the primary instance
Complete the following steps:
- Create a password file using orapwd:
- Validate by listing the file:
- Create a symbolic link:
- Validate the symbolic link:
Copy the backup sets, parameter file, and password file to the S3 bucket
Copy the files to the RDS Custom database server either via an S3 bucket or via SSH, based on your VPC setup. In the following steps, we copy the files to Amazon S3.
- Create an S3 bucket named
- Validate the bucket by listing the contents:
- Copy all RMAN backup files and standby control file to the S3 bucket:
- Copy the parameter file and password file to the S3 bucket:
RDS Custom standby instance steps
In the following section, we walk through the steps to perform on the standby instance.
Copy all the backup files from the S3 bucket rds-rmanbackup to the standby instance
- Create a directory
/rddbdata/backupon standby instance:
- Copy the backup files from Amazon S3 to the standby instance:
Pause automation on the standby instance
You must pause the automation mode on your standby instance before proceeding with the next steps to make sure the automation doesn’t interfere with the activities you perform. Pause the automation by running the following AWS CLI command:
Drop the ORCL empty database on Amazon RDS Custom
Make sure you’re connected to the empty database on the standby instance before running the
DROP DATABASE command:
Remove the transactionLogUploadTracker.json metadata file on the standby instance
When we create a standby on Amazon RDS Custom from a database on Amazon EC2 or on premises by dropping the RDS Custom instance, Amazon RDS Custom automation fails to upload the archive logs. This is because the automation recognizes that there is a new incarnation so it tries to fetch all previous incarnation based on current incarnation. In this case, because there’s no relation between the incarnation of the current instance and the RDS Custom instance incarnation, the automation query fails and returns no rows. To overcome this error, we have to move or remove the
transactionLogUploadTracker.json metadata file, which keeps track of the logs.
As the root user, check the status the RDS Custom agent to make sure it’s not running:
If the agent is running, stop it using
service rdscustomagent stop.
Copy the parameter file of the primary instance to the standby instance
Copy the parameter file from Amazon S3 with the following command:
Copy the password file of the primary instance to the standby instance
- Copy the password file from Amazon S3 with the following command:
- Create a symbolic link:
- Validate the file by listing it:
Edit the parameter file on the standby instance
Using a text editor, open
ORCL_B, and replace every occurrence of
ORCL_A in the paths with
ORCL_B. You should also change your memory parameter settings accordingly based on your environment. Refer to the below example:
Create two subdirectories
Create two subdirectories with the following commands:
Make sure the following paths exists (if not, create them):
Create the server parameter file from the parameter file on the standby instance
- Create the server parameter file with the following sql command:
- Create a link to spfile and validate it:
- Move the
Primary and standby instance steps
In this section, we perform the following steps on both the primary and standby instances to complete the migration
Configure Oracle listeners on both instances
To configure Oracle listeners on both instances, complete the following steps:
- On the primary instance, stop the listener:
- Append the following to
listener.oraand substitute primary_instance_ipv4 with your standby instance IP address:
- Start the Data Guard local listener:
- Check the listener status:
- On the standby instance, stop the original listener:
- Use the following AWS CLI command to get the standby private IP address by substituting <standby_instance_resource_id>:
You can find the value for the resource ID on the Amazon RDS Custom console in the Summary section under Configuration, or by using the AWS CLI.
- Append the following to
listener.oraand substitute standby_instance_ipv4 with your primary instance IP address:
- Start the Data Guard local listener:
- Check the status of the Data Guard local listener:
Configure tnsnames.ora on both instances
Put the following entry in
ORCL_B with the TNS address names you choose. Replace <primary_instance_ipv4> with your primary instance IP, and replace <standby_instance_ipv4> with the IP in the previous step. The port for the standby instance is 1140.
Verify the networking connection and TNS entries by performing the following commands on both instances. A successful configuration will return OK.
Perform restore and recovery on the standby database
To restore and recover the standby database, complete the following steps:
- On the standby instance, start the standby database with
NOMOUNTmode and restore the standby control file:
report schemato verify
DB_FILE_NAME_CONVERTand that the data file path and name look accurate:
- Restore the database:
If the standby instance has the different directory structure for backup pieces, you need to catalog the files manually and use
- Get the last available sequence number from the archive log backup set:
- Recover the database:
Update the symbolic links on the standby instance
Update the symbolic links as below:
Start the Oracle Data Guard broker on both instances
Start the Oracle Data Guard broker with the following command:
Enable Oracle Data Guard configuration
To enable Oracle Data Guard, complete the following steps:
- On the primary instance, connect to the Data Guard broker:
- Inside the software interface, create the configuration for the primary instance. For this post, the database unique name for the primary instance is
- Add the standby instance to this configuration. For this post, the database unique name for the standby instance is
- Check the configuration was created successfully (
ORCL_Awith the primary database role and
ORCL_Bwith the physical standby database role):
- Set the static connect identifiers for both databases. Replace <primary_instance_ipv4> and <standby_instance_ipv4> with the IP addresses:
- Enable the Oracle Data Guard configuration (the following command could take a few minutes to complete):
Configure fal_server and fal_client on the standby instance
Enter the following commands to configure
Update the local_listener parameter on both instances
On the primary instance, enter the following sql command:
On the standby instance, enter the following sql command:
Configure the standby redo log files on both instances
On the standby instance, add n+1 standby redo log files. In this post, the primary instance and standby instance have four redo log files each:
Recover the standby instance
Enter the following sql command:
Perform the manual switchover
To perform the manual switchover, complete the following steps:
- On the primary instance, connect to the Data Guard broker and validate if both databases are ready for switchover:
- Switch over from the primary database to the standby database:
- Verify the switchover is successful and check to make sure database roles are changed:
Resume automation on the RDS Custom instance
After all the activities are completed, we can resume the automation by running the following AWS CLI command:
You have now set up a Data Guard standby instance on Amazon RDS Custom with the primary instance on Amazon EC2.
In this series of posts, we learned how to perform a physical migration of an Oracle database to Amazon RDS Custom using Oracle native tools like Oracle Data Guard and RMAN duplicate to reduce migration downtime and also get the benefits of a managed service. Share your thoughts in the comments.
About the Authors
Yamuna Palasamudram is a Senior Database Specialist Solutions Architect with Amazon Web Services. She works with AWS RDS team, focusing on commercial database engines like Oracle. She enjoys working with customers to help design, deploy, and optimize relational database workloads on AWS.
Nitin Saxena is a Senior Database Engineer in RDS DBS Managed Commercial Engines with Amazon Web Services. He focuses on services like RDS Oracle and RDS Custom for Oracle . He enjoys designing and developing new features on RDS Oracle and RDS Custom to solve customer problems.