Given the Limitations:
· Oracle Database data in geographical region X cannot be physically moved to region Y due to legal constraints
· Oracle Database data in region X can only be accessed remotely from region Y
Given that the volume of data accessed across region with be medium i.e., the OLTP application Z at region Y will access Oracle data from region X remotely. There will be no OLTP application Z at region X where Oracle database data is located.
1. What is the recommended (best practice) method of accessing region X Oracle data remotely from region Y using OLTP application Z?
2. What other options are available to remotely access region X Oracle data from region Y?
3. What are the pros & cons of using Oracle DB links to access region X Oracle data from region Y?
4. Is Oracle Gateway required for access of region X Oracle data from region Y – given that end-to-end it is only Oracle database data?
First of all for connecting from an Oracle database to another Oracle database NO gateway is required. Gateways are commonly used to connect to foreign databases like DB2, SQL Server, MySQl etc.
In general a normal database link created in Oracle database residing in region Y to access the data in region X is sufficient. But please keep in mind that depending on the amount of data you fetch all the time through the database link your network bandwith might not be sufficient and you have to think about different approaches like replicating the data from region x to region y using Oracle Streams or GoldenGate.
The advantage using a database link is for sure that you have realtime data access. As soon as it changes in region X you'll see the changed data in region Y whereas replicating data is most commonly having some delay until the data is replicated.
but to answer 4 - the answer is no. Gateways should not be used for connectivity between Oracle databases. They are designed to be used for connections between Oracle and non-Oracle databases.
There is no advantgae to using a gateway (the only one that could be used would be the database Gateway for ODBC) between Oracle database than using direct database links.