Tag Archives: goldengate

To resync one or more tables/schemas on different SCN's using a single or minimum number of replicats without using handlecollisions.

This can be done with Oracle goldenGate (OGG) 11.1.1.0.x or higher.
We are going to add new table SOURCE.DASANI to existing extract,pump and replicat


Environment

Source

Server Name		: RAC1
OS Version		: Red Hat Enterprise Linux Server release 6.5
RAC			: No
Database Version	: 12.2.0.1.0
GoldenGate Version	: Version 12.3.0.1.4 for oracle db 12.2.0.1.0
Database Name		: PSG01DAS
Golden Gate User	: OGGADMIN
Golden Gate Extract	: ERAJ
Golden Gate Pump	: PRAJ
Source Schema		: SOURCE
Oracle Home		: /u01/app/oracle/product/12.2.0/dbhome_1
GoldenGate Home		: /u01/app/oracle/product/12.3/ogg

Target

Server Name		: RAC1
OS Version		: Red Hat Enterprise Linux Server release 6.5
RAC			: No
Database Version	: 12.2.0.1.0
GoldenGate Version	: Version 12.3.0.1.4 for oracle db 12.2.0.1.0
Database Name		: USG01DAS
Golden Gate User	: OGGADMIN
Golden Gate Replicat    : RRAJ
Target Schema		: TARGET
Oracle Home		: /u01/app/oracle/product/12.2.0/dbhome_1
GoldenGate Home		: /u01/app/oracle/product/12.3/ogg

Source:

1. Login to GGSCI

GGSCI (rac1.rajasekhar.com) 1> dblogin UserIdAlias OGGADMIN
Successfully logged into database.

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 2>

2. Add trandata for new table

SQL> SELECT COUNT (*) FROM SOURCE.DASANI;

  COUNT(*)
----------
        15  <-----

SQL> ALTER TABLE SOURCE.DASANI ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Table altered.

SQL>

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 2> ADD TRANDATA SOURCE.DASANI

2019-04-24 00:39:01  WARNING OGG-06439  No unique key is defined for table DASANI. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2019-04-24 00:39:01  INFO    OGG-15130  No key found for table SOURCE.DASANI.  All viable columns will be logged.

2019-04-24 00:39:03  INFO    OGG-15132  Logging of supplemental redo data enabled for table SOURCE.DASANI.

2019-04-24 00:39:03  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table SOURCE.DASANI.

2019-04-24 00:39:03  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table SOURCE.DASANI.

2019-04-24 00:39:04  INFO    OGG-10471  ***** Oracle Goldengate support information on table SOURCE.DASANI *****
Oracle Goldengate support native capture on table SOURCE.DASANI.
Oracle Goldengate marked following column as key columns on table SOURCE.DASANI: NAME, ROLE
No unique key is defined for table SOURCE.DASANI.

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 3>

3. Add table to Extract

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 4> edit params eraj

-- Add below
TABLE SOURCE.DASANI;

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 5> view params eraj

Extract ERAJ
SETENV (ORACLE_SID='PSG01DAS')
UserIdAlias OGGADMIN
TranlogOptions IntegratedParams (max_sga_size 256)
Exttrail ./dirdat/ea
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE SOURCE.EMP;
TABLE SOURCE.EMPLOYEE;
TABLE SOURCE.X;
TABLE SOURCE.X1;
TABLE SOURCE.DASANI;  <---

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 6>

4. Add table to Pump

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 7> edit params praj

-- Add below
TABLE SOURCE.DASANI;


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 8> view params praj

Extract  PRAJ
SETENV (ORACLE_SID='PSG01DAS')
UserIdAlias OGGADMIN
rmthost rac2, mgrport 7809
rmttrail ./dirdat/pa
TABLE SOURCE.EMP;
TABLE SOURCE.EMPLOYEE;
TABLE SOURCE.X;
TABLE SOURCE.X1;
TABLE SOURCE.DASANI; <----


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 9>

5. Restart Extract

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 11> stop eraj

Sending STOP request to EXTRACT ERAJ ...
Request processed.


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 12> start eraj

Sending START request to MANAGER ...
EXTRACT ERAJ starting


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 13>

6. Restart Pump

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 13> stop praj

Sending STOP request to EXTRACT PRAJ ...
Request processed.


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 14> start praj

Sending START request to MANAGER ...
EXTRACT PRAJ starting


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 15>

7. Verify Status

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ERAJ        00:00:03      00:00:00
EXTRACT     RUNNING     PRAJ        00:00:00      00:00:10


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 16>

8. Capture current SCN

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2298283 <----

SQL>

9. Export Table using FLASHBACK_SCN

SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

OWNER      DIRECTORY_NAME            DIRECTORY_PATH                           ORIGIN_CON_ID
---------- ------------------------- ---------------------------------------- -------------
SYS        DATA_PUMP_DIR             /u01/app/oracle/admin/PSG01DAS/dpdump/               0

SQL>


[oracle@rac1 ~]$ expdp \'/ as sysdba\' directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani.log tables=source.dasani flashback_scn=2298283

Export: Release 12.2.0.1.0 - Production on Wed Apr 24 00:52:11 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani.log tables=source.dasani flashback_scn=2298283
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SOURCE"."DASANI"                           5.687 KB      15 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/PSG01DAS/dpdump/source_dasani.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Apr 24 00:52:48 2019 elapsed 0 00:00:28

[oracle@rac1 ~]$

10. Transfer dump to target server

