Migrating On-Prem Oracle DB to AWS RDS Using Datapump

Migrating Oracle DB on-prem to AWS RDS using datapump.

Assumptions:

1. You have AWS account access, with VPC, IAM and necessary roles.
2. RDS Oracle DB is created, sized properly and you have super user privilege and can connect to it using SQLPlus or other tools like TOAD/SQL Developer.
3. Oracle version and feature set is similar or compatible on both on-prem and RDS.

Setup:

Since we lose the ability to login as sys or system in RDS, we’ll use the super user admin account we created when setting up the RDS db. RDS provides options to create password verification functions or to use our custom function(s) used on-prem and make it available as pass through function.

1. Create any password functions for use with profiles:

Create password validation function CREATE OR REPLACE FUNCTION userpass_verify_function.... Normally, this will be in SYS, but in RDS its under the admin account we created. This password function should be made available under SYS for us to use it in profiles.

begin
    rdsadmin.rdsadmin_password_verify.create_passthrough_verify_fcn(
        p_verify_function_name => 'USER_VERIFY_FUNCTION',
        p_target_owner         => 'SUPERDBA',
        p_target_function_name => 'USERPASS_VERIFY_FUNCTION');
end;
/

2. Create profiles

CREATE PROFILE DB_DEFAULT LIMIT
  SESSIONS_PER_USER DEFAULT
  ...
  ...
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 180
  ...
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME 10
  PASSWORD_VERIFY_FUNCTION USER_VERIFY_FUNCTION;

3. Create tablespaces

RDS manages the datafiles, so create tablespace without datafile option. Use TOAD or other tools or dba tables to get the information.

-- Just samples here. Create all needed tablespaces.
CREATE TABLESPACE USERDATA
       DATAFILE SIZE 10 G
       AUTOEXTEND ON NEXT 8 K MAXSIZE UNLIMITED
       LOGGING
       ONLINE
       EXTENT MANAGEMENT LOCAL AUTOALLOCATE
       BLOCKSIZE 8 K
       SEGMENT SPACE MANAGEMENT AUTO
       FLASHBACK ON;


CREATE TABLESPACE USERIDX
       DATAFILE SIZE 10 G
       AUTOEXTEND ON NEXT 8 K MAXSIZE UNLIMITED
       LOGGING
       ONLINE
       EXTENT MANAGEMENT LOCAL AUTOALLOCATE
       BLOCKSIZE 8 K
       SEGMENT SPACE MANAGEMENT AUTO
       FLASHBACK ON;

4. Create USERS

Use DBA_USERS, or tools to generate CREATE USER statements. Some tools will generate GRANTS as well - either you can run them all (grants will error because objects don’t exist yet) or just select the user statements along with CONNECT grants and other essentials.

CREATE USER WEBADMIN
  IDENTIFIED BY VALUES 'abcdefghijklmnop'
  DEFAULT TABLESPACE USERDATA
  TEMPORARY TABLESPACE TEMP
  PROFILE DB_DEFAULT
  ACCOUNT UNLOCK;

  GRANT CONNECT TO WEBADMIN;