Interview Q & A for Oracle Golden Gate Part - 6

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

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

105. Where can macros be invoked?

The macros can be called from the following parameter files.

• Manager
• Extract
• Replicat
• Gobals 

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

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

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

109. What information can you expect when there us 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

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

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

Whenever a process is started, the parameters in the .prm file for the process is written to the process REPORT. You can look at the older process reports to view the parameters which were used to start up the process. By comparing the older and the current reports you can identify the changes in the parameters.

112. Can you talk about parallelism for replicats in Oracle 12c?

Yes. The database parallel process is 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.

113. What are the changes at the pump level in 12c when using integrated delivery?

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.

114. What is the difference between Integrated delivery and Coordinated delivery?

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.

115. What are the areas to monitor in Oracle Goldengate Replication?

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

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

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

117. What database does GoldenGate support for replication?

·      Oracle Database

·      TimesTen

·      MySQL

·      IBM DB2

·      Microsoft SQL Server

·      Informix

·      Teradata

·      Sybase

·      Enscribe

·      SQL/MX 

118. Oracle GoldenGate 12c: Advanced New Features

Here are a few highlights of the new features available with GoldenGate 12c Software.

1. Replication: Coordinated Mode – For environments where there is a large number of transactions against a table or a group of tables, the RANGE function has traditionally been used to improve performance of the replicats. Management of this requires quite a bit of effort, especially when this is done on a group of tables which have relationships with each other. The need for this in most situations is eliminated by the introduction of the Coordinated Replicat in GoldenGate 12c. This allows the replicat to process transactions in parallel similar to a multithreaded process. The coordinated replicat feature is able to handle referential integrity, applying the records to the table in the correct order.
To enable Coordinated Mode Replication the COORDINATED parameter needs to be specified in the parameter file. The THREADRANGE parameter is used in conjunction with COORDINATED parameter to specify the thread number to be assigned to the process. The SYNCHRONIZE REPLICAT can be used to synchronize all these threads to the same position in the trail file.
2. Replicat: Integrated Mode – With Oracle Goldengate 11g, the Integrated Extract mode on the source database was made available. In GoldenGate 12c, the Integrated Replicat Mode has also been introduced for use on the target database. With Integrated Replicat the Logical Change Records (LCR’s) are created and transferred to the inbound server which applies them to the database.
The DBOPTIONS parameter with the INTEGRATEDPARAMS(parallelism n) option needs to be used to create the Replicat in Integrated mode. Here the parallelism is specified where ‘n’ is the number for parallel processes to be used.
3. Multitenant Support: 12c Database – To support the specification of objects within a given PDB, Oracle GoldenGate now supports three-part object name specifications. Extraction and Replication is supported using the TABLE and MAP statements with other parameters and commands which accept the object names as input. The format for these parameters is container.schema.object.
Three-part names are required to capture from a source Oracle container database because one Extract group can capture from more than one container. Thus, the name of the container, as well as the schema, must be specified for each object or objects in an Extract TABLE statement.

Specify a three-part Oracle CDB name as follows:
Syntax: container.schema.object
Example: PDB1.HR.EMP

Oracle GoldenGate supports three-part names for the following databases:
• Oracle container databases (CDB)
• Informix Dynamix Server
• NonStop SQL/MX

Alternatively, to be able to be backup compatible the two naming scheme SCHEMA.OBJECT is still supported with the use of the SOURCECATALOG database name. Below is a sample of the entry required in the parameter file.

SOURCECATALOG plugdb1
MAP schema*.tab*, TARGET *1.*;
4. Security: Credential store – The username and encrypted password login credentials are no longer required to be encrypted and can securely be stored in a database wallet. Reference to this login information is made via an alias.
5. Security: Wallet and Master Key – Data in trail files and the network are encrypted using the master-key wallet mechanism. With the creation of each trail file, a new encryption key is generated. This is used to encrypt the data while the encryption key is encrypted by the master key. Secure network transfer is done by creating a session key using the master key and a crystallographic function.
6. DDL Replication: Native Capture – For capturing DDL operations, the DDL trigger mechanism has been replaced by a new triggerless capture method. This allows support of additional DDL statements which was not previously possible.
7. Installation: Using Oracle Universal Installer (OUI). The installation mechanism no longer uses the untarring of the binaries, rather it uses the OUI, much like most Oracle products.

8. Enhanced character set conversion: The conversion of the source character set to an Oracle target character set is now performed by the Replicat instead of the OCI. The name of the source character set is included in the trail and Replicat uses that character set for its session. This enhancement eliminates the requirement to set NLS_LANG on the target to support conversion. 

