GOLDENGATE Technical Q & A

1. What are the different replication topologies supported by Oracle GoldenGate?

Answer : Oracle GoldenGate supports the following replication topologies:

• Unidirectional replication

• Bidirectional replication

• One-to-many replication

• Many-to-one replication

• Peer-to-peer replication

• Cascading replication

2. Why would you prefer Oracle GoldenGate over Oracle Streams?

Answer : Oracle GoldenGate is the strategic solution from Oracle for all replication needs. It not only supports homogeneous but supports heterogeneous environments too. Installing and configuring OGG is simple compared to Oracle Streams. Also, Oracle GoldenGate delivers better performance in hightransaction environments. In addition to Oracle database to Oracle database replication, OGG supports replication between several popular non-Oracle databases and allows you to integrate relational database systems with nonrelational database systems and cloud systems.

 In terms of new features, Oracle has stopped adding new features to Oracle Streams and shifted the focus to OGG for all replication needs.

3. What databases does Oracle GoldenGate support for replication?

Answer : OGG currently supports the following database types:

• Oracle Database

• Sybase

• Microsoft SQL Server

• MySQL

• IBM DB2

• TimesTen

• Teradata

• Informix

• Enscribe

• SQL/MX

 However, you cannot install Oracle GoldenGate as the source on DB2 for i and TimesTen databases. You still have the option to send feeds from other supported databases and apply the transactions on DB2 for i or TimesTen databases.

4. Why do you need to configure supplemental logging for replication?

Answer : When a transaction is committed on the source database, by default only the new data (an after image of the affected rows) is written to the redo logs for recovery. To apply the same changes on the target, the trail file should have additional information to identify the before image of key columns. To allow the database to capture and write before images of key columns, supplemental logging is required.

 Specifically, in the context of an Oracle database as the source, OGG reads the transaction blocks, which record the redo information for the block. The replicat needs to create SQL statements to apply the captured changes to the target database. The SQL statement should be able to identify the rows to be changed on the target uniquely. Supplemental logging records additional columns for identifying rows uniquely even though the redo block didn’t change these columns.

5. What are some key considerations before setting up bidirectional replication using Oracle GoldenGate?

Answer : The following are some key points for bidirectional replication using OGG:

• Both the systems in an active-active replication should have the same time zone. This is required so that timestamp-based conflicts can be resolved.

• The TRUNCATE statement cannot be configured bidirectional. It can, however, be configured to originate from one machine to another but not vice versa. All truncates should originate only from one machine and be replicated to the other.

• If triggers on tables exist and generate DML operations, then in order to avoid

conflicts between local DML operations and replicated triggered DML operations,

modify your triggers to handle local DML and replicated DMLs. The OGG replicat in

integrated mode allows you to handle triggers.

• Do not replicate database-generated values such as sequences to avoid conflicts. You can use even sequences on one database and odd sequences on another to avoid conflicts.

• Tables should have a primary or unique key to identify rows. This will be needed for  resolving conflicts.

6. Where can you filter particular column data in Oracle GoldenGate?

Answer : Filtering can be done in the extract, data pump, and replicat processes.

7. Why do you need a data pump if an extract can also send trail files to the target?

Answer : Data pumps are similar to GoldenGate extract processes. These are the secondary extract processes that read from the source trails written by the local extract and that pump them to the target over a TCP/IP network. Having data pumps is not absolutely necessary but is highly recommended.

 The following are some of the advantages of having a data pump:

• Protection against losing connectivity to the target system

• Filter and transformation

• One-to-many replication

• Many-to-one replication

8. How is a classic extract different from an integrated extract process?

Answer : In the classic capture mode, data changes are captured from the Oracle redo logs/archive logs.

This was the initial capture mode developed and used by GoldenGate Software Inc. The classic capture process is supported for all databases that are supported by Oracle GoldenGate.

 Integrated capture mode is specific to Oracle Database 11.2 onward. In the integrated capture mode,

the extract process communicates with the database log mining server and receives information in the form of a logical change record (LCR). The database log mining server mines the redo log and captures changes in the form of an LCR.

 The following are some advantages of integrated capture over classic capture:

• It supports more Oracle data types compared to classic capture mode.

• The interaction with the database log mining server allows you to switch