[oracle@rac1 ~]$ scp /u01/app/oracle/admin/PSG01DAS/dpdump/source_dasani.dmp oracle@rac2:/u01/app/oracle/admin/USG01DAS/dpdump/
oracle@rac2's password:
source_dasani.dmp                 100%  200KB 200.0KB/s   00:00
[oracle@rac1 ~]$

Target:

11. Import new table

SQL> col OWNER for a10
SQL> col DIRECTORY_NAME for a25
SQL> col DIRECTORY_PATH for a40
SQL> set lines 180
SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

OWNER      DIRECTORY_NAME            DIRECTORY_PATH                           ORIGIN_CON_ID
---------- ------------------------- ---------------------------------------- -------------
SYS        DATA_PUMP_DIR             /u01/app/oracle/admin/USG01DAS/dpdump/               0

SQL>

[oracle@rac2 ~]$ impdp \'/ as sysdba\' directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani_imp.log remap_schema=source:target transform=segment_attributes:n transform=oid:n

Import: Release 12.2.0.1.0 - Production on Wed Apr 24 00:58:28 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani_imp.log remap_schema=source:target transform=segment_attributes:n transform=oid:n
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TARGET"."DASANI"                           5.687 KB      15 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Wed Apr 24 00:58:54 2019 elapsed 0 00:00:23

[oracle@rac2 ~]$

12. Add table to Replicat

Note : 

Please use single quotes since OGG v12 uses ANSI SQL parameter by default, if you use double quotes then you may receive this error.
You can use double quotes in pre OGG v12
    

2019-04-24T01:10:34.244+0800  ERROR   OGG-00375  Oracle GoldenGate Delivery for Oracle, rraj.prm:  Error in FILTER clause.
2019-04-24T01:10:39.386+0800  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rraj.prm:  PROCESS ABENDING.

GGSCI (rac2.rajasekhar.com) 14> edit params rraj

-- Add below line

Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283);


GGSCI (rac2.rajasekhar.com) 15> view params rraj

Replicat RRAJ
SETENV(ORACLE_SID='USG01DAS')
UserIdAlias OGGADMIN
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
UserIdAlias OGGADMIN
Map SOURCE.EMP, target TARGET.EMP;
Map SOURCE.EMPLOYEE, target TARGET.EMPLOYEE;
Map SOURCE.X, target TARGET.X;
Map SOURCE.X1, target TARGET.X1;
Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283);  <-----


GGSCI (rac2.rajasekhar.com) 16>

13. Restart Replicat

GGSCI (rac2.rajasekhar.com) 16> stop rraj

Sending STOP request to REPLICAT RRAJ ...
Request processed.


GGSCI (rac2.rajasekhar.com) 17>
GGSCI (rac2.rajasekhar.com) 19> start rraj

Sending START request to MANAGER ...
REPLICAT RRAJ starting


GGSCI (rac2.rajasekhar.com) 20>

14. Verify Status

GGSCI (rac2.rajasekhar.com) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     RRAJ        00:00:00      00:00:11


GGSCI (rac2.rajasekhar.com) 21>

15. Data sync verify

Source:

SQL> SELECT COUNT(*) FROM SOURCE.DASANI;

  COUNT(*)
----------
        15 <---

SQL> 
SQL> INSERT INTO SOURCE.DASANI VALUES ('CHITTI','MANAGER');

1 row created.

SQL> INSERT INTO SOURCE.DASANI VALUES ('SRINIVAS','LEAD');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT COUNT(*) FROM SOURCE.DASANI;

  COUNT(*)
----------
        17 <-----

SQL>

Target:

SQL> SELECT COUNT(*) FROM TARGET.DASANI;

  COUNT(*)
----------
        15

SQL>

SQL> SELECT COUNT(*) FROM TARGET.DASANI;

  COUNT(*)
----------
        17 <----

SQL>

It's sync now, next step we need to FILTER PARAMETER from Relicat

16. Remove the FILTER parameters replicat

GGSCI (rac2.rajasekhar.com) 31> dblogin UserIdAlias OGGADMIN
Successfully logged into database.

GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 32> lag rraj

Sending GETLAG request to REPLICAT RRAJ ...
Last record lag 246 seconds.
Low watermark lag: 1.
High watermark lag: 258.
Low watermark position: 2211928.
High watermark position: 2316576.
At EOF, no more records to process


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 33> edit params rraj

-- From 
Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283);

-- To
Map SOURCE.DASANI, target TARGET.DASANI;


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 34> view params rraj

Replicat RRAJ
SETENV(ORACLE_SID='USG01DAS')
UserIdAlias OGGADMIN
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
UserIdAlias OGGADMIN
Map SOURCE.EMP, target TARGET.EMP;
Map SOURCE.EMPLOYEE, target TARGET.EMPLOYEE;
Map SOURCE.X, target TARGET.X;
Map SOURCE.X1, target TARGET.X1;
Map SOURCE.DASANI, target TARGET.DASANI; <-----


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 35>

17. Restart the replicat

GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 35> stop rraj

Sending STOP request to REPLICAT RRAJ ...
Request processed.


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 36> 

GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 37> start rraj

Sending START request to MANAGER ...
REPLICAT RRAJ starting


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 38>

18. Verify Status

GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 38> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     RRAJ        00:00:00      00:00:05


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 39>

19. Re-verify data sync

Source:

SQL> SELECT COUNT(*) FROM SOURCE.DASANI;

  COUNT(*)
----------
        17 <----

SQL>    INSERT INTO SOURCE.DASANI VALUES ('SOMU','DBA');

1 row created.

SQL> INSERT INTO SOURCE.DASANI VALUES ('ZABI','DBA');

1 row created.

SQL> INSERT INTO SOURCE.DASANI VALUES ('GIRI','BIGDATA');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT COUNT(*) FROM SOURCE.DASANI;

  COUNT(*)
