oracle goldengate best practices: replication between ... · pdf fileoracle goldengate best...
TRANSCRIPT
Oracle GoldenGate Best Practices: Replication between Cloud and On-Premise Environments with Oracle GoldenGate Version 12c
Document ID 1996653.1
O R A C L E W H I T E P A P E R | A P R I L 2 0 1 5
Tracy West
Consulting Solution Architect A-Team – Cloud Solution Architects
REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Disclaimer
This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.
This document touches briefly on many important and complex concepts and does not provide a detailed explanation of any one topic since the intent is to present the material in the most expedient manner. The goal is simply to help the reader become familiar enough with the product to successfully design and implement an Oracle GoldenGate environment. To that end, it is important to note that the activities of design, unit testing and integration testing which are crucial to a successful implementation have been intentionally left out of the guide. All the sample scripts are provided as is. Oracle consulting service is highly recommended for any customized implementation.
1 | OGG 12C TUTORIAL FOR ORACLE TO ORACLE 12C MULTITENANT CONTAINER DATABASE
Table of Contents
Disclaimer 1
Introduction 5
Prerequisites 6
Additional OGG Considerations 6
Overview 6
Cloud to On-premise utilizing a Mid-tier 8
Setup of OGG in Cloud Environment 8
Source Setup 8
Create OGG user (oragg) 9
Grant oragg user permissions 9
Enable Supplemental logging at the schema level 9
Create a credential store and Add user credentials 9
Creating the Wallet and Adding a Master Key 9
Create CMDSEC file in the OGG Install Directory 10
MGR Parameter File 10
Extract Parameter File 10
Extract Pump Parameter File 11
Creating OGG Processes and Trail Files on Cloud Server 11
Setup of OGG on Mid-Tier 11
Mid-tier binaries Setup 11
Copy the Wallet From the Source System to the Mid-Tier 11
Create CMDSEC file in the OGG Install Directory 11
2 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Mid-Tier MGR Parameter File 12
Mid-Tier Passive Extract Pump Parameter File 12
Syntax to Create Groups and Trail File on Mid-Tier 12
Setup of OGG Target On-Premise 12
On-Premise Setup 12
Create OGG user (oragg) 12
Grant oragg user permissions 13
Create a credential store and Add user credentials 13
Copy the Wallet From the Source System to the On-Premise System 13
Create CMDSEC file in the OGG Install Directory 13
On-Premise MGR Parameter File 13
On-Premise Replicat Parameter File 14
Syntax to Create On-Premise Groups and Trail Files 14
Start of OGG Processes 14
Cloud to On-premise utilizing a Passive Extract Pump Process 15
Setup of OGG in Cloud Environment 15
Source Setup 15
Create OGG user (oragg) 16
Grant oragg user permissions 16
Enable Supplemental logging at the schema level 16
Create a credential store and Add user credentials 16
Creating the Wallet and Adding a Master Key 16
Create CMDSEC file in the OGG Install Directory 17
3 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
MGR Parameter File 17
Extract Parameter File 17
Extract Pump Parameter File 18
Creating OGG Processes and Trail Files on Cloud Server 18
Setup of OGG Target On-Premise 18
On-Premise Setup 18
Create OGG user (oragg) 18
Grant oragg user permissions 18
Create a credential store and Add user credentials 19
Copy the Wallet From the Source System to the On-Premise System 19
Create CMDSEC file in the OGG Install Directory 19
On-Premise MGR Parameter File 19
On-Premise Replicat Parameter File 19
Syntax to Create On-Premise Groups and Trail Files 20
Start of OGG Processes 20
Cloud to On-premise utilizing a Passive Extract 21
Setup of OGG in Cloud Environment 21
Source Setup 21
Create OGG user (oragg) 22
Grant oragg user permissions 22
Enable Supplemental logging at the schema level 22
Create a credential store and Add user credentials 22
Creating the Wallet and Adding a Master Key 22
4 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Create CMDSEC file in the OGG Install Directory 23
MGR Parameter File 23
Extract Parameter File 23
Creating OGG Processes and Trail Files on Cloud Server 24
Setup of OGG Target On-Premise 24
On-Premise Setup 24
Create OGG user (oragg) 24
Grant oragg user permissions 24
Create a credential store and Add user credentials 24
Copy the Wallet From the Source System to the On-Premise System 25
Create CMDSEC file in the OGG Install Directory 25
On-Premise MGR Parameter File 25
On-Premise Replicat Parameter File 25
Syntax to Create On-Premise Groups and Trail Files 26
Start of OGG Processes 26
Where to Go for More Information 26
5 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Introduction
Oracle GoldenGate Extract, Replicat and associated utilities enable you to create, load and refresh
one database to another database. This white paper will be utilizing examples for Oracle to Oracle
homogenous replication, but it does not preclude the source and target environments being
heterogeneous databases. These examples also show data being replicated between a Cloud and
On-Premise environment. The example environments can easily be reversed to be replication
between On-Premise and a Cloud environment. It depends on your specific requirements.
This best practice provides a quick overview of different approaches for replicating data between an
Oracle database in the Cloud and an Oracle database On-Premise. This paper will be referencing
Integrated Capture and Integrated Delivery for version 12c and above. For more detailed information
on the OGG components described in this paper, please consult the Oracle GoldenGate
Administration Guide.
This best practice may be read to get a general overview of how to replicate data between a cloud
environment and an on-premise environment. The configurations described in this paper are only
slightly different than a normal OGG configuration due to the heightened network security
requirements when replicating data outside the firewall.
6 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Prerequisites
If you plan to execute the instructions in this best practice, make sure all software is already installed. For the mid-
tier, the full database binaries must be installed as well as the OGG software. The reader should be familiar with
basic OGG architecture and functionality. For Oracle RDBMS 12.1.0.2 and above, the init.ora parameter,
enable_goldengate_replication must be set to TRUE in both the source and target databases.
The following table describes items that are referred to throughout the document. You will need to identify your
installation-specific values and substitute them as you go.
Item Reference Description
Unix Programs /ggs Directory of Unix GoldenGate installation.
Unix Parameter Files /ggs/dirprm Directory for GoldenGate parameter files.
Unix Report Files /ggs/dirrpt Directory for output from GoldenGate programs.
Unix Definitions Files /ggs/dirdef Directory for generated Oracle DDL and definition files.
GGS temporary storage /ggs/dirdat Directory to hold temporary Extract trails
Oracle Logon userid, password User ID and password for the source or target database. When implementing
Integrated Extract or Replicat, this user must be granted admin privileges with the
DBMS_GOLDENGATE_AUTH procedure on the both source and target databases.
Unix System Network
Address
On-Premise Server
Cloud Server
Mid-Tier Server
IP address/hostname of the target Unix system in network.
IP address/hostname of the source Unix system in network
IP address/hostname of the mid-tier Unix-system in network
Additional OGG Considerations
» Key Management
» Need to share encryption key for trail file encryption between OGG source in Cloud and OGG target On-
Premise
» Password Security
» Passwords used by GoldenGate for Database access encrypted using AES.
» Command Security
» Manager configured with CMDSEC security. Restrict by IP Address and User for IPC Messages using
ACCESSRULE Parameter.
» Process Management
» GoldenGate can be configured to auto start / restart processes upon any failure including network failure.
» Monitoring
» All GoldenGate deployments can be monitored by EM with optional Monitor Agent which are not covered
in this document.
Overview
7 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
This document will address different approaches to extracting data from a Cloud environment and replicating it to an
on-premise environment.
» Cloud to On-Premise using a Mid-Tier
» In this approach, a mid-tier is utilized to eliminate the requirement of the cloud or on-premise environment
to have a direct connection into either environment. Only the mid-tier allows a direct network connection to
specific ports defined by the PORT and DYNAMICPORTLIST parameters in the manager parameter file.
» Cloud to On-Premise utilizing a Passive Extract Pump
» In this approach, the cloud environment is allowing a connection to very specific ports from the trusted on-
premise environment. The data is extracted from the cloud database with a standard extract, but the data
is pushed to the on-premise environment using a Passive extract pump. The passive extract pump is
stopped and started from the on-premise environment. The connection to the passive extract pump is
initiated by the on-premise environment to specific ports defined by the PORT and DYNAMICPORTLIST
parameters in the manager parameter file.
» Cloud to On-Premise utilizing a Passive Extract
» In this approach, the cloud environment is allowing a connection to very specific ports from the trusted on-
premise environment. The data is extracted from the cloud database with a passive extract.. The passive
extract is stopped and started from the on-premise environment. The connection to the passive extract is
initiated by the on-premise environment to specific ports defined by the PORT and DYNAMICPORTLIST
parameters in the manger parameter file. The caveat with this approach is that if the network
connection goes down, the extract process will abend. As a result, data will not be extracted until
the connection is restored.
Extract, Extract pump and Replicat work together to keep the databases in sync near real-time via incremental
transaction replication. In all examples this function is accomplished by
» Starting the Manager program in all OGG installed systems.
» Adding supplemental transaction log data for update operations on the source system.
» Running the real-time Extract to retrieve and store the incremental changed data from the Oracle tables into
trail files on the target Unix system.
» In the first 2 approaches, running the real-time Passive Extract pump to send incremental changed data from
the cloud environment or the mid-tier to the target on-premise system.
After initial synchronization,
» Start the real-time Replicat to replicate extracted data.
Once Extract and Replicat are running, changes are replicated perpetually.
Notes on Command Syntax: Commands throughout the document make specific references to directories, file
names, checkpoint group names, begin times, etc. Unless otherwise noted, these items do not have to correspond
exactly in your environment; they are used to illustrated concrete examples. For exact syntax, consult the Oracle
GoldenGate Reference Guide.
8 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Cloud to On-premise utilizing a Mid-tier
This configuration is required when the Cloud and On-Premise environments will not allow a direct
connection into their repective networks. This section utilizes an extract process pulling data from a
cloud database and an extract pump process to send encrypted data across the network to a system
outside the internal firewalls of the cloud environment to a Mid-Tier system behind a public network
firewall. The on-premise system then pulls the data via a passive extract pump to be applied to the on-
premise database with an OGG delivery process. The passive extract pump process is started from
behind the Internal Firewall within the On-Premise system. The data is encrypted and processes can
only be initiated from specific IP Addresses by utilizing the ACCESSRULE parameter for additional
security.
Figure 1 – Architecture Mid-Tier
Setup of OGG in Cloud Environment
Source Setup
See 12c Installation Guide
1. Install OGG Software
2. Set Library paths, Oracle Environmental Variables
3. Create subdirectories for OGG
GGSCI> create subdirs
4. Enable minimal supplemental logging in the database
9 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
5. Increase UNDO to allowed maximum (recommend 24 hours)
6. Turn on FORCE LOGGING in the tablespaces where tables are created with NOLOGGING
Create OGG user (oragg)
See 12c Installation Guide
SQL> create user oragg identified by xxxxxx;
SQL> alter user oragg default tablespace <OGG Tablespace Name> temporary
tablespace TEMP quota unlimited on <OGG Tablespace Name>;
Grant oragg user permissions
See 12c Installation Guide
SQL> grant resource, dba to oragg;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ORAGG' );
Enable Supplemental logging at the schema level
See 12c Installation Guide
----- Source System
GGSCI> DBLOGIN USERID oragg, PASSWORD xxxx
GGSCI> ADD SCHEMATRANDATA APPS
Create a credential store and Add user credentials
See 12c Adminstrative Guide
1. (Optional) To store the credential store in a location other than the dircrd subdirectory of the Oracle
GoldenGate installation directory, specify the desired location with the CREDENTIALSTORELOCATION
parameter in the GLOBALS file.
2. Create the credential store.
GGSCI> ADD CREDENTIALSTORE
3. Add each set of credentials to the credential store.
GGSCI> ALTER CREDENTIALSTORE ADD USER oragg, PASSWORD xxxx ALIAS oggalias
Creating the Wallet and Adding a Master Key
See 12c Adminstrative Guide
1. (Optional) To store the wallet in a location other than the dirwlt subdirectory of the Oracle GoldenGate
installation directory, specify the desired location with the WALLETLOCATION parameter in the GLOBALS
file.
WALLETLOCATION directory_path
10 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
2. Create a master-key wallet
GGSCI> CREATE WALLET
3. Add a master key to the wallet with the
GGSCI> ADD MASTERKEY
4. Confirm that the key you added is the current version. In a new installation, the version should be 1.
GGSCI> INFO MASTERKEY
5. Copy the wallet to all of the other Oracle GoldenGate systems once they are setup
Create CMDSEC file in the OGG Install Directory
CMDSEC – blocks all commands to all Operating System users except the OGG User. Lock access to this file
(chmod go-r CMDSEC).
--Command Object Group User Access Allowed?
* * oinstall oragg YES
* * * * NO
MGR Parameter File
PORT 7801
DYNAMICPORTLIST 7802-7803
PURGEOLDEXTRACTS ./et* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
DOWNREPORTMINUTES 15
DOWNCRITICAL
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
--- Define what System can issue GGSCI Commands
ACCESSRULE, PROG *, IPADDR <Cloud System IP Address>, ALLOW
ACCESSRULE, PROG *, IPADDR *, DENY
Note: The local system IPADDR may be the first entry of the loopback (127.0.0.1) or localhost in the /etc/hosts file.
The MGR.rpt file will list what IP Address is trying to initiate the command.
Extract Parameter File
EXTRACT ECLOUD
SETENV (ORACLE_SID = ‘<oracle_sid>’)
SETENV (NLS_LANG = ‘AMERICAN_AMERICA.AL32UTF8’)
--- Use Alias to login
USERIDALIAS oggalias
DISCARDFILE ./dirrpt/ecloud.dsc, APPEND
DISCARDROLLOVER AT 01:00 ON SUNDAY
--- Use Wallet to encrypt local trail
ENCRYPTTRAIL AES128
EXTTRAIL ./dirdat/et
11 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
STATOPTIONS REPORTFETCH
REPORTCOUNT every 10 minutes, RATE
REPORTROLLOVER AT 01:00 ON SUNDAY
--- DDL Parameters
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TABLE APPS.* ;
Extract Pump Parameter File
EXTRACT PCLOUD
RMTHOST <Mid-tier ip address>, MGRPORT 7801
PASSTHRU
RMTTRAIL ./dirdat/mt
TABLE APPS.* ;
Creating OGG Processes and Trail Files on Cloud Server
GGSCI> DBLOGIN USERID oragg, PASSWORD xxxx
GGSCI> REGISTER EXTRACT ECLOUD, DATABASE
GGSCI> ADD EXTRACT ECLOUD, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL ./dirdat/et, EXTRACT ECLOUD, MEGABYTES 500
GGSCI> ADD EXTRACT PCLOUD, EXTTRAILSOURCE ./dirdat/et
GGSCI> ADD RMTTRAIL ./dirdat/mt, EXTRACT PCLOUD, MEGABYTES 500
Setup of OGG on Mid-Tier
The mid-tier will be utilizing a passive extract pump. Even though the passive extract pump is installed in the middle
tier, the passive extract pump will be started and stopped from the On-Premise target system.
Mid-tier binaries Setup
Full database binaries are required to be installed in order to execute OGG on that system.
See 12c Installation Guide
1. Install full database binaries and OGG Software
2. Create subdirectories for OGG
GGSCI> create subdirs
Copy the Wallet From the Source System to the Mid-Tier
See 12c Adminstrative Guide
Create CMDSEC file in the OGG Install Directory
CMDSEC – blocks all commands to all Operating System users except the OGG User. Lock access to this file
(chmod go-r CMDSEC).
--Command Object Group User Access Allowed?
* * oinstall oragg YES
* * * * NO
12 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Mid-Tier MGR Parameter File
PORT 7801
DYNAMICPORTLIST 7802-7803
PURGEOLDEXTRACTS ./mt* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
DOWNREPORTMINUTES 15
DOWNCRITICAL
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
ACCESSRULE, PROG *, IPADDR <Cloud Server IP Address>, ALLOW
ACCESSRULE, PROG *, IPADDR <Mid-Tier IP Address>, ALLOW
ACCESSRULE, PROG *, IPADDR <On-Premise IP Address>, ALLOW
ACCESSRULE, PROG *, IPADDR *, DENY
Note: The local system IPADDR may be the first entry of the loopback (127.0.0.1) or localhost in the /etc/hosts file.
The MGR.rpt file will list what IP Address is trying to initiate the command.
Mid-Tier Passive Extract Pump Parameter File EXTRACT PASSEXT
PASSTHRU
RMTTRAIL ./dirdat/rt
TABLE APPS.* ;
Syntax to Create Groups and Trail File on Mid-Tier
GGSCI> ADD EXTRACT PASSEXT, EXTTRAILSOURCE ./dirdat/mt, PASSIVE, DESC
“passive pump”
GGSCI> ADD RMTTRAIL ./dirdat/rt, EXTRACT PASSEXT, MEGABYTES 500
Setup of OGG Target On-Premise
The mid-tier passive extract pump will be stopped and started from this server. Communication will be initiated from
the On-Premise server.
On-Premise Setup
See 12c Installation Guide
1. Install OGG Software
2. Set Library paths, Oracle Environmental Variables
3. Create subdirectories for OGG
GGSCI> create subdirs
Create OGG user (oragg)
See 12c Installation Guide
SQL> create user oragg identified by xxxxxx;
SQL> alter user oragg default tablespace <OGG Tablespace Name> temporary
tablespace TEMP quota unlimited on <OGG Tablespace Name>;
13 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Grant oragg user permissions
See 12c Installation Guide
SQL> grant resource, dba to oragg;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ORAGG' );
Create a credential store and Add user credentials
See 12c Adminstrative Guide
1. (Optional) To store the credential store in a location other than the dircrd subdirectory of the Oracle
GoldenGate installation directory, specify the desired location with the CREDENTIALSTORELOCATION
parameter in the GLOBALS file.
2. Create the credential store.
GGSCI> ADD CREDENTIALSTORE
3. Add each set of credentials to the credential store.
GGSCI> ALTER CREDENTIALSTORE ADD USER oragg, PASSWORD xxxx ALIAS oggalias
Copy the Wallet From the Source System to the On-Premise System
See 12c Adminstrative Guide
Create CMDSEC file in the OGG Install Directory
CMDSEC – blocks all commands to all Operating System users except the OGG User. Lock access to this file
(chmod go-r CMDSEC).
--Command Object Group User Access Allowed?
* * oinstall oragg YES
* * * * NO
On-Premise MGR Parameter File
PORT 7801
DYNAMICPORTLIST 7802-7803
PURGEOLDEXTRACTS ./rt* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
DOWNREPORTMINUTES 15
DOWNCRITICAL
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
ACCESSRULE, PROG *, IPADDR <On-Premise IP Address>, ALLOW
ACCESSRULE, PROG *, IPADDR *, DENY
14 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Note: The local system IPADDR may be the first entry of the loopback (127.0.0.1) or localhost in the /etc/hosts file.
The MGR.rpt file will list what IP Address is trying to initiate the command.
On-Premise Replicat Parameter File
REPLICAT RONPREM
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/ronprem.dsc, APPEND
SETENV (NLS_LANG = ‘AMERICAN_AMERICA.AL32UTF8’)
SETENV (ORACLE_SID= ‘<ORACLE_SID>’)
--- Use Alias to login
USERIDALIAS oggalias
--- Replicat Automatically Decrypts Trail Data
--- DECRYPTTRAIL not required
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 01:00 ON SUNDAY
DISCARDROLLOVER AT 01:00 ON SUNDAY
--- DDL Parameters
DDL INCLUDE ALL
DDLOPTIONS REPORT
MAP APPS.*, TARGET APPS.*;
Syntax to Create On-Premise Groups and Trail Files
GGSCI> DBLOGIN USERID oragg, PASSWORD xxxx
GGSCI> ADD REPLICAT RONPREM, INTEGRATED, EXTTRAIL ./dirdat/rt
GGSCI> ADD EXTRACT PASSEXT, RMTHOST <Mid-Tier hostname/IP address>, MGRPORT <Mid-
Tier mgr port>
Start of OGG Processes
The manager process on all servers should be started.
GGSCI> start MGR
Start Extract and Extract Pump Process on Cloud Environment
GGSCI(Cloud Server)> START EXTRACT ECLOUD
GGSCI(Cloud Server)> START EXTRACT PCLOUD
Start Passive Extract Process from On-Premise Server
GGSCI (On-Premise Server)> START EXTRACT PASSEXT
Once the On-Premise Target system has been instantiated, the Delivery process can be started on the On-Premise
Server. For the documented best practice of instantiating from an Oracle source, please refer to Oracle
GoldenGate Best Practices: Instantiation from an Oracle Source Database (Doc ID 1276058.1)
Start Delivery Process at On-Premise Server
GGSCI (On-Premise Server)> START REPLICAT RONPREM, ATCSN|AFTERCSN <SCN>
15 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Cloud to On-premise utilizing a Passive Extract Pump Process
This configuration is an option when the On-Premise environment will not allow a direct connection into
the network, but the Cloud environment will allow a direct connection from a trusted system. This
section utilizes an extract process to pull data from the Cloud database. A passive extract pump
process pushes the data from the cloud environment to be applied to the on-premise database with an
OGG delivery process. The passive extract process is started from behind the Internal Firewall within
the On-Premise system. The connection to the OGG cloud environment is initiated by the on-premise
OGG environment to specific ports defined by the PORT and DYNAMICPORTLIST parameters in
the manager parameter file. The data is encrypted and processes can only be initiated from specific
IP Addresses by utilizing the ACCESSRULE parameter for additional security.
Figure 2 – Architecture Passive Extract Pump
Setup of OGG in Cloud Environment
Source Setup
See 12c Installation Guide
1. Install OGG Software
2. Set Library paths, Oracle Environmental Variables
3. Create subdirectories for OGG
GGSCI> create subdirs
4. Enable minimal supplemental logging in the database
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
16 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
5. Increase UNDO to allowed maximum (recommend 24 hours)
6. Turn on FORCE LOGGING in the tablespaces where tables are created with NOLOGGING
Create OGG user (oragg)
See 12c Installation Guide
SQL> create user oragg identified by xxxxxx;
SQL> alter user oragg default tablespace ORAGG_DATA temporary tablespace TEMP
quota unlimited on ORAGG_DATA;
Grant oragg user permissions
See 12c Installation Guide
SQL> grant resource, dba to oragg;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ORAGG' );
Enable Supplemental logging at the schema level
See 12c Installation Guide
----- Source System
GGSCI> DBLOGIN USERID oragg, PASSWORD xxxx
GGSCI> ADD SCHEMATRANDATA APPS
Create a credential store and Add user credentials
See 12c Adminstrative Guide
1. (Optional) To store the credential store in a location other than the dircrd subdirectory of the Oracle
GoldenGate installation directory, specify the desired location with the CREDENTIALSTORELOCATION
parameter in the GLOBALS file.
2. Create the credential store.
GGSCI> ADD CREDENTIALSTORE
3. Add each set of credentials to the credential store.
GGSCI> ALTER CREDENTIALSTORE ADD USER oragg, PASSWORD xxxx ALIAS oggalias
Creating the Wallet and Adding a Master Key
See 12c Adminstrative Guide
4. (Optional) To store the wallet in a location other than the dirwlt subdirectory of the Oracle GoldenGate
installation directory, specify the desired location with the WALLETLOCATION parameter in the GLOBALS
file.
WALLETLOCATION directory_path
5. Create a master-key wallet
GGSCI> CREATE WALLET
17 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
6. Add a master key to the wallet with the
GGSCI> ADD MASTERKEY
7. Confirm that the key you added is the current version. In a new installation, the version should be 1.
GGSCI> INFO MASTERKEY
8. Copy the wallet to all of the other Oracle GoldenGate systems once they are setup
Create CMDSEC file in the OGG Install Directory
CMDSEC – blocks all commands to all Operating System users except the OGG User. Lock access to this file
(chmod go-r CMDSEC).
--Command Object Group User Access Allowed?
* * oinstall oragg YES
* * * * NO
MGR Parameter File
PORT 7801
DYNAMICPORTLIST 7802-7803
PURGEOLDEXTRACTS ./et* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
DOWNREPORTMINUTES 15
DOWNCRITICAL
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
--- Define what System can issue GGSCI Commands
ACCESSRULE, PROG *, IPADDR <Source System IP Address>, ALLOW
ACCESSRULE, PROG *, IPADDR *, DENY
Note: The local system IPADDR may be the first entry of the loopback (127.0.0.1) or localhost in the /etc/hosts file.
The MGR.rpt file will list what IP Address is trying to initiate the command.
Extract Parameter File
EXTRACT ECLOUD
SETENV (ORACLE_SID = ‘<oracle_sid>’)
SETENV (NLS_LANG = ‘AMERICAN_AMERICA.AL32UTF8’)
--- Use Alias to login
USERIDALIAS oggalias
DISCARDFILE ./dirrpt/ecloud.dsc, APPEND
DISCARDROLLOVER AT 01:00 ON SUNDAY
--- Use Wallet to encrypt local trail
ENCRYPTTRAIL AES128
EXTTRAIL ./dirdat/et
18 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
STATOPTIONS REPORTFETCH
REPORTCOUNT every 10 minutes, RATE
REPORTROLLOVER AT 01:00 ON SUNDAY
--- DDL Parameters
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TABLE APPS.* ;
Extract Pump Parameter File
EXTRACT PASSEXT
PASSTHRU
RMTTRAIL ./dirdat/rt
TABLE APPS.* ;
Creating OGG Processes and Trail Files on Cloud Server
GGSCI> DBLOGIN USERID oragg, PASSWORD xxxx
GGSCI> REGISTER EXTRACT ECLOUD, DATABASE
GGSCI> ADD EXTRACT ECLOUD, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL ./dirdat/et, EXTRACT ECLOUD, MEGABYTES 500
GGSCI> ADD EXTRACT PASSEXT, EXTTRAILSOURCE ./dirdat/et, PASSIVE, DESC
“passive pump”
GGSCI> ADD RMTTRAIL ./dirdat/rt, EXTRACT PASSEXT, MEGABYTES 500
Setup of OGG Target On-Premise
The mid-tier passive extract pump will be stopped and started from this server. Communication will be initiated from
the On-Premise server.
On-Premise Setup
See 12c Installation Guide
1. Install OGG Software
2. Set Library paths, Oracle Environmental Variables
3. Create subdirectories for OGG
GGSCI> create subdirs
Create OGG user (oragg)
See 12c Installation Guide
SQL> create user oragg identified by xxxxxx;
SQL> alter user oragg default tablespace <OGG Tablespace Name> temporary
tablespace TEMP quota unlimited on <OGG Tablespace Name>;
Grant oragg user permissions
See 12c Installation Guide
SQL> grant resource, dba to oragg;
19 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ORAGG' );
Create a credential store and Add user credentials
See 12c Adminstrative Guide
1. (Optional) To store the credential store in a location other than the dircrd subdirectory of the Oracle
GoldenGate installation directory, specify the desired location with the CREDENTIALSTORELOCATION
parameter in the GLOBALS file.
2. Create the credential store.
GGSCI> ADD CREDENTIALSTORE
3. Add each set of credentials to the credential store.
GGSCI> ALTER CREDENTIALSTORE ADD USER oragg, PASSWORD xxxx ALIAS oggalias
Copy the Wallet From the Source System to the On-Premise System
See 12c Adminstrative Guide
Create CMDSEC file in the OGG Install Directory
CMDSEC – blocks all commands to all Operating System users except the OGG User. Lock access to this file
(chmod go-r CMDSEC).
--Command Object Group User Access Allowed?
* * oinstall oragg YES
* * * * NO
On-Premise MGR Parameter File
PORT 7801
DYNAMICPORTLIST 7802-7803
PURGEOLDEXTRACTS ./rt* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
DOWNREPORTMINUTES 15
DOWNCRITICAL
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
ACCESSRULE, PROG *, IPADDR <On-Premise IP Address>, ALLOW
ACCESSRULE, PROG *, IPADDR *, DENY
Note: The local system IPADDR may be the first entry of the loopback (127.0.0.1) or localhost in the /etc/hosts file.
The MGR.rpt file will list what IP Address is trying to initiate the command.
On-Premise Replicat Parameter File
REPLICAT RONPREM
20 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/ronprem.dsc, APPEND
SETENV (NLS_LANG = ‘AMERICAN_AMERICA.AL32UTF8’)
SETENV (ORACLE_SID= ‘<ORACLE_SID>’)
--- Use Alias to login
USERIDALIAS oggalias
--- Replicat Automatically Decrypts Trail Data
--- DECRYPTTRAIL not required
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 01:00 ON SUNDAY
DISCARDROLLOVER AT 01:00 ON SUNDAY
--- DDL Parameters
DDL INCLUDE ALL
DDLOPTIONS REPORT
MAP APPS.*, TARGET APPS.*;
Syntax to Create On-Premise Groups and Trail Files
GGSCI> DBLOGIN USERID oragg, PASSWORD xxxx
GGSCI> ADD REPLICAT RONPREM, INTEGRATED, EXTTRAIL ./dirdat/rt
GGSCI> ADD EXTRACT PASSEXT, RMTHOST <Cloud System hostname/IP address>, MGRPORT
<Cloud mgr port>
Start of OGG Processes
The manager process on all servers should be started.
GGSCI> start MGR
Start Extract on Cloud Environment
GGSCI(Cloud Server)> START EXTRACT ECLOUD
Start Passive Extract Process from On-Premise Server
GGSCI (On-Premise Server)> START EXTRACT PASSEXT
Once the On-Premise Target system has been instantiated, the Delivery process can be started on the On-Premise
Server. For the documented best practice of instantiating from an Oracle source, please refer to Oracle
GoldenGate Best Practices: Instantiation from an Oracle Source Database (Doc ID 1276058.1)
Start Delivery Process at On-Premise Server
GGSCI (On-Premise Server)> START REPLICAT RONPREM, ATCSN|AFTERCSN <SCN>
21 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
Cloud to On-premise utilizing a Passive Extract
» This configuration is an option when the On-Premise environments will not allow a direct connection into
their network, but the Cloud environment will allow a direct connection from a trusted system to specific
ports. This section utilizes a passive extract to pull data from the Cloud database to be applied to the on-
premise database with an OGG delivery process. The passive extract process is started from behind the
Internal Firewall within the On-Premise system to specific ports defined by the PORT and DYNAMICPORTLIST parameters in the manager parameter file. This approach is utilized
when an extract process cannot be stopped and started from the Cloud Environment. With this approach,
Extract will stop processing data if the network connection between the Cloud and On-Premise
environment is not available. The data is encrypted and processes can only be initiated from specific IP
Addresses by utilizing the ACCESSRULE parameter for additional security. The caveat with this
approach is that if the network connection goes down, the extract process will abend. As a result,
data will not be extracted until the connection is restored and all the required archive logs are
available on the Cloud environment. So any archive logs removed and backed up during the
outage will need to be restored for extract to restart. Also extract performance is dependent on the
network performance.
Figure 3 – Architecture Passive Extract
Setup of OGG in Cloud Environment
Source Setup
See 12c Installation Guide
1. Install OGG Software
2. Set Library paths, Oracle Environmental Variables
3. Create subdirectories for OGG
GGSCI> create subdirs
4. Enable minimal supplemental logging in the database
22 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
5. Increase UNDO to allowed maximum (recommend 24 hours)
6. Turn on FORCE LOGGING in the tablespaces where tables are created with NOLOGGING
Create OGG user (oragg)
See 12c Installation Guide
SQL> create user oragg identified by xxxxxx;
SQL> alter user oragg default tablespace <OGG Tablespace Name> temporary
tablespace TEMP quota unlimited on <OGG Tablespace Name>;
Grant oragg user permissions
See 12c Installation Guide
SQL> grant resource, dba to oragg;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ORAGG' );
Enable Supplemental logging at the schema level
See 12c Installation Guide
----- Source System
GGSCI> DBLOGIN USERID oragg, PASSWORD xxxx
GGSCI> ADD SCHEMATRANDATA APPS
Create a credential store and Add user credentials
See 12c Adminstrative Guide
1. (Optional) To store the credential store in a location other than the dircrd subdirectory of the Oracle
GoldenGate installation directory, specify the desired location with the CREDENTIALSTORELOCATION
parameter in the GLOBALS file.
2. Create the credential store.
GGSCI> ADD CREDENTIALSTORE
3. Add each set of credentials to the credential store.
GGSCI> ALTER CREDENTIALSTORE ADD USER oragg, PASSWORD xxxx ALIAS oggalias
Creating the Wallet and Adding a Master Key
See 12c Adminstrative Guide
1. (Optional) To store the wallet in a location other than the dirwlt subdirectory of the Oracle GoldenGate
installation directory, specify the desired location with the WALLETLOCATION parameter in the GLOBALS
file.
WALLETLOCATION directory_path
23 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
2. Create a master-key wallet
GGSCI> CREATE WALLET
3. Add a master key to the wallet with the
GGSCI> ADD MASTERKEY
4. Confirm that the key you added is the current version. In a new installation, the version should be 1.
GGSCI> INFO MASTERKEY
5. Copy the wallet to all of the other Oracle GoldenGate systems once they are setup
Create CMDSEC file in the OGG Install Directory
CMDSEC – blocks all commands to all Operating System users except the OGG User. Lock access to this file
(chmod go-r CMDSEC).
--Command Object Group User Access Allowed?
* * oinstall oragg YES
* * * * NO
MGR Parameter File
PORT 7801
DYNAMICPORTLIST 7802-7803
AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
DOWNREPORTMINUTES 15
DOWNCRITICAL
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
--- Define what System can issue GGSCI Commands
ACCESSRULE, PROG *, IPADDR <cloud System IP Address>, ALLOW
ACCESSRULE, PROG *, IPADDR <on-premise system IP Address>, ALLOW
ACCESSRULE, PROG *, IPADDR *, DENY
Note: The local system IPADDR may be the first entry of the loopback (127.0.0.1) or localhost in the /etc/hosts file.
The MGR.rpt file will list what IP Address is trying to initiate the command.
Extract Parameter File
EXTRACT PASSEXT
SETENV (ORACLE_SID = ‘<oracle_sid>’)
SETENV (NLS_LANG = ‘AMERICAN_AMERICA.AL32UTF8’)
--- Use Alias to login
USERIDALIAS oggalias
DISCARDFILE ./dirrpt/passext.dsc, APPEND
DISCARDROLLOVER AT 01:00 ON SUNDAY
--- Use Wallet to encrypt local trail
ENCRYPTTRAIL AES128
RMTTRAIL ./dirdat/rt
24 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
STATOPTIONS REPORTFETCH
REPORTCOUNT every 10 minutes, RATE
REPORTROLLOVER AT 01:00 ON SUNDAY
--- DDL Parameters
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TABLE APPS.* ;
Creating OGG Processes and Trail Files on Cloud Server
GGSCI> DBLOGIN USERID oragg, PASSWORD xxxx
GGSCI> REGISTER EXTRACT PASSEXT, DATABASE
GGSCI> ADD EXTRACT PASSEXT, INTEGRATED TRANLOG, BEGIN NOW, PASSIVE, DESC “passive
extract”
GGSCI> ADD RMTTRAIL ./dirdat/rt, EXTRACT PASSEXT, MEGABYTES 500
Setup of OGG Target On-Premise
The passive extract will be stopped and started from this server. Communication will be initiated from the On-
Premise server.
On-Premise Setup
See 12c Installation Guide
1. Install OGG Software
2. Set Library paths, Oracle Environmental Variables
3. Create subdirectories for OGG
GGSCI> create subdirs
Create OGG user (oragg)
See 12c Installation Guide
SQL> create user oragg identified by xxxxxx;
SQL> alter user oragg default tablespace <OGG Tablespace Name> temporary
tablespace TEMP quota unlimited on <OGG Tablespace Name>;
Grant oragg user permissions
See 12c Installation Guide
SQL> grant resource, dba to oragg;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ORAGG' );
Create a credential store and Add user credentials
See 12c Adminstrative Guide
25 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
1. (Optional) To store the credential store in a location other than the dircrd subdirectory of the Oracle
GoldenGate installation directory, specify the desired location with the CREDENTIALSTORELOCATION
parameter in the GLOBALS file.
2. Create the credential store.
GGSCI> ADD CREDENTIALSTORE
3. Add each set of credentials to the credential store.
GGSCI> ALTER CREDENTIALSTORE ADD USER oragg, PASSWORD xxxx ALIAS oggalias
Copy the Wallet From the Source System to the On-Premise System
See 12c Adminstrative Guide
Create CMDSEC file in the OGG Install Directory
CMDSEC – blocks all commands to all Operating System users except the OGG User. Lock access to this file
(chmod go-r CMDSEC).
--Command Object Group User Access Allowed?
* * oinstall oragg YES
* * * * NO
On-Premise MGR Parameter File
PORT 7801
DYNAMICPORTLIST 7802-7803
PURGEOLDEXTRACTS ./rt* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
DOWNREPORTMINUTES 15
DOWNCRITICAL
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
ACCESSRULE, PROG *, IPADDR <On-Premise IP Address>, ALLOW
ACCESSRULE, PROG *, IPADDR *, DENY
Note: The local system IPADDR may be the first entry of the loopback (127.0.0.1) or localhost in the /etc/hosts file.
The MGR.rpt file will list what IP Address is trying to initiate the command.
On-Premise Replicat Parameter File
REPLICAT RONPREM
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/ronprem.dsc, APPEND
SETENV (NLS_LANG = ‘AMERICAN_AMERICA.AL32UTF8’)
SETENV (ORACLE_SID= ‘<ORACLE_SID>’)
--- Use Alias to login
USERIDALIAS oggalias
--- Replicat Automatically Decrypts Trail Data
--- DECRYPTTRAIL not required
26 | REPLICATION BETWEEN CLOUD AND ON-PREMISE ENVIRONMENTS WITH ORACLE GOLDENGATE 12C
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 01:00 ON SUNDAY
DISCARDROLLOVER AT 01:00 ON SUNDAY
--- DDL Parameters
DDL INCLUDE ALL
DDLOPTIONS REPORT
MAP APPS.*, TARGET APPS.*;
Syntax to Create On-Premise Groups and Trail Files
GGSCI> DBLOGIN USERID oragg, PASSWORD xxxx
GGSCI> ADD REPLICAT RONPREM, INTEGRATED, EXTTRAIL ./dirdat/rt
GGSCI> ADD EXTRACT PASSEXT, RMTHOST <Cloud System hostname/IP address>, MGRPORT
<Cloud mgr port>
Start of OGG Processes
The manager process on all servers should be started.
GGSCI> start MGR
Start Passive Extract Process from On-Premise Server
GGSCI (On-Premise Server)> START EXTRACT PASSEXT
Once the On-Premise Target system has been instantiated, the Delivery process can be started on the On-Premise
Server. For the documented best practice of instantiating from an Oracle source, please refer to Oracle
GoldenGate Best Practices: Instantiation from an Oracle Source Database (Doc ID 1276058.1)
Start Delivery Process at On-Premise Server
GGSCI (On-Premise Server)> START REPLICAT RONPREM, ATCSN|AFTERCSN <SCN>
Where to Go for More Information
Hopefully, this white paper has provided a quick overview of what options you can utilize to replicate data from the
cloud to on-premise. Undoubtedly, you will eventually fine-tune this process in your own environment.
Reference the Oracle Database 12.1 Documentation for additional information on the Oracle 12.1 RDBMS.
Reference the Oracle GoldenGate 12c Reference Guide and the Oracle GoldenGate 12c Administration Guide for
additional information on:
Extract Parameters for Windows and Unix
Replicat Parameters for Windows and Unix
Passive Extract for Windows and Unix
Extract Management Considerations
Replicat Management Considerations
Oracle Corporation, World Headquarters
500 Oracle Parkway
Redwood Shores, CA 94065, USA
Worldwide Inquiries
Phone: +1.650.506.7000
Fax: +1.650.506.7200
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only, and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document, and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group. 0415 OGG 12c Tutorial for Oracle to Oracle (12c Multitenant Container Database) April 2015 Author: Tracy West Contributing Authors:
C O N N E C T W I T H U S
blogs.oracle.com/oracle
facebook.com/oracle
twitter.com/oracle
oracle.com
A-Team Chronicles ateam-oracle.com