Category Archives: GoldenGate

Install Oracle GoldenGate 19C for Database 19C

Install Oracle GoldenGate 19.1.0.0.4 for Oracle Database 19c on Linux 7

Table of Contents
___________________________________________________________________________________________________

1. Overview
2. Environment
3. Verify Certification
4. Download software
5. Unzip the software
6. Installation
7. Verify Central Inventory
8. Set Environmental Variables
9. Verify Oracle GoldenGate libraries are linked with the Oracle Libraries
10. Create the required working directories for GoldenGate

___________________________________________________________________________________________________


1. Overview

An instance of Oracle GoldenGate can be installed for only one major Oracle Database version in any given Oracle home. For example, if you have Oracle Database 11.2 and 12.1, you must have separate Oracle GoldenGate installations for each one.

(Optional) Select Start Manager to perform configuration functions, such as creating the Oracle GoldenGate sub directories in the installation location, setting library paths, and starting Manager on the specified port number. To proceed, a database must exist on the system. When Start Manager is selected, the Database Location and Manager Port fields are displayed.

The default port is the first available one starting with 7809. If you are installing multiple instances of Oracle GoldenGate on the same system, each must use a different port number.


2. Environment

Server Name		: RAC2
OS Version		: Red Hat Enterprise Linux Server release 7.5
Database Version	: 19c
GoldenGate Version	: 19.1.0.0.4 for oracle db 19c
Oracle Home		: /u01/app/oracle/product/19.0.0/dbhome_1
GoldenGate Home		: /u01/app/oracle/product/19cogg


3. Verify Certification


4. Download software

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
 
(OR)

https://edelivery.oracle.com >> Select a Product Pack: Oracle Fusion Middleware


5. Unzip the software

[oracle@rac2 GG19]$ pwd
/u01/app/backup/GG19
[oracle@rac2 GG19]$ unzip 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@rac2 GG19]$ ls -ltr
total 543540
-rw-r--r-- 1 oracle oinstall      1413 May 29  2019 OGG-19.1.0.0-README.txt
drwxr-xr-x 3 oracle oinstall        19 Oct 18 21:20 fbo_ggs_Linux_x64_shiphome
-rw-r--r-- 1 oracle oinstall    332523 Oct 21 11:01 OGG_WinUnix_Rel_Notes_19.1.0.0.4.pdf
-rw-r--r-- 1 oracle oinstall 556240981 Feb 11 21:22 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@rac2 GG19]$

[oracle@rac2 GG19]$ cd fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@rac2 Disk1]$ ls -ltr
total 12
drwxr-xr-x  4 oracle oinstall 4096 Oct 18 21:20 install
drwxr-xr-x 12 oracle oinstall 4096 Oct 18 21:20 stage
-rwxr-xr-x  1 oracle oinstall  918 Oct 18 21:20 runInstaller
drwxrwxr-x  2 oracle oinstall   25 Oct 18 21:20 response
[oracle@rac2 Disk1]$


6. Installation

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/product/19cogg

[oracle@rac2 Disk1]$ pwd
/u01/app/backup/GG19/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@rac2 Disk1]$ export DISPLAY=192.168.2.2:0.0
[oracle@rac2 Disk1]$ ls -ltr
total 12
drwxr-xr-x  4 oracle oinstall 4096 Oct 18 21:20 install
drwxr-xr-x 12 oracle oinstall 4096 Oct 18 21:20 stage
-rwxr-xr-x  1 oracle oinstall  918 Oct 18 21:20 runInstaller <-----
drwxrwxr-x  2 oracle oinstall   25 Oct 18 21:20 response
[oracle@rac2 Disk1]$
[oracle@rac2 Disk1]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 4044 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4095 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-02-16_03-29-08AM. Please wait ...











[oracle@rac2 Disk1]$ You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2020-02-16_03-29-08AM.log


7. Verify Central Inventory

[oracle@rac2 ~]$ cd /u01/app/oraInventory/ContentsXML
[oracle@rac2 ContentsXML]$ cat inventory.xml | grep -i "/u01/app/oracle/product/19cogg"
<HOME NAME="OraHome1" LOC="/u01/app/oracle/product/19cogg" TYPE="O" IDX="5"/>
[oracle@rac2 ContentsXML]$


8. Set Environmental Variables

[oracle@rac2 ~]$ cat 19cogg.env
GG19C=/u01/app/oracle/product/19cogg; export GG19C
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1;  export ORACLE_HOME
ORACLE_SID=MAVIC12; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/19cogg; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/19cogg; export LD_LIBRARY_PATH
alias ggsci='cd $GG19C; ./ggsci'
[oracle@rac2 ~]$


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

[oracle@rac2 ~]$ . 19cogg.env
[oracle@rac2 ~]$ env | grep ORACLE
ORACLE_SID=MAVIC12
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@rac2 ~]$
[oracle@rac2 ~]$ env | grep LD_LIBR
LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/dbhome_1/lib:/u01/app/oracle/product/19cogg
[oracle@rac2 ~]$
[oracle@rac2 ~]$ cd /u01/app/oracle/product/19cogg
[oracle@rac2 19cogg]$
[oracle@rac2 19cogg]$ ldd mgr
        linux-vdso.so.1 =>  (0x00007fffe37fa000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f3b96a4d000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f3b96848000)
        libgglog.so => /u01/app/oracle/product/19cogg/./libgglog.so (0x00007f3b964e2000)
        libggutil.so => /u01/app/oracle/product/19cogg/./libggutil.so (0x00007f3b962c6000)
        libggrepo.so => /u01/app/oracle/product/19cogg/./libggrepo.so (0x00007f3b9600b000)
        libdb-6.1.so => /u01/app/oracle/product/19cogg/./libdb-6.1.so (0x00007f3b95bf5000)
        liblmdb.so => /u01/app/oracle/product/19cogg/./liblmdb.so (0x00007f3b959e0000)
        libggperf.so => /u01/app/oracle/product/19cogg/./libggperf.so (0x00007f3b957ad000)
        libggparam.so => /u01/app/oracle/product/19cogg/./libggparam.so (0x00007f3b94094000)
        libicui18n.so.62 => /u01/app/oracle/product/19cogg/./libicui18n.so.62 (0x00007f3b93baf000)
        libicuuc.so.62 => /u01/app/oracle/product/19cogg/./libicuuc.so.62 (0x00007f3b937ad000)
        libicudata.so.62 => /u01/app/oracle/product/19cogg/./libicudata.so.62 (0x00007f3b91795000)
        libxerces-c-3.2.so => /u01/app/oracle/product/19cogg/./libxerces-c-3.2.so (0x00007f3b91121000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f3b90f04000)
        libantlr3c.so => /u01/app/oracle/product/19cogg/./libantlr3c.so (0x00007f3b90ce9000)
        libnnz19.so => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libnnz19.so (0x00007f3b905a1000)
        libclntsh.so.19.1 => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libclntsh.so.19.1 (0x00007f3b8c57d000)
        libclntshcore.so.19.1 => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libclntshcore.so.19.1 (0x00007f3b8bfdd000)
        libggnnzitp.so => /u01/app/oracle/product/19cogg/./libggnnzitp.so (0x00007f3b8b4c6000)
        libstdc++.so.6 => /u01/app/oracle/product/19cogg/./libstdc++.so.6 (0x00007f3b8b12e000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f3b8ae2c000)
        libgcc_s.so.1 => /u01/app/oracle/product/19cogg/./libgcc_s.so.1 (0x00007f3b8ac16000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f3b8a848000)
        /lib64/ld-linux-x86-64.so.2 (0x000055ea0224e000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f3b8a62e000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f3b8a42b000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f3b8a212000)
[oracle@rac2 19cogg]$
[oracle@rac2 19cogg]$ ldd extract
        linux-vdso.so.1 =>  (0x00007ffc1cf96000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f520828e000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f5208089000)
        libgglog.so => /u01/app/oracle/product/19cogg/./libgglog.so (0x00007f5207d23000)
        libggutil.so => /u01/app/oracle/product/19cogg/./libggutil.so (0x00007f5207b07000)
        libggrepo.so => /u01/app/oracle/product/19cogg/./libggrepo.so (0x00007f520784c000)
        libdb-6.1.so => /u01/app/oracle/product/19cogg/./libdb-6.1.so (0x00007f5207436000)
        liblmdb.so => /u01/app/oracle/product/19cogg/./liblmdb.so (0x00007f5207221000)
        libggperf.so => /u01/app/oracle/product/19cogg/./libggperf.so (0x00007f5206fee000)
        libggparam.so => /u01/app/oracle/product/19cogg/./libggparam.so (0x00007f52058d5000)
        libicui18n.so.62 => /u01/app/oracle/product/19cogg/./libicui18n.so.62 (0x00007f52053f0000)
        libicuuc.so.62 => /u01/app/oracle/product/19cogg/./libicuuc.so.62 (0x00007f5204fee000)
        libicudata.so.62 => /u01/app/oracle/product/19cogg/./libicudata.so.62 (0x00007f5202fd6000)
        libxerces-c-3.2.so => /u01/app/oracle/product/19cogg/./libxerces-c-3.2.so (0x00007f5202962000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f5202745000)
        libantlr3c.so => /u01/app/oracle/product/19cogg/./libantlr3c.so (0x00007f520252a000)
        libnnz19.so => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libnnz19.so (0x00007f5201de2000)
        libclntsh.so.19.1 => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libclntsh.so.19.1 (0x00007f51fddbe000)
        libclntshcore.so.19.1 => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libclntshcore.so.19.1 (0x00007f51fd81e000)
        libggnnzitp.so => /u01/app/oracle/product/19cogg/./libggnnzitp.so (0x00007f51fcd07000)
        libstdc++.so.6 => /u01/app/oracle/product/19cogg/./libstdc++.so.6 (0x00007f51fc96f000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f51fc66d000)
        libgcc_s.so.1 => /u01/app/oracle/product/19cogg/./libgcc_s.so.1 (0x00007f51fc457000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f51fc089000)
        /lib64/ld-linux-x86-64.so.2 (0x00005600bebc9000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f51fbe6f000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f51fbc6c000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f51fba53000)
[oracle@rac2 19cogg]$
[oracle@rac2 19cogg]$ ldd replicat
        linux-vdso.so.1 =>  (0x00007ffe6ad7f000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f1649396000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f1649191000)
        libgglog.so => /u01/app/oracle/product/19cogg/./libgglog.so (0x00007f1648e2b000)
        libggutil.so => /u01/app/oracle/product/19cogg/./libggutil.so (0x00007f1648c0f000)
        libggrepo.so => /u01/app/oracle/product/19cogg/./libggrepo.so (0x00007f1648954000)
        libdb-6.1.so => /u01/app/oracle/product/19cogg/./libdb-6.1.so (0x00007f164853e000)
        liblmdb.so => /u01/app/oracle/product/19cogg/./liblmdb.so (0x00007f1648329000)
        libggperf.so => /u01/app/oracle/product/19cogg/./libggperf.so (0x00007f16480f6000)
        libggparam.so => /u01/app/oracle/product/19cogg/./libggparam.so (0x00007f16469dd000)
        libicui18n.so.62 => /u01/app/oracle/product/19cogg/./libicui18n.so.62 (0x00007f16464f8000)
        libicuuc.so.62 => /u01/app/oracle/product/19cogg/./libicuuc.so.62 (0x00007f16460f6000)
        libicudata.so.62 => /u01/app/oracle/product/19cogg/./libicudata.so.62 (0x00007f16440de000)
        libxerces-c-3.2.so => /u01/app/oracle/product/19cogg/./libxerces-c-3.2.so (0x00007f1643a6a000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f164384d000)
        libantlr3c.so => /u01/app/oracle/product/19cogg/./libantlr3c.so (0x00007f1643632000)
        libnnz19.so => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libnnz19.so (0x00007f1642eea000)
        libclntsh.so.19.1 => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libclntsh.so.19.1 (0x00007f163eec6000)
        libclntshcore.so.19.1 => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libclntshcore.so.19.1 (0x00007f163e926000)
        libggnnzitp.so => /u01/app/oracle/product/19cogg/./libggnnzitp.so (0x00007f163de0f000)
        libstdc++.so.6 => /u01/app/oracle/product/19cogg/./libstdc++.so.6 (0x00007f163da77000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f163d775000)
        libgcc_s.so.1 => /u01/app/oracle/product/19cogg/./libgcc_s.so.1 (0x00007f163d55f000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f163d191000)
        /lib64/ld-linux-x86-64.so.2 (0x00005634bbe76000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f163cf77000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f163cd74000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f163cb5b000)
[oracle@rac2 19cogg]$
[oracle@rac2 19cogg]$ ldd ggsci
        linux-vdso.so.1 =>  (0x00007fffdb8df000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f73a1461000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f73a125c000)
        libgglog.so => /u01/app/oracle/product/19cogg/./libgglog.so (0x00007f73a0ef6000)
        libggutil.so => /u01/app/oracle/product/19cogg/./libggutil.so (0x00007f73a0cda000)
        libggrepo.so => /u01/app/oracle/product/19cogg/./libggrepo.so (0x00007f73a0a1f000)
        libdb-6.1.so => /u01/app/oracle/product/19cogg/./libdb-6.1.so (0x00007f73a0609000)
        liblmdb.so => /u01/app/oracle/product/19cogg/./liblmdb.so (0x00007f73a03f4000)
        libggperf.so => /u01/app/oracle/product/19cogg/./libggperf.so (0x00007f73a01c1000)
        libggparam.so => /u01/app/oracle/product/19cogg/./libggparam.so (0x00007f739eaa8000)
        libicui18n.so.62 => /u01/app/oracle/product/19cogg/./libicui18n.so.62 (0x00007f739e5c3000)
        libicuuc.so.62 => /u01/app/oracle/product/19cogg/./libicuuc.so.62 (0x00007f739e1c1000)
        libicudata.so.62 => /u01/app/oracle/product/19cogg/./libicudata.so.62 (0x00007f739c1a9000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f739bf8d000)
        libxerces-c-3.2.so => /u01/app/oracle/product/19cogg/./libxerces-c-3.2.so (0x00007f739b918000)
        libantlr3c.so => /u01/app/oracle/product/19cogg/./libantlr3c.so (0x00007f739b6fd000)
        libnnz19.so => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libnnz19.so (0x00007f739afb5000)
        libclntsh.so.19.1 => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libclntsh.so.19.1 (0x00007f7396f91000)
        libclntshcore.so.19.1 => /u01/app/oracle/product/19.0.0/dbhome_1/lib/libclntshcore.so.19.1 (0x00007f73969f1000)
        libggnnzitp.so => /u01/app/oracle/product/19cogg/./libggnnzitp.so (0x00007f7395eda000)
        libstdc++.so.6 => /u01/app/oracle/product/19cogg/./libstdc++.so.6 (0x00007f7395b42000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f7395840000)
        libgcc_s.so.1 => /u01/app/oracle/product/19cogg/./libgcc_s.so.1 (0x00007f739562a000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f739525c000)
        /lib64/ld-linux-x86-64.so.2 (0x000055f6fb3e5000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f7395041000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f7394e3f000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f7394c26000)
