- Control everything from inside of GGSCI using Obey files
- Control everything from outside of GGSCI using shell scripts such as
bash. - 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.
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: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:Prerequisites
Before starting this tutorial, you should:- 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.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.
|
sqlplus as well as GGSCI.| ~/.bash_profile |
...(other lines not shown)...
alias sqlplus='rlwrap sqlplus'
alias ggsci='rlwrap ./ggsci'
|
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.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."| 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) > |
| 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..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
|
64:" is a continuation of the previous line, so LD_LIBRARY_PATH is all on one line through the end of "amd64/server."| 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
| 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
|
GGSCI > Obey startup.oby| 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/*
|
[username path]$ ./shutdown_ogg.shNote that eini and rini are deleted explicitly because initial load tasks are not included in the Delete ER * wildcard.- 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
| 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 *
|
$ORACLE_HOME, and can use relative paths such as ../.., but you cannot use tilde ~ shortcuts for $HOME inside of GGSCI.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
|
rm: cannot remove `dirrpt/*': No such file or directoryThis is the same behavior when running from a regular OS prompt.
2.2: Calling SQL*Plus Commands from Within Obey
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
|
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.
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
| 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 *
|
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.
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
|
2.4: Unsupported: The test! Command
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.
|
| 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
|
- 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
./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...)
|
3.2: Calling SQL*Plus Commands from Within Shell Scripts
.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
|
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.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
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/*
|
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.| ~\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.'
|
*.RPT are always UPPERCASE, but by convention all process *.prm filenames are always lowercase.- 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
- 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.
- Lead Curriculum Developer: Steve Friedberg
- Other SME Contributors: Pete Daly, Chris Lawless, Steve George
No comments:
Post a Comment