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.
Are you copying the file from an Oracle Client to the RDS OMF? Can you be more specific?
ReplyDeleteArian,
ReplyDeleteYou 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
Tom
ReplyDeleteWe 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
Sriram,
ReplyDeletePlease provide your email address and I can provide step by step instructions and sample code.
Tom
Hi Tom,
Deleteit would be nice to have the code sample here, I am struggling to understand but can't find no way. Thank you
This comment has been removed by the author.
ReplyDeleteThank you Tom for this post! This is how I uploaded Datapump dump file to RDS DATA_PUMP_DIR directory.
ReplyDelete1. 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
Tom,
ReplyDeleteSteps given over here help me to upload my dumpfiles to RDS data_pump_dir.
Thanks.
Chirag Shah
Tom,
ReplyDeleteEverything clear Except the @RDS -- does oracle have inbuilt support for RDS type suffix like the one you have mentioned here?
Hi there,
ReplyDeleteI 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
Testing tools online training
ReplyDeleteVM Ware online training
Windows server online training
Pega online training
Php online training