[oracle@rac2 19cogg]$

Make sure no missing links to $ORACLE_HOME/lib.


10. Create the required working directories for GoldenGate

[oracle@rac2 ~]$ . 19cogg.env
[oracle@rac2 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.

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



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

Creating subdirectories under current directory /u01/app/oracle/product/19cogg

Parameter file                 /u01/app/oracle/product/19cogg/dirprm: created.
Report file                    /u01/app/oracle/product/19cogg/dirrpt: created.
Checkpoint file                /u01/app/oracle/product/19cogg/dirchk: created.
Process status files           /u01/app/oracle/product/19cogg/dirpcs: created.
SQL script files               /u01/app/oracle/product/19cogg/dirsql: created.
Database definitions files     /u01/app/oracle/product/19cogg/dirdef: created.
Extract data files             /u01/app/oracle/product/19cogg/dirdat: created.
Temporary files                /u01/app/oracle/product/19cogg/dirtmp: created.
Credential store files         /u01/app/oracle/product/19cogg/dircrd: created.
Masterkey wallet files         /u01/app/oracle/product/19cogg/dirwlt: created.
Dump files                     /u01/app/oracle/product/19cogg/dirdmp: created.


GGSCI (rac2.rajasekhar.com) 2>

Congratulations, Oracle GoldenGate 19c 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.

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

Reference:

https://docs.oracle.com/en/middleware/goldengate/core/19.1/installing/installing-oracle-goldengate.html

GG 19C

Table of Contents
________________________________________________________________________________________________

1. Installing Oracle GoldenGate 19.1.0.0 for Oracle 12c on Linux 7

2. Installing Oracle GoldenGate 19.1.0.0 for Oracle 19c on Linux 7

________________________________________________________________________________________________

 

Install Oracle GoldenGate 19c

Installing Oracle GoldenGate 19.1.0.0 for Oracle DB 12c on Linux 7

Table of Contents
___________________________________________________________________________________________________

1. Overview
2. Environment
3. Verify Certification
4. Download software
5. Unzip the software
6. Installation
7. Verify Central Inventory
8. Set Environmental Variables
9. Verify Oracle GoldenGate libraries are linked with the Oracle Libraries
10. Create the required working directories for GoldenGate

___________________________________________________________________________________________________


1. Overview

An instance of Oracle GoldenGate can be installed for only one major Oracle Database version in any given Oracle home. For example, if you have Oracle Database 11.2 and 12.1, you must have separate Oracle GoldenGate installations for each one.

(Optional) Select Start Manager to perform configuration functions, such as creating the Oracle GoldenGate subdirectories in the installation location, setting library paths, and starting Manager on the specified port number. To proceed, a database must exist on the system. When Start Manager is selected, the Database Location and Manager Port fields are displayed.

The default port is the first available one starting with 7809. If you are installing multiple instances of Oracle GoldenGate on the same system, each must use a different port number.


2. Environment

Server Name		: RAC1
OS Version		: Red Hat Enterprise Linux Server release 7.5
Database Version	: 12.2.0.1
GoldenGate Version	: 19.1.0.0.4 for oracle db 12.2.0.1
Oracle Home		: /u01/app/oracle/product/12.2.0/dbhome_1
GoldenGate Home		: /u01/app/oracle/product/12cogg


3. Verify Certification


4. Download software

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


5. Unzip the software

[oracle@rac1 GG]$ pwd
/u01/app/backup/GG
[oracle@rac1 GG]$
[oracle@rac1 GG]$ unzip 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@rac1 GG]$ ls -ltr
total 543540
-rw-r--r--. 1 oracle oinstall      1413 May 29  2019 OGG-19.1.0.0-README.txt
drwxr-xr-x. 3 oracle oinstall        19 Oct 18 21:20 fbo_ggs_Linux_x64_shiphome
-rw-r--r--. 1 oracle oinstall    332523 Oct 21 11:01 OGG_WinUnix_Rel_Notes_19.1.0.0.4.pdf
-rw-r--r--. 1 oracle oinstall 556240981 Feb 11 21:22 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@rac1 GG]$ 

[oracle@rac1 GG]$ cd fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@rac1 Disk1]$ ls -ltr
total 12
drwxr-xr-x.  4 oracle oinstall 4096 Oct 18 21:20 install
drwxr-xr-x. 12 oracle oinstall 4096 Oct 18 21:20 stage
-rwxr-xr-x.  1 oracle oinstall  918 Oct 18 21:20 runInstaller
drwxrwxr-x.  2 oracle oinstall   25 Oct 18 21:20 response
[oracle@rac1 Disk1]$


6. Installation

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/product/12cogg

[oracle@rac1 Disk1]$ pwd
/u01/app/backup/GG/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@rac1 Disk1]$ export DISPLAY=192.168.2.2:0.0
[oracle@rac1 Disk1]$ ls -ltr
total 12
drwxr-xr-x.  4 oracle oinstall 4096 Oct 18 21:20 install
drwxr-xr-x. 12 oracle oinstall 4096 Oct 18 21:20 stage
-rwxr-xr-x.  1 oracle oinstall  918 Oct 18 21:20 runInstaller <----
drwxrwxr-x.  2 oracle oinstall   25 Oct 18 21:20 response
[oracle@rac1 Disk1]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 4047 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4095 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-02-12_12-58-11AM. Please wait ...[oracle@rac1 Disk1]$











[oracle@rac1 Disk1]$ You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2020-02-12_12-58-11AM.log


7. Verify Central Inventory

[oracle@rac1 ContentsXML]$ pwd
/u01/app/oraInventory/ContentsXML
[oracle@rac1 ContentsXML]$ cat inventory.xml | grep -i "/u01/app/oracle/product/12cogg"
<HOME NAME="OraHome1" LOC="/u01/app/oracle/product/12cogg" TYPE="O" IDX="6"/>
[oracle@rac1 ContentsXML]$


8. Set Environmental Variables

[oracle@rac1 ~]$ cat 12cogg.env
GG12C=/u01/app/oracle/product/12cogg; export GG12C
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1;  export ORACLE_HOME
ORACLE_SID=MAVIC12; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/12cogg; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/12cogg; export LD_LIBRARY_PATH
alias ggsci='cd $GG12C; ./ggsci'
[oracle@rac1 ~]$


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

[oracle@rac1 ~]$ . 12cogg.env
[oracle@rac1 ~]$ env | grep ORACLE
ORACLE_SID=MAVIC12
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
[oracle@rac1 ~]$

[oracle@rac1 ~]$ env | grep LD_LIBR
LD_LIBRARY_PATH=/u01/app/oracle/product/12.2.0/dbhome_1/lib:/u01/app/oracle/product/12cogg
[oracle@rac1 ~]$

[oracle@rac1 ~]$ cd /u01/app/oracle/product/12cogg
[oracle@rac1 12cogg]$
[oracle@rac1 12cogg]$ ldd mgr
        linux-vdso.so.1 =>  (0x00007ffde853a000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f3e7c71f000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f3e7c51a000)
        libgglog.so => /u01/app/oracle/product/12cogg/./libgglog.so (0x00007f3e7c1b4000)
        libggutil.so => /u01/app/oracle/product/12cogg/./libggutil.so (0x00007f3e7bf98000)
        libggrepo.so => /u01/app/oracle/product/12cogg/./libggrepo.so (0x00007f3e7bcdd000)
        libdb-6.1.so => /u01/app/oracle/product/12cogg/./libdb-6.1.so (0x00007f3e7b8c7000)
        liblmdb.so => /u01/app/oracle/product/12cogg/./liblmdb.so (0x00007f3e7b6b2000)
        libggperf.so => /u01/app/oracle/product/12cogg/./libggperf.so (0x00007f3e7b47f000)
        libggparam.so => /u01/app/oracle/product/12cogg/./libggparam.so (0x00007f3e79d66000)
        libicui18n.so.62 => /u01/app/oracle/product/12cogg/./libicui18n.so.62 (0x00007f3e79881000)
        libicuuc.so.62 => /u01/app/oracle/product/12cogg/./libicuuc.so.62 (0x00007f3e7947f000)
        libicudata.so.62 => /u01/app/oracle/product/12cogg/./libicudata.so.62 (0x00007f3e77467000)
        libxerces-c-3.2.so => /u01/app/oracle/product/12cogg/./libxerces-c-3.2.so (0x00007f3e76df3000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f3e76bd6000)
        libantlr3c.so => /u01/app/oracle/product/12cogg/./libantlr3c.so (0x00007f3e769bb000)
        libnnz12.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so (0x00007f3e76272000)
        libclntsh.so.12.1 => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1 (0x00007f3e727cc000)
        libons.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libons.so (0x00007f3e7257e000)
        libclntshcore.so.12.1 => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntshcore.so.12.1 (0x00007f3e71fb0000)
        libggnnzitp.so => /u01/app/oracle/product/12cogg/./libggnnzitp.so (0x00007f3e714dd000)
        libstdc++.so.6 => /u01/app/oracle/product/12cogg/./libstdc++.so.6 (0x00007f3e71146000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f3e70e44000)
        libgcc_s.so.1 => /u01/app/oracle/product/12cogg/./libgcc_s.so.1 (0x00007f3e70c2d000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f3e70860000)
        /lib64/ld-linux-x86-64.so.2 (0x000055fd1da0b000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f3e70645000)
        libmql1.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libmql1.so (0x00007f3e703ce000)
        libipc1.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libipc1.so (0x00007f3e6ff9a000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f3e6fd98000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f3e6fb7f000)
[oracle@rac1 12cogg]$

[oracle@rac1 12cogg]$ ldd extract
        linux-vdso.so.1 =>  (0x00007ffdad253000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f6a88b86000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f6a88981000)
        libgglog.so => /u01/app/oracle/product/12cogg/./libgglog.so (0x00007f6a8861b000)
        libggutil.so => /u01/app/oracle/product/12cogg/./libggutil.so (0x00007f6a883ff000)
        libggrepo.so => /u01/app/oracle/product/12cogg/./libggrepo.so (0x00007f6a88144000)
        libdb-6.1.so => /u01/app/oracle/product/12cogg/./libdb-6.1.so (0x00007f6a87d2e000)
        liblmdb.so => /u01/app/oracle/product/12cogg/./liblmdb.so (0x00007f6a87b19000)
        libggperf.so => /u01/app/oracle/product/12cogg/./libggperf.so (0x00007f6a878e6000)
        libggparam.so => /u01/app/oracle/product/12cogg/./libggparam.so (0x00007f6a861cd000)
        libicui18n.so.62 => /u01/app/oracle/product/12cogg/./libicui18n.so.62 (0x00007f6a85ce8000)
        libicuuc.so.62 => /u01/app/oracle/product/12cogg/./libicuuc.so.62 (0x00007f6a858e6000)
        libicudata.so.62 => /u01/app/oracle/product/12cogg/./libicudata.so.62 (0x00007f6a838ce000)
        libxerces-c-3.2.so => /u01/app/oracle/product/12cogg/./libxerces-c-3.2.so (0x00007f6a8325a000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f6a8303d000)
        libantlr3c.so => /u01/app/oracle/product/12cogg/./libantlr3c.so (0x00007f6a82e22000)
        libnnz12.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so (0x00007f6a826d9000)
        libclntsh.so.12.1 => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1 (0x00007f6a7ec33000)
        libons.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libons.so (0x00007f6a7e9e5000)
        libclntshcore.so.12.1 => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntshcore.so.12.1 (0x00007f6a7e417000)
        libggnnzitp.so => /u01/app/oracle/product/12cogg/./libggnnzitp.so (0x00007f6a7d944000)
        libstdc++.so.6 => /u01/app/oracle/product/12cogg/./libstdc++.so.6 (0x00007f6a7d5ad000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f6a7d2ab000)
        libgcc_s.so.1 => /u01/app/oracle/product/12cogg/./libgcc_s.so.1 (0x00007f6a7d094000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f6a7ccc7000)
        /lib64/ld-linux-x86-64.so.2 (0x000055e7e6189000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f6a7caac000)
        libmql1.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libmql1.so (0x00007f6a7c835000)
        libipc1.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libipc1.so (0x00007f6a7c401000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f6a7c1ff000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f6a7bfe6000)
[oracle@rac1 12cogg]$

[oracle@rac1 12cogg]$ ldd replicat
        linux-vdso.so.1 =>  (0x00007ffeca88d000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fa8bfba9000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fa8bf9a4000)
        libgglog.so => /u01/app/oracle/product/12cogg/./libgglog.so (0x00007fa8bf63e000)
        libggutil.so => /u01/app/oracle/product/12cogg/./libggutil.so (0x00007fa8bf422000)
        libggrepo.so => /u01/app/oracle/product/12cogg/./libggrepo.so (0x00007fa8bf167000)
        libdb-6.1.so => /u01/app/oracle/product/12cogg/./libdb-6.1.so (0x00007fa8bed51000)
        liblmdb.so => /u01/app/oracle/product/12cogg/./liblmdb.so (0x00007fa8beb3c000)
        libggperf.so => /u01/app/oracle/product/12cogg/./libggperf.so (0x00007fa8be909000)
        libggparam.so => /u01/app/oracle/product/12cogg/./libggparam.so (0x00007fa8bd1f0000)
        libicui18n.so.62 => /u01/app/oracle/product/12cogg/./libicui18n.so.62 (0x00007fa8bcd0b000)
        libicuuc.so.62 => /u01/app/oracle/product/12cogg/./libicuuc.so.62 (0x00007fa8bc909000)
        libicudata.so.62 => /u01/app/oracle/product/12cogg/./libicudata.so.62 (0x00007fa8ba8f1000)
        libxerces-c-3.2.so => /u01/app/oracle/product/12cogg/./libxerces-c-3.2.so (0x00007fa8ba27d000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fa8ba060000)
        libantlr3c.so => /u01/app/oracle/product/12cogg/./libantlr3c.so (0x00007fa8b9e45000)
        libnnz12.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so (0x00007fa8b96fc000)
        libclntsh.so.12.1 => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1 (0x00007fa8b5c56000)
        libons.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libons.so (0x00007fa8b5a08000)
        libclntshcore.so.12.1 => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntshcore.so.12.1 (0x00007fa8b543a000)
        libggnnzitp.so => /u01/app/oracle/product/12cogg/./libggnnzitp.so (0x00007fa8b4967000)
        libstdc++.so.6 => /u01/app/oracle/product/12cogg/./libstdc++.so.6 (0x00007fa8b45d0000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fa8b42ce000)
        libgcc_s.so.1 => /u01/app/oracle/product/12cogg/./libgcc_s.so.1 (0x00007fa8b40b7000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fa8b3cea000)
        /lib64/ld-linux-x86-64.so.2 (0x000055bb3e3e8000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fa8b3acf000)
        libmql1.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libmql1.so (0x00007fa8b3858000)
        libipc1.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libipc1.so (0x00007fa8b3424000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007fa8b3222000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fa8b3009000)
