Configuring Integrated Extract and Integrated Replicat Using Oracle GoldenGate 12c on Linux
Overview
- Reads the Oracle GoldenGate trail.
- Performs data filtering, mapping, and conversion.
- Constructs logical change records (LCR) that represent source database DML transactions (in committed order). DDL is applied directly by Replicat.
- Attaches to a background process in the target database known as a database inbound server by means of a lightweight streaming interface.
- Transmits the LCRs to the inbound server, which applies the data to the target database.
- ogguser - Password: Welcome1. The Oracle GoldenGate user. This user has DBA privileges and its schema does not contain replication objects. Oracle GoldenGate uses this user to perform replication from the source schema to the target schema.
- oggsrc - Password: Welcome1. The replication source schema. This user only has connect and resource privileges and its schema defines all replication source objects.
- oggtrg - Password: Welcome1. The replication target shcema. This user only has connect and resource privileges and its schema defines all replication target objects.
- Creating the necessary database schemas and objects to prepare the environment for replication.
- Configuring an Integrated Extract to ensure data capture for the oggsrc schema.
- Configuring a data pump Extract to provide data capture for the oggsrc
- Configuring an Integrated Replicat to ensure data delivery to the oggtrg schema.
- Generating same database activity inserting and updating rows in the replication source schema (oggsrc.)
- Verifying that the data is replicated as expected.
- Generating some statistics from the Extract and Replicat processes.
- One Linux host: it can be a virtual machine. This OBE uses Oracle Linux OL 6.5, 64-bit.
- A different version of the Linux OS can also be used, as long as it is supported by Oracle Corporation such as RedHat and RedHat-based distributions. CentOS can also be used.
- Oracle GoldenGate on Oracle, Linux-64 ( Oracle Software Delivery Cloud ), version 12.1.2.0.0, part number V40146-01. This is the part number for 64-bit Linux.
- Oracle 12cR1 Database installed on the host.
- Oracle DDL and DML files needed by this OBE, which can be
downloaded here.
- Have root access to the Linux environment
- Have web access to download the software and documentation.
- Oracle Database 12c
- A database instance, OGG12 is created and running.
- Its datafiles are stored in the /u02/oradata directory.
- The SYS and SYSTEM users share the same password, "oracle."
- The Oracle GoldenGate software is installed in two different directories:
- The Oracle Goldengate replication source environment resides in /u01/app/oracle/product/ogg_src.
- The Oracle Goldengate replication target environment resides in /u01/app/oracle/product/ogg_trg.
- Creating the Oracle Database Release 12c (12.1.0.1.0) Instance for Data Synchronization Using Oracle GoldenGate 12c on Linux
- Installing Oracle GoldenGate for Oracle 12.1.2 on Linux EL 6.x/RHEL 6.x/CentOS 6.x
- Replication Source, the red environment
- Replication Target, the green environment
- Some_Command
- You type this as a command or a value. Example:
Enter ./ggsci to start the command line interpreter.
- Some_Prompt
- The system responds with this as a prompt or reply.
Example:
After the welcome splash banner, you can enter commands at the GGSCI (host01) 1> prompt.
- Some_Button
- Click this on-screen button. Example:
After selecting the version you want, click Continue to start the download.
- Some_Variable
- A variable that you substitute with a real value. Example:
Enter your userid/password at the prompt.
- Some_Filename
- A filename, path, or folder/directory. Example:
Edit the hosts file in the /etc directory.
- Some_Code
- A keyword or code element. Example:
Change the parameter HandleCollisions to NoHandleCollisions after the initial load.
- p=process=
- e(xtract), p(ump), r(eplicat), i(nitial), d(efgen), s(tartup).
- xxxx=project=
- All files related to a common project xxxx, for example hr, sales, engr.
- hh=host-to-host=
- aa, ab, ba, bb, as indicated by source and target host names where a=host01, and b=host02. Later on you will see that you cannot use numbers as part of some file names, so better not to go there at all.
- ext=extension=
- prm=parameter (stored in dirprm/), dsc=discard, rpt=report (stored in dirrpt/), def=definition (stored in dirdef/), oby=obey (stored in installation directory), sql=SQL (stored in dirsql/).
Purpose
This Oracle-By-Example (OBE) tutorial covers configuring Oracle GoldenGate 12c data capture and data delivery, using Integrated Extract and Integrated Replicat.
Time to Complete
Approximately 45 minutes.
Introduction
Oracle GoldenGate provides very fast replication of heterogeneous databases by reading transaction logs and writing the changes to one or more target databases. Oracle GoldenGate 11g introduced the Integrated Extract feature; Integrated Replicat was delivered with Oracle GoldenGate 12c.
In integrated capture mode, the Oracle GoldenGate Extract process interacts directly with the database log mining server which mines or reads the database redo log files and captures the changes in the form of Logical Change Records (LCRs.) LCRs are written to the GoldenGate trail files.
In integrated mode, the Replicat process leverages the apply processing functionality that is available within the Oracle database. In this mode, Replicat operates as follows:
This OBE instructs on how to configure data capture using
Integrated Extract and how to configure data delivery using
Integrated Replicat.
Scenario
There is one Linux host (or one Virtual Machine) running a 64 bit version of Linux (OL 6.x,) which runs the Oracle RDBMS 12cR1 (12.1.0). Two instances of the Oracle GoldenGate 12c software run on the same host, one which simulates the replication source and the second which simulates the replication target.
The Oracle RDBMS instance ogg12 contains three users/schemas:
The figure below gives a pictorial representation of the OBE environment from a logical standpoint:
NOTE: please pay attention to the color of the screen banners to know which commands are going to which environment. The convention is that the replication source environment has red banners, while the replication target environment has green banners. Also note whether you are entering GGSCI commands, SQL commands, or OS commands (the prompt at the beginning of each line will guide you).
The Oracle RDBMS must be installed in "standard" directory locations, according to the Optimal Flexible Architecture (OFA) guidelines. ORACLE_HOME points to /u01/app/oracle/product/12.1.0/dbhome_1.
Hardware and Software Requirements
The following is a list of hardware and software requirements:
Hardware
Software
Prerequisites
Before starting this tutorial, you should:
IMPORTANT: This OBE assumes the presence on the computer of the software listed below:
There are two OBEs in this section (Oracle Learning Library - Oracle GoldenGate) which cover creating the Oracle Database instance and installing the Oracle Goldengate software:
Before starting this OBE you are strongly advised to either complete the two OBEs mentioned above or, at least, make sure that you have a running Oracle database instance called OGG12. The instance parameter STREAMS_POOL_SIZE should be set to at least 512M and the instance must be running in ARCHIVELOG mode. In addition, this OBE assumes that the Oracle GoldenGate software is installed in the two directories /u01/app/oracle/product/ogg_src and /u01/app/oracle/product/ogg_trg.
Bear in mind that there is one host, but there are two environments:
Each environment has three windows, to allow interaction with the OS, the Oracle GoldenGate Software Command Interface (GGSCI) and SQL, through sqlplus. That makes six different places in which you could be typing! Try to be extra careful about which command you enter in which location. The wrong command in the wrong context is the most common error.
Typographic Conventions
Text color and font in the directions and in the screens should be interpreted as follows:
File and Process Naming Conventions
File and process naming conventions can be whatever works for
you or your company. Here are the sample conventions used in
this OBE:
pxxxxhh.ext
where:
1. Creating and Preparing the Database Users and Schemas for Replication.
- The user ogguser. This user has a heightened level of privileges to allow the Oracle GoldenGate software to perform replication.
- The user oggsrc. This is the unprivileged replication source user.
- The user oggtrg. This is the unprivileged replication target user.
- ECONOMIC_ENTITY
- GDP_BY_YEAR
- GDP_GROWTH_BY_YEAR
- ECONOMIC_ENTITY
- GDP_BY_YEAR
- GDP_GROWTH_BY_YEAR
You must create three users on the ogg12 database:
Additionally, you must download and unpack the SQL scripts used by this OBE to create database objects and data to be replicated between the replication source and the replication target.
1.1 Creating Terminal Shells for "root", "oggsrc" and "oggtrg."
Login to the Linux computer using a Gnome environment connected as oracle.
1.2 Creating Directories for the database files.
Create the containers for the database files. You will create two tablespaces, the first to host the objects for the replication source, and the second to host the replicated objects on the replication target.
Select the "ROOT" window and use the su command to acquire superuser privileges. As per OBE requirements, the password for the OS user "root" is "oracle.
| Host01 - ROOT |
[root@host01 ~]# su - Password: ****** |
Create the /u02/oradata/ogg12/obe directory. Change ownership of the newly created directory to the oracle user.
| Host01 - ROOT |
[root@host01 ~]# mkdir -p /u02/oradata/ogg12/obe [root@host01 ~]# chown oracle.oinstall /u02/oradata/ogg12/obe |
1.3 Creating database tablespaces and users.
Select the OGGSRC window. Using sqlplus connect to the Oracle database as sysdba. Verify that the database is running in ARCHIVELOG mode.
| Host01 - OGGSRC |
[oracle@host01 ~]$ export ORACLE_SID=ogg12 [oracle@host01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 2 13:28:59 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> SELECT log_mode FROM v$database; LOG_MODE ------------ ARCHIVELOG SQL> |
The result should be ARCHIVELOG. You specified ARCHIVELOG mode when you created the database using DBCA. If you did not create the database in ARCHIVELOG mode, do the following:
| Host01 - OGGSRC |
LOG_MODE ------------ NOARCHIVELOG SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2290416 bytes Variable Size 1744833808 bytes Database Buffers 385875968 bytes Redo Buffers 4886528 bytes Database mounted. SQL> ALTER DATABASE ARCHIVELOG; Database altered. SQL> ALTER DATABASE OPEN; Database altered. SQL> SELECT log_mode FROM v$database; LOG_MODE ------------ ARCHIVELOG SQL> |
Create two tablespaces, SRCDATA to host the replication source database objects, and TRGDATA to host the replication target database objects.
| Host01 - OGGSRC |
SQL> create tablespace srcdata datafile '/u02/oradata/ogg12/obe/srcdata01.dbf' size 50M autoextend on extent management local uniform size 256k; Tablespace created. SQL> create tablespace trgdata datafile '/u02/oradata/ogg12/obe/trgdata01.dbf' size 50M autoextend on extent management local uniform size 256k; Tablespace created. SQL> |
Create an Oracle GoldenGate user that can connect to the source and target databases for transactional data (OGGUSER). An Oracle GoldenGate user requires a database user with at least the following privileges:
|
User Privilege |
Extract (Source Side) |
Replicat (Target Side) |
|
CREATE
SESSION, ALTER SESSION |
X |
X |
|
RESOURCE
|
X |
X |
|
SELECT ANY DICTIONARY |
X |
X |
|
FLASHBACK
ANY TABLE or FLASHBACK ON <owner.table> |
X |
|
|
SELECT
ANY TABLE or SELECT ON <owner.table> |
X |
X |
|
INSERT, UPDATE, DELETE ON <target tables> |
|
X |
|
CREATE TABLE |
|
X |
|
EXECUTE on DBMS_FLASHBACK package |
X |
|
In addition to the above privileges, you would normally run:
| Host01 - OGGSRC |
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('OGGUSER');
|
To learn about DBMS_GOLDENGATE_AUTH syntax, enter the following commands:
| Host01 - OGGSRC |
SQL> set pages 100 SQL> desc dbms_goldengate_auth SQL> SELECT text FROM all_source WHERE name='DBMS_GOLDENGATE_AUTH'; SQL> |
In order to save all this work and to keep the OBE simple, this step will "cheat" and assign the OGGUSER user the DBA privilege.
| Host01 - OGGSRC |
SQL> create user OGGUSER identified by Welcome1 default tablespace USERS temporary tablespace TEMP; User created. SQL> grant DBA to OGGUSER; Grant succeeded. SQL> |
Create the OGGSRC and OGGTRG database users. The default tablespace for OGGSRC is SRCDATA. The default tablespace for OGGTRG is TRGDATA. These two users only need the CONNECT and RESOURCE privileges. Oracle 12c removed the unlimited quota privilege from the RESOURCE role, so you must now grant quota unlimited to the user.
| Host01 - OGGSRC |
SQL> create user OGGSRC identified by Welcome1 default tablespace SRCDATA temporary tablespace TEMP; User created. SQL> grant CONNECT,RESOURCE to OGGSRC; Grant succeeded. SQL> alter user OGGSRC quota unlimited on SRCDATA; User altered. SQL> create user OGGTRG identified by Welcome1 default tablespace TRGDATA temporary tablespace TEMP; User created. SQL> alter user OGGTRG quota unlimited on TRGDATA; User altered. SQL> grant CONNECT,RESOURCE to OGGTRG; Grant succeeded. |
You must enable minimal supplemental logging by executing the following commands:
| Host01 - OGGSRC |
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. SQL> SELECT supplemental_log_data_min, force_logging FROM |
To ensure that the redo and archive logs contain supplemental log data, switch the logs by executing the following command:
| Host01 - OGGSRC |
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL>
|
1.4 Unloading and Unpacking the SQL scripts
| Host01 - OGGSRC |
SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@host01 Desktop]$ cd ~ [oracle@host01 ~]$ mkdir obe |
Download the OBE_DDL_FILES.zip file containing the sql scripts for this OBE.
Copy the zip file into the obe directory. The downloaded file is by default stored into the /home/oracle/Downloads directory.
| Host01 - OGGSRC |
[oracle@host01 ~]$ cp ./Downloads/OBE_DDL_FILES.zip ./obe
[oracle@host01 ~]$
|
In this example the OBE_DDL_FILES.zip file is downloaded by the browser into the /tmp directory. The obe directory is created under /home/oracle and then the zip file OBE_DDL_FILES.zip is extracted into the obe directory:
| Host01 - OGGSRC |
[oracle@host01 ~]$ cd obe [oracle@host01 obe]$ ls OBE_DDL_files.zip [oracle@host01 obe]$ unzip OBE_DDL_FILES.zip Archive: OBE_DDL_files.zip inflating: drop_tables.sql inflating: economic_entity.sql inflating: gdp_by_year.sql inflating: gdp_by_year_2008.sql inflating: gdp_by_year_2009.sql inflating: gdp_by_year_2010.sql inflating: gdp_growth_by_year.sql inflating: gdp_growth_by_year_2008.sql inflating: gdp_growth_by_year_2009.sql inflating: gdp_growth_by_year_2010.sql inflating: oracle_table_creation.sql [oracle@ologg obe]$ |
1.5 Creating Database Objects and Loading Data
Select the OGGSRC window. Make sure your default directory is /home/oracle/obe and launch sqlplus, connecting to the replication source user (OGGSRC.)
| Host01 - OGGSRC |
[oracle@host01 obe]$ pwd /home/oracle/obe [oracle@host01 obe]$ sqlplus oggsrc/Welcome1@ogg12 SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 2 16:17:21 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> |
Run the the oracle_table_creation.sql script to create the required database objects. Make sure three tables have been created:
| Host01 - OGGSRC |
SQL> @oracle_table_creation.sql Table created. Table created. Table created. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- ECONOMIC_ENTITY GDP_BY_YEAR GDP_GROWTH_BY_YEAR SQL> |
Run the the economic_entity.sql
script to populate the ECONOMIC_ENTITY
table.
| Host01 - OGGSRC |
SQL> @economic_entity.sql 1 row created. 1 row created. |
Select the OGGTRG window. Make sure your default directory is /home/oracle/obe and launch sqlplus, connecting to the replication target user (OGGTRG.)
| Host01 - OGGTRG |
[oracle@host01 Desktop]$ pwd /home/oracle/obe [oracle@host01 obe]$ sqlplus oggtrg/Welcome1@ogg12 SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 2 16:17:21 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> |
Run the the oracle_table_creation.sql script to create the required database objects in the replication target schema. Make sure three tables have been created:
| Host01 - OGGTRG |
SQL> @oracle_table_creation.sql Table created. Table created. Table created. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- ECONOMIC_ENTITY GDP_BY_YEAR GDP_GROWTH_BY_YEAR SQL> |
Run the the economic_entity.sql
script to populate the ECONOMIC_ENTITY
table.
| Host01 - OGGTRG |
SQL> @economic_entity.sql 1 row created. 1 row created. |
2. Preparing the Oracle GoldenGate Environment for Replication.
- OGGSRC
- OGGTRG
- GGSCI_SRC
- GGSCI_TRG
- ROOT
- DynamicPortList
- PurgeOldExtracts
- Autostart
- AutoRestart
In this section you use the Oracle GoldenGate Software Command Interface (GGSCI) to set the parameters for the Oracle GoldenGate manager processes, and you create a password wallet to avoid typing passwords in clear (and in the various scripts.) Later, you use the Oracle GoldenGate TRANDATA option to enable additional logging, needed by the software to uniquely identify each row that has been changed in the database.
2.1 Configuring the Oracle GoldenGate Manager Process.
Create two additional terminal windows and name them GGSCI_SRC and GGSCI_TRG. Drag them so that they are aligned with the existing window on your Gnome workspace. You should have five active windows aligned as shown below:
IMPORTANT! GGSCI for Oracle will not start if the LD_LIBRARY_PATH environment variable is either undefined or if it does not point to the ${ORACLE_HOME}/lib directory.
Make sure LD_LIBRARY_PATH is correctly set before launching GGSCI. The preferred way to accomplish this is by adding the line:
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:$LD_LIBRARY_PATH
to the file ~/.bashrc and source that file. Change directory to the directory where the Oracle GoldenGate software has been installed (replication source, /u01/app/oracle/product/ogg_src) and launch ggsci. Enter the info mgr command to verify that the manager is running.
| Host01 - GGSCI_SRC |
[oracle@host01 Desktop]$ cd /u01/app/oracle/product/ogg_src/ [oracle@host01 ogg_src]$ pwd /u01/app/oracle/product/ogg_src [oracle@host01 ogg_src]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54 Operating system character set identified as UTF-8. Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (host01.example.com) 1> info mgr Manager is running (IP port ologg.example.com.7809, Process ID 17583). GGSCI (host01.example.com) 2> |
| Host01 - GGSCI_TRG |
[oracle@host01 Desktop]$ cd /u01/app/oracle/product/ogg_trg/ [oracle@host01 ogg_trg]$ pwd /u01/app/oracle/product/ogg_trg [oracle@host01 ogg_trg]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54 Operating system character set identified as UTF-8. Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (host01.example.com) 1> info mgr Manager is running (IP port ologg.example.com.7909, Process ID 13645). GGSCI (host01.example.com) 2> |
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) 2> edit param mgr
PORT 7809
DynamicPortList 20000-20099
PurgeOldExtracts ./dirdat/*, UseCheckPoints, MinKeepHours 2
Autostart Extract E*
AUTORESTART Extract *, WaitMinutes 1, Retries 3
|
Select the GGSCI_TRG window. Add the same parameters you just added for the replication source. In this case, however, you set the auto start and auto restart parameters for Replicats, rather than Etxracts.
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) 2> edit param mgr
Port 7909
DynamicPortList 20100-20199
PurgeOldExtracts ./dirdat/pe*, UseCheckPoints, MinKeepHours 2
Autostart Replicat R*
AUTORESTART Replicat *, WaitMinutes 1, Retries 3
|
Select the GGSCI_SRC window. Stop and restart the manager to force it to re-read the newly defined parameters.
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) 3> stop mgr Manager process is required by other GGS processes. Are you sure you want to stop it (y/n)?y Sending STOP request to MANAGER ... Request processed. Manager stopped. GGSCI (host01.example.com) 4> start mgr Manager started. GGSCI (host01.example.com) 5> info mgr detail Manager is running (IP port host01.example.com.7809, Process ID 52948). GGSCI (host01.example.com) 6> |
In your environment the process id will obviously be different.
Select the GGSCI_TRG window. Stop and restart the manager to force it to re-read the newly defined parameters.
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) 3> stop mgr Manager process is required by other GGS processes. Are you sure you want to stop it (y/n)?y Sending STOP request to MANAGER ... Request processed. Manager stopped. GGSCI (host01.example.com) 4> start mgr Manager started. GGSCI (host01.example.com) 5> info mgr detail Manager is running (IP port host01.example.com.7909, Process ID 53712). GGSCI (host01.example.com) 6> |
In your environment the process id will obviously be different.
2.2 Using the Oracle GoldenGate Wallet Facility.
To avoid storing passwords in clear text in the various Extract and Replicat parameter files, OracleGoldenGate 12c offers the new wallet facility. You can store encrypted credentials in the wallet credential store and refer them through an alias, rather than the combination username/password.
Select the GGSCI_SRC window. Create the wallet, add a credential store to it and add the user credentials for the OGGUSER user.
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) 4> Create Wallet Created wallet at location 'dirwlt'. Opened wallet at location 'dirwlt'. GGSCI (host01.example.com) 5> Add CredentialStore Credential store created in ./dircrd/. GGSCI (host01.example.com) 6> Alter CredentialStore Add User ogguser@ogg12 Password Welcome1 Alias ogg_user Credential store in ./dircrd/ altered. GGSCI (host01.example.com) 7> Info CredentialStore Reading from ./dircrd/: Domain: OracleGoldenGate Alias: ogg_user Userid: ogguser@ogg12 GGSCI (host01.example.com) 8> |
Make sure the Oracle GoldenGate user can connect to the Oracle database using a credential alias.
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) 8> DBLogin UserIDAlias ogg_user
Successfully logged into database.
GGSCI (host01.example.com) 9>
|
Select the GGSCI_TRG window. Leave ggsci. At the OS prompt, copy the wallet and credential files from GGSCI_SRC to GGSCI_TRG.
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) 4> exit [oracle@host01 ogg_trg]$ cp /u01/app/oracle/product/ogg_src/dircrd/* ./dircrd [oracle@host01 ogg_trg]$ ls ./dircrd cwallet.sso [oracle@host01 ogg_trg]$ cp /u01/app/oracle/product/ogg_src/dirwlt/* ./dirwlt [oracle@host01 ogg_trg]$ ls ./dirwlt cwallet.sso [oracle@host01 ogg_trg]$ |
Launch ggsci again and make sure the Oracle GoldenGate user can connect to the Oracle database using a credential alias also from the replication target instance.
| Host01 - GGSCI_TRG |
[oracle@host01 ogg_trg]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54 Operating system character set identified as UTF-8. Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (host01.example.com) 1> DBLogin UserIDAlias ogg_user Successfully logged into database. GGSCI (host01.example.com) 2> |
2.3 Using the Oracle GoldenGate TranData Option.
You must enable supplemental logging on the source database for the specified tables. Before you can start capturing real-time data, the Oracle database must be set to log the table key values whenever it logs a rows change, so that they are available to Oracle GoldenGate in Redo. By default, the database logs only those column values that change. This is required so Oracle GoldenGate can locate the correct row on the target for update and delete operations.
Select the GGSCI_SRC window. The last command entered was a succesful database connection for the Oracle GoldenGate user OGGUSER. Enter the Add TranData command for all tables defined in the OGGSRC schema.
| Host01 - GGSCI_SRC |
Successfully logged into database. GGSCI (host01.example.com) 9> Add TranData OGGSRC.* ALLCOLS TRANDATA for scheduling columns has been added on table 'OGGSRC.ECONOMIC_ENTITY'.TRANDATA |
3. Configuring Data Capture Using Integrated Extract
- Automatically includes in the trail record the before image for UPDATE operations.
- Automatically includes in the trail record the before image of all supplementally logged columns for both UPDATE and DELETE operations
Data capture, also known as Extract, is done on the source side. A primary extract is required; a secondary extract, known as a Data Pump, is optional but highly recommended. To configure data capture, specifically an Integrated Extract, perform the following steps:
3.1 Configuring the Primary Integrated Extract on the Oracle source schema
Select the GGSCI_SRC window to create the primary Extract parameter file.
Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/einta.prm. The path and extension will be added automatically.
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) 2> edit param einta
Extract einta
SETENV (ORACLE_SID='ogg12')
UserIdAlias ogg_user
TranlogOptions IntegratedParams (max_sga_size 256)
Exttrail ./dirdat/in
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
Table OGGSRC.*;
|
Save the parameter file and leave the editor. Note the two parameters LOGALLSUPCOLS and UPDATERECORDFORMAT. The Integrated Replicat, which you are going to configure shortly, requires the source extract parameter file to contain these new parameters introduced in 12c.
LOGALLSUPCOLS causes Extract to do the following with these supplementally logged columns:
When two records are generated for an update to a single row, it incurs additional disk I/O and processing for both Extract and Replicat. If supplemental logging is enabled on all columns, the unmodified columns may be repeated in both the the before and after records. The overall size of the trail is larger, as well. This overhead is reduced by using UPDATERECORDFORMAT COMPACT
Login to the database, register the Integrated Extract and create the Extract group and the local Extract trail file.
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) >dblogin useridalias ogg_user Successfully logged into database. GGSCI (host01.example.com) > register extract einta database Extract EINTA successfully registered with database at SCN 1905933. GGSCI (host01.example.com) > add extract einta, integrated tranlog, begin now |
Your SCN will be different. The Megabytes 10 is optional. The default is 100 Megabytes.
The primary Extract has been created and configured, but not started. Leave GGSCI running for the next step.
3.2 Configuring the Data Pump
Create the secondary Extract (data pump) parameter file.
Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/pinta.prm. The path and extension will be added automatically.
The remote host RmtHost
is host01 in the
example. If you are running this OBE in one
environment simulating both source and target hosts,
be sure to have host01
defined in /etc/hosts,
pointing to 127.0.0.1. Alternatively, use localhost.
Perform a ping
host01
to make sure that the address is correctly resolved to
127.0.0.1.
If the hostname host01
cannot be resolved into a valid IP Address, the
Extract pump won't start.
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) > Edit Param pinta Extract pinta SETENV (ORACLE_SID='ogg12') UserIdAlias ogg_user rmthost localhost, mgrport 7909 rmttrail ./dirdat/pn table OGGSRC.*; |
Save the file leaving the editor. You can check your work by entering View Param pinta any time.
Create the data pump group and the remote Extract trail file.
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) > add extract pinta, exttrailsource ./dirdat/in EXTRACT added. GGSCI (host01.example.com) > add rmttrail ./dirdat/pn, extract pinta, megabytes 10 RMTTRAIL added. GGSCI (host01.example.com) > |
The Megabytes 10 is optional. The default is 100 Megabytes.
The data pump reads from the local trail file in and writes
to the remote trail file pn.
The remote trail file that will be created will be
named dirdat/pn000000,
then when that one fills up the next will be dirdat/pn000001,
then dirdat/pn000002,
and so on.
The secondary Extract has been created and configured, but not started. Leave GGSCI running for the next step.
3.3 Starting the Primary Extract and the Data Pump Processes
Start the two Extract processes Einta and Pinta.
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) > start Extract einta Sending START request to MANAGER ... EXTRACT EINTA starting GGSCI (host01.example.com) > start extract pinta Sending START request to MANAGER ... EXTRACT PINTA starting GGSCI (host01.example.com) > |
Enter the Info All command to assess the status of the two Extract processes. It is not unusual for the Integrated Extract to take a few seconds to start. In this case, the status for the Integrated Extract is STARTING.
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STARTING EINTA 00:00:00 20:43:37 EXTRACT RUNNING PINTA 00:00:00 00:00:03 GGSCI (host01.example.com) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STARTING EINTA 00:00:00 20:43:41 EXTRACT RUNNING PINTA 00:00:00 00:00:07 GGSCI (host01.example.com) > Info All Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EINTA 20:44:16 00:00:01 EXTRACT RUNNING PINTA 00:00:00 00:00:03 GGSCI (host01.example.com) > |
Make sure both Extract processes are in a RUNNING status before going to the next step.
Leave GGSCI running for the next steps.
4. Configuring Data Delivery Using Integrated Replicat
When the replication target is a database, data delivery is
accomplished by a Replicat process. This OBE simulates
replication between
two schemas (oggsrc, the
replication source and oggtrg,
the replication target ) of the same database (ogg12.)
To configure data delivery, perform the following steps:
4.1 Configuring the Replicat process
Select the GGSCI_TRG window. Using the naming conventions discussed in the Overview, the path/filename.ext will be dirprm/rinta.prm. The path and extension will be added automatically.
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) >Edit Param rinta
|
Leave the editor saving the file rinta.prm.
The parameter DBOPTIONS INTEGRATEDPARAMS(parallelism 6) denotes that for this Integrated Replicat, you are specifying that the minimum number of parallel apply processes will be 6.
Connect to the database and add the Replicat rinta, connecting it to the Exttrail ./dirdat/pn
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) > DBlogin UserIdAlias ogg_user Successfully logged into database. GGSCI (host01.example.com) > Add Replicat rinta Integrated exttrail ./dirdat/pn REPLICAT (Integrated) added. GGSCI (host01.example.com) > Start Replicat rinta Sending START request to MANAGER ... REPLICAT RINTA starting GGSCI (host01.example.com) > info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RINTA 00:00:00 00:00:39 GGSCI (host01.example.com) > |
4.2 Obtaining Information About All Processes
Select the GGSCI_TRG window. You already used the simple Info All command. To display more information about the rinta Replicat process you can enter the command Info rinta:
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) > Info rinta
REPLICAT RINTA Last Started 2014-02-03 22:34 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 50022
Log Read Checkpoint File ./dirdat/pn000000
First Record RBA 0
GGSCI (host01.example.com) >
|
The output specifies that rinta is an Integrated Replicat process. You can also enter "Info rinta, Detail" to display the most detailed information.
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) > Info rinta, Detail
REPLICAT RINTA Last Started 2014-02-03 22:34 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 50022
Log Read Checkpoint File ./dirdat/pn000000
First Record RBA 0
INTEGRATED Replicat
DBLOGIN Provided, inbound server name is OGG$RINTA in ATTACHED state
Current Log BSN value: <NULL>
Extract Source Begin End
./dirdat/pn000000 * Initialized * First Record
./dirdat/pn000000 * Initialized * First Record
./dirdat/pn000000 * Initialized * First Record
Current directory /u01/app/oracle/product/ogg_trg
Report file /u01/app/oracle/product/ogg_trg/dirrpt/RINTA.rpt
Parameter file /u01/app/oracle/product/ogg_trg/dirprm/rinta.prm
Checkpoint file /u01/app/oracle/product/ogg_trg/dirchk/RINTA.cpr
Process file /u01/app/oracle/product/ogg_trg/dirpcs/RINTA.pcr
Error log /u01/app/oracle/product/ogg_trg/ggserr.log
GGSCI (host01.example.com) >
|
In all cases, the Status should be RUNNING, and the time since the last update or checkpoint should be under 10 seconds.
Select the OGGSRC window, where sqlplus is running connected to the replication source schema. Connect as sysdba to be able to access the data dictionary tables. The rinta Replicat process is "Integrated," which means that the Oracle database knows about it. There are several Oracle GoldenGate views defined in the database data dictionary which can be queried to display information about Oracle GoldenGate queues and processes.
| Host01 - OGGSRC |
SQL> connect / as sysdba Connected. SQL> column replicat_name format a30 SQL> column server_name format a30 SQL> select replicat_name,server_name from DBA_GOLDENGATE_INBOUND; REPLICAT_NAME SERVER_NAME ----------------------------- ------------------------------ RINTA OGG$RINTA SQL> column apply_name format a30 SQL> column queue_name format a30 SQL> select apply_name,queue_name from DBA_APPLY; APPLY_NAME QUEUE_NAME ------------------------------ ------------------------------ OGG$RINTA OGGQ$RINTA OGG$EINTA OGG$Q_EINTA SQL> |
Select the GGSCI_SRC window. Display information about all processes on the replication source instance.
Display summary information:
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EINTA 00:00:05 00:00:01
EXTRACT RUNNING PINTA 00:00:00 00:00:05
GGSCI (host01.example.com) >
|
Display detailed information:
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) > Info Extract *
EXTRACT EINTA Last Started 2014-02-03 21:51 Status RUNNING
Checkpoint Lag 00:00:05 (updated 00:00:05 ago)
Process ID 49304
Log Read Checkpoint Oracle Integrated Redo Logs
2014-02-03 23:57:35
SCN 0.2032340 (2032340)
EXTRACT PINTA Last Started 2014-02-03 21:51 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 49316
Log Read Checkpoint File ./dirdat/in000000
First Record RBA 1429
GGSCI (host01.example.com) >
|
Similar to the Extract on the Green Host, the Status should be RUNNING, and the time since the last update or checkpoint should be under 10 seconds.
Display even more detailed information:
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) > Info Extract *, Detail
EXTRACT EINTA Last Started 2014-02-03 21:51 Status RUNNING
Checkpoint Lag 00:00:06 (updated 00:00:04 ago)
Process ID 49304
Log Read Checkpoint Oracle Integrated Redo Logs
2014-02-03 23:59:24
SCN 0.2032590 (2032590)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/in 0 1429 10 EXTTRAIL
Integrated Extract outbound server first scn: 0.1905933 (1905933)
Extract Source Begin End
Not Available 2014-02-03 01:07 2014-02-03 23:59
Not Available * Initialized * 2014-02-03 01:07
Current directory /u01/app/oracle/product/ogg_src
Report file /u01/app/oracle/product/ogg_src/dirrpt/EINTA.rpt
Parameter file /u01/app/oracle/product/ogg_src/dirprm/einta.prm
Checkpoint file /u01/app/oracle/product/ogg_src/dirchk/EINTA.cpe
Process file /u01/app/oracle/product/ogg_src/dirpcs/EINTA.pce
Error log /u01/app/oracle/product/ogg_src/ggserr.log
EXTRACT PINTA Last Started 2014-02-03 21:51 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 49316
Log Read Checkpoint File ./dirdat/in000000
First Record RBA 1429
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/pn 0 0 10 RMTTRAIL
Extract Source Begin End
./dirdat/in000000 * Initialized * First Record
./dirdat/in000000 * Initialized * First Record
Current directory /u01/app/oracle/product/ogg_src
Report file /u01/app/oracle/product/ogg_src/dirrpt/PINTA.rpt
Parameter file /u01/app/oracle/product/ogg_src/dirprm/pinta.prm
Checkpoint file /u01/app/oracle/product/ogg_src/dirchk/PINTA.cpe
Process file /u01/app/oracle/product/ogg_src/dirpcs/PINTA.pce
Error log /u01/app/oracle/product/ogg_src/ggserr.log
GGSCI (host01.example.com) >
|
Everything should show a status of RUNNING. The source tables are still empty. No data has flowed yet, nothing has replicated yet.
5. Generating Data and Testing Replication
There is a difference in how you replicate an existing table (more complicated) versus starting with a new empty table (simpler). To generate sample data against an empty set of tables, perform the following steps:
5.1 Generating INSERTs
Run the SQL script gdp_by_year_2008.sql, stored in the /home/oracle/obe directory, to INSERT rows.
Select the OGGSRC
window, where sqlplus
is running connected as sysdba. Leave sqlplus
and make sure that the default directory at the OS
prompt is /home/oracle/obe.
If /home/oracle/obe
is not yur default directory, change directory to /home/oracle/obe
before launching sqlplus.
Launch sqlplus
again, connecting to the Oracle database as OGGSRC.
Execute the gdp_by_year_2008.sql
script, then compute the number of rows in the table GDP_BY_YEAR.
| Host01 - OGGSRC |
SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@host01 obe]$ pwd /home/oracle/obe [oracle@host01 obe]$ sqlplus oggsrc/Welcome1@ogg12 SQL*Plus: Release 12.1.0.1.0 Production on Tue Feb 4 00:11:22 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Last Successful login time: Mon Feb 03 2014 23:21:44 +11:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> @gdp_by_year_2008.sql 1 row created. 1 row created. ... many lines omitted for clarity ... 1 row created. Commit complete. SQL> select count(*) from gdp_by_year; COUNT(*) ---------- 235 SQL> |
There should not be any errors. Leave SQL*Plus running for the next step.
Verify that the rows were inserted into the table in the replication target schema (oggtrg.) Select the OGGTRG window, which should already be running sqlplus connected to the Oracle database as OGGTRG. If that is not the case, launch sqlplus connecting to the Oracle database as OGGTRG.
| Host01 - OGGTRG |
SQL> show user USER is "OGGTRG" SQL> select count(*) from gdp_by_year; COUNT(*) ---------- 235 SQL> |
Verify that the source GGSCI Extract processes are still running.
Select the GGSCI_SRC window and enter the following command:
| Host02 - GGSCI_SRC |
GGSCI (host01.example.com) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EINTA 00:00:06 00:00:01
EXTRACT RUNNING PINTA 00:00:00 00:00:07
GGSCI (host01.example.com) >
|
If the Status
says ABENDED,
then check the process reports to see what the error
was.You should use the
command "view report
einta" or "view
report pinta" to find out what happened to
the Extract group.
Use the Stats
command to request the Extract processes EINTA
and PINTA to
display to the screen statistics
about their run so far:
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) > Stats einta Sending STATS request to EXTRACT EINTA ... Start of Statistics at 2014-02-04 00:28:42. Output to ./dirdat/in: Extracting from OGGSRC.GDP_BY_YEAR to OGGSRC.GDP_BY_YEAR: *** Total statistics since 2014-02-04 00:12:26 *** Total inserts 235.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 235.00 *** Daily statistics since 2014-02-04 00:12:26 *** Total inserts 235.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 235.00 *** Hourly statistics since 2014-02-04 00:12:26 *** Total inserts 235.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 235.00 *** Latest statistics since 2014-02-04 00:12:26 *** Total inserts 235.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 235.00 End of Statistics. GGSCI (host02.example.com) > Stats pinta Sending STATS request to EXTRACT PINTA ... Start of Statistics at 2014-02-04 00:29:20. Output to ./dirdat/pn: Extracting from OGGSRC.GDP_BY_YEAR to OGGSRC.GDP_BY_YEAR: *** Total statistics since 2014-02-04 00:12:34 *** Total inserts 235.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 235.00 *** Daily statistics since 2014-02-04 00:12:34 *** Total inserts 235.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 235.00 *** Hourly statistics since 2014-02-04 00:12:34 *** Total inserts 235.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 235.00 *** Latest statistics since 2014-02-04 00:12:34 *** Total inserts 235.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 235.00 End of Statistics. GGSCI (host01.example.com) > |
Verify that the target GGSCI Integrated Replicat process is still running.
Select the GGSCI_TRG window, GGSCI should still be running. Enter the following command:
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RINTA 00:00:00 00:00:05
GGSCI (host01.example.com) >
|
If the Status
says ABENDED,
then check the process reports to see what the error
was. You should use the command
view report rinta to find out what happened
to the Replicat group.
Use the Stats command to request the Replicat process RINTA to display to the screen statistics about its run so far:
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) > Stats rinta
Sending STATS request to REPLICAT RINTA ...
Start of Statistics at 2014-02-04 00:35:16.
Integrated Replicat Statistics:
Total transactions 1.00
Redirected 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from OGGSRC.GDP_BY_YEAR to OGGTRG.GDP_BY_YEAR:
*** Total statistics since 2014-02-04 00:12:58 ***
Total inserts 235.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 235.00
*** Daily statistics since 2014-02-04 00:12:58 ***
Total inserts 235.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 235.00
*** Hourly statistics since 2014-02-04 00:12:58 ***
Total inserts 235.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 235.00
*** Latest statistics since 2014-02-04 00:12:58 ***
Total inserts 235.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 235.00
End of Statistics.
GGSCI (host01.example.com) >
|
5.2 Generating UPDATEs/DELETEs
Select the OGGSRC
window, where sqlplus
is running connected to the replication source schema
( OGGSRC)
In the ECONOMIC_ENTITY
table all economic entities which are not single
countries, like "East Asia Less Japan" or "Other
Western Europe" are listed as "N/A"
in the CONTINENT
column. Change "N/A"
to "Not a continent".
In addition, the GDP_BY_YEAR
table contains economic data for the years 2008, which
was inserted to generate some replication activity.
Erase all entries pertaining to the year 2008 in the GDP_BY_YEAR table.
| Host01 - OGGSRC |
SQL> UPDATE ECONOMIC_ENTITY set CONTINENT = 'Not a continent' WHERE CONTINENT = 'N/A'; 36 rows updated. SQL> DELETE FROM GDP_BY_YEAR where GDP_YEAR=2008; 235 rows deleted. SQL> commit; Commit complete. SQL> |
Select the GGSCI_SRC window. Use the Stats command to request the Extract processes EINTA and PINTA to display to the screen statistics about their run so far:
| Host01 - GGSCI_SRC |
GGSCI (host01.example.com) > Stats einta Sending STATS request to EXTRACT EINTA ... Start of Statistics at 2014-02-04 00:49:37. Output to ./dirdat/in: Extracting from OGGSRC.GDP_BY_YEAR to OGGSRC.GDP_BY_YEAR: *** Total statistics since 2014-02-04 00:12:26 *** Total inserts 235.00 Total updates 0.00 Total deletes 235.00 Total discards 0.00 Total operations 470.00 *** Daily statistics since 2014-02-04 00:12:26 *** Total inserts 235.00 Total updates 0.00 Total deletes 235.00 Total discards 0.00 Total operations 470.00 *** Hourly statistics since 2014-02-04 00:12:26 *** Total inserts 235.00 Total updates 0.00 Total deletes 235.00 Total discards 0.00 Total operations 470.00 *** Latest statistics since 2014-02-04 00:12:26 *** Total inserts 235.00 Total updates 0.00 Total deletes 235.00 Total discards 0.00 Total operations 470.00 Extracting from OGGSRC.ECONOMIC_ENTITY to OGGSRC.ECONOMIC_ENTITY: *** Total statistics since 2014-02-04 00:12:26 *** Total inserts 0.00 Total updates 36.00 Total deletes 0.00 Total discards 0.00 Total operations 36.00 *** Daily statistics since 2014-02-04 00:12:26 *** Total inserts 0.00 Total updates 36.00 Total deletes 0.00 Total discards 0.00 Total operations 36.00 *** Hourly statistics since 2014-02-04 00:12:26 *** Total inserts 0.00 Total updates 36.00 Total deletes 0.00 Total discards 0.00 Total operations 36.00 *** Latest statistics since 2014-02-04 00:12:26 *** Total inserts 0.00 Total updates 36.00 Total deletes 0.00 Total discards 0.00 Total operations 36.00 End of Statistics. GGSCI (host01.example.com) > Stats pinta Sending STATS request to EXTRACT PINTA ... Start of Statistics at 2014-02-04 00:50:34. Output to ./dirdat/pn: Extracting from OGGSRC.GDP_BY_YEAR to OGGSRC.GDP_BY_YEAR: *** Total statistics since 2014-02-04 00:12:34 *** Total inserts 235.00 Total updates 0.00 Total deletes 235.00 Total discards 0.00 Total operations 470.00 *** Daily statistics since 2014-02-04 00:12:34 *** Total inserts 235.00 Total updates 0.00 Total deletes 235.00 Total discards 0.00 Total operations 470.00 *** Hourly statistics since 2014-02-04 00:12:34 *** Total inserts 235.00 Total updates 0.00 Total deletes 235.00 Total discards 0.00 Total operations 470.00 *** Latest statistics since 2014-02-04 00:12:34 *** Total inserts 235.00 Total updates 0.00 Total deletes 235.00 Total discards 0.00 Total operations 470.00 Extracting from OGGSRC.ECONOMIC_ENTITY to OGGSRC.ECONOMIC_ENTITY: *** Total statistics since 2014-02-04 00:12:34 *** Total inserts 0.00 Total updates 36.00 Total deletes 0.00 Total discards 0.00 Total operations 36.00 *** Daily statistics since 2014-02-04 00:12:34 *** Total inserts 0.00 Total updates 36.00 Total deletes 0.00 Total discards 0.00 Total operations 36.00 *** Hourly statistics since 2014-02-04 00:12:34 *** Total inserts 0.00 Total updates 36.00 Total deletes 0.00 Total discards 0.00 Total operations 36.00 *** Latest statistics since 2014-02-04 00:12:34 *** Total inserts 0.00 Total updates 36.00 Total deletes 0.00 Total discards 0.00 Total operations 36.00 End of Statistics. GGSCI (host01.example.com) > |
Verify that the target GGSCI Integrated Replicat process is still running.
Select the GGSCI_TRG window. Enter the following command:
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) > Info All
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RINTA 00:00:00 00:00:00
GGSCI (host01.example.com) >
|
If the Status
says ABENDED,
then check the process reports to see what the error
was. You should use the command
view report rinta to find out what happened
to the Replicat group
Use the Stats command to request the Replicat process RINTA to display to the screen statistics about its run so far:
| Host01 - GGSCI_TRG |
GGSCI (host01.example.com) > Stats rinta
Sending STATS request to REPLICAT RINTA ...
Start of Statistics at 2014-02-04 00:56:17.
Integrated Replicat Statistics:
Total transactions 2.00
Redirected 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from OGGSRC.GDP_BY_YEAR to OGGTRG.GDP_BY_YEAR:
*** Total statistics since 2014-02-04 00:12:58 ***
Total inserts 235.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 470.00
*** Daily statistics since 2014-02-04 00:12:58 ***
Total inserts 235.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 470.00
*** Hourly statistics since 2014-02-04 00:12:58 ***
Total inserts 235.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 470.00
*** Latest statistics since 2014-02-04 00:12:58 ***
Total inserts 235.00
Total updates 0.00
Total deletes 235.00
Total discards 0.00
Total operations 470.00
Replicating from OGGSRC.ECONOMIC_ENTITY to OGGTRG.ECONOMIC_ENTITY:
*** Total statistics since 2014-02-04 00:12:58 ***
Total inserts 0.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 36.00
*** Daily statistics since 2014-02-04 00:12:58 ***
Total inserts 0.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 36.00
*** Hourly statistics since 2014-02-04 00:12:58 ***
Total inserts 0.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 36.00
*** Latest statistics since 2014-02-04 00:12:58 ***
Total inserts 0.00
Total updates 36.00
Total deletes 0.00
Total discards 0.00
Total operations 36.00
End of Statistics.
GGSCI (host01.example.com) >
|
This completes the configuration and operation of the basic heterogeneous unidirectional functionality of Oracle GoldenGate: Extract, Data Pump, and Delivery Replicat.
Summary
- Configure and start Integrated Extract, Data Pump, and data delivery Integrated Replicat processes.
- Generate sample data and test the validity of the replication
- Generate simple statistics on the rows transferred by the replication processes (both Extract and Replicat)
- Oracle GoldenGate Product Documentation version 12.1.2 ( http://docs.oracle.com/goldengate/1212/gg-winux/docs.htm) and other older versions
- Courses from Oracle University
- External Web sites for related information
- To learn more about Oracle GoldenGate, refer to additional OBEs in the Oracle Learning Library
- Lead Curriculum Developer: Elio Bonazzi.
- Other Contributors: Steve Friedberg, Joe deBuzna, Chris Lawless.
Oracle GoldenGate can do far more than was demonstrated in this simple exercise.
In this tutorial, you have learned how to:
Resources
Credits
To help navigate this Oracle by Example, note the following:
- Hiding Header Buttons:
- Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
- Topic List Button:
- A list of all the topics. Click one of the topics to navigate to that section.
- Expand/Collapse All Topics:
- To show/hide all the detail for all the sections. By default, all topics are collapsed.
- Show/Hide All Images:
- To show/hide all the screenshots. By default, all images are displayed.
- Print:
- To print the content. The content currently displayed or hidden will be printed.
To navigate to a particular section in this tutorial, select the topic from the list.