9. Remote task data type support: Remote task now supports all Oracle data types, including BLOB, CLOB, NCLOB, LONG, UDT, and XML.
A remote task is a special type of initial-load process in which Extract communicates directly with Replicat over TCP/IP. Neither a Collector process nor temporary disk storage in a trail or file is used. The task is defined in the Extract parameter file with the RMTTASK parameter.
This method supports standard character, numeric, and datetime data types, as well as CLOB, NCLOB, BLOB, LONG, XML, and user-defined datatypes (UDT) embedded with the following attributes: CHAR, NCHAR, VARCHAR, NVARCHAR, RAW, NUMBER, DATE, FLOAT, TIMESTAMP, CLOB, BLOB, XML, and UDT. Character sets are converted between source and target where applicable.
10. Enhanced timezone conversion: Extract now writes the source time zone to the trail. Replicat sets its session to this time zone. This eliminates the need to use SQLEXEC to alter the Replicat session time zone to apply the source database time zone to the target. See Parameter Changes and Additions for parameter changes related to this enhancement.
11. CSN-based transaction filtering: You can now start Extract at a specific CSN in the transaction log or trail.
Syntax: START EXTRACT group_name [ATCSN csn | AFTERCSN csn] START EXTRACT extfin ATCSN 725473
START EXTRACT extfin AFTERCSN 725473
12. Automatic discard file creation: By default, Oracle GoldenGate processes now generate a discard file with default values whenever a process is started with the START command through GGSCI. However, if you currently specify the DISCARDFILE parameter in your parameter files, those specifications remain valid. If you did not specify DISCARDROLLOVER along with DISCARDFILE, however, your discard file will roll over automatically every time the process starts. This automatic rollover behavior contradicts the DISCARDFILE [APPEND/PURGE] option because the new default is to rollover.
• The default discard file has the following properties:
• The file is named after the process that creates it, with a default extension of .dsc.
Example: extfin.dsc.
• The file is created in the dirrpt sub-directory in the GoldenGate home.
• The maximum file size is 50 megabytes.
• At startup, if a discard file exists, it is purged before new data is written.
You can change these properties by using the DISCARDFILE parameter. You can disable the use of a discard file by using the NODISCARDFILE parameter.
If a process is started from the command line of the operating system, it does not generate a discard file by default. You can use the DISCARDFILE parameter to specify the use of a discard file and its properties.

119. What is Bounded Recovery in GoldenGate?

The Oracle online redo log files contain both committed as well as uncommitted transactions, but Oracle GoldenGate only writes committed transactions to the trail files.

Sometimes long running transactions in batch jobs can take several hours to complete. So until the long running transaction is not completed or committed how will GoldenGate handle the situation where an extract is reading from a particular online redo log file when the transaction starts and then with the passage of time other DML activity in the database causes that particular online redo log file to be archived – and then maybe that archive log file is not available on disk because the nightly RMAN backup job has deleted the archive log files from disk after the backup completes.

So GoldenGate has two kinds of recovery – Normal Recovery where the extract process needs all the archive log files starting from the current recovery read checkpoint of the extract and Bounded Recovery which is what we will discuss here with an example.

In very simple terms there is a Bounded Recovery (BR) Interval for an extract which by default is 4 hours and every 4 hours the extract process will makes a Bounded Recovery checkpoint. At every BR interval GoldenGate will check for any long running transactions which are older than the BR interval (which defaults to 4 hours) and writes information about the current state as well as data of the extract to disk – which again by default is the BR sub-directory in the GoldenGate software home location. This will continue at every BR interval until the long running transaction is committed or a rollback is performed. So, a point to keep in mind:

If we are using the Bounded Recovery interval with the default value of 4 hours, then ensure that we keep on disk at least at a minimum archive log files for the past 8 hours to cater for any long running transactions.

120. Adding new tables to a GoldenGate Extract and using the TABLEEXCLUDE parameter

At one of my recent client sites, there was a requirement to enable GoldenGate Change Data Capture for a schema with over 800 tables and also application tables were frequently added which also required to have CDC enabled without the need to stop and restart the extract because we had made a change to the extract parameter file when we added for example the new table name.

Using information gathered from AWR reports and DBA_TAB_MODIFICATIONS, we were able to identify the top 20 tables which encountered the highest level of DML activity and these tables (as well as their associated child tables) were placed in 5 different extract groups.

