FAQ's Oracle GoldenGate 12c

FAQ's  Oracle GoldenGate 12c: Part I

This post covers common uestions related to Oracle GoldenGate 12c that we get freuently from our trainees from our training Oracle GoldenGate 12c Administration.

If you are new to Oracle GoldenGate then I highly recommend you to look at Oracle GoldenGate 12c Overview & Components and Oracle GoldenGate 12c Download & Installation 

Common uestions on Oracle GoldenGate Few uestions on Oracle GoldenGate 12c raised by our trainees from our training Oracle GoldenGate 12c Administration:

1: 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?

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

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

A2: Yes

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

A3: If DB is down, Goldengate will abend. 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.

4: Instead of running ‘add trandata’, if I directly run ‘alter table add supplemental logging’ at SL prompt, will OGG still work?

A4: Yes

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

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

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

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

6: 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 able be defined at the target database before starting OGG initial load?

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

READ  Oracle GoldenGate 12c Training : Step by Step Activity Guides /Hands-On Lab Exercise

7: OGG “Skiptransaction” option can be provided only for replicat process? Can it be used for extract and data pump processes also?

A7: Replicat only

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

A8: There is no such thing called as OGG RAC. Only DB is RAC.

9: Under which circumstances do we need to run dblogin in ggsci?

Ans: Whenever any change to DB is reuired from GGSCI

10: If the file system on which trail files are stored gets filled up:

a) how will be the OGG processes behave? Will they get ABENDED?

Ans: Yes. OGG processes get ADENDED

b) How will the underlying source and databases behave?

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

c) How will be the apply gap be detected and restored?

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

If you wish to learn GoldenGate systematically then look at Activity Guides (tasks) you must perform from our Step by Step Guide to Learn Oracle GoldenGate or if you are already working/using Oracle GoldenGate then look at Oracle GoldenGate 12c Troubleshooting using Logdump Utility 

This post is from our Oracle GoldenGate 12c Administration Training, in which we cover  Architecture, Installation, Configuring & Preparing the Environment, DML Replication – Online Change Synchronization, Initial Load, Zero Downtime Migration & Upgrading using GoldenGate, Oracle GoldenGate Security, Performance of Oracle GoldenGate and Troubleshooting and much more.

======================

FAQ’s: Oracle GoldenGate 12c: Part II

Do you want to know what are the most frequently asked questions in Oracle GoldenGate?

This post covers the common questions related to Oracle GoldenGate 12c that we get frequently from the trainees of our  Oracle GoldenGate 12c Administration course.

If you are new to Oracle GoldenGate then I highly recommend you to look at Oracle GoldenGate 12c Overview & Components and Oracle GoldenGate 12c Download & Installation 

Here are the Queries from Trainees:

Q1: What is extract, replicat?

Ans: Extract: The Extract process is responsible for capturing/extracting the committed data from transaction logs of source database. It always runs on source system.

Once extracted it writes the data on local machine into local trail (if exist) or send the data over TCP/IP network to write to remote trail.

Replicat: The Replicat process is the apply process in the Goldengate configuration.

This process runs at the end point of the data delivery chain on the target database.

This process reads the destination trail files and applies the data changes to the target systems.

Q2: What is the datapump in Goldengate?

Ans: The Pump process which is also known as secondary extract process is optional in the Goldengate setup.

This process reads the data from local trail and send it over TCP/IP network to write over to remote trail on target..

Q3: What are the Goldengate types or topologies?

• Uni-directional: Data is replicated in one direction from source to target

• Bi-Directional: The data flows in both direction and stays synced up between site A and site B

• Peer to Peer: Similar to Bi-directional but involves more that 2 databases which stay synced up

• Broadcast: Data from source is sent to multiple destinations

• Consolidation: Data from multiple sources is delivered to one destination DB

• Cascading: Data from source to target in cascaded way or thru intermediate server

Q4: Types of replication in Goldengate.

Ans: Goldengate supports replication of DML and DDL transactions.

DML replication is supported in both unidirectional and bidirectional mode.

DDL replication is supported in unidirectional mode only.

Q4: Difference between classic & integral extract process?

Ans: Classic extract reads data directly from transaction logs of database however integrated extract receives data from logminer server and writes to local trails.

Moreover, Integrated extract is only supported for Oracle Databases.

For more info, Refer our blog

GoldenGate: Upgrade Classic Capture to Integrated Capture

Q5: What is the difference between local trail / Remote Trail?

Ans: Local trail written by primary extract is local to source database and resides on source server.

Remote trails are remote to source database and resides on target server which receives data over TCP/IP network from data pump.

