Software a instalar:
- Oracle Golden Gate 12c
- Parche de OGG para la BBDD 12c p25445845_12201170221_Linux-x86-64.zip
- Parche de OGG para la BBDD 11g p25445840_12201170221_Linux-x86-64.zip
Le ponemos un path de acuerdo al origen y destino:
Origen:
- Host: sorigen11g
- Directorio: /db/R01_GG
Destino:
- Host: sdestino11g
- Directorio: /db/R01_GG
En las 2 BBDD's tiene que estar habilitado el parámetro para activar la réplica.
alter system set enable_goldengate_replication=true scope=both sid='*';
USUARIOS
Empezamos creando los usuarios en las BBDD.
Lanzar esto en la BBDD origen orcl_orig:
-- Crear Tablespace
CREATE BIGFILE TABLESPACE R01_GG_DAT datafile '+DATA' SIZE 10M AUTOEXTEND ON;
-- Usuario
CREATE USER R01_GG IDENTIFIED BY R01_GG PROFILE DEFAULT
DEFAULT TABLESPACE R01_GG_DAT TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;
-- Roles
execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('R01_GG');
-- Privilegios del Sistema
GRANT SELECT ANY TRANSACTION TO R01_GG;
GRANT ALTER ANY RULE TO R01_GGWITH ADMIN OPTION;
GRANT CREATE EVALUATION CONTEXT TO R01_GGWITH ADMIN OPTION;
GRANT LOCK ANY TABLE TO R01_GG;
GRANT CREATE ANY RULE TO R01_GGW ITH ADMIN OPTION;
GRANT CREATE RULE TO R01_GG WITH ADMIN OPTION;
GRANT ALTER ANY RULE SET TO R01_GG WITH ADMIN OPTION;
GRANT EXECUTE ANY RULE TO R01_GG WITH ADMIN OPTION;
GRANT EXECUTE ANY RULE SET TO R01_GG WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO R01_GG;
GRANT CREATE RULE SET TO R01_GG WITH ADMIN OPTION;
GRANT CREATE ANY RULE SET TO R01_GG WITH ADMIN OPTION;
GRANT RESTRICTED SESSION TO R01_GG;
GRANT ENQUEUE ANY QUEUE TO R01_GG WITH ADMIN OPTION;
GRANT DEQUEUE ANY QUEUE TO R01_GG WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO R01_GG;
GRANT ALTER SYSTEM TO R01_GG;
GRANT MANAGE ANY QUEUE TO R01_GG WITH ADMIN OPTION;
GRANT DBA TO R01_GG;
GRANT SELECT_CATALOG_ROLE TO R01_GG;
ALTER USER R01_GG DEFAULT ROLE SELECT_CATALOG_ROLE,DBA;
Lanzar esto en la BBDD destino orcl_dest:
-- Crear Tablespace
CREATE BIGFILE TABLESPACE R01_GG_DAT datafile '+DATA' SIZE 10M AUTOEXTEND ON;
-- Usuario
CREATE USER R01_GG IDENTIFIED BY R01_GG PROFILE DEFAULT
DEFAULT TABLESPACE R01_GG_DAT TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;
-- Rolesexecute
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('R01_GG');
-- Privilegios del Sistema
GRANT SELECT ANY TRANSACTION TO R01_GG;
GRANT ALTER ANY RULE TO R01_GG WITH ADMIN OPTION;
GRANT CREATE EVALUATION CONTEXT TO R01_GG WITH ADMIN OPTION;
GRANT LOCK ANY TABLE TO R01_GG;
GRANT CREATE ANY RULE TO R01_GG WITH ADMIN OPTION;
GRANT CREATE RULE TO R01_GG WITH ADMIN OPTION;
GRANT ALTER ANY RULE SET TO R01_GG WITH ADMIN OPTION;
GRANT EXECUTE ANY RULE TO R01_GG WITH ADMIN OPTION;
GRANT EXECUTE ANY RULE SET TO R01_GG WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO R01_GG;
GRANT CREATE RULE SET TO R01_GG WITH ADMIN OPTION;
GRANT CREATE ANY RULE SET TO R01_GG WITH ADMIN OPTION;
GRANT RESTRICTED SESSION TO R01_GG;
GRANT ENQUEUE ANY QUEUE TO R01_GG WITH ADMIN OPTION;
GRANT DEQUEUE ANY QUEUE TO R01_GG WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO R01_GG;
GRANT ALTER SYSTEM TO R01_GG;
GRANT MANAGE ANY QUEUE TO R01_GGWITH ADMIN OPTION;
GRANT INSERT, UPDATE, DELETE ON ESQUEMA.TABLA1 TO R01_GG;
GRANT INSERT, UPDATE, DELETE ON ESQUEMA.TABLA2 TO R01_GG;
GRANT INSERT, UPDATE, DELETE ON ESQUEMA.TABLA3 TO R01_GG;
...
GRANT DBA TO R01_GG;
GRANT SELECT_CATALOG_ROLE TO R01_GG;
ALTER USER G58_GG DEFAULT ROLE CONEXION,SELECT_CATALOG_ROLE,DBA,ESQUEMA;
PROFILES
Se crean los ficheros para cargar las variables de entorno para conexión a GG.
En el Host Origen (sorigen11g). El fichero lo llamaremos .profile_R01_GG
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome
export OGG_HOME=/db/R01_GG
export PATH=/u02/app/11.2.0/grid/bin:$OGG_HOME:$PATH
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/dbhome/lib:$OGG_HOME:$LD_LIBRARY_PATH
cd $OGG_HOME
alias ggsci="rlwrap ./ggsci"
En el Host Destino (sdestino11g)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome
export OGG_HOME=/db/R01_GG
export PATH=/u02/app/11.2.0/grid/bin:$OGG_HOME:$PATH
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/dbhome/lib:$OGG_HOME:$LD_LIBRARY_PATH
cd $OGG_HOME
alias ggsci="rlwrap ./ggsci"
ALIAS
Para no tener que identificarse en todo momento creamos los alias.
En el Host Origen (sorigen11g)
[oracle@sorigen11g]$ . .profile_R01_GG
[oracle@sorigen11g R01_GG]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (sorigen11g.localdomain) 1> add credentialstore
Credential store created in ./dircrd/.
GGSCI (sorigen11g.localdomain) 2> alter credentialstore add user R01_GG@ORCL_ORIG , password R01_GG alias R01_GG_EX;
Credential store in ./dircrd/ altered.
En el Host Destino (sdestino11g)
[oracle@sdestino11g]$ . .profile_R01_GG
[oracle@sdestino11g R01_GG]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (sdestino11g.localdomain) 1> add credentialstore
Credential store created in ./dircrd/.
GGSCI (sdestino11g.localdomain) 2> alter credentialstore add user R01_GG@ORCL_DEST, password R01_GG alias R01_GG_RP;
Credential store in ./dircrd/ altered.
SUPPLEMENTAL LOGGING
Creamos un fichero sh que se lanzarán en la BBDD origen (ORCL_ORIG)
[oracle@sdestino11g R01_GG]$ vi supplemental_logging.sh
ggsci <<EOF
dblogin useridalias R01_GG_EX
delete trandata ESQUEMA_ORIG.TABLA1 ALLCOLS
delete trandata ESQUEMA_ORIG.TABLA2 ALLCOLS
delete trandata ESQUEMA_ORIG.TABLA3 ALLCOLS
delete trandata ESQUEMA_ORIG.TABLA4 ALLCOLS
...
add trandata ESQUEMA_ORIG.TABLA1 , ALLCOLS
add trandata ESQUEMA_ORIG.TABLA2 , ALLCOLS
add trandata ESQUEMA_ORIG.TABLA3 , ALLCOLS
add trandata ESQUEMA_ORIG.TABLA4 , ALLCOLS
...
EOF
[oracle@sdestino11g R01_GG]$ chmod +x supplemental_logging.sh
[oracle@sdestino11g R01_GG]$ ./supplemental_logging.sh
PARAMETRIZACIÓN GLOBAL
Se procede a parametrizar los ficheros GLOBALS y MGR
En el Host Origen (sorigen11g)
[oracle@sorigen11g]$ . .profile_R01_GG
[oracle@sorigen11g R01_GG]$ ggsci
GGSCI (sorigen11g.localdomain) 1> EDIT PARAMS ./GLOBALS
-->metemos esto en el vi:
GGSCHEMA R01_GG
ENABLE_HEARTBEAT_TABLE
USE_TRAILDEFS
ENABLEMONITORING
-->guardamos y salimos del vi
Sin salir del ggsci
GGSCI (sorigen11g.localdomain) 2> EDIT PARAMS MGR
-->metemos esto en el vi:
PORT 7812
DYNAMICPORTLIST 7840-8096
AUTOSTART ER *
AUTORESTART ER *, RETRIES 20, WAITMINUTES 1, RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /db/R01_GG/dirdat/*, USECHECKPOINTS, MINKEEPFILES 3
-->Guardamos y salimos del vi
En el Host Destino (sdestino11g)
[oracle@sdestino11g]$ . .profile_R01_GG
[oracle@sdestino11g R01_GG]$ ggsci
GGSCI (sdestino11g.localdomain) 1> EDIT PARAMS ./GLOBALS
-->metemos esto en el vi:
GGSCHEMA R01_GG
ENABLE_HEARTBEAT_TABLE
USE_TRAILDEFS
ENABLEMONITORING
-->guardamos y salimos del vi
Sin salir del ggsci seguimos con el manager
GGSCI (sdestino11g.localdomain) 2> EDIT PARAMS MGR
-->metemos esto en el vi:
PORT 7812
DYNAMICPORTLIST 7840-8096
AUTOSTART ER *
AUTORESTART ER *, RETRIES 20, WAITMINUTES 1, RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /db/R01_GG/dirdat/*, USECHECKPOINTS, MINKEEPFILES 3
-->Guardamos y salimos del vi
CONFIGURACION DEL EXTRACT
En el Host Origen (sorigen11g)
[oracle@sorigen11g]$ . .profile_R01_GG
[oracle@sorigen11g R01_GG]$ ggsci
GGSCI (sorigen11g.localdomain) 1> EDIT PARAMS EXR01
-->metemos esto en el vi:
USERIDALIAS R01_GG_EX
EXTTRAIL /db/R01_GG/dirdat/vp
--TRANLOGOPTIONS HANDLEDFAILOVER (solo en presencia de standy)
--TRANLOGOPTIONS _FAILOVERTARGETDESTID 2 (solo en presencia de standby)
TRANLOGOPTIONS INCLUDEREGIONID excludetag 111
GETTRUNCATES
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
REPORTCOUNT EVERY 15 MINUTES, RATE
REPORTROLLOVER ON SUNDAY
DISCARDFILE /db/R01_GG/dirrpt/r1exdiscard.dsc, APPEND
DISCARDROLLOVER ON SUNDAY
WILDCARDRESOLVE DYNAMIC
OBEY /db/R01_GG/dirprm/obey_exr01
-->guardamos y salimos del vi
Obey
cd $OGG_HOME/dirprm
vi obey_exr01
-->metemos esto en el vi
TABLE ESQUEMA.TABLA1;
TABLE ESQUEMA.TABLA2;
TABLE ESQUEMA.TABLA3;
TABLE ESQUEMA.TABLA4;
...
-->guardamos y salimos de vi
CONFIGURACION DEL DATAPUMP
En el Host Origen (sorigen11g)
[oracle@sorigen11g]$ . .profile_R01_GG
[oracle@sorigen11g R01_GG]$ ggsci
GGSCI (sorigen11g.localdomain) 1> EDIT PARAMS DPR01
-->metemos esto en el vi:
EXTRACT DPR01
USERIDALIAS R01_GG_EX
RMTHOST sdestino11g, MGRPORT 7809 , PARAMS -f -B1048576, TCPBUFSIZE 1048576, TCPFLUSHBYTES 1048576
RMTTRAIL /u01/GG_R01/dirdat/vp
PASSTHRU
OBEY /db/R01_GG/dirprm/obey_exr01
-->guardamos y salimos del vi
CONFIGURACION DEL REPLICAT
En el Host destino (sdestino11g)
[oracle@sorigen11g]$ . .profile_R01_GG
[oracle@sorigen11g R01_GG]$ ggsci
GGSCI (sorigen11g.localdomain) 1> EDIT PARAMS RPR01
-->metemos esto en el vi:
REPLICAT RPR01
USERIDALIAS R01_GG_RP
WILDCARDRESOLVE DYNAMIC
GETTRUNCATES
DBOPTIONS INTEGRATEDPARAMS(disable_on_error Y)
DBOPTIONS INTEGRATEDPARAMS(parallelism 4)
ALLOWNOOPUPDATES
REPORTCOUNT EVERY 15 MINUTES, RATE
REPORTROLLOVER ON SUNDAY
DISCARDFILE /db/R01_GG/dirrpt/r01rpdiscard.dsc, APPEND
DISCARDROLLOVER ON SUNDAY
OBEY /db/R01_GG/dirprm/obey_rpr01
-->guardamos y salimos del vi
Obey
cd $OGG_HOME/dirprm
vi obey_rpr01
-->metemos esto en el vi
MAP ESQUEMA.TABLA1, TARGET ESQUEMA_DEST.TABLA_DEST1;
TABLE ESQUEMA.TABLA2, TARGET ESQUEMA_DEST.TABLA_DEST2,
COLMAP(CAMPO1 = CAMPO_DEST3,
CAMPO2 = CAMPO_DEST1
...);
...
-- O Todas las tablas del Esquema
-- MAP ESQUEMA.*, TARGET ESQUEMA_DEST.*;
-->guardamos y salimos de vi
TABLA HEARTBEAT
Creamos la tabla HeartBeat que se encarga de monitorizar la réplica. Desde sorigen11g con el perfil . .profile_R01_GG cargado
[oracle@sorigen11g R01_GG]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (nodo1siap.localdomain) 1> dblogin useridalias R01_GG_EX
Successfully logged into database.
GGSCI (nodo1siap.localdomain as G58_GG@erezeta1) 2> add heartbeattable
2017-08-16 13:12:40 INFO OGG-14001 Successfully created heartbeat seed table ["GG_HEARTBEAT_SEED"].
2017-08-16 13:12:40 INFO OGG-14032 Successfully added supplemental logging for heartbeat seed table ["GG_HEARTBEAT_SEED"].
2017-08-16 13:12:40 INFO OGG-14000 Successfully created heartbeat table ["GG_HEARTBEAT"].
2017-08-16 13:12:40 INFO OGG-14033 Successfully added supplemental logging for heartbeat table ["GG_HEARTBEAT"].
2017-08-16 13:12:40 INFO OGG-14016 Successfully created heartbeat history table ["GG_HEARTBEAT_HISTORY"].
2017-08-16 13:12:40 INFO OGG-14023 Successfully created heartbeat lag view ["GG_LAG"].
2017-08-16 13:12:40 INFO OGG-14024 Successfully created heartbeat lag history view ["GG_LAG_HISTORY"].
2017-08-16 13:12:40 INFO OGG-14003 Successfully populated heartbeat seed table with [].
2017-08-16 13:12:40 INFO OGG-14004 Successfully created procedure ["GG_UPDATE_HB_TAB"] to update the heartbeat tables.
2017-08-16 13:12:40 INFO OGG-14017 Successfully created procedure ["GG_PURGE_HB_TAB"] to purge the heartbeat history table.
2017-08-16 13:12:40 INFO OGG-14005 Successfully created scheduler job ["GG_UPDATE_HEARTBEATS"] to update the heartbeat tables.
2017-08-16 13:12:40 INFO OGG-14018 Successfully created scheduler job ["GG_PURGE_HEARTBEATS"] to purge the heartbeat history table.
-->guardamos y salimos del vi
- 3 tablas que se encargan de la monitorización de la réplica: GG_HEARTBEAT, GG_HEARTBEAT_HISTORY, GG_HEARTBEAT_SEED.
- 2 Procedures encargados de borrar e historificar las tablas HeartBeat: GG_PURGE_HB_TAB, GG_UPDATE_HB_TAB.
- 2 Jobs para actualizar las tablas.
CREACION EXTRACT
En este punto vamos a crear de verdad los procesos de GG, por lo que el parámetro de BD enable_goldengate_replication debe estar a true.
Desde el sorigen11g, nos conectamos a la BBDD para obtener un SCN asociado al build. Ejecutamos la siguiente query:
SELECT DISTINCT FIRST_CHANGE#, NAME , FIRST_TIME
FROM GV$ARCHIVED_LOG
WHERE DICTIONARY_BEGIN = 'YES'
ORDER BY 1 DESC;
Anotamos el SCN más alto del build que nos pinte en pantalla. Ahora Con el perfil de GG cargado lanzamos:
[oracle@sorigen11g R01_GG]$ ggsci
GGSCI (nodo1siap.localdomain) 1> dblogin useridalias R01_GG_EX
GGSCI (nodo1siap.localdomain) 2> register extract EXR01 with database scn <scn del build obtenido en el punto anterior>
Ahora lo creamos
[oracle@sorigen11g R01_GG]$ ggsci
GGSCI (sorigen11g.localdomain) 1> dblogin useridalias R01_GG_EX
GGSCI (sorigen11g.localdomain) 2> register extract EXR01 with database scn <scn del build obtenido en el punto anterior>
CREACION DATAPUMP
Con el profile cargado se ejecuta desde sorigen11g
[oracle@sorigen11g R01_GG]$ ggsci
GGSCI (sorigen11g.localdomain) 1> add extract DPR01, exttrailsource /db/R01_GG/dirdat/vp
GGSCI (sorigen11g.localdomain) 2> add rmttrail /db/R01_GG/dirdat/vp, extract DPR01
CREACION REPLICAT
Con el profile cargado se ejecuta desde sdestino11g
[oracle@sdestino11g R01_GG]$ ggsci
GGSCI (sdestino11g.localdomain) 1> dblogin useridalias R01_GG_RP
GGSCI (sdestino11g.localdomain) 2> add replicat RPR01, integrated, exttrail /db/R01_GG/dirdat/vp
ARRANQUE REPLICAT
Con el profile cargado se ejecuta desde sdestino11g
[oracle@sdestino11g R01_GG]$ ggsci
GGSCI (sdestino11g.localdomain) 1> alter replicat RPR01 extseqno 0 extrba 0
GGSCI (sdestino11g.localdomain) 2> start replicat RPR01, aftercsn <scn del build en el punto anterior>
ARRANQUE EXTRACT
Con el profile cargado se ejecuta desde sorigen11g
[oracle@sorigen11g R01_GG]$ ggsci
GGSCI (sorigen11g.localdomain) 1> start extract EXR01
GGSCI (sorigen11g.localdomain) 2> view report EXR01
ARRANQUE DATAPUMP
Con el profile cargado se ejecuta desde sorigen11g
[oracle@sorigen11g R01_GG]$ ggsci
GGSCI (sorigen11g.localdomain) 1> start extract DPR01
GGSCI (sorigen11g.localdomain) 2> view report DPR01
A partir de ahí ya debería replicar la info del origen al destino.
En las siguientes entradas pondré otros ejemplos de réplicas GG más complejas: Bidireccionales, con dml_handlers,...
