Oracle GoldenGate 12c Overview And Component Details
Presentation by:
Phone: +080-41156843/+91-9606734482
Email: easyreliable@gmail.com/support@easyreliable.com
Agenda
• Oracle GoldenGate Architecture Overview
• Installation Oracle GoldenGate .
• Configuration Overview and Preparing the Environment
•
Important Keyword
for Golden Gate
Oracle GoldenGate Architecture Overview
Oracle GoldenGate software
is a middleware product designed
to work in a
heterogeneous environment with different databases. It is a replication
technology used to move data among many different platforms.
Oracle GoldenGate can move changes
across a TCP/IP
network and does not
require Oracle Net. Oracle GoldenGate uses its own system of checkpoint files to
maintain transaction integrity
Golden Gate consist of Manager process, Extract (capture) process, data pump process, and replicate (delivery) process. In between these processes are proprietary binary files called trail files. The trail files are used to store the captured transactions.
Oracle GoldenGate Components
Oracle GoldenGate has the following components:
• Manager
• Collector
• Extract
• Data Pump
• Replicat
• Trails or extract files
• Checkpoints
Oracle GoldenGate Process Groups:A process group consists of the following:
•Process (either Extract or Replicat)
–Parameter file
–Checkpoint file
–Any other files associated with the process, such as Report (*.rpt) and Discard (*.dsc)
•Groups can be defined by using the Add Extract and
Add Replicat commands.
•Each process group must have a unique name
Each of these components is covered in detail in later lessons in this course.
Manager: Is required to start and stop the other processes, but is not required for the ongoing operation of another process. That is, you can start the Manager, use the Manager to start the Extract, and then stop and restart the Manager (perhaps to pick up new Manager configuration parameters) without affecting the running Extract.
Server Collector: Also known simply as Collector
Extract: Also known as Change Data Capture
Data Pump: Optional; highly recommended
Replicat : It is on target system
Trails or extract files: Can optionally be compressed and/or encrypted
Details
1) Manager Process: The Manager Process is the Oracle GoldenGate parent process. It controls other Oracle GoldenGate processes, resources, user interface, and reporting of thresholds and errors.
Manager process is responsible for the following functions:
Start and restart of Oracle GoldenGate processes
Starting of dynamic processes
Maintaining port numbers for processes
Trail file management
Event, error, and threshold reports
2) Server Collector: Also known simply as Collector. The collector process is a background process that runs on the delivery (target) side when online change synchronization is active. Collector processes are needed to ensure that the following tasks are performed:
Connection requests from a remote extract to manager can be scanned and bound to available ports. Then assign the port number to the requesting extract via the manager process.
Receive extracted transactions that are sent by the extract and write them to the trail file.
When a network connection is requested, the manager process automatically starts the collector process so there is no need for Oracle GoldenGate users to interact with the collector process. Because the collector can only receive information from only one extract, this means that there will be one collector started for each extract started. The collector process is terminated when the extract process is terminated.
3) Replicate Process: The Replicate process is responsible for reading the trail files and applying the transactions found in chronological order. This ensures that the data is applied in the same order it was captured (SCN order). The Replicate process runs on the target system, It read extract file and replicate it to the target table .Replicate reads extract file and log files sequentially and processes the inserts , updates and deletes specified by selection parameters and Replicate reads extracted data in blocks to maximize throughput.
Oracle GoldenGate uses the SCN on an Oracle database to identify where the transactions are within the replication process. This number is also used when instantiating the replication process, so it is a very important piece of information to gather after setting up the capture process.
SQL> select current_scn from v$database;
SQL> select dbms_flashback.get_system_change_number from dual;
4) Extract Process: It captured committed transactions (and can be filtered) as they occur by reading the transaction logs. It is also known as Change Data Capture . The extract is used to synchronize data that is read from the online transaction log (in Oracle the online redo logs) or the associated archive logs. the extract persists the transaction to disk where it is stored in a series of files known as a trail file. Committed transactions will be stored in a trail file in sequentially organized transaction units. Once transactions are persisted to disk in a trail file, the transaction can be shipped using standard TCP/IP protocols.
5) Data Pump: Optional; highly recommended. The data pump group is a secondary extract group that is used to help ship data across network. An Extract data pump helps protect against network failures. Dump can write to one or multiple remote trails and/or servers
This ability to protect the captured transactions from being lost is huge for many business reasons. If your network were to have an outage, there are two scenarios that can occur:
With a network outage, the data pump process will continue to collect the trail files generated by the primary capture process and store them until the network is restored.
If you are not using local trail files and only writing remote trail files, in the case of a network outage, the primary extract will fail. Once the network is restored, the primary extract can be restored with no loss of transactions.
Parameter Files
Most Oracle GoldenGate functionality is controlled by the use of parameters that are specified in text files.
Oracle GoldenGate uses two types of parameter files:
GLOBALSfile: Stores parameters that relate to the Oracle GoldenGate instance as a whole
Run-time parameter file: Is coupled with a specific process (such as Extract)
By default, parameter files are in dirprmin the Oracle GoldenGate directory.
1) GLOBALS file: Stores parameters that relate to the Oracle Golden Gate instance as a whole. It control things that are common to all processes in an Oracle Golden Gate instance and It can be overridden by parameters at the process level
•GLOBALS parameters apply to all processes.
– These are set when Manager starts.
– They reside in <OGG_HOME>/GLOBALS.
After you add or change any GLOBALS parameters,
you must exit GGSCI for the new parameters to take effect.
Some of the most common parameters include:
MgrServName ggsmanager1: Defines a unique Manager service name on Windows
CheckPointTable oggadmin.ggschkpt: Defines the default table name used for the Replicat checkpoint table
To create a parameter file in GGSCI, use the EDIT PARAM option.
• The GLOBALS parameter file is identified by its file path:
GGSCI> Edit Param ./GLOBALS
2) Run-time parameter file : It is specific to process. Each process has separate parameter files (Manager, Extract, Server Collector, Replicat, and utilities).
– These are set when the process starts.
– They override GLOBALS settings.
– By default, they reside in the dirprm directory in files named <processname>.prm.
– Most apply to all the tables that are processed, but some can be specified at table level.
–Is coupled with a specific process (such as Extract)
GLOBALSVersus Process Parameters
GLOBALSparameters apply to all processes.
These are set when Manager starts.
They reside in <OGG_HOME>/GLOBALS.
Run-time parameter file (process parameters) apply to a specific process
(Manager, Extract, Server Collector, Replicat, and utilities).
These are set when the process starts.
They override GLOBALSsettings.
By default, they reside in the dirprmdirectory in files named <processname>.prm.
Trails files
Trails are used in Oracle GoldenGate to support the continuous extraction and replication of database changes.
A trail can exist on the source or target system, or on an intermediary system.
Only one primary Extract process writes to a trail.
Processes that read the trail include:
Data pump Extract
Replicat
Adding a Local or Remote Trail
how to attach remote trail
Add a local or remote trail with the GGSCI command:
GGSCI> Add ExtTrail | RmtTrail<trail_name>
, Extract <Extract name/group_name>
[, Megabytes <n>]
If you are using a data pump:
The primary Extract needs a local trail (ExtTrail)
The data pump Extract needs a remote trail (RmtTrail)
how to attach remote trail
------------------------------------------
GGSCI> Add ExtTrail /u01/app/ogg/dirdat/aa,
Extract finance, Megabytes 10
or
GGSCI> Add RmtTrail /u01/app/ogg/dirdat/bb, Extract parts, Megabytes 5
On the local system, a trail is known as an Extract trail (or local trail). On a remote system, it is known as a remote trail. The primary Extract writes to a local trail.
The data pump Extract sends changes to a remote trail. The default trail file size is 500 MB. In this example, the parts Extract file is set to 5 MB.
Capture and Delivery Work Flow
Capture and Delivery Work Flow without datapump
On the source system
1. An Extract process captures transactional changes from transaction logs.
2. The Extract process sends data across a TCP/IP network to the target system.
On the target system
1. A Server Collector process reassembles and writes the data to an Oracle GoldenGate remote trail.
2. A Replicat process reads the trail and applies it to the target database. (This can be concurrent with data capture or performed later.)
Manager processes on both systems control activities such as starting, monitoring, and restarting processes; allocating data storage; and reporting errors and events.
In an Oracle Real Application Clusters (RAC) configuration with Oracle GoldenGate, one primary Extract is used for each node, and each primary Extract uses its own thread of REDO.
Capture and Delivery Using a Data Pump
On the source system
1. An Extract process captures transactional changes from transaction logs.
2. The Extract process writes a local oracle golden gate trail .
3. A second Extract process (called a data pump) sends the data across the network to the target system.
On the target system
1. A Server Collector process reassembles and writes the data to an Oracle GoldenGate Remote trail.
2. A Replicat process reads the remote trail and applies it to the target database.
Manager processes on both systems control activities such as starting, monitoring, and restarting processes; allocating data storage; and reporting errors and events.
Important Keyword for Golden Gate
SourceIsTable :SourceIsTable instructs Extract to read the source tables directly rather than from the transaction log.
RmtTask instructs the Manager process on the target system to start a Replicat process with a group name specified in the Group clause
DiscardFile creates a log file to receive records that cannot be processed.
MAP establishes the relationship between source table and the target table.
Obey Files :Obey files are files that you create to run against an Oracle GoldenGate environment. The content of an obey file is a sequenced set of Oracle GoldenGate commands that can be run from a single file. The below is an example of an obey file that is used to create a capture and data pump process.
.
Obey File Example
--Adds Extract process
ADD EXTRACT E_HR, TRANLOG, BEGIN NOW
--Adds local trail file
ADD EXTTRAIL ./dirdat/lt, EXTRACT E_HR, megabytes 1000
--Adds Data Pump Process
ADD EXTRACT P_HR, EXTTRAILSOURCE ./dirdat/lt
--Adds remote trail file
ADD RMTTRAIL./dirdat/rt, EXTRACT P_HR, megabytes 1000
You will notice in that all the commands have to do with adding the processes to Oracle GoldenGate. Obey files are created for setting up environments and they can be created for any command you would like to run within the Oracle GoldenGate environment.
Until now, you have taken a look at the profiler that is run against an Oracle database to see if there is anything you need to worry about before setting up your replication environment.
You have also taken a look at the different parameter files that are needed for the components of the replication environment.
Now, let’s take a look at how to put these pieces together in a simple replication environment.
Process Report Files :It enable to view Runtime statistics for the number of operations processed. Every Extract, Replicat, and Manager process generates a report file at the end of each run. The report can help you diagnose problems that occurred during the run, such as invalid mapping syntax , SQL errors, and connection errors.
To view the report, you can use the following within GGSCI:
GGSCI> View Report process name
MinKeepDays parameter : It is used to purge used trail data (best practice).It is configured in the Manager parameter file.
TranData: It enables supplemental logging of primary keys and/or indexes for a schema or table in the transaction logs. Also, use SchemaTranData for all the present and future tables in a schema. Creates an object or enables TranData or SchemaTranData capture Enabling
Supplemental Logging : Adding supplemental log data forces the log writer to also include the primary keys to logs instead of just the columns that have changed.
SCHEMATRANDATA And TRANDATA
There are two types of trandata that can be used within Oracle GoldenGate. Both types do the same thing, but one is more specific to the schema level and the other is more specific to the database level. Both approaches can be used with enabling trandata, though.
1) SCHEMATRANDATA:When you use SCHEMATRANDATA It enable schema-level supplemental logging for tables, logging will act on all of the current and future tables in an associated schema. This option automatically logs a superset of available keys that Oracle GoldenGate requires for row identification. This option is valid for both classic and integrated capture modes with Oracle GoldenGate.
ADD SCHEMATRANDATA provides the following benefits:
Enables Oracle supplemental logging for new tables created with the CREATE TABLE command.
Updates supplemental logging information for tables affected by an ALTER TABLE to add or drop columns.
Updates supplemental logging for tables that are renamed.
Updates supplemental logging for tables where unique or primary keys are added or dropped.
An additional benefit of using ADD SCHEMATRANDATA is that by default it logs the key columns of a table in the following order of priority:
Primary keys.
If there are no primary keys, all unique indexes will be used for the table, including those that are disabled, unusable, or invisible. Additionally, foreign keys will be used for row dependency.
If the prior two are not available, the all scalar columns of the table will be logged.
2) TRANDATA: When you have a need to capture the transaction information from the transaction records, then it is time to use ADD TRANDATA. ADD TRANDATAis valid for specific databases like, IBM DB2, DB2 LUW, DB2 z/OS, MS SQL Server, and a few others, including the Oracle Database. This option expands on the information captured during the extraction process. Oracle recommends that force logging be enabled along with minimal supplemental logging at the database level when using Oracle GoldenGate.
Consider a schema named myschema with tables A, B, and C, and run the following command:
GGSCI> Add TranData myschema.*
The result is that it adds the transaction data of the three tables. Later, you create another table myschema.D, but now that table is not added to the TranData list because Add has already happened and the wildcard was already resolved.
Alternatively, suppose that you have another schema named yourschema and it has tables E, F, and, G. You run the following command:
GGSCI> Add SchemaTranData yourschema
It adds the three tables just like Trandata would. Later, you create another table myschema.H, and that table and all future tables for yourschema are automatically added to the TranData list.
Now that you have a difference between ADD SCHEMATRANDATA and ADD TRANDATA, you will need to add it to database configuration through Oracle GoldenGate. To do this, you need to log in to the Oracle Database as the GoldenGate user through GGSCI. The steps for this process are outlined here.
TranData and SchemaTranData do the same thing, except TranData operates at the table level (including wildcards), and SchemaTranData operates at the schema level. For Collision Detection, you need to add more columns than the default to the TranData command
defgen utility : The defgen utility program produces a file containing a definition of the layouts of the source files and tables. The output definitions are saved in an edit file and transferred to all target
systems in text format.
Replicat and Collector read in the definitions at process startup and
use the information to interpret the data from Oracle GoldenGate trails.
Commit Sequence Number (CSN)
A CSN is an identifier that Oracle GoldenGate constructs to identify a transaction for the purpose of maintaining transactional consistency and data integrity.
A CSN uniquely identifies a particular point in time at which a transaction commits to the database.
The CSN can be required to position Extract in the transaction log, to reposition Replicat in the trail, or to fulfill other purposes.
(CSN) is an identifier that Oracle GoldenGate constructs to identify a source transaction for the purpose of maintaining transactional consistency and data integrity.
It uniquely identifies a particular point in time at which a transaction commits to the database. Each kind of database management system generates some kind of unique serial number of its own at the completion of each transaction; this number uniquely identifies that transaction. A CSN captures this same identifying information and represents it internally as a series of bytes, but the CSN is processed in a platform-independent manner.
A comparison of any two CSN numbers, each of which is bound to a transaction-commit record in the same log stream, reliably indicates the order in which the two transactions are completed.
Extract Process Details
Oracle GoldenGate’s Extract process reads native transaction logs and captures transactions as soon as they commit, and takes the transactions outside of the database system to be queued. Oracle GoldenGate moves only changed, committed transactional data, allowing it to operate with extremely high performance and very low impact.
Oracle GoldenGate allows filtering at the schema, table, column, or row level. Row-level transformations can be applied either at this capture stage or later when delivering.
It capture committed transactions (and can be filtered) as they
occur by reading the transaction logs. It is also known as Change Data Capture
Oracle GoldenGate can do a “Classic” Extract for any platform. However, if the platform is Oracle Database of a certain version, it can also do an optional “Integrated” Extract (bypassing the logs) by using direct APIs
Note: Classic Extract does not work when the source database is Oracle 12c configured in multitenant mode.
Extract Process Details
•Extract writes data to any of the following:
– Local trail (ExtTrail) on the local system
– Local file (ExtFile) on the local system
– Remote trail (RmtTrail) on a remote system
– Remote file (RmtFile) on a remote system
Extract trails and files are unstructured, with variable length records.
– I/O is performed using large block writes.
•Extract writes checkpoints for trails during change capture:
– This guarantees that no data is lost during restart.
– Multiple Replicat processes may process the same trail.
•Extract does not write checkpoints for files.
Types of Extract
From 11gR2 onwards, Oracle Goldengate introduced a new feature called as Integrated Extract. The main difference between the classic capture and integrated capture modes is that in the classic capture the extract reads the Oracle database online redo log files/archive log files to capture changes while in the integrated capture mode the database log mining server reads the redo log files and captures changes in the form of LCR’s (Logical Change Records) which are then accessed by the Golden Gate extract process.
Integrated Extract:
• Is an Oracle GoldenGate Extract for Oracle databases
• Is multithreaded
• Relies on Oracle’s internal log parsing and processing implementation
• Supports more data types
• Supports downstream topologies
• Is available with OGG version 11.2.1 and later
Classic Extract:
• Is traditional REDO log–based extract for Oracle
• Works for all supported DB platforms and versions
Overview of Trails/Extract Parameter Files
Oracle GoldenGate converts the committed transactions into a canonical (universal) data format and store it in “trail” files. Using source and target trail files, it ensures that data integrity is maintained—even if there is a system error or an outage.
• Trails are used in Oracle GoldenGate to support the continuous extraction and replication of database changes.
• A trail can exist on the source or target system, or on an intermediary system.
• Only one primary Extract process writes to a trail.
• Processes that read the trail include:
– Data pump Extract
– Replicat
If the output trail is remote, this normally triggers the target Manager process to start a Server Collector process with default parameters.
Extract Trails and Files Contents
After the capture ,Oracle GoldenGate converts the committed transactions into a canonical (universal) data format in “trail” files. Using source and target trail files, it ensures that data integrity is maintained—even in the event of a system error or outage.
• Each record in the trail contains an operation that has been committed in the source database.
• Committed transactional order is preserved.
• Operations in a transaction are grouped together in the order in which they were applied.
• By default, only the primary key and changed columns are recorded.
• Flags indicate the first and last records in each transaction.
• Starting from Oracle GoldenGate release 12.2, the information about database objects being replicated.
Adding a Local or Remote Trail
Add a local or remote trail with the GGSCI command:
GGSCI> Add ExtTrail | RmtTrail <trail_name>, Extract <group_name>
[, Megabytes <n>]
• If you are using a data pump:
– The primary Extract needs a local trail (ExtTrail)
– The data pump Extract needs a remote trail (RmtTrail)
GGSCI> Add Extract
GGSCI> Add Extract ExtTrail /u01/app/ogg/dirdat/aa, mypump, Megabytes 10 RmtTrail /u01/app/ogg/dirdat/bb, parts, Megabytes 5
On the local system, a trail is known as Extract trail
(or local trail). On a remote system, it is known as a remote trail
the primary extract write to a local trail. The data pump Extract sends
changes to a remote trail. The default file size is 500 MB. In this example, the parts Extract file is set to 5 MB.
The Passthru parameter is used on a data pump if you do not need to perform any data transformations
Sizing and Retention of Trail Files
Here are a few recommendations that can be followed:
Make the size the same as archive logs.
Identify the size and frequency of redo log switches and use that as a guide.
Monitor lag and trail file switch, based on size, and readjust as needed.
Retention of trail files should be handled similar to retention of archive logs.
Extract Trails and Files Contents
Each record in the trail contains an operation that has been committed in the source database.
Committed transactional order is preserved.
Operations in a transaction are grouped together in the order in which they were applied.
By default, only the primary key and changed columns are recorded.
Flags indicate the first and last records in each transaction.
Starting from Oracle GoldenGate release 12.2, the trail files also contain metadata information about database objects being replicated.
Primary Extract Configuration for Oracle on source system
GGSCI> Edit Params finance
Extract finance
UserIDAlias oggalias
ExtTrail /ggs/dirdat/lt
Table SALES.ORDERS;
Table SALES.INVENTORY;
Or
Simple Extract Parameter File
EXTRACT E_HR
USERID ggate, PASSWORD ggate
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_3")
SETENV (ORACLE_SID="tst12c")
TRANLOGOPTIONS DBLOGREADER
EXTTRAIL ./dirdat/bt
TABLE HR.*;
We can see that this is for a capture process named E_HR, uses a user named ggate to access the database, and sets up the environment to access the tst12c database. After the environment is set, the capture process is looking to read from the transaction log. TRANLOGOPTIONS is the parameter that controls the way the extract will interact with the database transaction log. By using the DBLOGREADER option, the extract knows to use a new application programming interface (API) that is available in Oracle 11.2.0.2 and later. This API uses the database server access to mine the redo and archive logs. After accessing the transaction log, the extract checks for any long-running transactions on a 30-minute interval, and moves captured data to the defined local trail file. Finally, the capture process captures all Data Manipulation Language (DML) coming from tables in the HR schema
Data pump
A secondary Extract process that reads from a local trail and distributes that data to a remote system it allow a local trail on source system which is useful for recovery if the network or target system fails RmtHost is used to identify the name or IP address of the remote system and the port that is being used.
This configuration enhances the fault tolerance and reliability of the
Trail overall GoldenGate environment. In the event of a network failure (between the source and the target systems), the Oracle GoldenGate Capture component can continue to capture transactions, because the data can be queued up locally in the trail files on the source, enhancing the recoverability in case of database failures.
Data Pump: Optional; highly recommended. GoldenGate distributes data for routing to targets.
• An Extract data pump can write to one or multiple remote trails and/or servers(not to be confused with Oracle Data Pump).
• A data pump:
– Reads the local trail
– Manipulates the data or passes it through without change
– Sends the data to one or more targets
•A data pump is useful:
– As a safeguard against network and target failures
– To break complex data filtering and transformation into phases
– To consolidate data from many sources
– To synchronize one source with multiple targets.
• Trails or extract files: Can optionally be compressed and/or encrypted- GoldenGate stages and queues data for routing.
GGSCI> Edit Params mypump
Extract mypump
Passthru
RmtHost <target>, MgrPort <port>
RmtTrail ./dirdat/rt
Table SALES.ORDERS;
Table SALES.INVENTORY;
GGSCI> View Params somename
Extract somename
-- UserIDAlias oggalias
RmtHost mytarget.example.com, MgrPort 7909
RmtTrail ./dirdat/rt
Passthru
Table SALES.ORDERS;
Table SALES.INVENTORY;
• UserID and Password supply database credentials.
• if Datapump is running with Passthru ,The UserID lines are not required for data pumps, only for primary extracts. The Passthru parameter is used on a data pump if you do not need to perform any data transformations or user exit processing.
• RmtHost specifies the target system; the MgrPort option specifies the port where Manager is running
• RmtTrail specifies the Oracle GoldenGate path and trail file prefix on the target system.
• The system will append 000000000, 000000001, 000000002, and so on to the file name prefix.
• Table specifies a source table for which activity will be extracted.
Data Pump Parameter File
-- Verifies parameter file syntax. COMMENT OUT AFTER TESTING.
EXTRACT P_HR
PASSTHRU
RMTHOST 172.16.15.132, MGRPORT 15000, COMPRESS
RMTTRAIL ./dirdat/rt
TABLE HR.*;
Data Pump Parameter File
Starting in Oracle GoldenGate 12c (12.2), the PASSTHRU parameter is deprecated due to metadata being shipped in the trail files.
When reading this parameter file, you can tell that an extract process is set up as a data pump when you see the parameter PASSTHRU. If the PASSTHRU parameter is not included, then the extract will operate as a normal extract. Additionally, in the parameter file you will see where the trail files and transactions are shipped to with the remote host (RMTHOST) parameters. As part of the RMTHOST parameter, you need to tell the data pump how to connect to the remote server with the MGRPORT option with port number. Finally, the COMPRESS option forces the data packets to be compressed across the network. The RMTTRAIL parameter tells Oracle GoldenGate to create a new trail file on the remote side, containing data and transformations, when they are shipped. The data pump needs to know what data is being shipped.
Note
The MGRPORT is the port of the manager process on the target side. The number can be different for every manager.
Replicat
• The Replicat process runs on the target system and used to apply changes on target database.
• Multiple Replicat processes can be used with multiple Extract processes in parallel to increase throughput.
• Replicat can:
– Read data out of Oracle GoldenGate trails file
– Perform data filtering by table, row, or operation
– Perform data transformation
– Perform database operations just as your application performed them
Oracle GoldenGate trails are temporary queues for the Replicat process. Each record header in the trail provides information about the database change record. Replicat reads these trail files sequentially and then processes
INSERTs ,UPDATEs and DELETs that meets your criteria.
Replicat parameters specify the following:
• A group name that is also associated with a checkpoint file
• A list of source-to-target relationships:
– Optional row-level selection criteria
– Optional column mapping facilities
–Optional transformation services
– Optional stored procedure or SQL query execution
• Error handling
• Various optional parameter settings
Oracle GoldenGate can do a “Classic” Replicat (delivery) for any supported platform. However, if the platform is Oracle Database of a certain version, it can also do an optional “Integrated” Replicat (bypassing the SQL apply) by using direct APIs, which is more efficient.
Replicat Parameter File
The replicat parameter file is the parameter file that tells Oracle GoldenGate what to apply. It provides the mapping command that links the incoming transactions to the targeted tables. Replicat Parameter File
REPLICAT R_HR
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_3")
SETENV (ORACLE_SID="tst12cr")
USERID ggate, PASSWORD ggate
map HR.*, target HR.*;
Just like the extract parameter file, the replicat parameter file needs to set up the environment that is needed to connect to an Oracle database. You then need to provide a username and password to interact with the database. In older versions of Oracle GoldenGate, the parameter ASSUMETARGETDEFS is used to correlate the metadata structure between source and target if the tables match. If the metadata of tables are different, then this parameter needs to be replaced with a SOURCEDEF file that supports the mapping. At the end of the file, you see the map statement. This statement maps the incoming transactions to the target side.
Sample Replicat Parameter File
-- Created by Joe Admin on 10/11/2017.
Replicat salesrpt
SetEnv (ORACLE_SID = 'orcl')
UserIDggsuser@myorcl, Password ggspass
--UserIDAliasoggalias
DiscardFile ./dirrpt/SALESRPT.dsc, Append
Map HR.STUDENT, Target HR.STUDENT
Where (STUDENT_NUMBER < 400000);
Map HR.CODES, Target HR.CODES;
Map SALES.ORDERS, Target SALES.ORDERS,
Where (STATE = 'CA' AND OFFICE = 'LA');
•Replicat names the group that links the process, checkpoints, and log files together.
•Indicate the SID in either the Set Environment or the User ID (probably, not both).
•UserID and Password provide the credentials to access the database.
Sample Replicat Parameter File
Replicat salesrpt
SetEnv (ORACLE_SID = 'orcl')
UserID ggsuser@myorcl, Password ggspass
-- UserIDAlias oggalias
DiscardFile ./dirrpt/SALESRPT.dsc, Append
Map HR.STUDENT, Target HR.STUDENT
Where (STUDENT_NUMBER < 400000);
Map HR.CODES, Target HR.CODES;
Map SALES.ORDERS, Target SALES.ORDERS,
Where (STATE = 'CA' AND OFFICE = 'LA');
Replicat names the group that links the process, checkpoints, and log files together. Map links the source tables to the target tables and applies mapping, selection, error handling, and data transformation, depending on options. Because the Map statements are terminated with a semi-colon, they may span multiple lines in the parameter text file without any special continuation characters
Replicat Parameter File
DiscardFile identifies the file to receive records that cannot be processed for any reason. Records will be appended or the file will be purged at the beginning of the run, depending on the options. Starting with version 12.1.2, creation of the discard file is the default and does not need to be specified. If you do not specify this parameter, the file name will be the process name. If it is a Coordinated Replicat, the file name will be the process name plus the thread ID. If the file already exists, you must specify Append or Purge, else you will get an error. See also DiscardRollover. By default, the discard file rolls over for each new process start. Rolled file names are the process name plus a one-digit sequence number starting with 0 (similar to the reports).
•Map links the source tables to the target tables and applies mapping, selection, error handling, and data transformation, depending on options. Because the Map statements are terminated with a semi-colon, they may span multiple lines in the parameter text file without any special continuation characters.
Manager Process
The Manager is the Oracle GoldenGate parent process.
• The Manager controls other Oracle GoldenGate processes, resources, user interface and reporting of thresholds and errors.
Sample Manager Parameter File
-- Some Comment with leading double-dashes.
-- Created by Joe Admin on 10/11/2017.
Port 7809
DynamicPortList 9001–9100
Autostart ER *
AutoRestart Extract *, WaitMinutes 2, Retries 5
LagReportHours 1
LagInfoMinutes 3
LagCriticalMinutes 5
PurgeOldExtracts ./dirdat/rt*, UseCheckpoints
Port: Establishes TCP/IP port number on which Manager listens for requests.
DynamicPortList : specifies the ports that Manager can dynamically allocate
Autostart:specifies he processes that are to be automatically started when Manager starts
AutoRestart: Specifies the processes to be restarted after abnormal termination
LagReportHours: Sets the interval, in hours, at which Manager checks the lag for Extract and Replicat processing. Alternatively, this can be set in minutes.
LagInfoMinutes: Specifies the interval at which Extract and Replicat will send an
informational message to the event log. Alternatively, this can be set in seconds or hours.
LagCriticalMinutes: Specifies the interval at which Extract and Replicat will send a
critical message to the event log. Alternatively, this can be set in seconds or hours.
PurgeOldExtracts: Purges the Oracle GoldenGate trails that are no longer needed, based on option settings
Manager Parameter File
Notice you do not need to name the manager. In Oracle GoldenGate environments, the manager process has a default name of MGR, so the parameter file has to be named MGR.prm.
Manager Parameter File
PORT 15000
Listing 3-6 shows a simple parameter file for the manager process. The only thing required in the manager parameter file is the port number that the manager will listen on. This allows Oracle GoldenGate instances to connect with each other and write trails files. Additionally, as mentioned earlier, the manager process is used to keep track of items like events, up or down status of processes, and errors that occur. To make better use of the items that the manager keeps track of, the parameter file can be expanded on.
Listing 3-7 shows a detailed version of the manager parameter file with additional details.
Detailed Manager Parameter File
PORT 15000
DYNAMICPORTLIST 15010-15035
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2
AUTORESTART ER *, RETRIES 6, WAITMINUTES 2, RESETMINUTES 30
LAGCRITICALSECONDS 30
LAGREPORTMINUTES
In this version of the manager parameter file, you see there are more parameters added to tell the manager what to do with old trail files (PURGEOLDEXTRACTS), when to restart (AUTORESTART) the other processes if they are terminated abnormally, and when to check for lag (LAGCRITITCALSECONDS/LAGREPORTMINUTES). You will also notice there is a parameter to use dynamic ports. The DYNAMICPORTLIST parameter is used to help Oracle GoldenGate communicate beyond firewalls if needed and limits port allocations to specific ranges for communication. This is helpful, because Oracle GoldenGate will grab random unused ports otherwise.
