Goldengate between Oracle 11gR2 and Oracle 12cR1 include DDL replication

Goldengate Multiple Schema Replication between

Oracle 11gR2 and Oracle 12cR1

include DDL replication

Source Host:

OS:                  Red Hat Enterprise Linux Server release 5.10 (Tikanga)  x86_64

DB Name:       source  à 11gR2  (11.2.0.4.0)  reside on ASM

Host Name:     rac1.localdomain  

                

Target Host:

OS:                  Red Hat Enterprise Linux Server release 6.6 (Santiago) x86_64 

DB Name:        target  à 12cR1   (12.1.0.2.0)  reside on ASM

Host Name:     node1.localdomain

Goldengate Version:  12.1.2.1.0

Main Configuration Steps

• Install Goldengate Software on both source and target systems
• Export the schemas which will be replicated and import them to the target database
• Prepare the source database
• Prepare the target database
• Configure extract, pump and replicat process
• Start extract and replication proccess
• Control DML and DDL operations

  

1.       Install Goldengate Software on both source and target systems

Source:

[oracle@rac1 ~]$ cd /media/sf_oracle_setups/goldengate/fbo_ggs_Linux_x64_shiphome/Disk1/

[oracle@rac1 Disk1]$ sh runInstaller

 [oracle@rac1 gg]$ pwd

/u01/app/oracle/product/gg

