What Is Oracle Golden Gate

Table Level Supplemental Logging

Oracle GoldenGate uses supplemental log groups to configure supplemental logging at table level.

Supplemental logging is enabled using the ADD TRANDATA command in GGSCI.

$ ggsciGGSCI  1>  DBLOGIN USERID us01 PASSWORD us01GGSCI  2>  ADD TRANDATA t22Logging of supplemental redo data enabled for table US01.T22.

When the above statement is executed, internally the table is locked using:

LOCK TABLE "US01"."T22" IN SHARE MODE NOWAIT

The following DDL statement is executed to create a supplemental log group for the specified table.

ALTER TABLE "US01"."T22" ADD SUPPLEMENTAL LOG GROUP "GGS_76111"  ALWAYS  /* GOLDENGATE_DDL_REPLICATION */

In this example, the table T22 has a single primary key column C1 which is included in the supplemental logging.

The current status of supplemental logging for a table can be verified using the INFO TRANDATA command in GGSCI. For example:

$ ggsciGGSCI  3>  INFO TRANDATA t22Logging of supplemental redo log data is enabled for table US01.T22.Columns supplementally logged for table US01.T22: C1.

Supplemental logging is disabled using the DELETE TRANDATA command in GGSCI.

$ ggsciGGSCI  1>  DBLOGIN USERID us01 PASSWORD us01GGSCI  2>  DELETE TRANDATA us01.t22Logging of supplemental redo data disabled for table US01.T22.

Note that, unlike the ADD TRANDATA command, the schema name MUST be specified for the DELETE TRANDATA command. This appears to be a bug.

The following DDL statement is executed to create a supplemental log group for the specified table.

Foreign Key Supplemental Logging

This example demonstrates foreign key supplemental logging which is enabled as follows:

ALTER TABLE student ADD SUPPLEMENTAL LOG DATA  COLUMNS 

The following statement updates the foreign key SUBJECT column

UPDATE student SET subject = 'Chemistry'WHERE student_key = 1003 

The redo log dump for the above change is as follows:

The above redo record consists of four changes:

The above statement will generate supplemental data as follows:

Header   00021C01 00060001 00000006 00001000 00000000Bytes 00-01: Number of columns Bytes 02-03: UnknownBytes 04-05: Column number of first column modified Bytes 06-07: UnknownBytes 08-0B: Column number of first column modified Bytes 0C-0D: UnknownBytes 0E-0F: UnknownBytes 10-13: Unknown

The column number is either SEGCOL# or INTCOL# in COL$ for the first column updated in the UPDATE statement. It is definitely not COL#.

Column List Header   0005 0007Bytes 00-01: INTCOL# of column 1 Bytes 02-03: INTCOL# of column 2 Column Size List   0009 0003Bytes 00-01: Length of column 1 Bytes 02-03: Length of column 2 Column 1 Value   43 61 6D 62 72 69 64 67 65col  4:   43 61 6d 62 72 69 64 67 65       # University = Cambridge Column 2 Value   C2 15 0E col  6:   c2 15 0e       # Entry Year = 2013

Which Is The Goldengate Command For Oracle Lag

See Oracle GoldenGate Commands . Use the LAGCRITICALSECONDS, LAGCRITICALMINUTES, or LAGCRITICALHOURS parameter to specify a lag threshold that is considered critical, and to force a warning message to the error log when the threshold is reached. This parameter affects Extract and Replicat processes on the local system.

You May Like: Gold Rush Season 10 Release Date

What If Table Doesnt Have Primary Key/unique Key

If the table does not have either a primary key or a unique key constraint defined, then ADD TRANDATA will automatically add supplemental logging based on sequence mentioned above. Hence in absence of both Supplemental logging will be enabled on all columns of a table.

Please note it will increase the size of the redo logs and the trail files as well. More importantly, if you are applying a change on the target database using all the columns as the key then the DML will not be efficient and will cause your REPLICAT to lag behind.

You might need to sit with the application team to find out what key could be defined for that table.

You can enable supplemental logging based on columns defined by Application team on a particular object: You can use the following syntax to ensure only those columns are used to add supplemental logging.

ADD TRANDATA < table name> , COLS , NOKEY

Please note the usage of NOKEY usually if you dont use NOKEY and you try using only COLS then you will get an error saying that the columns you define in COLS are already used because by default ADD TRANDATA adds supplemental logging for all the columns if there is no primary key defined as stated earlier.