automatically between copies of archived logs and mirror images of redo logs. This is useful in the case of a disk block corruption of archived/redo logs.

• It supports multitenant container databases containing multiple pluggable

databases. This is not possible with classic capture.

• It offers easy configuration for Oracle RAC and ASM.

9. Can you configure multiple extracts to write to the same trail file?

Answer : No, it will either abend with an error that the trail file is locked by another process, or if another extract is not writing to it at the moment, it will overwrite and corrupt the trail file.

10. Can you validate the syntax of parameters used in a parameter file without actually running the process?

Answer : Use the SHOWSYNTAX parameter in the parameter file and start the process. It will report the errors in the report file if there are any.

11. What are the supplemental logging prerequisites for OGG?

Answer : The following supplemental logging is required for OGG on the source where the extract captures information from the redo/archive logs:

• Minimal supplemental logging at the database level

• Object-level supplemental logging (trandata)

12. What are the steps to upgrade a classic capture to an integrated capture?

Answer : The steps to upgrade the extract process are as follows:

 1. Stop the extract and register it with the database.

 2. Test whether the extract can be upgraded.

 3. Use UPGRADE INTEGRATED TRANLOG to upgrade the extract.

 4. Start and verify whether the extract has been upgraded.

13. What are the limitations with DDL replication in Oracle GoldenGate?

Answer : DDL replication is slightly trickier when compared to replicating data. Some of the important limitations are as follows:

• The maximum length of a DDL statement to be replicated is between 2 MB to 4 MB.

If the DDL length exceeds this size, the extract process will issue a warning message and ignore the DDL statement. These ignored DDL statements will be stored in the marker table. The GoldenGate ddl_ddl2file.sql file allows you to dump all the DDL statements at a location specified by the USER_DUMP_DEST directory. You can find this file in your OGG home directory.

• The source and target database objects on which DDL replication will happen should be identical.

• DDL replication is not supported on standby databases.

• DDL replication can be mapped to different object names only by the primary

extract process. Data pumps support DDL replication in PASSTHRU mode.

• A DML/DDL operation within a DDL trigger is not captured.

• It is mandatory to use ASSUMETARGETDEFS instead of SOURCEDEFS in the replicat configured for DDL replication.

14. What is the use of DUMPDDL?

Answer : OGG maintains the DDL history table. The DUMPDDL command can be executed in GGSCI to view the contents of the DDL history table. DUMPDDL will dump all the contents of the DDL history table into GGS_DDL_* tables in the database. Since the DDL history table usually has a large volume of data, only the first 4,000 bytes are dumped for performance reasons. The syntax of DUMPDDL is as follows:

 GGSCI> DUMPDDL

 or as follows:

 GGSCI> DUMPDDL SHOW

15. When configuring DDL replication in bidirectional configuration, what DDLOPTIONS should be used in the source and target OGG processes?

Answer : In bidirectional configuration, the extract and replicat on the primary and secondary database servers should have the following parameters:

• Extract on both servers:

 DDLOPTIONS GETREPLICATES, GETAPPLOPS

• Replicat on both servers:

 DDLOPTIONS UPDATEMETADATA

16. How will you configure DDL replication in a heterogeneous environment?

Answer : Each database has its own DDL statement structures and varies in the syntax. Hence, Oracle GoldenGate DDL replication can be configured only between two Oracle databases. Non-Oracle databases are not supported.

17. What is the GETENV function used for? Give a few examples.

Answer : It returns information about the GoldenGate environment.

 Here’s an example:

 @GETENV ('STATS','TABLE','schema_name.table_name','CDR_CONFLICTS')

 @GETENV ('STATS','TABLE','schema_name.table_name', 'CDR_RESOLUTIONS_FAILED')

 @GETENV ('STATS','CDR_RESOLUTIONS_SUCCEEDED')

 @GETENV ('STATS', 'TRUNCATE')

 @GETENV ('STATS', 'DML')

18. When do you need FLUSH SEQUENCE in OGG?

Answer : FLUSH SEQUENCE is executed during the initial synchronization when the extract is started for the first time. It updates the Oracle sequence so that the initial redo logs are available when the extract starts capturing data. This is needed since the redo is not generated until the current cache for the sequence is exhausted.

 The syntax to use FLUSH SEQUENCE is as follows:

 GGSCI> FLUSH SEQUENCE schema_name.sequence_name

