Press ESC to close

Oracle 19.1.0.4 Golden Gate Classic Architecture Install, Configuration And Direct Load

Classic Architecture Kurulumu ardından iki veritabanı arasında Initial Load yönetimi olarak Direct Bulk Load kullanarak hızlı bir veri transferi yapacağız, sonrasında Kaynak veritabanında extract, pump ve hedef veritabanında replicate servislerini çalıştırarak schema ların senkron olmasını takip edeceğiz.

Kaynak Veriabanımız:

  • Hostaname: sourcegg
  • IP: 150.150.100.201
  • DB Version : 11.2.0.4
  • SID . ORCLSDB

Hedef Veritabanı

  • Hostname : targetgg
  • IP : 150.150.100.191
  • DB Verison: 19.3.0.0
  • SID: ORCLDB

İlk kurulum başlangıç için aynı aşamaları içeriyor. Öncelikle dizinlerimizi ve ortam değişkenlerimizi ayarlayalım. 

--Kaynak Veritabanı için
# mkdir /u01/ogg/oggsrc
--Hedef Veritabanı için
# mkdir /u01/ogg/oggtrg

--Ortam Değişkenleri bash_profile aşağıdaki gibidir.

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=sourcegg.ttnet.com; export ORACLE_HOSTNAME
ORACLE_UNQNAME=ORCLSDB; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
GRID_HOME=/u01/app/11.2.0.4/grid; export GRID_HOME
DB_HOME=$ORACLE_BASE/product/11.2.0.4/db; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=ORCLSDB; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch:$BASE_PATH; export PATH

export OGG_HOME=/u01/ogg/oggsrc

export PATH=$ORACLE_HOME/bin:$OGG_HOME/bin:$BASE_PATH
export TNS_ADMIN=/u01/app/oracle/product/11.2.0.4/db/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export LD_LIBRARY_PATH=${OGG_HOME}lib:${LD_LIBRARY_PATH}
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

--Kurulum için Oracle User ile
#cd /home/oracle/Software
./runInstaller

(Resimdeki path yanlış orayı dikkate almayın doğrusu; /u01/ogg/oggsrc)

Target Kurulumu

Kurulum Tamamlandıktan sonra, benim senaryomda kaynak ve hedegf için Veitablarında yönetim amaçlı ggadmin adında bir schema oluşturuldu bu schemanın default tablespace tbs_ggadmin oluşturuldu, GGADMIN e DBA yetkisi verildi.

Aktarılacak ve senkron olacak scheamalar ise TARGETGG scheması her iki tarafta da oluşturuldu create session, resource ve tablespace yazma yetkileri verildi.

Sonrası GGADMIN için, her iki veritabanında da;

SQL> show parameter enable_golden

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
enable_goldengate_replication        boolean     FALSE

SQL> alter system set enable_goldengate_replication=true scope=both;


System altered.
SQL> show parameter streams_pool

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

SQL> alter system set streams_pool_size=300m scope=both;


System altered.
SQL> select supplemental_log_data_min, force_logging from v$database;
SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
NO       NO

SQL> alter database add supplemental log data;
SQL> alter database force logging;
SQL> alter system set enable_goldengate_replication=true;

BEGIN
DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE
(grantee=>'GGADMIN',
privilege_type=>'capture',
grant_optional_privileges=>'DV_GOLDENGATE_ADMIN');
END;
/

Veritabanımız archivelog modda olmalı.

OGG_HOME dizini altında sys ile aşağıdaki scriptleri çalıştıralım yazı çok uzuyor o yüzden çıktılarını yazmıyorum ggadmin userına yetki verilecek bu scriptlerde;

cd $OGG_HOME
SQL> @marker_setup.sql;
SQL> @ddl_setup.sql;
SQL> @role_setup.sql;
SQL> @ddl_enable.sql;
SQL> @prvtlmpg.plb

Bu işlemlerin ardından ggsci diyerek GoldenGate Komut arayüzüne bağlanalım

GGSCI (sourcegg.ttnet.com) 4> edit param mgr

PORT 7809
DynamicPortlist 20000-20099
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2
AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60

Her iki veritabanımız içinde wallet oluşturalım ve Alias tanımlayalım, Kaynak Veritabanımız için;

GGSCI (sourcegg.ttnet.com) 1> create wallet

Created wallet.

Opened wallet.

GGSCI (sourcegg.ttnet.com) 2> add credentialstore

Credential store created.

GGSCI (sourcegg.ttnet.com) 3> alter credentialstore add user ggadmin@orclsdb password Welcome1 Alias oggadmin_sdb

Credential store altered.

