Oracle GoldenGate Interview Questions
What type of Topology does Goldengate support?
GoldenGate supports the following topologies. More details can be found
What are the main components of the Goldengate replication?
The replication configuration consists of the following processes.
What database does GoldenGate support for replication?
Microsoft SQL Server
What transaction types does Goldengate support for Replication?
Goldengate supports both DML and DDL Replication from the source to target.
What are the supplemental logging pre-requisites?
The following supplemental logging is required.
Database supplemental logging
Object level logging
Why is Supplemental logging required for Replication?
When a transaction transaction is committed on the source database, only new data is written to the Redo log. However for Oracle to apply these transactions on the destination database, the before image key values are required to identify the effected rows. This data is also placed in the trail file and used to identify the rows on the destination, using the key value the transactions are executed against them.
List important considerations for bi-directional replication?
The customer should consider the following points in an active-active replication environment.
Primary Key: Helps to identify conflicts and Resolve them.
Sequences: Are not supported. The work around is use to use odd/even, range or concatenate sequences.
Triggers: These should be disabled or suppressed to avoid using uniqueness issue
Data Looping: This can easy avoided using OGG itself
LAG: This should be minimized. If a customer says that there will not be any LAG due to network or huge load, then we don’t need to deploy CRDs. But this is not the case always as there would be some LAG and these can cause Conflicts.
CDR (Conflict Detection & Resolution): OGG has built in CDRs for all kind of DMLs that can be used to detect and resolve them.
Packaged Application: These are not supported as it may contain data types which are not support by OGG or it might not allow the application modification to work with OGG.
What is data pump process in Goldengate?
The Data Pump (not to be confused with the Oracle Export Import Data Pump) is an optional secondary Extract group that is created on the source system. When Data Pump is not used, the Extract process writes to a remote trail that is located on the target system using TCP/IP. When Data Pump is configured, the Extract process writes to a local trail and from here Data Pump will read the trail and write the data over the network to the remote trail located on the target system.
The advantages of this be it protects against a network failure as in the absence of a storage device on the local system, the Extract process writes data into memory before the same is sent over the network. Any failures in the network could then cause the Extract process to abort (abend). Also, if we are doing any complex data transformation or filtering, the same can be performed by the Data Pump. It will also be useful when we are consolidating data from several sources into one central target where data pump on each individual source system can write to one common trail file on the target.
Where can filtering of data for a column be configured?
Filtering of the columns of a table can be set at the Extract, Pump or Replicat level.
Desired to gain proficiency on Oracle GoldenGate? Explore the blog post on
Oracle GoldenGate Training to become a pro in Oracle GoldenGate.
Is it a requirement to configure a PUMP extract process in OGG replication?
A PUMP extract is an option, but it is highly recommended to use this to safe guard against network failures. Normally it is configured when you are setting up OGG replication across the network.
What are the differences between the Classic and integrated Capture?
The Classic Capture mode is the traditional Extract process that accesses the database redo logs (optionally archive logs) to capture the DML changes occurring on the objects specified in the parameter files.
At the OS level, the GoldenGate user must be a part of the same database group which owns the database redo logs.
This capture mode is available for other RDBMS as well.
There are some data types that are not supported in Classic Capture mode.
Classic capture can’t read data from the compressed tables/tablespaces.
Integrated Capture (IC):
In the Integrated Capture mode, GoldenGate works directly with the database log mining server to receive the data changes in the form of logical change records (LCRs).
IC mode does not require any special setup for the databases using ASM, transparent data encryption, or Oracle RAC.
This feature is only available for oracle databases in Version 220.127.116.11 or higher.
It also supports various object types which were previously not supported by Classic Capture.
This Capture mode supports extracting data from source databases using compression.
Integrated Capture can be configured in an online or downstream mode.
List the minimum parameters that can be used to create the extract process?
The following are the minimium required parameters which must be defined in the extract parameter file.
What are macros?
Macro is an easier way to build your parameter file. Once a macro is written it can be called from different parameter files. Common parameters like username/password and other parameters can be included in these macros. A macro can either be another parameter file or a library.
What is the command line utility in GoldenGate (or) what is ggsci?
Golden Gate Command Line Interface essential commands – GGSCI
GGSCI -- (Oracle) GoldenGate Software Command Interpreter
Where can macros be invoked?
The macros can be called from the following parameter files.
How is a macro defined?
A macro statement consists of the following.
Name of the Macro
PARAMS (#param1, #param2, …)
< macro_body >
What type of Encryption is supported in Goldengate?
Oracle Goldengate provides 3 types of Encryption.
Data Encryption using Blow fish.
What are the different password encrytion options available with OGG?
You can encrypt a password in OGG using
Blowfish algorithm and
Advance Encryption Standard (AES) algorithm
Is there a way to check the syntax of the commands in the parameter file without running the GoldenGate process?
Yes, you can place the SHOWSYNTAX parameter in the parameter file and try starting. If there is any error, you will see it.
How can you increase the maximum size of the read operation into the buffer that holds the results of the reads from the transaction log?
If you are using the Classical Extract you may use the TRANSLOGOPTION ASMBUFSIZE parameter to control, the read size for ASM Databases.
What information can you expect when there us data in the discard file?
When data is discarded, the discard file can contain:
Discard row details
Trail file number
What command can be used to switch writing the trail data to a new trail file?
You can use the following command to write the trail data to a new trail file.
SEND EXTRACT ext_name, ROLLOVER
How can you determine if the parameters for a process was recently changed?
Whenever a process is started, the parameters in the. prm file for the process is written to the process REPORT. You can look at the older process reports to view the parameters which were used to start up the process. By comparing the older and the current reports you can identify the changes in the parameters.
What happens if RVWR cannot write to disk?
It depends on the context where the write error occurs:
If there’s a Guaranteed Restore Point, the database crashes to ensure the restore point guarantee is not voided.
If there isn’t a Guaranteed Restore Point and it’s a primary database, the Flashback Mode will be automatically turned off for the database, which will have continued to operate normally.
If there isn’t a Guaranteed Restore Point and it’s a standby database, the database will hang until the cause of the write failure is fixed.
How to list restore points in RMAN?
In RMAN you can use the LIST RESTORE POINT [ALL|restore_point_name] command. If you use a recovery catalog you can use the view RC_RESTORE_POINT in the recovery catalog repository, or the command the V$RESTORE_POINT in the target database.
Can you see the progress of a FLASHBACK DATABASE operation?
Yes, you can. During a FLASHBACK DATABASE operation, you can query V$SESSION_LONGOPS from another session to see the progress of the flashback.
The FLASHBACK DATABASE operation has two distinct phases: the actual flashback and the media recovery that happens afterwards to bring the database to a consistent state.
While the actual flashback is running, you’ll see the following message in V$SESSION_LONGOPS, on Oracle 11gR2:
Flashback Database: Flashback Data Applied : 238 out of 282 Megabytes done
During the media recovery, the following messages will be seen:
Media Recovery: Redo Applied: 263 out of 0 Megabytes done
Media Recovery: Average Apply Rate: 1164 out of 0 KB/sec done
Media Recovery: Last Applied Redo: 626540 out of 0 SCN+Time done
Media Recovery: Elapsed Time: 232 out of 0 Seconds done
Media Recovery: Active Time: 116 out of 0 Seconds done
Media Recovery: Active Apply Rate: 1859 out of 0 KB/sec done
Media Recovery: Maximum Apply Rate: 1859 out of 0 KB/sec done
Media Recovery: Log Files: 15 out of 0 Files done
Media Recovery: Apply Time per Log: 7 out of 0 Seconds done
How should I set the database to improve Flashback performance?
Oracle’s recommendations are:
Use a fast file system for your flash recovery area, preferably without operating system file caching. It is recommended to use a file system that avoids operating system file caching, such as ASM.
Configure enough disk spindles for the file system that will hold the flash recovery area. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively.
If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance
For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.