19. Are OGG binaries supported on ASM Cluster File System (ACFS) and Database File System (DBFS)?

Answer : OGG can be installed and configured on ACFS, but OGG binaries cannot be stored on DBFS.

However, files such as parameter files, trail files, and checkpoint files can be stored on DBFS.

20. What are macros in Oracle GoldenGate? How are they defined?

Answer : Macros in Oracle GoldenGate help to reuse commands and parameters. It is a built-in automation tool that can execute a stored set of processing steps. Macros can be nested among each other and can also be stored in libraries and used via library calls.

 You can use macros for extract, replicat, and defgen parameter files. The following is the syntax for

defining a macro:

 MACRO #macro_name

 PARAMS (#p1, #p2 ...)

 BEGIN

 macro_body

 END;

21. What are the primary components of OGG high availability using Oracle Clusterware?

Answer : The main components of OGG HA are as follows:

• Virtual IP

• Shared storage

• ActionScript (or AgentScript)

22. List a few commonly used data filter methods in OGG.

Answer : To filter data, you can use one or more of the following options:

• Use the FILTER or WHERE clause in TABLE / MAP statements

• Use SQLEXEC to execute SQL queries or stored procedures

• Use user exits

23. List data transformation methods in OGG.

Answer : Data manipulation or transformation can be achieved using one or more of the following options:

• Using Oracle GoldenGate conversion functions

• Using user exits from the extract or replicat process to apply a database routine and  return results to Oracle GoldenGate

• Using the replicat to deliver data directly to another ETL engine

24. What is a token?

Answer : A user token is information that can be stored to and captured from a user token area in an Oracle GoldenGate trail header. It is typically used to store environment information but can also be used to store any type of information and then used in OGG replication in multiple ways.

 Here’s the syntax to use the TOKEN option at the source:

 TABLE <table_name>, TOKENS (<token name> = <token data> [, ...]) ;

 For example, the following are sets of token values to be written to a trail file header:

 TABLE TIGER.AUDIT_LOG,

 TOKENS (TKN-OSUSER = @GETENV("GGENVIRONMENT","OSUSERNAME"),

 TKN-TRANSACTION-CSN =@GETENV("TRANSACTION","CSN"),

 TKN-DBNAME = @GETENV ("DBENVIRONMENT","DBNAME"),

 TKN-HOSTNAME = @GETENV ("GGENVIRONMENT","HOSTNAME"),

 TKN-COMMITTIME = @GETENV("GGHEADER","COMMITTIMESTAMP"),

 TKN-REC-FILESEQNO=@GETENV ("RECORD", "FILESEQNO"),

 TKN-REC-FILERBA=@GETENV ("RECORD", "FILERBA"));

25. What are user exits, and what are they used for?

Answer : User exits are user-defined callbacks to C/C++ functions from within Oracle GoldenGate.

These can be called from either the extract or replicat process by using the CUSEREXIT parameter.

 User exits can work as an alternative to or can be used along with column conversion functions.

The major advantage is that data is processed only once when it is extracted at the source. This is unlike the column conversion functions where data is first extracted and then read again to perform the transformation.

 User exits can be used to

• Perform column conversion and manipulation for data transformation

• Repair bad data

• Compute delta information from the before and after update values of any record

• Do complex filtering on extraction and replication of data

• Accumulate statistic data

• Respond to database events

26. What is SQLEXEC used for in OGG?

Answer : SQLEXEC can be used in the extract or replicat parameter file to allow Oracle GoldenGate extract and replicat processes to communicate with the database to execute queries, commands, and stored procedures. This greatly enhances Oracle GoldenGate’s capabilities to perform complex processing.

27. What is data loopback in bidirectional replication?

Answer : Imagine a situation where data capture on Machine A is replicated to Machine B and the same transaction on Machine B gets captured by the extract on Machine B and gets replicated back to Machine A.This will lead to an endless loop. This is called data loopback . You must configure OGG bidirectional replication to distinguish between user transactions and local replicat transactions to avoid such data loopback.

28. What are the types of output supported by the OGG flat-file adapter?

Answer : There are two modes of output supported by flat-file adapter, as listed here:

• Delimiter-separated values (DSV)

• Length-delimited values (LDV)

29. What are the different types of flat-file writers in OGG for producing data files?

Answer : There are three flat-file writers.

• dsvwriter

• diffswriter

• binarywriter

30. How can you integrate relational database systems with Java systems?

Answer : Oracle GoldenGate using the Java application adapter libraries can now capture messages from JMS capture and deliver them to supported target databases. It can also capture transactions and publish them in the form of JMS messages to be fed into and processed by JMS delivery processes on the target system.

 This capability of Oracle GoldenGate has been enabled for integrated transactional systems with Java systems in near real time.

31. List the key considerations before you start configuring OGG high availability using Oracle Clusterware.

Answer : The following are some key points of consideration when you begin configuring OGG high availability.

• Oracle GoldenGate runs on one server at any time. Hence, in a high availability

configuration, OGG can run in active-passive mode only.

• Each node in the cluster must have identical parameter files.

• If one node fails, Oracle GoldenGate is automatically started on the other node. The processes are started from the point they stopped to maintain data consistency.

• A shared server should be available to store recovery-related files. The checkpoint files and Oracle GoldenGate trails should be stored on this shared system.

• You can install Oracle GoldenGate locally on each node and maintain recoveryrelated files on a shared location. Alternatively, you can install Oracle GoldenGate on a shared location and register it with Oracle Clusterware.

• Shared file system options from Oracle are Oracle Cluster File System (OCFS2),

Oracle ASM Cluster File System (OACFS), and Oracle Database File System (DBFS).

Oracle ACFS is the recommended cluster file system for Oracle GoldenGate binaries and trail files in Real Application Cluster configurations for ease of management and high availability. However, if your Oracle Grid Infrastructure version is older than 11.2.0.3, then ACFS mounted on multiple servers concurrently does not support file locking. Thus, you need to mount ACFS on only one server.

32. What is GGCS?

Answer : The service offering by Oracle GoldenGate for data integration and replication to the cloud is called Oracle GoldenGate Cloud Service (GGCS).

33. What are the main components of GGCS?

Answer : Oracle GoldenGate Cloud Service has the following six main components:

• My Account

• My Services

• GGCS console

• Oracle database cloud

• Oracle storage cloud

• Oracle compute cloud

34. What is a SOCKS5 proxy?

Answer : Socket Secure 5 (SOCKS5) is an Internet protocol to exchange network data between the client and server through the proxy server. The SOCKS5 proxy server creates a TCP connection on behalf of the client server. The advantage of the SOCKS5 proxy is it allows you to deliver data to the cloud without a VPN connection using GGCS. The SOCKS protocol provides additional authentication that allows only authorized users to access the server.

35. What are a few new interesting features in Oracle GoldenGate 12c?

Answer : The following are some of the new and significant features added to Oracle GoldenGate 12 c :

• Support for Oracle Database 12 c multitenant architecture.

• Oracle Universal Installer support.

• Integrated replicat for Oracle databases.

• Coordinated replicat for non-Oracle databases.

• Inclusion of metadata in trail files such as the table definition record (TDR). Thus,you are no longer required to create definition files containing table metadata at the target system.

• Ease of monitoring channel lags using automatic heartbeat table.

• Improved trail file recovery. In the case of a bad trail file or missing trail files, you can delete the bad trail files and bounce the extract process. This will automatically

rebuild the required trail files.

• Support for quick and easy logical design of GoldenGate solutions using GoldenGate Studio.

• Nine-digit trail file sequence to support 1 billion files per trail.

• Expanded heterogeneous support.

• Enhanced security with the introduction of the wallet and master key.

• Support for Big Data.

• Support for private and public cloud systems.

• Repair capabilities in Oracle GoldenGate Veridata.

36. What is a credential store?

Answer : A credential store has been introduced with Oracle GoldenGate 12 c . It allows you to manage

user IDs and passwords in Oracle GoldenGate. You can now store usernames and passwords used in OGG processes in the credential store and can use an alias instead in the extract and replicat processes. The credential store also can be used to store usernames and passwords that you can use for DBLOGIN through

GGSCI.

 This shields the original credentials to be displayed and provides an extra layer of security. The credential store is implemented as an auto-login wallet within the Oracle Credential Store Framework (CSF).

37. How do you create a credential store in OGG?

Answer : Use the ADD CREDENTIALSTORE command to create a credential store. By default the credential store is created in the OGG directory dircrd .

 GGSCI> ADD CREDENTIALSTORE

 Credential store created in ./dircrd/.

 The credential store gets created in the dircrd directory in your OGG home. Alternatively, you can specify CREDENTIALSTORELOCATION in the GLOBALS parameter file to create a wallet at another location.

 $ cd dircrd

 $ ls

 cwallet.sso

38. How will you add a coordinated replicat with a maximum of five threads?

Answer : Execute the following command in GGSCI:

 GGSCI> add replicat RFDLD, coordinated, EXTTRAIL /app/ggs/fox/dirdat/f1, maxthreads 5

39. What types of encryption are supported in Oracle GoldenGate?

Answer: There are three types of encryption that Oracle GoldenGate can provide for securing information.

• Encrypting passwords used in extract and replicat parameter files

• Encrypting trail files on both the extract and the replicat

• Data being sent over a TCP/IP network for replication

40. I have Oracle GoldenGate configured for Oracle RAC using classic capture processes. How can

I increase the maximum size of the read operation into the buffer that holds the results of the read operation?

Answer : Use TRANSLOGOPTION ASMBUFSIZE in the extract parameter file to set the maximum ASM buffer size.

41. Name a few parameters that you can use in the replicat to improve performance.

Answer : BATCHSQL , BATCHTRANSOPS , GROUPTRANSOPS , INSERTAPPEND

42. What is the significance of PASSTHRU in a data pump parameter file?

Answer : A data pump essentially serves the function of reading extract trail files and copying them to the target machine. In some cases, data pumps are also configured to filter some information before actually copying or mapping the source to the target tables when the table names or structures differ.

 You can make use of PASSTHRU for tables with no filtering required and when the table structure on the source and the target match exactly. You can define PASSTHRU and NOPASSTHRU in the same data pump as follows:

 PASSTHRU TABLE TIGER.TABLE1;

 NOPASSTHRU TABLE TIGER.TABLE2, WHERE (REGION< 10);

 In pass-through mode, the extract does not look for table definitions either from the database or from the definition file. This increases the overall throughput.

43. What are the common reasons for the extract process slowing down?

Answer : An extract process can slow down because of the following reasons:

• Long-running batch transactions

• Low-bandwidth network

• Overburdened network

• Long-running and uncommitted transactions writing to dirtmp with insufficient

space

44. What are the common reasons for the replicat process slowing down?

Answer : A replicat process can slow down because of the following reasons:

• High volume of transactions on the table

• Table locked on the target database because of blocking sessions

• Missing index on target tables

• Target table that is too large causing increased processing time for update and delete operations

45. An integrated capture with default values has a lag of around 30 minutes. What steps will you take to

address this latency?

Answer : Check whether STREAMS_POOL_SIZE is not undersized. For an integrated extract, allocate a sufficient STREAMS_POOL_SIZE value for the database instance and set MAX_SGA_SIZE in the OGG extract

parameter file.

 Execute the following as SYSDBA :

 SQL> alter system set STREAMS_POOL_SIZE=3G

 Add the following to the OGG extract parameter file:

 TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2048, PARALLELISM 4)

