Automating Oracle GoldenGate 11g

 

Overview

    Purpose

    This tutorial covers samples of tools and scripts used to automate tasks in Oracle GoldenGate. There are basically two ways that accomplish the same thing:

    • Control everything from inside of GGSCI using Obey files
    • Control everything from outside of GGSCI using shell scripts such as bash.

    Time to Complete

    Approximately 1 hour (though there is nothing to actually do, this is just a collection of samples).

    Introduction

    The scripts themselves are not usable as-is, rather they can be used to give you an idea of the kinds of things that can be done, and the syntax used to do them. Expect to modify the scripts before they are usable, but the modifications required are simple. The Obey scripts are platform-independent, though the database login (DBLogin) parameters may vary based on platform (Oracle versus DB2 versus MySQL, and so on). The shell scripts are based on bash, but are simple enough that they would work in other OS shells as well using basic redirection. None of the scripts do any testing nor branching (GGSCI commands do not make return codes).

    Scenario

    Any time you see an italic word prefaced with "my" it should be replaced with your information. For example, if you were using the Oracle DB sample schemas, myuserid might be replaced with SCOTT, and mypassword might be replaced with TIGER, and so on.

    Assume that there are two Oracle 11g databases, both running on Linux hosts. The source host is named mysrchost, the target host is mytrghost, the source database schema is mysrcdb, and the target database schema is mytrgdb. Assume the source SID is sorcl and the target SID is torcl.

    Hardware and Software Requirements

    The following is a list of hardware and software requirements:

    • Oracle GoldenGate 11g
    • Sufficient hardware to run two OGG instances: source and target. That could be two hosts or two directories on one host.
    • A database such as Oracle Database 11g
    • Sufficient hardware to run two DB instances: source and target. That could be two hosts or two instances/schemas on one host.

    Prerequisites

    Before starting this tutorial, you should:

    • Have access to or have installed Oracle GoldenGate 11g (henceforth abbreviated "OGG")
    • Have access to or have installed the OGG documentation, available online generically at docs and specific versions such as 11.2.1.

 

