Unable to import tables and data from .dmp file from S3 to AWS RDS

user_14590803 0
Viewed 309

I am trying to import data from .dmp file from AWS S3 to my RDS instance Oracle DB. I am able to download the file from S3 to my oracle directory DATA_PUMP_DIR. I am able to run the below script and able to view the dump file here.

SELECT * FROM TABLE (RDSADMIN.RDS_FILE_UTIL.LISTDIR(p_directory => 'DATA_PUMP_DIR'));

But when I try to import the data from this dump file I am getting error saying invalid arguments. What needs to be done?

The command I have used:

DECLARE
  hdnl NUMBER;
BEGIN
  hdnl := DBMS_DATAPUMP.OPEN( 
    operation => 'IMPORT', 
    job_mode  => 'FULL', 
    job_name  => null);
  DBMS_DATAPUMP.ADD_FILE( 
    handle    => hdnl, 
    filename  => 'testDump.dmp', 
    directory => 'DATA_PUMP_DIR', 
    filetype  => dbms_datapump.ku$_file_type_dump_file,
    reusefile => 1
   );
  DBMS_DATAPUMP.ADD_FILE( 
    handle    => hdnl, 
    filename  => 'testDump.log', 
    directory => 'DATA_PUMP_DIR', 
    filetype  => dbms_datapump.ku$_file_type_log_file);
  DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''Test_SCHEMA'')');
  DBMS_DATAPUMP.START_JOB(hdnl);
END;
datapump amazon-rds amazon-s3 oracle database
1 Answers

AWS Importing using Oracle Data Pump

When you use Oracle Data Pump to import data into an Oracle DB instance, we recommend the following best practices:

Perform imports in schema or table mode to import specific schemas and objects.

Limit the schemas you import to those required by your application.

Don't import in full mode.

Because Amazon RDS for Oracle does not allow access to SYS or SYSDBA administrative users, importing in full mode, or importing schemas for Oracle-maintained components, might damage the Oracle data dictionary and affect the stability of your database.

DECLARE
  hdnl NUMBER;
BEGIN
  hdnl := DBMS_DATAPUMP.OPEN( 
    operation => 'IMPORT', 
    job_mode  => 'SCHEMA', 
    job_name  => null);
  DBMS_DATAPUMP.ADD_FILE( 
    handle    => hdnl, 
    filename  => 'testDump.dmp', 
    directory => 'DATA_PUMP_DIR', 
    filetype  => dbms_datapump.ku$_file_type_dump_file);
  DBMS_DATAPUMP.ADD_FILE( 
    handle    => hdnl, 
    filename  => 'testDump.log', 
    directory => 'DATA_PUMP_DIR', 
    filetype  => dbms_datapump.ku$_file_type_log_file);
  DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''Test_SCHEMA'')');
  DBMS_DATAPUMP.START_JOB(hdnl);
END;
user_77347320
Related