This section describes the concept behind the configuration of GoldenGate and how to set up data replication. The configuration examples are of a basic level and are based on the Oracle Scott/Tiger schema. They do not necessarily represent a production environment. Chapter 7, Advanced Configuration provides greater detail.
You may also wish to refer to the Oracle GoldenGate Reference Guide 10.4 to support your understanding of commands and parameters.
The following steps create a simple GoldenGate unidirectional source-to-target configuration, where data is replicated from the SRC schema in the OLTP database on dbserver1, to the TGT schema in the OLAP database on dbserver2.
Before any configuration can take place, we need to create a GoldenGate Administrator user account on both source and target databases. This account provides access to the database tables for GoldenGate configuration and runtime operations.
Log on to each database as SYSDBA and issue the following commands:
[oracle@dbserver1 ggs]$ sqlplus '/as sysdba' SQL> create user ggs_admin identified by ggs_admin; User created. SQL> grant dba to ggs_admin; Grant succeeded.
You will notice that the DBA Role has been granted to the GGS_ADMIN database user. This is deliberate because of the high level of database access required. However, the GoldenGate installation and setup guide lists the minimum individual roles and privileges required against each process, which also command a high privilege.
GoldenGate configuration starts with the Manager process. The first parameter file to create is the mgr.prm file. This file is implicitly created in the dirprms subdirectory by typing the following command on the GGSCI command line:
GGSCI (dbserver1) 1> EDIT PARAM MGR
The EDIT command invokes your default editor. In the case of Linux, that will be the vi editor. The following is a typical GoldenGate Manager configuration for the source system, stored in the mgr.prm file. We will learn more about the Manager process parameter file contents in the Chapter 4 , Configuring Oracle GoldenGate.
-- GoldenGate Manager parameter file PORT 7809 PURGEOLDEXTRACTS ./dirdat/sa*, USECHECKPOINTS, MINKEEPHOURS 2
A double hyphen (--) prefix allows comments to be placed in the GoldenGate parameter files.
A period (.) depicts the GoldenGate home directory.
The Manager process must be configured on both source and target systems and must be started before any other configuration tasks are performed in GGSCI.
GGSCI (dbserver1) 2> START MGR Manager started.
You will also notice that the GGSCI tool (Linux) includes the following useful information at the command prompt:
GGSCI (<hostname>) <command sequence number> >
The next parameter file to create is the Extract parameter file. Again, the file <group name>.prm is implicitly created in the dirprm subdirectory by typing the following command on the GGSCI command line. For example:
GGSCI (dbserver1) 3> EDIT PARAMS EOLTP01
The Extract process scans the database online or archived redo logs for committed transactions. Should your source Oracle database be using Automatic Storage Management (ASM) to store all its database files, GoldenGate will require access to the ASM disk groups to scan the logs in the Flash Recovery Area (FRA). In this case, the ASM SYS username and password will be required in the parameter file.
Tip
GoldenGate does not support OS authentication, which is the reason for the password to be hardcoded in its Extract process parameter files. However, this can be encrypted and is discussed in Chapter 5, Configuration Options. ASM itself necessitates a SYS password file.
The basic configuration consists of:
- The Extract group name
- The Oracle Database System ID (
ORACLE_SID) - The source database GoldenGate username and password
- The source trail file path and prefix
- The ASM SYS username and password (if the database is using ASM)
- The source table names
The following code demonstrates the basic configuration required for the Extract process. Our source system's Oracle System ID is OLTP, set by the SETENV parameter. We also need to include the database user login information for the GoldenGate administrator user; ggs_admin.
-- Change Data Capture parameter file to extract -- OLTP table changes -- EXTRACT EOLTP01 SETENV (ORACLE_SID=OLTP) USERID ggs_admin, PASSWORD ggs_admin EXTTRAIL ./dirdat/sa TRANLOGOPTIONS ASMUSER SYS@ASM, ASMPASSWORD Password1 TABLE SRC.DEPT; TABLE SRC.EMP;
Now that we have created an Extract parameter file, the next step is to add the Extract to GoldenGate using GGSCI. The following example uses the Extract parameter file (EOLTP01.prm) shown in paragraph titled "The Extract Process":
[oracle@dbserver1 ggs]$ ggsci GGSCI (dbserver1) 1> add extract EOLTP01, tranlog, begin now, threads 1 EXTRACT added.
The previous GGSCI command string includes the TRANLOG keyword that tells GoldenGate to extract data from the source database's online redologs. The BEGIN NOW statement tells GoldenGate to start data replication immediately.
The next step is to define the local trail for the Extract process. The GGSCI command string below specifies a local trail having the prefix sa. Also, each trail file associated with the EOLTP01 Extract process will be a maximum of 50MB in size:
GGSCI (dbserver1) 2> add exttrail ./dirdat/sa, extract EOLTP01, megabytes 50 EXTTRAIL added.
Before starting the Extract process, let's define its associated Data Pump process, ensuring that the sa trail prefix is specified. The example below uses the Extract parameter file EPMP01.prm shown in paragraph titled "The Data Pump Process":
GGSCI (dbserver1) 3> add extract EPMP01, exttrailsource ./dirdat/sa, begin now EXTRACT added.
Now that we have an Extract and Data Pump process defined and configured, we can start them:
GGSCI (dbserver1) 4> start extract EOLTP01 Sending START request to MANAGER ... EXTRACT EOLTP01 starting GGSCI (dbserver1) 5> start EXTRACT EPMP01 Sending START request to MANAGER ... EXTRACT EPMP01 starting
To view the status of the Extract process, use the following command:
GGSCI (dbserver1) 6> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EOLTP01 00:00:00 00:00:02 EXTRACT RUNNING EPMP01 00:00:00 00:00:00
If you plan to use a Data Pump process, which is highly recommended, you need to create a Data Pump parameter file on the source system. Create the file <group name>.prm by typing the following command on the GGSCI command line. For example:
GGSCI (dbserver1) 4> EDIT PARAMS EPMP01
The Data Pump process is in essence an Extract process that sends changed data to the target system. The basic configuration consists of:
- The Data Pump name
- The data processing option (
PASSTHRUorNOPASSTHRU) - The target hostname
- The target trail file path and prefix
- The Manager port TCP/IP port number
- The source table names
The following code demonstrates the basic configuration required for the Data Pump process. We have chosen to use the PASSTHRU parameter ensuring that the data is propagated to the target host without any manipulation.
-- Data Pump parameter file to read the local -- trail of table changes -- EXTRACT EPMP01 PASSTHRU RMTHOST dbserver2, MGRPORT 7809 RMTTRAIL ./dirdat/ta TABLE SRC.DEPT; TABLE SRC.EMP;
To use a Replicat process, you need to create a Replicat parameter file on the target system. Create the file <group name>.prm by typing the following command on the GGSCI command line. For example:
GGSCI (dbserver2) 1> EDIT PARAMS ROLAP01
The Replicat process reads the target trail files and converts the GoldenGate messages to DML or DDL and applies the changes to the target database. The basic configuration consists of:
- The Replicat group name
- The Oracle Database System ID (
ORACLE_SID) - The target database GoldenGate username and password
- The target trail file path and prefix
- The discarded data file (data records that suffer an error during apply)
- The mapping information between source table and target table
The following code demonstrates the basic configuration required for the Replicat process. Similar in structure to the Extract process, we have defined a process name, the target database's Oracle System ID, which is OLAP, the GoldenGate user login credentials and a discard file. We have also defined the mapping between source and target tables.
-- Replicator parameter file to apply changes -- to tables -- REPLICAT ROLAP01 SETENV (ORACLE_SID=OLAP) USERID ggs_admin, PASSWORD ggs_admin DISCARDFILE ./dirrpt/rolap01.dsc, PURGE MAP SRC.DEPT, TARGET TGT.DEPT; MAP SRC.EMP, TARGET TGT.EMP;
Perform the Replicat process creation on the target system. The example below uses the Replicat parameter file ROLAP01.prm shown in paragraph titled "The Replicat Process":
[oracle@dbserver2 ggs]$ ggsci GGSCI (dbserver2) 1> add replicat ROLAP01, exttrail ./dirdat/ta REPLICAT added.
Note that the above GGSCI command string includes the Replicat group name ROLAP01 and the ta remote trail prefix as specified in the Data Pump parameter file EPMP01.prm.
Now start the newly created Replicat process:
GGSCI (dbserver2) 2> start replicat ROLAP01 Sending START request to MANAGER ... REPLICAT ROLAP01 starting GGSCI (dbserver2) 3> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING ROLAP01 00:00:00 00:00:02
