3
Buts :
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
---------- ----------
--------- ----------
1
2
3
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é.
Aucun commentaire:
Enregistrer un commentaire