Unless you’ve been hiding under a stone for the past few years you’ll know that the cloud is the big thing at Oracle. There are fewer and fewer on-premise installations for greenfield projects. With the new pricing structure it is easy to see why more and more organizations are considering cloud services for their new developments. An easy venture for a client new to cloud may be say a reporting suite, developed in APEX, utilizing data from their source ERP system. The big question then of course is how do you transfer your data to the cloud securely? there are many products out there to facilitate this, such as Oracle Data Integrator (ODI), Oracle DataSync, custom processes with file transfers over sFTP etc. However I want to show a really easy way to do this via an SSH tunnel.
There are a number of steps that need to be done – some are optional (such as TNS Names entries) and you can work without them, however I’ve written the post as I would prefer to set it up – you may choose . I am using E-Business Suite R12.1.3 Vision as a source system, however the principle applies equally to others.
Source System Configuration
First we create a read-only user on the source system and grant the objects we wish to expose. We then create synonyms as that user to make querying easier (and to protect against change in the future).
As SYS
VIS121 r121@ebs121-vm ~ $ sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Tue Dec 12 16:00:40 2017 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create user apps_cl_query identified by apps_cl_query; User created. SQL> grant connect, resource to apps_cl_query; Grant succeeded. SQL> conn apps/apps Connected. SQL> grant select on per_all_people_f to apps_cl_query; Grant succeeded. SQL> conn apps_cl_query/apps_cl_query Connected. SQL> create synonym per_all_people_f for apps.per_all_people_f; Synonym created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options VIS121 r121@ebs121-vm ~ $
Now we’ll create an unprivileged user to connect as, and copy our public key from our DBaaS instance into the ~/.ssh/authorized_keys file on our source system. If you don’t have a cloud private/public key pair then you can create one using ssh-keygen.
Note – we create the user without a password, so it’s only possible to log in using SSH keys. This is generally considered more secure.
As root
VIS121 root@ebs121-vm $ useradd cloudlink VIS121 root@ebs121-vm $ su - cloudlink [cloudlink@ebs121-vm ~]$ cat id_rsa.pub >> ~/.ssh/authorized_keys [cloudlink@ebs121-vm ~] $ chmod 644 ~/.ssh/authorized_keys
Next, ensure that ssh is running on your source system and that the port is surfaced through your firewall. This will be specific to your infrastructure, however on my test system I surfaced as port 23 as I was already using port 22 on another service.
DBaaS Configuration
As opc user
First, add an entry into your /etc/hosts file on the DBaaS instance so that you can reference the connection via a hostname rather than the IP Address
[opc@JKEYMER ~]$ sudo vi /etc/hosts
As oracle user
Now verify you can SSH onto your on-premise system (as the new cloudlink user) from the DBaaS instance. Note – I’ve removed details for security reasons.
[oracle@JKEYMER ~]$ ssh -p 23 cloudlink@beyond_public The authenticity of host '[beyond_public]:23 ([xx.xx.xx.xx]:23)' can't be established. RSA key fingerprint is xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '[beyond_public]:23,[xx:xx:xx:xx]:23' (RSA) to the list of known hosts. Last login: Tue Dec 12 14:57:39 2017 from oc-xx-xx-xx-xx.compute.oraclecloud.com VIS121 cloudlink@ebs121-vm ~ $
Now we have verified connectivity, we set up an SSH tunnel on the DBaaS instance to our on-premise instance. We use -N to prevent a launching a remote shell, we map port 1522 to 1531 on the local machine, and we run in the background with &.
[oracle@JKEYMER ~]$ ssh -N -p 23 -L 1531:localhost:1522 cloudlink@beyond_public & [1] 16731
Then we add an TNS entry for the remote service on the local port.
[oracle@JKEYMER ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
ORCL_RMT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = vis121) ) )
Now test that we can see the remote table we granted earlier.
[oracle@JKEYMER ~]$ sqlplus apps_cl_query/apps_cl_query@orcl_rmt SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 12 15:17:24 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for Linux: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Now let’s connect to our DBaaS instance and create a database link to our on-premise system.
[oracle@JKEYMER ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 12 15:18:39 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production SQL> alter session set container=pdb1; Session altered. SQL> create user app_user identified by app_user; User created. SQL> grant connect, resource to app_user; Grant succeeded. SQL> grant unlimited tablespace to app_user; Grant succeeded. SQL> grant create database link to app_user; Grant succeeded. SQL> grant create synonym to app_user; Grant succeeded. SQL> conn app_user/app_user@pdb1 Connected. SQL> create database link orcl_rmt connect to apps_cl_query identified by apps_cl_query using 'orcl_rmt'; Database link created. SQL> create synonym rmt_per_all_people_f for per_all_people_f@orcl_rmt; Synonym created. SQL> select count(*) from rmt_per_all_people_f; COUNT(*) ---------- 32298
I prefer to create synonyms for objects when accessing over a database link however it isn’t strictly necessary.
Anway… so what if I now wanted to extract all my employees into a table in my DBaaS instance? Well, that’s as easy as:
[oracle@JKEYMER ~]$ sqlplus app_user/app_user@pdb1 SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 12 15:26:50 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Tue Dec 12 2017 15:26:04 +00:00 Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production SQL> set timing on SQL> create table people_extract as select person_id, full_name, employee_number from rmt_per_all_people_f; Table created. Elapsed: 00:00:00.84 SQL> select count(*) from people_extract; COUNT(*) ---------- 32298 Elapsed: 00:00:00.02 SQL>
So even from my R12.1.3 vision instance which is running with 3.5Gb RAM on a VirtualBox machine on a PC in our office, I can still transfer 32K records (albeit very slim ones) extremely quickly! Plus my cloud instance is the smallest possible one I could create. So let’s try a table that’s a bit more hefty.
First we need to give visibility on our source EBS instance.
VIS121 r121@ebs121-vm $ sqlplus apps/apps SQL*Plus: Release 11.1.0.7.0 - Production on Tue Dec 12 16:30:22 2017 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from gl_balances; COUNT(*) ---------- 9231273 SQL> grant select on gl_balances to apps_cl_query; Grant succeeded. SQL> conn apps_cl_query/apps_cl_query; Connected. SQL> create synonym gl_balances for apps.gl_balances; Synonym created.
Then we simply load that in:
As apps_cl_query
SQL> create synonym rmt_gl_balances for gl_balances@orcl_rmt; Synonym created. SQL> set timing on SQL> create table balances_extract as select ledger_id, code_combination_id, currency_code, period_name, actual_flag, 2 period_net_dr, period_net_cr from rmt_gl_balances; Table created. Elapsed: 00:01:02.66 SQL> select count(*) from balances_extract; COUNT(*) ---------- 9231273 Elapsed: 00:00:00.35 SQL>
Just over a minute to load in 9.2M rows over the internet between two pretty lightweight services. I was reasonably surprised! 🙂 Note – if you are connecting to the remote database using sqlplus directly (i.e. sqlplus apps_cl_query/apps_cl_query@orcl_rmt) and dumping out large amounts of data, you’ll probably want to increase the arraysize in sqlplus (i.e. set arraysize 1000) because I found I got a lot of client waits otherwise.
This method does require you to publish an SSH port to the external world, however that is obviously secured via SSH security. Your database port isn’t published externally as we tunnel that port through SSH – that gives the additional benefit that the data transmission between the two servers is also secure. Plus connectivity to the ERP system is done via a read-only user – there is no way we can accidentally delete anything, update records etc, and we can only see what has specifically been granted to that user. So the weakest link here is of course the SSH protocol – which isn’t really all that weak! The unprivileged user we created (cloudlink) has no elevated access on the host system so risk is again minimised on that front.
So if you’re looking for a lightweight, powerful and extremely fast method of getting your data from your source system into the cloud, maybe give this a thought…
Enter your text here …