----------
        20 <-----

SQL>

Target:

SQL> SELECT COUNT(*) FROM TARGET.DASANI;

  COUNT(*)
----------
        17

SQL> 

SQL> SELECT COUNT(*) FROM TARGET.DASANI;

  COUNT(*)
----------
        20 <------

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Reference:

OGG How to Resync Tables / Schemas on Different SCN s in a Single Replicat (Doc ID 1339317.1)

Zero Downtime Migration

GoldenGategoldengate, migration from 10g to 11g, Rajasekhar Tirupathi, Zero downtime migration, Zero downtime migration using goldengate

AIM: Zero Downtime Migration with one-way replication between an Oracle 10gR2 database and an Oracle 11gR2 database on same server.

1. Environment
2. GoldenGate Installation on Source Oracle 10gR2
3. GoldenGate Installation on Target Oracle 11gR2
4. Create CDS User (Application user) for testing

5. OGG Configuration on Source Oracle 10gR2
a) Set the environment
b) Login to Database
c) Configure Manager
d) Create Extract Group
e) Create Pump Group
f) Add supplemental logging

6. OGG Configuration on Target Oracle 11gR2
a) Set the environment
b) Login to Database
c) Configure Manager
d) Set up the Checkpoint table
i) Create a GLOBALS file
ii) Activate the GLOBALS parameters
iii) Add a Replicat checkpoint table

7) Create the Replicat Group on Target 11gR2
8. On Source Start Manager, Extract and Pump
9. On Target start the Manager only
10. Export data from source Oracle 10gR2
11. Transfer the dumpfile to 11g DATA_PUMP_DIR
12. Generate data on source Oracle 10gR2 (After Export)
13. Import Data and Start Replicat on Target 11gR2

14. Test and Verify the Results

INSERT OPERATION

a1) Execute INSERT again on the Source Oracle Database 10gR2
a2) Verify Insert Changes on Target Oracle Database 11gR2

UPDATE OPERATION

b1) Execute update on the Source Oracle Database 10gR2
b2) Verify Update Changes on Target Oracle Database 11gR2

DELETE OPERATION

c1) Execute Delete on the Source Oracle Database 10gR2
c2) Verify Delete Changes on Target Oracle Database 11gR2

15. Verify using GGSCI

1. Environment

Source Oracle 10gR2

Server Name			: RAC1.RAJASEKHAR.COM
OS Version			: Red Hat Enterprise Linux 5.7
Database Version		: 10.2.0.5
GoldenGate Version		: Version 11.2.1.0.1 for 10g database
Database Name			: DB10
File System			: ASM 
Golden Gate User		: GATE
Golden Gate Extract		: E10G
Golden Gate Pump		: P10G
Application User		: CDS (Migrating this user to target)
Oracle Home			: /u01/app/oracle/product/10g
GoldenGate Home			: /u01/app/oracle/product/10gogg

Target Oracle 11gR2

Server Name			: RAC1.RAJASEKHAR.COM
OS Version			: Red Hat Enterprise Linux 5.7
Database Version		: 11.2.0.4
GoldenGate Version		: Version 11.2.1.0.5 for 11g database
Database Name			: DB11
File System			: Non-ASM
Golden Gate User		: GATE
Golden Gate Replicate	        : R11G
Application User		: CDS
Oracle Home			: /u01/app/oracle/product/11.2.0/dbhome_1
GoldenGate Home			: /u01/app/oracle/product/11gogg

2. GoldenGate Installation on Source Oracle 10gR2

http://www.br8dba.com/installation/

3. GoldenGate Installation on Target Oracle 11gR2

http://www.br8dba.com/installation/

4. Create CDS User (Application user) for testing

On Source (10gR2)

SQL> create user cds identified by cds default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to cds;

Grant succeeded.

SQL> create table CDS.A as select * from dba_objects;

Table created.

SQL> create table CDS.TEST(Name varchar2(10) PRIMARY KEY, Role varchar2(10));

Table created.

SQL>

On Target (11gR2)

SQL> create user cds identified by cds default tablespace users temporary tablespace temp;

User created.

SQL>

5. OGG Configuration on Source Oracle 10gR2

a) Set the environment

 . oraenv
ORACLE_SID = [DB10] ?

[oracle@rac1 ~]$ cat 10g.env
GG10=/u01/app/oracle/product/10gogg; export GG10
ORACLE_HOME=/u01/app/oracle/product/10g;  export ORACLE_HOME
ORACLE_SID=DB10 ; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/10gogg; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/10gogg; export LD_LIBRARY_PATH
alias ggsci='cd $GG10; ./ggsci'
[oracle@rac1 ~]$

[oracle@rac1 ~]$ . 10g.env

b) Login to Database

[oracle@rac1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Apr 23 2012 07:30:46

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

GGSCI (rac1.rajasekhar.com) 1> dblogin userid gate, password gate
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2>

c) Configure Manager

GGSCI (rac1.rajasekhar.com) 3> edit param mgr

-- Add below entries 