[oracle@rac1 12cogg]$

[oracle@rac1 12cogg]$ ldd ggsci
        linux-vdso.so.1 =>  (0x00007ffd3cdd0000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fcbc2f48000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fcbc2d43000)
        libgglog.so => /u01/app/oracle/product/12cogg/./libgglog.so (0x00007fcbc29dd000)
        libggutil.so => /u01/app/oracle/product/12cogg/./libggutil.so (0x00007fcbc27c1000)
        libggrepo.so => /u01/app/oracle/product/12cogg/./libggrepo.so (0x00007fcbc2506000)
        libdb-6.1.so => /u01/app/oracle/product/12cogg/./libdb-6.1.so (0x00007fcbc20f0000)
        liblmdb.so => /u01/app/oracle/product/12cogg/./liblmdb.so (0x00007fcbc1edb000)
        libggperf.so => /u01/app/oracle/product/12cogg/./libggperf.so (0x00007fcbc1ca8000)
        libggparam.so => /u01/app/oracle/product/12cogg/./libggparam.so (0x00007fcbc058f000)
        libicui18n.so.62 => /u01/app/oracle/product/12cogg/./libicui18n.so.62 (0x00007fcbc00aa000)
        libicuuc.so.62 => /u01/app/oracle/product/12cogg/./libicuuc.so.62 (0x00007fcbbfca8000)
        libicudata.so.62 => /u01/app/oracle/product/12cogg/./libicudata.so.62 (0x00007fcbbdc90000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fcbbda74000)
        libxerces-c-3.2.so => /u01/app/oracle/product/12cogg/./libxerces-c-3.2.so (0x00007fcbbd3ff000)
        libantlr3c.so => /u01/app/oracle/product/12cogg/./libantlr3c.so (0x00007fcbbd1e4000)
        libnnz12.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so (0x00007fcbbca9b000)
        libclntsh.so.12.1 => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1 (0x00007fcbb8ff5000)
        libons.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libons.so (0x00007fcbb8da7000)
        libclntshcore.so.12.1 => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntshcore.so.12.1 (0x00007fcbb87d9000)
        libggnnzitp.so => /u01/app/oracle/product/12cogg/./libggnnzitp.so (0x00007fcbb7d06000)
        libstdc++.so.6 => /u01/app/oracle/product/12cogg/./libstdc++.so.6 (0x00007fcbb796f000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fcbb766d000)
        libgcc_s.so.1 => /u01/app/oracle/product/12cogg/./libgcc_s.so.1 (0x00007fcbb7456000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fcbb7089000)
        /lib64/ld-linux-x86-64.so.2 (0x000055f4b6ba5000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fcbb6e6e000)
        libmql1.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libmql1.so (0x00007fcbb6bf7000)
        libipc1.so => /u01/app/oracle/product/12.2.0/dbhome_1/lib/libipc1.so (0x00007fcbb67c3000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007fcbb65c1000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fcbb63a8000)
[oracle@rac1 12cogg]$

Make sure no missing links to $ORACLE_HOME/lib.


10. Create the required working directories for GoldenGate

[oracle@rac1 ~]$ . 12cogg.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Oct 18 2019 01:38:51
Operating system character set identified as UTF-8.

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



GGSCI (rac1) 1> CREATE SUBDIRS

Creating subdirectories under current directory /u01/app/oracle/product/12cogg

Parameter file                 /u01/app/oracle/product/12cogg/dirprm: created.
Report file                    /u01/app/oracle/product/12cogg/dirrpt: created.
Checkpoint file                /u01/app/oracle/product/12cogg/dirchk: created.
Process status files           /u01/app/oracle/product/12cogg/dirpcs: created.
SQL script files               /u01/app/oracle/product/12cogg/dirsql: created.
Database definitions files     /u01/app/oracle/product/12cogg/dirdef: created.
Extract data files             /u01/app/oracle/product/12cogg/dirdat: created.
Temporary files                /u01/app/oracle/product/12cogg/dirtmp: created.
Credential store files         /u01/app/oracle/product/12cogg/dircrd: created.
Masterkey wallet files         /u01/app/oracle/product/12cogg/dirwlt: created.
Dump files                     /u01/app/oracle/product/12cogg/dirdmp: created.


GGSCI (rac1) 2>

Congratulations, Oracle GoldenGate 19c 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.

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

Reference:

https://docs.oracle.com/en/middleware/goldengate/core/19.1/installing/installing-oracle-goldengate.html

ADD NEW TABLE TO AN EXISTING GOLDENGATE CONFIGURATION

ADD NEW TABLE TO AN EXISTING EXTRACT, PUMP AND REPLICAT

Table of Contents

___________________________________________________________________________________________________

0. Overview

Source Side:

1. Login to GGSCI
2. Add trandata for new table
3. Add table to Extract
4. Add table to Pump
5. Restart Extract
6. Restart Pump
7. Verify Status
8. Capture current SCN
9. Export Table using FLASHBACK_SCN
10. Transfer dump to target server

Target Side:

11. Import new table
12. Add table to Replicat
13. Restart Replicat
14. Verify Status

15. Data sync Verify

Source
Target

16. Remove the FILTER parameters replicat
17. Restart the replicat
18. Verify Status

19. Re-verify data sync

___________________________________________________________________________________________________


0. Overview:

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)

Credential Store

Oracle GoldenGate Security – Credential Store for Database Logins

Table of Contents

___________________________________________________________________________________________________

1. Overview
2. Login to GoldenGate
3. Add/Create Credential store

 

4. Add user to credential store

4.1 Add user to credential store with ALIAS
                                — OR —
4.2 Add user to credential store with domain
                                — OR —
4.3 Add user with connect string

 

5. Info credential store

5.1 Info credential store with ALIAS
5.2 Info credential store with domain

 

6. Login to GoldenGate

6.1 Login to OGG with USERIDALIAS
                                   — OR —
6.2 Login to OGG with USERIDALIAS and DOMAIN

 

7. REPLACE USER

7.1 Change password at GoldenGate level (ALIAS)
                                    — OR —
7.2 Change password at GoldenGate level (DOMAIN)

 

8. DELETE user

8.1 Delete user with ALIAS or default
                                     — OR —
8.2 Delete user with ALIAS and DOMAIN

___________________________________________________________________________________________________


1. Overview

Credential Store - OGG 12.1 New Feature

The credential store manages user IDs and their encrypted passwords

A Credential Store is a User ID and Password storage mechanism that utilizes the auto login wallet within the Oracle Credential Store Framework to store and encrypt sensitive information, which in turn will be accessed by GoldenGate processes via ALIAS parameters, such as USERIDALIAS and ASMUSERIDALIAS.

The Credential Store is created in the default directory dircrd under the Oracle GoldenGate installation directory.

We can't change the ALIAS name, if you really want to change then delete and re-create

We can create multiple ALIAS names for same userid 


2. Login to GoldenGate

GGSCI (rac2.rajasekhar.com) 1> dblogin userid oggadmin, password oggadmin_123
Successfully logged into database.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 2> 


3. Add/Create Credential store

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 2>  sh ls -ltr /u01/app/oracle/product/12.3/ogg/dircrd

total 0  <-----------

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 3>  ADD CREDENTIALSTORE

Credential store created.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 4>  sh ls -ltr /u01/app/oracle/product/12.3/ogg/dircrd

total 4
-rw-r-----. 1 oracle oinstall 418 Apr  1 14:31 cwallet.sso <------

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 5> 


4. Add user to credential store

4.1 Add user to credential store with ALIAS

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 5> ALTER CREDENTIALSTORE ADD USER oggadmin PASSWORD oggadmin_123 ALIAS ogg

Credential store altered.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 6>

—- OR —-

4.2 Add user to credential store with domain

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 9> ALTER CREDENTIALSTORE ADD USER oggadmin PASSWORD oggadmin_123 ALIAS ogg DOMAIN gg

Credential store altered.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 10>

—- OR —-


4.3 Add user with connect string

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 17> ALTER CREDENTIALSTORE ADD USER oggadmin@PDEV PASSWORD oggadmin_987 ALIAS oggs

Credential store altered.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 18> 


5. Info credential store

5.1 Info credential store with ALIAS

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 6> INFO CREDENTIALSTORE

Reading from credential store:

Default domain: OracleGoldenGate

 Alias: ogg
Userid: oggadmin

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 7>

--- OR ---

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 19> ALTER CREDENTIALSTORE ADD USER oggadmin PASSWORD oggadmin_987 ALIAS ogg

Credential store altered.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 20> info CREDENTIALSTORE

Reading from credential store:

Default domain: OracleGoldenGate

Alias: oggs
Userid: oggadmin@PDEV

Alias: ogg
Userid: oggadmin

Other domains:

gg

To view other domains, use INFO CREDENTIALSTORE DOMAIN

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 21>

—- OR —-


5.2 Info credential store with domain

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 11> INFO CREDENTIALSTORE DOMAIN gg

Reading from credential store:

Domain: gg

Alias: ogg
Userid: oggadmin

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 12>


6. Login to GoldenGate using USERIDALIAS


6.1 Login to OGG with ALIAS

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 7> DBLOGIN USERIDALIAS ogg
Successfully logged into database.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 8>

— OR —


6.2 Login to OGG with ALIAS and DOMAIN

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 12> DBLOGIN USERIDALIAS ogg DOMAIN gg
Successfully logged into database.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 13>


7. REPLACE USER

-- Incase of password change at database level due security reasons, then we need to change at GoldenGate level as well

SQL> alter user oggadmin identified by oggadmin_987;

User altered.

SQL>


7.1 Change password at GoldenGate level

GGSCI (rac2.rajasekhar.com) 1> DBLOGIN USERIDALIAS ogg
ERROR: Unable to connect to database using user oggadmin. Ensure that the necessary privileges are granted to the user.
OCI Error ORA (status = 1017-ORA-01017: invalid username/password; logon denied
).

GGSCI (rac2.rajasekhar.com) 2>

GGSCI (rac2.rajasekhar.com) 3> INFO CREDENTIALSTORE

Reading from credential store:

Default domain: OracleGoldenGate

  Alias: ogg
  Userid: oggadmin

GGSCI (rac2.rajasekhar.com) 4>

GGSCI (rac2.rajasekhar.com) 8> ALTER CREDENTIALSTORE REPLACE USER oggadmin PASSWORD oggadmin_987 ALIAS ogg

Credential store altered.

GGSCI (rac2.rajasekhar.com) 9> DBLOGIN USERIDALIAS ogg
Successfully logged into database.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 10>

— OR —


7.2 Change password at GoldenGate level (DOMAIN)

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 14> INFO CREDENTIALSTORE DOMAIN gg

Reading from credential store:

Domain: gg

  Alias: ogg
  Userid: oggadmin

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 15> ALTER CREDENTIALSTORE REPLACE USER oggadmin PASSWORD oggadmin_987 ALIAS ogg DOMAIN gg

Credential store altered.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 16> DBLOGIN USERIDALIAS ogg DOMAIN gg
Successfully logged into database.

GGSCI (rac2.rajasekhar.com as oggadmin@PDEV) 17>


8. Delete user

8.1 Delete user with ALIAS or default

GGSCI (rac2.rajasekhar.com) 18> ALTER CREDENTIALSTORE DELETE USER ogg

Credential store altered.

GGSCI (rac2.rajasekhar.com) 19>

— OR —


8.2 Delete user with ALIAS and DOMAIN

GGSCI (rac2.rajasekhar.com) 15> ALTER CREDENTIALSTORE DELETE USER ogg

ERROR: Credential domain 'OracleGoldenGate' not found in credential store.

GGSCI (rac2.rajasekhar.com) 16> ALTER CREDENTIALSTORE DELETE USER ogg DOMAIN gg

Credential store altered.

GGSCI (rac2.rajasekhar.com) 17>

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/

Oracle GoldenGate Heterogeneous Replication

Oracle GoldenGate Replication between MySQL 5.5 to Oracle 12c

Contents
______________________________________________________________________________________________________________

1. Overview
2. Environment
3. Install GoldenGate 12C for MySQL
4. Install GoldenGate 12C for Oracle 12.1.0.1.0

5. Create Database and User

a) On Source MySQL
b) On Target Oracle 12c

6. OGG Configuration on Source MySQL

         a) Set Environment
         b) Login to ggsci
         c) Configure Manager
         d) Create Extract Group
         e) Create Pump Extract Group
         f) Create Source Table Definitions

7. OGG Configuration on Target Oracle 12c

       a) Set 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

8. Create the Replicat Group
9. On Source (MySQL) Start Manager, Extract and Pump
10. On Target (Oracle 12C) start the Manager and Replicat

11. Test and Verify the Results

INSERT OPERATION

      a1) Execute Insert on the Source Oracle Database MySQL
      a2) Verify Insert Changes on Target Oracle Database Oracle 12C

UPDATE OPERATION

      b1) Execute update on the Source Oracle Database MySQL
      b2) Verify Update Changes on Target Oracle Database Oracle 12c

DELETE OPERATION

      c1) Execute Delete on the Source Oracle Database MySQL
      c2) Verify Delete Changes on Target Oracle Database Oracle 12c

_____________________________________________________________________________________________________________


1. Overview

Configure Oracle GoldenGate to perform one-way replication from MySQL 5.5 database to an Oracle 12c database on same server


2. Environment

Source (MySQL):

Server Name			: RAC1.RAJASEKHAR.COM
OS Version			: Red Hat Enterprise Linux 5.8
GoldenGate Version		: 12.1.2.0.0 for MySQL
Database Name			: CHITTI
File System			: Normal 
Golden Gate User		: OGGUSER
Golden Gate Extract		: E_MYSQL
Golden Gate Pump		: P_MYSQL
Application User		: CHITTI (Replicating this user data to target)
GoldenGate Home			: /u01/app/oracle/product/mysqlogg

