Oracle 11g - Golden Gate : 2 Yönlü Replikasyon

1.Veritabanı kurulumu yapılmış olmalıdır.

2. GoldenGate kurulumu yapılmış olmalıdır.


Ankara’da yaratılacak golden gate varlıkları :

Ext1

Pump1

rep1


Eskisehir’de yaratılacak golden gate varlıkları :

Ext2

Pump2

rep2


Yaratılacak klasörler

Ankara

[CMD] -> mkdir C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\aa

[CMD] -> mkdir C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\ab

[CMD] -> mkdir C:\ogg112101_ggs_Windows_x64_ora11g_64bit\discard


Eskisehir

[CMD] -> mkdir C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\ac

[CMD] -> mkdir C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\ad

[CMD] -> mkdir C:\ogg112101_ggs_Windows_x64_ora11g_64bit\discard


İşlemler

Ankara

SQL -> create table test (id number primary key,name varchar2(100));

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> select log_mode from v$database;

LOG_MODE

________________________________________

ARCHIVELOG


SQL> alter database add supplemental log data;

Database altered.

SQL> alter system set recyclebin=off scope=spfile; SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.


SQL> create tablespace gg_tbls datafile 'C:\app\Administrator\oradata\ANKARA\gg_tbls.dbf' size 100m reuse autoextend on;

Tablespace created.

SQL> create user ggate identified by oracle default tablespace gg_tbls quota unlimited on gg_tbls;

User created.

SQL> grant create session, connect, resource to ggate;

Grant succeeded.

SQL> grant dba to ggate; -- just in case

Grant succeeded.

SQL> grant execute on utl_file to ggate;

Grant succeeded.

SQL> exit

[CMD] cd C:\ogg112101_ggs_Windows_x64_ora11g_64bit

[CMD] sqlplus / as sysdba

SQL> @marker_setup.sql

SQL> @ddl_setup.sql

SQL> @role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

SQL> @ddl_enable.sql

SQL> exit


GoldenGate komut satırına geçilir.

GGSCI (ankara) 14> add extract ext1 tranlog begin now EXTRACT added.

GGSCI (ankara) 4> add exttrail C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\aa extract ext1 EXTTRAIL added.

GGSCI (ankara) 16> add extract pump1 exttrailsource C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\aa EXTRACT added.

GGSCI (ankara) 17> add rmttrail C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\ab extract pump1 RMTTRAIL added.

GGSCI (ankara) 14> edit params ext1

extract ext1

userid ggate, password oracle

exttrail C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\tr

ddl include mapped objname source.test;

TRANLOGOPTIONS EXCLUDEUSER ggate

TABLE source.test,

GETBEFORECOLS (

ON UPDATE KEYINCLUDING (id,name),

ON DELETE KEYINCLUDING (id,name));

GGSCI (ankara) 15> edit params pump1

extract pump1

userid ggate, password oracle

rmthost 10.204.90.10, mgrport 7809

rmttrail C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\tr

passthru

table source.test;

GGSCI (ankara) 16> add replicat rep1 exttrail C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\ad REPLICAT added.

GGSCI (ankara) 17> edit params rep1

REPLICAT rep1

ASSUMETARGETDEFS

HANDLECOLLISIONS

USERID ggate, PASSWORD oracle

DISCARDFILE C:\ogg112101_ggs_Windows_x64_ora11g_64bit\discard\discard.txt, append,

MAP target.test, TARGET source.test, KEYCOLS(id);

GGSCI (ankara) 18> dblogin userid ggate password oracle Successfully logged into database.

GGSCI (ankara) 19> add trandata source.test

Logging of supplemental redo data enabled for table SOURCE.TEST.

GGSCI (ankara) 20> info trandata source.test

Logging of supplemental redo log data is enabled for table SOURCE.TEST.

Columns supplementally logged for table SOURCE.TEST: ID.



Eskisehir

SQL -> create table test (id number primary key,name varchar2(100));

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> select log_mode from v$database;

LOG_MODE

________________________________________

ARCHIVELOG


SQL> alter database add supplemental log data;

Database altered.

SQL> alter system set recyclebin=off scope=spfile; SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.


SQL> create tablespace gg_tbls datafile 'C:\app\Administrator\oradata\ANKARA\gg_tbls.dbf' size 100m reuse autoextend on;

Tablespace created.

SQL> create user ggate identified by oracle default tablespace gg_tbls quota unlimited on gg_tbls;

User created.

SQL> grant create session, connect, resource to ggate;

Grant succeeded.

SQL> grant dba to ggate; -- just in case