GGSCI (sourcegg.ttnet.com) 4> info credentialstore

Reading from credential store:

Default domain: OracleGoldenGate

  Alias: oggadmin_sdb
  Userid: ggadmin@orclsdb


GGSCI (sourcegg.ttnet.com) 5> dblogin useridalias oggadmin_sdb
Successfully logged into database.

Hedef Veritabanımız için;

GGSCI (targetgg.ttnet.com) 6> create wallet

Created wallet.

Opened wallet.

GGSCI (targetgg.ttnet.com) 7> add credentialstore

Credential store created.

GGSCI (targetgg.ttnet.com) 8> alter credentialstore add user ggadmin@orcldb password Welcome1 alias oggadmin_tdb;

Credential store altered.

GGSCI (targetgg.ttnet.com) 9> info credentialstore

Reading from credential store:

Default domain: OracleGoldenGate

  Alias: oggadmin_tdb
  Userid: ggadmin@orcldb


GGSCI (targetgg.ttnet.com) 10> dblogin useridalias oggadmin_tdb
Successfully logged into database.

Extract Oluşturalım;

GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 9> Register Extract extwest database

2021-01-27 18:56:38  INFO    OGG-02003  Extract EXTWEST successfully registered with database at SCN 932477.


GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 10> add extract extwest, integrated tranlog , begin now
EXTRACT (Integrated) added.


GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 11> add exttrail ./dirdat/ew, extract extwest, megabytes 10
EXTTRAIL added.

GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 12> edit param extwest


Extract extwest
UserIdAlias oggadmin_sdb
TranlogOptions IntegratedParams (max_sga_size 256)
ExtTrail ./dirdat/ew
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL INCLUDE ALL
TABLE TESTGG.testext;
TABLE TESTGG.CREATETABLE_TEST;

GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 15> ADD TRANDATA TESTGG.*;

GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 16> start extwest

Sending START request to MANAGER ...
EXTRACT EXTWEST starting


GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 17> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTWEST     00:00:00      00:08:19

Extract Kısmını tamamladık şimdi Pump Kısmını ayarlayalım. 

GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 28> edit param pwest

Extract pwest
UserIdAlias oggadmin_sdb
RMTHOST targetgg, mgrport 7809
rmttrail ./dirdat/pe
DDL INCLUDE ALL
TABLE TESTGG.*;

GSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 29> add extract pwest, exttrailsource ./dirdat/ew
EXTRACT added.

GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 30> add rmttrail ./dirdat/pe, extract pwest, megabytes 10
RMTTRAIL added.

GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 32> start pwest

Sending START request to MANAGER ...
EXTRACT PWEST starting

GGSCI (sourcegg.ttnet.com as ggadmin@ORCLSDB) 45> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTWEST     00:00:24      00:00:02
EXTRACT     RUNNING     PWEST       00:00:00      00:07:50

INITIAL LOAD

Kaynak Veritabanımız ile senkron olacak tablolar veya schema için hedef veritabanımızın başlangıçta hızlı bir şekilde eşitleme ihtiyacımız vardır, sonrasın da extract, pump ve raplicat servisleri veritabanlarını veya tablolarımızı eşit tutmak için çalışacaklardır. Initial Load yöntemleri GoldeGate ten bağımsız olarak ta yapılabilir. Bu yöntemler Transportable Tablespace, RMAN Duplicate, Data Pump, exp-imp vb.. şeklinde bir çok yöntemle yapılabilir, fakat GoldenGate Kullanacaksak genel yöntemler aşağıdaki gibidir.

  • File To Replicate (Trail) –> Yavaş
  • File to DB Utility
  • Direc Load (Specilal) –>Hızlı
  • Direct Bulk Load (SQL *Loader API) –En Hızlı

Burada ben sadece Direct Bulk Load (SQL*Loader API) yöntemini kullanacağım. İşlem bittikten sonra hedef veritabanında, indexleri ve constraintler mutlaka kontrol edilmelidir işlem sırasında hedefte bu tür bağımlılıklar otomatik disable ediliyor, bu performans açısından oldukça önemli fakat işlem sonrası tutarlılık açısından butun constraintler tekrar oluşturulmalıdır.

Başlıyalım o halde; İlk olarak kaynak veritabanında eini adında bir spacial extract tanımlayacağız

GGSCI (sourcegg.ttnet.com) 5> edit param eini
Extract eini
UserIdAlias oggadmin_sdb
rmthost targetgg, mgrport 7809
RMTTASK replicat, GROUP rini
TABLE TESTGG.*;

GGSCI (sourcegg.ttnet.com) 6> add extract eini , sourceistable
EXTRACT added.

GGSCI (sourcegg.ttnet.com) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTWEST     00:00:08      00:00:03
EXTRACT     RUNNING     PWEST       00:00:00      00:00:00

--info all komutu ile special process leri göremeyiz bunun için aşağıdaki sorguları kullanmalıyız

GGSCI (sourcegg.ttnet.com) 8> info eini

EXTRACT    EINI      Initialized   2021-01-28 10:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

--veya

GGSCI (sourcegg.ttnet.com) 13> info all,allprocesses

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EINI
EXTRACT     RUNNING     EXTWEST     00:02:18      00:00:01
EXTRACT     RUNNING     PWEST       00:00:00      00:00:09

--yada

GGSCI (sourcegg.ttnet.com) 15> info *,tasks

EXTRACT    EINI      Initialized   2021-01-28 10:28   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

Target Veritabanı tarafında rini isimli bir special replicat tanımlayacağız

GGSCI (targetgg.ttnet.com) 3> edit param rini
REPLICAT rini
USERIDALIAS oggadmin_tdb
BULKLOAD
MAP TESTGG.*, TARGET TESTGG.*;


GGSCI (targetgg.ttnet.com) 5> add replicat rini, specialrun
REPLICAT added.

GGSCI (targetgg.ttnet.com) 7> info *,tasks

REPLICAT   RINI      Initialized   2021-01-28 10:38   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:46 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

yada

GGSCI (targetgg.ttnet.com) 8> info rini

REPLICAT   RINI      Initialized   2021-01-28 10:38   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:01:04 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN


yada hepsini bir arada görmek için

GGSCI (targetgg.ttnet.com) 9> info all,allprocesses

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     RINI

Kaynak ve hedef veritabanlarında ki tablolarımıza bir bakalım

GGSCI (sourcegg.ttnet.com) 16> sh sqlplus TESTGG/Welcome1


SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 10:41:06 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>SQL> select count(*) from TBL_DEVICE;

  COUNT(*)
----------
       169

SQL> select count(*) from TESTEXT;

  COUNT(*)
----------
         2

TArget Tarafta

GGSCI (targetgg.ttnet.com) 10> sh sqlplus TESTGG/Welcome1


SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 28 10:46:14 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 28 2021 10:45:10 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "TESTGG"
SQL> select count(*) from TBL_DEVICE;

  COUNT(*)
----------
         0

SQL> select count(*) from TESTEXT;

  COUNT(*)
----------
         0

Kaynak Tarafında eini spacial extractını çalıştıralım

GSCI (sourcegg.ttnet.com) 9> start extract eini

Sending START request to MANAGER ...
EXTRACT EINI starting


GGSCI (sourcegg.ttnet.com) 10> info eini

EXTRACT    EINI      Last Started 2021-01-28 11:44   Status RUNNING
Checkpoint Lag       Not Available
Process ID           13977
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

GGSCI (sourcegg.ttnet.com) 14> info eini

EXTRACT    EINI      Last Started 2021-01-28 11:44   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table TESTGG.TESTEXT
                     2021-01-28 11:44:56  Record 2
Task                 SOURCEISTABLE


GGSCI (sourcegg.ttnet.com) 15> view report eini

2021-01-28 11:44:48  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************

***********************************************************************

2021-01-28 11:44:48  INFO    OGG-03059  Operating system character set identified as UTF-8.

2021-01-28 11:44:48  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.

2021-01-28 11:44:48  INFO    OGG-01360  EXTRACT is running in Initial Load mode.

2021-01-28 11:44:48  INFO    OGG-01889  Flush size (max message size) is set to 27,985.
Extract eini
UserIdAlias oggadmin_sdb
rmthost targetgg, mgrport 7809
RMTTASK replicat, GROUP rini
TABLE TESTGG.*;


Report at 2021-01-28 11:44:57 (activity since 2021-01-28 11:44:48)

Output to rini:

From Table TESTGG.TBL_DEVICE:
       #                   inserts:       169
       #                   updates:         0
       #                   deletes:         0
       #                   upserts:         0
       #                  discards:         0
From Table TESTGG.TESTEXT:
       #                   inserts:         2
       #                   updates:         0
       #                   deletes:         0
       #                   upserts:         0
       #                  discards:         0


REDO Log Statistics
  Bytes parsed                    0
  Bytes output                44673

Taget Tarafını kontrol edelim

GGSCI (targetgg.ttnet.com) 28> info rini

REPLICAT   RINI      Initialized   2021-01-28 11:43   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:02:26 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN


GGSCI (targetgg.ttnet.com) 29> view report rini


***********************************************************************
                 Oracle GoldenGate Delivery for Oracle
    Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
   Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:17:25

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2021-01-28 11:44:48
***********************************************************************

Operating System Version:
Linux
Version #2 SMP Wed Jan 6 13:08:02 PST 2021, Release 4.1.12-124.46.4.1.el7uek.x86_64
Node: targetgg.ttnet.com
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 17127

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************


Report at 2021-01-28 11:45:02 (activity since 2021-01-28 11:44:55)

From Table TESTGG.TBL_DEVICE to TESTGG.TBL_DEVICE:
       #                   inserts:       169
       #                   updates:         0
       #                   deletes:         0
       #                   upserts:         0
       #                  discards:         0
From Table TESTGG.TESTEXT to TESTGG.TESTEXT:
       #                   inserts:         2
       #                   updates:         0
       #                   deletes:         0
       #                   upserts:         0
       #                  discards:         0

Target Tarafında REPLICATE Oluşturalım

GGSCI (targetgg.ttnet.com) 32> edit param reast

Replicat reast
UserIdAlias oggadmin_tdb
DBOPTIONS Integratedparams(parallelism 6)
ASSUMETARGETDEFS
HANDLECOLLISIONS
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP TESTGG.*, TARGET TESTGG.*;

#DiscadFile ./dirrpt/rpdw.dsc, Purge satırı hataya neden oldu

GGSCI (targetgg.ttnet.com) 34> add checkpointtable ggadmin.checkpoint
Successfully created checkpoint table ggadmin.checkpoint.

GGSCI (targetgg.ttnet.com) 34> add Replicat reast Integrated exttrail ./dirdat/pe checkpointtable                                                                                                                          ggadmin.checkpoint

GGSCI (targetgg.ttnet.com) 36> start replicat reast

Sending START request to MANAGER ...
REPLICAT REAST starting

GGSCI (targetgg.ttnet.com) 51> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REAST       00:00:00      00:06:41

GoldenGAte başarı ile çalıştı ve veri senkronizasyonu başarılı. Aşağıdaki sorgu ile işlemleri görebiliriz

GGSCI (sourcegg.ttnet.com) 3> stats *

Sending STATS request to EXTRACT EXTWEST ...

No active extraction maps.
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                         0.00
        Mapped operations                                  0.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00


Sending STATS request to EXTRACT PWEST ...

Start of Statistics at 2021-01-28 15:40:34.

Output to ./dirdat/pe:

Extracting from TESTGG.TBL_DEVICE to TESTGG.TBL_DEVICE:

*** Total statistics since 2021-01-28 10:42:56 ***
        Total inserts                                    169.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                 169.00

*** Daily statistics since 2021-01-28 10:42:56 ***
        Total inserts                                    169.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                 169.00

*** Hourly statistics since 2021-01-28 14:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2021-01-28 10:42:56 ***
        Total inserts                                    169.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                 169.00

Extracting from TESTGG.CREATETABLE_TEST to TESTGG.CREATETABLE_TEST:

*** Total statistics since 2021-01-28 10:42:56 ***
        Total inserts                                   3666.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                3666.00

*** Daily statistics since 2021-01-28 10:42:56 ***
        Total inserts                                   3666.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                3666.00

*** Hourly statistics since 2021-01-28 14:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2021-01-28 10:42:56 ***
        Total inserts                                   3666.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                3666.00

Extracting from TESTGG.TESTEXT to TESTGG.TESTEXT:

*** Total statistics since 2021-01-28 10:42:56 ***
        Total inserts                                      2.00
        Total updates                                      1.00
        Total deletes                                      1.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00

*** Daily statistics since 2021-01-28 10:42:56 ***
        Total inserts                                      2.00
        Total updates                                      1.00
        Total deletes                                      1.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00

şeklinde uzuyor...

Eğer replicat servisimiz ABONDING olur ise ; durudurup tekrar derleyerek çalıştırmamız gerekir. Bu durumda yukarıdaki statistiks bilgileri ve tüm servis sıfırlanacaktır.

GGSCI (targetgg.ttnet.com) 10> stop REAST
GGSCI (targetgg.ttnet.com) 10> alter REAST begin now 
GGSCI (targetgg.ttnet.com) 10> start REAST

Ne kadar kısa ve öz olmasını istesem de uzun bir yazı oldu, umarım faydalı olur.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

logo

Database Engineer | Oracle & PostgreSQL & ClickHouse
Hello, I am Faruk Çevik. I have professional skills Oracle and other databases. I share my experiences with System, Database Bigdata, DWH Databases on this my site. .

Gizlilik Politikası