Target (Oracle 12c):

		
Server Name			: RAC1.RAJASEKHAR.COM
OS Version			: Red Hat Enterprise Linux 5.8
Database Version		: 12.1.0.1.0
GoldenGate Version		: 12.1.2.0.0 for Oracle 12c
Database Name			: ORCL12
File System			: Normal 
Golden Gate User		: OGGUSER
Golden Gate Replicat	        : R_MYSQL
Application User		: CHITTI
GoldenGate Home			: /u01/app/oracle/product/12cogg


3. Install GoldenGate 12C for MySQL

We will publish soon about this separatly !!!


4. Install GoldenGate 12C for Oracle 12.1.0.1.0

We will publish soon about this separatly !!!


5. Create MySQL Database and User CHITTI for testing


a) On Source (MySQL)

[root@rac1 raj]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database CHITTI;
Query OK, 1 row affected (0.00 sec)

mysql>

mysql> CREATE USER 'CHITTI'@'localhost' IDENTIFIED BY 'CHITTI';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user where user='CHITTI';
+-----------+--------+
| host      | user   |
+-----------+--------+
| localhost | CHITTI |
+-----------+--------+
1 row in set (0.15 sec)

mysql>

mysql> GRANT ALL ON *.* TO 'CHITTI'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| CHITTI             |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql>

mysql> show tables;
Empty set (0.00 sec)

mysql>


-- Create the source database tables
  
[root@rac1 oracle]# mysql -u CHITTI -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE CHITTI;
Database changed
mysql> 
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| CHITTI     |
+------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT USER();
+------------------+
| USER()           |
+------------------+
| CHITTI@localhost |
+------------------+
1 row in set (0.00 sec)

mysql>
mysql> USE CHITTI;
Database changed
mysql> drop table if exists trans_type;
Query OK, 0 rows affected (0.21 sec)

mysql> create table trans_type (
    ->   trans_id smallint,
    ->   trans varchar(5),
    ->   primary key (trans_id))
    ->   ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql>

mysql> show tables;
+------------------+
| Tables_in_CHITTI |
+------------------+
| trans_type       |
+------------------+
1 rows in set (0.00 sec)

mysql>

mysql> select count(*) as trans_type from trans_type;
+------------+
| trans_type |
+------------+
|          0 |
+------------+
1 row in set (0.01 sec)

mysql>


b) On Target (Oracle 12c)

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL12    READ WRITE

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

User created.

SQL> grant connect,resource to chitti;

Grant succeeded.

SQL> alter user chitti quota unlimited on users;

User altered.

SQL> conn CHITTI/CHITTI;
Connected.

SQL> create table trans_type (
  trans_id smallint,
  trans varchar2(5),
  primary key (trans_id)
  using index
);

Table created.

SQL> 
SQL> col tname for a15
SQL> select * from tab;

TNAME           TABTYPE  CLUSTERID
--------------- ------- ----------
TRANS_TYPE      TABLE

1 rows selected.

SQL>
SQL> set echo on;
SQL> select count(*) from TRANS_TYPE;

  COUNT(*)
----------
         0  <------------------ empty table 

SQL>


6. OGG Configuration on Source MySQL


a) Set Environment

. mysql.env

#!/bin/bash
GG=/u01/app/oracle/product/mysqlogg ; export GG
JAVA_HOME=/u01/software/jdk1.7.0_45 ; export JAVA_HOME
PATH=$JAVA_HOME/bin:$PATH:$HOME/bin:/usr/local/bin ; export PATH
LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64/server/libjvm.so:/u01/app/oracle/product/mysqlogg ; export LD_LIBRARY_PATH
alias ggsci='cd $GG; rlwrap ./ggsci'


b) Login to ggsci

[root@rac1 ~]# cd /home/oracle
[root@rac1 oracle]# . mysql.env
[root@rac1 oracle]# ggsci

Oracle GoldenGate Command Interpreter for MySQL
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316
Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 24 2013 15:32:47
Operating system character set identified as UTF-8.

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

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

GGSCI (rac1.rajasekhar.com) 3> 


c) Configure Manager

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

-- Add below lines

PORT 15100
DYNAMICPORTLIST 15110-15120
PURGEOLDEXTRACTS ./dirdat/eo, USECHECKPOINTS, MINKEEPFILES 10

GGSCI (rac1.rajasekhar.com) 5> view params mgr

PORT 15100
DYNAMICPORTLIST 15110-15120
PURGEOLDEXTRACTS ./dirdat/eo, USECHECKPOINTS, MINKEEPFILES 10

GGSCI (rac1.rajasekhar.com) 6>


d) Create Extract Group

-- CREATE EXTRACT AND LOCAL EXTRACT TRAIL OF 50MB

GGSCI (rac1.rajasekhar.com) 6> ADD EXTRACT E_MYSQL, VAM, BEGIN NOW
EXTRACT added.

GGSCI (rac1.rajasekhar.com) 7> ADD EXTTRAIL ./dirdat/eo, EXTRACT E_MYSQL, MEGABYTES 50
EXTTRAIL added.

GGSCI (rac1.rajasekhar.com) 8>

GGSCI (rac1.rajasekhar.com) 22> edit param E_MYSQL

-- Add below lines

EXTRACT E_MYSQL

EXTTRAIL ./dirdat/eo

SOURCEDB CHITTI@localhost:3306, USERID CHITTI, PASSWORD CHITTI

TRANLOGOPTIONS ALTLOGDEST /var/lib/mysql/pts-bin.index

STATOPTIONS RESETREPORTSTATS

REPORT AT 00:01

REPORTROLLOVER AT 00:01

REPORTCOUNT EVERY 60 SECONDS, RATE

TABLE CHITTI.trans_type;

GGSCI (rac1.rajasekhar.com) 23> view param E_MYSQL

EXTRACT E_MYSQL

EXTTRAIL ./dirdat/eo

SOURCEDB CHITTI@localhost:3306, USERID CHITTI, PASSWORD CHITTI

TRANLOGOPTIONS ALTLOGDEST /var/lib/mysql/pts-bin.index

STATOPTIONS RESETREPORTSTATS

REPORT AT 00:01

REPORTROLLOVER AT 00:01

REPORTCOUNT EVERY 60 SECONDS, RATE

TABLE CHITTI.trans_type;


GGSCI (rac1.rajasekhar.com) 24>


e) Create Pump Extract Group

-- CREATE DATA PUMP FOR ORACLE 12C AND REMOTE EXTRACT TRAIL OF 50MB

GGSCI (rac1.rajasekhar.com) 25> ADD EXTRACT P_MYSQL, EXTTRAILSOURCE ./dirdat/eo
EXTRACT added.


GGSCI (rac1.rajasekhar.com) 26> ADD RMTTRAIL ./dirdat/ra, EXTRACT P_MYSQL, MEGABYTES 50
RMTTRAIL added.


GGSCI (rac1.rajasekhar.com) 27> edit param P_MYSQL

-- Add below lines

EXTRACT P_MYSQL

USERID ogguser, password oracle

RMTHOST 192.168.2.101, MGRPORT 15000

RMTTRAIL ./dirdat/ra

PASSTHRU

REPORTCOUNT EVERY 60 SECONDS, RATE

TABLE CHITTI.*;


GGSCI (rac1.rajasekhar.com) 28> view params P_MYSQL

EXTRACT P_MYSQL

USERID ogguser, password oracle

RMTHOST 192.168.2.101, MGRPORT 15000

RMTTRAIL ./dirdat/ra

PASSTHRU

REPORTCOUNT EVERY 60 SECONDS, RATE

TABLE CHITTI.*;


GGSCI (rac1.rajasekhar.com) 29>


f) Create Source Table Definitions

login as root
cd /home/oracle
. mysql.env

cd /u01/app/oracle/product/mysqlogg/dirprm/

vi defgen_chitti.prm

-- Add below lines
DEFSFILE ./dirdef/chittidefs.sql
SOURCEDB CHITTI@localhost, USERID CHITTI, PASSWORD CHITTI
TABLE CHITTI.*;

[root@rac1 dirprm]# cat defgen_chitti.prm
DEFSFILE ./dirdef/chittidefs.sql
SOURCEDB CHITTI@localhost, USERID CHITTI, PASSWORD CHITTI
TABLE CHITTI.*;
[root@rac1 dirprm]#

cd /u01/app/oracle/product/mysqlogg

[root@rac1 mysqlogg]# ./defgen paramfile /u01/app/oracle/product/mysqlogg/dirprm/defgen_chitti.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for MySQL
 Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316
 Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 24 2013 16:01:59

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


                    Starting at 2018-09-05 15:12:31
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Nov 7 18:13:56 PST 2012, Release 2.6.32-300.39.1.el5uek
Node: rac1.rajasekhar.com
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 9617

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
DEFSFILE ./dirdef/chittidefs.sql
SOURCEDB CHITTI@localhost, USERID CHITTI, PASSWORD ******
TABLE CHITTI.*;
Expanding wildcard table specification CHITTI.*:

Retrieving definition for CHITTI.trans_type.


Definitions generated for 1 table in ./dirdef/chittidefs.sql.

[root@rac1 mysqlogg]#

[root@rac1 dirdef]# pwd
/u01/app/oracle/product/mysqlogg/dirdef
[root@rac1 dirdef]# cp chittidefs.sql /u01/app/oracle/product/12cogg/dirdef
[root@rac1 dirdef]# chmod 644 /u01/app/oracle/product/12cogg/dirdef/chittidefs.sql
[root@rac1 dirdef]#


7. OGG Configuration on Target Oracle 12C


a) Set Environment

. 12c.env

#!/bin/bash
GG=/u01/app/oracle/product/12cogg ; export GG
ORACLE_HOME=/u01/app/oracle/product/12c/db_1;  export ORACLE_HOME
ORACLE_SID=ORCL12 ; export ORACLE_SID
JAVA_HOME=/u01/software/jdk1.7.0_45 ; export JAVA_HOME
PATH=$JAVA_HOME/bin:$PATH:$HOME/bin:/usr/local/bin ; export PATH
LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64/server/libjvm.so:$ORACLE_HOME/lib; export LD_LIBRARY_PATH
alias ggsci='cd $GG; rlwrap ./ggsci'


b) Login to Database

[oracle@rac1 ~]$ . 12c.env
[oracle@rac1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.

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

GGSCI (rac1.rajasekhar.com) 1> DBLOGIN USERID ogguser, PASSWORD oracle
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 lines

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

— Global file need to create on target where replicat process running
— Checkpoint tables are only used by the Replicat, so NO need to create on source because it’s one-way replication.


ii) Activate the GLOBALS parameters

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

-- Add below entries
GGSCHEMA OGGUSER
CHECKPOINTTABLE OGGUSER.CHECKPOINT

GGSCI (rac1.rajasekhar.com) 7> view params ./GLOBALS

GGSCHEMA OGGUSER
CHECKPOINTTABLE OGGUSER.CHECKPOINT


GGSCI (rac1.rajasekhar.com) 8>

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 12cogg]$


iii) Add a Replicat checkpoint table

GGSCI (rac1.rajasekhar.com) 1> DBLOGIN USERID ogguser, PASSWORD oracle
Successfully logged into database.

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

Successfully created checkpoint table OGGUSER.CHECKPOINT.

GGSCI (rac1.rajasekhar.com) 3> 


8. Create the Replicat Group

GGSCI (rac1.rajasekhar.com) 3> ADD REPLICAT R_MYSQL, EXTTRAIL ./dirdat/ra, CHECKPOINTTABLE OGGUSER.CHECKPOINT
REPLICAT added.


GGSCI (rac1.rajasekhar.com) 4>  edit param R_MYSQL

-- Add below lines

REPLICAT R_MYSQL

SOURCEDEFS ./dirdef/chittidefs.sql

USERID ogguser, PASSWORD oracle

DISCARDFILE ./dirout/R_MYSQL.DSC, PURGE

STATOPTIONS RESETREPORTSTATS

MAP CHITTI.*, TARGET CHITTI.*;


GGSCI (rac1.rajasekhar.com) 7> view param R_MYSQL

REPLICAT R_MYSQL

SOURCEDEFS ./dirdef/chittidefs.sql

USERID ogguser, PASSWORD oracle

DISCARDFILE ./dirout/R_MYSQL.DSC, PURGE

STATOPTIONS RESETREPORTSTATS

MAP CHITTI.*, TARGET CHITTI.*;


GGSCI (rac1.rajasekhar.com) 8>


9. On Source (MySQL) Start Manager, Extract and Pump

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     E_MYSQL     00:00:00      02:11:01
EXTRACT     STOPPED     P_MYSQL     00:00:00      02:10:38


GGSCI (rac1.rajasekhar.com) 2> start mgr
Manager started.


GGSCI (rac1.rajasekhar.com) 3> start E_MYSQL

Sending START request to MANAGER ...
EXTRACT E_MYSQL starting


GGSCI (rac1.rajasekhar.com) 4> start P_MYSQL

Sending START request to MANAGER ...
EXTRACT P_MYSQL starting


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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E_MYSQL     02:12:25      00:00:05
EXTRACT     RUNNING     P_MYSQL     00:00:00      02:12:08


GGSCI (rac1.rajasekhar.com) 6>


10. On Target (Oracle 12C) start the Manager and Replicat

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
REPLICAT    STOPPED     R_MYSQL     00:00:00      00:47:04


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


GGSCI (rac1.rajasekhar.com) 10> start R_MYSQL

Sending START request to MANAGER ...
REPLICAT R_MYSQL starting


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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     R_MYSQL     00:00:00      00:00:04


GGSCI (rac1.rajasekhar.com) 16>


11. Test and Verify the Results


INSERT OPERATION


a1) Execute Insert on the Source Oracle Database MySQL

[root@rac1 oracle]# mysql -u CHITTI -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.5.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE CHITTI;
Database changed

mysql> select user();
+------------------+
| user()           |
+------------------+
| CHITTI@localhost |
+------------------+
1 row in set (0.00 sec)

mysql>

mysql> INSERT INTO trans_type values(1,'SUGI');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO trans_type values(2,'TEJU');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO trans_type values(3,'RAJ');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO trans_type values(4,'SOMU');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO trans_type values(5,'SREERAM');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>


GGSCI (rac1.rajasekhar.com) 15> stats E_MYSQL total

Sending STATS request to EXTRACT E_MYSQL ...

Start of Statistics at 2018-09-05 17:26:03.

