DB-hub Technology Oracle GoldenGate Interview Questions

Oracle GoldenGate Interview Questions

1.Oracle GoldenGate Troubleshooting and Tuning

Refer to: GoldenGate Troubleshooting and Tuning

2.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.

4.Are OGG binaries supported on ASM Cluster File System (ACFS)?

Yes, you can install and configure OGG on ACFS.

5.Are OGG binaries supported on the Database File System (DBFS)? What files can be stored in DBFS?

No, OGG binaries are not supported on DBFS. You can however store parameter files, data files (trail files), and checkpoint files on DBFS.

6.What is the default location of the GLOBALS file?

A GLOBALS file is located under Oracle GoldenGate installation directory (OGG HOME)

7.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.

8.What are the differences between the Classic and integrated Capture?

Classic 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 11.2.0.3 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.

9.What DDL are not supported by GoldenGate?

The following DDL constructs are not supported by either capture method:

  • ALTER TABLE MOVE TABLESPACE.
  • ALTER DATABASE.
  • ALTER SYSTEM.
  • DDL on nested tables.
  • DDL on a standby database.

10.What are the Limitations of Support for Sequences?

Oracle GoldenGate supports the replication of sequence values in a uni-directional and active-passive high-availability configuration.
Oracle GoldenGate ensures that the target sequence values will always be higher than those of the source (or equal to them, if the cache is zero).

These limitations apply to integrated and classic capture modes.

  • Oracle GoldenGate does not support the replication of sequence values in an active-active bi-directional configuration.
  • The cache size and the increment interval of the source and target sequences must be identical. The cache can be any size, including 0 (NOCACHE).
  • The sequence can be set to cycle or not cycle, but the source and target databases must be set the same way.
  • Tables with default sequence columns are excluded from replication for Coordinated Extract.

11. What Data Types are not Supported?

Understanding What’s Supported
Oracle GoldenGate does not support the following data types.

  • ANYDATA fetch-based column
    If you want to capture from an Advanced Queue (AQ) object, do not use normal table replication for that AQ object. Instead use the Procedural Replication functionality, see Procedural Replication Process Overview.
    The ANYDATA support is limited to normal table objects, not AQ objects.

  • ANYDATASET

  • ANYTYPE
  • MLSLABEL
  • ORDDICOM
  • TIMEZONE_ABBR
  • URITYPE
  • UDT containing an unsupported Oracle data type

12.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.

  • EXTRACT NAME
  • USERID
  • EXTTRAIL
  • TABLE

13.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.

14.Where can macros be invoked?

The macros can be called from the following parameter files.

  • Manager
  • Extract
  • Replicat
  • Gobals

15.How is a macro defined?

A macro statement consists of the following.

  • Name of the Macro
  • Parameter list
  • Macro body

Sample:

