Oracle Golden Gate : Installing GG in AWS (Table replication in same DB)

Dear Readers,

In this article we will see Installing GG in AWS (Table replication in same DB).

 

Step 1 : Download GG software

Step 2 : check for xclock in AWS server.

[ec2-user@ip-172-31-21-177 tmp]$ su – oracle
Password:
Last login: Sat Dec 24 12:03:29 EST 2016 on pts/3
Last failed login: Sat Dec 24 12:06:15 EST 2016 on pts/3
There was 1 failed login attempt since the last successful login.
[oracle@ip-172-31-21-177 ~]$ xclock

Step 3 : Setup .bash_profile

[oracle@ip-172-31-21-177 ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$HOME
export PATH

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12102
export ORACLE_SID=cloud12c
export GG_HOME=/u01/app/oracle/product/12102/gg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH:$GG_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

Step 4 : run installer

 

 

Step 5 : DB Changes

SQL> alter system set enable_goldengate_replication=true;

System altered.

SQL> alter system set enable_goldengate_replication=true scope=spfile;

System altered.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT supplemental_log_data_min FROM v$database;

SUPPLEME
——–
YES

Please restart DB after above steps.

SQL> create tablespace ggs datafile ‘+DATA01’ size 100M;

Tablespace created.

SQL> create user ggs_user identified by ggs_user default tablespace ggs;

User created.

SQL> grant dba to ggs_user;

Grant succeeded.

SQL> show user
USER is “SCOTT”

SQL> alter table unpar_table add primary key (A,y);

Table altered.

SQL> create table unpar_table_gg as select * from unpar_table where 1=2;

Table created.
SQL> alter table unpar_table_gg add primary key (A,y);

Table altered.

Step 6 : GG changes

Modify default manager param file :

GGSCI (ip-172-31-21-177) 1> view params mgr

PORT 7809
userid ggs_user, password ggs_user
purgeoldextracts ./dirdat/tr, usecheckpoints, minkeephours 2

 

 

 

[oracle@ip-172-31-21-177 gg]$ pwd
/u01/app/oracle/product/12102/gg
[oracle@ip-172-31-21-177 gg]$ mkdir discard

GGSCI (gg2) 9> edit params ./GLOBALS
GGSCI (gg2) 10> shell cat GLOBALS

CHECKPOINTTABLE ggs_user.checkpoint

GGSCI (gg2) 3> dblogin userid ggs_user, password ggs_user
Successfully logged into database.

GGSCI (gg2) 4> add checkpointtable ggs_user.checkpoint

ADD EXTRACT extsrc, TRANLOG, BEGIN NOW
add exttrail ./dirdat/tr, extract extsrc

[oracle@ip-172-31-21-177 dirprm]$ cat extsrc.prm
extract extsrc
–setenv (ORACLE_HOME = “/u01/app/oracle/product/11.2.0/db_1”)
userid ggs_user, password ggs_user
tranlogoptions dblogreader
exttrail ./dirdat/tr
table scott.unpar_table;

start extract here.

From DB :

SQL> select * from v$transaction;

no rows selected

SQL> select current_scn from v$database;

CURRENT_SCN
———–
2101800

Initial Load Process : 

expdp ggs_user/<PW> directory=DATA_PUMP_DIR dumpfile=partable1.dmp logfile=partable.log tables=scott.unpar_table flashback_scn=2101800

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported “SCOTT”.”UNPAR_TABLE” 29.34 MB 1000000 rows
Master table “GGS_USER”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

impdp ggs_user/ggs_user directory=DATA_PUMP_DIR dumpfile=partable1.dmp logfile=partable_imp.log remap_table=scott.unpar_table:unpar_table_gg TABLE_EXISTS_ACTION=append

Table “SCOTT”.”UNPAR_TABLE_GG” exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”UNPAR_TABLE_GG” 29.34 MB 1000000 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

 

add replicat reptrgt, exttrail ./dirdat/tr

[oracle@ip-172-31-21-177 dirprm]$ cat reptrgt.prm
replicat reptrgt
–setenv (ORACLE_HOME = “/u01/app/oracle/product/11.2.0/db_1”)
–setenv (ORACLE_SID = “ggtarget1”)
setenv (NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252)
assumetargetdefs
HANDLECOLLISIONS
userid ggs_user, password ggs_user
discardfile ./discard/rep1_discard.txt, append, megabytes 10
map scott.unpar_table, target scott.unpar_table_gg;

–> start replicat reptrgt atcsn 2101800

 

edit reptrgt and remove handle collisions :

begin
for i in 3001 .. 4000
loop
for j in 3001 .. 4000
loop
insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
commit;
end loop;
end loop;
commit;
end;

 

Note: If you have any questions regarding implementation please comment below.