Output to ./dirdat/eo:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:10:24 ***
        Total inserts                                      5.00 <----
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00

End of Statistics.

GGSCI (rac1.rajasekhar.com) 17>
GGSCI (rac1.rajasekhar.com) 18> stats P_MYSQL total

Sending STATS request to EXTRACT P_MYSQL ...

Start of Statistics at 2018-09-05 17:26:37.

Output to ./dirdat/ra:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:23:49 ***
        Total inserts                                      5.00 <----
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 18>

mysql> select * from trans_type;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
|        5 | SREER |
+----------+-------+
5 rows in set (0.00 sec)

mysql>


a2) Verify Insert Changes on Target Oracle Database Oracle 12c

GGSCI (rac1.rajasekhar.com) 17> stats R_MYSQL total

Sending STATS request to REPLICAT R_MYSQL ...

Start of Statistics at 2018-09-05 17:31:09.

Replicating from CHITTI.trans_type to CHITTI.TRANS_TYPE:

*** Total statistics since 2018-09-05 17:24:11 ***
        Total inserts                                      5.00 <---
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 18>

SQL> select * from CHITTI.TRANS_TYPE;

  TRANS_ID TRANS
---------- -----
         1 SUGI
         2 TEJU
         3 RAJ
         4 SOMU
         5 SREER

SQL>


UPDATE OPERATION


b1) Execute update on the Source Oracle Database MySQL

mysql> select * from trans_type;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
|        5 | SREER |
+----------+-------+
5 rows in set (0.00 sec)

mysql> update trans_type set trans='SELVA' where trans_id='5';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from trans_type;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
|        5 | SELVA |
+----------+-------+
5 rows in set (0.00 sec)

mysql>


GGSCI (rac1.rajasekhar.com) 19> stats E_MYSQL total

Sending STATS request to EXTRACT E_MYSQL ...

Start of Statistics at 2018-09-05 17:46:50.

Output to ./dirdat/eo:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:10:24 ***
        Total inserts                                      5.00
        Total updates                                      1.00 <----
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   6.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 20> stats P_MYSQL total

Sending STATS request to EXTRACT P_MYSQL ...

Start of Statistics at 2018-09-05 17:47:11.

Output to ./dirdat/ra:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:23:49 ***
        Total inserts                                      5.00
        Total updates                                      1.00 <----
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   6.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 21>


b2) Verify Update Changes on Target Oracle Database Oracle 12c

GGSCI (rac1.rajasekhar.com) 18> stats R_MYSQL total

Sending STATS request to REPLICAT R_MYSQL ...

Start of Statistics at 2018-09-05 17:48:53.

Replicating from CHITTI.trans_type to CHITTI.TRANS_TYPE:

*** Total statistics since 2018-09-05 17:24:11 ***
        Total inserts                                      5.00
        Total updates                                      1.00 <----
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   6.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 19>

SQL> select * from CHITTI.TRANS_TYPE;

  TRANS_ID TRANS
---------- -----
         1 SUGI
         2 TEJU
         3 RAJ
         4 SOMU
         5 SELVA  <------

SQL>


DELETE OPERATION


c1) Execute Delete on the Source Oracle Database MySQL

mysql> select * from trans_type;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
|        5 | SELVA |
+----------+-------+
5 rows in set (0.00 sec)

mysql> DELETE FROM trans_type WHERE TRANS_ID = 5;
Query OK, 1 row affected (0.21 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from trans_type;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
+----------+-------+
4 rows in set (0.00 sec)

mysql>

GGSCI (rac1.rajasekhar.com) 21> stats E_MYSQL total

Sending STATS request to EXTRACT E_MYSQL ...

Start of Statistics at 2018-09-05 17:52:42.

Output to ./dirdat/eo:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:10:24 ***
        Total inserts                                      5.00
        Total updates                                      1.00
        Total deletes                                      1.00 <----
        Total discards                                     0.00
        Total operations                                   7.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 22> stats P_MYSQL total

Sending STATS request to EXTRACT P_MYSQL ...

Start of Statistics at 2018-09-05 17:52:50.

Output to ./dirdat/ra:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:23:49 ***
        Total inserts                                      5.00
        Total updates                                      1.00
        Total deletes                                      1.00 <----
        Total discards                                     0.00
        Total operations                                   7.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 23>


c2) Verify Delete Changes on Target Oracle Database Oracle 12c

GGSCI (rac1.rajasekhar.com) 19> stats R_MYSQL total

Sending STATS request to REPLICAT R_MYSQL ...

Start of Statistics at 2018-09-05 17:55:07.

Replicating from CHITTI.trans_type to CHITTI.TRANS_TYPE:

*** Total statistics since 2018-09-05 17:24:11 ***
        Total inserts                                      5.00
        Total updates                                      1.00
        Total deletes                                      1.00 <-----
        Total discards                                     0.00
        Total operations                                   7.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 20>

SQL> select * from CHITTI.TRANS_TYPE;

  TRANS_ID TRANS
---------- -----
         1 SUGI
         2 TEJU
         3 RAJ
         4 SOMU

SQL>

GGSCI (rac1.rajasekhar.com) 20> info R_MYSQL

REPLICAT   R_MYSQL   Last Started 2018-09-05 17:01   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           13305
Log Read Checkpoint  File ./dirdat/ra000000
                     2018-09-05 17:52:02.000864  RBA 2433


GGSCI (rac1.rajasekhar.com) 21> lag R_MYSQL

Sending GETLAG request to REPLICAT R_MYSQL ...
Last record lag 7 seconds.
At EOF, no more records to process.


GGSCI (rac1.rajasekhar.com) 22>

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
Email: br8dba@gmail.com

Integrated Capture – Local deployment

AIM: Configure GoldenGate 12C Integrated Capture  (All extract/pump and       replicat on same database/server).
_________________________________________________________________________________

1. Overview
2. Environment
3. Install GoldenGate 12C for Oracle 11.2.0.3.10
4. Create test schemas for replication
5. Grant DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE
6. Set streams_pool_size
7. Verify compatible

8. Setup DDL scripts at Database level

     a) Run marker_setup script
     b) Run ddl_setup script
     c) Run ddl_enable script
     d) Run ddl_pin script

9. OGG Configuration

      a) Set the environment
      b) Login to Database
      c) Configure Manager
      d) Create Extract Group
      e) Create Pump Group
      f) Add supplemental logging
      g) Set up the Checkpoint table
      h) Activate the GLOBALS parameters
      i) Add a Replicat checkpoint table
      j) Create the Replicat Group
      k) Start Manager, Extract, Pump and Replicat

10. Verify

LIST AVAILABLE TABLES CURRENTLY

     a1) Source
     a2) Target

CREATE TABLE OPERATION

      b1) Source
      b2) Target

INSERT OPERATION

       c1) Execute INSERT on the Source
       c2) Verify Insert Changes on Target

UPDATE OPERATION

        d1) Execute update on the Source
        d2) Verify Update Changes on Target

DELETE OPERATION

        e1) Execute Delete on the Source
        e2) Verify Delete Changes on Target

ALTER TABLE OPERATION

         f1) Execute ALTER TABLE on the Source
         f2) Verify ALTER TABLE Changes on Target

DROP TABLE OPERATION

         g1) Execute DROP TABLE on the Source
         g2) Verify DROP TABLE Changes on Target

TRUNCATE TABLE OPERATION

         h1) Execute TRUNCATE TABLE on the Source
         h2) Verify TRUNCATE TABLE Changes on Target

11. Verify alertlog

_________________________________________________________________________________________________________________

1. Overview

GoldenGate 12c: 
Integrated Extract is available from OGG 11g
Integrated Replicat is available from OGG 12c

What is Integrated Capture?

Instead of reading from the redo/archive logs, the Oracle GoldenGate Extract process interacts directly with a database logmining server to receive data changes in the form of logical change records (LCR).

In Pre-Goldengate 12c classic capture mode, the Oracle GoldenGate Extract process captures data changes from the Oracle redo or archive log files on the source system.

Integrated capture modes (2 Types)

a) Local deployment
The source database and the log mining server are the same database

b) Downstream deployment <--- We will cover this soon in upcoming article
The source and log mining databases are different databases. 


2. Environment

Server Name			: RAC1,RAC2
OS Version			: Red Hat Enterprise Linux 5.7
RAC				: 2 Node RAC
Database Version		: 11.2.0.3.10
GoldenGate Version		: Version 12.1.2.0.0 for oracle db 11.2.0.3.10
Database Name			: BR8DBA (br8dba1, br8dba2)
File System			: ASM 
Golden Gate User		: GATE
Golden Gate Extract		: E11G
Golden Gate Pump		: P11G
Golden Gate Replicat	        : R11G
Source Schema			: SENDER
Target Schema			: RECEIVER
Oracle Home			: /u01/app/oracle/product/11.2.0.3/db_1
GoldenGate Home			: /u01/app/oracle/product/gg/ogg11 (shared home)


3. Install GoldenGate 12C for Oracle 11.2.0.3.10


4. Create test schemas for replication

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

User created.

SQL> grant connect,resource,unlimited tablespace to sender;

Grant succeeded.

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

User created.

SQL> grant connect,resource,unlimited tablespace to receiver;

Grant succeeded.

SQL>


5. Grant DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE

SQL> exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GATE', 'capture');

PL/SQL procedure successfully completed.

SQL>


6. Set streams_pool_size

On Instance 1 (br8dba1):

SQL> alter system set streams_pool_size=150M sid='br8dba1';

System altered.

SQL> 

On Instance 2 (br8dba2):

SQL> alter system set streams_pool_size=150M sid='br8dba2';

System altered.

SQL>


7. Verify compatible

SQL> select ISSYS_MODIFIABLE from V$PARAMETER where NAME='compatible';

ISSYS_MOD
---------
FALSE

SQL>

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.3.0 <----
SQL>

*** Database version should be atleast 11.2.0.3.0 OR above
*** Also using below MOS note we have to apply patch based on your current PSU

11.2.0.3 Database Specific Bundle Patches for Integrated Extract 11.2.x (Doc ID 1411356.1)

[oracle@rac1 ~]$ opatch lsinventory | grep -i 18466925
Patch  18466925     : applied on Mon Dec 05 21:56:31 IST 2016
[oracle@rac1 ~]$


8. Setup DDL scripts at Database level

a) Run marker_setup script

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:gate <---


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GATE

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.
SQL>


b) Run ddl_setup script

SQL> @ddl_setup.sql;

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:gate <----

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using GATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GATE

CLEAR_TRACE STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

CREATE_TRACE STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

INITIAL_SETUP STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos   Error
---------- ------------------------------
No errors  No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED

STAYMETADATA IN TRIGGER
-----------------------------------
OFF

DDL TRIGGER SQL TRACING
-----------------------------------
0

DDL TRIGGER TRACE LEVEL
-----------------------------------
0

LOCATION OF DDL TRACE FILE
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/br8dba/br8dba1/trace/ggs_ddl_trace.log

Analyzing installation status...


VERSION OF DDL REPLICATION
----------------------------------------------------------------------
OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316

STATUS OF DDL REPLICATION
----------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL>


c) Run ddl_enable script

SQL> @ddl_enable.sql;

Trigger altered.

SQL>


d) Run ddl_pin script

SQL> @ddl_pin.sql gate

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL>


9. OGG Configuration

a) Set the environment

[oracle@rac1 ~]$ cat 11.env
GG11=/u01/app/oracle/product/gg/ogg11; export GG11
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1;  export ORACLE_HOME
ORACLE_SID=br8dba1; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/gg/ogg11; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/gg/ogg11; export LD_LIBRARY_PATH
alias ggsci='cd $GG11; ./ggsci'
[oracle@rac1 ~]$

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


b) Login to Database

