Export and Import from On-premise database to AWS cloud database

Schema Export and Import from  On-premise database to AWS cloud database.

Step 1:Connect the on-premise database and the export the schema

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => ‘EXPORT’, job_mode => ‘SCHEMA’, job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘sample_ff.dmp’, directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘sample_expdp_ff.log’, directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,’SCHEMA_EXPR’,’IN (”FLOWS_FILES”)’);
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/

Step 2:  Create DB link in the on-premise database to connect to the aws cloud database

create database link Link_name connect to user identified by “password” using ‘cloud_tns’;

Step 3:  Transfer the dump file from the on-premise database to aws cloud database

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object       => ‘DATA_PUMP_DIR’,
source_file_name              => ‘sample_ff.dmp’,
destination_directory_object  => ‘DATA_PUMP_DIR’,
destination_file_name         => ‘sample_ff.dmp’,
destination_database          => ‘FMS_RDS’
);
END;
/

Step 4:Import the dump in the aws cloud database.
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => ‘IMPORT’, job_mode => ‘SCHEMA’, job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘sample_ff.dmp’, directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘impdp_ff.log’, directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,’SCHEMA_EXPR’,’IN (”FLOWS_FILES”)’);
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/  
  • January 22, 2019 | 21 views
  • Comments