MartyFL has asked for the wisdom of the Perl Monks concerning the following question:
Has anyone written a Perl script to transfer files FROM an RDS instance's DATA_PUMP_DIR?
I have successfully implemented the Perl script (below) to transfer files TO an RDS instance's DATA_PUMP_DIR from the "Strategies for Migrating Oracle Databases to AWS" whitepaper found at http://d0.awsstatic.com/whitepapers/strategies-for-migrating-oracle-database-to-aws.pdf. This solved the issue of using impdp with files (happy dance!). Now I need to solve the issue of using expdp with files and I'm no Perl programmer so I'm struggling.
Any help in retrieving files from an RDS instance's DATA_PUMP_DIR directory would be greatly appreciated!
Thank you,
Marty
Perl script used to transfer files TO the RDS instance:
use DBI; use warnings; use strict; # RDS instance info my $RDS_PORT=1521; my $RDS_HOST="<rds_endpoint>"; my $RDS_LOGIN="<schema_id>/<schema_pwd>"; my $RDS_SID="<rds_db_name>"; # Oracle destination directory and file name #The $ARGV[0] is a parameter you pass into the script my $dirname = "DATA_PUMP_DIR"; my $fname = $ARGV[0]; my $data = "dummy"; my $chunk = 8192; my $sql_open = "BEGIN perl_global.fh := utl_file.fopen(:dirname, :fnam +e, 'wb', :chunk); END;"; my $sql_write = "BEGIN utl_file.put_raw(perl_global.fh, :data, true); +END;"; my $sql_close = "BEGIN utl_file.fclose(perl_global.fh); END;"; my $sql_global = "create or replace package perl_global as fh utl_file +.file_type; end;"; my $conn = DBI->connect('dbi:Oracle:host='.$RDS_HOST.';sid='.$RDS_SID. +';port='.$RDS_PORT,$RDS_LOGIN, '') || die ( $DBI::errstr . "\n"); # create a package just to have a global variable my $updated=$conn->do($sql_global); # open the file for writing my $stmt = $conn->prepare ($sql_open); $stmt->bind_param_inout(":dirname", \$dirname, 12); $stmt->bind_param_inout(":fname", \$fname, 12); $stmt->bind_param_inout(":chunk", \$chunk, 4); $stmt->execute() || die ( $DBI::errstr . "\n"); open (INF, $fname) || die "\nCan't open $fname for reading: $!\n"; binmode(INF); $stmt = $conn->prepare ($sql_write); my %attrib = ('ora_type','24'); my $val=1; while ($val> 0) { $val = read (INF, $data, $chunk); $stmt->bind_param(":data", $data , \%attrib); $stmt->execute() || die ( $DBI::errstr . "\n") ; }; die "Problem copying: $!\n" if $!; close INF || die "Can't close $fname: $!\n"; $stmt = $conn->prepare ($sql_close); $stmt->execute() || die ( $DBI::errstr . "\n") ;
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re: Perl script to transfer files FROM an AWS RDS instance's DATA_PUMP_DIR
by Corion (Patriarch) on Apr 29, 2015 at 07:05 UTC |