Netsoftmate

Tag: replicat

  • Setup Oracle Golden Gate one way replication on windows server

    Overview:
    Oracle Golden Gate provides very fast replication of data by reading transaction logs and writing the changes to one or more target databases in a homogeneous and heterogeneous environment. It is useful for High Availability Architectures and especially for Data Warehouse and Decision Support Systems. Thus, the variety of techniques and methods spreads from unidirectional environments for query offloading/reporting to bidirectional or Peer-to-Peer architectures in an active-active fashion.

    Prerequisites:
    Oracle Golden Gate Software Installed
    Database should be in archive log mode
    Supplemental logging should be enable.
    Force logging should be set to yes. 


    Environment Details:

    Hostname
    PROD-DB1
    Ip Address
    10.0.0.0
    Operating
    system
    Windows server
    2012 R2
    Environment
    production
    Oracle Home
    D:oracle11204product11.2.0dbhome_1
    Golden Gate
    stage
    D:oraclegg_stage
    Golden Gate
    Home
    D:oraclegg_homeproduct12.1.2.1ggfor11
    OGG Processes
    m_shl01s,m_shl01t,e_shl01s,p_shl01s,r_shl01t

     
    Steps to setup Oracle Golden Gate One way Replication:
     1. Install Oracle Golden Gate Software, refer below link.

    Oracle Golden Gate Installation on windows

    2. Upgrade Oracle Golden gate version to latest release, Refer following link.

    Oracle Golden Gate upgrade on Windows Server


    3. Verify archive log mode, then enable the archive log mode.

    4. Verify force logging and supplemental logging are set to ‘YES’ and Enable goldengate replication.

    5. Create a file ” GOLBALS”

    GGSCI  5> edit param ./GLOBALS

    6. Set db_recovery_file_dest_size parameter.

    7. Create golden gate database tablespace, user account and grant permissions.


    8. Enable DDL replication, as sysdba run the following in sequence providing ggadmin as schema.


    9. Login as sysdba and execute role_setup then grant GGS_GGSUSER_ROLE then ddl_enable and ddl_pin.



    10. Enable sequence replication, as sysdba run the following script from gg home.

    11. Enable trandata for the tables to be replicated, login to database from ggsci.
    GGSCI (  as ggadmin@****) 4> add trandata <Schema_name>.*,

    GGSCI (  as ggadmin@****) 5> add trandata <Schema_name>.*,

    Prepare Source environment
     
    12. Create parameter file for manager

    PORT 7809
    purgeoldextracts ./dirdat/shelldb/sh*, usecheckpoints, minkeepdays 2
    lagreportminutes 1
    laginfominutes 10
    lagcriticalminutes 90

    — delay starting other process after rebooting servers by 30min
    –bootdelayminutes 20
    –autostart ER *

    — auto start pump processes to startup if network failed
    autorestart EXTRACT p*, retries 4, waitminutes 10
    autorestart EXTRACT e*, retries 4, waitminutes 10

    13. Verify manager parameter file.


    14. Create PARAM file for primary extract (e_shl01s).
    EXTRACT e_shl01s
    — add extract e_shl01s, tranlog, begin 2015-04-28, threads 2
    — add exttrail ./dirdat/****/sh, extract e_shl01s, megabytes 50

    — add extract e_shl01s, tranlog, begin now, threads 2
    — add exttrail ./dirdat/****/sh, extract e_shl01s, megabytes 50

    discardfile ./dirrpt/discard/e_shl01s.dsc, append megabytes 50

    SETENV (ORACLE_SID=’****’)
    SETENV (NLS_LANG = “AMERICAN_AMERICA.AR8MSWIN1256”)
    –SETENV (ORACLE_HOME=D:oracle11204product11.2.0dbhome_1)

    userid ggadmin, password ggadmin123

    — to read from asm
    — tranlogoptions dblogreader
    — tranlogoptions ASMUSER SYS@ASM1, ASMPASSWORD GCSasmadmin2015

    — to prevent looping in bidirectional replication or you can user excludetag
    tranlogoptions excludeuser ggadmin

    exttrail ./dirdat/shelldb/sh
    cachemgr cachesize 1GB
    fetchoptions usesnapshot, uselatestversion
    FETCHOPTIONS FETCHPKUPDATECOLS
    dboptions allowunusedcolumn

    include ./dirprm/include_reporting.inc
    –warnlongtrans 3H, chekinterval 1H

    logallsupcols

    ddl include all
    ddloptions addtrandata, report

    –include ./dirprm/HB_Extract.inc

    table ggadmin.ggsync;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table test_user.*;

    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence test_user.*;




    15. Create PARAM file for pump extract (p_shl01s).

    extract p_shl01s
    — add extrat p_shl01s, exttrailsource ./dirdat/****/sh
    — add rmttrail ./dirdat/****/th, extract p_shl01s, megabytes 50
    discardfile ./dirrpt/discard/p_shl01s.dsc, append megabytes 50
    passthru
    rmthost 10.10.10.10  mgrport 7809
    rmttrail ./dirdat/****/th
    include ./dirprm/include_reporting.inc

    –include ./dirprm/HB_pmp.inc

    table ggadmin.ggsync;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table test_user.*;

    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence test_user.*;


    16. View param file of pump extract.

    17. Add primary extract

    18. Add pump extract.

    19. Add checkpoint table.


    Prepare Target Environment
     
    19. Repeat the steps from 1 to 11 on Target site.

    20. Create parameter file for manager on target site.

    21. Create parameter for replicat process.

    replicat r_shl01t
    — add replicat r_shl01t, exttrail ./dirdat/****/th
    discardfile ./dirrpt/discard/r_shl01t.dsc, append megabytes 50

    SETENV (ORACLE_SID=’****’)
    SETENV (NLS_LANG = “AMERICAN_AMERICA.AR8MSWIN1256”)
    –SETENV (ORACLE_HOME=C:oraclerdbms11gproduct11.2.0dbhome_1)

    userid ggadmin, password ggadmin123
    AssumeTargetDefs
    — for triggers starting from 11.2.0.2 oracle automatically disable trigger fire
    dboptions suppresstriggers
    — for cascading const
    dboptions deferrefconst

    DDL include all
    –DDLSUBST ‘SYS_C0040303’ WITH ‘SYS_C0040298’
    –ddlerror 1031 ignore
    grouptransops 1000

    include ./dirprm/include_reporting.inc

    –include ./dirprm/HB_Rep.inc
    APPLYNOOPUPDATES

    map ggadmin.ggsync, target ggadmin.ggsync;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map test_user.*, Target test_user.*;

    22. View replicate parameter and start the process.



    Conclusion
    In above article we have learned that, how to setup Oracle Golden Gate One way Replication on windows Server, where we have prepared Source and Target sides with respective extract process.

    BY
    Name: Mirza Hidayathullah Baig
    Designation: Senior Database Engineer
    Organization: Netsoftmate IT Solutions.
    Email: info@netsoftmate.com