Q6: How to start/stop, extract/replicat for source & target?

Ans:The method to start/stop the process remains same as depicted below:

GGSCi> Start|STOP EXTRACT|REPLICAT <process name>

Q7: What is the format for trail file?

Ans: By default Oracle GoldenGate will write the trail files in it’s own Canonical Format. But other than this default format, we can make the Extract Process to write the Trail files in the below formats which is offered by Oracle GoldenGate itself.,

ASCII

SQL

XML

Q8: What is GLOBAL file in Goldengate & location?

Refer: GLOBALS file reside inside dirprm directory. GLOBALS is a parameter file. The parameters specified in GLOBALS file are global and applicable to all the processes in that particular instance.

Q9: What are the directory in Goldengate?

Ans: Goldengate directories are created during installation of goldengate. The directories are responsible for holding the trail files,checkpoint files,definition files,report files,process status files etc.

Q10: What is credential store?

Ans: Credential store by default resides inside dircrd directory under Goldengate home. The credential store manages user IDs and their encrypted passwords (together known as credentials ) that are used by Oracle GoldenGate processes to interact with the local database. The credential store eliminates the need to specify user names and clear-text passwords in the Oracle GoldenGate parameter files.

For more GoldenGate 12c questions and answers check our previous post: FAQ’s: Oracle GoldenGate 12c

If you wish to learn GoldenGate systematically then look at Activity Guides (tasks) you must perform from our Step by Step Guide to Learn Oracle GoldenGate or if you are already working/using Oracle GoldenGate then look at Oracle GoldenGate 12c Troubleshooting using Logdump Utility 

This post is from our Oracle GoldenGate 12c Administration Training, in which we cover  Architecture, Installation, Configuring & Preparing the Environment, DML Replication – Online Change Synchronization, Initial Load, Zero Downtime Migration & Upgrading using GoldenGate, Oracle GoldenGate Security, Performance of Oracle GoldenGate and Troubleshooting and much more.

======================

FAQ’s: Oracle GoldenGate 12c: Part III

Do you want to know what are the most frequently asked questions in Oracle GoldenGate?

This post covers the most common questions related to Oracle GoldenGate 12c that we get frequently from the trainees of our  Oracle GoldenGate 12c Administration course.

If you are new to Oracle GoldenGate then I highly recommend you to look at Oracle GoldenGate 12c Overview & Components and Oracle GoldenGate 12c Download & Installation 

Here are the Queries from Trainees:

Q1. What are the important process in Goldengate?

Ans: Manager, Extract, Data pump, Collector (background process) and Replicat.

For details refer to our below review of training:

Blog (Oracle GoldenGate Training: Lessons Learned & Key Takeaway: Day2 Review)

Q2. What is difference between CSN, SCN?

Ans: CSN in goldengate is the commit sequence number. It identifies a particular point in time of the source transaction during the capture.

System change number (SCN) is Oracle’s clock – every time we commit, the clock increments. The SCN just marks a consistent point in time in the database.

GoldenGate uses CSN during apply to identify the point in time at which the transaction is committed for maintaining transaction consistency and data integrity.

Q3. How to get log from source & target?

Ans: gserr.log in goldengate is present in Goldengate installation directory.

For viewing report files for each process, it resides inside dirrpt directory.

Q4. What are the pre-request for Goldengate?

Ans:

PRE-REQUISITES FOR REPLICATION

In order for GoldenGate to replicate data from Source database to target database, there are some pre-requisite steps to be performed on both Source & Target Database like

1. Set Parameter Enable_GoldeGate_Replication to TRUE (on both source & target database)

2. Create Global User in Database 12c with prefix C## (on both source & target database)

3. Grant DBA Role to this user (on both source & target database)

4. Enable Supplimental Logging(on source database)

5. Enable Database in Archive Log Mode (on source database)

Q5. What are the areas monitoring required in Goldengate?

Ans: Below are the areas of monitoring required in Goldengate:

1. Process status- Monitor the process status if its running or abended.

2. Lag – Monitor the lag of each process and if in case of lag crossing threshold then troubleshoot.

Q6. What are the Goldengate utilities used?

Ans: Below are the Goldengate utilities used for monitoring and ensuring data integrity:

1. Goldengate Monitor

2. Goldengate Veridata

3. GOldengate Director

4. Goldengate plugin for OEM

Q7. How to define size for extract file?

Ans: You can change the size of trail files with the MEGABYTES option of either the ALTER EXTTRAIL or ALTER RMTTRAIL command, depending on whether the trail is local or remote. To change the file size, follow this procedure.