MACRO #macro_name
PARAMS (#param1, #param2, …)
BEGIN
< macro_body >
END;

16.I want to configure multiple extracts to write to the same exttrail file? Is this possible?

Only one Extract process can write to one exttrail at a time. So you can’t configure multiple extracts to write to the same exttrail.

17.What type of Encryption is supported in Oracle Goldengate?

Encrypts the data in files, across data links, and across TCP/IP. Use any of the following:

  • Any Advanced Encryption Security (AES)Foot 1 cipher:
  • AES-128
  • AES-192
  • AES-256
  • BlowfishFoot 2 64bit

GoldenGate的安全选项主要有以下5个方面：

  • Encrypting Data with the Master Key and Wallet Method
    To use this method of data encryption, you create a master-key wallet and add a master key to the wallet. This method works as follows, depending on whether the data is encrypted in the trails or across TCP/IP:
    Each time Oracle GoldenGate creates a trail file, it generates a new encryption key automatically. This encryption key encrypts the trail contents. The master key encrypts the encryption key. This process of encrypting encryption keys is known as key wrap and is described in standard ANS X9.102 from American Standards Committee.
    To encrypt data across the network, Oracle GoldenGate generates a session key using a cryptographic function based on the master key.
    Oracle GoldenGate uses an auto-login wallet (file extension .sso), meaning that it is an obfuscated container that does not require human intervention to supply the necessary passwords.

  • Encrypting Data with the ENCKEYS Method
    To use this method of data encryption, you configure Oracle GoldenGate to generate an encryption key and store the key in a local ENCKEYS file. This method makes use of a permanent key that can only be changed by regenerating the algorithm according to the instructions in Populating an ENCKEYS File with Encryption Keys. The ENCKEYS file must be secured through the normal method of assigning file permissions in the operating system.
    This procedure generates an AES encryption key and provides instructions for storing it in the ENCKEYS file.

  • Encrypting a Password in a Command or Parameter File
    This method takes a clear-text password as input and produces an obfuscated password string and a lookup key, both of which can then be used in the command or parameter file. This encryption method supports all of the databases that require a login for an Oracle GoldenGate process to access the database.
    command:

ENCRYPT PASSWORD password algorithm ENCRYPTKEY {key_name | DEFAULT}

  • Configuring GGSCI Command Security
    You can establish command security for Oracle GoldenGate to control which users have access to which Oracle GoldenGate functions. For example, you can allow certain users to issue INFO and STATUS commands, while preventing their use of START and STOP commands. Security levels are defined by the operating system’s user groups.
    To implement security for Oracle GoldenGate commands, you create a CMDSEC file in the Oracle GoldenGate directory. Without this file, access to all Oracle GoldenGate commands is granted to all users.

  • Using Target System Connection Initiation
    When a target system resides inside a trusted intranet zone, initiating connections from the source system (the standard Oracle GoldenGate method) may violate security policies if the source system is in a less trusted zone. It also may violate security policies if a system in a less trusted zone contains information about the ports or IP address of a system in the trusted zone, such as that normally found in an Oracle GoldenGate Extract parameter file.
    In this kind of intranet configuration, you can use a passive-alias Extract configuration. Connections are initiated from the target system inside the trusted zone by an alias Extract group, which acts as an alias for a regular Extract group on the source system, known in this case as the passive Extract. Once a connection between the two systems is established, data is processed and transferred across the network by the passive Extract group in the usual way.

17.What is OGG Crendential Store?

OGG Crendential Store manages Encrypted Passwords and USERIDs that are used to interact with the local database and Associate them with an Alias. Instead of specifying actual USERID and Password in a command or a parameter file, you can use an alias. The Credential Store is implemented as an autologin wallet within the Oracle Credential Store Framework (CSF).

ADD CREDENTIALSTORE

By Default, Credential Store is located under “dircrd” directory.
If you want to specify a different location you can specify “CREDENTIALSTORELOCATION” parameter in GLOBALS file.
Example:

CREDENTIALSTORELOCATION /u01/app/oracle/OGG_PASSWD

add credentials to the credential store:

ALTER CREDENTIALSTORE ADD USER userid,
[PASSWORD password]
[ALIAS alias]
[DOMAIN domain]
Example: GGSCI> ALTER CREDENTIALSTORE ADD USER GGS@orcl, PASSWORD oracle ALIAS extorcl DOMAIN OracleGoldenGate

retrieve information from the Credential Store:

GGSCI> INFO CREDENTIALSTORE
OR
GGSCI> INFO CREDENTIALSTORE DOMAIN OracleGoldenGate

18.Is there a way to check the syntax of the commands in the parameter file without actually 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.

19.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.

20.What information can you expect when there is data in the discard file?

When data is discarded, the discard file can contain:
1. Discard row details
2. Database Errors
3. Trail file number

21.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

22.How can you determine if the parameters for a process was recently changed?

When ever 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

23.How processes work in Coordinated Mode?

In a Coordinated Mode Replicat operates as follows:

  • Reads the Oracle GoldenGate trail.
  • Performs data filtering, mapping, and conversion.
  • Constructs SQL statements that represent source database DML or DDL transactions (in committed order).
  • Applies the SQL to the target through the SQL interface that is supported for the given target database, such as ODBC or the native database interface.

24.What difference of Replication between classic mode and coordinated mode?

The difference between classic mode and coordinated mode is that Replicat is multi-threaded in coordinated mode. Within a single Replicat instance, multiple threads read the trail independently and apply transactions in parallel. Each thread handles all of the filtering, mapping, conversion, SQL construction, and error handling for its assigned workload. A coordinator thread coordinates the transactions across threads to account for dependencies among the threads.

25.How to create a coordinated mode process?

You can create the COORDINATED REPLICATE with the following OGG Command:

ADD REPLICAT rfin, COORDINATED MAXTHREADS 50, EXTTRAIL dirdat/et

26.What happen if there was no DISCARDFIL specified?

Starting with OGG 12c, if you don’t specify a DISCARDFILE OGG process now generates a dicard file with default values whenever a process is started with START command through GGSCI.

27.Can I start Extract at a specific CSN?

Yes, Starting with OGG 12c you can now start Extract at a specific CSN in the transaction log or trail.
Example:

START EXTRACT fin ATCSN 12345
START EXTRACT finance AFTERCSN 67890

28.How parallel replicate process worked?

Yes. The database parallel process are leveraged on the target database for automatic dependency aware parallel apply.

This key enhancement makes it very easy to maintain throughput and reducing latency. Previously the Replicat process had to manually be broken down into multiple threads.

29.How Integrated mode read the trail files?

The trail generated by the extract process is read by Integrated Delivery and Logical Chase Records (LCR) are created. These LCR’S are then shipped over the network to the destination database.

30.What is the difference of Integrated mode and Coordinated mode?

Integrated delivery is the new 12c mechanism of sending extract trail to the destination in an Oracle enviornment. Coordinated delivery is the new mechanism to send data between now-Oracle databases.

31.What type of Topology does Goldengate support?

GoldenGate supports the following topologies.

  • Unidirectional
  • Bidirectional
  • Peer-to-peer
  • Broadcast
  • Consolidation
  • Cascading

32.What are the supplemental logging pre-requisites?

The following supplemental logging is required.

  • Database supplemental logging
  • Object level logging

33.I want to configure multiple extracts to write to the same exttrail file? Is this possible?

Only one Extract process can write to one exttrail at a time. So, you can’t configure multiple extracts to write to the same exttrail.

34.What are some of the key features of GoldenGate 12c?

The following are some of the more interesting features

  • Support for Multitenant Database
  • Coordinated Replicate
  • Integrated Replicate Mode
  • Use of Credential store
  • Use of Wallet and master key
  • Trigger-less DDL replication
  • Automatically adjusts threads when RAC node failure/start
  • Supports RAC PDML Distributed transaction
  • RMAN Support for mined archive logs

35.What are the different data encryption methods available in OGG 12c?

In OGG 12c you can encrypt data with the following 2 methods:
1) Encrypt Data with Master Key and Wallet
2) Encrypt Data with ENCKEYS

