Monday, November 23, 2015

Oracle on Amazon – Importing data into the Cloud Database

So I had a think about how to do this. I’ve set up a SQLDeveloper connection so thought I could use that, but that would only work for small and simple data transfers because it would probably be slow. 

Obviously the best way would be to use data pump, but I don’t have access to the host because the database is in the cloud, I didn’t set up a host and install Oracle (maybe another time). 

However, there is a data_pump_dir defined in the database: 

SYS   DATA_PUMP_DIR    /rdsdbdata/datapump 

So there must be a way to use it, and surely Amazon have some information on how to migrate data to the cloud. 

Aha! 

They suggest SQLDeveloper for small stuff, but also show how to use data pump. 

I pre-created the tablespace using SQL Developer – since I assumed the database would be using OMF, I just ran ‘create tablespace replicon;’ and let the database do all the hard work. 

Next, grab an export file from the source database –the Amazon document shows how to take an export using DMBS_DATAPUMP, or you can do it the old-fashioned way from the command line. 

Once you have the dump file, you create a database link from the source database to your Amazon cloud database: 

create database link andy_amazon connect to dbasupport identified by password 
using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))(CONNECT_DATA=(SID=ANDY)))'; 

Database link created.  

SQL> select table_name from user_tables@andy_amazon;  

no rows selected   

Next, the document says you need to get the file to the Amazon DATA_PUMP_DIR, using DBMS_FILE_TRANSFER and import using impdp. 

BEGIN DBMS_FILE_TRANSFER.PUT_FILE( 
source_directory_object => 'DATA_PUMP_DIR',  
source_file_name => 'replicon.dmp',  
destination_directory_object => 'DATA_PUMP_DIR',  
destination_file_name => 'replicon.dmp',  
destination_database => 'ANDY_AMAZON' );  
END;  
/ 

SQL> @trans_file  PL/SQL procedure successfully completed.   
The documentation shows the impdp command - but doesn't explain that you can't run commands because you don't have host access, you just have a database. Further searching revealed that you have to create an Amazon EC2 instance, install the Oracle client and then use the impdp binary from that.
  
That's a bit of a pain to be honest, but the easier way is just to use the host you took the export on - although you will need to weigh up the time it would take to create the Amazon EC2 host against the copy of the dumpfile. A 13MB file took about 3 minutes to transfer.

Create a tnsnames.ora entry for the Amazon database on your database host: 

ANDY_AMAZON = 
(DESCRIPTION =     
(ADDRESS_LIST =       
(ADDRESS = (PROTOCOL = TCP)(HOST = andy.xxxxx.us-west-2.rds.amazonaws.com)(PORT = 1521))     )     
(CONNECT_DATA =       (SERVICE_NAME = ANDY)     
)   
) 

Test that you can connect: 

[oracle@mgalinux admin]$ sqlplus dbasupport@ANDY_AMAZON  

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 07:09:48 2015  
Copyright (c) 1982, 2013, Oracle.  All rights reserved.  

Enter password: 
 SQL> 

select host_name from v$instance;  

HOST_NAME 
---------------------------------------------------------------- 
ip-10-3-x-xx 

Pre-create the user and grant any permissions on tablespaces as required. 

Now you should be able to use the data pump import into the Amazon database using the tnsnames entry, assuming you've copied the file there. 

impdp dbasupport@andy_amazon DUMPFILE=replicon.dmp DIRECTORY=DATA_PUMP_DIR full=y 

Import: Release 11.2.0.4.0 - Production on Tue Nov 24 07:29:09 2015  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password:  Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production Master table "DBASUPPORT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "DBASUPPORT"."SYS_IMPORT_FULL_01":  dbasupport/********@andy_amazon DUMPFILE=replicon.dmp DIRECTORY=DATA_PUMP_DIR full=y Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"REPLICON" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "REPLICON"."REPLICON"                       5.133 MB   34534 rows . . imported "REPLICON"."REPLICON_BACKUP"                5.121 MB   34438 rows . . imported "REPLICON"."LOGO"                           559.1 KB       1 rows . . imported "REPLICON"."CONTACT_DETAILS"                6.882 KB      22 rows . . imported "REPLICON"."PROJECTS"                       14.21 KB     100 rows . . imported "REPLICON"."REPLICON_YEARS"                 5.476 KB       5 rows Job "DBASUPPORT"."SYS_IMPORT_FULL_01" completed at Mon Nov 23 20:38:05 2015 elapsed 0 00:00:09 
 Log into SQLDeveloper and check the tables are there: 
Image 
  
So that was actually quite easy - obviously large amounts of data may be a challenge, but the process works. 
Next time - installing APEX and running an APEX application from the cloud.