Step-by-Step Oracle DB Migration to AWS Cloud

Step-by-Step Oracle DB Migration to AWS Cloud with Command-Line and Console Instructions

1. Planning and Assessment

• Analyze: Understand your current Oracle environment. Use queries like:

  SELECT  FROM v$version; SELECT  FROM dba_data_files; SELECT * FROM dba_segments;

• Choose AWS Service:

  • For RDS: Opt for a fully-managed option.

  • For EC2: Choose EC2 if you need full control over the database.

• Estimate Downtime: Calculate downtime by assessing transaction loads using:

  SELECT SUM(value) FROM v$sysstat WHERE name = 'user commits';

2. Choose a Migration Strategy

• Homogeneous Migration: Oracle to Oracle using RDS or EC2.

• Heterogeneous Migration: Oracle to AWS Aurora or PostgreSQL.

  • Use AWS Schema Conversion Tool (SCT) for schema conversion:

    1. Download and install AWS SCT here.

    2. Open the tool and connect to your source Oracle DB and target AWS DB.

3. Set Up AWS Environment

Console Steps:

1. Login to AWS Console.

2. Create VPC: Navigate to VPC > Create VPC. Set up subnets and routing tables.

3. Security Groups: In the console, go to EC2 > Security Groups > Create Security Group.

   • Allow ports 1521 for Oracle and 22 for SSH (for EC2 setup).

Command Line (AWS CLI):

• Configure AWS CLI:

  bash

  aws configure

• Create VPC:

  bash

  aws ec2 create-vpc --cidr-block 10.0.0.0/16

• Create Security Group:

  bash

  aws ec2 create-security-group --group-name MyDBSecurityGroup --description "Security group for Oracle DB"

4. Prepare the Oracle Source Database

Command Line (Oracle SQLPlus or SQL Developer):

1. Check Oracle Version:

   sql

   Copy code

   SELECT * FROM v$version;

2. Export Schemas and Objects:

   • Use Oracle Data Pump:

     bash

     expdp user/password@sid DIRECTORY=exp_dir DUMPFILE=export.dmp LOGFILE=expdp.log FULL=Y

3. Stop Oracle Services (if needed for downtime):

   bash

   sudo systemctl stop oracle-xe

5. Set Up Target Database on AWS

RDS for Oracle - Console Steps:

1. Launch RDS for Oracle:

   • Go to RDS > Create Database.

   • Select Oracle as the database engine.

   • Configure instance size, storage, and backup settings.

Command Line (AWS CLI):

• Create RDS Instance:

  bash

  aws rds create-db-instance --db-instance-identifier mydbinstance --db-instance-class db.m5.large --engine oracle-se2 --allocated-storage 100 --master-username admin --master-user-password password

EC2 with Oracle - Console Steps:

1. Launch EC2 Instance:

   • Go to EC2 > Launch Instance.

   • Choose an appropriate instance type (e.g., t3.medium or m5.large).

   • SSH into the EC2 instance and install Oracle using the Oracle binaries.

Command Line (EC2 CLI):

• Launch EC2 Instance:

  bash

  aws ec2 run-instances --image-id ami-0123456789abcdef0 --count 1 --instance-type t3.medium --key-name MyKeyPair --security-group-ids sg-0123456789abcdef0

6. Migrate Data

Using AWS DMS - Console Steps:

1. Set up DMS: Go to DMS > Create Replication Instance.

   • Define source (on-prem Oracle) and target (AWS RDS or EC2 Oracle) endpoints.

   • Create a migration task for full load or CDC (Change Data Capture).

Command Line (AWS CLI):

• Create Replication Instance:

  bash

  aws dms create-replication-instance --replication-instance-identifier my-dms-instance --replication-instance-class dms.t2.medium --allocated-storage 100

• Create DMS Task:

  bash

  aws dms create-replication-task --replication-task-identifier my-dms-task --source-endpoint-arn arn:source --target-endpoint-arn arn:target --migration-type full-load --table-mappings file://mapping-file.json

Using Oracle Data Pump - Command Line:

• Import on AWS:

  bash

  impdp admin/password@rds_endpoint DIRECTORY=imp_dir DUMPFILE=export.dmp LOGFILE=impdp.log FULL=Y

7. Application Testing and Optimization

Console Steps:

• Use Amazon CloudWatch for monitoring performance metrics.

• Use Performance Insights in RDS to check for slow queries and optimize indexes.

SQL Command-Line:

• Test sample queries on the new database:

  sql

  SELECT * FROM employees WHERE department_id = 10;

• Fine-tune with SQL optimizer hints, if necessary.

8. Cutover and Go Live

Console Steps:

1. Update Application Connection Strings: Change the connection string in your application to point to the new AWS Oracle DB.

   • Example:

     ruby

     jdbc:oracle:thin:@rds-instance.endpoint.amazonaws.com:1521:ORCL

2. Monitor Performance: Use CloudWatch and RDS Performance Insights to monitor after going live.

9. Post-Migration Activities

Console Steps:

• Enable Backups: Go to RDS > Modify Instance > Enable automated backups.

• Create CloudWatch Alarms: For CPU, memory, and storage thresholds.

Command Line:

• Backup:

  bash

  aws rds modify-db-instance --db-instance-identifier mydbinstance --backup-retention-period 7

10. Decommission Old Infrastructure

• Console Steps:

  • Decommission old Oracle on-prem instances or EC2 if no longer needed.

  • Terminate EC2:

    bash

    aws ec2 terminate-instances --instance-ids i-1234567890abcdef0

  
  

This guide provides both the conceptual steps and practical command-line and AWS console instructions. By following these detailed steps, you can achieve a smooth and efficient migration of your Oracle database to AWS Cloud.