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'))