36.How do you enable Oracle GoldenGate for Oracle database?

The database services required to support Oracle GoldenGate capture and apply must be enabled explicitly for an Oracle 11.2.0.4 database. This is required for all modes of Extract and Replicate.
To enable Oracle GoldenGate, set the following database initialization parameter. All instances in Oracle RAC must have the same setting.
oracle 11.2.0.4和oracle 12.1.0.2及之后的版本需要设置如下参数:

ENABLE_GOLDENGATE_REPLICATION=true

37.List a few parameters which may help improve the replicate performance?

The parameters below can be used to improve the replicate performance:
– BATCHSQL
BATCHSQL is a setup in Oracle GoldenGate replicat to optimize the delivery performance for both initial load and change data capture. By default, Oracle GoldenGate replicat applies one SQL statement at a time. With BATCHSQL, Oracle GoldenGate process multiple SQL statements at once and thus reduces the time of the delivery.
There are conditions which stop the BATCHSQL process, such as

  • LOB and LONG data
  • Row greater than 25k
  • Tables having more than one unique keys besides primary key
  • (SQL Server) The target table has a trigger
  • Errors

In these cases, the batch process will rollback and return to the default apply mode.

38.Oracle Goldengate Relicat，what should be monitor?

The lag and checkpoint latency of the Extract, pump and Replicat processes are normally monitored.

39.When should we use PASSTHRU mode?

In pass-through mode, the Extract process does not look up the table definitions, either from the database or from a data definitions file. This increases the throughput of the data pump, as the object definition look-up is bypassed.

40.What are the most common reasons of an Extract process slowing down?

Some of the possible reasons are:

  • Long running batch transactions on a table.
  • Insufficient memory on the Extract side. Uncommitted, long running transactions can cause writing of a transaction to a temporary area (dirtmp) on disk. Once the transaction is committed it is read from the temporary location on the file system and converted to trail files.
  • Slow or overburdened Network.

41.What are the most common reasons of the Replicat process slowing down?

Some of the possible reasons are:

  • Large amount of transactions on a particular table.
  • Blocking sessions on the destination database where non-Goldengate transactions are also taking place on the same table as the replicat processing.
  • If using DBFS, writing & reading of trail files may be slow if SGA parameters are not tuned.
  • For slow Replicat’s, latency may be due to missing indexes on target.
  • Replicat having to process Update, delete of rows in very large tables.