46. When using the integrated replicat, where else other than the OGG report file can you see performance statistics?

Answer : The database AWR reports capture performance statistics for integrated replicat processes.

47. What parameter will you use to report long-running transactions to ggserror.log?

Answer : Use WARNLONGTRANS to set a threshold time for open transactions in an extract parameter file as follows:

 WARNLONGTRANS 1h, CHECKINTERVAL 5m

48. What is the difference between RESTARTCOLLISION and HANDLECOLLISION?

Answer : The RESTARTCOLLISION and NORESTARTCOLLISION parameters control whether the replicat applies HANDLECOLLISION logic after the replicat has stopped because of a conflict. When the replicat is started again, RESTARTCOLLISIONS enables HANDLECOLLISIONS only until the first replicat checkpoint is complete. HANDLECOLLISION is turned off automatically after the first checkpoint.

 On the other hand, when using HANDLECOLLISION , the replicat will continue to overwrite and process

transactions until the parameter is removed from the replicat parameter file and the replicat is restarted.

49. How would you view an encrypted trail file using LOGDUMP?

Answer : Use the LOGDUMP command DECRYPT ON to decrypt the trail file.

50. How will you instruct an extract process to switch and start writing to a new trail file?

Answer : Execute the following command in GGSCI:

 GGSCI> SEND EXTRACT extract_name, ROLLOVER

