Posted by: Amit Kumar Srivastava (OCM11g)
After Installation Goldengate binary on your database server add below concern environment variable on your bash profile.
SOURCE DATABASE : ggtest @SOURCE (vm212.ora.com)TARGET DATABASE : ggtest @TARGET (vm213.ora.com)
ENVIRONMENT:
SOURCE Database:
export ORACLE_SID=SOURCE
eport ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:/u01/app/gg_home
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/gg_home
TARGET Database:
export ORACLE_SID=SOURCE
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:/u01/app/gg_home
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/gg_home
ON SOURCE DATABASE (SOURCE@ggtest):
For DDL REPLICATION if you want then follow below step or ignore
DDL support scripts should be run in source database:
[oracle@vm212]$ cd /u01/app/gg_home
[oracle@vm212]$ sqlplus / as sysdba
SQL> @marker_setup.sql Enter GoldenGate schema name: ggtest
Setting schema name to ggtest
MARKER TABLE
——————————-
OK
MARKER SEQUENCE
——————————-
OK
Script complete.
Run ddl_setup script.
SQL> @ddl_setup.sqlEnter GoldenGate schema name: ggtest
SQL> @role_setup.sql
SQL> GRANT GGS_GGSUSER_ROLE TO ggtest
Grant succeeded.
SQL> @ddl_enable
Trigger altered.
ON SOURCE GOLDENGATE SERVER
GGSCI (vm212.ora.com) 1> create subdirs
Creating subdirectories under current directory /u01/app/gg_home
Parameter files /u01/app/gg_home/dirprm: Already Exists
Report files /u01/app/gg_home/dirrpt: created
Checkpoint files /u01/app/gg_home/dirchk: created
Process status files /u01/app/gg_home/dirpcs: created
SQL script files /u01/app/gg_home/dirsql: created
Database definitions files /u01/app/gg_home/dirdef: created
Extract data files /u01/app/gg_home/dirdat: created
Temporary files /u01/app/gg_home/dirtmp: created
Stdout files /u01/app/gg_home/dirout: created
ON TARGET GOLDENGATE SERVER
GGSCI (vm213.ora.com) 1> create subdirs
Creating subdirectories under current directory /u01/app/gg_home
Parameter files /u01/app/gg_home/dirprm: Already Exists
Report files /u01/app/gg_home/dirrpt: created
Checkpoint files /u01/app/gg_home/dirchk: created
Process status files /u01/app/gg_home/dirpcs: created
SQL script files /u01/app/gg_home/dirsql: created
Database definitions files /u01/app/gg_home/dirdef: created
Extract data files /u01/app/gg_home/dirdat: created
Temporary files /u01/app/gg_home/dirtmp: created
Stdout files /u01/app/gg_home/dirout: created
ON SOURCE GOLDENGATE SERVER
Creating parameter files for EXTRACT AND PUMP process:add_extora_pumpora.oby
vi dirprm/add_extora_pumpora.oby
– DATABASE LOGIN DETAILS
dblogin userid ggtest, password oracle
– Add a change data extract process group named extora
— that reads from the database redologs
add extract extora, tranlog, begin now
– Associate trailfile that will contain the change data
— as output from the extora process group
add exttrail ./dirdat/et, extract extora
– FOR REMOTE OPERATION
— Add a change data extract process called a Pump
— that will copy local trail data to a
— remote, off box location
add extract pumpora, exttrailsource ./dirdat/et
– Associate remote trail with pumpora
add rmttrail ./dirdat/rt, extract pumpora
– Connect to database and add table level supplementel
— logging for tables emp and dept
— For Schema Level
add trandata ggtest.*
— For Individual table level
— add trandata ggtest.emp
— add trandata ggtest.dept
Creating parameter file for MANAGER
GGSCI> edit params mgr
PORT 7809
SYSLOG NONE
GGSCI> VIEW PARAMS MGR
Creating parameter file for EXTORA
GGSCI> edit params extora
– The first line must be the word extract
— followed by the name of extract
extract extora
— We log in here to get metadata.
— Yes the password can be encrypted
userid ggtest, password oracle
– Here is we are writing the data into trail
exttrail ./dirdat/et
– Optional parameter to update time since chkpt
— from default every 10 Secs
checkpointsecs 1
– We can list each table or using wildcarding
– DDL REPLICATIONS
DDL INCLUDE MAPPED OBJNAME ggtest.*
table ggtest.* ;
GGSCI> view params extora
Creating parameter file for PUMPORA
extract pumpora
– This is a pump process, so there is
— no reason to log into database so
— we can use the parameter passthru
passthru
– The pump writes remotely to the target machine.
— Here is we put the DNS or IP ADDRESS.
— We also put the port address that
— the REMOTE manager is running on.
rmthost vm213.ora.com, mgrport 7810
– This is the location where the trail
— is written on the target machine.
rmttrail ./dirdat/rt
– Optional parameter to update time since
— chkpt from default every 10 Secs.
checkpointsecs 1
– Listing of the Schema/tables (or wilecards)
table ggtest.*;
ON TARGET GOLDENGATE SERVER
Creating parameter file to add REPLICATE GROUP
GGSCI> edit params dirprm/add_repora.oby
– Connect to database
dblogin userid ggtest, password oracle
– Add checkpoint table
add checkpointtable ggtest.ogg_checkpoint
– Add replicat process repora that will convert
— trail rt into SQL transaction continiously
add replicat repora, exttrail ./dirdat/rt, checkpointtable ggtest.ogg_checkpoint
GGSCI> obey dirprm/add_repora.oby
Creating parameter file for MANAGER on TARGET SERVER
GGSCI> edit params mgr
PORT 7810
DYNAMICPORTLIST 8000-8010
– Dynamicportlist is a list of ports
— that the pump process on the source
— side work with the server collector
— on the TARGET side
SYSLOG NONE
GGSCI> START MGR
GGSCI> INFO ALL
Creating parameter file for REPLICATE on TARGET SERVER
GGSCI> edit params repora
replicat repora
ASSUMETARGETDEFS
userid ggtest, password oracle
– Ignore DDL error on replication side and continue DDL REPLICATION.
DDLERROR DEFAULT IGNORE
MAP ggtest.*, TARGET ggtest.* ;
Creating parameter file for GLOBALS on TARGET SERVER to store SCHEMA NAME for DDL REPLICATION
GGSCI> edit params ./GLOBALS
GGSCHEMA ggtest
GGSCI> view params ./GLOBALS
GGSCI> info all
GGSCI> start repora
GGSCI> info all
ON SOURCE GOLDENGATE SERVER
GGSCI> start extora
GGSCI> start pumpora
GGSCI> info all
Execute transaction on SOURCE DATABASE ON REPLICATION TABLES
GGSCI> stats extora total
GGSCI> stats pumpora total
GGSCI> info extora, detail
GGSCI> info pumpora, detail
ON TARGET GOLDENGATE SERVER
GGSCI> shell ls -lh dirdat/rt*
GGSCI> info repora
GGSCI> start repora
GGSCI> stats repora total
GGSCI> info repora
Execute DML/DDL transaction on source database and check will it replicate on target server …
Hope this will DML/DDL Replication on Goldengate.
