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).
VIS121 r121@ebs121-vm ~ $ sqlplus / as sysdba SQL*Plus: Release 220.127.116.11.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 18.104.22.168.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 22.214.171.124.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options VIS121 r121@ebs121-vm ~ $