[oracle@rac1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

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> edit param mgr

-- Add below entries 
PORT 15500
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/ogg11/dirdat/*, USECHECKPOINTS

GGSCI (rac1.rajasekhar.com) 3> view params mgr

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

GGSCI (rac1.rajasekhar.com) 4>


d) Create Extract Group

GGSCI (rac1.rajasekhar.com) 4> ADD EXTRACT E11G INTEGRATED TRANLOG, BEGIN NOW
EXTRACT added.

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

GGSCI (rac1.rajasekhar.com) 6> edit params E11G

-- Add below entries
EXTRACT E11G
EXTTRAIL ./dirdat/ea
USERID gate, PASSWORD gate
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100)
DDL INCLUDE ALL
DDLOPTIONS REPORT
TABLE sender.*;

GGSCI (rac1.rajasekhar.com) 7> view params E11G

EXTRACT E11G
EXTTRAIL ./dirdat/ea
USERID gate, PASSWORD gate
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100)
DDL INCLUDE ALL
DDLOPTIONS REPORT
TABLE sender.*;

GGSCI (rac1.rajasekhar.com) 8>

GGSCI (rac1.rajasekhar.com) 14> REGISTER EXTRACT E11G DATABASE

Extract E11G successfully registered with database at SCN 2152703.

GGSCI (rac1.rajasekhar.com) 15>


e) Create Pump Group

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

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

GGSCI (rac1.rajasekhar.com) 10> edit params P11G

GGSCI (rac1.rajasekhar.com) 11> view params P11G

EXTRACT P11G
RMTHOST 192.168.2.101, MGRPORT 15500
USERID gate, password gate
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE sender.*;

GGSCI (rac1.rajasekhar.com) 12>


f) Add supplemental logging

GGSCI (rac1.rajasekhar.com) 16> ADD SCHEMATRANDATA sender

2016-12-05 20:36:13  INFO    OGG-01788  SCHEMATRANDATA has been added on schema sender.

2016-12-05 20:36:13  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema sender.

GGSCI (rac1.rajasekhar.com) 17>

--
The ADD SCHEMATRANDATA command adds supplemental logging to all tables both current and future for sender schema.


g) Set up the Checkpoint table

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

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

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

GGSCHEMA GATE
ENABLEMONITORING
CHECKPOINTTABLE GATE.CHECKPOINT

GGSCI (rac1.rajasekhar.com) 19>


h) 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) 19> exit
[oracle@rac1 ogg11]$


i) Add a Replicat checkpoint table

[oracle@rac1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

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>


j) Create the Replicat Group

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


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

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

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

GGSCI (rac1.rajasekhar.com) 8> view params r11g

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

GGSCI (rac1.rajasekhar.com) 9>


k) Start Manager, Extract, Pump and Replicat

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
JAGENT      STOPPED
EXTRACT     STOPPED     E11G        00:00:00      01:18:04
EXTRACT     STOPPED     P11G        00:00:00      01:08:06
REPLICAT    STOPPED     R11G        00:00:00      00:12:02


GGSCI (rac1.rajasekhar.com) 10> start mgr
Manager started.


GGSCI (rac1.rajasekhar.com) 11> start extract E11G

Sending START request to MANAGER ...
EXTRACT E11G starting


GGSCI (rac1.rajasekhar.com) 12> start extract P11G

Sending START request to MANAGER ...
EXTRACT P11G starting


GGSCI (rac1.rajasekhar.com) 13> start replicat R11G

Sending START request to MANAGER ...
REPLICAT R11G starting


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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
EXTRACT     RUNNING     E11G        00:00:09      00:00:00
EXTRACT     RUNNING     P11G        00:00:00      02:29:55
REPLICAT    RUNNING     R11G        00:00:00      00:00:04

GGSCI (rac1.rajasekhar.com) 5>


10. Verify

LIST AVAILABLE TABLES CURRENTLY

a1) Source:

SQL> conn sender/sender;
Connected.
SQL> select * from tab;

no rows selected  <----

SQL>


a2) Target:

SQL> conn receiver/receiver
Connected.
SQL> select * from tab;

no rows selected <---

SQL>


CREATE TABLE OPERATION

b1) Source:

SQL> create table sugi(Name varchar2(10) PRIMARY KEY, Role varchar2(10));

Table created.

SQL>


b2) Target:

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SUGI                           TABLE <-----

SQL>


INSERT OPERATION

c1) Execute INSERT on the Source

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

1 row created.

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

1 row created.

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

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM SUGI;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA

SQL>


c2) Verify Insert Changes on Target

SQL> SELECT * FROM SUGI;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA

SQL>


UPDATE OPERATION

d1) Execute update on the Source

SQL> update SUGI TEJU SET role='MANAGER' where name='RAJ';

1 row updated.

SQL> update SUGI SET role='MANAGER' where name='TEJU';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from sugi;

NAME       ROLE
---------- ----------
RAJ        MANAGER <----
SUGI       DBA
TEJU       MANAGER <----

SQL>


d2) Verify Update Changes on Target

SQL> select * from sugi;

NAME       ROLE
---------- ----------
RAJ        MANAGER <----
SUGI       DBA
TEJU       MANAGER <----

SQL>


DELETE OPERATION

e1) Execute Delete on the Source

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

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM SUGI;

NAME       ROLE
---------- ----------
SUGI       DBA
TEJU       MANAGER

SQL>


e2) Verify Delete Changes on Target

SQL> SELECT * FROM SUGI;

NAME       ROLE
---------- ----------
SUGI       DBA
TEJU       MANAGER

SQL>


ALTER TABLE OPERATION

f1) Execute ALTER TABLE on the Source

SQL> CREATE TABLE TEST AS SELECT * FROM SUGI;

Table created.

SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SUGI                           TABLE
TEST                           TABLE <----

SQL> ALTER TABLE TEST ADD EMPNO VARCHAR2(30);

Table altered.

SQL> desc test
 Name        Null?    Type
 ---------   -------- ----------------------------
 NAME                 VARCHAR2(10)
 ROLE                 VARCHAR2(10)
 EMPNO                VARCHAR2(30)
             
SQL>


f2) Verify ALTER TABLE Changes on Target

SQL> desc test
 Name      Null?    Type
 -------   -------- ----------------------------
 NAME               VARCHAR2(10)
 ROLE               VARCHAR2(10)
 EMPNO              VARCHAR2(30) <-----
          
SQL>


DROP TABLE OPERATION

g1) Execute DROP TABLE on the Source

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SUGI                           TABLE
TEST                           TABLE

SQL> drop table sugi purge;

Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE <----

SQL>


g2) Verify DROP TABLE Changes on Target

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE <----

SQL>


TRUNCATE TABLE OPERATION

h1) Execute TRUNCATE TABLE on the Source

SQL> truncate table test;

Table truncated.

SQL> select count(*) from test;

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

SQL>


h2) Verify TRUNCATE TABLE Changes on Target

SQL> select count(*) from test;

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

SQL>


11. Verify alertlog (alert_br8dba1.log)

CAPTURE OGG$CAP_E11G: Source Database: BR8DBA
CAPTURE OGG$CAP_E11G: Parameter Set by User: CAPTURE_IDKEY_OBJECTS Value: Y
CAPTURE OGG$CAP_E11G: Parameter Set by User: CAPTURE_SEQUENCE_NEXTVAL Value: N
CAPTURE OGG$CAP_E11G: Parameter Set by User: GETAPPLOPS Value: Y
CAPTURE OGG$CAP_E11G: Parameter Set by User: GETREPLICATES Value: N
CAPTURE OGG$CAP_E11G: Parameter Set by User: INCLUDE_OBJECTS Value: SYS.SMON_SCN_TIME
CAPTURE OGG$CAP_E11G: Parameter Set by User: MAX_SGA_SIZE Value: 100 <---
CAPTURE OGG$CAP_E11G: Parameter Set by User: PARALLELISM Value: 2 <---
GoldenGate CAPTURE CP01 for OGG$CAP_E11G with pid=54, OS id=32549 is in combined capture and apply mode.
Capture OGG$CAP_E11G is handling 1 applies.
  -- capture is running in apply-state checkpoint mode.
Starting persistent Logminer Session with sid = 1 for GoldenGate Capture OGG$CAP_E11G
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 4, Transaction Chunk Size = 1
LOGMINER: Memory Size = 66M, Checkpoint interval = 1000M
LOGMINER: SpillScn 0, ResetLogScn 1974745
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 2279365 (0x0000.0022c7c5)
LOGMINER: EndScn: 0
LOGMINER: HighConsumedScn: 2279365 (0x0000.0022c7c5)
LOGMINER: session_flag: 0xf0
LOGMINER: Read buffers: 64
LOGMINER: Memory LWM limit: 10M, 79%
LOGMINER: Memory Release Limit: 15000
LOGMINER: LowCkptScn: 2275760 (0x0000.0022b9b0)
LOGMINER: HighCkptScn: 2275762 (0x0000.0022b9b2)
LOGMINER: SkipScn: 2275760 (0x0000.0022b9b0)
Tue Dec 06 00:56:22 2016
LOGMINER: session#=1 (OGG$CAP_E11G), reader MS00 pid=55 OS id=32557 sid=164 started
Tue Dec 06 00:56:22 2016
LOGMINER: session#=1 (OGG$CAP_E11G), builder MS01 pid=57 OS id=32561 sid=162 started
Tue Dec 06 00:56:22 2016
LOGMINER: session#=1 (OGG$CAP_E11G), preparer MS02 pid=58 OS id=32565 sid=33 started
Tue Dec 06 00:56:22 2016
LOGMINER: session#=1 (OGG$CAP_E11G), preparer MS03 pid=60 OS id=32569 sid=22 started
CAPTURE OGG$CAP_E11G: Session Restart SCN: 2275760
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 20, +TEST/br8dba/onlinelog/group_2.272.924477045
LOGMINER: Begin mining logfile for session 1 thread 2 sequence 17, +TEST/br8dba/onlinelog/group_3.271.924477045
Tue Dec 06 00:56:23 2016
XStream Out client for OGG$E11G with pid=61, OS id=32572 is waiting for GoldenGate Capture OGG$CAP_E11G to complete initializ
ation.
XStream Out client for OGG$E11G successfully attached to GoldenGate Capture OGG$CAP_E11G to receive uncommitted changes with
pid=61, OS id=32572.
[oracle@rac1 trace]$

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

Reference:

11.2.0.3 Database Specific Bundle Patches for Integrated Extract 11.2.x (Doc ID 1411356.1)

GoldenGate 12C Installation

Installing Oracle GoldenGate 12.1.2.0.0 for Oracle 11.2.0.3 on Linux x86-64

Contents:
_________________________________________________________________________________________________________________

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

1. Download software


2. Unzip the software

[oracle@rac1 share]$ unzip fbo_ggs_Linux_x64_shiphome.zip


3. Installation

Please click here to download the screen shot

*** Screenshots took from different server, so directory path will be different from this article

[oracle@rac1 share]$ cd fbo_ggs_Linux_x64_shiphome
[oracle@rac1 fbo_ggs_Linux_x64_shiphome]$ ll
total 4
drwxr-xr-x 5 oracle oinstall 4096 Sep 25  2013 Disk1
[oracle@rac1 fbo_ggs_Linux_x64_shiphome]$ cd Disk1/
[oracle@rac1 Disk1]$ ll
total 16
drwxr-xr-x  4 oracle oinstall 4096 Sep 25  2013 install
drwxrwxr-x  2 oracle oinstall 4096 Sep 25  2013 response
-rwxr-xr-x  1 oracle oinstall  918 Sep 25  2013 runInstaller <-----
drwxr-xr-x 11 oracle oinstall 4096 Sep 25  2013 stage
[oracle@rac1 Disk1]$ export DISPLAY=192.168.2.2:0.0
[oracle@rac1 Disk1]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 5571 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 5671 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-12-03_04-15-08PM. Please wait ...[oracle@rac1 Disk1]$ You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2016-12-03_04-15-08PM.log

gg1gg2
gg3
gg4
gg5


4. Set Environmental Variables

[oracle@rac1 ~]$ cat 11.env
GG11=/u01/app/oracle/product/gg/ogg11; export GG11
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1;  export ORACLE_HOME
ORACLE_SID=br8dba1; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/gg/ogg11; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/gg/ogg11; export LD_LIBRARY_PATH
alias ggsci='cd $GG11; ./ggsci'
[oracle@rac1 ~]$


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

. 11g.env

[oracle@rac1 ~]$ env | grep ORACLE
ORACLE_SID=br8dba1
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
[oracle@rac1 ~]$

[oracle@rac1 ~]$ env | grep LD_LIBR
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/db_1/lib:/u01/app/oracle/product/gg/ogg11
[oracle@rac1 ~]$

[oracle@rac1 ~]$ cd /u01/app/oracle/product/gg/ogg11
[oracle@rac1 ogg11]$ ldd mgr
        linux-vdso.so.1 =>  (0x00007fffa67ff000)
        librt.so.1 => /lib64/librt.so.1 (0x00000039b2000000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00000039b1400000)
        libgglog.so => /u01/app/oracle/product/gg/ogg11/libgglog.so (0x00007f8ea033c000)
        libggrepo.so => /u01/app/oracle/product/gg/ogg11/libggrepo.so (0x00007f8ea00d1000)
        libdb-5.2.so => /u01/app/oracle/product/gg/ogg11/libdb-5.2.so (0x00007f8e9fe35000)
        libggperf.so => /u01/app/oracle/product/gg/ogg11/libggperf.so (0x00007f8e9fc07000)
        libicui18n.so.48 => /u01/app/oracle/product/gg/ogg11/libicui18n.so.48 (0x00007f8e9f8f9000)
        libicuuc.so.48 => /u01/app/oracle/product/gg/ogg11/libicuuc.so.48 (0x00007f8e9f67a000)
        libicudata.so.48 => /u01/app/oracle/product/gg/ogg11/libicudata.so.48 (0x00007f8e9dfb5000)
        libxerces-c.so.28 => /u01/app/oracle/product/gg/ogg11/libxerces-c.so.28 (0x00007f8e9da9e000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039b1800000)
        libantlr3c.so => /u01/app/oracle/product/gg/ogg11/libantlr3c.so (0x00007f8e9d987000)
        libnnz11.so => /u01/app/oracle/product/11.2.0.3/db_1/lib/libnnz11.so (0x00007f8e9d5ba000)
        libclntsh.so.11.1 => /u01/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so.11.1 (0x00007f8e9ac23000)
        libggnnzitp.so => /u01/app/oracle/product/gg/ogg11/libggnnzitp.so (0x00007f8e9a4ca000)
        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)
        libaio.so.1 => /usr/lib64/libaio.so.1 (0x00007f8e9a2c7000)
[oracle@rac1 ogg11]$
[oracle@rac1 ogg11]$ ldd extract
        linux-vdso.so.1 =>  (0x00007fff75363000)
        librt.so.1 => /lib64/librt.so.1 (0x00000039b2000000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00000039b1400000)
        libicui18n.so.48 => /u01/app/oracle/product/gg/ogg11/libicui18n.so.48 (0x00007f52d1e8e000)
        libicuuc.so.48 => /u01/app/oracle/product/gg/ogg11/libicuuc.so.48 (0x00007f52d1c10000)
        libicudata.so.48 => /u01/app/oracle/product/gg/ogg11/libicudata.so.48 (0x00007f52d054a000)
        libxerces-c.so.28 => /u01/app/oracle/product/gg/ogg11/libxerces-c.so.28 (0x00007f52d0033000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039b1800000)
        libantlr3c.so => /u01/app/oracle/product/gg/ogg11/libantlr3c.so (0x00007f52cff1c000)
        libgglog.so => /u01/app/oracle/product/gg/ogg11/libgglog.so (0x00007f52cfb74000)
        libggrepo.so => /u01/app/oracle/product/gg/ogg11/libggrepo.so (0x00007f52cf909000)
        libdb-5.2.so => /u01/app/oracle/product/gg/ogg11/libdb-5.2.so (0x00007f52cf66d000)
        libggperf.so => /u01/app/oracle/product/gg/ogg11/libggperf.so (0x00007f52cf43f000)
        libnnz11.so => /u01/app/oracle/product/11.2.0.3/db_1/lib/libnnz11.so (0x00007f52cf072000)
        libclntsh.so.11.1 => /u01/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so.11.1 (0x00007f52cc6db000)
        libggnnzitp.so => /u01/app/oracle/product/gg/ogg11/libggnnzitp.so (0x00007f52cbf82000)
        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)
        libaio.so.1 => /usr/lib64/libaio.so.1 (0x00007f52cbd7f000)
[oracle@rac1 ogg11]$
[oracle@rac1 ogg11]$ ldd replicat
        linux-vdso.so.1 =>  (0x00007fff7f3c6000)
        librt.so.1 => /lib64/librt.so.1 (0x00000039b2000000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00000039b1400000)
        libgglog.so => /u01/app/oracle/product/gg/ogg11/libgglog.so (0x00007ffc2ad0e000)
        libggrepo.so => /u01/app/oracle/product/gg/ogg11/libggrepo.so (0x00007ffc2aaa3000)
        libdb-5.2.so => /u01/app/oracle/product/gg/ogg11/libdb-5.2.so (0x00007ffc2a807000)
        libggperf.so => /u01/app/oracle/product/gg/ogg11/libggperf.so (0x00007ffc2a5d9000)
        libicui18n.so.48 => /u01/app/oracle/product/gg/ogg11/libicui18n.so.48 (0x00007ffc2a2cb000)
        libicuuc.so.48 => /u01/app/oracle/product/gg/ogg11/libicuuc.so.48 (0x00007ffc2a04c000)
        libicudata.so.48 => /u01/app/oracle/product/gg/ogg11/libicudata.so.48 (0x00007ffc28987000)
        libxerces-c.so.28 => /u01/app/oracle/product/gg/ogg11/libxerces-c.so.28 (0x00007ffc28470000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039b1800000)
        libantlr3c.so => /u01/app/oracle/product/gg/ogg11/libantlr3c.so (0x00007ffc28359000)
        libnnz11.so => /u01/app/oracle/product/11.2.0.3/db_1/lib/libnnz11.so (0x00007ffc27f8c000)
        libclntsh.so.11.1 => /u01/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so.11.1 (0x00007ffc255f5000)
        libggnnzitp.so => /u01/app/oracle/product/gg/ogg11/libggnnzitp.so (0x00007ffc24e9c000)
        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)
        libaio.so.1 => /usr/lib64/libaio.so.1 (0x00007ffc24c99000)
[oracle@rac1 ogg11]$
[oracle@rac1 ogg11]$ ldd ggsci
        linux-vdso.so.1 =>  (0x00007fff947ff000)
        librt.so.1 => /lib64/librt.so.1 (0x00000039b2000000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00000039b1400000)
        libgglog.so => /u01/app/oracle/product/gg/ogg11/libgglog.so (0x00007f4bd913f000)
        libggrepo.so => /u01/app/oracle/product/gg/ogg11/libggrepo.so (0x00007f4bd8ed4000)
        libdb-5.2.so => /u01/app/oracle/product/gg/ogg11/libdb-5.2.so (0x00007f4bd8c38000)
        libggperf.so => /u01/app/oracle/product/gg/ogg11/libggperf.so (0x00007f4bd8a0a000)
        libicui18n.so.48 => /u01/app/oracle/product/gg/ogg11/libicui18n.so.48 (0x00007f4bd86fc000)
        libicuuc.so.48 => /u01/app/oracle/product/gg/ogg11/libicuuc.so.48 (0x00007f4bd847d000)
        libicudata.so.48 => /u01/app/oracle/product/gg/ogg11/libicudata.so.48 (0x00007f4bd6db8000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039b1800000)
        libxerces-c.so.28 => /u01/app/oracle/product/gg/ogg11/libxerces-c.so.28 (0x00007f4bd68a0000)
        libantlr3c.so => /u01/app/oracle/product/gg/ogg11/libantlr3c.so (0x00007f4bd678a000)
        libnnz11.so => /u01/app/oracle/product/11.2.0.3/db_1/lib/libnnz11.so (0x00007f4bd63bd000)
        libclntsh.so.11.1 => /u01/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so.11.1 (0x00007f4bd3a26000)
        libggnnzitp.so => /u01/app/oracle/product/gg/ogg11/libggnnzitp.so (0x00007f4bd32cd000)
        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)
        libaio.so.1 => /usr/lib64/libaio.so.1 (0x00007f4bd30ca000)
[oracle@rac1 ogg11]$


6. Perform database prerequisites

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+TEST/br8dba/datafile/users.259.924476255
+TEST/br8dba/datafile/undotbs1.260.924476255
+TEST/br8dba/datafile/sysaux.258.924476255
+TEST/br8dba/datafile/system.256.924476253
+TEST/br8dba/datafile/undotbs2.263.924476255
+TEST/br8dba/datafile/undotbs3.262.924476255
+TEST/br8dba/datafile/ggadmin.261.924476255

7 rows selected.

SQL> CREATE TABLESPACE GATE DATAFILE '+TEST' SIZE 100M AUTOEXTEND ON MAXSIZE 30g;

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>


7. Create the required working directories for GoldenGate

[oracle@rac1 ogg11]$ pwd
/u01/app/oracle/product/gg/ogg11
[oracle@rac1 ogg11]$ ggsci <----

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

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

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

Creating subdirectories under current directory /u01/app/oracle/product/gg/ogg11

Parameter files                /u01/app/oracle/product/gg/ogg11/dirprm: already exists
Report files                   /u01/app/oracle/product/gg/ogg11/dirrpt: created
Checkpoint files               /u01/app/oracle/product/gg/ogg11/dirchk: created
Process status files           /u01/app/oracle/product/gg/ogg11/dirpcs: created
SQL script files               /u01/app/oracle/product/gg/ogg11/dirsql: created
Database definitions files     /u01/app/oracle/product/gg/ogg11/dirdef: created
Extract data files             /u01/app/oracle/product/gg/ogg11/dirdat: created
Temporary files                /u01/app/oracle/product/gg/ogg11/dirtmp: created
Credential store files         /u01/app/oracle/product/gg/ogg11/dircrd: created
Masterkey wallet files         /u01/app/oracle/product/gg/ogg11/dirwlt: created
Dump files                     /u01/app/oracle/product/gg/ogg11/dirdmp: created


GGSCI (rac1.rajasekhar.com) 2>

GoldenGate 12C 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.

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

DDL Replication

AIM: ONE-WAY DDL replication between an Oracle 10gR2 database and an Oracle 11gR2 database.

1. Pre-requisites
2. Stop GoldenGate Process on Source Oracle 10gR2
3. Stop GoldenGate Process on Target Oracle 11gR2

4. Configure DDL setup on Source Oracle 10gR2

      A) Turn off the Oracle Recycle Bin
      B) Grant GRANT EXECUTE ON UTL_FILE to GATE
      C) Run marker_setup script
      D) Run ddl_setup script
      E) Run role_setup script
      F) Run the ddl_enable script
      G) Run ddl_pin script
      H) Edit Extract (E10G)

5. Configure DDL setup on Target Oracle 11gR2

      A) Run role_setup.sql
      B) Grant DBA to GATE
      C) ENABLE_GOLDENGATE_REPLICATION to TRUE
      D) Edit Replicat R11G

6. Start GoldenGate Process on Source Oracle 10gR2
7. Start GoldenGate Process on Target Oracle 11gR2

8. Verify

LIST AVAILABLE TABLES CURRENTLY

       a1) List tables on Source 10gR2
       a2) List tables on Target 11gR2

CREATE TABLE OPERATION

        b1) Execute CREATE TABLE on the Source Oracle Database 10gR2
        b2) Verify CREATE TABLE Changes on Target Oracle Database 11gR2

ALTER TABLE OPERATION

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

DROP TABLE OPERATION

        d1) Execute DROP TABLE on the Source Oracle Database 10gR2
        d2) Verify DROP TABLE Changes on Target Oracle Database 11gR2

TRUNCATE TABLE OPERATION

        e1) Execute TRUNCATE TABLE on the Source Oracle Database 10gR2
        e2) Verify TRUNCATE TABLE Changes on Target Oracle Database 11gR2


1. Pre-requisites


2. Stop GoldenGate Process on Source Oracle 10gR2

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E10G        57:25:09      00:00:02
EXTRACT     RUNNING     P10G        00:00:00      57:25:04


GGSCI (rac1.rajasekhar.com) 5>
GGSCI (rac1.rajasekhar.com) 9> stop extract P10G

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


GGSCI (rac1.rajasekhar.com) 10> stop extract E10G

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


GGSCI (rac1.rajasekhar.com) 11> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.


GGSCI (rac1.rajasekhar.com) 12>


3. Stop GoldenGate Process on Target Oracle 11gR2

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

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

GGSCI (rac1.rajasekhar.com) 5>

GGSCI (rac1.rajasekhar.com) 6> stop REPLICAT R11G

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


GGSCI (rac1.rajasekhar.com) 7> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.


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:00:09

GGSCI (rac1.rajasekhar.com) 9>


4. Configure DDL setup on Source Oracle 10gR2


A) Turn off the Oracle Recycle Bin.

*** Recycle Bin can be ON is only for Oracle 11g or above

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
DB10      READ WRITE

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

System altered.

SQL>


B) Grant GRANT EXECUTE ON UTL_FILE Privilege to goldengate user

SQL> GRANT EXECUTE ON UTL_FILE TO GATE;

Grant succeeded.

SQL>


C) Run marker_setup script

[oracle@rac1 10gogg]$ sqlplus / as sysdba

SQL> @marker_setup.sql;

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:gate  <---


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GATE

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.
SQL>


D) Run ddl_setup script

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/
[oracle@rac1 10gogg]$ sqlplus / as sysdba

SQL> @ddl_setup.sql;

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:gate <----

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using GATE as a Oracle GoldenGate schema name.

Working, please wait ...

RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes <----


DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GATE

CLEAR_TRACE STATUS:

Line/pos   Error
---------- ------------------
No errors  No errors

CREATE_TRACE STATUS:

Line/pos   Error
---------- ------------------
No errors  No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error
---------- ------------------
No errors  No errors

INITIAL_SETUP STATUS:

Line/pos   Error
---------- ------------------
No errors  No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos   Error
---------- -------------------------------
No errors  No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos   Error
---------- -------------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos   Error
---------- -------------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos   Error
---------- -------------------------------
No errors  No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED

STAYMETADATA IN TRIGGER
-----------------------------------
OFF

DDL TRIGGER SQL TRACING
-----------------------------------
0

DDL TRIGGER TRACE LEVEL
-----------------------------------
0

LOCATION OF DDL TRACE FILE
----------------------------------------------------------------
/u01/app/oracle/product/10g/admin/DB10/udump/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
----------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL>


E) Run role_setup script

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/
[oracle@rac1 10gogg]$ sqlplus / as sysdba

SQL> @role_setup.sql;

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:gate <-----
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where  is the user assigned to the GoldenGate processes.
SQL>

-- Run below command as advised by above script

SQL> GRANT GGS_GGSUSER_ROLE TO gate;  <----

Grant succeeded.

SQL>


F) Run the ddl_enable script

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/
[oracle@rac1 10gogg]$ sqlplus / as sysdba

SQL> @ddl_enable.sql;

Trigger altered.

SQL>


G) Run ddl_pin script

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/
[oracle@rac1 10gogg]$ sqlplus / as sysdba

SQL> @ddl_pin.sql gate <-----

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL>


H) Edit Extract (E10G)

GGSCI (rac1.rajasekhar.com) 3> edit params R11G

-- Add below parameters
ddl include mapped objname cds.*
DDLOPTIONS ADDTRANDATA REPORT

GGSCI (rac1.rajasekhar.com) 4> view params E10G

EXTRACT E10G
EXTTRAIL ./dirdat/ea
USERID gate, PASSWORD gate
TABLE CDS.*;
-- Enable DDL
ddl include mapped objname cds.*
DDLOPTIONS ADDTRANDATA REPORT

GGSCI (rac1.rajasekhar.com) 5>


5. Configure DDL setup on Target Oracle 11gR2


A) Run role_setup.sql

-- conn / as sysdba

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:gate
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where  is the user assigned to the GoldenGate processes.
SQL>

SQL> GRANT GGS_GGSUSER_ROLE TO GATE; <---

Grant succeeded.

SQL>


B) Grant DBA to GATE

SQL> grant dba to gate;

Grant succeeded.

SQL> 


C) ENABLE_GOLDENGATE_REPLICATION to TRUE

SQL> show parameter ENABLE_GOLDENGATE_REPLICATION

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------                                                                
enable_goldengate_replication        boolean     FALSE <----

SQL> alter system set enable_goldengate_replication=TRUE;

System altered.

SQL>


D) Edit Replicat R11G

-- Add below parameters

-- DDL Report
DDLOPTIONS REPORT

GGSCI (rac1.rajasekhar.com) 52> view params r11g

REPLICAT R11G
ASSUMETARGETDEFS
userid gate, password gate
DISCARDFILE ./dirout/receiver.dsc, purge
MAP CDS.*, TARGET CDS.*;
-- DDL Report
DDLOPTIONS REPORT

GGSCI (rac1.rajasekhar.com) 53>


6. Start GoldenGate Process on Source Oracle 10gR2

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

Manager started.

GGSCI (rac1.rajasekhar.com) 6> start extract E10G

Sending START request to MANAGER ...
EXTRACT E10G starting

GGSCI (rac1.rajasekhar.com) 7> start extract P10G

Sending START request to MANAGER ...
EXTRACT P10G starting

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E10G        06:57:03      00:00:07
EXTRACT     RUNNING     P10G        00:00:00      06:57:13

GGSCI (rac1.rajasekhar.com) 9>


7. Start GoldenGate Process on Target Oracle 11gR2

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

Manager started.

GGSCI (rac1.rajasekhar.com) 6> start replicat R11G

Sending START request to MANAGER ...
REPLICAT R11G starting

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

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

GGSCI (rac1.rajasekhar.com) 8>


8. Verify


LIST AVAILABLE TABLES CURRENTLY


a1) List tables on Source 10gR2

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
TEST                           TABLE

SQL>


a2) List tables on Target 11gR2

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
TEST                           TABLE

SQL>


CREATE TABLE OPERATION


b1) Execute CREATE TABLE on the Source Oracle Database 10gR2

SQL> create table b as select * from a;

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
TEST                           TABLE
B                              TABLE <-----

SQL>

SQL> select count(*) from b;

  COUNT(*)
----------
     50605  <----

SQL>


b2) Verify CREATE TABLE Changes on Target Oracle Database 11gR2

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
B                              TABLE <-----
TEST                           TABLE

SQL> select count(*) from b;

  COUNT(*)
----------
     50605 <---

SQL>


ALTER TABLE OPERATION

c1) Execute ALTER TABLE on the Source Oracle Database 10gR2

SQL> desc test
 Name           Null?    Type
 -------------- -------- -------------
 NAME           NOT NULL VARCHAR2(10)
 ROLE                    VARCHAR2(10)

SQL> ALTER TABLE TEST ADD EMPNO VARCHAR2(30);

Table altered.

SQL> 


c2) Verify ALTER TABLE Changes on Target Oracle Database 11gR2

SQL> desc test;
 Name           Null?    Type
 -------------- -------- -------------
 NAME           NOT NULL VARCHAR2(10)
 ROLE                    VARCHAR2(10)
 EMPNO                   VARCHAR2(30) <---

SQL>


DROP TABLE OPERATION


d1) Execute DROP TABLE on the Source Oracle Database 10gR2

SQL> drop table b;

Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
TEST                           TABLE

SQL> 


d2) Verify DROP TABLE Changes on Target Oracle Database 11gR2

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
BIN$QnU5q0BiTuPgU2UCqMCWyg==$0 TABLE <----
TEST                           TABLE

SQL>

SQL> show parameter recyclebin

NAME         TYPE        VALUE
------------ ----------- -------
recyclebin   string      on <-----
SQL>


TRUNCATE TABLE OPERATION


e1) Execute TRUNCATE TABLE on the Source Oracle Database 10gR2

SQL> truncate table test;

Table truncated.

SQL> select count(*) from test;

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

SQL>


e2) Verify TRUNCATE TABLE Changes on Target Oracle Database 11gR2

SQL> select count(*) from test;

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

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

Reference:

https://blogs.oracle.com/imc/entry/oracle_goldengate_configuring_ddl_replication
http://www.vitalsofttech.com/configure-goldengate-ddl-replication/
http://goldengatereplication.blogspot.in/2011/12/steps-for-enabling-ddl-replication-with.html
http://www.askmaclean.com/archives/goldengate-best-parameters-test-from-maclean.html
http://gavinsoorma.com/2014/06/adding-new-tables-to-a-goldengate-extract-and-using-the-tableexclude-parameter/
https://www.pythian.com/blog/truncates-sequences-replication-oracle-goldengate/
http://anuj-singh.blogspot.in/2014/11/ora-04098-trigger-sysggsddltriggerbefor.html
http://www.oracle-scn.com/ddl-replication-configuration-of-oracle-goldengate-11g/
http://www.juliandyke.com/Research/GoldenGate/GoldenGateDDLReplication.php

Zero Downtime Migration

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


3. GoldenGate Installation on Target Oracle 11gR2


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

Unidirectional DML Replication

Note: All Datafiles, Control files, Redo logs and Archive logs under ASM file system on both Source and Target

Configure Oracle GoldenGate to perform DML one-way replication between an Oracle 10gR2 database to an Oracle 11gR2 database.

Contents
___________________________________________________________________________________________________________________________________

0. Environment
1. Configure TNS Entries on Source Oracle 10gR2
2. Configure TNS Entries on Target Oracle 11gR2
3. Create test schemas for replication on Source and Target
4. GoldenGate Installation on Source and Target
5. OGG Configuration on Source Oracle 10gR2
     a) Login to Database
     b) Configure Manager
     c) Create Extract Group
     d) Create Pump Extract Group
     e) Add supplemental logging for table

6. OGG Configuration on Target Oracle 11gR2
     a) Login to Database
     b) Configure Manager
     c) 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
8. On Source Start Manager, Extract and Pump
9. On Target Start the Manager and Replicat
10. Test and Verify the Results

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

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

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

___________________________________________________________________________________________________________________________________


0. Environment

	Source:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.10
		DB Version: Oracle 10.2.0.5, File system: ASM, All db files on ASM.
		DB Name: RCM
		GoldenGate Version: V11.2.x for oracle db 10g
		GoldenGate Schema: GATE
		Source Database Schema: SENDER
		
	Target:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.11
		DB Version: Oracle 11.2.0.3, , File system: ASM,  All db files on ASM.
		DB Name: MGM
		GoldenGate Version: V11.2.x for oracle db 11g
		GoldenGate Schema: GATE
		Target Database Schema: RECEIVER


1. Configure TNS Entries on source Oracle 10gR2

[oracle@rac1 ~]$ ps -ef | grep tns
root         9     2  0 10:46 ?        00:00:00 [netns]
oracle    6334     1  0 15:11 ?        00:00:00 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle    8326  4131  0 23:53 pts/2    00:00:00 grep tns
[oracle@rac1 ~]$

[oracle@rac1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 24-JUL-2015 00:03:58

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.10)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                24-JUL-2015 00:03:18
Uptime                    0 days 0 hr. 0 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.10)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "rcm" has 1 instance(s).
  Instance "rcm", status READY, has 1 handler(s) for this service...
Service "rcmXDB" has 1 instance(s).
  Instance "rcm", status READY, has 1 handler(s) for this service...
Service "rcm_XPT" has 1 instance(s).
  Instance "rcm", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac1 admin]$ cat listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME       = +ASM)
      (ORACLE_HOME    = /u01/app/oracle/product/10.2.0/db_1)
    )
  )
[oracle@rac1 admin]$
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ASM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = +ASM)
    )
  )

MGM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mgm)
    )
  )

RCM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rcm)
    )
  )

[oracle@rac1 admin]$

[oracle@rac1 ~]$ sqlplus sys/sys@rcm as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jul 23 23:39:57 2015
Copyright (c) 1982, 2010, 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

SQL>

[oracle@rac1 ~]$ sqlplus sys/sys@mgm as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jul 23 23:40:29 2015
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>

[oracle@rac1 ~]$ sqlplus sys/sys@asm as sysdba << Trying to connect to ASM instance. >>
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jul 23 23:41:11 2015 Copyright (c) 1982, 2010, 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 SQL> select NAME, STATE from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
VOL                            MOUNTED

SQL>


2. Configure TNS Entries on Target Oracle 11gR2

[oracle@rac2 admin]$ ps -ef | grep tns
root         9     2  0 10:46 ?        00:00:00 [netns]
oracle    8335     1  0 17:04 ?        00:00:00 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle   12023  9019  0 23:50 pts/2    00:00:00 grep tns
[oracle@rac2 admin]$

[oracle@rac2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-JUL-2015 00:08:45

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                23-JUL-2015 17:04:34
Uptime                    0 days 7 hr. 4 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "mgm" has 1 instance(s).
  Instance "mgm", status READY, has 1 handler(s) for this service...
Service "mgmXDB" has 1 instance(s).
  Instance "mgm", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$

[oracle@rac2 ~]$ cd /u01/app/11.2.0/grid/network/admin/
[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
[oracle@rac2 admin]$

[oracle@rac2 ~]$  cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/11.2.0/grid)
      (SID_NAME = rcm)
    )
    (SID_DESC =
      (ORACLE_HOME = /u01/app/11.2.0/grid)
      (SID = +ASM)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.11)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

ASM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = +ASM)
    )
  )

MGM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mgm)
    )
  )

RCM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rcm)
    )
  )

[oracle@rac2 admin]$ 

[oracle@rac2 admin]$ sqlplus sys/sys@rcm as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 23 23:49:44 2015
Copyright (c) 1982, 2011, 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

SQL>

[oracle@rac2 admin]$ sqlplus sys/sys@mgm as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 23 23:50:04 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>

[oracle@rac2 admin]$ sqlplus sys/sys@asm as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 23 23:50:22 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL> select NAME, STATE from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DATA                           MOUNTED

SQL>


3. Create test schemas for replication on Source and Target

On Source Oracle Database 10gR2

Create test schemas for replication. I will create a replication from schema sender to schema receiver

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jul 23 11:41:21 2015

Copyright (c) 1982, 2010, 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

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

User created.

SQL> grant connect,resource,unlimited tablespace to sender;

Grant succeeded.

[oracle@rac1 ~]$ sqlplus sender/sender;

SQL> create table sugi(Name varchar2(10), Role varchar2(10));

Table created.

SQL>

On Target Oracle Database 11gR2

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 23 11:43:14 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

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

User created.

SQL> grant connect,resource,unlimited tablespace to receiver;

Grant succeeded.

SQL> create table sugi(Name varchar2(10), Role varchar2(10));

Table created.

SQL>


4. GoldenGate Installation on Source and Target

Please click here 

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


5. OGG Configuration on Source Oracle 10gR2

a) Login to Database

[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> dblogin userid gate, password gate
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2>


b) 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 entries 
PORT 15000

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

PORT 15000

GGSCI (rac1.rajasekhar.com) 5>


c) Create Extract Group

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

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

GGSCI (rac1.rajasekhar.com) 10> edit param e10g

-- Add below entries
EXTRACT e10g
USERID gate, PASSWORD gate
EXTTRAIL ./dirdat/ea
TRANLOGOPTIONS ASMUSER SYS@ASM, ASMPASSWORD sys << Note: If REDO logs in ASM, Then must to add this entry here >>
TABLE sender.*; 

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

EXTRACT e10g
USERID gate, PASSWORD gate
EXTTRAIL ./dirdat/ea
TRANLOGOPTIONS ASMUSER SYS@ASM, ASMPASSWORD sys
TABLE sender.*;

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     E10G        00:00:00      00:01:38

GGSCI (rac1.rajasekhar.com) 13>


d) Create Pump Group

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

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

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

-- Add below entries

EXTRACT p10g
USERID gate, password gate
RMTHOST 192.168.2.11, mgrport 15000
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE sender.*;

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

EXTRACT p10g
USERID gate, password gate
RMTHOST 192.168.2.11, mgrport 15000
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE sender.*;

GGSCI (rac1.rajasekhar.com) 17>


e) Add supplemental logging for table

GGSCI (rac1.rajasekhar.com) 37> ADD TRANDATA SENDER.*

2015-07-23 14:21:43  WARNING OGG-00869  No unique key is defined for table 'SUGI'. 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 SENDER.SUGI.

GGSCI (rac1.rajasekhar.com) 38> INFO TRANDATA SENDER.SUGI

Logging of supplemental redo log data is enabled for table SENDER.SUGI.

Columns supplementally logged for table SENDER.SUGI: NAME, ROLE.

GGSCI (rac1.rajasekhar.com) 39>


6. OGG Configuration on Target Oracle 11gR2

a) Login to Database

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

GGSCI (rac2.rajasekhar.com) 2>


b) Configure Manager

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

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

-- Add below entry 
PORT 15000

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

PORT 15000

GGSCI (rac2.rajasekhar.com) 5> 


c) Set up the Checkpoint table

i) Create a GLOBALS file

— Global file need to create on target where replicat process running
— Checkpoint tables are only used by the Replicat, so no need to create on source because it’s one-way replication.

GGSCI (rac2.rajasekhar.com) 5>  EDIT PARAMS ./GLOBALS
-- Add below entries
GGSCHEMA GATE
CHECKPOINTTABLE GATE.CHECKPOINT

GGSCI (rac2.rajasekhar.com) 6> sh cat ./GLOBALS

GGSCHEMA GATE
CHECKPOINTTABLE GATE.CHECKPOINT

GGSCI (rac2.rajasekhar.com) 7>


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 (rac2.rajasekhar.com) 7> EXIT


iii) Add a Replicat checkpoint table

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

GGSCI (rac2.rajasekhar.com) 2>

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

Successfully created checkpoint table GATE.CHECKPOINT.

GGSCI (rac2.rajasekhar.com) 3>


7) Create the Replicat Group

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

GGSCI (rac2.rajasekhar.com) 4> edit param r11g
-- Add below 
REPLICAT R11G
ASSUMETARGETDEFS
userid gate, password gate
DISCARDFILE ./dirout/receiver.dsc, purge
MAP sender.*, TARGET receiver.*;

GGSCI (rac2.rajasekhar.com) 5> view param r11g

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


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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
REPLICAT    STOPPED     R11G        00:00:00      00:00:44


GGSCI (rac2.rajasekhar.com) 7>


8. On Source Start Manager, Extract and Pump

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     E10G        00:00:00      00:24:30
EXTRACT     STOPPED     P10G        00:00:00      00:21:12

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

Manager started.

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

Sending START request to MANAGER ...
EXTRACT E10G starting

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

Sending START request to MANAGER ...
EXTRACT P10G starting

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E10G        00:00:00      00:24:54
EXTRACT     RUNNING     P10G        00:00:00      00:21:36

GGSCI (rac1.rajasekhar.com) 44>


9. On Target start the Manager and Replicat

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
REPLICAT    STOPPED     R11G        00:00:00      00:00:44


GGSCI (rac2.rajasekhar.com) 8> start mgr

Manager started.


GGSCI (rac2.rajasekhar.com) 9> start replicat R11G

Sending START request to MANAGER ...
REPLICAT R11G starting


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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING      R11G        00:00:00      00:08:04


GGSCI (rac2.rajasekhar.com) 11>


10. Test and Verify the Results

INSERT OPERATION

a1) Execute Insert on the Source Oracle Database 10gR2

[oracle@rac1 ~]$ sqlplus sender/sender;

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jul 24 02:31:44 2015

Copyright (c) 1982, 2010, 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

SQL> 
SQL> select * from sugi;

no rows selected

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> select * from sugi;

NAME       ROLE
---------- ----------
SUGI       DBA 
TEJA       DBA 
VIJAY      DBA 
SATYA      DBA 
RAJ        DBA 
MOHAN      DBA
AMITH      DBA
PAVAN      DBA 
PREM       DBA 
9 rows selected. 
SQL>


a2) Verify Insert Changes on Target Oracle Database 11gR2

SQL> conn receiver/receiver;
Connected.
SQL> select * from sugi;

no rows selected

SQL>
SQL>
SQL> /

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

9 rows selected.

SQL>


UPDATE OPERATION

b1) Execute update on the Source Oracle Database 10gR2

SQL> show user
USER is "SENDER"
SQL> update sugi set role='PSE' where name='VIJAY';

1 row updated.

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

1 row updated.

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

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from sugi;

NAME       ROLE
---------- ----------
SUGI       DBA
TEJA       DBA
VIJAY      PSE 
SATYA      PSE 
MOHAN      DBA
AMITH      DBA
RAJ        DBA
PAVAN      PSE 
PREM       DBA

9 rows selected.

SQL>


b2) Verify Update Changes on Target Oracle Database 11gR2

SQL> show user
USER is "RECEIVER"
SQL> select * from sugi;

NAME       ROLE
---------- ----------
SUGI       DBA
TEJA       DBA
VIJAY      PSE 
SATYA      PSE 
MOHAN      DBA
AMITH      DBA
RAJ        DBA
PAVAN      PSE 
PREM       DBA

9 rows selected.

SQL>


DELETE OPERATION

c1) Execute Delete on the Source Oracle Database 10gR2

SQL> show user
USER is "SENDER"
SQL> delete from sugi where name='SUGI';

1 row deleted.

SQL> delete from sugi where name='TEJA';

1 row deleted.

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

1 row deleted.

SQL> commit;

Commit complete.

SQL>
SQL> select * from sugi;

NAME       ROLE
---------- ----------
VIJAY      PSE
SATYA      PSE
MOHAN      DBA
AMITH      DBA
PAVAN      PSE
PREM       DBA

6 rows selected.

SQL>


c2) Verify Delete Changes on Target Oracle Database 11gR2

SQL> show user
USER is "RECEIVER"
SQL> select * from sugi;

NAME       ROLE
---------- ----------
VIJAY      PSE
SATYA      PSE
MOHAN      DBA
AMITH      DBA
PAVAN      PSE
PREM       DBA

6 rows selected.

SQL>

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.

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


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.