Issue one of the following commands, depending on the location of the trail, to view the path name of the trail you want to alter. Use a wildcard to view all trails.

INFO RMTTRAIL *

INFO EXTTRAIL *

Issue one of the following commands, depending on the location of the trail, to change the file size.

ALTER RMTTRAIL trail, EXTRACT group, MEGABYTES n

ALTER EXTTRAIL trail, EXTRACT group, MEGABYTES n

Issue the following command to cause Extract to switch to the next file in the trail.

SEND EXTRACT group, ROLLOVER

Q8. How to change Goldengate writing extract file?

Ans: The trail file switchover happens in below scenario:

1. When u restart the process the GG writing trail file switches to next file

OR

2. You may use ETROLLOVER to switch to next trail file

Q9. How to purge extract file automatically?

Ans: Use PURGEOLDEXTRACTS in manager parameter file

Q10. How to check how many extract file created per day?

Ans: The trail files are created inside dirdat directory. You may count the trail files from the directory.

For more GoldenGate 12c questions and answers check our previous posts FAQ’s: Oracle GoldenGate 12c and FAQ’s: Oracle GoldenGate 12c: Part II 

If you wish to learn GoldenGate systematically then look at Activity Guides (tasks) you must perform from our Step by Step Guide to Learn Oracle GoldenGate or if you are already working/using Oracle GoldenGate then look at Oracle GoldenGate 12c Troubleshooting using Logdump Utility 

This post is from our Oracle GoldenGate 12c Administration Training, in which we cover  Architecture, Installation, Configuring & Preparing the Environment, DML Replication – Online Change Synchronization, Initial Load, Zero Downtime Migration & Upgrading using GoldenGate, Oracle GoldenGate Security, Performance of Oracle GoldenGate and Troubleshooting and much more.

=======================

FAQ’s: Oracle GoldenGate 12c: Part IV

Do you want to know what are the most frequently asked questions in Oracle GoldenGate?

This post covers the most common questions related to Oracle GoldenGate 12c that we get frequently from the trainees of our  Oracle GoldenGate 12c Administration course.

This blog is the fourth part of the series “FAQ’s: Oracle GoldenGate 12c”.

For previous FAQ’s Oracle GoldenGate 12c series check here:

FAQ’s: Oracle GoldenGate 12c: Part I 

FAQ’s: Oracle GoldenGate 12c: Part II 

FAQ’s: Oracle GoldenGate 12c Q/A: Part III

Here are the Queries from Trainees:

Q1. What is reverse utility?

Ans: Reverse utility is used for the following purposes:

To restore a test database to its original state before the test run. Because the Reverse utility only backs out changes, a test database can be restored in a matter of minutes, much more efficiently than a complete database restore, which can take hours.

Reverse Utility

To reverse errors, caused by corrupt data or accidental deletions.

For example, if an UPDATE or DELETE command is issued without a WHERE clause, the Reverse utility reverses the operation.

Q2. In unidirectional GoldenGate setup, steps to add a table for replication?

Ans: Below are the steps to already running extract and replicates:

a) Take necessary approval to stop the Goldengate extraction and replication.

b) Stop the processes

c) Edit the parameter files and add a new table in the extract, data pump, and replicate the process.

d) Verify the changes.

e) Start the processes.

If the table in the source database already contains the data then, ensure to perform the Initial Load.

Q3. Can we change the Goldengate Oracle DB without setting up an operating system level?

Ans: Golden gate is installed at OS level outside the database. However, Goldengate setup needs some DB prerequisites to be met.

Q4. What are the types of extract checkpoint positions?

Ans: Extract checkpoint stores below information:

a) Startup checkpoint

b) Recovery checkpoint

c) Current checkpoint

d) Write Checkpoint

For checking details of checkpoint use below command at GGSCI,

GGSCI> info <extract>,showch

Q6. How to check how many records processed in replication /extraction?

Ans: Each process statistics are updated as and when records are processed. The number of records processed since the last restart of the process can be checked by using Using STATS command

GGSCI>STATS <procees-name>

OR using REPORTCOUNT Parameter

Q7. How to calculate & allocate swap space for GG server?

Ans: Swap space is located on hard drives to provide additional memory when the physical memory (RAM) is full.

To estimate the required swap space, you can:

a) Startup one Extract or Replicat.

b) Run GGSCI.

c) View the report file and find the line PROCESS VM AVAIL FROM OS (min).

d) Round up the value to the next full gigabyte if needed. For example, round up 1.76GB to 2 GB.

