Welcome to the Beyond Blog

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.




The views expressed in this blog and completely my own and do not reflect those of Beyond Systems Ltd. All content is provided for informational purposes only, and you are solely responsible for how you use this on your own or others systems. We accept no liability for any losses or damages caused.
8 minutes reading time (1515 words)

Connecting your DBaaS Cloud Instance to your On-Premise ERP Instance

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 ...

Data Visualization Desktop – Data Actions
Provisioning an Oracle Database Cloud Instance - F...
 

Comments

No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Guest
Thursday, 18 October 2018

Demonstration

Request a demo of our products here

REQUEST DEMO

Contact

Beyond Systems Limited

76 King Street, Manchester

M2 4NH United Kingdom

 

 

Tel:    +44(0)8450 940 998

Email:  contact@wegobeyond.co.uk