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 (ORCL):
Check to make sure the primary database in running in archive log mode and FORCE_LOGGING is enabled.
Create the user RDS_DATAGUARD.
Create the user RDSADMIN.
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-rmanbackup to the standby instance.
Pause automation on the standby DB instance.
Drop the ORCL empty database on Amazon RDS Custom.
Remove the transactionLogUploadTracker.json metadata file.
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.
Configure tnsnames.ora on both instances.
Perform restore and recovery on the standby instance by starting it in NOMOUNT mode.
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.
Configure fal_server and fal_client on the standby instance.
Update the local_listener parameter 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 ORCL.
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.
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 (ORCL).
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 the user RDSADMIN 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:
Create the profile RDSADMIN:
Set SYS, SYSTEM, and DBSNMP user profiles to RDSADMIN:
Create the RDSADMIN tablespace:
Create the user RDSADMIN:
Grant privileges to RDSADMIN:
Perform an RMAN online backup of the primary database
In this example, we use /rdsdbdata/backup as the backup file location.
Create the directory /rddbdata/backup:
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 rds-rmanbackup:
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/backup on 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.
Rename the transactionLogUploadTracker.json file:
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 $ORACLE_HOME/dbs/initORCL.ora, change db_unique_name to 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 init file to /tmp:
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.ora and substitute primary_instance_ipv4 with your primary 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.ora and 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 tnsnames.ora. Replace ORCL_A and 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 NOMOUNT mode and restore the standby control file:
Use report schema to verify DB_FILE_NAME_CONVERT and 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 set newname.
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 ORCL_A:
Add the standby instance to this configuration. For this post, the database unique name for the standby instance is ORCL_B:
Check the configuration was created successfully (ORCL_A with the primary database role and ORCL_B with 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 fal_server and fal_client:
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:
Create master user and grant privileges
Since the starter database was dropped and the standby database was restored from the primary the master user has to be re-created. In this example the master username is called admin.
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 focus 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.
Read MoreAWS Database Blog