Faire de la réplication Golden gate entre oracle et SQLserver pour la table res_charges afin d’avoir très simplement une image a un temps t pour une table qui coté oracle fait 20G et 124 millions de rangées.

On va commencer les test par un table relativement simple pour être a l’aise puis on ira vers res_charges.

Le document est fait dans l’ordre chronologique des actions mais on aurait pu le diviser en 4 parties :

1 – system incluant les . profiles les tnsnames, les passwords, les resource cluster

2 – les Sql (DDL) ,

3 – les configurations golden gate incluant le defgen,

4 – les tests .

4 Cluster :

Notre base source est en cluster RAC sur linux.

Un aperçu des ressources cluster :

On a 2 choses a regarder la vip pocrac-gg et la resource ggateapp

--------------------------------------------------------------------------------

NAME TARGET STATE SERVER STATE_DETAILS

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.LISTENER.lsnr

ONLINE ONLINE pocracm1

ONLINE ONLINE pocracm2

ora.POCARCH.dg

ONLINE ONLINE pocracm1

ONLINE ONLINE pocracm2

ora.POCDB.dg

ONLINE ONLINE pocracm1

ONLINE ONLINE pocracm2

ora.POCOCR.dg

ONLINE ONLINE pocracm1

ONLINE ONLINE pocracm2

ora.POCREDO.dg

ONLINE ONLINE pocracm1

ONLINE ONLINE pocracm2

ora.asm

ONLINE ONLINE pocracm1 Started

ONLINE ONLINE pocracm2

ora.gsd

OFFLINE OFFLINE pocracm1

OFFLINE OFFLINE pocracm2

ora.net1.network

ONLINE ONLINE pocracm1

ONLINE ONLINE pocracm2

ora.ons

ONLINE ONLINE pocracm1

ONLINE ONLINE pocracm2

ora.registry.acfs

ONLINE ONLINE pocracm1

ONLINE ONLINE pocracm2

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ggateapp

1 ONLINE ONLINE pocracm1

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE pocracm2

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE pocracm1

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE pocracm1

ora.cvu

1 ONLINE ONLINE pocracm1

ora.mg.db

1 OFFLINE OFFLINE

2 OFFLINE OFFLINE

ora.oc4j

1 ONLINE ONLINE pocracm1

ora.pocracm1.vip

1 ONLINE ONLINE pocracm1

ora.pocracm2.vip

1 ONLINE ONLINE pocracm2

ora.pocrad.db

1 ONLINE ONLINE pocracm1 Open

2 ONLINE ONLINE pocracm2 Open

ora.pocrad.mysrvb.svc

1 ONLINE ONLINE pocracm1

2 ONLINE ONLINE pocracm2

ora.pocrad.ocr_srv.svc

1 OFFLINE OFFLINE

2 OFFLINE OFFLINE

ora.pocrad.precosrvb.svc

1 ONLINE ONLINE pocracm1

ora.pocrad.precosrvb_preconnect.svc

1 ONLINE ONLINE pocracm2

ora.pocrad.radairrmpocsrv.noam.transat.local.svc

1 OFFLINE OFFLINE

2 OFFLINE OFFLINE

ora.pocrad.radentpocsrv.noam.transat.local.svc

1 OFFLINE OFFLINE

ora.pocrad.radffspocsrv.noam.transat.local.svc

1 ONLINE ONLINE pocracm1

2 ONLINE ONLINE pocracm2

ora.pocrad.radgdstapocsrv.noam.transat.local.svc

1 OFFLINE OFFLINE

2 OFFLINE OFFLINE

ora.pocrad.radgdstbpocsrv.noam.transat.local.svc

1 OFFLINE OFFLINE

2 OFFLINE OFFLINE

ora.pocrad.radtsreportingpocsrv.noam.transat.local.svc

1 OFFLINE OFFLINE

2 OFFLINE OFFLINE

ora.pocrad.radwebjobprdsrv.svc

1 ONLINE ONLINE pocracm1

ora.pocrad.radwebjobprdsrv_preconnect.svc

1 ONLINE ONLINE pocracm2

ora.pocrad.radwebpocsrv.noam.transat.local.svc

1 OFFLINE OFFLINE

2 OFFLINE OFFLINE

ora.pocrad.sale_ro.svc

1 OFFLINE OFFLINE

2 OFFLINE OFFLINE

ora.pocrad.sale_rw.svc

1 ONLINE ONLINE pocracm1

ora.pocrad.sale_rw_preconnect.svc

1 ONLINE ONLINE pocracm2

ora.pocrad.sqlsrv.svc