Also, ensure the target database has indexes defined on the columns in question.

Reference:

Setting Up The Replicat Process On Hrprd1

Your Oracle 11g database, HRPRD1, is also on the same server. Since it is a 11g database version, you need to use the Oracle GoldenGate for Oracle 11g software which you had unzipped at the start of this article into the C:\OGG11G directory.

For the Oracle 11g database home, set the environment variables as follows:

 set ORACLE_HOME=C:\app\havipori\product\11.1.0\db_1 set ORACLE_SID=HRPRD1 set PATH=%ORACLE_HOME%\bin %PATH% set LD_LIBRARY_PATH=C:\OGG11G  %ORACLE_HOME%\LIB  %ORACLE_HOME%\jdbc\lib set CLASSPATH= C:\Program Files\Java\jre6\lib\ext\QTJava.zip %ORACLE_HOME%\jdbc\lib

You follow similar steps to the Oracle GoldenGate for Oracle 10g installation and create a Manager service named OracleGGSMGR2 and start it. You create the working subdirectories and the mgr.prm file. These steps are shown below:

 C:\OGG11G> ggsci  GGSCI  1>  EDIT PARAMS ./GLOBALS

Type in MGRSERVNAME OracleGGSMGR2 in this file and save it.

Type in the file the following lines and save it. Note the different port number used and that the Manager is now logging on to HRPRD1.

You can now start the Manager. The next step is to use DBLOGIN to log in to the Oracle 11g database and create the checkpoint table in this database.

Now, you are ready to create the second Replicat group in this database HRPRD1. Note that you are using the exact same trail as was used before by the database HRPRD3.

In this file, enter the following lines:

Now start the Replicat group and check the status:

Read Also: How Much Does A Brick Of Gold Cost

Exploring Oracle Goldengate Alternatives

Data replication, specifically, data replication using log-based Change Data Capture, has been a data integration option for organizations for over a decade. It is a solution that serves several traditional and modern use cases: distributing load and offloading reporting, consolidating data or Data Warehouse), feeding a data lake, high availability , or delivering data for streaming analytics .

What is Oracle GoldenGate?

Oracle acquired GoldenGate Software in 2009. GoldenGate Softwares main product at the time was called GoldenGate and delivered heterogeneous, real-time data replication. Oracle now calls this product Oracle GoldenGate, available either as a separately-licensed database option for the Oracle Database or as a separately-licensed product for a non-Oracle Database.

Since the acquisition, a lot of effort has been put into embedding GoldenGate into the Oracle Database. With Oracle Database 19c, Oracle GoldenGate supersedes and displaces Oracle Streams, replacing a data replication capability that used to be included with the Enterprise Edition of the Oracle Database.

For non-Oracle Databases, Oracle tried its best to maintain support but had to make changes for Microsoft SQL Server and the Teradata Database , increasing the license fees for organizations wanting to explore modern cloud platforms such as AWS S3 or Snowflake.

Data Replication Requirements

Considering this list of requirements, why would you consider an Oracle GoldenGate alternative?

Bringing Goldengate Data Integration Service To The Cloud Again

The new service isn’t the first time that Oracle GoldenGate has been available in the cloud.

Pollock noted that GoldenGate had previously been available on Oracle’s first-generation cloud service. OCI is Oracle’s second-generation cloud and provides a consumption-based model for service consumption as well as increased automation.

This is one of many steps that Oracle is taking to make their technology central in the operations of their customers. Their first priority is to retain Oracle Database customers and move them to OCI. Carl OlofsonResearch vice president of data management software, IDC

“What that initial Gen 1 cloud service lacked was the fully managed capability,” Pollock said. “So, we do actually have a track record with GoldenGate in the cloud, but it didn’t bring this level of automation.”

Oracle OCI also brings with it a microservices-based approach that uses the Kubernetes container orchestration system at the infrastructure layer. Pollock noted that a key benefit of having the microservices approach is that it helps further enable multi-cloud operations for GoldenGate. Pollock noted that GoldenGate is already certified and supported on non-Oracle clouds, including Amazon, Microsoft Azure and Google, and can also run on premises.

“What happens when you begin to have these different Golden Gate installs talking to each other, is it ends up creating this kind of data mesh of connected services,” Pollock said.

Related Resources

Don’t Miss: Kay 19.99 Ring