51. What are the different types of failures in an Oracle GoldenGate replication environment?

Answer : These are the types of failure that impact OGG replication:

• Oracle GoldenGate process failure

• Problem with the source and or target database

• Server storage issues

• Network issues

• User errors (conscious or unconscious mistakes)

52. OGG writes error messages to which files?

Answer : There are three main log files where OGG writes information and error messages.

• OGG error log file: ggserror.log

• Report file of each extract, data pump, or replicat process

• Discard file of each extract, data pump, or replicat process

53. What is REPERROR?

Answer : The REPERROR parameter is used in the replicat parameter file to instruct the replicat what to do in case of a specific replication error.

 Oracle GoldenGate GUI Tools

54. What are the installation steps to install and configure OGG Monitor?

Answer : OGG Monitor installation involves the following steps:

 1. Install Oracle WebLogic Server 12 c with JRF or JDBC.

 2. Install the Oracle GoldenGate Monitor server.

 3. Create the OGG Monitor repository using the Repository Creation Utility (RCU).

 4. Create the WebLogic Server domain using the Configuration Wizard.

 5. Configure the OGG Monitor server.

 6. Start the OGG Monitor server.

 7. Install the Oracle GoldenGate Monitor Agent.

 8. Create and configure the OGG Monitor Agent instances.