1 ONLINE ONLINE pocracm1

2 ONLINE ONLINE pocracm2

ora.scan1.vip

1 ONLINE ONLINE pocracm2

ora.scan2.vip

1 ONLINE ONLINE pocracm1

ora.scan3.vip

1 ONLINE ONLINE pocracm1

pocrac-gg

1 ONLINE ONLINE pocracm1

5 Choix de la table pour les premiers tests :

Pour la replication il est preferable d avoir une clefs unique a la source et a la destination.

On veut avoir plusieurs colonnes representatives de la table RES_charge d ou les ajouts

SQL> desc MG.test

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NUMBER

SQL> alter table MG.test add (description varchar2(10));

SQL> alter table MG.test add (mdate date);

SQL> alter table MG.test add (idnum float(9));

SQL> create index MG.IND_TEST on MG.test (id);

SQL> alter table mg.test add (constraint PK_TEST primary key (id) using index MG.IND_TEST enable validate);

SQL> select * from MG.test;

ID DESCRIPTIO MDATE IDNUM

---------- ---------- --------- ----------

6 Réinitialiser les password de ggs_owner :

6.1 Cote instance ORACLE POCRAD :

[pocracm1.noam.transat.local:oracle:POCRAD1:/home/oracle]# sqlplus / as sysdba

SQL> alter user ggs_owner identified by xxxxx;

6.2 Coté ASM :

[pocracm1.noam.transat.local:oracle:POCRAD1:/home/oracle]# su - grid

[pocracm1.noam.transat.local:grid:\:/home/grid]#export ORACLE_HOME=$GRID_HOME

[pocracm1.noam.transat.local:grid:\:/home/grid]# export ORACLE_SID=+ASM1

[pocracm1.noam.transat.local:grid:\:/home/grid]#sqlplus / as sysasm

SQL> alter user ggs_owner identified by xxxxx;

SQL> exit

7 Mis a jour des .profile du user unix oracle sur les 2 nœuds :

Ajout de :

La variable GGS_HOME : /apps/oracle/ggates/12

Mis à jour de la variable Path de $GGS_HOME

Mis à jour de LD_LIB_PATH pour y inclure $GGS_HOME/lib

8 GOLDEN GATE le manager :

[pocracm1.noam.transat.local:oracle:POCRAD1:/home/oracle]# cd $GGS_HOME

[pocracm1.noam.transat.local:oracle:POCRAD1:/apps/oracle/ggates/12/dirprm]# vi mgr.prm

PORT 7809

LAGREPORTMINUTES 5 -- Interval at which lag is checked

LAGINFOMINUTES 5 -- Threshold at which lag is reported

LAGCRITICALMINUTES 15 -- Critical threshold reporting value

