Oracle GoldenGate Interview Questions and Answers

Last updated on 05th Oct 2020, Blog, Interview Question

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

Ans:

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

24.What is the PASSTHRU mode used for?

Ans:

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 for an Extract process slowing down?

Ans:

Some of the possible reasons are:

1. 1. Long running batch transactions on a table.
2. 2. 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.
3. 3. Slow or overburdened Network.

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

Ans:

Some of the possible reasons are:

1. 1. Large amount of transactions on a particular table.
2. 2. Blocking sessions on the destination database where non-Goldengate transactions are also taking place on the same table as the replicat processing.
3. 3. If using DBFS, writing & reading of trail files may be slow if SGA parameters are not tuned.
4. 4. For slow Replicat’s, latency may be due to missing indexes on target.
5. 5. 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 the last 1 hour?

Ans:

OGG checkpoint provides the fault tolerance and makes sure that the transaction marked for committed is captured 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 will you resolve this performance issue?

Ans:

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:

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

Ans:

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

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

Depending the amount of redo generation you can split the tables in a schema in multiple extracts and replicats 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:

30.How can we report on long running transactions?

Ans:

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?

Ans:

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?

Ans:

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 HANDLECOLLISIONS GoldenGate will continue to overwrite 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?

Ans:

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?

Ans:

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.

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

Ans:

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

List a 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

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

Ans:

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. You can read more about how to upgrade to Integrated Extract and more about Integrated Delivery. Going forward preference should be given to create new extracts as Integrated Extracts and also to upgrade existing traditional Extracts.

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

Ans:

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

38.What databases support GoldenGate Integrated Delivery?

Ans:

Oracle Integrated Delivery is only available for Oracle Databases.

 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.

39.What are the steps required to add a new table to an existing replication setup?

Ans:

The steps to be executed would be the following:

• Include the new table to the Extract & pump process.
• Obtain starting database SCN and Copy the source table data to the target database
• Start Replicat on target at the source SCN database point.

44.What does the GoldenGate CAN equate to, in the Oracle Database?

Ans:

It is equivalent to the Oracle database SCN transaction number.

41.How do you create a CSV file from the Extracted data?

Ans:

You will have to use the CSV Flat File Adaptor to create CSV files. The source would be the extract trail files which use the configuration of the adapter settings to generate CSV files.

42.What is the purpose of the DEFGEN utility?

Ans:

When the source and the target schema objects are not the same (different DDL) the Replicat process needs to know the source definition of the objects. The output from the DEFGEN utility is used in conjunction with the trail data to determine which column value in the trail belongs to which column.

43.We want to set up one-way data replication for my online transaction processing application. However there are compressed tables in the environment. Please suggest how I can achieve it.

Ans:

You must use OGG 11.2 and configure GoldenGate Integrated Capture process to extract data from compressed tables.

Note: Pre OGG 11.2 doesn’t support extracting data from compressed tables

44.We want to set up one-way data replication using Integrated Extract for my Oracle database running 10.2.0.4. Please suggest the best architecture to achieve it.

Ans:

Oracle GoldenGate Integrated Capture process supports Oracle databases 10.2 and higher. But if you are running Oracle database 10.2 and want your Oracle GoldenGate Integrated Capture process then you must configure downstream topology.

45.I am migrating my Oracle database from non-exadata to exadata but my source OGG software and target OGG software versions are different (Say Source is running OGG 11.1 and target is running OGG 11.2). How can I configure my OGG process to work in such configuration?

Ans:

It is recommended that all instances of Oracle GoldenGate be the same version to take advantage of the new functionality, but this is not possible all the time and is not required. In this scenario, OGG provides a parameter called ‘FORMAT RELEASE’ which allows customers to use different versions of Oracle GoldenGate Extract, trail files, and Replicat together.

Example: RmtTrail /u01/app/oracle/dirdat, Format Release 11.1

Note: The input and output trails of a data pump must have the same trail file version.

46.What are the different OGG Initial load methods available?

Ans:

OGG has 2 functionalities, one it is used for Online data Replication and second for Initial Loading.

If you are replicating data between 2 homogeneous databases then the best method is to use database specific methods (Exp/Imp, RMAN, Transportable tablespaces, Physical Standby and so on). Database specific methods are usually faster than the other methods.

