The Oracle GoldenGate software package delivers low-impact, real-time data integration and transactional data replication across heterogeneous systems for continuous availability, zero-downtime migration, and business intelligence.
Oracle GoldenGate 12c empowers organizations to capture, route, transform, and deliver transactional data between heterogeneous databases in real time with minimal overhead. In 12c, it is now available with new features for all major operating systems and databases.
In Part 1 of this series I covered:
- Expanded heterogeneous Support
- Multitenant Container Database (CDB) Support
- Oracle Universal Installer (OUI) Support
- Support for Public and Private Clouds
- Integrated Replicat
In this article I will cover:
- Coordinated Replicat
- New 32K VARCHAR2 Support
- High Availability (HA) enhancements
- Support for Other Oracle products
- Improvements to feature Functionality
Oracle GoldenGate 12c now integrates with the Oracle Credential store and Oracle Wallet to store and retrieve encrypted usernames and passwords. User can create an alias which is associated with actual usernames and passwords. Prior to 12c, username and password information was stored in lightly encrypted or plan text format.
User can create the alias as shown below:
GCSCI> alter CREDENTIALSTORE add user GGADM password xxxx alias GGADMALIAS
When you install Oracle GoldenGate for the first time, the default credential store will be created. If you don’t use the alias then then it defaults to the USERNAME. You can create multiple entries with different alias by using ADD USER with ALIAS option.
Oracle GoldenGate simplifies security using DBMS_GOLDENGATE_AUTH package. This package provides subprograms for granting and revoking privileges.
GRANT_ADMIN_PRIVILEGE – Grant the Admin privilege
REVOKE_ADMIN_PRIVILEGE – Revokes the Admin privilege
In order to GRANT or REVOKE the privilege, the user must be an administrative user.
The main advantage of using coordinated against classic mode, is that Replicat is multi-threaded and this helps to read trail files independently and apply transactions in parallel. Each thread associated with coordinated Replicat can handle the assigned workload such as filtering, mapping, conversion,SQL construction and error handling. The coordinator thread plays a key role in during the replication process, it manages the transactions across multiple threads to take care of dependencies across the threads such as DDL and Primary key updates, SQLEXEC Processing and Event Management Infrastcture (EMI) processing.
This method can be used for both Oracle and Non-Oracle databases and it eliminates the need to manage multiple parameter files.
Coordinated Replicat does not include dynamic allocation of threads and you need to indicate which tables belong to which threads. Also note that coordinate Replicat is not aware of foreign key constraints.
The main parameters controls this process are THREAD or THREADRANGE. You can use THREAD or THREADRANGE option in MAP statement to specify threads and maximum number of threads in a Replicat group.
New 32K VARCHAR2 Support
Oracle GoldenGate12c supports capture and Replicat of the new 32K VARCHAR2 data type, which is introduced in Oracle Database 12c. This new data type column is supported when extract is integrated capture mode and all modes of replicate are supported. But there are few limitations for 32K VARCHAR2 data type:
- Does not support 32k VARCHAR2 columns as part of key or unique index, nor column in a KEYCOLS
- Cannot be used as row identifiers
- Not supported as resolution columns in conflict resolution and detection (CDR) config
- Doesn’t have limit number of 32k columns, but each trail record has a length limit of 4MB for inline records.
Please check the below link for supported Oracle data types
High Availability (HA) enhancements
The main enhancements in HA are:
- Integration with Data Guard
- GoldenGate DownSteam Capture
Integration with Data Guard
Oracle GoldenGate 12c offers automated and transparent failover of components with the failover or switch over of DataGuard. Oracle GoldenGate Integrated capture is now integrated with Data Guard Fast-Start Failover and Oracle Clusterware.
If the user has any planned or unplanned failover then the processes will start on Standby during the role transmission and ensure no data loss.
Note that the license for Oracle GoldenGate includes a full use license for Oracle Active Data Guard, and XStream in the Oracle Database.
GoldenGate DownSteam Capture
GoldenGate Downstream capture offloads all GoldenGate capture processing to another system and the source system is continuously ship redo to a downstream site using Data Guard remote deployment (redo) log transport service. In the case of the source database going down, the GoldenGate process continues to run with this configuration, minimizing potential data loss.
For more information, see the Oracle Data Guard Concepts and Administration documentation.
Support for Other Oracle products
Oracle GoldenGate 12c provides integration with other Oracle products to increase capabilities.
- Oracle ATG Web commerce – Users using Oracle ATG Web commerce now offload operational reporting to a reporting server, and this helps to optimize reporting environment. Also, users can configure Active-Active replication using GoldenGate for bi-directional replication capabilities, this configuration helps business continuity in a cost effective way.
For more information on Oracle ATG Web commerce, please visit http://www.oracle.com/us/products/applications/commerce/atg/index.html
- Coherence Applications – If database is involved with third-part updates then it may cause coherence applications to work with stale or out-of-date data. Oracle GoldenGate 12c offers “HotCache”, with this we can monitor the database and pushing changes into coherence cache. This helps to improve application data consistency.
For more information on Oracle Coherence, please visit http://www.oracle.com/technetwork/middleware/coherence/overview/index-087514.html
- E-business Suite – Users using E-Business suite can use Oracle GoldenGate to migrate their environments with minimal disruption to operations through Oracle Advanced Customer Support Services (ACS).
For more information on Oracle Advanced customer support and E-Business suite, please visit
- Oracle Business Intelligence (BI) – When extracting data from OLTP systems contention can arise due to significant data changes, to relieve this contention we can implement Source dependent data store in the same database. Note that the source dependent schema (SDS) is a different schema in the same database, which contains the extracted data from an OLTP schema.
For more information on Oracle Business Intelligence, please visit http://www.oracle.com/us/solutions/business-analytics/business-intelligence/overview/index.html
Improvements to feature functionality
Oracle GoldenGate also provides improvements to existing feature functionality such as:
- Oracle GoldenGate Monitor – The new release offers capabilities to control GoldenGate’s Capture and Delivery process and ability to edit configuration files. It offers a more secure single sign -on, a variety of new metrics that provide timely and comprehensive view into the health of GoldenGate solutions, and support for monitoring GoldenGate instances running on DB2 for Z/OS.
- Automatic Discard file Generation – In prior versions users need to set explicitly, but it is standard in new Oracle GoldenGate 12c release. This functionality is helpful in problem diagnostics and conflict management.
- WildCard functionality – Now you can filter by schema wildcards, catalog wildcards and also new parameters introduced such as CATALOGEXCLUDE, SOURCECATALOG, and SCHEMAEXCLUDE.
- Character set – Oracle GoldenGate 12c offers automatic character set support by eliminating the need to set NLS_LANG parameter for replicate process.
- SHOWSYNTAX – In prior versions users need to use extra parameters along with SHOWSYNTAX, but in new Oracle GoldenGate 12c the requirements have been reduced. Users can start an interactive session where you can view each Replicat SQL statements before it is applied.
SHOWSYNTAX is not supported for coordinated Replicate group. When SHOWSYNTAX is running BATCHSQL processing will be suspended. BATCHSQL mode will be resumed when Replicat is restarted without SHOWSYNTAX.
SHOWSYNTAX [APPLY | NOAPPLY] [INCLUDELOB [max_bytes | ALL]]
APPLY | NOAPPLY – This parameter controls whether or not Replicat applies the data displayed with SHOWSYNTAX. The default value is
INCLUDELOB – This includes LOB, XML, and UDT data in the output
Max_bytes – Specifies the maximum length of LOB,XML or DUT data that will be displayed.
ALL – Display LOB data in its entirety.
- Data type support – Initial load remote task is now supports all data types including CLOB, BLOB, NCLOB, LONG, UDT and XML.
Key Benefits of using Oracle GoldenGate
- Reduce risk, ensure business continuity – GoldenGate offers failback options and minimizes data loss, downtime risk during upgrade, migrate, and maintenance activities. It moves data with extreme reliability and transactional integrity and helps companies achieve business continuity.
- Cut Cost and improve efficiencies – GoldenGate high performance bidirectional data replication enables to eliminate downtime for planned unplanned outages, which can be cost prohibitive. The low overhead nature of the solution enables better use of existing hardware and offloads resource intensive operations to a lower cost Replica.
- Improve business operations – GoldenGate enables uninterrupted operations during system migrations with zero downtime. It enables customer satisfaction maintaining SLA for availability and companies can have better business insight with up-to-date data.
- https://docs.oracle.com/goldengate/1212/gg-winux/index.html>Oracle GoldenGate complete Documentation
- Oracle GoldenGate Best Practices: Extracting from Oracle Archive log files (ALO Mode) (Doc ID 1358342.1)
- https://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/wu_performance.htm>Tuning the Performance of Oracle GoldenGate
- Oracle GoldenGate Best Practice – Configuring Downstream Integrated Extract v1.4 (Doc ID 1485620.1)
- Oracle GoldenGate Performance Tuning for Oracle Databases Integration (PDF)
Oracle GoldenGate 12c offers most robust and flexible data replication with all exciting new features and improvements. It provides continuous availability, disaster tolerance and real-time data integration solutions that enable the management and movement of transactional data across the enterprise.