Grant succeeded.

SQL> grant execute on utl_file to ggate;

Grant succeeded.

SQL> exit

[CMD] cd C:\ogg112101_ggs_Windows_x64_ora11g_64bit

[CMD] sqlplus / as sysdba

SQL> @marker_setup.sql

SQL> @ddl_setup.sql

SQL> @role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

SQL> @ddl_enable.sql

SQL> exit


GoldenGate komut satırına geçilir.

GGSCI (eskisehir) 37> add replicat rep2, exttrail C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\tr, begin now

REPLICAT added.

GGSCI (eskisehir) 10> edit params rep2

replicat rep2

assumetargetdefs

userid target, password oracle

discardfile C:\ogg112101_ggs_Windows_x64_ora11g_64bit\discard\rep1_discard.txt, append, megabytes 10

ddl include mapped

ddlerror default ignore retryop

map source.test, target target.test, KEYCOLS(id);

GGSCI (eskisehir) 3> add extract ext2 tranlog begin now EXTRACT added.

GGSCI (eskisehir) 4> add exttrail discardfile C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\ac extract ext2 EXTTRAIL added.

GGSCI (eskisehir) 5> add extract pump2 exttrailsource C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\ac EXTRACT added.

GGSCI (eskisehir) 6> add rmttrail C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\ad extract pump2 RMTTRAIL added.

GGSCI (eskisehir) 31> edit params ext2

EXTRACT ext2

USERID ggate, PASSWORD oracle

EXTTRAIL C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\ac

TRANLOGOPTIONS EXCLUDEUSER ggate

TABLE target.test,

GETBEFORECOLS (

ON UPDATE KEYINCLUDING (id,name),

ON DELETE KEYINCLUDING (id,name));

GGSCI (eskisehir) 32> edit params pump2

EXTRACT pump2

USERID ggate, PASSWORD oracle

RMTHOST 10.204.90.11, MGRPORT 7809

RMTTRAIL C:\ogg112101_ggs_Windows_x64_ora11g_64bit\dirdat\ac

PASSTHRU

table target.test;

GGSCI (eskisehir) 18> dblogin userid ggate password oracle Successfully logged into database.

GGSCI (eskisehir) 19> add trandata source.test

Logging of supplemental redo data enabled for table SOURCE.TEST.

GGSCI (eskisehir) 20> info trandata source.test

Logging of supplemental redo log data is enabled for table SOURCE.TEST.

Columns supplementally logged for table SOURCE.TEST: ID.


EXTRACT ve REPLICAT’ları başlat

Sırasıyla olmalı: Önce ankara’da extract’lar başlatılır

GGSCI (ankara) 31> start extract ext1

Sending START request to MANAGER … EXTRACT EXT1 starting

GGSCI (ankara) 23> start extract pump1

Sending START request to MANAGER … EXTRACT PUMP1 starting

GGSCI (ankara) 34> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING EXTRACT RUNNING PUMP1 00:00:00 00:00:07 EXTRACT RUNNING EXT1 00:00:00 00:00:03


Sonra Eskisehir’de replicat başlatılır

GGSCI (eskisehir) 38> start replicat rep2

Sending START request to MANAGER … REPLICAT REP2 starting


Sonra Eskisehir’de extract’lar başlatılır:

GGSCI (eskisehir) 22> start extract ext2

Sending START request to MANAGER … EXTRACT EXT2 starting

GGSCI (eskisehir) 23> start extract pump2

Sending START request to MANAGER … EXTRACT PUMP2 starting

GGSCI (eskisehir) 130> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT2 00:00:00 00:00:02

EXTRACT RUNNING PUMP2 00:00:00 00:00:02

REPLICAT RUNNING REP2 00:00:00 00:00:09


Sonra Ankara’da replicat başlatılır

GGSCI (ankara) 38> start replicat rep1

Sending START request to MANAGER … REPLICAT REP1 starting

GGSCI (ankara) 130> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:00:02

EXTRACT RUNNING PUMP1 00:00:00 00:00:02

REPLICAT RUNNING REP1 00:00:00 00:00:09

Sonra test edilir:

Karşılıklı olarak şu komutlar çalıştırılabilir

Ankara

SQL> insert into test values (1,'ank);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test order by 1;

       ID NAME

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

        1 ank

Eskisehir

SQL> select * from test order by 1;

       ID NAME

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

        1 ank

SQL> insert into test values (2,'esk');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test order by 1;

       ID NAME

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

        1 ank

        2 esk

Ankara

SQL> select * from test order by 1;

       ID NAME

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

        1 ank

        2 esk