Configuring Integrated Extract and Integrated Replicat Using Oracle GoldenGate 12c on Linux

You must create three users on the ogg12 database:

1. The user ogguser. This user has a heightened level of privileges to allow the Oracle GoldenGate software to perform replication.
2. The user oggsrc. This is the unprivileged replication source user.
3. The user oggtrg. This is the unprivileged replication target user.
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.


Position your mouse pointer somewhere in the main Gnome background and right-click. A pop-up window appears. Select and click "Open in Terminal" twice to create two terminal windows:




Click and drag the second window to align it next to the first one:




Click the border of the left-most window to make it active, then click Terminal, and then select the Set Title option from the drop-down window:




Name the window OGGSRC. You will run the Oracle sqlplus utility connected to the source schema in this window:





Select the right-most window and click Terminal, then select Set Title and enter OGGTRG as the title for the window. You will run the Oracle sqlplus utility connected to the target schema in this window:





Open a third terminal window. Click and drag the third window below the other two windows and name it "ROOT." You will connect to the "root" user and perform commands which require superuser privileges in this window:





 

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: ******   root@ologg ~]#

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 v$database; SUPPLEME FORCE_LOGGING -------- --------------------------------------- YES YES SQL>

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

Select the OGGSRC window, leave sqlplus and change directory to the default directory for the oracle user. Create the obe directory (the absolute pathname will be:/home/oracle/obe.) Download the zip file containing the sql scripts for this OBE. Copy the downloaded zip file into the/home/oracle/obe directory.

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 ~]$

On the Linux host, chnge directory to the obe directory under the default directory (/home/oracle) and expand the OBE_DDL_FILES.zip file there.
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

 

2. Preparing the Oracle GoldenGate Environment for Replication.

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:


• OGGSRC
• OGGTRG
• GGSCI_SRC
• GGSCI_TRG
• ROOT






Select the GGSCI_SRC window.
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>

Select the GGSCI_TRG window. Change directory to the directory where the Oracle GoldenGate software has been installed (replication target, /u01/app/oracle/product/ogg_trg) and launch ggsci. Enter the info mgr command to verify that the manager is running.


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>

Select the GGSCI_SRC window. Edit the parameter file for the manager. You can choose to edit the file using gedit instead of vi. In this case, enter the command set editor gedit before entering the command edit param mgr. You specify the list of TCP/IP ports available to the manager process, you request to purge the old extracts and you instruct the manager process to automatically start all Extracts whenever the manager process starts. Additionally, the manager will try to restart failed processes three times, every minute until all retries are attempted. The parameters used are:



• DynamicPortList
• PurgeOldExtracts
• Autostart
• AutoRestart




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 for all columns has been added on table 'OGGSRC.ECONOMIC_ENTITY'. Logging of supplemental redo data enabled for table OGGSRC.GDP_BY_YEAR. TRANDATA for scheduling columns has been added on table 'OGGSRC.GDP_BY_YEAR'.TRANDATA for all columns has been added on table 'OGGSRC.GDP_BY_YEAR'. Logging of supplemental redo data enabled for table OGGSRC.GDP_GROWTH_BY_YEAR. TRANDATA for scheduling columns has been added on table 'OGGSRC.GDP_GROWTH_BY_YEAR'.TRANDATA for all columns has been added on table 'OGGSRC.GDP_GROWTH_BY_YEAR'. GGSCI (host01.example.com) 10> Info TranData OGGSRC.* Logging of supplemental redo log data is enabled for table OGGSRC.ECONOMIC_ENTITY. Columns supplementally logged for table OGGSRC.ECONOMIC_ENTITY: ALL. Logging of supplemental redo log data is enabled for table OGGSRC.GDP_BY_YEAR. Columns supplementally logged for table OGGSRC.GDP_BY_YEAR: ALL. Logging of supplemental redo log data is enabled for table OGGSRC.GDP_GROWTH_BY_YEAR. Columns supplementally logged for table OGGSRC.GDP_GROWTH_BY_YEAR: ALL.

 

3. Configuring Data Capture Using Integrated Extract

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:



• 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

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 EXTRACT added. GGSCI (host02.example.com) > add exttrail ./dirdat/in, extract einta, megabytes 10 EXTTRAIL added. GGSCI (host02.example.com) >

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 Replicat rinta SETENV(ORACLE_SID='ogg12') DBOPTIONS INTEGRATEDPARAMS(parallelism 6) AssumeTargetDefs DiscardFile ./dirrpt/rpdw.dsc, Purge UserIdAlias ogg_user Map oggsrc.*, target oggtrg.*;

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