How to Migrate to AWS RDS - Oracle Database Migration To RDS - Step By Step Tutorial

Video Description

In the video I am demonstrating how we can migrate an on-premises Oracle database to AWS RDS for Oracle database using DataPump export/import. Hope this will help DBAs... Source Side Commands: CREATE USER APPUSER IDENTIFIED BY "appuser12345" DEFAULT TABLESPACE APP_TBS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; -- 3 Roles for APPUSER GRANT CONNECT,RESOURCE TO APPUSER; ALTER USER APPUSER DEFAULT ROLE ALL; -- 1 System Privilege for APPUSER GRANT UNLIMITED TABLESPACE TO APPUSER; --Objects create table appuser.my_objects as select * from dba_objects; create table appuser.my_tables as select * from dba_tables; create table appuser.my_indexes as select * from dba_indexes; create index appuser.my_objects_ix1 on appuser.my_objects(object_name); create index appuser.my_tables_ix1 on appuser.my_tables(table_name); create index appuser.my_indexes_ix1 on appuser.my_indexes(index_name); create sequence appuser.my_squence start with 1 increment by 1; select object_name,object_type from dba_objects where owner='APPUSER' select * from DBA_DIRECTORIES order by directory_name expdp userid=\'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=appuser_onprem.dmp LOGFILE=expdp_appuser_onprem.log SCHEMAS=APPUSER FLASHBACK_TIME=SYSTIMESTAMP RDS Side Command: CREATE USER APPUSER IDENTIFIED BY "appuser12345" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; -- 3 Roles for APPUSER GRANT CONNECT,RESOURCE TO APPUSER; ALTER USER APPUSER DEFAULT ROLE ALL; -- 1 System Privilege for APPUSER GRANT UNLIMITED TABLESPACE TO APPUSER; select * from dba_users where username='APPUSER' select object_name,object_type from dba_objects where owner='APPUSER' select * from DBA_DIRECTORIES order by directory_name Downloading from S3: SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3( p_bucket_name ="greater than symbol" 'migrationproject', p_s3_prefix ="greater than symbol" 'dba/appuser_onprem.dmp', p_directory_name ="greater than symbol" 'DATA_PUMP_DIR') AS TASK_ID FROM DUAL; 1670703010887-1356 -The Task ID Reading the Task Logfile: SELECT text FROM table(RDSADMIN.rds_file_util.read_text_file('BDUMP','dbtask-1670703010887-1356.log')) Listing the Directory: select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime desc Import Job: DECLARE v_hdnl NUMBER; BEGIN v_hdnl := DBMS_DATAPUMP.OPEN( operation ="greater than symbol" 'IMPORT', job_mode ="greater than symbol" 'SCHEMA', job_name ="greater than symbol" null); DBMS_DATAPUMP.ADD_FILE( handle ="greater than symbol" v_hdnl, filename ="greater than symbol" 'appuser_onprem.dmp', directory ="greater than symbol" 'DATA_PUMP_DIR', filetype ="greater than symbol" dbms_datapump.ku$_file_type_dump_file); DBMS_DATAPUMP.ADD_FILE( handle ="greater than symbol" v_hdnl, filename ="greater than symbol" 'impdp_appuser_onprem.log', directory ="greater than symbol" 'DATA_PUMP_DIR', filetype ="greater than symbol" dbms_datapump.ku$_file_type_log_file); DBMS_DATAPUMP.METADATA_FILTER(v_hdnl,'SCHEMA_EXPR','IN (''APPUSER'')'); -- DBMS_DATAPUMP.METADATA_REMAP(v_hdnl,'REMAP_SCHEMA','APPUSER','APPUSER2'); DBMS_DATAPUMP.METADATA_REMAP(v_hdnl,'REMAP_TABLESPACE','APP_TBS','USERS'); -- DBMS_DATAPUMP.SET_PARALLEL(v_hdnl,2); DBMS_DATAPUMP.START_JOB(v_hdnl); END; / Reading the import log file: SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('DATA_PUMP_DIR','impdp_appuser_onprem.log'))