GOLDEN GATE SETUP - DML/DDL

GOLDEN GATE SETUP  -DML/DDL

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.