Replicación de datos con múltiples procesos "Extract" y "Replicat" con el modo de captura integrada utilizando Oracle GoldenGate 12c

Introducción:

Oracle GoldenGate 12c y el modo de captura integrado

El modo de captura integrado (Integrated capture mode) introducido en Oracle GoldenGate 11gR2 y mejorado en 12c soporta la integración de ambientes "Multitenant" de Oracle Database 12c. Esto permite la minería sobre una o muchas bases de datos "Pluggable" y la replicación de datos hacia múltiples destinos. Este modo proporciona ventajas respecto al modo de extracción clásico pues interactúa directamente con una base de datos de "LogMining" que es la encargada de leer los "Online Redo Log Files" y capturar los cambios en un nuevo formato denominado 'Logical Change Records' (LCR's) que son trasladados a los 'Trail Files' para el proceso de replicación.

Para una completa guía sobre Integrated Capture Mode referirse a la documentación oficial de Oracle GoldenGate (https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/config_capt_integrated.htm#GIORA912).

Procesos múltiples en Oracle GoldenGate

La replicación de una gran cantidad de datos puede generar cuellos de botella en la extracción y aplicación, con el objetivo de mejorar el rendimiento de estos procesos Oracle GoldenGate permite crear grupos de procesos para ejecutar tareas en paralelo. Para definir múltiples procesos es necesario crear grupos de procesamiento.

Un grupo de procesamiento consiste en un proceso (ya sea "Extract" o "Replicat) un archivo de parámetros, un archivo de "checkpoint" y cualquier otro archivo asociado al proceso. Para los procesos "Replicat" también es necesario una tabla de "Checkpoint"

Replicación de datos con múltiples procesos

Oracle GoldenGate 12c permite configurar varios procesos "Replicat" en paralelo para mejorar el rendimiento de la replicación en una base de datos destino. Configurar Oracle GoldenGate en modo de captura integrado (Integrated capture mode) permite aprovechar la funcionalidad de un servidor de "LogMiner" residente en la base de datos y simplificar la administración.

Este articulo está diseñado para configurar Oracle GoldenGate 12c y diseñar una solución de replicación DML y DDL unidireccional desde una base de datos Origen (Oracle 12c) con múltiples procesos "Extract" y "Pump" hacia una base de dato Destino (Oracle 12c) con múltiples procesos "Replicat".

Para este laboratorio, "srcdb" será nuestra base de datos Origen (Oracle database 12c) y "tardb" será nuestra base de datos Destino (Oracle database 12c).

Base de datos Origen (srcdb)	Base de datos Destino (tardb)
Oracle 12c R1 Database (12.1.0.1.0) Oracle GoldenGate 12c (12.1.2.1.0) Oracle Enterprise Linux 5.8 (x86-64) Database Name : srcdb Schema Name : scott Host Name : ggnode1 IP Address: 192.168.56.105	Oracle 12c R1 Database (12.1.0.1.0) Oracle GoldenGate 12c (12.1.2.1.0.) Oracle Enterprise Linux 5.8 (x86-64) Database Name : tardb Schema Name : scott Hostname: ggnode2 IP Address: 192.168.56.106

Procesos OGG en base de datos Origen (srcdb)	Procesos OGG en bas de datos Destino (tardb)
Manager: Listening port 15100 Extract Process-1: ES1a Oracle Enterprise Linux 5.8 (x86-64) Extract Process-2: ES1b Extract Process-3: ES1c Pump Process-1: PS1a Pump Process-2: PS1b Pump Process-3: PS1c	Manager: Listening port 15200 Replicat Process-1: RS1a Replicat Process-2: RS1b Replicat Process-3: RS1c

Serie de pasos en base de datos @Origen (srcdb):

Instalar el software de base de datos Oracle 12c R1 como usuario 'Oracle'en el directorio:
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
Instalar el software de Oracle GoldenGate 12c como usuario 'oracle' en el directorio:
GOLDENGATE_HOME=/u01/app/ogg/12g
Configurar y crear subdirectorios necesarios para Oracle GoldenGate 12c en servidor de base de datos Origen (srcdb).
Crear y configurar el proceso "Manager", multiples procesos "Extract" y "Pump" para replicacion entre las bases de datos Oracle 12c (srcdb) y Oracle 12c (tardb).
Activar la caracteristica SUPPLEMENTAL LOGGING para asegurar que los datos sean replicados hacia la base de datos destino (tardb).
Colocar la base de datos en modo ARCHIVELOG.
Establecer el tamaño adecuado del "Streams pool" para utilizar le modo "integrated capture" de GoldenGate.

Paso-1: Ingresar con el usuario 'Oracle' en el servidor de la base de datos Origen (srcdb) e instalar el software de Oracle GoldenGate, seleccionar la opcion 'Oracle GoldenGate for Oracle Database 12c'

Al finalizar la instalacion, ingresar a la interfaz de linea de comandos GGSCI y crear los directorios necesarios para Oracle GoldenGate 12c

[oracle@ggnode1 12g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:21:34
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode1.oracle.com) 1> create subdirs


Creating subdirectories under current directory /u01/app/ogg/12g
Parameter files                  /u01/app/ogg/12g/dirprm: already exists
Report files                     /u01/app/ogg/12g/dirrpt: created
Checkpoint files                /u01/app/ogg/12g/dirchk: created
Process status files            /u01/app/ogg/12g/dirpcs: created
SQL script files                 /u01/app/ogg/12g/dirsql: created
Database definitions files      /u01/app/ogg/12g/dirdef: created
Extract data files               /u01/app/ogg/12g/dirdat: created
Temporary files                 /u01/app/ogg/12g/dirtmp: created
Credential store files          /u01/app/ogg/12g/dircrd: created
Masterkey wallet files          /u01/app/ogg/12g/dirwlt: created
Dump files                       /u01/app/ogg/12g/dirdmp: created

Ingresar a la base de datos Origen (srcdb) mediante sqlplus con privilegios de 'sysdba'

[oracle@ggnode1 ~]$ source 12c.env
[oracle@ggnode1 ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 20 13:46:03 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> connect sys/oracle@srcdb as sysdba
Connected.

SQL> set lines 200 pages 1000
SQL> CREATE TABLESPACE GOLDENGATE DATAFILE '/u01/app/oracle/oradata/srcdb/goldengate01.dbf'

SIZE 100M AUTOEXTEND ON;
Tablespace created.

SQL> CREATE USER ogguser IDENTIFIED BY oracle DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE
 TEMP ACCOUNT UNLOCK;
User created.

SQL> GRANT CONNECT TO ogguser;
Grant succeeded.

SQL> GRANT DBA TO ogguser;
Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO ogguser;
Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO ogguser;
Grant succeeded.

SQL> alter database force logging;
Database altered.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,LOG_MODE FROM V$DATABASE;

SUPPLEME  LOG_MODE
---------------  -----------------
YES        ARCHIVELOG

SQL> alter user scott identified by oracle account unlock;
User altered.

Cuando utilizamos procesos "Extract" en el modo de captura integrado de GoldenGate debemos configurar un area de memoria opcional dentro del SGA de la base de datos denominado "Streams Pool"

 
SQL> sho parameter streams

NAME                                  TYPE         VALUE
------------------------------------  -----------  ------------------------------
streams_pool_size                     big integer  0

SQL> alter system set streams_pool_size=256M scope=both;
System altered.

SQL> sho parameter streams

NAME                                  TYPE         VALUE
------------------------------------  -----------  ------------------------------
streams_pool_size                     big integer  256M

SQL> connect scott/oracle@srcdb
Connected.

SQL> select tname from tab;

TNAME
-------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE

Ingresamos nuevamente a Oracle GoldenGate 12c en el servidor de base de datos Origen (srcdb)

GGSCI (ggnode1.oracle.com) 2> dblogin userid ogguser, password oracle
Successfully logged into database.

Register extract processes ES1a, ES1 & ES1c with separate trail files. Create multiple pump processes 

(PS1a, PS1b & PS1c) for transforming trail files to target database (tardb). 

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 4> register extract es1a database
Extract ES1A successfully registered with database at SCN 4341495.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 5> add extract es1a, integrated tranlog, begin now
EXTRACT added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 6> add exttrail ./dirdat/ya, extract es1a, megabytes 10
EXTTRAIL added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 7> register extract es1b database
Extract ES1B successfully registered with database at SCN 4360660.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 8> add extract es1b, integrated tranlog, begin now
EXTRACT added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 9> add exttrail ./dirdat/yb, extract es1b, megabytes 10
EXTTRAIL added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 10> register extract es1c database
Extract ES1C successfully registered with database at SCN 4374333.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 11> add extract es1c, integrated tranlog, begin now
EXTRACT added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 12> add exttrail ./dirdat/yc, extract es1c, megabytes 10
EXTTRAIL added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 13> add extract ps1a, exttrailsource ./dirdat/ya
EXTRACT added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 14> add rmttrail ./dirdat/ya, extract ps1a, megabytes 10
RMTTRAIL added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 15> add extract ps1b, exttrailsource ./dirdat/yb
EXTRACT added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 16> add rmttrail ./dirdat/yb, extract ps1b, megabytes 10
RMTTRAIL added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 17> add extract ps1c, exttrailsource ./dirdat/yc
EXTRACT added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 18> add rmttrail ./dirdat/yc extract ps1c, megabytes 10
RMTTRAIL added.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 19> edit param es1a
GGSCI (ggnode1.oracle.com as ogguser@srcdb) 20> view param es1a

extract es1a
exttrail ./dirdat/ya
userid ogguser, password oracle
cachemgr cachesize 512M
ddl include all
ddloptions report
statoptions resetreportstats
tranlogoptions excludeuser ogguser
tranlogoptions integratedparams (max_sga_size 1024, parallelism 1)
table scott.dept;

Verificar que el proceso "Extract" este configurado en modo de caputra integrado (la opcion 'Log Read Checkpoint' debe aparecer como 'Oracle Integrated Redo Logs').

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 49> info extract es1a

EXTRACT    ES1A       Last Started 2015-06-21 22:51    Status RUNNING
Checkpoint Lag        00:00:01 (updated 00:00:05 ago)
Process ID            23996
Log Read Checkpoint   Oracle Integrated Redo Logs
                       2015-06-22 11:06:55
                       SCN 0.5652891 (5652891)
GGSCI (ggnode1.oracle.com as ogguser@srcdb) 50>

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 21> edit param es1b
GGSCI (ggnode1.oracle.com as ogguser@srcdb) 22> view param es1b

extract es1b
exttrail ./dirdat/yb
userid ogguser, password oracle
cachemgr cachesize 512M
ddl include all
ddloptions report
statoptions resetreportstats
tranlogoptions excludeuser ogguser
tranlogoptions integratedparams (max_sga_size 1024, parallelism 1)
table scott.dept;

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 23> edit param es1c
GGSCI (ggnode1.oracle.com as ogguser@srcdb) 24> view param es1c

extract es1c
exttrail ./dirdat/yc
userid ogguser, password oracle
cachemgr cachesize 512M
ddl include all
ddloptions report
statoptions resetreportstats
tranlogoptions excludeuser ogguser
tranlogoptions integratedparams (max_sga_size 1024, parallelism 1)
table scott.dept;

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 25> edit param ps1a
GGSCI (ggnode1.oracle.com as ogguser@srcdb) 26> view param ps1a

extract ps1a
userid ogguser, password oracle
rmthost ggnode2, mgrport 15200, compress
rmttrail ./dirdat/ya
table scott.dept, filter (@range (1,3));

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 27> edit param ps1b
GGSCI (ggnode1.oracle.com as ogguser@srcdb) 28> view param ps1b

extract ps1b
userid ogguser, password oracle
rmthost ggnode2, mgrport 15200, compress
rmttrail ./dirdat/yb
table scott.dept, filter (@range (2,3));

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 29> edit param ps1c
GGSCI (ggnode1.oracle.com as ogguser@srcdb) 30> view param ps1c

extract ps1c
userid ogguser, password oracle
rmthost ggnode2, mgrport 15200, compress
rmttrail ./dirdat/yc
table scott.dept, filter (@range (3,3));

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 31> info all
Program      Status       Group        Lag at Chkpt   Time Since Chkpt

MANAGER      STOPPED
EXTRACT      STOPPED      ES1A         00:00:00       00:05:04
EXTRACT      STOPPED      ES1B         00:00:00       00:04:28
EXTRACT      STOPPED      ES1C         00:00:00       00:03:52
EXTRACT      STOPPED      PS1A         00:00:00       00:03:17
EXTRACT      STOPPED      PS1B         00:00:00       00:03:11
EXTRACT      STOPPED      PS1C         00:00:00       00:03:07

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 32> edit param mgr
GGSCI (ggnode1.oracle.com as ogguser@srcdb) 33> view param mgr

PORT 15100
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

Oracle GoldenGate (OGG) requiere que los valores clave de una columna (KEY VALUES) sean registrador en los REDO LOG para asegurar que los mismos valores modificados o eliminados en la base de datos Origen sean encontrados en la base de datos Destino.

Para esto, necesitamos activar la carateristica SUPPLEMENTAL LOGGING a nivel de esquema (SCOTT en este ejemplo) utilizando el comando ADD SCHEMATRANDATA.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 34> add schematrandata scott
2015-06-21 22:25:22  INFO    OGG-01788  SCHEMATRANDATA has been added on schema scott.
2015-06-21 22:25:22  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema scott.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 35> edit param ./GLOBALS
GGSCI (ggnode1.oracle.com as ogguser@srcdb) 36> view param ./GLOBALS

GGSCHEMA OGGUSER
ENABLEMONITORING
CHECKPOINTTABLE GGS_CHECKPOINT

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 39> info all

Program      Status       Group        Lag at Chkpt   Time Since Chkpt

MANAGER      STOPPED
EXTRACT      STOPPED      ES1A         00:00:00       00:14:26
EXTRACT      STOPPED      ES1B         00:00:00       00:13:50
EXTRACT      STOPPED      ES1C         00:00:00       00:13:14
EXTRACT      STOPPED      PS1A         00:00:00       00:12:39
EXTRACT      STOPPED      PS1B         00:00:00       00:12:34
EXTRACT      STOPPED      PS1C         00:00:00       00:12:29

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 38> start mgr
Manager started.

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 48> info mgr
Manager is running (IP port ggnode1.oracle.com.15100, Process ID 23542).

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 40> info all
Program      Status       Group        Lag at Chkpt   Time Since Chkpt

MANAGER      RUNNING
EXTRACT      STOPPED      ES1A         00:00:00       00:17:02
EXTRACT      STOPPED      ES1B         00:00:00       00:16:26
EXTRACT      STOPPED      ES1C         00:00:00       00:15:50
EXTRACT      STOPPED      PS1A         00:00:00       00:15:15
EXTRACT      STOPPED      PS1B         00:00:00       00:15:10
EXTRACT      STOPPED      PS1C         00:00:00       00:15:05

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 45> info exttrail ./dirdat/ya

Extract Trail:  ./dirdat/ya
Extract:  ES1A
Seqno:   8
RBA:   8881941
File Size:  10M

Extract Trail: ./dirdat/ya
Extract:  PS1A
Seqno:   2
RBA:   6898892
File Size:  10M

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 46> info exttrail ./dirdat/yb

Extract Trail:  ./dirdat/yb
Extract:  ES1B
Seqno:   8
RBA:   9172543
File Size:  10M

Extract Trail:  ./dirdat/yb
Extract:  PS1B
Seqno:   2
RBA:   6978307
File Size:  10M

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 47> info exttrail ./dirdat/yc

Extract Trail:  ./dirdat/yc
Extract:  ES1C
Seqno:   8
RBA:   5718157
File Size:  10M

Extract Trail:  ./dirdat/yc
Extract:  PS1C
Seqno:   2
RBA:   7041522
File Size:  10M

GGSCI (ggnode1.oracle.com as ogguser@srcdb) 48>

Serie de pasos en base de datos @Destino (tardb):

Instalar el software de base de datos Oracle 12c R1 como usuario 'Oracle'en el directorio:
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
Instalar el software de Oracle GoldenGate 12c como usuario 'oracle' en el directorio:
GOLDENGATE_HOME=/u01/app/ogg/12g
Configurar y crear subdirectorios necesarios para Oracle GoldenGate 12c en servidor de base de datos Origen (tardb).
Crear y configurar el proceso "Manager" y multiples procesos "Replicat" en la base de datos Destino (tardb).
Activar la caracteristica SUPPLEMENTAL LOGGING para asegurar la correcta replicacion de datos.
Colocar la base de datos en modo ARCHIVELOG.
Activar la caracteristica FORCE LOGGING de la base de datos.
Establecer el tamaño adecuado del "Streams pool" para utilizar le modo "integrated capture" de GoldenGate.

Paso-2: Ingresar al servidor de base de datos Destino (tardb) como usuario 'Oracle' e instalar el software de Oracle GoldenGate 12c, elegir la opcion 'Oracle GoldenGate for Oracle Database 12c'

Ingresar a la interfaz de linea de comandos de GoldenGate GGSCI y crear los sobdirectorios necesarios

 
[oracle@ggnode2 ~]$ source 12c.env
[oracle@ggnode2 ~]$ cd $GG
[oracle@ggnode2 12g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:21:34
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode2.oracle.com) 1> create subdirs

Creating subdirectories under current directory /u01/app/ogg/12g

Parameter files                  /u01/app/ogg/12g/dirprm: already exists
Report files                     /u01/app/ogg/12g/dirrpt: created
Checkpoint files                /u01/app/ogg/12g/dirchk: created
Process status files            /u01/app/ogg/12g/dirpcs: created
SQL script files                 /u01/app/ogg/12g/dirsql: created
Database definitions files      /u01/app/ogg/12g/dirdef: created
Extract data files               /u01/app/ogg/12g/dirdat: created
Temporary files                 /u01/app/ogg/12g/dirtmp: created
Credential store files          /u01/app/ogg/12g/dircrd: created
Masterkey wallet files          /u01/app/ogg/12g/dirwlt: created
Dump files                       /u01/app/ogg/12g/dirdmp: created

Ingresar a la base de datos Destino (tardb) mendiante sqlplus con privilegios de 'sysdba'

 
 [oracle@ggnode2 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 20 13:49:28 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> connect sys/oracle@tardb as sysdba
Connected.

SQL> CREATE TABLESPACE GOLDENGATE DATAFILE '/u01/app/oracle/oradata/tardb/goldengate01.dbf' SIZE 100M
 AUTOEXTEND ON;
Tablespace created.

SQL> CREATE USER ogguser IDENTIFIED BY oracle DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE TEMP
 ACCOUNT UNLOCK;
User created.

SQL> GRANT CONNECT TO ogguser;
Grant succeeded.

SQL> GRANT DBA TO ogguser;
Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO ogguser;
Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO ogguser;
Grant succeeded.

SQL> alter database force logging;
Database altered.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,LOG_MODE FROM V$DATABASE;
SUPPLEME  LOG_MODE
--------------  ------------
YES        ARCHIVELOG

SQL> alter user scott identified by oracle account unlock;
User altered.

SQL> sho parameter streams
NAME                                  TYPE         VALUE
------------------------------------  -----------  ------------------------------
streams_pool_size                     big integer  0
SQL> alter system set streams_pool_size=256M scope=both;
System altered.

SQL> sho parameter streams
NAME                                  TYPE         VALUE
------------------------------------  -----------  ------------------------------
streams_pool_size                     big integer  256M

SQL> connect scott/oracle@tardb
Connected.

SQL> select tname from tab;

TNAME
----------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE

Nuevamente ingresamos a GGSCI de GoldenGate en el servidor de base de datos Destino (tardb) para configuar los procesos "Replicat"

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:21:34
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggnode2.oracle.com) 2> dblogin userid ogguser, password oracle
Successfully logged into database.

GGSCI (ggnode2.oracle.com as ogguser@tardb) 4> edit param ./GLOBALS
GGSCI (ggnode2.oracle.com as ogguser@tardb) 5> view param ./GLOBALS

GGSCHEMA OGGUSER
ENABLEMONITORING
CHECKPOINTTABLE GGS_CHECKPOINT

GGSCI (ggnode2.oracle.com as ogguser@tardb) 6> exit
[oracle@ggnode2 12g]$ ./ggsci

GGSCI (ggnode2.oracle.com) 1> dblogin userid ogguser, password oracle
Successfully logged into database.

GGSCI (ggnode2.oracle.com as ogguser@tardb) 1> add checkpointtable
No checkpoint table specified. Using GLOBALS specification (GGS_CHECKPOINT)...
Successfully created checkpoint table GGS_CHECKPOINT.

GGSCI (ggnode2.oracle.com as ogguser@tardb) 2> add replicat rs1a, exttrail ./dirdat/ya
REPLICAT added.

GGSCI (ggnode2.oracle.com as ogguser@tardb) 3> add replicat rs1b, exttrail ./dirdat/yb
REPLICAT added.

GGSCI (ggnode2.oracle.com as ogguser@tardb) 4> add replicat rs1c, exttrail ./dirdat/yc
REPLICAT added.

GGSCI (ggnode2.oracle.com as ogguser@tardb) 5> edit param rs1a
GGSCI (ggnode2.oracle.com as ogguser@tardb) 6> view param rs1a

replicat rs1a
userid ogguser, password oracle
assumetargetdefs
ddloptions report
discardfile ./dirout/rs1a.dsc, purge
map scott.dept, target scott.dept, filter (@range (1,3));

GGSCI (ggnode2.oracle.com as ogguser@tardb) 47> info replicat rs1a

REPLICAT   RS1A       Last Started 2015-06-21 22:37    Status RUNNING
Checkpoint Lag        00:00:00 (updated 00:00:10 ago)
Process ID            23194
Log Read Checkpoint   File ./dirdat/ya000002
                       2015-06-21 22:58:13.060948  RBA 6898892

GGSCI (ggnode2.oracle.com as ogguser@tardb) 7> edit param rs1b
GGSCI (ggnode2.oracle.com as ogguser@tardb) 8> view param rs1b

replicat rs1b
userid ogguser, password oracle
assumetargetdefs
ddloptions report
discardfile ./dirout/rs1b.dsc, purge
map scott.dept, target scott.dept, filter (@range (2,3));

GGSCI (ggnode2.oracle.com as ogguser@tardb) 11> edit param rs1c
GGSCI (ggnode2.oracle.com as ogguser@tardb) 12> view param rs1c

replicat rs1c
userid ogguser, password oracle
assumetargetdefs
ddloptions report
discardfile ./dirout/rs1c.dsc, purge
map scott.dept, target scott.dept, filter (@range (3,3));

GGSCI (ggnode2.oracle.com as ogguser@tardb) 13> edit param mgr
GGSCI (ggnode2.oracle.com as ogguser@tardb) 14> view param mgr

PORT 15200
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

GGSCI (ggnode2.oracle.com as ogguser@tardb) 15> info all

Program      Status       Group        Lag at Chkpt   Time Since Chkpt

MANAGER      STOPPED
REPLICAT     STOPPED      RS1A         00:00:00       00:03:44
REPLICAT     STOPPED      RS1B         00:00:00       00:03:37
REPLICAT     STOPPED      RS1C         00:00:00       00:03:26

GGSCI (ggnode2.oracle.com as ogguser@tardb) 24> start mgr
Manager started.

GGSCI (ggnode2.oracle.com as ogguser@tardb) 46> info mgr
Manager is running (IP port ggnode2.oracle.com.15200, Process ID 23184).

GGSCI (ggnode2.oracle.com as ogguser@tardb) 25> info all
Program      Status       Group        Lag at Chkpt   Time Since Chkpt

MANAGER      RUNNING
REPLICAT     STOPPED      RS1A         00:00:00       00:08:45
REPLICAT     STOPPED      RS1B         00:00:00       00:08:38
REPLICAT     STOPPED      RS1C         00:00:00       00:08:27

GGSCI (ggnode2.oracle.com as ogguser@tardb) 26> start er *

Sending START request to MANAGER ...
REPLICAT RS1A starting

Sending START request to MANAGER ...
REPLICAT RS1B starting

Sending START request to MANAGER ...
REPLICAT RS1C starting

GGSCI (ggnode2.oracle.com as ogguser@tardb) 27> info all
Program      Status       Group        Lag at Chkpt   Time Since Chkpt
MANAGER      RUNNING
REPLICAT     RUNNING      RS1A         00:00:00       00:00:01
REPLICAT     RUNNING      RS1B         00:00:00       00:00:01
REPLICAT     RUNNING      RS1C         00:00:00       00:00:01

Para verificar el comportamiento de nuestra configuracion, ingresar a Oracle GoldenGate en el servidor de la base de datos Origen (srcdb) y generamos una transaccion en la table DEPT del usuario SCOTT.

SQL> select count(*) from dept;

COUNT(*)
----------
99913

SQL> declare
deptno      number(6);
dname      varchar2(14);
loc      varchar2(13);
begin
    for i in 100000..500000 loop
        insert into dept values (i,'ORACLE','REDWOOD');
        commit;
   end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> select count(*) from dept;

COUNT(*)
----------
499914

Nota: cambiar el valor del contador para generar mas transacciones y ejectucar el segmento nuevamente.

Verificar la sincronizacion de las transacciones desde la base de datos Origen (srcdb) hacia la base de datos Destino (tardb).

Veriicar la ubicacion y tamaño de los archivos TRAIL (trail files) en la base de datos Origen (srcdb)

Verificar la ubicacion y tamaño de los archivos Trail (trail files) en la base de datos Destino (tardb). Segun la configuracion anterior, hemos utilizado la opcion de compression para los archivos TRAIL en la base de datos destino.

Como pasos finales verificamos estadisticas de los procesos "Extract" en la base de datos Origen (srcdb)

Verificando las estadisticas para el proceso "Extract" ES1a

Verificando las estadisticas para el proceso "Extract" ES1b

Verificando las estadisticas para el proceso "Extract" ES1c

Verificar las estadisticas de los multiples procesos "Pump" en la base de datos Origen (srcdb)

Verifcar las estadistcias de los multiples procesos "Replicat" en la base de datos Destino (tardb)

Conclusiones: La configuracion de multiples procesos "Replicat" en la base de datos Destino mejora el rendimiento de la aplicacion. Configurar Oracle GoldenGate en modo de captura integrado (Integrated capture mode) permite obtener ventajas en la funcionalidad del servidor de "LogMiner" que reside en la base de datos origen y simplifica la administracion. Oracle recomienda configurar inicialmente un solo proceso "Replicat" y monitorear rendimiento y tiempos de espera en la apicacion. La adición de más procesos "Replicat" sólo debe realizarse cuando el rendimiento de un único "Replicat" provoca latencia de aplicación o rendmiento degradado.

Oracle Database

Replicación de datos con múltiples procesos "Extract" y "Replicat" con el modo de captura integrada utilizando Oracle GoldenGate 12c

Comentarios

Publicar un comentario

Entradas populares de este blog

Installing Oracle GoldenGate for Oracle 12c

How To Rename ASM Diskgroup With RAC Database