Thursday, April 25, 2013

Importing on premise Oracle Database into AWS RDS


There is an article here but does not get into specifics on the performance of each option. http://aws.amazon.com/articles/4173109646282306

Oracle data pump is one of the fastest ways to move data between Oracle databases.  However, the approach shown in the above article uses database links which can be very slow.  A more performant method would be to use data pump file mode. Unfortunately, AWS Oracle RDS does not off access to the OS.  There is a workaround to this by using a little known feature of the Oracle database called Oracle Managed File System

Oracle RDS has access to this Oracle Managed File (OMF) system. Once the file exported from the on premise database and moved to the Oracle RDS OMF you can use the  UTL_FILE capability in the database and import a file into the RDS instance, and then do impdb (data pump import) from there.

Things to keep in mind:
1.       Since the file sits on the Oracle managed file system, it consumes the space allocated for the database. So you will need to make sure you allocate enough space for the instance
2.       Once you do the impdb operation, you can delete the file but till then it will consume space on the instance
3.       File sizes will need to be less than 400-500 GB since the largest instance currently is 1 TB, and you will need space for both the file as well as the database
4.       As you do the import, you will consume IOPS for reading the dump file and then writing to the database.


11 comments:

  1. Are you copying the file from an Oracle Client to the RDS OMF? Can you be more specific?

    ReplyDelete
  2. Arian,

    You would FTP, SCP, or use some other method to transfer the file from your on premise system to AWS. You would then use UTL_FILE to copy the file into the RDS instance OMF directory.

    Tom

    ReplyDelete
  3. Tom

    We are looking to load files into an RDS instance for two purposes:

    1. database export from on-premises server to RDS

    2. For use with external tables. We receive files from clients and need to upload them to the DB using external tables.

    However, I am told that UTL_FILE cannot handle files NOT on the RDS server. How did you manage to use UTL_FILE to load files from another server to RDS server? Could you show some examples?

    Thanks
    Sriram

    ReplyDelete
  4. Sriram,

    Please provide your email address and I can provide step by step instructions and sample code.

    Tom

    ReplyDelete
    Replies
    1. Hi Tom,

      it would be nice to have the code sample here, I am struggling to understand but can't find no way. Thank you

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Thank you Tom for this post! This is how I uploaded Datapump dump file to RDS DATA_PUMP_DIR directory.

    1. I assume you have a working Dblink called 'rds' on the other DB (The non RDS) pointing to RDS, and the users you use have the read / write permission on DATA_PUMP_DIR (both on RDS and on the non RDS DB's)

    2. Create and execute the following procedure in order to upload the file to RDS: (Change the file / directory names to yours)
    create or replace procedure copy_to_rds
    is
    p_source_dir varchar2(100) := 'DATA_PUMP_DIR';
    p_source_file varchar2(100) := 'test.dmp';
    p_target_dir varchar2(100) := 'DATA_PUMP_DIR';
    p_target_file varchar2(100) := 'test.dmp';
    l_target utl_file.file_type@rds;
    l_source utl_file.file_type;
    Buf RAW(32767);
    Amnt CONSTANT BINARY_INTEGER := 32767;

    begin
    l_source := utl_file.fopen( p_source_dir, p_source_file, 'rb', 32767 );
    l_target := utl_file.fopen@rds( p_target_dir, p_target_file, 'wb', 32767 );

    LOOP
    BEGIN
    UTL_FILE.GET_RAW(l_source, Buf, Amnt);
    UTL_FILE.PUT_RAW@rds (l_target, Buf);

    EXCEPTION WHEN No_Data_Found THEN
    EXIT;
    utl_file.fclose@rds( l_target );
    utl_file.fclose( l_source );
    END;
    END LOOP;

    end copy_to_rds;
    /

    3. Now once the file uploaded to RDS, run impdp regulary:
    impdp your_rds_user@rds dumpfile=test.dmp

    ReplyDelete
  7. Tom,

    Steps given over here help me to upload my dumpfiles to RDS data_pump_dir.

    Thanks.
    Chirag Shah

    ReplyDelete
  8. Tom,

    Everything clear Except the @RDS -- does oracle have inbuilt support for RDS type suffix like the one you have mentioned here?

    ReplyDelete
  9. Hi there,

    I was able to execute DBMS_FILE_TRANSFER.PUT_FILE procedure successfully.
    But when I try to do a impdp import, I am receiving the below error message.
    ==================
    ORA-39001: invalid argument value
    ORA-39000: bad dump file specification
    ORA-31640: unable to open dump file "/rdsdbdata/datapump/QC_RO_MOD_02.dmp" for r
    ead
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3
    ====================

    How can I verify that the dump file has been transferred successfully and has necessary permissions in RDS oracle directory?

    Thanks,
    Abhijit

    ReplyDelete