55. What are the benefits of using the OGG Director?

Answer : The Oracle GoldenGate Director can greatly add to your ability to manage Oracle GoldenGate implementations in your organization. The OGG Director enables you to perform the following tasks:

• Manage and administer Oracle GoldenGate instances remotely

• Issue GGSCI commands remotely from the OGG Director

• Start/stop processes and instances

• Add new extract or replicat processes

• View error and report files for troubleshooting

• Present a graphical view of Oracle GoldenGate implementations in your company network

56. What are the important components of OGG Veridata?

Answer : These are the components of Oracle GoldenGate Veridata:

• Veridata server

• Veridata web user interface

• Veridata repository

• Veridata Agent

• Veridata CLI or Veridata command-line interface

57. What is Oracle GoldenGate Studio?

Answer : Oracle GoldenGate Studio is a WYSIWYG, drag-and-drop interface to quickly build and deploy Oracle GoldenGate replication solutions. It provides you with templates to quickly build configurations aligned to best practices.

58. What is the minimum Oracle database version you need to use an integrated replicat in your OGG configuration?

Answer : Oracle Database 11.2.0.4 and above.

59. Can you generate comma-separated values (CSV) files using Oracle GoldenGate?

Answer : Yes, you can use the OGG flat-file adapters to generate delimiter-separated flat files.

60. Can you capture transactions on compressed tables in your source database?

Answer : Yes, OGG 11.2 and higher support capture from compressed tables using the integrated extract

process.

61. How do you configure OGG replication when the source and the target OGG are of different versions?

Answer : Use the FORMAT RELEASE parameter to generate trail files in a particular release format.

62. How do you delete old trail files in Oracle GoldenGate?

Answer : Use PURGEOLDEXTRACTS in the MANAGER parameter file.

 Here’s an example:

 PURGEOLDEXTRACTS /app/ggs/tiger/dirdat/t*, USECHECKPOINTS, MINKEEPHOURS 2

63. The OS patch needs to be applied on the source and target servers that have your OGG set up in a one-directional configuration. Provide the sequence of steps for supporting this maintenance activity.

Answer : Ensure that the extract has processed all the records in the redo/archive logs.

 GGSCI> SEND EXTRACT extract_name, LOGEND

 The previous command should show YES ; otherwise, wait for some time and check again. Next, execute

the following steps in order:

 1. Stop the application.

 2. Stop the database.

 3. Stop the OGG processes on the source.

 4. Stop the OGG processes on the target.

 5. Apply the OS patch (the SA team performs this step).

 6. Start the database.

 7. Start the OGG processes on the target.

 8. Start the OGG processes on the source.

 9. Start the application.

64. What is a checkpoint table?