When Do You Use Oracle Goldengate

Oracle GoldenGate meets almost any data movement requirements you might have. Some of the most common use cases are described in this section.

You can use Oracle GoldenGate to meet the following business requirements:

Business Continuity and High Availability

Business Continuity is the ability of an enterprise to provide its functions and services without any lapse in its operations. High Availability is the highest possible level of fault tolerance. To achieve business continuity, systems are designed with multiple servers, multiple storage, and multiple data centers to provide high enough availability to support the true continuity of the business. To establish and maintain such an environment, data needs to be moved between these multiple servers and data centers, which is easily done using Oracle GoldenGate.

Initial Load and Database Migration

Initial load is a process of extracting data records from a source database and loading those records onto a target database. Initial load is a data migration process that is performed only once. Oracle GoldenGate allows you to perform initial load data migrations without taking your systems offline.

Data Integration

Data integration involves combining data from several disparate sources, which are stored using various technologies, and provide a unified view of the data. Oracle GoldenGate provides real-time data integration.

Moving From Data Replication To Real

Jeff Pollock, vice president of product management at Oracle, noted that GoldenGate has undergone significant improvement over the past decade and has an expanded focus. He noted that when GoldenGate entered the Oracle portfolio the emphasis was on transaction replication and operational continuity applications.

“GoldenGate is no longer just that kind of a vanilla replication solution that you would have been aware of in the 2010 time frame,” Pollock said. “GoldenGate has expanded pretty dramatically in the last 10 years since being a part of the Oracle ecosystem as we’ve invested in support for a whole range of non-Oracle source technologies.”

Over the last five years in particular, Pollock said, GoldenGate has increasingly focused on real-time data ingestion use cases, as well as on event streaming integration for data pipelines. He emphasized that GoldenGate in 2021 has hundreds of different combinations of data stores platform and technologies that it supports for data integration.

Also Check: Kay Jewelers Ring 19.99

What Is Rba In Goldengate

. Also question is, what is GoldenGate checkpoint?

Checkpointing is a technique that Oracle GoldenGate uses to add fault tolerance into the replication process. GoldenGate extract, data pump, and replicat processes record their read and write positions and time along the data flow. These positions are called Checkpoints.

Secondly, what is time since checkpoint in GoldenGate? Time Since CheckpointAnd it starts processing all the transactions after that checkpoint. You can see the increase in the Time Since Checkpoint, when extract or replicat is processing a long running transaction. By using the SEND EXTRACT or SEND REPLICAT command you can know the exact status of the processes.

In this way, what is RBA in Oracle?

Redo Byte Address An RBA points to a specific location in a particular redo thread. It. is ten bytes long and has three components: log sequence number, block number within log, and byte number within block.

How do I read a GoldenGate trail file?

To open a trail file and read it’s content, specify the trail file at the logdump prompt. Trail files are usually found in the GoldenGate dirdat directory. You can also determine the current trail file directory/name by running the INFO process_name command at the ggsci prompt.

Integrated Replicat Slow Due To Query Onsys: Dba: Apply: Cdr: Info

Oracle GoldenGate Conflict Detection and Resolution provides basic conflict resolution routines. Forexample, CDR can resolve a unique conflict for an INSERT statement.

When CDR resolves a collision, it can insert records into the exception table_DBA_APPLY_CDR_INFO temporarily. Integrated REPLICAT deletes these recordslater. In a rare scenario, the integrated REPLICAT can process a large number of collisions, buta new integrated REPLICAT does not replace it. Instead of being removed, the existing rows in_DBA_APPLY_CDR_INFO are orphaned. Any new integrated REPLICAT processes slowdown because they are querying orphaned rows in _DBA_APPLY_CDR_INFO.

To remove all rows from _DBA_APPLY_CDR_INFO, use the Amazon RDS procedurerdsadmin_util.truncate_apply$_cdr_info. This procedure is released as part of the October2020 release and patch update. The procedure is available in the following database versions:

Recommended Reading: How Much Is A Gold Bar Worth In Rdr2

Enabling Supplemental Logging At Db Level:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

ORSQL> alter database add supplemental log data columns

To ensure the same has been enabled query V$Database view.

By default, only changed column is logged into transaction logs for any operations. Normally, this means that primary key columns are not logged during an update/delete operation. However, Replicat requires the extra column in order to apply the update/delete on the target system. The ADD TRANDATA command in GGSCI is used to cause Oracle to log extra column in logs for all transactions on a table.

