Oracle Pro Labs

Blog on my Oracle Experience with realtime projects

Golden Gate Veridata

Oracle GoldenGate Installation [10g to 12c Migration]

Bymithun

Dec 22, 2019

In this Post i will show how we done a Oracle GoldenGate unidirectional replication setup done for the Data Migration from 10g to 12c. Source database is 10g EE on and target database with oracle 12cEE_database both source and target instance are running on Two node RAC. Source we have four database instances(DB1, DB2, DB3 & DB4) for which we will be replicating one schema from each database instance. Target we have only one database instance orcl.

Check for the certified Oracle gate version from Oracle Support. Here we are using Oracle GoldenGate versions 11.2.1.0.1 for 10.2.0.4 database and OGG 12.3 version for 12.2 database that is certified to use.

Referred Documents

Oracle GoldenGate 12c installation & Configuration Guide for Oracle Databases

Oracle® GoldenGate Oracle Installation and Setup Guide 11g

Connectivity Matrix, Both source and target should be reachable

Download Oracle Golden gate package from www.edelivery.oracle.com oracle and copy to both Source. Target.

Patch On TARGET 12c OGG to resolve bug 28011195

Below patch need to be applied on target to avoid replicat getting abend due to port scans. With out Patch Oracle Golden-gate service on 12c target will get terminate when dp port sdcan, either

GOLDENGATE CONFIGURATION DETAILS

Source database schemas are mapped to the target as per the above target schema mapping

  1. OS USER PREREQUISITE

For best practice we will create a separated operating system on the servers for the GoldenGate installation and configuration by executing the below command as root user. The command need to be executed on all source nodes as well as target nodes.

useradd -g oinstall -G dba,oinstall ggate
passwd ggate

Verify on both the nodes the user created with same UID
orcl10g1:/u01 # id ggate
uid=1001(ggate) gid=100(users) groups=107(oinstall),108(dba),100(users)

orcl10g2:/u01 # id ggate
uid=1001(ggate) gid=100(users) groups=107(oinstall),108(dba),100(users)

  1. GOLDEN GATE DIRECTORY CREATION

Create directory on source “/u01/”, where /u01 is shared disk mounted and accessible from both the Production nodes.
And grant required permission to ggate OS user
mkdir /u01/ggate
chown –R ggate:oinstall /u01/ggate
chmod 777 /u01/ggate
mkdir -p /u01/ggate/oradata/
chown -R oracle:oinstall /u01/ggate/oradata

  1. DATABASE PRE-REQUISITE