Answer : Checkpoints are maintained for each OGG process to aid in recovery in the case of failure and to maintain data integrity. Checkpoints are maintained individually for the extract, data pump, and replicat processes.

 The checkpoint information can be stored either in the checkpoint files on the server or in a table in the database called a checkpoint table.

65. List some of the database views that you can query to monitor the integrated extract or replicat process.

Answer : Some of the database views for monitoring integrated extract or replicat are as follows:

• DBA_CAPTURE

• GV$GG_APPLY_COORDINATOR

• GV$GG_APPLY_READER

• GV$GG_APPLY_RECEIVER

• GV$GG_APPLY_SERVER

• GV$GOLDENGATE_CAPABILITIES

• GV$GOLDENGATE_CAPTURE

• GV$GOLDENGATE_MESSAGE_TRACKING

• GV$GOLDENGATE_TRANSACTION

• GV$LOGMNR_SESSION

• V$GOLDENGATE_CAPTURE

• V$GOLDENGATE_TABLE_STATS

• V$GOLDENGATE_TRANSACTION

66. List a few things that can cause data inconsistencies between the source and target databases even though you have OGG replication configured.

Answer : In today’s increasingly complex IT environment, data distributed across multiple systems is prone to inconsistencies because of several reasons.

• Hardware and network issues such as system failures, disk corruptions/failures,

and network failure/glitches can lead to data inconsistencies. These inconsistencies might get introduced after a system is brought back from a failure.

• Errors during the initial load of data before replication can actually be started can lead to data inconsistencies. This may happen based on the initial data migration tool used and the way it handles specific data. Also, sometimes a difference in character sets of two systems may introduce bad characters in the target system during the initial load. These may go unnoticed until you have a transaction that fails because of the inconsistency on the two systems.

• It is extremely important to ensure that the source and target database systems

match in terms of table constraints that limit data entered into them. For example,if you have missed the primary/unique constraint or have not enabled a foreign key after the initial load, it may result in duplicates in your target tables when replication is turned on.

• Even though replication is working absolutely fine, transactions on the source can be done in a compromised manner that will prevent it from being captured by the

capture process. For example, doing a bulk data load using the NOLOGGING feature in Oracle databases to boost the performance of the load can create problems.

• When replication is unidirectional, the application, users, and DBA on the target

machine are not supposed to modify data on the target machine; they still, however, may do it, which may lead to inconsistencies in data.

67. What are the currently supported databases that allow you to use the OGG Reverse Utility?

Answer : These are the databases currently supported by the Oracle GoldenGate Reverse Utility:

• Oracle

• Sybase

• SQL Server

• IBM DB2

68. What parameters will you add to your OGG MANAGER parameter file to report the current lag every hour and write a critical message if the lag exceeds 60 minutes?

Answer : LAGREPORTMINUTES 60 , LAGINFOHOURS 1 .

69. When configuring replication between two Oracle tables with the same name, same columns, and

data types but a different column order, can you consider the two tables identical?

Answer : No.

70. What are the responsibilities of the OGG manager process?

Answer : These are main responsibilities of the OGG manager process:

• Cleaning up old OGG trails

• Starting dynamic processes, such as server collectors, extracts, and replicats

• Error and lag reporting

71. What parameter is used to specify the data source (source trail) for a data pump extract?

Answer : EXTRAILSOURCE .

72. Where is user token information stored?

Answer : In a trail file.

73. You have a replicat group called RFDLD001, and you need to start it with a trail sequence 15 from RBA

215424. What is the command?

Answer : ALTER REPLICAT RFDLD001 , EXTSEQNO 15 , EXTRBA 215424 .

74. How will you restore a corrupt checkpoint table from checkpoint files?

Answer : Use the CONVCHK utility.

75. When using both FILTER and SQLEXEC, by default which option is evaluated first?

Answer : FILTER .

76. What is an extract lag?

Answer : A lag is the difference in time when a change record is processed by the extract and the timestamp of the record in the data source.

77. What is the CHECKPARAMS parameter used for?

Answer : It instructs the process to check the parameter syntax and then stop.

78. What OGG feature provides recoverability without a loss of transactions?

Answer : Checkpointing.

79. Where are Oracle GoldenGate macros defined?

Answer : Macros can be defined inside the parameter file or included in a macro file.