If you are replicating data between 2 heterogeneous databases or your replicat involves complex transformations, then the database specific method can’t be used. In those cases you can always use Oracle GoldenGate to perform initial load.

Within Oracle GoldenGate you have 4 different ways to perform initial load.

• Direct Load – Faster but doesn’t support LOB data types (12c include support for LOB)
• Direct Bulk Load – Uses SQL*LOAD API for Oracle and SSIS for MS SQL SERVER
• File to replicat – Fast but the rmtfile limit is 2GB. If the table can’t be fit in 1 rmtfile you can use maxfiles but the replicat needs to be registered on the target OGG home to read the rmt files from source.
• File to Database utility – depending on the target database, use SQL*LOAD for Oracle and SSIS for MS SQL SERVER and so on.

Oracle GoldenGate initial loading reads data directly from the source database tables without locking them. So you don’t need downtime but it will use database resources and can cause performance issues. Take extra precaution to perform the initial load during the non-peak time so that you don’t run into resource contention.

47.I have a table called ‘TEST’ on source and target with the same name, structure and data type but in a different column order. How can you set up replication for this table?

OGG by default assumes that the sources and target tables are identical. A table is said to be identical if and only if the table structure, data type and column order are the same on both the source and the target.

Ans:

If the tables are not identical you must use the parameter ‘SOURCEDEFS’ pointing to the source table definition and ‘COLMAP’ parameter to map the columns from source to target.

48.What is the best practice to delete the extract files in OGG?

Ans:

Use the manager process to delete the extract files after they are consumed by the extract/replicat process

PURGEOLDEXTRACTS /u01/app/oracle/dirdat/et*, USECHECKPOINTS, MINKEEPHOURS 2

49.What parameters can be used to configure Oracle GoldenGate to extract data from Archived Redo log files only?

Ans:

Use the TRANLOGOPTIONS ARCHIVEDLOGONLY option in the parameter file.

50.Can you see the progress of a FLASHBACK DATABASE operation?

Ans:

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

51.How should I set the database to improve Flashback performance?

Ans:

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.

52.What is the OGG s/w version to use if source DB is Oracle 12c R2 and platform Linux and destination is DB2 10.1 (32-bit) with 32-bit OS. Does it mean we have to download and install separate OGG software versions at source and target servers? Will they be able to communicate with each other?

Ans:

In any case, you will have to install and configure Goldengate on both target and source even if versions are the same. however, they will be two separate binaries.

53.Can the source platform be 32-bit and target platform on 64 bit OS for OGG implementation?

Ans:

Yes

54.We ran an update statement in the source database which updates ten million records, committed it and immediately ran “shut abort” in the source database. Will data get replicated correctly to the target database by OGG?

Ans:

If DB is down, Goldengate will abide. As long as data is written in logs when you restart the process, goldengate will pick from the point where it stopped. So everything depends on what is getting written to logs and goldengate will pick committed transactions.

55.Instead of running ‘add trandata’, if I directly run ‘alter table add supplemental logging’ at SQL prompt, will OGG still work?

Ans:

Yes

56.What happens if we add trandata for a table which do not have a primary key or unique key, but has invisible columns:

a) Will the invisible column be considered for uniqueness while enabling supplemental logging?

b) What happens when we make the invisible column of the table visible?

Ans:

No invisible columns will not be considered for uniqueness if invisible columns are made visible they will be treated as normal columns.

57.When we talk about OGG initial load, the target tables should be empty, but the metadata should be present. What about the indexes corresponding to the tables at the Target database? Should they be defined at the target database before starting OGG initial load?

Ans:

Goldengate Initial load takes care of only data. So yes metadata should be present. For faster load, it is advisable to turn off indexes but it’s not mandatory.

58.OGG “Skip Transaction” option can be provided only for replicat process? Can it be used for extract and data pump processes also?

Ans:

Replicat only

59.If both the source database and target database are RAC databases, will the OGG instances also be RAC?

Ans:

There is no such thing called an OGG RAC. Only DB is RAC.

60.Under which circumstances do we need to run dblogin in ggsci?

Ans:

Whenever any change to DB is required from GGSCI

61.How will the OGG processes behave? Will they get ABENDED?

Ans:

Yes. OGG processes get AMENDED

62.How will the underlying source and databases behave?

