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.

  • Data Guard is an Oracle specific technology while GoldenGate support heterogeneous database systems including all the major RDBMS as DB2,Sybase, MySQL .
  • Data Guard supports active-passive replication. One of the database is the primary database and the other one is in an inactive Data Guard mode.
  • GoldenGate supports an active-active replication mode and allows both systems to work simultaneously while maintaining the data integrity.
  • GoldenGate allows transformation of the data, with conflict management while it is being replicated between both database systems.
  • GoldenGate allows replication across platform. Data can be extracted from a Unix platform and replicated to an Oracle database running on platform Windows.
  • GoldenGate has many case of utilization. The use of flat files for data transportation and the support of heterogeneous systems makes the technology so very interesting

 

GoldenGate is the best solution:
  • Replicate one or many tables to a read-write database.
  • Replicate and transform tables.
  • Bi-directional replication.
  • 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.

Data Synchronization: You want to Synchronize data across different departments of the company (different databases).

Furthermore the GoldenGate software supports extraction from the legacy systems and does not have any limitation on the distance either. Additionally, the trail files generated by GoldenGate capture can also be compressed and encrypted.

Oracle Data Guard

Used for

  • High availability
  • Data protection
  • Disaster recovery
  • Primary database workload reduction

Database roles

In a data guard environment, one database is a primary database, the others are standby databases. These roles are mutually exclusive.
The role of a database can be queried in the column database_role in v$database.

Primary database

The primary db is the »real« production database.
The primary db must run in archive log mode and with forced logging.

Standby databases

Standby databases are (transactionally consistent) copies of the primary database.
Up to 30 standby databases are supported.

Physical and logical standby databases

A standby DB is either physical or logical standby database. (A standby database can also become a snapshot standby database).
A physical database is an exact byte-wise copy of the primary database.
A logical database has the same data as the primary database, but is not necessarily stored in the same db blocks.
The physical database is kept synchronized with redo apply, the logical database with SQL apply.

Cascading standby database

A physical standby database might be configured to receive redo from a primary database and relay it to a another standby database.
In this case, the relaying database is  called a cascading standby database. The receiving standby databases is also known as a terminal destination.
With such a configuration, the load on the primary database can be reduced.

SQL*Plus

Some SQL*Plus statements come with the standby keyword which indicates that the statement should be applied on the standby databases.

Role of redo

Data Guard basically works as follows:

  • Archived redo log files from the production DB are copied to the standby DB.
  • They are then registered with the standby DB.
  • Finally, using media recovery, the redo is used to roll forward the standby database.

Preventing physical errors

A standby database validates the redo it receives from the primary DB.
Thus, physical errors (such as data corruption) on the primary DB are not propagated to standby databases.

Changing roles of databases

The role (primary vs standby) of a database can be changed by either a

  • switch over or
  • a fail-over
A switch over is a planned role transition. In a switch over, the primary database becomes a standby database while (one of) the standby databases becomes the primary database.
A switch over is performed in two steps:
1) the primary DB becomes a standby DB
2) a standby DB becomes a primary DB.
A fail over occurs when the primary database becomes unavailable. In this case, one of the standby databases becomes the new primary database.

RAC

The primary db and the standby databases can be run as single instance or real application clusters(RAC).

Active Data Guard

Active data guard offers the same features as data guard plus that it standby databases are opened read only while the copying of data takes place
Thus, it allows to query the standby databases without impacting the performance of the production database (for example to create resource-intensive reports).
The standbase database is called physical standby database.
The buzzword is real time query.
Active data guard must be licenced seperatly.

Data protection modes

Data Guard runs in one of three data protection modes. This allows to balance data availability against system performance requirements.

These protection modes are:

  • Maximum performance (default)
  • Maximum protection
  • Maximum availability

Automatic gap detection

If connectivity is lost between the primary and one or more standby databases
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle Goldengate: Tune TCPBUFSIZE and TCPFLUSHBYTES parameters.

Tune TCPBUFSIZE and TCPFLUSHBYTES parameters:

The two RMTHOST parameters, TCPBUFSIZE and TCPFLUSHBYTES, are very useful for increasing the buffer sizes and network packets sent by Data Pump over the network from the source to the target system. This is especially beneficial for high latency networks.
It is recommended that you set these parameters to an initial value of 1MB (1,048,576 bytes) or the calculated value, whichever is larger.

Use the ping command to obtain the average round trip time (RTT).
For example: % ping ggtarget.doyensys.com Pinging ggtarget.doyensys.com [192.168.1.171] with 32 bytes of data: Reply from 192.168.1.171: bytes=32 time=31ms TTL=56 Reply from 192.168.1.117: bytes=32 time=61ms TTL=56 Reply from 192.168.1.117: bytes=32 time=32ms TTL=56 Reply from 192.168.1.117: bytes=32 time=34ms TTL=56 Ping statistics for 192.168.1.117: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 31ms, Maximum = 61ms, Average = 45ms

Calculate the bandwidth-delay product (BDP):
For example, if the network between the source and target databases is 125 megabits per second (Mbits) and the latency is 45ms the calculation would be as follows: BDP = (125,000,000 / 8) * 0.045 = 703125bytes

Multiply the result by 4 to determine 4xBDP:
For example: 4xBDP = 703125 x 4 = 2812500
In this example, because the result is more than 1MB, set the values of TCPBUFSIZE and TCPFLUSHBYTES to 2812500
The parameters are set in the Data Pump parameter file. For example: RMTHOST ggtarget, MGRPORT 7810, TCPBUFSIZE 2812500, TCPFLUSHBYTES 2812500 

Note: The maximum socket buffer size for non-Windows systems is usually limited by default.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba