{"id":1225,"date":"2023-10-02T17:14:31","date_gmt":"2023-10-02T17:14:31","guid":{"rendered":"https:\/\/adrianotanaka.com.br\/?p=1225"},"modified":"2023-10-02T17:14:33","modified_gmt":"2023-10-02T17:14:33","slug":"goldengate-downstream-architecture","status":"publish","type":"post","link":"https:\/\/adrianotanaka.com.br\/index.php\/2023\/10\/02\/goldengate-downstream-architecture\/","title":{"rendered":"GoldenGate – Downstream architecture"},"content":{"rendered":"\n

How to extract data using GoldenGate without overloading your primary database or connecting to it directly<\/strong><\/p>\n\n\n\n

You have received a request to extract data using GoldenGate, but your primary database is overloaded or you have a strong security posture that prohibits other applications besides your main application to connect to your database. How do you accomplish this?<\/p>\n\n\n\n

GoldenGate has an architecture called Downstream that lets you extract data from a shell database (this database only receives the data changed). In this article, I will show you how to deploy this.<\/p>\n\n\n\n

Topology<\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n

The topology here is very simple:<\/p>\n\n\n\n

\n
    \n
  • Source DB: \n
      \n
    • SID: DB11G <\/li>\n\n\n\n
    • VERSION: 11.2.0.4.0 <\/li>\n\n\n\n
    • HOSTNAME: ol79-db11g<\/li>\n<\/ul>\n<\/li>\n\n\n\n
    • Mining DB: \n
        \n
      • SID: cdb1 <\/li>\n\n\n\n
      • VERSION: 19.3.0.0.0 <\/li>\n\n\n\n
      • HOSTNAME: ol79-db-ogg<\/li>\n<\/ul>\n<\/li>\n\n\n\n
      • GoldenGate for Oracle 21.3.0.0.0<\/li>\n<\/ul>\n\n\n\n

        The GoldenGate deployment connects to Source DB to get Metadata info and to Mining(or Downstream) to do the mining process, this architecture is based on DataGuard config.<\/p>\n\n\n\n

        <\/p>\n\n\n\n

        Configuring the Source<\/h2>\n<\/div>\n\n\n\n

        For source, we need to setup a archive dest like we do when setting up a Data Guard config.<\/p>\n\n\n\n

        Copy the password file to Downstream host:<\/p>\n\n\n\n

        scp orapwdb* oracle@dbprd:\/u01\/app\/oracle\/product\/19.0.0\/dbhome_1\/dbs<\/p>\n\n\n\n

        Add a tnsnames entry pointing to the downstream database:<\/p>\n\n\n\n

        DB19C =
        (DESCRIPTION =
        (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ol79-db-ogg.myguest.virtualbox.org)(PORT = 1521))
        )
        (CONNECT_DATA =
        (SERVICE_NAME = cdb1)
        )
        )<\/code><\/pre>\n\n\n\n
        To configure log shipping, we need to change this 3 parameters, in my case I’m using the _2 but choose one that you are not using.<\/p>\n\n\n\n
        LOG_ARCHIVE_DEST_2\nLOG_ARCHIVE_DEST_STATE_2 -> Configure this after configuring both source and target.\nLOG_ARCHIVE_CONFIG\n\nALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DB19C ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=10 DB_UNIQUE_NAME=cdb1' scope=both sid='*';\n\nALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(db11g,cdb1)' scope=both sid='*';<\/code><\/pre>\n\n\n\n
        <\/p>\n\n\n\n
        Configuring the Mining DB<\/h2>\n\n\n\n
        Add Standby Log files following the rule: number of redo groups + 1:<\/p>\n\n\n\n
        ALTER DATABASE ADD STANDBY LOGFILE size 50m;<\/code><\/pre>\n\n\n\n
        Configure the location for redo log generated by the mining db, its important to not mix the mining db and received logs:<\/p>\n\n\n\n
        ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=\/u01\/oradata\/CDB1\/local VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';<\/code><\/pre>\n\n\n\n
        <\/p>\n\n\n\n
        Configure the location for the received redo logs:<\/p>\n\n\n\n
        ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=\/u01\/oradata\/CDB1\/remote VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)';<\/code><\/pre>\n\n\n\n
        ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(db11g,cdb1)' scope=both sid='*';<\/code><\/pre>\n\n\n\n
        <\/p>\n\n\n\n
        After everything is configured, enable the dest in source and in mining db:<\/p>\n\n\n\n
        ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;<\/code><\/pre>\n\n\n\n
        <\/p>\n\n\n\n
        Run a switch logfile in source db and see if the log is shipped to mining db:<\/p>\n\n\n\n
        <\/p>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        <\/p>\n\n\n\n
        GoldenGate<\/h2>\n\n\n\n
        Once the log shipping is in place, we need to set up GoldenGate and create two connections, one to the source database and one to the mining database.<\/p>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        Add trandata to source db<\/p>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        <\/p>\n\n\n\n
        And create an extract:<\/p>\n\n\n\n
        If you are using the Micros-services architecture its easy, just check the Downstream capture option and select both databases:<\/p>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        <\/p>\n\n\n\n
        If you are using the command line, you will need these parameters in your parameter file:<\/p>\n\n\n\n
        USERIDALIAS db11g DOMAIN OracleGoldenGate ->  This points to source DB
        TRANLOGOPTIONS MININGUSERALIAS cdb DOMAIN OracleGoldenGate -> This points to mining DB<\/code><\/pre>\n\n\n\n
        <\/p>\n\n\n\n
        Bonus – Real Time Capture<\/h2>\n\n\n\n
        GoldenGate Downstream enables you to use a parameter called downstream_real_time_mine<\/strong>. With this parameter enabled, GoldenGate does not wait for the switch (and shipping) to occur to mine the needed data. Instead, it receives data into the standby redo log files and mines it immediately.<\/p>\n\n\n\n
        Your parameter file will look like this:<\/p>\n\n\n\n
        USERIDALIAS db11g DOMAIN OracleGoldenGate
        TRANLOGOPTIONS MININGUSERALIAS cdb DOMAIN OracleGoldenGate
        TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)<\/code><\/pre>\n\n\n\n
        <\/p>\n\n\n\n
        And you can validate if the Extract is using Real Time mode with this query, you need to see at least one ACTIVE group:<\/p>\n\n\n\n
        select GROUP#,THREAD#,STATUS from V$STANDBY_LOG;<\/code><\/pre>\n\n\n\n
        \"\"
        downstream_real_time_mine = Y<\/figcaption><\/figure>\n\n\n\n
        <\/p>\n\n\n\n
        \"\"
        downstream_real_time_mine = N<\/figcaption><\/figure>\n\n\n\n
        If Real Time capture is disabled, the lag will increase until the redo shipping:<\/p>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        Hope this article has been helpful in configuring the downstream architecture. If you have any questions, please feel free to ask.<\/p>\n","protected":false},"excerpt":{"rendered":"
        How to extract data using GoldenGate without overloading your primary database or connecting to it directly You have received a request to extract data using GoldenGate, but your primary database is overloaded or you have a strong security posture that prohibits other applications besides your main application to connect to your database. How do you […]<\/p>\n","protected":false},"author":1,"featured_media":1228,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"material-hide-sections":[],"footnotes":""},"categories":[48,6],"tags":[],"class_list":["post-1225","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-goldengate","category-oracle"],"_links":{"self":[{"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1225","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=1225"}],"version-history":[{"count":18,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1225\/revisions"}],"predecessor-version":[{"id":1251,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1225\/revisions\/1251"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/media\/1228"}],"wp:attachment":[{"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=1225"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=1225"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=1225"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}