Ans:

Source and Target DB are independent of OGG hence no effect on DBS.

63.How will the applied gap be detected and restored?

Ans:

As soon as you restart the process, it will be taken care of. This is done using the Goldengate Checkpoint process.

66.How do you add credentials to the credential store?

Ans:

[DOMAIN domain]

Example:

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

Ans:

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.

68.Is there a separate background process for writing flashback logs?

Ans:

Yes. RVWR (Recovery Writer, a.k.a Flashback Writer) was introduced in Oracle 10g to write flashback data from the Flashback Buffer in the SGA to the flashback database logs on disk.

69.Do I need to shutdown and mount the database to turn flashback on?

Ans:

ALTER DATABASE FLASHBACK ON is an online operation in 11g Release 2. In 10g Release 2 the database must be mounted, but not open.

Once flashback is turned on Oracle starts to save the before-images of the database blocks before they are changed. This guarantees that the database can be flashbacked to the exact point-in-time when the flashback mode was turned on.

70.What happens if RVWR cannot write to disk?

Ans:

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

71.Is it possible to specify the size of the Flashback Buffer in the SGA?

Ans:

Yes, but indirectly. The size of the Flashback Buffer is set to 2 * LOG_BUFFER.

For performance reasons, it’s recommended to set LOG_BUFFER to at least 8MB for

databases running in Flashback Mode.

UPDATE: For large 11.1.0.7+ databases with more than a 4GB SGA, you may consider setting LOG_BUFFER to values in the range of 32-64 MB.

72.Can RMAN be used to backup flashback logs?

Ans:

No. Flashback Logs are not backed up. Even if the command BACKUP RECOVERY AREA is used to backup the contents of the FRA to tape only the following file types are backed up: full and incremental backup sets, control file autobackups, datafile copies, and archived redo logs.

• Flashback Logs are considered to be transient files and cannot be backed up by RMAN. They are not needed for media recovery.

73.When are the flashback logs deleted?

Ans:

Flashback logs are managed by Oracle only. Oracle will try to keep as many Flashback logs as needed to satisfy the DB_FLASHBACK_RETENTION_TARGET parameter. However, if there’s space pressure in the Flash Recovery Area (FRA), flashback logs may be deleted to make room for other things, like backups and archived logs.

74.How can you determine if the parameters for a process have recently changed?

Ans:

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.

75.How would you comment out a line in the parameter file?

Ans:

You can use the “–” character to comment out a line.

76.What are the basic resources required to configure Oracle GoldenGate high availability solution with Oracle Clusterware?

Ans:

There are 3 basic resources required:

1. 1. Virtual IP
2. 2. Shared storage
3. 3. Action script

77.I have a one-way replication setup. The system administration team wants to apply an OS patch to both the OGG source host and the target servers. Provide the sequence of steps that you will carry before and after applying this patch.

Ans:

Procedure:

1. Check to make sure that the Extract has processed all the records in the data source (Online Redo/archive logs)

• GGSCI> send extract , logend

(The above command should print YES)

2. Verify the extract, pump and replicat has zero lag.

• GGSCI> send extract , getlag
• GGSCI> send extract , getlag
• GGSCI> send replicat , getlag

(The above command should pring “At EOF, no more records to process.”)

3. Stop all application and database activity.

4. Make sure that the primary extract is reading the end of the redo log and that there is no LAG at all for the processes.

5. Now proceed with stopping the processes:?

Source:

1. Stop the primary extract

2. Stop the pump extract

3. Stop the manager process

4. Make sure all the processes are down.

Target:

1. Stop replicat process

2. Stop mgr

3. Make sure that all the processes are down.

4. Proceed with the maintenance

5. After the maintenance, proceed with starting up the processes:

Source:

1. Start the manager process

2. Start the primary extract

3. Start the pump extract

(Or simply all the extract processes as GGSCI> start extract *)

4. Make sure all that the processes are up.

Target:

1. 1. Start the manager process
2. 2. Start the replicat process.
3. 3. Make sure that all the processes are up.

78.What database does GoldenGate support for replication?

Ans:

• Oracle Database
• TimesTen
• MySQL
• IBM DB2
• Microsoft SQL Server
• Informix
• Teradata
• Sybase
• Enscribe
• SQL/MX