The sixth extract would be like a ‘catch-all’ extract for all other tables as well as any new tables which were subsequently added. Rather than have to list 780 tables in the extract parameter file we instead used the TABLEEXCLUDE clause to list the 20 tables which were contained in the 5 other extract group and then finally use a TABLE [SCHEMA NAME].* to account for all other tables which had not been explicitly listed in the extract parameter files.

DYNAMICRESOLUTION

When the extract process starts, if there are many tables listed in the parameter file, GoldenGate has to query the database and build a metadata records for each table listed via the TABLE clause. If there are many tables involved, it can affect startup time for the extract. DYNAMICRESOLUTION causes the record to be built one table at a time, instead of all at once. The metadata of any given table is added when Extract first encounters the object ID in the transaction log, while record-building for other tables is deferred until their object IDs are encountered.

DDL INCLUDE ALL

DDL support for not only objects referenced in MAPPED clauses but DDL operations that pertain to tables that are not mapped with a TABLE or MAP statement

DDLOPTIONS ADDTRANDATA, REPORT

Enable Oracle table-level supplemental logging automatically for new tables created with a CREATE TABLE statement. It produces the same results as executing the ADD TRANDATA command in GGSCI.

Also controls whether or not expanded DDL processing information is written to the report file. The default of NOREPORT reports basic DDL statistics. REPORT adds the parameters being used along with a step-by-step history of the operations that were processed as part of the DDL capture

121. What is relation of GoldenGate and Virtual Memory? Explain CACHEMGR CACHESIZE and CACHEDIRECTORY in GG ?

After a recent Oracle GoldenGate installation at a client site running on Solaris 11, we observed memory related errors in the GoldenGate error log like the ones mentioned below as well as extract processes were abending on start up.

ERROR OGG-01841 CACHESIZE TOO SMALL:
ERROR OGG-01843 default maximum buffer size (2097152) > absolute maximum buffer size (0)

The Oracle database alert log also seemed to be reporting quite few “ORA-04030: out of process memory” type errors as well.

The Solaris server had 64 GB of RAM but it seemed that GoldenGate was requiring 128 GB when the extract processes were started.

Let us see why and take a look also at how GoldenGate manages memory operations.

The Oracle redo log files contain both committed as well as uncommitted changes but GoldenGate only replicates committed transactions. So, it needs some kind of cache where it can store the operation of each transaction until it receives a commit or rollback for that transaction. This is particularly significant for both large as well as long-running transactions.

This cache is a virtual memory pool or global cache for all the extract and replicat processes and sub-pools are allocated for each Extract log reader thread or Replicat trail reader thread as well as dedicated sub-pools for holding large data like BLOBs.

Documentation states: “While the actual amount of physical memory that is used by any Oracle GoldenGate process is controlled by the operating system, the cache manager keeps an Oracle GoldenGate process working within the soft limit of its global cache size, only allocating virtual memory on demand.”

The CACHEMGR parameter controls the amount of virtual memory and temporary disk space that is available for caching uncommitted transaction data.

The CACHEMGR CACHESIZE parameter controls the virtual memory allocations and in GoldenGate versions 11.2 onwards for a 64-bit system the CACHESIZE by default is 64 GB.

While the CACHESIZE parameter controls the Virtual Memory, if that is exceeded then GoldenGate will swap data to disk temporarily and that is by default being allocated in the dirtmp sub-directory of the Oracle GoldenGate installation directory.

The dirtmp location will contain the .cm files. The cache manager assumes that all of the free space on the file system is available and will use it to create the .cm files until it becomes full. To regulate this, we can use the CACHEMGR CACHEDIRECTORY parameter and provide both a size as well assign a directory location where these .cm files will be created.

So, the usage of these parameters is:

CACHEMGR CACHESIZE {size}
CACHEMGR CACHEDIRECTORY {path} {size}

The CACHESIZE as mentioned earlier on 64-bit systems defaults to 64 GB and we see that 128 GB is being used because the documentation states:

“The CACHESIZE value will always be a power of two, rounded down from the value of PROCESS VM AVAIL FROM OS”

So, in our case we had set the extract and replicat processes to be started automatically by the manager on restart. These processes start simultaneously so when the first extract process started it momentarily grabbed 128 GB of memory and there was no memory left for the other extract processes to start.

So we used the CACHESIZE parameter to regulate the upper limit on which machine virtual memory can be used by GoldenGate by adding this parameter to each of the extract parameter files:

CACHEMGR CACHESIZE 8G