t2.micro amazon linux instance with docker running an oracle xe 11g instance for RDS imports

docker_with_oracle.md

export RDSHELPER=the_ip_address_of_the_t2.micro_to_be_used

install docker

ssh -i ~/.ssh/e2ckey.pem ec2-user@$RDSHELPER
sudo yum update -y
sudo yum install -y docker
sudo service docker start
sudo usermod -a -G docker ec2-user
exit

install git

ssh -i ~/.ssh/e2ckey.pem ec2-user@$RDSHELPER
sudo yum install -y git
git config user.name "Karsten Lang Pedersen"
git config user.email "karsten@dashsoft.dk"
git config --global color.ui auto
git config --global alias.lol 'log --oneline --graph --all --decorate'
git config --global alias.unstage 'reset --'

pull oracle-xe-11g

docker pull wnameless/oracle-xe-11g

run oracle-xe-11g

docker run -d -v /tmp:/tmp -p 49160:22 -p 49161:1521 wnameless/oracle-xe-11g

reset known_hosts

If ssh connection to the container fails, reset known_hosts

echo "" > .ssh/known_hosts

connect to the container

ssh root@localhost -p 49160
password: admin

example

[ec2-user@rdshelper ~]$ sudo ssh root@localhost -p 49160
The authenticity of host '[localhost]:49160 ([127.0.0.1]:49160)' can't be established.
ECDSA key fingerprint is 8f:cd:28:28:95:86:cb:25:b5:9d:d6:a8:64:8e:f4:e4.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '[localhost]:49160' (ECDSA) to the list of known hosts.
root@localhost's password:
Welcome to Ubuntu 14.04 LTS (GNU/Linux 3.2.0-70-generic x86_64)

 * Documentation:  https://help.ubuntu.com/

The programs included with the Ubuntu system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Ubuntu comes with ABSOLUTELY NO WARRANTY, to the extent permitted by
applicable law.

root@abbeeb549647:~#

create a database connection

For convenience, we create a tns entry in tnsnames.ora rds-endpoint has to be specified in each case

echo "to_rds = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rds-endpoint)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))" >> /u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora

From inside the database, we create a database link to the rds:

sqlplus system/oracle
drop database link to_rds;
create database link to_rds connect to sysstibo identified by stibo123 using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rds-endpoint)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))';

At this point, we should be able to select from to_rds:

select version, instance_name from V$instance@to_rds;

If the connection hangs, make sure that the ip-address for rdshelper ip-address is added as an "Oracle-RDS" inbound rule on the security group for the RDS. The normal rules for instance to instance communication on AWS applies.

create a oracle directory

Assume that the dmp file /tmp/EXPDP-SPACE-01.DMP is present, and make a link to that.

CREATE OR REPLACE DIRECTORY DATA_DUMP AS '/tmp/';

It is now possible to transfer the dmp file to the RDS, like this:

BEGIN
  DBMS_FILE_TRANSFER.PUT_FILE(
    source_directory_object       => 'DATA_DUMP',
    source_file_name              => 'EXPDP-SPACE-01.DMP',
    destination_directory_object  => 'DATA_PUMP_DIR',
    destination_file_name         => 'EXPDP-SPACE-01.DMP',
    destination_database          => 'to_rds' 
  );
END;
/

verify that the file was transferred

sqlplus username/password@to_rds
select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime; 

prepare the RDS

At this point, prepare the tablespaces and users on the rds instance:

sqlplus username/password@to_rds

Where (master) username and password were specified during the RDS construction.

(create tablespaces and users that match the info in the dmp file)

import the data

impdp tells the RDS to import the dmp file located in it's own DATA_PUMP_DIR

impdp username/password@to_rds DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP-SPACE-01.DMP full=y table_exists_action=replace