PORT 15500
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS /u01/app/oracle/product/10gogg/dirdat/*, USECHECKPOINTS

GGSCI (rac1.rajasekhar.com) 4> view param mgr

PORT 15500
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS /u01/app/oracle/product/10gogg/dirdat/*, USECHECKPOINTS

GGSCI (rac1.rajasekhar.com) 5>

d) Create Extract Group

GGSCI (rac1.rajasekhar.com) 5> ADD EXTRACT E10G, TRANLOG, BEGIN NOW
EXTRACT added.


GGSCI (rac1.rajasekhar.com) 6> ADD EXTTRAIL ./dirdat/ea, EXTRACT E10G, MEGABYTES 50
EXTTRAIL added.


GGSCI (rac1.rajasekhar.com) 7> edit param E10G

-- Add below entries

EXTRACT E10G
EXTTRAIL ./dirdat/ea
USERID gate, PASSWORD gate
TABLE CDS.*;

GGSCI (rac1.rajasekhar.com) 8> view param e10g

EXTRACT E10G
EXTTRAIL ./dirdat/ea
USERID gate, PASSWORD gate
TABLE CDS.*;

GGSCI (rac1.rajasekhar.com) 9>

e) Create Pump Group

GGSCI (rac1.rajasekhar.com) 9> ADD EXTRACT P10G, EXTTRAILSOURCE ./dirdat/ea
EXTRACT added.

GGSCI (rac1.rajasekhar.com) 10> ADD RMTTRAIL ./dirdat/pa, EXTRACT P10G, MEGABYTES 50
RMTTRAIL added.

GGSCI (rac1.rajasekhar.com) 11> edit param p10g

-- Add below entries

EXTRACT P10G
RMTHOST 192.168.2.101, MGRPORT 15000
USERID gate, password gate
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE CDS.*;

GGSCI (rac1.rajasekhar.com) 12> view param p10g

EXTRACT P10G
RMTHOST 192.168.2.101, MGRPORT 15000
USERID gate, password gate
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE CDS.*;

GGSCI (rac1.rajasekhar.com) 13>

f) Add supplemental logging

GGSCI (rac1.rajasekhar.com) 13> ADD TRANDATA CDS.*

2016-11-26 21:32:37  WARNING OGG-00869  No unique key is defined for table 'A'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table CDS.A.

Logging of supplemental redo data enabled for table CDS.TEST.

GGSCI (rac1.rajasekhar.com) 14>

6. OGG Configuration on Target Oracle 11gR2

a) Set the environment

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? DB11
[oracle@rac1 ~]$ 

[oracle@rac1 ~]$ cat 11g.env
GG11=/u01/app/oracle/product/11gogg; export GG11
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1;  export ORACLE_HOME
ORACLE_SID=DB11 ; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/11gogg; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/11gogg; export LD_LIBRARY_PATH
alias ggsci='cd $GG11; ./ggsci'
[oracle@rac1 ~]$

[oracle@rac1 ~]$ . 11g.env

b) Login to Database

[oracle@rac1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 11g on Feb  5 2013 08:11:55

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

GGSCI (rac1.rajasekhar.com) 1> dblogin userid gate, password gate
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2>

c) Configure Manager

GGSCI (rac1.rajasekhar.com) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED


GGSCI (rac1.rajasekhar.com) 3> edit param mgr

-- Add below entry 
PORT 15000
DYNAMICPORTLIST 15010-15020
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

GGSCI (rac1.rajasekhar.com) 4> view param mgr

PORT 15000
DYNAMICPORTLIST 15010-15020
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS


GGSCI (rac1.rajasekhar.com) 5>

d) Set up the Checkpoint table

i) Create a GLOBALS file

GGSCI (rac1.rajasekhar.com) 6> EDIT PARAMS ./GLOBALS

-- Add below entries
GGSCHEMA GATE
ENABLEMONITORING
CHECKPOINTTABLE GATE.CHECKPOINT

GGSCI (rac1.rajasekhar.com) 7> sh cat ./GLOBALS

GGSCHEMA GATE
ENABLEMONITORING
CHECKPOINTTABLE GATE.CHECKPOINT

GGSCI (rac1.rajasekhar.com) 8>

ii) Activate the GLOBALS parameters

For the GLOBALS configuration to take effect, you must exit the session in which the changes were made. Execute the following command to exit GGSCI.

GGSCI (rac1.rajasekhar.com) 8> exit
[oracle@rac1 11gogg]$

iii) Add a Replicat checkpoint table

[oracle@rac1 11gogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 11g on Feb  5 2013 08:11:55

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


GGSCI (rac1.rajasekhar.com) 1> dblogin userid gate, password gate
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2> ADD CHECKPOINTTABLE GATE.CHECKPOINT

Successfully created checkpoint table GATE.CHECKPOINT.

GGSCI (rac1.rajasekhar.com) 3>

7) Create the Replicat Group

GGSCI (rac1.rajasekhar.com) 4> ADD REPLICAT R11G, EXTTRAIL ./dirdat/pa,checkpointtable gate.checkpoint
REPLICAT added.

GGSCI (rac1.rajasekhar.com) 5> edit param r11g

-- Add below 
REPLICAT R11G
ASSUMETARGETDEFS
userid gate, password gate
DISCARDFILE ./dirout/receiver.dsc, purge
MAP CDS.*, TARGET CDS.*;

GGSCI (rac1.rajasekhar.com) 6> view param r11g

REPLICAT R11G
ASSUMETARGETDEFS
userid gate, password gate
DISCARDFILE ./dirout/receiver.dsc, purge
MAP CDS.*, TARGET CDS.*;

-- The ASSUMETARGETDEFS statement means that we are assuming both source and target table structure is same.

8. On Source Start Manager, Extract and Pump

GGSCI (rac1.rajasekhar.com) 14> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED 
EXTRACT     STOPPED     E10G        00:00:00      00:38:06
EXTRACT     STOPPED     P10G        00:00:00      00:32:34


GGSCI (rac1.rajasekhar.com) 15> start mgr

Manager started.


GGSCI (rac1.rajasekhar.com) 16> start extract e10g

Sending START request to MANAGER ...
EXTRACT E10G starting


GGSCI (rac1.rajasekhar.com) 17> start extract p10g

Sending START request to MANAGER ...
EXTRACT P10G starting


GGSCI (rac1.rajasekhar.com) 18> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E10G        00:00:00      00:00:03
EXTRACT     RUNNING     P10G        00:00:00      00:38:49


GGSCI (rac1.rajasekhar.com) 19>

9. On Target start the Manager ONLY

GGSCI (rac1.rajasekhar.com) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
JAGENT      STOPPED
REPLICAT    STOPPED     R11G        00:00:00      00:14:36


GGSCI (rac1.rajasekhar.com) 9> start mgr

Manager started.


GGSCI (rac1.rajasekhar.com) 10> info mgr

Manager is running (IP port rac1.rajasekhar.com.15000).


GGSCI (rac1.rajasekhar.com) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    STOPPED     R11G        00:00:00      00:15:11


GGSCI (rac1.rajasekhar.com) 12>

10. Export data from source Oracle 10gR2

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  468487  <--- Please note this

SQL>

SQL> grant execute on DBMS_FLASHBACK to CDS;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rac1 ~]$

[oracle@rac1 ~]$ expdp  \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR SCHEMAS=CDS dumpfile=CDS.dmp logfile=cds.log FLASHBACK_SCN=468487

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 26 November, 2016 22:28:56

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR SCHEMAS=CDS dumpfile=CDS.dmp logfile=cds.log FLASHBACK_SCN=468487
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "CDS"."A"                                   4.720 MB   50605 rows <---
. . exported "CDS"."TEST"                                    0 KB       0 rows <---
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/product/10g/admin/DB10/dpdump/CDS.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:29:10

[oracle@rac1 ~]$

11. Transfer the dumpfile to 11g DATA_PUMP_DIR

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10g/admin/DB10/dpdump/
[oracle@rac1 dpdump]$ cp CDS.dmp /u01/app/oracle/admin/DB11/dpdump/
[oracle@rac1 dpdump]$

12. Generate data on source Oracle 10gR2 (After Export)

SQL> conn cds/cds;
Connected.
SQL> insert into test values ('&a','&b');
Enter value for a: RAJ
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('RAJ','DBA')

1 row created.

SQL> /
Enter value for a: SUGI
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('SUGI','DBA')

1 row created.

SQL> /
Enter value for a: TEJU
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('TEJU','DBA')

1 row created.

SQL> /
Enter value for a: VIJAY
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('VIJAY','DBA')

1 row created.

SQL>
SQL> insert into test values ('&a','&b');
Enter value for a: SATYA
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('SATYA','DBA')

1 row created.

SQL> /
Enter value for a: MOHAN
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('MOHAN','DBA')

1 row created.

SQL> /
Enter value for a: AMITH
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('AMITH','DBA')

1 row created.

SQL> /
Enter value for a: PAVAN
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('PAVAN','DBA')

1 row created.

SQL> /
Enter value for a: SARAVANA
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('SARAVANA','DBA')

1 row created.

SQL> /
Enter value for a: PREM
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('PREM','DBA')

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
        10

SQL> select * from test;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA
SATYA      DBA
MOHAN      DBA
AMITH      DBA
PAVAN      DBA
SARAVANA   DBA
PREM       DBA

10 rows selected.

SQL>


GGSCI (rac1.rajasekhar.com) 19> STATS E10G, TOTAL

Sending STATS request to EXTRACT E10G ...

Start of Statistics at 2016-11-26 22:45:02.

Output to ./dirdat/ea:

Extracting from CDS.TEST to CDS.TEST:

*** Total statistics since 2016-11-26 22:38:48 ***
        Total inserts                                     10.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                  10.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 20>

13. Import Data and Start Replicat on Target Database 11gR2

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [DB11] ?
[oracle@rac1 ~]$

[oracle@rac1 ~]$ impdp  \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR SCHEMAS=CDS dumpfile=CDS.dmp logfile=imp_cds.log

Import: Release 11.2.0.4.0 - Production on Sat Nov 26 22:43:26 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR SCHEMAS=CDS dumpfile=CDS.dmp logfile=imp_cds.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CDS" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CDS"."A"                                   4.720 MB   50605 rows <-----
. . imported "CDS"."TEST"                                    0 KB       0 rows <----
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Sat Nov 26 22:43:36 2016 elapsed 0 00:00:07

[oracle@rac1 ~]$

SQL> conn cds/cds;
Connected.
SQL> select count(*) from test;

  COUNT(*)
----------
       0   <-----

SQL>

*** Note in GGSCI, the Oracle SCN number is AFTERCSN, not AFTERSCN.
*** Start the REPLICAT only after successful of import.

GGSCI (rac1.rajasekhar.com) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    STOPPED     R11G        00:00:00      00:15:11


GGSCI (rac1.rajasekhar.com) 12> start r11g, aftercsn 468487

Sending START request to MANAGER ...
REPLICAT R11G starting


GGSCI (rac1.rajasekhar.com) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    RUNNING     R11G        00:00:00      00:00:05


GGSCI (rac1.rajasekhar.com) 14>

GGSCI (rac1.rajasekhar.com) 14> STATS R11G, TOTAL

Sending STATS request to REPLICAT R11G ...

No active replication maps.


GGSCI (rac1.rajasekhar.com) 15> STATS R11G, TOTAL

Sending STATS request to REPLICAT R11G ...

Start of Statistics at 2016-11-26 22:52:00.

Replicating from CDS.TEST to CDS.TEST:

*** Total statistics since 2016-11-26 22:51:49 ***
        Total inserts                                     10.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                  10.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 16>


SQL> conn cds/cds;
Connected.
SQL> select count(*) from test;

  COUNT(*)
----------
        10 <----

SQL> select * from test;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA
SATYA      DBA
MOHAN      DBA
AMITH      DBA
PAVAN      DBA
SARAVANA   DBA
PREM       DBA

10 rows selected.

SQL>

14. Test and Verify the Results

INSERT OPERATION

a1) Execute INSERT again on the Source Oracle Database 10gR2

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
DB10      READ WRITE
SQL> conn cds/cds;
Connected.
SQL> insert into TEST values ('&a','&b');
Enter value for a: RAJKUMAR
Enter value for b: MANAGER
old   1: insert into TEST values ('&a','&b')
new   1: insert into TEST values ('RAJKUMAR','MANAGER')

1 row created.

SQL> /
Enter value for a: SATISH
Enter value for b: MANAGER
old   1: insert into TEST values ('&a','&b')
new   1: insert into TEST values ('SATISH','MANAGER')

1 row created.

SQL> insert into TEST values ('&a','&b');
Enter value for a: LOKESH
Enter value for b: DBA
old   1: insert into TEST values ('&a','&b')
new   1: insert into TEST values ('LOKESH','DBA')

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from test;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA
SATYA      DBA
MOHAN      DBA
AMITH      DBA
PAVAN      DBA
SARAVANA   DBA
PREM       DBA

NAME       ROLE
---------- ----------
RAJKUMAR   MANAGER <---
SATISH     MANAGER <---
LOKESH     DBA     <---

13 rows selected.

SQL>

a2) Verify Insert Changes on Target Oracle Database 11gR2

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DB11      READ WRITE
SQL> conn cds/cds;
Connected.
SQL> set pages 13
SQL> select * from test;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA
SATYA      DBA
MOHAN      DBA
AMITH      DBA
PAVAN      DBA
SARAVANA   DBA
PREM       DBA

NAME       ROLE
---------- ----------
RAJKUMAR   MANAGER
SATISH     MANAGER
LOKESH     DBA

13 rows selected.

SQL>

UPDATE OPERATION

b1) Execute update on the Source Oracle Database 10gR2

SQL> update test set role='PSE' where name='VIJAY';

1 row updated.

SQL> update test set role='PSE' where name='SATYA';

1 row updated.

SQL> update test set role='PSE' where name='PAVAN';

1 row updated.

SQL> commit;

Commit complete.

SQL> set pages 100
SQL> select * from test;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      PSE
SATYA      PSE
MOHAN      DBA
AMITH      DBA
PAVAN      PSE
SARAVANA   DBA
PREM       DBA
RAJKUMAR   MANAGER
SATISH     MANAGER
LOKESH     DBA

13 rows selected.

SQL>

b2) Verify Update Changes on Target Oracle Database 11gR2

SQL> select * from test;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      PSE
SATYA      PSE
MOHAN      DBA
AMITH      DBA
PAVAN      PSE
SARAVANA   DBA
PREM       DBA
RAJKUMAR   MANAGER
SATISH     MANAGER
LOKESH     DBA

13 rows selected. <------

SQL>

DELETE OPERATION

c1) Execute Delete on the Source Oracle Database 10gR2

SQL> delete from test where name='SUGI';

1 row deleted.

SQL> delete from test where name='TEJU';

1 row deleted.

SQL> delete from test where name='RAJ';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from test;

NAME       ROLE
---------- ----------
VIJAY      PSE
SATYA      PSE
MOHAN      DBA
AMITH      DBA
PAVAN      PSE
SARAVANA   DBA
PREM       DBA
RAJKUMAR   MANAGER
SATISH     MANAGER
LOKESH     DBA

10 rows selected. <--- 3 rows deleted

SQL>

c2) Verify Delete Changes on Target Oracle Database 11gR2

SQL> select * from test;

NAME       ROLE
---------- ----------
VIJAY      PSE
SATYA      PSE
MOHAN      DBA
AMITH      DBA
PAVAN      PSE
SARAVANA   DBA
PREM       DBA
RAJKUMAR   MANAGER
SATISH     MANAGER
LOKESH     DBA

10 rows selected.

SQL>

15. Verify using GGSCI

on Source Oracle 10gR2

GGSCI (rac1.rajasekhar.com) 26> STATS E10G, TOTAL

Sending STATS request to EXTRACT E10G ...

Start of Statistics at 2016-11-26 23:21:30.

Output to ./dirdat/ea:

Extracting from CDS.TEST to CDS.TEST:

*** Total statistics since 2016-11-26 22:38:48 ***
        Total inserts                                     13.00 <---
        Total updates                                      3.00 <---
        Total deletes                                      3.00 <---
        Total discards                                     0.00
        Total operations                                  19.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 27>

on Target Oracle 11gR2

GGSCI (rac1.rajasekhar.com) 16> STATS R11G, TOTAL

Sending STATS request to REPLICAT R11G ...

Start of Statistics at 2016-11-26 23:21:14.

Replicating from CDS.TEST to CDS.TEST:

*** Total statistics since 2016-11-26 22:51:49 ***
        Total inserts                                     13.00 <---
        Total updates                                      3.00 <---
        Total deletes                                      3.00 <---
        Total discards                                     0.00
        Total operations                                  19.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 17>

It’s working !!! 🙂

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

GoldenGate Installation

GoldenGategoden gate, golden gate installation, goldengate, goldengate installation

Installing Oracle GoldenGate V11.2.x for Oracle 10gR2 on Linux x86-64

Note: The below procedure is same for “Installing Oracle GoldenGate V11.2.x for Oracle 11gR2 on Linux x86-64”

Contents:
_________________________________________________________________________________________________________________

1. Download software
2. Unzip the software
3. Set Environmental Variables
4. Verify Oracle GoldenGate libraries are linked with the Oracle Libraries
5. Perform database prerequisites
6. Create the required working directories for GoldenGate
_________________________________________________________________________________________________________________

1. Download software

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html or
https://edelivery.oracle.com >> Select a Product Pack: Oracle Fusion Middleware

2. Unzip the software

cd /u01/app/oracle/product/gg10
unzip V32400-01.zip
Archive: V32400-01.zip
 inflating: fbo_ggs_Linux_x64_ora10g_64bit.tar
 inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
 inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
 inflating: Oracle GoldenGate 11.2.1.0.1 README.doc
tar -xvf fbo_ggs_Linux_x64_ora10g_64bit.tar

3. Set Environmental Variables

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@rac1 ~]$ export PATH=$PATH:/u01/app/oracle/product/gg10
[oracle@rac1 ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/gg10
[oracle@rac1 ~]$ env | grep ORACLE
ORACLE_SID=rcm
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@rac1 ~]$ env | grep LD_LIBR
LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/db_1/lib:/u01/app/oracle/product/gg10

4. Verify the Oracle GoldenGate libraries are linked with the Oracle Libraries

[oracle@rac1 ~]$ cd /u01/app/oracle/product/gg10
[oracle@rac1 gg10]$ ldd mgr
 linux-vdso.so.1 => (0x00007fff6a098000)
 libdl.so.2 => /lib64/libdl.so.2 (0x00000039b1400000)
 libgglog.so => /u01/app/oracle/product/gg10/libgglog.so (0x00007f869c8af000)
 libggrepo.so => /u01/app/oracle/product/gg10/libggrepo.so (0x00007f869c75b000)
 libdb-5.2.so => /u01/app/oracle/product/gg10/libdb-5.2.so (0x00007f869c4ba000)
 libicui18n.so.38 => /u01/app/oracle/product/gg10/libicui18n.so.38 (0x00007f869c159000)
 libicuuc.so.38 => /u01/app/oracle/product/gg10/libicuuc.so.38 (0x00007f869be20000)
 libicudata.so.38 => /u01/app/oracle/product/gg10/libicudata.so.38 (0x00007f869ae44000)
 libxerces-c.so.28 => /u01/app/oracle/product/gg10/libxerces-c.so.28 (0x00007f869a92c000)
 libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039b1800000)
 libantlr3c.so => /u01/app/oracle/product/gg10/libantlr3c.so (0x00007f869a816000)
 libnnz10.so => /u01/app/oracle/product/10.2.0/db_1/lib/libnnz10.so (0x00007f869a374000)
 libclntsh.so.10.1 => /u01/app/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1 (0x00007f8698e3a000)
 libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00000039c3200000)
 libm.so.6 => /lib64/libm.so.6 (0x00000039b1000000)
 libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00000039bee00000)
 libc.so.6 => /lib64/libc.so.6 (0x00000039b0c00000)
 /lib64/ld-linux-x86-64.so.2 (0x00000039b0800000)
 libnsl.so.1 => /lib64/libnsl.so.1 (0x00000039b5800000)
[oracle@rac1 gg10]$ ldd extract
 linux-vdso.so.1 => (0x00007fff955ff000)
 libdl.so.2 => /lib64/libdl.so.2 (0x00000039b1400000)
 libicui18n.so.38 => /u01/app/oracle/product/gg10/libicui18n.so.38 (0x00007fcd10e6c000)
 libicuuc.so.38 => /u01/app/oracle/product/gg10/libicuuc.so.38 (0x00007fcd10b33000)
 libicudata.so.38 => /u01/app/oracle/product/gg10/libicudata.so.38 (0x00007fcd0fb57000)
 libxerces-c.so.28 => /u01/app/oracle/product/gg10/libxerces-c.so.28 (0x00007fcd0f63f000)
 libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039b1800000)
 libantlr3c.so => /u01/app/oracle/product/gg10/libantlr3c.so (0x00007fcd0f529000)
 libgglog.so => /u01/app/oracle/product/gg10/libgglog.so (0x00007fcd0f2f0000)
 libggrepo.so => /u01/app/oracle/product/gg10/libggrepo.so (0x00007fcd0f19c000)
 libdb-5.2.so => /u01/app/oracle/product/gg10/libdb-5.2.so (0x00007fcd0eefb000)
 libnnz10.so => /u01/app/oracle/product/10.2.0/db_1/lib/libnnz10.so (0x00007fcd0ea59000)
 libclntsh.so.10.1 => /u01/app/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1 (0x00007fcd0d51f000)
 libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00000039c3200000)
 libm.so.6 => /lib64/libm.so.6 (0x00000039b1000000)
 libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00000039bee00000)
 libc.so.6 => /lib64/libc.so.6 (0x00000039b0c00000)
 /lib64/ld-linux-x86-64.so.2 (0x00000039b0800000)
 libnsl.so.1 => /lib64/libnsl.so.1 (0x00000039b5800000)
[oracle@rac1 gg10]$ ldd replicat
 linux-vdso.so.1 => (0x00007fff113f7000)
 libdl.so.2 => /lib64/libdl.so.2 (0x00000039b1400000)
 libgglog.so => /u01/app/oracle/product/gg10/libgglog.so (0x00007f41575cd000)
 libggrepo.so => /u01/app/oracle/product/gg10/libggrepo.so (0x00007f4157479000)
 libdb-5.2.so => /u01/app/oracle/product/gg10/libdb-5.2.so (0x00007f41571d8000)
 libicui18n.so.38 => /u01/app/oracle/product/gg10/libicui18n.so.38 (0x00007f4156e77000)
 libicuuc.so.38 => /u01/app/oracle/product/gg10/libicuuc.so.38 (0x00007f4156b3e000)
 libicudata.so.38 => /u01/app/oracle/product/gg10/libicudata.so.38 (0x00007f4155b62000)
 libxerces-c.so.28 => /u01/app/oracle/product/gg10/libxerces-c.so.28 (0x00007f415564a000)
 libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039b1800000)
 libantlr3c.so => /u01/app/oracle/product/gg10/libantlr3c.so (0x00007f4155534000)
 libnnz10.so => /u01/app/oracle/product/10.2.0/db_1/lib/libnnz10.so (0x00007f4155092000)
 libclntsh.so.10.1 => /u01/app/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1 (0x00007f4153b58000)
 libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00000039c3200000)
 libm.so.6 => /lib64/libm.so.6 (0x00000039b1000000)
 libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00000039bee00000)
 libc.so.6 => /lib64/libc.so.6 (0x00000039b0c00000)
 /lib64/ld-linux-x86-64.so.2 (0x00000039b0800000)
 libnsl.so.1 => /lib64/libnsl.so.1 (0x00000039b5800000)
[oracle@rac1 gg10]$ ldd ggsci
 linux-vdso.so.1 => (0x00007fff4235e000)
 libdl.so.2 => /lib64/libdl.so.2 (0x00000039b1400000)
 libgglog.so => /u01/app/oracle/product/gg10/libgglog.so (0x00007fbb96a78000)
 libggrepo.so => /u01/app/oracle/product/gg10/libggrepo.so (0x00007fbb96924000)
 libdb-5.2.so => /u01/app/oracle/product/gg10/libdb-5.2.so (0x00007fbb96683000)
 libicui18n.so.38 => /u01/app/oracle/product/gg10/libicui18n.so.38 (0x00007fbb96322000)
 libicuuc.so.38 => /u01/app/oracle/product/gg10/libicuuc.so.38 (0x00007fbb95fe9000)
 libicudata.so.38 => /u01/app/oracle/product/gg10/libicudata.so.38 (0x00007fbb9500d000)
 libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039b1800000)
 libxerces-c.so.28 => /u01/app/oracle/product/gg10/libxerces-c.so.28 (0x00007fbb94af5000)
 libantlr3c.so => /u01/app/oracle/product/gg10/libantlr3c.so (0x00007fbb949df000)
 libnnz10.so => /u01/app/oracle/product/10.2.0/db_1/lib/libnnz10.so (0x00007fbb9453d000)
 libclntsh.so.10.1 => /u01/app/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1 (0x00007fbb93003000)
 libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00000039c3200000)
 libm.so.6 => /lib64/libm.so.6 (0x00000039b1000000)
 libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00000039bee00000)
 libc.so.6 => /lib64/libc.so.6 (0x00000039b0c00000)
 /lib64/ld-linux-x86-64.so.2 (0x00000039b0800000)
 libnsl.so.1 => /lib64/libnsl.so.1 (0x00000039b5800000)
[oracle@rac1 gg10]$

5. Perform database prerequisites

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+VOL/rcm/users01.dbf
+VOL/rcm/sysaux01.dbf
+VOL/rcm/undotbs01.dbf
+VOL/rcm/system01.dbf
SQL>

SQL> CREATE TABLESPACE GATE DATAFILE '+VOL' SIZE 100M AUTOEXTEND ON;

Tablespace created.

SQL> CREATE USER GATE IDENTIFIED BY gate DEFAULT TABLESPACE GATE TEMPORARY TABLESPACE TEMP;

User created.

SQL> GRANT CONNECT TO GATE;

Grant succeeded.

SQL> GRANT DBA TO GATE;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO GATE;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO GATE;

Grant succeeded.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES

SQL>

6. Create the required working directories for GoldenGate

cd /u01/app/oracle/product/gg10
[oracle@rac1 gg10]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Apr 23 2012 07:30:46

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



GGSCI (rac1.rajasekhar.com) 1> CREATE SUBDIRS

Creating subdirectories under current directory /u01/app/oracle/product/gg10

Parameter files /u01/app/oracle/product/gg10/dirprm: already exists
Report files /u01/app/oracle/product/gg10/dirrpt: created
Checkpoint files /u01/app/oracle/product/gg10/dirchk: created
Process status files /u01/app/oracle/product/gg10/dirpcs: created
SQL script files /u01/app/oracle/product/gg10/dirsql: created
Database definitions files /u01/app/oracle/product/gg10/dirdef: created
Extract data files /u01/app/oracle/product/gg10/dirdat: created
Temporary files /u01/app/oracle/product/gg10/dirtmp: created
Stdout files /u01/app/oracle/product/gg10/dirout: created


GGSCI (rac1.rajasekhar.com) 2>

GoldenGate is installed 🙂

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Bright DBA

ORACLE DBA – Tips and Techniques

Tag Archives: goldengate

ADD NEW TABLE TO AN EXISTING GOLDENGATE CONFIGURATION

ADD NEW TABLE TO AN EXISTING EXTRACT, PUMP AND REPLICAT

Zero Downtime Migration

AIM: Zero Downtime Migration with one-way replication between an Oracle 10gR2 database and an Oracle 11gR2 database on same server.

GoldenGate Installation

Installing Oracle GoldenGate V11.2.x for Oracle 10gR2 on Linux x86-64