For enabling Table level Supplemental logging, Database level supplemental logging is required. Consider DB level supplemental logging as a gateway to enable supplemental logging at the table level.

Running The Oracle Goldengate Replicat Utility

The REPLICAT utility is used to “push” transaction information in the trail files to thetarget database.

The following tasks enable and start the REPLICAT utility:

add checkpointtable < user> .gg_checkpoint  

Recommended Reading: 18 K Gold Worth

Why Does Golden Gate Lag At Chkpt Is High

The issue: One of the Oracle GG EXTRACT processes was in ABENDED status. After fixing the underlying problem , the value for Lag at Chkpt is high. What does it mean? Each process has its own checkpoint file. Whenever a process sees a commit in the transaction, a checkpoint is made in the checkpoint file.

Enabling Supplemental Logging At Table Level:

GGSCI> dblogin userid c##gguser@pdb1,password *****

Note: For enabling supplemental logging at table level dblogin to the pluggable database is required if database architecture is Multitenant.

GGSCI> ADD TRANDATA pdb1.ggtraining1.dept1The above command at GGSCI level is equivalent at the database level as below depending on supplemental logging has to be enabled on primary key/Unique key for all columns

SQL> ALTER TABLE ggtraining1.DEPT1 ADD SUPPLEMENTAL LOG DATA columns

ADD TRANDATA automatically adds supplemental logging for the table based on the tables primary key or if thats missing, using any unique key constraints defined for that table.

Below is the order of columns in which Supplemental logging is enabled at table level:

Once supplemental logging is enabled, along with a column which has been updated/changed an extra column will be logged in to redo logs so that a row could be uniquely identified. Here it could be either of the one mentioned as above.

If you enable only table-level supplemental logging without database level then Oracle will not capture all changes.

Recommended Reading: Weight Of A Gold Brick

Oracle Goldengate Vs Oracle Data Guard

The configuration of Oracle GoldenGate / Data Guard has the purpose of synchronizing data between two or more systems .

The basic features of the two products may look similar but takes GoldenGate uses replication while dataguard not.

Oracle Data Guard and GoldenGate involving at least two systems where transactional data from one database is required to be moved to another database. The purpose of the replication of data can be disaster recovery, migration of data or preparation of a secondary system.

Data Guard is best for disaster recovery and data protection problems, GoldenGate is a more flexible heterogeneous replication mechanism and is also able to transform the data while it is being replicated.

GoldenGate is the best solution:

• Replicate one or many tables to a read-write database.
• Replicate and transform tables.
• Zero or near-zero downtime upgrade.
• Heterogeneous replication

GoldenGate replication.

Zero Downtime Upgrade/Migration: You may want to setup an exact replica of your database for upgrade or migration, keep the data in sync till the last moment.

High Availability: Your applications cannot afford to have downtime so you want to have two databases up and running at the same time to support those applications.

Query or Reporting: You want to offload the reporting or long query operations from your production database.

Real Time BI: For your data warehouse requirements.

Oracle Goldengate Appears To Be Properlyconfigured But Replication Is Not Working

For pre-existing tables, Oracle GoldenGate must be told which SCN it should work from. Take the following steps tofix this issue:

ALTER EXTRACT < extract process name>  SCN 223274start < extract process name> 

start < replicat process name>  atcsn 223274 

Recommended Reading: How Pure Is 24k Gold

Why Do You Need Oracle Goldengate

Enterprise data is typically distributed across the enterprise in heterogeneous databases. To get data between different data sources, you can use Oracle GoldenGate to load, distribute, and filter transactions within your enterprise in real-time and enable migrations between different databases in near zero-downtime.

To do this, you need a means to effectively move data from one system to another in real-time and with zero-downtime. Oracle GoldenGate is Oracles solution to replicate and integrate data.

Oracle GoldenGate has the following key features:

• Data movement is in real-time, reducing latency.

• Only committed transactions are moved, enabling consistency and improving performance.

• Different versions and releases of Oracle Database are supported along with a wide range of heterogeneous databases running on a variety of operating systems. You can replicate data from an Oracle Database to a different heterogeneous database.

• Simple architecture and easy configuration.

• High performance with minimal overhead on the underlying databases and infrastructure.

Parent topic:Introduction to Oracle GoldenGate