Important – This per-requisite mentioned in this step (a, b, c & d on point 3. DATABASE PER-REQUISITE Need to be executed on all source database wherever Golden-gate replication to be configured, in our case database instances DB1, DB2, DB3, DB4 on production two node RAC need to execute.

a. Enable Database Force logging

Make sure database force_logging mode is enabled by checking the below query, If logging is not enable enable it by executing alter command mentioned below.

select log_mode,force_logging from v$database;
alter database force logging;

b.Enable Supplemental Logging

In the database operating as a source, verify database-Level supplemental logging is enabled if not execute the alter statement. To see if supplemental logging is enabled execute below command

SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database; 

Execute below commands to enable supplemental logging

alter database add supplemental log data;
alter database add supplemental log data (all) columns

c. Create tablespace for GoldenGate User

Create tablespace for GoldenGate user, the tablespace created will be the default tablespace for the Goldengate User as well as the Veridata user.

CREATE SMALLFILE
TABLESPACE “GG_DATA_TBS”
LOGGING
DATAFILE ‘/u01/ggate/oradata/gg_DATA_DB1_tbs01.dbf’ SIZE
1024M REUSE AUTOEXTEND
ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ;

d. Create database user for GoldenGate configuration and administraiton

create user ggate identified by gg3t3 default tablespace GG_DATA_TBS quota unlimited on GG_DATA_TBS;
GRANT CREATE SESSION, ALTER SESSION TO ggate;
GRANT CONNECT,RESOURCE TO ggate;
GRANT SELECT ANY DICTIONARY TO ggate;
GRANT ALTER SYSTEM TO ggate;
GRANT FLASHBACK ANY TABLE TO ggate;
GRANT EXECUTE on DBMS_FLASHBACK to ggate;
GRANT SELECT ANY TRANSACTION to ggate;
GRANT SELECT on dba_clusters to ggate;
GRANT SELECT ANY TABLE TO ggate;
/–To avoid below error require grant ALTER ANY TABLE TO ggate/
/–GGSCI (ogg10g) 9> add trandata scott./
/–Database error 1031 (ORA-01031: insufficient privileges SQL ALTER TABLE ““.”BONUS”/ /SUPPLEMENTAL LOG GROUP “GGS_51153” /* GOLDENGATE_DDL_REPLICATION /)./

GRANT ALTER ANY TABLE TO ggate;

e. UNDOTABLESPACE CREATION AND UNDO RETENTION

Make sure undo_retention size is configured enough so while taking EXPDP backup with flashback_scn the export fill not fail with below errorerror
ORA-31693: Table data object “USER”.”TABLE” failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 154 with name “_SYSSMU1$” too small

We have increased retention and configured for database with databases DB1, DB2, DB3, DB4

export ORACLE_SID=DB1
sqlplus “/as sysdba”
alter system set undo_retention=1800 scope=both

export ORACLE_SID=DB2
sqlplus “/as sysdba”
alter system set undo_retention=86400 scope=both

export ORACLE_SID=DB3
sqlplus “/as sysdba”
alter system set undo_retention=200000 scope=both

export ORACLE_SID=DB3
sqlplus “/as sysdba”
alter system set undo_retention=200000 scope=both

TARGET PRE-REQUISITE

orcl12c1:/u01 # id ggate
uid=1001(ggate) gid=100(users) groups=107(oinstall),108(dba),100(users)

Set PATH, Oracl Home and SID

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/oracle/app/product/12.2.0/dbhome_1/bin:/u02/ogg12c
export ORACLE_HOME=/oracle/app/product/12.2.0/dbhome_1
export ORACLE_SID= orcl12c1
export PATH
export LD_LIBRARY_PATH=/oracle/app/product/12.2.0/dbhome_1/lib/:/u02/ogg12c

Need to create Directory for the OGG Installation and configuration on our target database resides on

mkdir /u01/ggate
chown –R ggate:oinstall /u01/ggate
chmod 777 /u01/ggate

  1. Database Pr-requisite on Target 12c Database

create tablespace gg_DATA_tbs datafile ‘+DATA size 1G reuse autoextend on;
alter system set ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
create user ggate identified by gg3t3 default tablespace gg_DATA_tbs quota unlimited on gg_DATA_tbs;
GRANT CREATE SESSION, ALTER SESSION TO ggate;
GRANT CONNECT,RESOURCE TO ggate;
GRANT SELECT ANY DICTIONARY TO ggate;
GRANT CREATE TABLE TO ggate;
GRANT CREATE ANY TABLE TO ggate;
GRANT LOCK ANY TABLE TO ggate;
GRANT SELECT ANY TABLE TO ggate;
GRANT insert any table to ggate;
grant update any table to ggate;
grant delete any table to ggate;
exec dbms_goldengate_auth.grant_admin_privilege(‘GGATE’);

SOURCE OGG SOFTWARE INSTALLATION
Copy the oracle golden-gate binary (OGG11_V32400-01_source.zip) to the Source server (oracle 10g node 1) location /u01/ggate/ and unzip the software from ggate OS user

ggate@*:/u01/ggate> unzip OGG11_V32400-01_source.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.

Execute below command to install Oracle GoldenGate on Source
tar -xvf fbo_ggs_Linux_x64_ora10g_64bit.tar

CREATE GOLDENGATE SUB DIRECTORIES
cd /u01/ggate
./ggsci
GGSCI (orcl10g1) 1> create subdirs
Creating subdirectories under current directory /u01/ggate
Parameter files /u01/ggate/dirprm: already exists
Report files /u01/ggate/dirrpt: created
Checkpoint files /u01/ggate/dirchk: created
Process status files /u01/ggate/dirpcs: created
SQL script files /u01/ggate/dirsql: created
Database definitions files /u01/ggate/dirdef: created
Extract data files /u01/ggate/dirdat: created
Temporary files /u01/ggate/dirtmp: created
Stdout files /u01/ggate/dirout: created


Configure Manager
GGSCI (orcl10g1) 1> edit param ./globals
GGSCHEMA ggate
ALLOWOUTPUTDIR /u01/ggate/dirdat
TRAIL_SEQLEN_6D

GGSCI (orcl10g1) 2> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7839
AUTORESTART er *, RETRIES 6, WAITMINUTES 1, RESETMINUTES 10
PURGEOLDEXTRACTS ./dirdat/ex, USECHECKPOINTS,MINKEEPDAYS 2

TARGET OGG SOFTWARE INSTALLATION

Copy the oracle Goldengate binary (OGG12c_V975837-01_Target.zip) to the target server location /u01/soft and unzip the software from ggate OS user

Copy the response file on the /u01/soft/fbo_ggs_Linux_x64_shiphome/Disk1/reponse/oggcore.rsp to /u01/fbo_ggs_Linux_x64_shiphome/Disk1/reponse/oggcore.rsp for configuration

Edit the reponse file oggcore.rsp and modify as per the configuration
INSTALL_OPTION=ORA12c
SOFTWARE_LOCATION= /u01/ogg12c
START_MANAGER=true
MANAGER_PORT=7809
DATABASE_LOCATION= /oracle/app/product/12.2.0/dbhome_1
INVENTORY_LOCATION=/oracle/oraInventory

Install GodenGate on target by executing below command

./runInstaller -silent -nowait -responseFile /ggate/soft/ /fbo_ggs_Linux_x64_shiphome/Disk1/oggcore.rsp

Configure target ggate os user profile (vi .bash_profile)
#.bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/oracle/app/product/12.2.0/dbhome_1/bin:/ggate/ogg12c
export ORACLE_HOME=/oracle/app/product/12.2.0/dbhome_1
export ORACLE_SID= ORCL12C1
export PATH
export LD_LIBRARY_PATH=/oracle/app/product/12.2.0/dbhome_1/lib/:/ggate/ogg12c

Patching 12c TARGET to resolve bug 28011195

Patch Apply (Replicate Process abended when to port scan, Telnet *.*.*.*. 7840) For Replicate abended on 12c Do Patch (Port scan issue)

Opatch latest version is required to patch OGG, take backup of OGG Opatch folder and Unzip the latest patch

Login to OGG console and stop all services before doing the patch
./ggsci
–stop all goldengate services
stop *

Rename the Opatch folder of OGG
cd /u01/ogg12c
mv OPatch OPatchold
export PATH=/u01/ogg12c/OPatch:/usr/sbin:/usr/local/bin:/u01/jdk1.8.0_121/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin
unzip p6880880_122010_Linux-x86-64.zip /u01/ogg12c/
opatch version
export ORACLE_HOME=/u01/ogg12c
cd /u01/ogg12c

Changing ownership of the below files for patching
chmod 775 crypto/
chmod 775 crypto
chmod 775 diretc
chmod 775 diretc/
unzip p28203147_123012_Linux-x86-64.zip
cd 28203147/
opatch apply

CONFIGURE MANAGER ON TARGET

GGSCI (ae086dbsv01)1> edit param ./GLOBALS
GGSCHEMA ggate
ALLOWOUTPUTDIR /u01/ogg12c/dirdat
checkpointtable ggate.checkpoint
TRAIL_SEQLEN_6D
USEIPV4

GGSCI (ae086dbsv01)2>edit param mgr
PORT 7809
ACCESSRULE, PROG , IPADDR 192.168.1.*, ALLOW
DYNAMICPORTLIST 7810-7830
AUTOSTART er *
AUTORESTART er , RETRIES 6, WAITMINUTES 1, RESETMINUTES 60 PURGEOLDEXTRACTS ./dirdat/, USECHECKPOINTS, MINKEEPFILES 2

GGSCI (ae086dbsv01)3> DBLOGIN USERID ggate password gg3t3
GGSCI (ae086dbsv01)4> add checkpointtable ggate.checkpoint

Start all Manager services on both Source, Target OGG and check the 7809 port reach ability between Source and target

./ggsci

After login to OGG console execute

cd /ggate/ogg12c

mv OPatch OPatchold

unzip p6880880_122010_Linux-x86-64.zip /u01/app/cd ogg12c/

     opatch version

export ORACLE_HOME=/ggate/ogg12c

cd /ggate/ogg12c

echo $PATH

export PATH=/ggate/ogg12c/OPatch:/usr/sbin:/usr/local/bin:/u01/jdk1.8.0_121/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin

Login to OGG console and stop all services before doing the patch

./ggsci

–stop all goldengate services

stop *

change ownership of files

chmod 775 crypto/

chmod 775 crypto

chmod 775 diretc

chmod 775 diretc/

unzip p28203147_123012_Linux-x86-64.zip

cd 28203147/

opatch apply  

CONFIGURE MANAGER

GGSCI (ae086dbsv01)1> edit  param ./GLOBALS

GGSCHEMA ggate

ALLOWOUTPUTDIR /ggate/ogg12c/dirdat

checkpointtable ggate.checkpoint

TRAIL_SEQLEN_6D

USEIPV4

GGSCI (ae086dbsv01)2>edit param mgr

PORT 7809

ACCESSRULE, PROG *, IPADDR 172.17.84.*, ALLOW

DYNAMICPORTLIST 7810-7830

AUTOSTART er *

AUTORESTART er *, RETRIES 6, WAITMINUTES 1, RESETMINUTES 60

PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPFILES 2

GGSCI (ae086dbsv01)3> DBLOGIN USERID ggate password gg3t3

GGSCI (ae086dbsv01)4> add checkpointtable ggate.checkpoint

Start all goldengate services

./ggsci

Copy the oracle goldengate binary (OGG11_V32400-01_source.zip) to the Source server (oracle 10g node 1) location /u01/ggate/ and unzip the software from ggate OS user

ggate@*****:/u01/ggate> unzip OGG11_V32400-01_source.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.

Execute below command to install Oracle Goldengate on Source

tar -xvf fbo_ggs_Linux_x64_ora10g_64bit.tar

CREATE GOLDENGATE SUB DIRECTORIES

cd /u01/ggate

./ggsci

GGSCI (orcl10g1) 1> create subdirs

Creating subdirectories under current directory /u01/ggate

Parameter files                /u01/ggate/dirprm: already exists

Report files                   /u01/ggate/dirrpt: created

Checkpoint files               /u01/ggate/dirchk: created

Process status files           /u01/ggate/dirpcs: created

SQL script files               /u01/ggate/dirsql: created

Database definitions files     /u01/ggate/dirdef: created

Extract data files             /u01/ggate/dirdat: created

Temporary files                /u01/ggate/dirtmp: created

Stdout files                   /u01/ggate/dirout: created

cd /u01/ggate

./ggsci

Configure Manager

GGSCI (orcl10g1) 1> edit param ./globals

GGSCHEMA ggate

ALLOWOUTPUTDIR /u01/ggate/dirdat

TRAIL_SEQLEN_6D

GGSCI (orcl10g1) 2> edit param mgr

PORT 7809

DYNAMICPORTLIST 7810-7839

AUTORESTART er *, RETRIES 6, WAITMINUTES 1, RESETMINUTES 10

PURGEOLDEXTRACTS ./dirdat/ex, USECHECKPOINTS,MINKEEPDAYS 2

GGSCI (orcl10g1) 3> start mgr

  1. a.    SOURCE ORACLE GOLDENGATE CONFIGURATION

For VIPENROLL schema replication from Source to target, Source Goldengate will have two extract process configured one to capture the change data Extract Name extvip and other pump for pushing the trail file from source to target location pmpvip.

Create test table on SOURCE database for testing the sync after the configuration and sync

CREATE TABLE vipenroll.GGTEST

(

  ID VARCHAR2(20)

, BLOB BLOB

, DESCRIPTION VARCHAR2(20)

);

Login to source OS user ggate and open goldengate command prompt and create goldengate parameter as specified and start the services.

su – ggate

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=DB1

cd /u01/ggate

./ggsci

GGSCI (orcl10g1) 3>dblogin userid ggate password gg3t3

GGSCI (orcl10g1) 4>edit param extrvip

EXTRACT extrvip

SETENV (ORACLE_HOME = “/oracle/product/10.2/db_1”)

SETENV (ORACLE_SID = “DB1”)

USERID ggate, PASSWORD gg3t3

EXTTRAIL ./dirdat/ex

DISCARDFILE ./dirdat/ext_ics.txt, PURGE

DISCARDROLLOVER AT 01:00 ON FRIDAY

TABLE vipenrol.*;

GGSCI (orcl10g1) 4>edit param pmpvip

extract pmpvip

SETENV (ORACLE_SID = “DB1”)

USERID ggate, PASSWORD gg3t3

rmthost 172.19.50.74, MGRPORT 7809

rmttrail ./dirdat/rx

DISCARDFILE ./dirrpt/pump_pbrok.txt, PURGE

PASSTHRU

table vipenrol.*;

Enabling Oracle GoldenGate table-level logging

GGSCI (orcl10g1) 5> DBLOGIN USERID ggate password gg3t3

GGSCI (orcl10g1) 6> add trandata vipenrol.*

can execute info trandata vipenrol.* to check trandata status

Start Goldengate Process On Source

GGSCI (orcl10g1) 1> dblogin userid ggate password gg3t3

GGSCI (orcl10g1) 2> add extract extrvip, tranlog,threads 2, begin now

GGSCI (orcl10g1) 3> add exttrail ./dirdat/ex, extract extrvip

GGSCI (orcl10g1) 4> add extract pmpvip, exttrailsource ./dirdat/ex, begin now

GGSCI (orcl10g1) 5> add rmttrail ./dirdat/rx, extract pmpvip

GGSCI (orcl10g1) 6> start extract extrvip

GGSCI (orcl10g1) 7> start extract pmpvip

GGSCI (orcl10g1) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER       RUNNING

EXTRACT        RUNNING       EXTRVIP       00:00:00          00:00:02 

EXTRACT        RUNNING       EXTSTG1     00:00:00          00:00:07

EXTRACT         RUNNING        PMPSTG1               00:00:00          00:00:01

EXTRACT        RUNNING     PMPVIP          00:00:00          00:00:01

INITIATE EXPORT BACKUP ON SOURCE

CHECK CURRENT SCN FROM DATABASE AND take export of the backup with the scn

Select current_scn from v$database;

10686557216122

Information – A Commit Sequence Number (CSN) is an identifier that GoldenGate constructs to identify a transaction for the purpose of maintaining transactional consistency and data integrity.

expdp dumpfile=GGVIPENROL_111219.dmp directory=VIP_EXPORT  schemas=vipenrol logfile=GGexpVIPENROL_111219.log flashback_scn=10686557216122

Copy the backup to target server

scp  *.dmp   172.19.50.74@root:/db_dump

  • Target Oracle Goldengate configuration

TABLESPACE AND USER CREATION ON TARGET

Tablespace and database User need to be created on target before configuring Goldengate replication for the schema replication.

TABLESPACE CREATION

create tablespace VL_DATA_TBS datafile ‘+DATADG’ size 5G;

create user oggprod01 identified by oggprod01 default tablespace VL_DATA_TBS quota unlimited on VL_DATA_TBS;

USER CREATION

Create user OGGPPMNEW identified by OGGPPMNEW  default tablespace PPMNEW_DATA_TBS quota unlimited on PPMNEW_DATA_TBS;

grant connect ,resource to OGGPPMNEW;

INITIATE IMPORT

impdp dumpfile=GGVIPENROL_111219.dmp directory=expdbvip  schemas=vipenrol logfile=GGimpVIPENROL_111219.log remap_schema=vipenrol:OGGVIPENRL remap_tablespace=VIPENROL:VIPENROL_DATA_TBS

Login to target database as oracle user and open goldengate command prompt and create goldengate parameter as specified and start the services.

su – oracle

cd /ggate/ogg12c

./ggsci

GGSCI (orcl10g1) 1> edit param rephyd01

REPLICAT rephyd01

SETENV (ORACLE_HOME = “/oracle/app/oracle/product/12.2.0/dbhome_1”)

SETENV (ORACLE_SID = “orcl12c1”)

ASSUMETARGETDEFS

USERID GGATE PASSWORD gg3t3

MAP VIPENROL.* TARGET OGGHYDVIPENROL.*;

Start Goldengate Process On Target

GGSCI (orcl10g1) 2> dblogin USERID ggate password  gg3t3

GGSCI (orcl10g1) 3>add checkpointtable ggate.checkpoint

GGSCI (orcl10g1) 4>add replicat rephyd01, exttrail /ggate/ogg12c/dirdat/rx, checkpointtable ggate.checkpoint

GGSCI (orcl10g1) 5>start replicat rephyd01

GGSCI (orcl10g1) 6>start replicat rephyd01, aftercsn 11153879918466

GGSCI (orcl10g1) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

REPLICAT    RUNNING     REPAYD01   00:00:00      00:00:04

  • Source Oracle Goldengate configuration

For PPMNEW schema replication from Source to target, Source Goldengate will have two extract process configured one to capture the change data Extract Name EXTPPM and other pump for pushing the trail file from source to target location PMPVIP.

Create test table on SOURCE database for testing the sync after the configuration and sync

CREATE TABLE PPMNEW.GGTEST

(

  ID VARCHAR2(20)

, BLOB BLOB

, DESCRIPTION VARCHAR2(20)

);

Login to source OS user ggate and open goldengate command prompt and create goldengate parameter as specified and start the services.

su – ggate

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID= DB121

cd /u01/ggate

./ggsci

GGSCI (orcl10g1) 3>dblogin userid ggate password gg3t3

GGSCI (orcl10g1) 4>edit param EXTPPM

EXTRACT extppm

SETENV (ORACLE_HOME = “/oracle/product/10.2/db_1”)

SETENV (ORACLE_SID = “DB121”)

USERID ggate, PASSWORD gg3t3

EXTTRAIL ./dirdat/ec

DISCARDFILE ./dirdat/ext_ppm.txt, PURGE

DISCARDROLLOVER AT 01:00 ON FRIDAY

TABLE PPMNEW.*;

GGSCI (orcl10g1) 4>edit param PMPPPM

extract pmpppm

SETENV (ORACLE_SID = “DB121”)

USERID ggate, PASSWORD gg3t3

rmthost 172.19.50.74, MGRPORT 7809

rmttrail ./dirdat/rc

DISCARDFILE ./dirrpt/pump_pbrokppm.txt, PURGE

PASSTHRU

table PPMNEW.*;

Enabling Oracle GoldenGate table-level logging

GGSCI (orcl10g1) 5> DBLOGIN USERID ggate password gg3t3

GGSCI (orcl10g1) 6> add trandata PPMNEW.*

Can execute info trandata PPMNEW.* to check trandata status

Start Goldengate Process On Source

GGSCI (orcl10g1) 1> dblogin userid ggate password gg3t3

GGSCI (orcl10g1) 2> add extract extppm, tranlog,threads 2, begin now

GGSCI (orcl10g1) 3> add exttrail ./dirdat/ec, extract extppm

GGSCI (orcl10g1) 4> add extract pmpppm, exttrailsource ./dirdat/ec, begin now

GGSCI (orcl10g1) 5> add rmttrail ./dirdat/rc, extract pmpppm

GGSCI (orcl10g1) 6> start extract extppm

GGSCI (orcl10g1) 7> start extract pmpppm

GGSCI (orcl10g1) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EXTPPM      00:00:00      00:00:08

EXTRACT     RUNNING     EXTRVIP     00:00:00      00:00:02

EXTRACT     RUNNING     EXTSTG1     00:00:00      00:00:07

EXTRACT     RUNNING     PMPPPM      00:00:00      00:00:05

EXTRACT     RUNNING     PMPSTG1     00:00:00      00:00:01

EXTRACT     RUNNING     PMPVIP      00:00:00      00:00:01

INITIATE EXPORT BACKUP ON SOURCE

CHECK CURRENT SCN FROM DATABASE AND take export of the backup with the scn

Select current_scn from v$database;

10686638975578

expdp dumpfile=GGPPMNEW_140120.dmp directory=ppmexp  schemas=PPMNEW logfile=GGexpPPMNEW_140120.log flashback_scn=10686638975578

Copy the backup to target server

scp  *.dmp   172.19.50.74@root:/db_dump

  • Target Oracle Goldengate configuration

TABLESPACE AND USER CREATION ON TARGET

Tablespace and database User need to be created on target before configuring Goldengate replication for the schema replication.

TABLESPACE CREATION

create tablespace PPMNEW_DATA_TBS datafile ‘+DATADG’ size 30G;

alter tablespace PPMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace PPMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace PPMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace PPMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace PPMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace PPMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace PPMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace PPMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace APMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace APMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace APMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace APMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace APMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace APMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace APMNEW_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

USER CREATION

Create user OGGAPMNEW identified by OGGAPMNEWdefault tablespace APMNEW_DATA_TBS quota unlimited on APMNEW_DATA_TBS;

grant connect ,resource to OGGAPMNEW;

INITIATE IMPORT

impdp dumpfile=GGAPMNEW_140120.dmp directory=expdbvip schemas=APMNEW logfile=GGimpAPMNEW_150120.log remap_tablespace=TS_PPM:APMNEW_DATA_TBS  remap_schema=APMNEW:OGGAPMNEW

Login to target database as oracle user and open goldengate command prompt and create goldengate parameter as specified and start the services.

su – oracle

cd /ggate/ogg12c

./ggsci

GGSCI (orcl10g1) 1> edit param REPQMNEW

REPLICAT reppmnew

SETENV (ORACLE_HOME = “/oracle/app/oracle/product/12.2.0/dbhome_1”)

SETENV (ORACLE_SID = “orcl12c1”)

ASSUMETARGETDEFS

USERID GGATE PASSWORD gg3t3

APPLYNOOPUPDATES

MAP PPMNEW.* TARGET OGGAPMNEW.*;

Start Goldengate Process On Target

GGSCI (orcl10g1) 2> dblogin USERID ggate password  gg3t3

GGSCI (orcl10g1) 3> add checkpointtable ggate.checkpoint

GGSCI (orcl10g1) 4> add replicat reppmnew, exttrail /ggate/ogg12c/dirdat/rc, checkpointtable

GGSCI (orcl10g1) 5> ggate.checkpoint

GGSCI (orcl10g1) 6> start replicat reppmnew

GGSCI (orcl10g1) 7> start replicat reppmnew, aftercsn 10686638975578

GGSCI (orcl10g1) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

REPLICAT    RUNNING     REPAYD01    00:00:00      00:00:04

REPLICAT    RUNNING     REPQMNEW    00:00:00      00:00:06

Source Oracle Goldengate configuration

For KSAschema replication from Source to target, Source Goldengate will have two extract process configured one to capture the change data Extract Name EXTRVSPDB and other pump for pushing the trail file from source to target location PMPDB3.

Create test table on SOURCE database for testing the sync after the configuration and sync

CREATE TABLE KSA.GGTEST

(

  ID VARCHAR2(20)

, BLOB BLOB

, DESCRIPTION VARCHAR2(20)

);

Login to source OS user ggate and open goldengate command prompt and create goldengate parameter as specified and start the services.

su – ggate

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID= DB31

cd /u01/ggate

./ggsci

GGSCI (orcl10g1) 3>dblogin userid ggate password gg3t3

GGSCI (orcl10g1) 4>edit param EXTRVADB

EXTRACT extrvpdb

SETENV (ORACLE_HOME = “/oracle/product/10.2/db_1”)

SETENV (ORACLE_SID = “DB31”)

USERID ggate, PASSWORD gg3t3

EXTTRAIL ./dirdat/ev

DISCARDFILE ./dirdat/ext_vps.txt, PURGE

DISCARDROLLOVER AT 01:00 ON FRIDAY

TABLE KSA.*;

GGSCI (orcl10g1) 4>edit param PMPDB3

extract pmpDB3

SETENV (ORACLE_SID = “DB31”)

USERID ggate, PASSWORD gg3t3

rmthost 172.19.50.74, MGRPORT 7809

rmttrail ./dirdat/rv

DISCARDFILE ./dirrpt/pump_pbrokvps.txt, PURGE

PASSTHRU

table KSA.*;

Enabling Oracle GoldenGate table-level logging

GGSCI (orcl10g1) 5> DBLOGIN USERID ggate password gg3t3

GGSCI (orcl10g1) 6> add trandata KSA.*

Can execute info trandata KSA.* to check trandata status

Start Goldengate Process On Source

GGSCI (orcl10g1) 1> dblogin userid ggate password gg3t3

GGSCI (orcl10g1) 2> add extract extrvpdb, tranlog,threads 2, begin now

GGSCI (orcl10g1) 3> add exttrail ./dirdat/ev, extract extrvpdb

GGSCI (orcl10g1) 4> add extract pmpDB3, exttrailsource ./dirdat/ev, begin now

GGSCI (orcl10g1) 5> add rmttrail ./dirdat/rv, extract pmpDB3

GGSCI (orcl10g1) 6> start extract extrvpdb

GGSCI (orcl10g1) 7> start extract pmpDB3

GGSCI (orcl10g1) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER         RUNNING

EXTRACT          RUNNING     EXTAPM      00:00:00      00:00:08

EXTRACT          RUNNING     EXTRAIP     00:00:00      00:00:02

EXTRACT     RUNNING     EXTSTG1     00:00:00      00:00:07

EXTRACT          RUNNING     EXTRAPDB    00:00:00      00:00:04

EXTRACT     RUNNING     PMPAPM      00:00:00      00:00:05

EXTRACT     RUNNING     PMPSTG1     00:00:00      00:00:01

EXTRACT     RUNNING     PMPAIP      00:00:00      00:00:01

EXTRACT     RUNNING     PMPAPSDB    00:00:00      00:00:02

INITIATE EXPORT BACKUP ON SOURCE

CHECK CURRENT SCN FROM DATABASE AND take export of the backup with the scn

Select current_scn from v$database;

10686686105223

Since the dump size is large we are splitting the dumpfiles to 20G

expdp ggate/gg3t3 dumpfile=GGVPS____280120_%U.dmp directory=datamigr   EXCLUDE=STATISTICS schemas=KSAfilesize=20G logfile=GGexpVPSUAE____280120.log flashback_scn=10686686105223

Copy the backup to target server

scp  GGVPS____280120*.dmp   172.19.50.74@root:/db_dump

  • Target Oracle Goldengate configuration

TABLESPACE AND USER CREATION ON TARGET

Tablespace and database User need to be created on target before configuring Goldengate replication for the schema replication.

TABLESPACE CREATION

create tablespace DB3_DATA_TBS  datafile ‘+DATADG’ size 30G;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace DB3_DATA_TBS add datafile ‘+DATADG’ size 10G AUTOEXTEND ON ;

USER CREATION

SQL> Create user OGGDB3UAE identified by OGGDB3UAE default tablespace DB3_DATA_TBS  quota unlimited on DB3_DATA_TBS;

SQL> grant connect , resource to OGGDB3UAE;

INITIATE IMPORT

impdp dumpfile=GGVPS____280120_%U.dmp directory=data_mig schemas=KSAlogfile=GGVPS____020220.log remap_tablespace=USERS:DB3_DATA_TBS remap_schema=KSA:OGGDB3UAE

Login to target database as oracle user and open goldengate command prompt and create goldengate parameter as specified and start the services.

su – oracle

cd /ggate/ogg12c

./ggsci

GGSCI (orcl10g1) 1> edit param REPDB3

REPLICAT repvpsdb

SETENV (ORACLE_HOME = “/oracle/app/oracle/product/12.2.0/dbhome_1”)

SETENV (ORACLE_SID = “orcl12c1”)

ASSUMETARGETDEFS

USERID GGATE PASSWORD gg3t3

APPLYNOOPUPDATES

MAP KSA.* TARGET OGGVPSDBUAE.*;

Start Goldengate Process On Target

GGSCI (orcl10g1) 2> dblogin USERID ggate password  gg3t3

GGSCI (orcl10g1) 3> add checkpointtable ggate.checkpoint

GGSCI (orcl10g1) 4> add replicat repDB3, exttrail /ggate/ogg12c/dirdat/rv, checkpointtable

GGSCI (orcl10g1) 5> ggate.checkpoint

GGSCI (orcl10g1) 6> start replicat repDB3

GGSCI (orcl10g1) 7> start replicat repDB3, aftercsn 10686686105223

GGSCI (orcl10g1) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

REPLICAT    RUNNING     REPAYD01   00:00:00      00:00:04

REPLICAT    RUNNING     REPQMNEW    00:00:00      00:00:06

REPLICAT    RUNNING     REPDB3    00:00:00      00:00:05

  1. Source Oracle Goldengate configuration

For UAEPRG schema replication from Source to target, Source Goldengate will have two extract process configured one to capture the change data Extract Name EXTRUAEP and other pump for pushing the trail file from source to target location PMPUAEP.

Create test table on SOURCE database for testing the sync after the configuration and sync

CREATE TABLE UAEPRG.GGTEST

(

  ID VARCHAR2(20)

, BLOB BLOB

, DESCRIPTION VARCHAR2(20)

);

Login to source OS user ggate and open goldengate command prompt and create goldengate parameter as specified and start the services.

su – ggate

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID= uaeprg1

cd /u01/ggate

./ggsci

GGSCI (orcl10g1) 3>dblogin userid ggate password gg3t3

GGSCI (orcl10g1) 4>edit param EXTRUAEP

EXTRACT extruaep

SETENV (ORACLE_HOME = “/oracle/product/10.2/db_1”)

SETENV (ORACLE_SID = “uaeprg1”)

USERID ggate, PASSWORD gg3t3

EXTTRAIL ./dirdat/eu

DISCARDFILE ./dirdat/ext_uaeprg.txt, PURGE

DISCARDROLLOVER AT 01:00 ON FRIDAY

TABLE UAEPRG.*;

GGSCI (orcl10g1) 4>edit param PMPUAEP

extract pmpuaep

SETENV (ORACLE_SID = “uaeprg1”)

USERID ggate, PASSWORD gg3t3

rmthost 172.19.50.74, MGRPORT 7809

rmttrail ./dirdat/ru

DISCARDFILE ./dirrpt/pump_pbrokuaeprg.txt, PURGE

PASSTHRU

table UAEPRG.*;

Enabling Oracle GoldenGate table-level logging

GGSCI (orcl10g1) 5> DBLOGIN USERID ggate password gg3t3

GGSCI (orcl10g1) 6> add trandata UAEPRG.*

Can execute info trandata UAEPRG.* to check trandata status

Start Goldengate Process On Source

GGSCI (orcl10g1) 1> dblogin userid ggate password gg3t3

GGSCI (orcl10g1) 2> add extract extruaep, tranlog,threads 2, begin now

GGSCI (orcl10g1) 3> add exttrail ./dirdat/eu, extract extruaep

GGSCI (orcl10g1) 4> add extract pmpuaep, exttrailsource ./dirdat/eu, begin now

GGSCI (orcl10g1) 5> add rmttrail ./dirdat/ru, extract pmpuaep

GGSCI (orcl10g1) 6> start extract extruaep

GGSCI (orcl10g1) 7> start extract pmpuaep

GGSCI (orcl10g1) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER       RUNNING

EXTRACT        RUNNING     EXTPPM      00:00:00      00:00:08

EXTRACT        RUNNING     EXTRUAEP    00:00:00      00:00:06

EXTRACT        RUNNING     EXTRVIP     00:00:00      00:00:02

EXTRACT        RUNNING     EXTSTG1     00:00:00      00:00:07

EXTRACT             RUNNING     EXTRVPDB    00:00:00      00:00:04

EXTRACT        RUNNING     PMPPPM      00:00:00      00:00:05

EXTRACT        RUNNING     PMPSTG1     00:00:00      00:00:01

EXTRACT            RUNNING     PMPUAEP     00:00:00      00:00:02

EXTRACT        RUNNING     PMPVIP      00:00:00      00:00:01

EXTRACT        RUNNING     PMPVPSDB    00:00:00      00:00:02

To avoid below error while taking expdp backup for DB4 schema, we have altered the lob columns on particular issue table PASSPORT_HOLDER to use pct instead of retention. As retention with a higher value was also failing.

ORA-31693: Table data object “KSAPRG”.”PASSPORT_HOLDER” failed to load/unload and is being skipped due to error:

ORA-02354: error in exporting/importing data

ORA-01555: snapshot too old: rollback segment number with name “” too small

ORA-22924: snapshot too old

ALTER TABLE KSAPRG. PASSPORT_HOLDER  MODIFY LOB(PH_FACE_IMAGE_DATA) (PCTVERSION 100);

ALTER TABLE KSAPRG. PASSPORT_HOLDER  MODIFY LOB(PH_FINGERPRINT_A) (PCTVERSION 100);

ALTER TABLE KSAPRG. PASSPORT_HOLDER  MODIFY LOB(PH_FINGERPRINT_B)

ALTER TABLE KSAPRG. PASSPORT_HOLDER  MODIFY LOB(PH_FINGERPRINT_TEMPLATE_A) (PCTVERSION 100);

ALTER TABLE KSAPRG. PASSPORT_HOLDER  MODIFY LOB(PH_FINGERPRINT_TEMPLATE_B) (PCTVERSION 100);

ALTER TABLE KSAPRG. PASSPORT_HOLDER  MODIFY LOB(PH_SIGNATURE) (PCTVERSION 100);

INITIATE EXPORT BACKUP ON SOURCE

CHECK CURRENT SCN FROM DATABASE AND take export of the backup with the scn

Select current_scn from v$database;

10686699596407

Since the dump size is large we are splitting the dumpfiles to 20G

expdp dumpfile=KSAPRG_20200203____%U.dmp directory=datamigr  filesize=20G  EXCLUDE=STATISTICS  schemas=UAEPRG logfile=GGexpKSAPRG____20200203.log flashback_scn=10686699596407

Copy the backup to target server

scp  KSAPRG_20200203*.dmp   172.19.50.74@root:/db_dump

  • Target Oracle Goldengate configuration

TABLESPACE AND USER CREATION ON TARGET

Tablespace and database User need to be created on target before configuring Goldengate replication for the schema replication.

TABLESPACE CREATION

create tablespace KSAPRG_DATA_TBS datafile ‘+DATADG’ size 30G;

alter tablespace KSAPRG_DATA_TBSadd datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 10G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 30G AUTOEXTEND ON ;

alter tablespace KSAPRG_DATA_TBS add datafile ‘+DATADG’ size 20G AUTOEXTEND ON ;

USER CREATION

SQL> Create user OGG1 identified by OGG default tablespace KSAPRG_DATA_TBS quota unlimited on KSAPRG_DATA_TBS;

SQL> grant connect , resource to OGGKSAPRG;

INITIATE IMPORT

impdp dumpfile= KSAPRG_20200203____%U.dmp directory=data_mig schemas=KSAlogfile=GGVPS____020220.log remap_tablespace=USERS:KSAPRG_DATA_TBS remap_schema=KSAPRG:OGGKSAPRG

Table TRANS_STATE_HISTon DB4 on source is having read only permission to the table. To avoid error Goldengate replica abended with error OGG-01296, OGG-01668, SQL error 12081 mapping

for the table TRANS_STATE_HIST we will make the table to read,write mode on target before starting the goldengate replication

SQL>ALTER TABLE OGGKSAPRG.TRANS_STATE_HIST READ WRITE

Login to target database as oracle user and open goldengate command prompt and create goldengate parameter as specified and start the services.

su – oracle

cd /ggate/ogg12c

./ggsci

GGSCI (orcl10g1) 1> edit param REPKSAPG

REPLICAT repuaepg

SETENV (ORACLE_HOME = “/oracle/app/oracle/product/12.2.0/dbhome_1”)

SETENV (ORACLE_SID = “orcl12c1”)

ASSUMETARGETDEFS

USERID GGATE PASSWORD gg3t3

APPLYNOOPUPDATES

MAP DB4.* TARGET OGGDB4.*;

Start GoldenGate Process On Target

GGSCI (orcl10g1) 2> dblogin USERID ggate password  gg3t3

GGSCI (orcl10g1) 3> add checkpointtable ggate.checkpoint

GGSCI (orcl10g1) 4> add replicat repuaepg, exttrail /ggate/ogg12c/dirdat/ru, checkpointtable

GGSCI (orcl10g1) 5> ggate.checkpoint

GGSCI (orcl10g1) 6> start replicat repuaepg

GGSCI (orcl10g1) 7> start replicat repuaepg, aftercsn 10686699596407

GGSCI (orcl10g1) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

REPLICAT    RUNNING     REPAYD01   00:00:00      00:00:04

REPLICAT    RUNNING     REPQMNEW    00:00:00      00:00:06

REPLICAT    RUNNING     REPKSAPG 00:00:00      00:00:06

REPLICAT    RUNNING     REPDB3    00:00:00      00:00:05

Insert some dummy data on test table created for all the goldengate replicated schema on source and test the sync status for the table on target

export ORACLE_SID= DB1

sqlplus “/as sysdba”

SQL> insert into vipenrol.ggtest values (11,null,’VIPENRL’);

SQL> commit;

export ORACLE_SID=DB121

sqlplus “/as sysdba”

SQL> insert into PPMNEW.ggtest values (22,null,’DB12’);

SQL> commit;

export ORACLE_SID=DB31

sqlplus “/as sysdba”

SQL> insert into ggtest values (33,null,’DB3’);

SQL> commit;

export ORACLE_SID=OGGPROD02

sqlplus “/as sysdba”

SQL> insert into ggtest values (44,null,’UAEPRG’);

SQL> commit;

Connect to target database server and query test table to check the replication status

export

SQL> select * from OGGPROD01.ggtest union all

select * from OGGPROD02.ggtest union all

select * from OGGPROD01.ggtest union all

select * from OGGUAEPRG.ggtest;

  1. ORACLE GOLDENGATE ADMINISTRATION

SOURCE ORACLE GOLDENGATE SERVICE START

Login to Node1 server as ggate os user and Start Oracle GoldenGate services by executing below commands. Once services are started check the status by executing info all command. The status would be RUNNING for all 

ggate@orcl10g1:~> cd /u01/ggate/

ggate@orcl10g1:/u01/ggate> ./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 (orcl10g1) 1> start mgr

GGSCI (orcl10g1) 2> start extract EXTRMIP    

GGSCI (orcl10g1) 3> start extract PMPMIP     

GGSCI (orcl10g1) 4> start extract EXTSTG1    

GGSCI (orcl10g1) 5> start extract PMPSTG1    

GGSCI (orcl10g1) 6> start extract EXTMPM     

GGSCI (orcl10g1) 7> start extract PMMPM     

GGSCI (orcl10g1) 8> start extract EXTRMPDB   

GGSCI (orcl10g1) 9> start extract PMPMPSDB   

GGSCI (orcl10g1) 10> start extract EXTRKSAP   

GGSCI (orcl10g1) 11> start extract PMPKSAP    

GGSCI (orcl10g1) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EXTMPM      00:00:00      00:00:08

EXTRACT     RUNNING     EXTRKSAP    00:00:00      00:00:06

EXTRACT     RUNNING     EXTRMIP     00:00:00      00:00:02

EXTRACT     RUNNING     EXTRAPDB    00:00:00      00:00:04

EXTRACT     RUNNING     EXTSTG1     00:00:00      00:00:07

EXTRACT     RUNNING     PMPMPM      00:00:00      00:00:05

EXTRACT     RUNNING     PMPSTG1     00:00:00      00:00:01

EXTRACT     RUNNING     PMPKSAP     00:00:00      00:00:02

EXTRACT     RUNNING     PMPMIP      00:00:00      00:00:01

EXTRACT     RUNNING     PMPKPSDB    00:00:00      00:00:02

SOURCE ORACLE GOLDENGATE SERVICE STOP

Login to Node1 server as ggate os user and stop Oracle GoldenGate services by executing below commands. Once services are started check the status by executing info all command. The status would be STOPPED for all 

ggate@orcl10g1:~> cd /u01/ggate/

ggate@orcl10g1:/u01/ggate> ./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 (orcl10g1) 11> stop extract PMPUAEP    

GGSCI (orcl10g1) 10> stop extract EXTRUAEP   

GGSCI (orcl10g1) 9> stop extract PMPDB3   

GGSCI (orcl10g1) 8> stop extract EXTRVPDB   

GGSCI (orcl10g1) 7> stop extract PMPPPM     

GGSCI (orcl10g1) 6> stop extract EXTPPM     

GGSCI (orcl10g1) 5> stop extract PMPSTG1    

GGSCI (orcl10g1) 4> stop extract EXTSTG1    

GGSCI (orcl10g1) 3> stop extract PMPVIP     

GGSCI (orcl10g1) 2> stop extract EXTRVIP    

GGSCI (orcl10g1) 1> stop mgr

GGSCI (orcl10g1) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

EXTRACT     STOPPED     EXTPPM      00:00:00      00:00:18

EXTRACT     STOPPED     EXTRKSAP    00:00:00      00:00:16

EXTRACT     STOPPED     EXTRVIP     00:00:00      00:00:12

EXTRACT     STOPPED     EXTRVPDB    00:00:00      00:00:14

EXTRACT     STOPPED     EXTSTG1     00:00:00      00:00:17

EXTRACT     STOPPED     PMPPPM      00:00:00      00:00:15

EXTRACT     STOPPED     PMPSTG1     00:00:00      00:00:11

EXTRACT     STOPPED     PMPKSAP     00:00:00      00:00:12

EXTRACT     STOPPED     PMPVIP      00:00:00      00:00:11

EXTRACT     STOPPED     PMPVPSDB    00:00:00      00:00:12

TARGET ORACLE GOLDENGATE SERVICE START

Login to target Node1 server as oracle OS user and Start Oracle GoldenGate services by executing below commands. Once services are started check the status by executing info all command. The status would be RUNNING for all 

[oracle@ae086dbsv01 ~]$ cd /ggate/ogg12c/

[oracle@ae086dbsv01 ogg12c]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.3.0.1.2 28011195_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Jun 19 2018 09:10:23

Operating system character set identified as UTF-8.

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

GGSCI (ae086dbsv01) 1>start mgr

GGSCI (ae086dbsv01) 2>start REPLICAT REPAYD01   

GGSCI (ae086dbsv01) 3>start REPLICAT REPQMNEW   

GGSCI (ae086dbsv01) 4>start REPLICAT REPDB3   

GGSCI (ae086dbsv01) 5>start REPLICAT REPKSAPG

GGSCI (ae086dbsv01) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

REPLICAT    RUNNING     REPAYD01   00:00:00      00:00:04

REPLICAT    RUNNING     REPQMNEW    00:00:00      00:00:06

REPLICAT    RUNNING     REPKSAPG 00:00:00      00:00:06

REPLICAT    RUNNING     REPKPSDB    00:00:00      00:00:05

TARGET ORACLE GOLDENGATE SERVICE STOP

Login to target Node1 server as oracle OS user and stop Oracle GoldenGate services by executing below commands. Once services are started check the status by executing info all command. The status would be STOPPED for all 

[oracle@ae086dbsv01 ~]$ cd /ggate/ogg12c/

[oracle@ae086dbsv01 ogg12c]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.3.0.1.2 28011195_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Jun 19 2018 09:10:23

Operating system character set identified as UTF-8.

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

GGSCI (ae086dbsv01) 1>stop REPLICAT REPAYD01   

GGSCI (ae086dbsv01) 2>stop REPLICAT REPQMNEW   

GGSCI (ae086dbsv01) 3>stop REPLICAT REPDB3   

GGSCI (ae086dbsv01) 4>stop REPLICAT REPKSAPG

GGSCI (ae086dbsv01) 5>stop mgr

GGSCI (ae086dbsv01) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

REPLICAT    STOPPED     REPAYD01   00:00:00      00:00:14

REPLICAT    STOPPED     REPQMNEW    00:00:00      00:00:16

REPLICAT    STOPPED     REPKSAPG 00:00:00      00:00:16

REPLICAT    STOPPED     REPKPSDB   00:00:00      00:00:15

Performing a GoldenGate instance backup

The backup for Oracle GoldenGate instance simply performed using the operating system cp command. The backup should include the root directory and sub-directories. The sub-directories include the parameter files and other related files. Using the –R option recursively backup the sub-directories. Also, ensure the shell environment variable $OGG_HOME is enabled. Zipping the backup into a single file is recommended for archiving purpose and file transfer.

$ echo $GGS_HOME

/u01/app/ogg

$ cp -R $GGS_HOME/* /u01/backup/ggs/

$ zip ggs.zip /u01/backup/ggs/*

By mithun

Related Post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

You missed

Oracle Uncategorized

Create Smart Dashboards Automate your Monitoring Using Oracle Apex

Mar 3, 2022 mithun
Uncategorized

Oracle 19c 2 node RAC to single instance

Nov 1, 2021 mithun
Uncategorized

Oracle Application Express (APEX 20+) Admin Password Reset

Mar 25, 2021 mithun
Uncategorized

Switch your CentOS workloads to Oracle Linux as Red Hat says Stream is “not a replacement for CentOS”

Dec 12, 2020 mithun