Topic 1: User (Keyboard) Automation

    Elaborate scripts are useful for doing complicated and repetitive tasks, but the day-to-day maintenance of a system still requires keying in one-off commands by hand. To make the keyboard entry of commands, consider the following tools.

     

    1.1: Command Line Automation: rlwrap

      While most of the operating system prompts have a built-in history mechanism for displaying and editing the last few commands, a few of the Oracle utilities do not have such a feature. It would be handy to be able to press (for example) up-arrow and down-arrow to look through and repeat and edit the last few commands.

      Standard disclaimers: Oracle makes no claims, express nor implied, about the suitability or availability of external utilities such as rlwrap. Oracle receives no remuneration for mentioning any other non-Oracle utilities.

      Install rlwrap package from http://utopia.knoware.nl/~hlub/uck/rlwrap/

      README.txt
      rlwrap v 0.35  January 8 2010
      
      * WHAT IT IS: 
      
      rlwrap is a 'readline wrapper', a small utility that uses the GNU
      readline library to allow the editing of keyboard input for any
      command. I couldn't find anything like it when I needed it, so I wrote
      this one back in 1999.  By now, there are (and, in hindsight, even
      then there were) a number of good readline wrappers around, like rlfe,
      distributed as part of the GNU readline library, and cle
      (http://kaolin.unice.fr/Cle/).  rlwrap should be especially useful
      when you need user-defined completion (by way of completion word
      lists) and persistent history, or if you want to program 'special
      effects' using the filter mechanism. rlwrap compiles and runs on a 
      fairly wide range of Unix-like systems.
                                                                                      
      

      This will work equally well for SQL*Plus sqlplus as well as GGSCI.

      Define aliases to wrap the utilities before invocation.

      ~/.bash_profile
      ...(other lines not shown)...
      alias sqlplus='rlwrap sqlplus'
      alias ggsci='rlwrap ./ggsci'
                                                                                      
      

      Note that sqlplus requires a correct $PATH variable and therefore can be invoked from anywhere, whereas ggsci requires ./ as part of the alias, and therefore must be invoked from the proper OGG home directory.

      While not a requirement, rlwrap will quickly become an indispensible addition to the "normal" use of ggsci and sqlplus commands.

     

    1.2: Command Line Confirmation: (bang)

      Some of the GGSCI commands require a confirmation before proceeding: "Are you sure? (y/n)" In the case of a keyboard entry, it might be nice to suppress asking the question. In the case of a background script, it is absolutely necessary. Generally, appending an exclamation point to the end of a command means, "Yes, I really want to do this, don't ask me."

      An example of stopping and starting the Manager:

      GGSCI Session
      GGSCI (myhost) >  Stop mgr 
      Manager process is required by other GGS processes.
      Are you sure you want to stop it (y/n)? y
      
      Sending STOP request to MANAGER ...
      Request processed.
      Manager stopped.
      
      GGSCI (myhost) >  Start mgr 
      Manager started.
      
      GGSCI (myhost) >  Stop mgr !
      Sending STOP request to MANAGER ...
      Request processed.
      Manager stopped.
      
      GGSCI (myhost) > 
                                                                                      
      

      An example of deleting several processes at once:

      GGSCI Session
      GGSCI (myhost) >  Delete ER west* 
      Are you sure you want to delete all groups? y
      Deleted EXTRACT WEST_EAB.
      Deleted EXTRACT WEST_PAB.
      Deleted REPLICAT WEST_RBA.
      
      GGSCI (myhost) >  Delete ER east* ! 
      Deleted EXTRACT EAST_EBA.
      Deleted EXTRACT EAST_PBA.
      Deleted REPLICAT EAST_RAB.
      
      GGSCI (myhost) > 
                                                                                      
      
     

    1.3: Environment Variables

      GGSCI can make use of environment variables, such as $ORACLE_SID, but it does not require any to run. It may be helpful though to define a few variables just to make the scripts self-documenting, and to help reduce keystrokes (and therefore reduce typos). Any kind of a keyboard shortcut is good.

      Define the OGG installation directories. This can be exported from .bash_profile, or in a separate setenv.sh, whichever is your preference. Of particular interest are the two OGG variables.

      setenv.sh
      ORACLE_HOME=/u01/app/oracle/product
      JAVA_HOME=/usr/java/jdk1.7.0_09
      export ORACLE_HOME JAVA_HOME 
      OGG_SRC=$ORACLE_HOME/ogg/source
      OGG_TRG=$ORACLE_HOME/ogg/target
      export OGG_SRC OGG_TRG 
      PATH=$JAVA_HOME/bin:$PATH
      LD_LIBRARY_PATH=$ORACLE_HOME/11.2.0/dbhome_1/lib:$JAVA_HOME/jre/lib/amd
         64:$OGG_TRG:$JAVA_HOME/jre/lib/amd64/server
      export PATH LD_LIBRARY_PATH
                                                                                      
      

      The indented line starting with "64:" is a continuation of the previous line, so LD_LIBRARY_PATH is all on one line through the end of "amd64/server."

      An example of using an environment variable to save typing:

      OS Session
      [OS prompt]$  ls $OGG_SRC/dirprm 
      west_rba.prm   west_eab.prm   west_pab.prm
      
      [OS prompt]$
                                                                                      
      
     

    1.4: Obey Scripts Everyone Ought to Have: startup, shutdown

      There are a few commands that ought to be entered every time you restart GGSCI. It is fairly easy to accidentally blow out of GGSCI by mistaking Ctrl + C for Copy in the wrong context: it kills GGSCI. The following commands are very useful if they have never been set, and harmless to re-enter if they have already been set.

      startup.oby
      --------------------------------------------------------------------------
      -- DISCLAIMER:                                                          --
      --    This script is provided for educational purposes only. It is NOT  --
      --    supported by Oracle World Wide Technical Support.                 --
      --    The script has been tested and appears to work as intended.       --
      --    You should always run new scripts on a test instance initially.   --
      --    Copyright (c) Oracle Corp. 2012. All Rights Reserved.             --
      --------------------------------------------------------------------------
      
      -- Useful as first thing when (re)starting ./ggsci, esp after shutdown_ogg.sh
      
      DBLogin UserID myogguser, Password mypassword
      Start mgr
      Set Editor gedit
      Add  CheckpointTable
      Info CheckpointTable
      Info All
                                                                                      
      

      Invoke at the GGSCI prompt as follows:

      GGSCI > Obey startup.oby

      When you are done with a test and need to restore OGG to a clean environment, it is often useful to have a shutdown script (the opposite of startup), to leave the processes and directories clean for the next experiment.

      shutdown_ogg.sh
      #--------------------------------------------------------------------------#
      #-- DISCLAIMER:                                                          --#
      #--    This script is provided for educational purposes only. It is NOT  --#
      #--    supported by Oracle World Wide Technical Support.                 --#
      #--    The script has been tested and appears to work as intended.       --#
      #--    You should always run new scripts on a test instance initially.   --#
      #--    Copyright (c) Oracle Corp. 2012. All Rights Reserved.             --#
      #--------------------------------------------------------------------------#
      
      # Use to reset OGG environment to a stopped state. Run as OS user=oracle.
      
      #kill west (source) OGG
      export ORACLE_SID=sorcl
      cd $OGG_SRC
      ./ggsci <<EOF
      Start Manager
      DBLogin UserID myogguser, Password mypassword
      Delete Extract eini
      Stop ER *
      Delete ER * !
      Delete CheckpointTable !
      Stop Manager !
      Exit
      EOF
      
      rm ./dirdat/*
      rm ./dirrpt/*
      
      #kill east (target) OGG
      export ORACLE_SID=torcl
      cd $OGG_TRG
      ./ggsci <<EOF
      Start Manager
      DBLogin UserID myogguser, Password mypassword
      Delete Replicat rini
      Stop ER *
      Delete ER * !
      Delete CheckpointTable !
      Stop Manager !
      Exit
      EOF
      
      rm ./dirdat/*
      rm ./dirrpt/*
                                                                                      
      

      Invoke at the OS prompt as follows:

      [username path]$ ./shutdown_ogg.sh

      Note that eini and rini are deleted explicitly because initial load tasks are not included in the Delete ER * wildcard.

 

Topic 2: GGSCI (Obey) Automation

    Assuming GGSCI is running, ongoing tasks require you to do a mix of GGSCI commands and moving of *.prm files in and out of source and target directories. The Obey files can have any extension, by convention it is .oby or equivalent. To control tasks from within GGSCI, perform the following steps:

     

    2.1: Calling Shell Commands from Within Obey

      You should probably use absolute paths.

      mysample211.oby
      -- Always have a comment at the top.
      -- Created by Joe Admin on 12/12/12
      sh rm /u01/ogg_src/dirrpt/*
      sh rm /u01/ogg_trg/dirrpt/*
      sh cp /home/myuserid/targets/*.prm  /u01/ogg_src/dirprm
      sh cp /home/myuserid/targets/*.def  /u01/ogg_trg/dirdef
      DBLogin UserID myuserid, Password mypassword
      List Tables *
                                                                                      
      

      You can use some environment variables such as $ORACLE_HOME, and can use relative paths such as ../.., but you cannot use tilde ~ shortcuts for $HOME inside of GGSCI.

      Unfortunately you may have to sprinkle sh sleep 5 statements throughout the scripts as there are asynchronous issues with completing commands. In other words, suppose there were no sleeps in the following example. The DBLogin might take 3 seconds to complete, but the script will encounter that line, start the login, and then immediately try to start the next statement (List Tables) which will fail if the login has not finished.

      mysample212.oby
      -- Created by Joe Admin on 12/12/12
      -- Connect to DB and list schema
      DBLogin UserID myuserid, Password mypassword
      sh sleep 5
      List Tables *
      -- Bounce the Manager
      Stop mgr !
      sh sleep 3
      Start mgr
      sh sleep 3
      Info mgr
                                                                                      
      

      If you attempt to remove files from an occupied directory, it works and you get no message back. If you attempt to remove files from an already-empty directory, you get a somewhat misleading message:
      rm: cannot remove `dirrpt/*': No such file or directory
      This is the same behavior when running from a regular OS prompt.

     

    2.2: Calling SQL*Plus Commands from Within Obey

      Invoke sqlplus with the credentials and SID inline, and the script appended with an at sign. The extension of .sql is optional and assumed, but is useful for self-documentation.

      mysample221.oby
      -- Created by Joe Admin and Dee Beeay on 12/12/12
      sh sqlplus myuserid@sorcl/mypassword @mysourcescript.sql
      sh sqlplus myuserid@sorcl/mypassword @./dirsql/mysourcescript.sql
      sh sqlplus myuserid/mypassword @mytargetscript.sql 
      sh sqlplus myuserid@torcl/mypassword @/mypath1/mypath2/mytargetscript.sql 
                                                                                      
      

      Make sure the SQL script ends with "exit" otherwise the SQL script will complete and remain inside of sqlplus and the GGSCI Obey script will hang at that point.

      The first line assumes the SQL script is located in the OGG home directory. A few, such as demo_ora_*.sql, are located there by default.

      The second line assumes the SQL scripts have been moved into the dirsql directory of the OGG home. That is not a bad place to keep OGG-related SQL scripts.

      The third line leaves off the explicit SID, so it assumes whatever is @ORACLE_SID's current definition.

      The fourth and last line shows that the SQL script can be located anywhere.

      SQL*Plus can echo commands or not. This is useful to have set echo on for debugging, and then later set echo off for production.

      SQL*Plus
      SQL>  set echo on  
      SQL>  set echo off  
                                                                                      
      
     

    2.3: Calling GGSCI Commands from Within Obey

      Use the commands just as you would from the GGSCI prompt.

      mysample231.oby
      -- Created by Joe Admin on 12/12/12
      Add Extract extwest, TranLog, Begin Now
      Add ExtTrail ./dirdat/ew, Extract extwest, Megabytes 50
      Add Extract pwest, ExtTrailSource ./dirdat/ew
      Add RmtTrail ./dirdat/pe, Extract pwest, Megabytes 50
      Shell sleep 3
      Info ER *
      Start ER *
      Shell sleep 3
      Info ER *
                                                                                      
      

      It is a good habit to do the Info ER * before and after starting processes just so you can see if it fails while debugging. Unfortunaley Start ER * does not return anything you can test, you just have to eyeball the messages as they go by.

      The only thing to be careful about is lines that are long and wrap. Use the text editor to make the long commands all one line without using continuation characters.

      A new AllowNested command enables the use of nested Obey files. A nested Obey file is one that contains another Obey file. The maximum number of nested levels is 16.

      mysample232.oby
      -- Created by Joe Admin on 12/12/12
      Obey startup.oby
      Add Extract morning, TranLog, Begin Now
      Add ExtTrail ./dirdat/xy, Extract morning
      Start Extract  morning
      Start Replicat morning
      sh sleep 300
      Obey shutdown.oby
                                                                                      
      

      This is particularly useful when you have a common startup or shutdown routine that you use often.

     

    2.4: Unsupported: The test! Command

      The GGSCI test command is unsupported and undocumented, and may disappear at any version. It is not recommended for production use, but you may encounter it in a test environment.

      GGSCI Session
      GGSCI (myhost) 1> test 
      
      Usage: TEST genericTestFileName
      
      Note: TEST is undocumented GGSCI command and is not supported.
      
      Purpose of TEST is to provide generic cross-platform utility of testing with Go
      ldenGate software.
      
      What does TEST do for you? 
      1. lets you write many files in one and then unpack them. Each file can reside 
         in different directory.
      2. provides string substitution across many files in consistent way.
      3. gives you centralized repository of substitution values in file 'ggtest'.
      
      TEST may make many tests situations (not all) easier to replicate on different 
      systems.
                                                                                      
      

      The idea is that it is a combination of Obey, SQL*Plus, and file creation tools all in one big text bundle.

      An example of a generic test text file. Note the substitution variables:

      gtest242.txt
      ~~readme
        This is readme file
      ~~dirsql/mySQLFile.sql
        INSERT INTO myTable VALUES (3);
      ~~dirprm/ext99.prm
        EXTRACT ext99
        USERID ~suser~~db~, PASSWORD ~spass~
        RMTHOST ~host~, mgrport ~mgr~
        RMTTRAIL ./dirdat/b1
        TABLE ~suser~.DDL_COL;
      ~~dirprm/rep99.prm
        REPLICAT rep99
        USERID ~tuser~ PASSWORD ~tpass~
        MAP ~suser~.TAB* TARGET ~tuser~.*
      ~~test_obey.ob
        SQLPLUS ~suser~/~spass~ < ./dirsql/mySQLFile.sql
        DELFILE ./dirdat/k3
        DELETE REPLICAT rep99
        ADD REPLICAT rep99, EXTTRAIL ./dirdat/e2, NODBCHECKPOINT
                                                                                      
      

      No details will be given on how to implement this command.

 

Topic 3: Shell (Script) Automation

    There may be circumstances where the majority of the tasks you need to run happen outside of GGSCI but still include a few GGSCI commands. In those circumstances, you may find it easier to control the overall operation from an OS shell script which can be used to, among other things, start and stop multiple GGSCI instances.

     

    3.1: Calling Shell Commands from Within Shell Scripts

      Of course this can be as simple or as complicated as you desire to make it. Look at any ./profile for examples.

      /etc/profile
      # /etc/profile
      
      # System wide environment and startup programs, for login setup
      # Functions and aliases go in /etc/bashrc
      
      # It's NOT a good idea to change this file unless you know what you
      # are doing. It's much better to create a custom.sh shell script in
      # /etc/profile.d/ to make custom changes to your environment, as this
      # will prevent the need for merging in future updates.
      
      pathmunge () {
          case ":${PATH}:" in
              *:"$1":*)
                  ;;
              *)
                  if [ "$2" = "after" ] ; then
                      PATH=$PATH:$1
                  else
                      PATH=$1:$PATH
                  fi
          esac
      }
      
      
      if [ -x /usr/bin/id ]; then
          if [ -z "$EUID" ]; then
              # ksh workaround
              EUID=`id -u`
              UID=`id -ru`
          fi
          USER="`id -un`"
          LOGNAME=$USER
          MAIL="/var/spool/mail/$USER"
      fi
       (...more lines not shown for clarity...) 
                                                                                      
      

      There are whole week-long courses on writing shell scripts. 'Nuff said.

      The only thing to be aware of in general is that "double quotes" and 'single straight quotes' and `slanted quotes` and dollar $ign$ all mean something special to shell scripts, so you may need to escape them to use them within SQL*Plus or within GGSCI.

     

    3.2: Calling SQL*Plus Commands from Within Shell Scripts

      You can redirect multiple separate .sql text files as input into sqlplus by using the @ at sign, but for just a few commands, it is easier to have the input inline with the script as follows:

      mysample321.sh
      # Created by Joe Admin on 12/12/12
      
      cd ~/labs/Section14/Practice14-1/sqlscripts
      export ORACLE_SID=sorcl
      sqlplus myuserid/mypassword <<EOF
      CREATE TABLE mytable (mycol1 VARCHAR2(20), mycol2 NUMBER(8,2), mycol3 CHAR(5));
      SELECT COUNT(*) FROM mytable;
      INSERT INTO mytable VALUES ('ABC123', 100.75, 'WEST');
      COMMIT;
      EXIT;
      EOF
      echo '**Created, truncated, seeded WEST/Source tables.'
      sleep 3
      
      cd ~/labs/Section14/Practice14-2/sqlscripts
      export ORACLE_SID=torcl
      sqlplus myuserid/mypassword <<EOF
      @database.sql
      @seed_database.sql
      EOF
      echo '**Created, truncated, seeded EAST/Target tables.'
      sleep 3
      
      cd ~/labs/Section14/Practice14-3/sqlscripts
      export ORACLE_SID=torcl
      sqlplus myuserid/mypassword @database.sql
      echo '**Created, truncated, seeded NORTH/Target tables.'
      sleep 3
                                                                                      
      

      The first example has no external files, all the SQL commands are inline, including the exit.

      The second example has two or more external @ files, the first one @database.sql does not end with an exit command, but the second one @seed_database.sql must have an embedded exit.

      The last example has only one @ file to call, so there is no need for the <<EOF construct.

      Note that you need to "escape" the dollar sign from v$database.

      mysample322.sh
      #--------------------------------------------------------------------------#
      #-- DISCLAIMER:                                                          --#
      #--    This script is provided for educational purposes only. It is NOT  --#
      #--    supported by Oracle World Wide Technical Support.                 --#
      #--    The script has been tested and appears to work as intended.       --#
      #--    You should always run new scripts on a test instance initially.   --#
      #--    Copyright (c) Oracle Corp. 2012. All Rights Reserved.             --#
      #--------------------------------------------------------------------------#
      
      # Use this script to complete Lab 5. Run as OS user=oracle.
      # WARNING! This overlays whatever you may have had in your own config files!
      
      cd ~/labs/Section5/sqlscripts/
      export ORACLE_SID=amer
      sqlplus west/oracle_4U @source_database.sql
      sqlplus west/oracle_4U @seed_database.sql
      sqlplus / as sysdba <<EOF
      GRANT dba TO gguser IDENTIFIED BY oracle_4U;
      CONN gguser/oracle_4U
      SHOW USER
      SHOW PARAMETER NAME
      ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
      ALTER SYSTEM SWITCH LOGFILE;
      SELECT supplemental_log_data_min FROM v\$database;
      exit
      EOF
      
      export ORACLE_SID=euro
      sqlplus east/oracle_4U @target_database.sql
      sqlplus / as sysdba <<EOF
      GRANT dba TO gguser IDENTIFIED BY oracle_4U;
      CONN gguser/oracle_4U
      SHOW USER
      SHOW PARAMETER NAME
      ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
      ALTER SYSTEM SWITCH LOGFILE;
      SELECT supplemental_log_data_min FROM v\$database;
      exit
      EOF
                                                                                      
      
     

    3.3: Calling GGSCI Commands from Within Shell Scripts

      Notice the redirection from within the file until the EOF. Setting the ORACLE_SID variable means that you did not have to sign on with the @sid, for example myogguser@torcl.

      mysample331.sh
      # Always include a comment at the top
      # Created by Joe Admin on 12/12/12
      # kill west (amer) OGG
      export ORACLE_SID=torcl
      cd $OGG_TRG_HOME
      ./ggsci <<EOF
      Start Manager
      DBLogin UserID myogguser, Password mypswd
      Delete Extract myini
      Stop ER *
      Delete ER * !
      Delete CheckpointTable !
      Stop Manager !
      Exit
      EOF
      
      rm ./dirdat/*
      rm ./dirrpt/*
                                                                                      
      

      Similar to using SQL*Plus within a shell script, you need to be mindful of ending with an embedded Exit if you need to continue on to other commands within the shell script.

      Note that the two remove commands at the bottom could have been in the GGSCI part using sh shell prefixes. It would have done the same thing.

      Pulling it all together, here is a more complex example from one of Oracle's OGG courses. Note that it is controlling two GGSCI instances (that happen to be on the same host).

      ~\D66519GC20_labs\labs\Section13\catchup13.sh
      #--------------------------------------------------------------------------#
      #-- DISCLAIMER:                                                          --#
      #--    This script is provided for educational purposes only. It is NOT  --#
      #--    supported by Oracle World Wide Technical Support.                 --#
      #--    The script has been tested and appears to work as intended.       --#
      #--    You should always run new scripts on a test instance initially.   --#
      #--    Copyright (c) Oracle Corp. 2012. All Rights Reserved.             --#
      #--------------------------------------------------------------------------#
      
      # Use this script to complete Lab 13. Run as OS user=oracle.
      # WARNING! This overlays whatever you may have had in your own config files!
      
      ~/labs/setup/kill-all-ogg.sh
      echo '**Killed all old processes.'
      sleep 3
      
      cp ~/labs/Section13/solutions/def* $GG_AMER_HOME/dirprm
      cp ~/labs/Section13/solutions/def* $GG_EURO_HOME/dirprm
      cp ~/labs/Section13/solutions/efu* $GG_AMER_HOME/dirprm
      cp ~/labs/Section13/solutions/ENC* $GG_AMER_HOME
      cp ~/labs/Section13/solutions/ENC* $GG_EURO_HOME
      cp ~/labs/Section13/solutions/mac* $GG_AMER_HOME/dirmac
      cp ~/labs/Section13/solutions/mac* $GG_EURO_HOME/dirmac
      cp ~/labs/Section13/solutions/pfu* $GG_AMER_HOME/dirprm
      cp ~/labs/Section13/solutions/rfu* $GG_EURO_HOME/dirprm
      cp ~/labs/Section13/solutions/sec* $GG_AMER_HOME/dirdef
      cp ~/labs/Section13/solutions/sec* $GG_EURO_HOME/dirdef
      echo '**Copied solutions to running OGG folders.'
      sleep 3
      
      cd ~/labs/Section13/sqlscripts
      export ORACLE_SID=amer
      sqlplus west/oracle_4U  @source_database.sql
      echo '**Created, truncated, seeded WEST/amer tables.'
      sleep 3
      
      cd ~/labs/Section13/sqlscripts
      export ORACLE_SID=euro
      sqlplus east/oracle_4U  @target_database.sql
      echo '**Created, truncated, seeded EAST/euro tables.'
      sleep 3
      
      cd $GG_AMER_HOME
      export ORACLE_SID=amer
      ./ggsci <<EOF
      Obey /home/oracle/labs/setup/startup.oby
      Shell sleep 5
      Add TranData WEST.*
      EOF
      echo '**Created WEST checkpoint tables.'
      sleep 3
      
      cd $GG_EURO_HOME
      export ORACLE_SID=euro
      ./ggsci <<EOF
      Obey /home/oracle/labs/setup/startup.oby
      Shell sleep 5
      EOF
      echo '**Created EAST checkpoint tables.'
      sleep 3
      
      cd $GG_AMER_HOME
      export ORACLE_SID=amer
      ./ggsci <<EOF
      Obey /home/oracle/labs/setup/startup.oby
      Shell sleep 5
      Add Extract efuncs, TranLog, Begin Now
      Add ExtTrail ./dirdat/ex, Extract efuncs, Megabytes 1
      Add ExtTrail ./dirdat/ec, Extract efuncs, Megabytes 1
      Add Extract pfuncs, ExtTrailsource ./dirdat/ex
      Add RmtTrail ./dirdat/px, Extract pfuncs, Megabytes 1
      Add Extract pfunca, ExtTrailsource ./dirdat/ec
      Add RmtTrail ./dirdat/pc, Extract pfunca, Megabytes 1
      Shell sleep 5
      Info ER *
      Exit
      EOF
      echo '**WEST created, but not started.'
      sleep 3
      
      cd $GG_EURO_HOME
      export ORACLE_SID=euro
      ./ggsci <<EOF
      Obey /home/oracle/labs/setup/startup.oby
      Shell sleep 5
      Add Replicat rfunc, ExtTrail ./dirdat/px
      Add Replicat rfunca, ExtTrail ./dirdat/pc
      Shell sleep 5
      Info ER *
      Exit
      EOF
      echo '**EAST created, but not started.'
      sleep 3
      
      cd $GG_AMER_HOME
      export ORACLE_SID=amer
      ./ggsci <<EOF
      Obey /home/oracle/labs/setup/startup.oby
      Shell sleep 3
      Start ER *
      Shell sleep 12
      Info ER *
      Exit
      EOF
      echo '**WEST started.'
      sleep 3
      
      cd $GG_EURO_HOME
      export ORACLE_SID=euro
      ./ggsci <<EOF
      Obey /home/oracle/labs/setup/startup.oby
      Shell sleep 3
      Start ER *
      Shell sleep 12
      Info ER *
      Exit
      EOF
      echo '**EAST started.'
      sleep 3
      
      cd ~/labs/Section13/sqlscripts
      export ORACLE_SID=amer
      sqlplus west/oracle_4U <<EOF
      @trans_generator.sql
      exit
      EOF
      echo '**Target WEST tables should now have more data. '
      echo 'Run (SELECT * FROM wshop_encrypt;) to see.'
                                                                                      
      

      Notice that in the file copy and cleanup, system generated report filenames *.RPT are always UPPERCASE, but by convention all process *.prm filenames are always lowercase.

 

Summary

    In this tutorial, you have learned how to:

    • Control OGG tasks from the keyboard using clever timesaving tricks
    • Control OGG tasks from Obey files
    • Control OGG tasks from OS script files

    Resources

    There are many other OGG education products you can use:

    • Specific documentation, viewlets, samples, and OLN URLs on OTN
    • Specific courses where the subject is discussed in detail
    • External Web sites for related information
    • To learn more about Oracle GoldenGate, refer to additional Oracle By Example (OBEs) in the Oracle Learning Library, specifically a set of OBEs on the subject.

    Credits

    • Lead Curriculum Developer: Steve Friedberg
    • Other SME Contributors: Pete Daly, Chris Lawless, Steve George