[oracle@rac1 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (rac1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

GGSCI (rac1.localdomain) 2>

Target:

[oracle@node1 goldengate]$ cd /media/sf_12c/goldengate/fbo_ggs_Linux_x64_shiphome/Disk1/

[oracle@node1 Disk1]$ sh runInstaller

   

  

  

[oracle@node1 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:21:34

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (node1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

GGSCI (node1.localdomain) 2>

  

2.       Export the schemas which will be replicated and import them to the target database

Source:

create or replace directory pump_dir as '/u01/app/oracle/pump';

expdp  userid='"/ as sysdba"' schemas=hr,scott dumpfile=scott_hr.dmp directory='pump_dir'

Target:

create or replace directory pump_dir as '/u01/app/oracle/pump';

create tablespace target_tbs datafile '+DATA' size 500m;

impdp  userid='"/ as sysdba"' dumpfile=scott_hr.dmp directory='pump_dir' remap_tablespace=example:target_tbs;

grant connect, resource, unlimited tablespace to hr, scott;

***impdp can give some errors about granting roles if you dont create the roles first manually.

3.      Prepare the source database

a.       Switch database archivelog

[oracle@rac1 ~]$ srvctl stop database -d source

[oracle@rac1 ~]$ srvctl start database -d source -o mount

SQL> alter database archivelog;

SQL> alter database open;

b.       Add supplemental log and disable recyclebin

SQL> alter database add supplemental log data;

SQL> alter system set recyclebin=off scope=spfile;

SQL> purge dba_recyclebin;

c.        Create goldengate tablespace and user

SQL> create tablespace gg_tbs datafile '+DATA' size 500M reuse autoextend on;

SQL> create user gg_user identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;

SQL> grant create session, connect, resource to gg_user;

SQL> grant dba to gg_user;

SQL> grant execute on utl_file to gg_user;

d.       Create the objects and roles for ddl replication

[oracle@rac1 gg]$ pwd

/u01/app/oracle/product/gg

SQL> @marker_setup.sql

--enter gg_user for goldengate schema

SQL> @ddl_setup.sql

--enter gg_user for goldengate schema

SQL> @role_setup.sql

--enter gg_user for goldengate schema

SQL> GRANT GGS_GGSUSER_ROLE TO gg_user;

SQL> @ddl_enable.sql

SQL> alter system set enable_goldengate_replication=true;

e.       Add info about Goldengate DDL schema

[oracle@rac1 gg]$ ./ggsci

GGSCI (rac1.localdomain) 1> edit params ./GLOBALS

GGSCHEMA gg_user

GGSCI (rac1.localdomain) 2>

f.        Create trail and discard(only on target) directories

Source:

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/product/gg/dirdat/tr

Target:

[oracle@node1 ~]$ mkdir -p /u01/app/oracle/product/gg/dirdat/tr

[oracle@node1 ~]$ mkdir -p /u01/app/oracle/product/gg/discard

g.       Check manager process and port on host side

GGSCI (rac1.localdomain) 1> edit params mgr

PORT 7809

GGSCI (rac1.localdomain) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

GGSCI (rac1.localdomain) 3> start manager

Manager started.

GGSCI (rac1.localdomain) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

h.       Add primary key information to the logfiles for replicated schemas

GGSCI (rac1.localdomain) 1> dblogin userid gg_user;

Password:

Successfully logged into database.

GGSCI (rac1.localdomain as gg_user@source1) 2> add schematrandata hr

2015-01-14 17:24:04  INFO    OGG-01788  SCHEMATRANDATA has been added on schema hr.

2015-01-14 17:24:04  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema hr.

GGSCI (rac1.localdomain as gg_user@source1) 3> add schematrandata scott

2015-01-14 17:24:28  INFO    OGG-01788  SCHEMATRANDATA has been added on schema scott.

2015-01-14 17:24:28  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema scott.

i.         Add ASM entry to the tnsnames.ora for redo logs

ASM1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.150.198)(PORT = 1521))

    (CONNECT_DATA =

      (INSTANCE_NAME = +ASM1)

      (SERVER = dedicated)

      (SERVICE_NAME = +ASM)

    )

  )

4.      Prepare the target database

SQL> create tablespace gg_tbs datafile '+DATA' size 500M reuse autoextend on;

SQL> create user gg_user identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;

SQL> grant create session, connect, resource to gg_user;

SQL> grant dba to gg_user;

SQL> grant execute on utl_file to gg_user;

SQL> alter system set enable_goldengate_replication=TRUE;

5.      Configure extract, pump and replicat process

Source:

GGSCI (rac1.localdomain) 1> add extract ext1, tranlog, begin now

EXTRACT added.

GGSCI (rac1.localdomain) 2> add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1

EXTTRAIL added.

GGSCI (rac1.localdomain) 3> edit params ext1

extract ext1

userid gg_user password oracle

tranlogoptions asmuser sys@asm1 asmpassword 123456

exttrail /u01/app/oracle/product/gg/dirdat/tr

ddl include mapped

map hr.*;

map scott.*;

table hr.*;

table scott.*;

GGSCI (rac1.localdomain) 5> add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr, begin now

EXTRACT added

GGSCI (rac1.localdomain) 7> add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1

RMTTRAIL added.

GGSCI (rac1.localdomain) 6> edit params pump1

extract pump1

userid gg_user, password oracle

rmthost 192.168.150.241, mgrport 7809

rmttrail /u01/app/oracle/product/gg/dirdat/tr

passthru

table hr.*;

table scott.*;

GGSCI (rac1.localdomain) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     STOPPED     EXT1        00:00:00      00:31:46   

EXTRACT     STOPPED     PUMP1       00:00:00      00:09:15   

Target:

GGSCI (node1.localdomain) 1> edit params mgr

PORT 7809

GGSCI (node1.localdomain) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

GGSCI (node1.localdomain) 6> edit params ./GLOBALS

checkpointtable gg_user.checkpoint

GGSCI (node1.localdomain) 1> dblogin userid gg_user

Password:

Successfully logged into database.

GGSCI (node1.localdomain as gg_user@target1) 2> add checkpointtable gg_user.checkpoint

Successfully created checkpoint table gg_user.checkpoint.

GSCI (node1.localdomain as gg_user@target1) 3> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin now

REPLICAT added.

GGSCI (node1.localdomain as gg_user@target1) 4> edit params rep1

replicat rep1

assumetargetdefs

SETENV (ORACLE_SID=target1)

SETENV (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)

userid gg_user, password oracle

discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10

DDL

map hr.*, target hr.*;

map scott.*, target scott.*;

GGSCI (node1.localdomain as gg_user@target1) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    STOPPED     REP1        00:00:00      00:11:44

  

6.      Start extract and replication proccess.

Source:

GGSCI (rac1.localdomain) 1> start extract ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

GGSCI (rac1.localdomain) 2> start extract pump1

Sending START request to MANAGER ...

EXTRACT PUMP1 starting

GGSCI (rac1.localdomain) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EXT1        00:00:00      00:00:00   

EXTRACT     RUNNING     PUMP1       00:00:00      00:00:08

Target:

GGSCI (node1.localdomain) 11> start replicat rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (node1.localdomain) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    RUNNING     REP1        00:00:00      00:00:08

7.      Control DML and DDL operations

Source:

[oracle@rac1 ~]$ sqlplus hr/hr

SQL> create table emre_test (idx number);

SQL> insert into emre_test values (1);

SQL> insert into emre_test values (2);

SQL> commit;

exit;

[oracle@rac1 ~]$ sqlplus scott/scott

SQL> create table emre_test2 (names varchar2(30));

SQL> insert into emre_test2 values ('emre');

SQL> insert into emre_test2 values ('emir');

SQL> commit;

Target:

[oracle@node1 tr]$ sqlplus hr/hr

SQL> select * from emre_test;

       IDX

----------

        1

        2

[oracle@node1 tr]$ sqlplus scott/scott

SQL> select * from emre_test2;

NAMES

------------------------------

emre

emir