42.My extract was running fine for a long time. All of a sudden it went down. I started the extract processes after 1 hour. What will happen to my committed transactions that occurred in the database during last 1 hour?

OGG checkpoint provides the fault tolerance and make sure that the transaction marked for committed is capture and captured only once. Even if the extract went down abnormally, when you start the process again it reads the checkpoint file to provide the read consistency and transaction recovery.

43. I have configured Oracle GoldenGate integrated capture process using the default values. As the data load increases I see that extract starts lagging behind by an hour (or more) and database performance degrades. How you will resolve this performance issue?

When operating in integrated capture mode, you must make sure that you have assigned sufficient memory to STREAMS_POOL_SIZE. An undersized STREAMS_POOL_SIZE or limiting the streams pool to use a specific amount of memory can cause troubles.
The best practice is to allocate STREAMS_POOL_SIZE at the instance level and allocate the MAX. SGA at GG process level as below:

SQL> alter system set STREAMS_POOL_SIZE=3G
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2048, PARALLELISM 4)

44.Why would you segregate the tables in a replication configuration? How would you do it?

In OGG you can configure replicate at the data at the schema level or at the table level using TABLE parameter of extract and MAP parameter of replicate.

For replicating the entire database, you can list all the schemas in the database in the extract/replicate parameter file.

Depending the amount of redo generation, you can split the tables in a schema in multiple extracts and replicates to improve the performance of data replication. Alternatively, you can also group a set of tables in the configuration by the application functionality.
Alternatively, you may need to remove tables which have long running transactions in a separate extract process to eliminate lag on the other tables.
Let’s say that you have a schema named SCOTT and it has 50 tables. Out of these 50 tables, 20 tables are heavily utilized by application.
To improve the overall replication performance, you create 3 extract and 3 replicats as follows:

Ext_1/Rep_1 –> 10 tables
Ext_2/Rep_2 –> 10 tables
Ext_3/Rep_3 –> 30 tables
Ext_1/Rep_1 and Ext_2/Rep_2 contains 10 table each which are heavily utilized or generate more redo.
Ext_3/Rep_3 contains all the other 30 tables which are least used.

45.What command can be used to view the checkpoint information for the extract process?

Use the following command to view the Extract checkpoint information.

GGSCI> info extract , showch
GGSCI> info extract ext_fin, showch

46.How is the RESTARTCOLLISION parameter different from HANDLECOLLISIONS?

The RESTARTCOLLISION parameter is used to skip ONE transaction only in a situation when the GoldenGate process crashed and performed an operation (INSERT, UPDATE & DELETE) in the database but could not checkpoint the process information to the checkpoint file/table. On recovery, it will skip the transaction and AUTOMATICALLY continue to the next operation in the trail file.

When using HANDLECOLLISION GoldenGate will continue to be overwritten and process transactions until the parameter is removed from the parameter files and the processes restarted.

Oracle GoldenGate: Handle Collision Parameter and its usage

RESTARTCOLLISIONS and NORESTARTCOLLISIONS
Use the RESTARTCOLLISIONS and NORESTARTCOLLISIONS parameters to control whether or not Replicat applies HANDLECOLLISIONS logic after Oracle GoldenGate has stopped because of a conflict. By default, NORESTARTCOLLISIONS applies. However, there might be circumstances when you would want Oracle GoldenGate to apply HANDLECOLLISIONS logic for the first transaction after startup. For example, if the server is forcibly shut down, the database might have committed the last Replicat transaction, but Oracle GoldenGate might not have received the acknowledgement. Consequently, Replicat will retry the transaction upon startup. HANDLECOLLISIONS automatically handles the resultant errors that occur.

RESTARTCOLLISIONS enables HANDLECOLLISIONS functionality until the first Replicat checkpoint (transaction) is complete. You need not specify the HANDLECOLLISIONS parameter in the parameter file. After the first checkpoint, HANDLECOLLISIONS is automatically turned off.

HANDLECOLLISIONS and NOHANDLECOLLISIONS
HANDLECOLLISIONS | NOHANDLECOLLISIONS

47. What is RBA?

What is RBA and checkpoint in GoldenGate?

48. Oracle 数据库中checkpoint和GoldenGate中的checkpoint有什么区别？

Oracle GoldenGate中Extract的checkpoint

49.How do you view the data which has been extracted from the redo logs?

The logdump utility is used to open the trail files and look at the actual records that have been extracted from the redo or the archive log files.

GoldenGate Logdump