e) Multiply that value by the number of extracts and replicate processes that will be running. The result is the maximum amount of swap space that could be required, not that this means all the extract and replicate process processing similar transactions. If not, you need to customized space needed per each process.

Because Oracle GoldenGate only writes the committed transaction to the trail files, all of the uncommitted transactions are cached in memory. The cache requires both the physical memory (RAM) and virtual memory (SWAP space). The cache can be configured with the Oracle GoldenGate CACHEMGR (128 GB by default) parameter.

The following is an example content from Oracle GoldenGate report file for the CACHE related information:

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 32G

CACHEPAGEOUTSIZE (normal): 8M

PROCESS VM AVAIL FROM OS (min): 63.97G

CACHESIZEMAX (strict force to disk): 48G

From the Oracle GoldenGate documentation, the operating system must have enough swap space to run Oracle GoldenGate. Oracle recommends a minimum of 512GB for the swap and page file space [2]. Without proper setup, you will get the following error:

Checking Temp space: must be greater than 120 MB. Actual 8257 MB Passed

Checking swap space: 0 MB available, 150 MB required. Failed <<<<

Q8. How to check last 5 recent extract checkpoints?

Ans: GGSCI>info <extname>,showch

Q9. What are the recovery types in the extract process?

Ans: There are two types of recovery mode for extract:

a) Append mode: By default, Extract operates in append mode, where if there is a process failure, a recovery marker is written to the trail and Extract appends recovery data to the file so that a history of all prior data is retained for recovery purposes.

b) Overwrite mode: Overwrite mode is another version of Extract recovery that was used in versions of Oracle GoldenGate prior to version 10.0. In these versions, Extract overwrites the existing transaction data in the trail after the last write-checkpoint position, instead of appending the new data. The first transaction that is written is the first one that qualifies for extraction after the last read checkpoint position in the data source.

Q10. How to manager process to check extract/replicate lag?

Ans: LAG is reported in Goldengate by using the LAGREPORTMINUTES or LAGREPORTHOURS parameter. Use the said parameters to specify the interval at which Manager checks for Extract and Replicat lag.

If you wish to learn GoldenGate systematically then look at Activity Guides (tasks) you must perform from our Step by Step Guide to Learn Oracle GoldenGate or if you are already working/using Oracle GoldenGate then look at Oracle GoldenGate 12c Troubleshooting using Logdump Utility 

==================

Oracle GoldenGate 12c: Download & Installation

This post covers Download & Installation of Oracle GoldenGate 12c (12.2.0.1.1) (software for real-time data integration and replication in heterogeneous IT Systems).

In the previous post, we covered Oracle GoldenGate 12c Overview & Components

GoldenGate 12c installation can be GUI or CLI (including Silent Mode) and needs to be run after unzipping the software.

Download Software: First download the software from OTN and then follow below steps:

Assumption:

ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/db_1

We will install the Oracle GoldenGate software for the replication source in the directory /u01/app/oracle/product/ogg_src, which you must create.

The steps are mentioned for Installation of Oracle GoldenGate in one instance. Same steps need to be followed for other installations of Oracle GoldenGate 12c.

We are configuring GoldenGate for Oracle Database 12c (other databases supported for GoldenGate replication are DB2, MySQL, Informix, SQL Server, Sybase, Teradata)

 

Oracle GoldenGate 12c Installation:

1. Use the terminal window connected as oracle and create the /u01/app/oracle/product/ogg_src directory.

2. Run the Oracle GoldenGate 12.2.0 installer as the oracle user. The installer is located in the /stage/oracle/GG_12c/fbo_ggs_Linux_x64_shiphome/Disk1 directory.

The installer starts, and after a few second the “Install Wizard – Step 1 of 5” window appears on screen:

3. On Installation Option screen, select “Oracle GoldenGate for Oracle Database 12c (799.0MB)” and click

4. On Installation Details screen, in the “Software Location” field enter the directory you created to host the Oracle GoldenGate software for the replication source, /u01/app/oracle/product/ogg_src. Leave the “Start Manager” checkbox selected, and accept the default values for the Database Location (/u01/app/oracle/product/12.1.0.2/db_1) and Manager Port (7809)

5. On Summary screen, verify the fields you entered in the previous screens and click Install to start the installation.

READ  Oracle GoldenGate: How to Make Changes in Parameter Files

6. The window displays the progress bar and keeps you informed about the installation process.

7. After a while, the installation process ends and the last window of the wizard appears, informing you that the installation was successful:

====================

Oracle GoldenGate 12c Overview & Components