PURGEOLDEXTRACTS /apps/oracle/ggates/12/trail/poc/wsrad/* MINKEEPHOURS 3 FREQUENCYMINUTES 20

--------------

USERID ggs_owner@GGATE, PASSWORD xxxxxxxxx

8.1 TNS service oracle pour ggate :

[pocracm1.noam.transat.local:oracle:POCRAD1:/apps/oracle/11.2.0.2/network/admin]# vi tnsnames.ora

GGATE=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = poccrs.noam.transat.local)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = sale_rw.noam.transat.local)

)

Le service ne me plait pas.

8.2 Ajout d un service pour ggate :

# srvctl add service -d POCRAD -s ggatesvc -r POCRAD2 -a POCRAD1

# srvctl start service -d POCRAD -s ggatesvc

# srvctl status service -d POCRAD -s ggatesvc

Service ggatesvc is running on instance(s) POCRAD2

Ajout du service au tnsnames.ora sur les 2 noeuds

GGATE=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = poccrs.noam.transat.local)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ggatesvc.noam.transat.local)

)

Idem sur le noeud 2:

[pocracm1.noam.transat.local:oracle:POCRAD1:/apps/oracle/11.2.0.2/network/admin]# ssh pocracm2

[pocracm2.noam.transat.local:oracle:POCRAD2:/apps/oracle/11.2.0.2/network/admin]#vi /apps/oracle/11.2.0.2/network/admin/tnsnames.ora

GGATE=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = poccrs.noam.transat.local)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ggatesvc.noam.transat.local)

)

9 Creation des extracts :

[pocracm1.noam.transat.local:oracle:POCRAD1:/apps/oracle/ggates/12]# cd dirprm

[pocracm1.noam.transat.local:oracle:POCRAD1:/apps/oracle/ggates/12]# ggsci

GGSCI (pocracm1.noam.transat.local) 4> dblogin userid ggs_owner@ggate,password U1509ggspoc

GGSCI (pocracm1.noam.transat.local as ggs_owner@POCRAD2) 5> add extract wsggradix tranlog, threads 2 , begin now

ERROR: Invalid group name (must be at most 8 characters).

Le nom de l extract doit être <8 caracteres la c est trop long.

GGSCI (pocracm1.noam.transat.local as ggs_owner@POCRAD2) 6> exit

On va reduire les noms et recommencer

[pocracm1.noam.transat.local:oracle:POCRAD1:/apps/oracle/ggates/12]# ggsci

GGSCI (pocracm1.noam.transat.local) 1> dblogin userid ggs_owner@ggate,password U1509ggspoc

GGSCI (pocracm1.noam.transat.local as ggs_owner@POCRAD2) 2> add extract wsrad tranlog, threads 2 , begin now

EXTRACT added.

GGSCI (pocracm1.noam.transat.local as ggs_owner@POCRAD2) 4> add exttrail /apps/oracle/ggates/12/trail/poc/wsrad/ws ,extract wsrad

EXTTRAIL added.

GGSCI (pocracm1.noam.transat.local as ggs_owner@POCRAD2) 5> add extract wsraddp, EXTTRAILSOURCE /apps/oracle/ggates/12/trail/poc/wsrad/ws

GGSCI (pocracm1.noam.transat.local as ggs_owner@POCRAD2) 6> add rmttrail T:\GG_Radixx\ws, extract wsraddp

10 Switch over goldengate :

On passe de pocracm1 a pocracm2

/apps/grid/bin]#./crsctl start resource ggateapp -n pocracm2.noam.transat.local

CRS-2672: Attempting to start 'ggateapp' on 'pocracm2'

CRS-2676: Start of 'ggateapp' on 'pocracm2' succeeded

11 Start des extract :

GGSCI (pocracm2.noam.transat.local) 2> start extract WSRAD

J’ai une erreur alors je vérifie la log :

/apps/oracle/ggates/12#tail -f ggserr.log

2015-09-08 14:22:15 INFO OGG-01851 Oracle GoldenGate Capture for Oracle, wsrad.prm: filecaching started: thread ID: 1257642304.

2015-09-08 14:22:15 WARNING OGG-01262 Oracle GoldenGate Capture for Oracle, wsrad.prm: The call to the ggDirIsWritable() function from line 2862 in cm_cachedir_add()s/oracle/ggates/12/dirtmp to hold temporary cachefiles does not exist or is not writable'.

/apps/oracle/ggates/12#mkdir -p /apps/oracle/ggates/12/dirtmp

/apps/oracle/ggates/12#./ggsci

GGSCI (pocracm2.noam.transat.local) 2> start extract WSRAD

GGSCI (pocracm2.noam.transat.local) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT ABENDED R2DTP 00:00:00 4629:56:08

EXTRACT ABENDED RADPPR2 00:00:00 2813:25:05

EXTRACT RUNNING WSRAD 00:00:00 00:22:39

EXTRACT STOPPED WSRADDP 00:00:00 00:17:33

GGSCI (pocracm2.noam.transat.local) 3> edit params WSRADDP

extract wsraddp

USERID GGS_OWNER@GGATE, PASSWORD xxxxxxxxxxxx

RMTHOST ppr-mFGFGbi005, MGRPORT 7809

RMTTRAIL T:\GG_Radixx\ws

GETTRUNCATES

PASSTHRU

table MG.TEST;

GGSCI (pocracm2.noam.transat.local) 5> start extract WSRADDP

GGSCI (pocracm2.noam.transat.local) 6> info all

GGSCI (pocracm2.noam.transat.local as ggs_owner@POCRAD2) 2> add trandata MG.test

12 Test #1 : TEST

[pocracm2.noam.transat.local:oracle:POCRAD2:/apps/oracle/ggates/12]#sqlplus / as sysdba

SQL> truncate table MG.test;

SQL> insert into MG.test values (1,null,null,null);

SQL> commit;

SQL> select * from Mg.test;

SQL> insert into mg.test values (2,null,null,null);

SQL> commit;

SQL> select * from Mg.test;

13 Defgen :

Sans le DEFgen on aura une erreur lors des update, delete…

/apps/oracle/ggates/12#mkdir /net/mtlunt48/backup/ORACLE_SW/GOLDEN_GATE/poc/dirdef

/apps/oracle/ggates/12#ln -s /net/mtlunt48/backup/ORACLE_SW/GOLDEN_GATE/poc/dirdef dirdef

/apps/oracle/ggates/12#./ggsci

GGSCI (pocracm2.noam.transat.local) 3> edit params defgen

defsfile /apps/oracle/ggates/12/dirdef/test.def

USERID GGS_OWNER@GGATE, PASSWORD xxxxxxxx

table MG.test

#./defgen paramfile "/net/mtlunt48/backup/ORACLE_SW/GOLDEN_GATE/poc/dirprm/defgen.prm"

[pocracm2.noam.transat.local:oracle:POCRAD2:/apps/oracle/ggates/12/dirdef]#ls -alrt

-rw-r-----+ 1 oracle oinstall 1417 Sep 9 09:52 test.def

Ce fichier doit etre envoye sur le serveur target dans le repertoire

$GGS_HOME/dirdef.

14 Replicat :

Voila la tete du cote target SQLSERVER le parametrage du replicat :

REPLICAT WSRAD

TARGETDB GoldenGate, USERID svcggs, PASSWORD xxxxxxxx

--ASSUMETARGETDEFS

HANDLECOLLISIONS

GETTRUNCATES

SOURCEDEFS D:\ggs_121210_Windows_x64_MSSQL_64bit\dirdef\test.def

--SOURCEDEFS D:\ggs_121210_Windows_x64_MSSQL_64bit\dirdef\GG_Radixx.def

MAP mg.test, TARGET dbo.test;

--MAP mg.res_charges, TARGET dbo.res_charges;

15 Test #2 :

[pocracm2.noam.transat.local:oracle:POCRAD2:/apps/oracle/ggates/12]#sqlplus / as sysdba

SQL> insert into MG.test values (3,'defgen',sysdate,3.00);

SQL> commit;

SQL> select * from MG.test;

SQL> update MG.test set idnum=3.01 where idnum=3.00;

SQL> commit;

SQL> truncate table MG.test;

SQL> insert into MG.test values (3,'defgen',sysdate,3.00);

SQL> commit;

SQL> update MG.test set idnum=3.01 where id=3;

SQL> commit;

SQL> delete MG.test where id=3;

SQL> commit;

SQL> insert into MG.test values (3,'defgen',sysdate,3.00);

SQL> commit;

SQL> update MG.test set idnum=3.01 where idnum=3.00;

SQL> commit;

SQL> insert into MG.test values (1,null,sysdate,1.00);

SQL> commit;

SQL> update MG.test set idnum=1.01 where id=1;

SQL> commit;

SQL> update MG.test set idnum=1.02 where id=1;

SQL> commit;

Tout est parfait, insert, delete, truncate et update.

16 La table Res_charges volet SQL :

16.1 Ajout du db_link pour res_charges vers radixqa :

16.1.1 Tnsnames :

Dans le tns je ne montre que ce que j’ai modifié pour pas allonger le document

/apps/oracle/11.2.0.2/network/admin]#vi tnsnames.ora

radqa=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = devrtt98)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rad)

)

16.1.2 Le DDL du db_link :

Pas de sécurité a ce niveau la!

create public database link res_charges

connect to radqa identified by xxxxxxx using 'radqa';

select count(1) from COD_MEAL_PREFERENCE@res_charges;

16.2 Création de la table RES_CHARGES :

SQL>create table mg.res_charges as( select * from res_charges@res_charges where 1=2);

SQL> CREATE UNIQUE INDEX MG.PK_RES_CHARGES ON MG.RES_CHARGES

SQL> ALTER TABLE MG.RES_CHARGES ADD (

CONSTRAINT PK_RES_CHARGES

PRIMARY KEY

(RES_CHARGE_ID)

USING INDEX MG.PK_RES_CHARGES

ENABLE VALIDATE);

17 Ajout de res_charges aux extracts :

17.1 Les paramétrages :

GGSCI (pocracm2.noam.transat.local) 2> edit params WSRAD

extract wsrad

USERID GGS_OWNER@GGATE, PASSWORD xxxxxxxxxxxxxxx

TRANLOGOPTIONS ASMUSER ggs_owner@ASM, ASMPASSWORD xxxxxxxxxxxxx

EXTTRAIL /apps/oracle/ggates/12/trail/poc/wsrad/ws

table MG.TEST;

table MG.RES_CHARGES;

GGSCI (pocracm2.noam.transat.local) 4> edit params WSRADDP

extract wsraddp

USERID GGS_OWNER@GGATE, PASSWORD U1509ggspoc

RMTHOST ppr-mtlbi005, MGRPORT 7809

RMTTRAIL T:\GG_Radixx\ws

GETTRUNCATES

PASSTHRU

table MG.TEST;

table MG.RES_CHARGES;

17.2 Defgen ajout de res_charges :

GGSCI (pocracm2.noam.transat.local) 5> edit params defgen

defsfile /apps/oracle/ggates/12/dirdef/wsrad.def

USERID GGS_OWNER@GGATE, PASSWORD xxxxxxxxxxxxxxx

table MG.test;

table MG.RES_CHARGES;

GGSCI (pocracm2.noam.transat.local) 6> stop extract wsrad*

/apps/oracle/ggates/12#./defgen paramfile "/net/mtlunt48/backup/ORACLE_SW/GOLDEN_GATE/poc/dirprm/defgen.prm"

18 TEST #3 : res_charges :

SQL> insert into mg.test values (2,null,null,null);

SQL> commit;

SQL> select * from mg.test;

SQL> insert into MG.res_charges

(select * from res_charges@res_charges where rownum <10);

SQL> commit;

SQL> update MG.res_charges set

code_type = 'AIR_' 2 where res_charge_id=31793715;

SQL> commit;

18.1 Add trandata :

GGSCI (pocracm2.noam.transat.local) 1> dblogin userid ggs_owner, password xxxxxxxxxxx

GGSCI (pocracm2.noam.transat.local as ggs_owner@POCRAD2) 3> add trandata MG.RES_CHARGES

GGSCI (pocracm2.noam.transat.local as ggs_owner@POCRAD2) 5> info all

GGSCI (pocracm2.noam.transat.local as ggs_owner@POCRAD2) 6> stop extract wsrad*

GGSCI (pocracm2.noam.transat.local as ggs_owner@POCRAD2) 7> alter extract WSRAD begin now

GGSCI (pocracm2.noam.transat.local as ggs_owner@POCRAD2) 8> alter extract WSRADDP begin now

GGSCI (pocracm2.noam.transat.local as ggs_owner@POCRAD2) 9> start extract wsrad*

18.2 Test #3 prise 2 :

[pocracm2.noam.transat.local:oracle:POCRAD2:/apps/oracle/ggates/12]#sqlplus / as sysdba

SQL> truncate table Mg.res_charges;

SQL> insert into MG.res_charges (select * from res_charges@res_charges where rownum <106);

SQL> commit;

SQL> update MG.res_charges set code_type = 'AIR_' where res_charge_id=31793715;

SQL> commit;

SQL> select code_type from MG.res_charges where res_charge_id=31793715;

SQL> select max (res_charge_id) from MG.res_charges;

SQL> select count(1) from res_charges@res_charges where RES_CHARGE_ID>31800248 and RES_CHARGE_ID<31800290;

SQL> insert into MG.res_charges (select * from res_charges@res_charges where RES_CHARGE_ID>31800248 and RES_CHARGE_ID<31800290);

SQL> commit;

SQL> select count(1) from MG.res_charges;

SQL> update MG.res_charges set CHARGE_COMMENT='MG GG JO' where RES_CHARGE_ID=31793744;

SQL> commit;

SQL> delete from Mg.res_charges where RES_CHARGE_ID=31800248;

SQL> commit;

SQL> delete from Mg.res_charges where RES_CHARGE_ID=31800249;

SQL> commit;

SQL> truncate table Mg.res_charges;

SQL> insert into MG.res_charges (select * from res_charges@res_charges where rownum <100000);

SQL> commit;

19 Conclusion:

Faut passer par une étape DEFGEN voilà la grosse nouveauté.

TANOS dba

Rechercher dans ce blog

mardi 15 septembre 2015

oracle golden gate : oracle to SQLSERVER

3 Buts :

4 Cluster :

5 Choix de la table pour les premiers tests :

6 Réinitialiser les password de ggs_owner :

6.1 Cote instance ORACLE POCRAD :

6.2 Coté ASM :

7 Mis a jour des .profile du user unix oracle sur les 2 nœuds :

8 GOLDEN GATE le manager :

8.1 TNS service oracle pour ggate :

8.2 Ajout d un service pour ggate :

9 Creation des extracts :

10 Switch over goldengate :

11 Start des extract :

12 Test #1 : TEST

13 Defgen :

14 Replicat :

15 Test #2 :

16 La table Res_charges volet SQL :

16.1 Ajout du db_link pour res_charges vers radixqa :

16.1.1 Tnsnames :

16.1.2 Le DDL du db_link :

16.2 Création de la table RES_CHARGES :

17 Ajout de res_charges aux extracts :

17.1 Les paramétrages :

17.2 Defgen ajout de res_charges :

18 TEST #3 : res_charges :

18.1 Add trandata :

18.2 Test #3 prise 2 :

19 Conclusion: