如何将oracle dump从ec2实例导入到AWS中的RDS [英] How to import oracle dump from ec2 instance to RDS in AWS

问题描述

我是AWS中RDS的新手，我有一个位于ec2 instance上的数据库转储文件.我有几个问题:

I am new to RDS in AWS.I have a database dump file that is residing on an ec2 instance. I have few questions :

1. 如何将转储文件移动到RDS Instance.
2. 执行此操作的先决条件是什么.我的意思是要执行此操作需要安装什么客户端.

感谢任何帮助或线索.

谢谢

推荐答案

我同意官方文档非常混乱，而且在Oracle导入转储文件时还不清楚.

I agree that the official document is quite confusing and not clear when it comes to Oracle import of dump file.

亚马逊还提供了另一个文档分步说明，网址为

There is another document step by step instructions from amazon at http://d0.awsstatic.com/whitepapers/strategies-for-migrating-oracle-database-to-aws.pdf and also a presentation at http://fr.slideshare.net/AmazonWebServices/dat308-28616289

基本上，过程是:

1. 您需要将文件传输到Amazon RDS数据库实例-Amazon提供了已经定义的DATA_PUMP_DIR，因此您需要一个脚本将转储文件从EC2实例移至Amazon RDS DATA_PUMP_DIR

1. you need to transfer files to Amazon RDS DB instance - Amazon offers an already defined DATA_PUMP_DIR so you would need to have a script to move your dump files from your EC2 instance to the Amazon RDS DATA_PUMP_DIR

以下脚本将为您完成工作

The following script would do the job for you

use DBI;
use warnings;
use strict;

# RDS instance info
my $RDS_PORT=1521;
my $RDS_HOST="<my rds instance>";
my $RDS_LOGIN="<myuser>/*******";
my $RDS_SID="<ORCL_LIKE>"; 

#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, :fname, '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");

my $updated=$conn->do($sql_global);
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") ;

确保所有文件都已导入

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by FILENAME;

1. 将数据从转储文件导入到RDS实例

您可以运行impdp，也可以使用PL/SQL脚本提交作业

you can run impdp or you can submit a job using PL/SQL script

declare
  h1    NUMBER;
begin

  h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'FULL', job_name => 'IMPORT_DUMP', version => 'COMPATIBLE');
  dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- optional 
  dbms_datapump.set_parallel(handle => h1, degree => 8);

  dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
  dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);

  dbms_datapump.add_file(handle => h1, filename => 'HOST_01.dmp', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
  <repeat the add_file for all your files>

  dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
  dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
  dbms_datapump.set_parameter(handle => h1, name => 'REUSE_DATAFILES', value => 0);
  dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNSUSABLE_INDEXES', value => 0);

  dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
  dbms_datapump.detach(handle => h1);
end;
/

导入已完成，数据可用于RDS数据库.您可以从DATA_PUMP_DIR清除文件，以下脚本即可完成

The import is done and data available into your RDS db. you can clean your files from the DATA_PUMP_DIR, the following script would do the job

-- remove file from data pump dir
begin
utl_file.fremove ('DATA_PUMP_DIR','import.log');
utl_file.fremove ('DATA_PUMP_DIR','<name of file>.dmp');
end fremove;
/

这篇关于如何将oracle dump从ec2实例导入到AWS中的RDS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！