80. You have a system that replicated between a central office and 11 regional nodes. What is the best product you can use to monitor these 13 instances and set up e-mail alerts based on the process status?

Answer : The Oracle GoldenGate Director.

81. How do you add comments in the Oracle GoldenGate parameter files?

Answer : Use the COMMENT parameter or double hyphen (--).

 Here’s an example:

 COMMENT this is just a comment statement

 Here’s another example:

 --this is just a comment statement

82. How can you execute a set of GGSCI instructions from a file rather executing each statement individually?

Answer : OBEY file_name .

83. If your source and target do not have primary key defined and you have not used KEYCOLS in your extract and replicat groups, how will the OGG process update and delete statements?

Answer : The first unique key alphanumerically, excluding virtual and nullable columns, will be treated as a primary key. The unique key should also not have a column with a timestamp. If no unique is present,

OGG constructs a pseudo-key with all the columns that can form a unique key excluding unsupported columns.

84. Which file contains the list of key values used by the extract for encryptions and by the collector for decryption? Where is it located?

Answer : The ENCKEYS file is located in the OGG home.

85. What is CDR, and what is it used for?

Answer : Conflict detection and resolution (CDR) plays an important role in a bidirectional replication. It ensures any INSERT , UPDATE , and DELETE conflicts between two machines are handled in real time.

86. When is TRANLOGOPTIONS EXCLUDEUSER used?

Answer : It is used to have the extract process avoid capturing DML transactions applied by the replicat process to the database; you have to tell the extract to skip any transactions from a particular user.

87. What is the difference between COLMAP and COLMATCH?

Answer : While COLMAP is used to map columns for a specific table, you can use COLMATCH to map columns for all tables in a particular extract or replicat group.

88. What is COLSTAT used for?

Answer : COLSTAT is a data manipulation function used with the MAP statement in the replicat parameter file for setting the NULL value expression. The COLSTAT function returns an indicator to the extract or replicat process stating that a column is NULL , MISSING , or INVALID .

89. How can you determine whether the parameters for a particular process were recently changed?

Answer : By looking at old report files and comparing the parameters reported in it with the current parameters.

90. What is DEFGEN?

Answer : The DEFGEN utility is used to generate source table definitions in a file called the definition file .

This file is referred to by the replicat process to map the target table with the source table for replication.

91. What is the difference between ADD SCHEMATRANDATA and ADD TRANDATA?

Answer : ADD SCHEMATRANDATA is used to enable schema-level supplemental logging. The user requires the dbms_streams_auth.grant_admin_privilege privilege to be able to enable schema-level supplemental

logging.

ADD TRANDATA is used to enable supplemental logging at the table level.

92. How will you remove unused checkpoint records from the checkpoint table in the database?

Answer : Execute the following command in GGSCI. You must be logged into the database using DBLOGIN prior to running this command.

 GGSCI> CLEANUP CHECKPOINTTABLE schema_name.checkpoint_table_name

93. What is a trace table?

Answer : A trace table is used in a bidirectional replication to identify replicat transactions to extract and hence prevent the extract from capturing them again. The TRACETABLE parameter is required in the extract and replicat parameter files only if an explicit trace table was created using the ADD TRACETABLE command.

The default trace table is GGS_TRACE .

 Trace tables are required only when using classic replicat. They are not used when integrated replicat is used.

94. What parameter can you use to start an extract when the manager process starts?

Answer : AUTOSTART .

95. Which parameter tells the extract the tables for which changes need to be captured?

Answer : The TABLE parameter in the extract parameter file.

96. For a system that has a firewall controlling the port access, what parameter can be used to control port access for the OGG manager?

Answer : DYNAMICPORTLIST .

97. How can you force the extract to close the current trail file and start writing to a new trail file?

Answer : Execute the following command in GGSCI:

 GGSCI> SEND EXTRACT extract_name, ROLLOVER

98. What image information does the extract store in the trail file based on operation type?

Answer : It stores an after image for the insert and update operations and a before image for the delete operations.

99. How can you schedule for aging discard files and start writing to new discard files?

Answer : Use the DISCARDROLLOVER parameter in extract or replicat parameter files.

100. How can you schedule for aging report files at a particular time instead of waiting until the process restarts?

Answer : Use the REPORTROLLOVER parameter in extract or replicat parameter files.