- Source Database:
Oracle Database 12.1.0.2.0 (AWS RDS) with Oracle GoldenGate 12.2.0.1 installed on EC2 instance (52.201.221.71) - Target Database:
Oracle Database 11.2.0.4 (129.152.156.112) with GGCS (running GoldenGate 12.2.0.1) (129.152.156.178)
> sqlplus awsuser/[password]@[rdshostname.com]:1521/ORCL12C SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 2 22:51:52 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Wed Nov 02 2016 22:41:24 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create table member (id number(4) primary key, name varchar2(25), region varchar2(15)); Table created. SQL> desc member; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(4) NAME VARCHAR2(25) REGION VARCHAR2(15)
[oracle@PM-DBaaS11g ~]$ sqlplus ggadmin/[password] SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 3 02:45:36 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g EE Extreme Perf Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> create user pmdemo identified by [password]; User created. SQL> grant connect, resource to pmdemo; Grant succeeded. SQL> connect pmdemo/pmdemo Connected. SQL> create table member (id number(4) primary key, name varchar2(25), region varchar2(15)); Table created. SQL> desc member Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(4) NAME VARCHAR2(25) REGION VARCHAR2(15)
sudo ssh -N -i ~/.ssh/[private_key] -v -D 127.0.0.1:9000 [email protected]
extract extgdrds useridalias awsuser EXTTRAIL ./dirdat/rd ddl include all ddloptions addtrandata, report TABLE awsuser.*;
pmpgdrds.prm:
extract pmpgdrds RMTHOST 129.152.156.178, MGRPORT 7744, socksproxy 127.0.0.1:9000 discardfile ./dirrpt/pumgdop.dsc, purge RMTTRAIL ./dirdat/rd PASSTHRU TABLE awsuser.*;
stop *! pause 5 info all sh ls ./dirdat sh rm ./dirdat/rd* sh ls ./dirdat dblogin useridalias awsuser unregister extract extgdrds database pause 5 delete extgdrds! delete pmpgdrds! info alladd_extgdrds.oby:
dblogin useridalias awsuser add extract extgdrds integrated tranlog, begin now add exttrail ./dirdat/rd extract extgdrds register extract extgdrds database pause 5 start extgdrds pause 25 info extgdrds info alladd_pmpgdrds.oby
ADD EXTRACT pmpgdrds EXTTRAILSOURCE ./dirdat/rd BEGIN NOW ADD RMTTRAIL ./dirdat/rd EXTRACT pmpgdrds start pmpgdrds pause 10 info pmpgdrds info all
GGSCI> obey dirprm/del_gdrds.oby GGSCI> obey dirprm/add_extgdrds.oby GGSCI> obey dirprm/add_pmpgdrds.oby
GGSCI>info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTGDRDS 00:01:58 00:00:02 EXTRACT RUNNING PMPGDRDS 00:00:00 00:00:00
GGSCI (ip-172-30-3-169.ec2.internal as awsuser@ORCL12C) 53> add trandata awsuser.member Logging of supplemental redo log data is already enabled for table AWSUSER.MEMBER. TRANDATA for instantiation CSN has been added on table 'AWSUSER.MEMBER'.
replicat repgdopc useridalias ggadmin dboptions integratedparams(parallelism 2) discardfile ./dirrpt/repgdrds.dsc,append megabytes 50 ddl include all map awsuser.*, TARGET pmdemo.*;
del_repgdopc.oby:
stop *! pause 5 info all sh ls ./dirdat sh rm ./dirdat/rd* sh ls ./dirdat pause 5 delete repgdopc! info all
add_repgdopc.oby:
add replicat repgdopc, integrated, exttrail ./dirdat/rd start repgdopc pause 10 info all
GGSCI> obey dirprm/add_repgdopc.oby
GGSCI (pm-ggcs11g-ggcs-1) 9> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPGDOPC 00:00:00 00:00:05
Insert a record on the source and theh DML operation will be prelicate to the target.
SQL> create table purch_hist(user_id number(4), item_name varchar2(10));
OPC DB:
SQL> desc purch_hist; Name Null? Type ----------------------------------------- -------- ---------------------------- USER_ID NUMBER(4) ITEM_NAME VARCHAR2(10)