This post covers Overview & Components of Oracle GoldenGate (software for real-time data integration and replication in heterogeneous IT Systems).

Oracle Goldengate is a must-know for DBAs & consists of following components:

Manager

Extract

Data Pump

Collector

Replicat

Trails Files

Change Sychronization

 

1. Extract

Oracle GoldenGate extract process resides on the source system and captures the committed transactions from the source database. The DB logs may contain committed as well as uncommitted data but, remember, extract process captures only committed transactions and write them to local trail files. It is important to note that Extract captures only the committed transaction from its data source.

The extract can be configured for any of  the following purposes:

Initial Load: For the Initial Load method of replication, extract captures a static set of data directly from the source table or objects.

Change Synchronization: In this method of replication, extract process continuously captures data (DML and DDL) from the source database to keep the source and target database in a consistent state of replication and it is the sole method to implement continuous replication between the source and target database.

The data source of the extract process could be one of the following

Source table (if the extract is configured for initial load)

The database transaction logs or recovery logs such as (Oracle Redo Logs, Oracle Archive Logs, or SQL audit trails or Sybase transaction logs) depending on the type of source database.

Third-party capture module can also be used to extract transactional data from the source database. In this method, the data and metadata from an external API are passed to the extract API.

Extract captures changes from the source database based on the extract configuration (contains the objects to be replicated from the source database).

Multiple extract processes can be configured on a source database to operate on same/different source objects.

The extract performs either of the following tasks after extracting the data/records from the source database objects.

Delivers the data extracted from the source to the target server Trail Files through the collector process

Writes the data extracted from the source on to the Local Trail Files on the source system

Optionally, Extract can also be configured to perform data filtering, transformation and mapping while capturing data and or before transferring the data to the target system.

2. DataPump

This is an optional GoldenGate process (server process) on the source system and comes into picture when the extracted data from the source is not directly transferred to the target Trail Files. In the DataPump setup, the extract process gets the records/data from a source and keeps it in the local file system by means of local Trail Files. The DataPump acts as a secondary extract process where it reads the records from Local Trail Files and delivers to the Target system Trail files through the collector.

Data Pump is also known as secondary extract process. It is always recommended to include data Pump in Goldengate configuration.

3. Collector

The collector is a server process that runs in the background on the target system in a GoldenGate replication setup where the extract is configured for continuous Change Synchronization.

Collector has the following roles to perform in the GoldenGate replication.

When a connection request is sent from the source extract, the collector process on the target system scan and map the requesting connection to the available port and send the port details back to the manager for assignment to the requesting extract process.

Collector receives the data sent by source extract process and writes them to Trail Files on the target system.

READ  Oracle GoldenGate: What is Supplemental Logging View & Its Importance

There is one collector process on the target system per one extract process on the source system, i.e it is a one to one mapping between extract and collector process.

4. Replicat

The Replicat process runs on the target system and is primarily responsible for replicating the extracted data delivered to the target trail files by the source extract process.

The replicat process scans the Trail Files on the target system, generates the DDL and DML from the Trail Files and finally applies them on to the target system.

Replicat has the following two types of configuration which relate to the type of extract being configured on the source system.

Initial loads: In initial data loads configuration, Replicat can apply a static data copy which is extracted by the Initial load extract to target objects or route it to a high-speed bulk-load utility.

Change synchronization: In change synchronization configuration, Replicat applies the continuous stream of data extracted from the source objects to the target objects using a native database interface or ODBC drivers, depending on the type of the target database.

Optionally, Replicat can also be configured to perform data filtering, transformation, and mapping before applying the transaction on to the target database

5. Trail or Extract Files

 Trails or Extract Files are the Operating system files which GoldenGate use to keep records extracted from the source objects by the extract process. Trail files can be created on the source system and target system depending on the GoldenGate replication setup. Trail Files on the source system are called Extract Trails or Local Trails and on the target system called as Remote Trails.

Trail files are the reason why Goldengate is platform-independent.

By using trail GoldenGate minimizes the load on the source database as once the transaction logs/online logs/redo logs/ archive logs are extracted and loaded by the extract process to trail files, all the operations like filtering, conversions, mapping happens out of the source database. Use of trail file also makes the extraction and replication process independent of each other.

6. Manager

Manager can be considered as the parent process in a GoldenGate replication setup on both the source and target system. Manager, controls, manages and maintains the functioning of other GoldenGate processes and files. A manager process is responsible for the following tasks.

Starting up Oracle GoldenGate processes

Maintaining port number for processes

Starting up dynamic processes

Performing GoldenGate Trail Management

Creating events, errors and threshold report.