Interview Q & A for Oracle Golden Gate Part - 1

1. What type of Topology does Goldengate support?

GoldenGate supports the following topologies.

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

2. What are the main components of the Goldengate replication?

The replication configuration consists of the following processes.

• Manager
• Extract
• Pump
• Replicate

3. What transaction types does Goldengate support for Replication?

Goldengate supports both DML and DDL Replication from the source to target.

4. What are the supplemental logging pre-requisites?

The following supplemental logging is required.

• Database supplemental logging
• Object level logging

5. List the minimum parameters that can be used to create the extract process?

The following are the minimum required parameters which must be defined in the extract parameter file.

• EXTRACT NAME
• USERID
• EXTTRAIL
• TABLE

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

7. What type of Encryption is supported in Goldengate?

Oracle Goldengate provides 3 types of Encryption.

• Data Encryption using Blow fish.
• Password Encryption.
• Network Encryption.

8. What are the different password encryption options available with OGG?

You can encrypt a password in OGG using

• Blowfish algorithm and
• Advance Encryption Standard (AES) algorithm

9. What are the different encryption levels in AES?

You can encrypt the password/data using the AES in three different keys

a) 128 bit
b) 192 bit and
c) 256 bit

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

11. What are the installation options available in OGG 12c?

You can install Oracle GoldenGate 12c using in 2 ways:

1) Interactive Installation with OUI – Graphical interface
2) Silent Installation with OUI – Command Interface

12. What is a Credential Store in OGG 12c?

OGG Credential 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 auto login wallet within the Oracle Credential Store Framework (CSF).

13. How to configure Credential Store in OGG 12c?

Steps to configure Oracle Credential Store are as follows:

1) By Default, Credential Store is located under “dircrd” directory.
If you want to specify a different location use can specify “CREDENTIALSTORELOCATION” parameter in GLOBALS file.
Example: CREDENTIALSTORELOCATION /u01/app/oracle/OGG_PASSWD
2) Goto OGG home and connect to GGSCI.
cd $OGG_HOME
./ggsci
GGSCI>

14. What command is used to create the credential store?

ADD CREDENTIALSTORE

15. How do you 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

16. How do you retrieve information from the Oracle Credential Store?

GGSCI> INFO CREDENTIALSTORE
OR
GGSCI> INFO CREDENTIALSTORE DOMAIN OracleGoldenGate

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

18. How do you enable Oracle GoldenGate for Oracle database 11.2.0.4?

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.

ENABLE_GOLDENGATE_REPLICATION=true

19. How does the Replicate works in a Coordinated Mode?

In a Coordinated Mode Replicate operates as follows:

1. Reads the Oracle GoldenGate trail.
2. Performs data filtering, mapping, and conversion.
3. Constructs SQL statements that represent source database DML or DDL transactions (in committed order).
4. 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.

20. What is the difference between Classic and Coordinated Replicat?

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.

21. How do you create a COORDINATED REPLICATE in OGG 12c?

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

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

22. If have created a Replicat process in OGG 12c and forgot to specify DISCARDFILE parameter. What will happen?

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

23. Is it possible to start OGG 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

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

The parameters below can be used to improve the replicate performance:

• BATCHSQL
• GROUPTRANSOPS
• INSERTAPPEND

23. What are the areas to monitor in Goldengate Replication?

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

24. What is the PASSTHRU mode used for?

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.

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

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

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

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

29. 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 100 hundred tables. Out of these hundred tables, 50 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 –> 25 tables
Ext_2/Rep_2 –> 25 tables
Ext_3/Rep_3 –> 50 tables
Ext_1/Rep_1 and Ext_2/Rep_2 contains 25 table each which are heavily utilized or generate more redo.
Ext_3/Rep_3 contains all the other 50 tables which are least used.

30. How can we report on long running transactions?

The WARNLONGTRANS parameter can be specified with a threshold time that a transaction can be open before Extract writes a warning message to the ggs error log.

Example: WARNLONGTRANS 1h, CHECKINTERVAL 10m

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

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

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

34. What does the RMAN-08147 warning signify when your environment has a GoldenGate Capture Processes configured?

This occurs when the V$ARCHIVED_LOG.NEXT_CHANGE# is greater than the SCN required by the GoldenGate Capture process and RMAN is trying to delete the archived logs. The RMAN-08147 error is raised when RMAN tries to delete these files.

When the database is open it uses the DBA_CAPTURE values to determine the log files required for mining. However, if the database is in the mount state the V$ARCHIVED_LOG. NEXT_CHANGE# value is used. See MetaLink note: 1581365.1

35. How would you look at a trail file using logdump, if the trail file is Encrypted?

You must use the DECRYPT option before viewing data in the Trail data.

36. List few useful Logdump commands to view and search data stored in OGG trail files.

Below are few logdump commands used on a daily basis for displaying or analyzing data stored in a trail file.

$ ./logdump – to connect to the logdump prompt
logdump> open /u01/app/oracle/dirdat/et000001 – to open a trail file in logdump
logdump> fileheader on – to view the trail file header
logdump> ghdr on – to view the record header with data
logdump> detail on – to view column information
logdump> detail data – to display HEX and ASCII data values to the column list
logdump> reclen 200 – to control how much record data is displayed
logdump> pos 0 – To go to the first record
logdump> next (or simply n) – to move from one record to another in sequence
logdump> count – counting records in a trail

37. Why should I upgrade my GoldenGate Extract processes to Integrated Extract?

Oracle is able to provide faster integration of the new database features by moving the GoldenGate Extraction processes into the database. Due to this, the GoldenGate Integrated Extract has a number of features like Compression which are not supported in the traditional Extract. Going forward preference should be given to create new extracts as Integrated Extracts and also to upgrade existing traditional Extracts.

38. What is the minimum Database version which supports Integrated Delivery?

Oracle 11.2.0.4 is the minimum required database version which supports both Integrated extract and Integrated Reaplicat.

39. What databases supports GoldenGate Integrated Delivery?

Oracle Integrated Delivery is only available for Oracle Databases.

40. With Integrated Delivery, where can we look for the performance stats?

Yes, with 12c, performance statistics are collected in the AWR repository and the data is available via the normal AWR reports.