OS Details:
[oracle@target ~]$
cat /etc/redhat-release
Red Hat Enterprise
Linux Server release 7.6 (Maipo)
[oracle@target ~]$
Node Name:
target.example.com ----------Target database server
host name
source.example.com -------- Source database server
host name
Database Name:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g
Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release
11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux:
Version 11.2.0.4.0 - Production
NLSRTL Version
11.2.0.4.0 - Production
GG Details:
Oracle GoldenGate
Command Interpreter for Oracle
Version 12.2.0.1.1
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit
(optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character
set identified as UTF-8.
Copyright (C) 1995,
2015, Oracle and/or its affiliates. All rights reserved.
Step: - Preparing the database
for GG (Source database)
A] Database should be
in archive Mode.
B] Enable supplemental
logging
sql> alter
database add supplemental log data;
sql> alter
database force logging;
sql> select
supplemental_log_data_min, force_logging from v$database;
sql> alter system
switch logfile;
C] Create golden gate
Admin user
sql> create
tablespce GG_user datafile
'/u01/app/oracle/oradata/DB11G/ggsample01.dbf' size 1GB;
sql> create user
ggadmin identified by 'passwrod' default tablespace GG_user;
sql> grant
connect,resource,DBA to ggadmin;
sql> exec
dbms_goldengate_auth.grant_admin_privilege('ggadmin');
sql> grant
flashback any table to ggadmin;
Step: - Add tns entry both
database (target/source) in each tnsname.ora
Step: - create wallet and
credentialstore (source and target database)
Connect GG prompt: (source
database)
GGSCI (source.example.com)> create wallet
GGSCI (source.example.com)> add credentialstore
GGSCI (source.example.com)> alter
credentialstore add user ggadmin@srcdb alias srcdb domain admin
(Enter
your password)
GGSCI (source.example.com)> info
credentailstore domain admin
Connect GG prompt: (target
database)
GGSCI (target.example.com)> create wallet
GGSCI (target.example.com)> add
credentialstore
GGSCI (target.example.com)> alter
credentialstore add user ggadmin@trgdb
alias trgdb domain admin
(Enter your password)
GGSCI (target.example.com)> info
credentailstore domain admin
Step:- enable supplemental
logging to source database.
GGSCI
(target.example.com) 10> dblogin
userid ggadmin@trgdb password ******
Successfully logged
into database.
GGSCI
(target.example.com as ggadmin@DB11G) 11> add trandata hr.*
Logging of
supplemental redo log data is already enabled for table HR.COUNTRIES.
TRANDATA for
instantiation CSN has been added on table 'HR.COUNTRIES'.
Logging of
supplemental redo log data is already enabled for table HR.DEPARTMENTS.
TRANDATA for
instantiation CSN has been added on table 'HR.DEPARTMENTS'.
Logging of
supplemental redo log data is already enabled for table HR.EMPLOYEES.
TRANDATA for
instantiation CSN has been added on table 'HR.EMPLOYEES'.
Logging of
supplemental redo log data is already enabled for table HR.JOBS.
TRANDATA for
instantiation CSN has been added on table 'HR.JOBS'.
Logging of
supplemental redo log data is already enabled for table HR.JOB_HISTORY.
TRANDATA for
instantiation CSN has been added on table 'HR.JOB_HISTORY'.
Logging of
supplemental redo log data is already enabled for table HR.LOCATIONS.
TRANDATA for
instantiation CSN has been added on table 'HR.LOCATIONS'.
Logging of
supplemental redo log data is already enabled for table HR.REGIONS.
TRANDATA for
instantiation CSN has been added on table 'HR.REGIONS'.
GGSCI
(source.example.com as ggadmin@DB11G) 12> redo data enabled for table hr.*
ERROR: Invalid
command.
GGSCI
(target.example.com as ggadmin@DB11G) 13> redo data enabled for table hr.*
ERROR: Invalid
command.
Step: - Preparing the database
for GG (target database)
A] Create goldengate
Admin user:
sql> create
tablespce GG_user add datafile
'/u01/app/oracle/oradata/DB11G/ggsample01.dbf' size 1GB;
sql> create user
ggadmin identified by 'passwrod' default tablespace GG_user;
sql> grant
connect,resource,DBA to ggadmin;
sql> exec
dbms_goldengate_auth.grant_admin_privilege('ggadmin');
sql> grant
flashback any table to ggadmin;
sql> alter system
set enable_goldengate_replicate=true
scope=both;
Step:- configure manager process
on both source and target server:
GGSCI
(source.example.com) 1> edit param mgr
PORT 7809
PURGEOLDEXTRACTS
./dirdat/aa*, USECHECKPOINTS, MINKEEPHOURS 2
GGSCI (source.example.com) 2> start mgr
Step:- configure capture process
GGSCI
(source.example.com) 3> edit param EXTRACT0
(Add
below parameter to extract par file)
-- Change Data
Capture parameter file to extract
-- source table
changes
EXTRACT extract0
SETENV (ORACLE_HOME
= "/u01/app/oracle/product/11.2.0.4/db_1")
SETENV
(ORACLE_SID="DB11G")
USERIDALIAS srcdb
DOMAIN admin
TRANLOGOPTIONS
NOUSENATIVEOBJSUPPORT
EXTTRAIL
/u01/ggs/dirdat/rx
TABLE
hr.departments;
DISCARDFILE
/u01/ggs/dirrpt/hr.dsc, APPEND, Megabytes 100
DISCARDROLLOVER AT
04:30 ON friday
Step:- create capture process
GGSCI
(source.example.com as ggadmin@DB11G) 7>
add extract EXTRACT0, integrated tranlog, begin now
EXTRACT (Integrated)
added.
GGSCI
(source.example.com as ggadmin@DB11G) 8> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED
EXTRACT0 00:00:00 00:00:05
GGSCI
(source.example.com as ggadmin@DB11G) 11> add exttrail /u01/ggs/dirdat/rx,
extract EXTRACT0, megabytes 100
EXTTRAIL added.
Step:- register capture process
to 11g database:
GGSCI
(source.example.com) 10> dblogin userid ggadmin@srcdb password ******
Successfully logged
into database.
GGSCI
(source.example.com as ggadmin@DB11G) 13> register extract extract0
database;
Step:- configure Data pump
process
GGSCI
(source.example.com) 4> edit param DATAPMP0
(Add below paramter
to datapump par file)
EXTRACT datapmp0
SETENV (ORACLE_HOME
= "/u01/app/oracle/product/11.2.0.4/db_1")
SETENV
(ORACLE_SID="DB11G")
USERIDALIAS srcdb
DOMAIN admin
RMTHOST
target.example.com, MGRPORT 7809
RMTTRAIL
/u01/ggs/dirdat/tx
TABLE
hr.departments;
Step:- create data pump process
GGSCI
(source.example.com as ggadmin@DB11G) 15> add extract DATAPMP0, exttrailsource /u01/ggs/dirdat/rx begin now
EXTRACT added.
GGSCI
(source.example.com as ggadmin@DB11G) 17> add rmttrail /u01/ggs/dirdat/tx extract DATAPMP0
RMTTRAIL added.
GGSCI
(source.example.com as ggadmin@DB11G) 18> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED
DATAPMP0 00:00:00 00:01:33
EXTRACT STOPPED
EXTRACT0 00:00:00 00:09:18
Step:- start extract and data
pump process.
GGSCI
(source.example.com) 2> start extract EXTRACT0
Sending START
request to MANAGER ...
EXTRACT EXTRACT0
starting
GGSCI
(source.example.com) 25> start extract DATAPMP0
Sending START
request to MANAGER ...
EXTRACT DATAPMP0
starting
GGSCI
(source.example.com) 26> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING
DATAPMP0 00:00:00 00:28:42
EXTRACT RUNNING
EXTRACT0 00:00:09 00:00:05
Step:- configure replicat
process(target Database)
GGSCI
(target.example.com) 2> edit param REPLCAT0
(Add below paramter
to replicat par file)
-- Replicator
parameter file to apply changes
-- to tables
--
REPLICAT replcat0
SETENV (ORACLE_HOME
= "/u01/app/oracle/product/11.2.0.4/db_1")
SETENV
(ORACLE_SID="DB11G")
USERIDALIAS trgdb
DOMAIN admin
DISCARDFILE
/u01/ggs/dirdsc/replcat0.dsc, PURGE
ASSUMETARGETDEFS
MAP hr.departments
TARGET hr.departments;
==> start replicate process:
GGSCI (target.example.com as ggadmin@DB11G) 16> add replicat replcat0 integrated exttrail /u01/ggs.12.2.0.2/dirdat/tx
GGSCI
(target.example.com as ggadmin@DB11G) 16> start replicat REPLCAT0
Sending START
request to MANAGER ...
REPLICAT REPLCAT0
starting
GGSCI
(target.example.com as ggadmin@DB11G) 17> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING
REPLCAT0 00:00:00 00:01:35
===========check GG
Replication setup====
==> Source Database
SQL> conn
hr/hr
Connected.
SQL> insert into
departments values(277,'Marketing',null,1700);
1 row created.
SQL> commit;
Commit complete.
oracle@source ggs]$ ./ggsci
Oracle GoldenGate
Command Interpreter for Oracle
Version 12.2.0.1.1
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit
(optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system
character set identified as UTF-8.
Copyright (C) 1995,
2015, Oracle and/or its affiliates. All rights reserved.
GGSCI
(source.example.com) 1> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING
DATAPMP0 00:00:00 00:00:07
EXTRACT RUNNING
EXTRACT0 00:00:04 00:00:05
GGSCI
(source.example.com) 2> stats
EXTRACT0
Sending STATS
request to EXTRACT EXTRACT0 ...
Start of Statistics
at 2019-06-22 02:36:24.
Output to
/u01/ggs/dirdat/rx:
Extracting from
HR.DEPARTMENTS to HR.DEPARTMENTS:
*** Total statistics
since 2019-06-22 02:36:04 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics
since 2019-06-22 02:36:04 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly
statistics since 2019-06-22 02:36:04 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest
statistics since 2019-06-22 02:36:04 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
==> Target
Database
GGSCI
(target.example.com as ggadmin@DB11G) 19> stats REPLCAT0
Sending STATS
request to REPLICAT REPLCAT0 ...
Start of Statistics
at 2019-06-22 02:36:42.
Integrated Replicat
Statistics:
Total transactions 1.00
Redirected 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from
HR.DEPARTMENTS to HR.DEPARTMENTS:
*** Total statistics
since 2019-06-22 02:36:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics
since 2019-06-22 02:36:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly
statistics since 2019-06-22 02:36:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest
statistics since 2019-06-22 02:36:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
==> Validation
from database (target database)
SQL> select *
from departments;
DEPARTMENT_ID
DEPARTMENT_NAME MANAGER_ID
LOCATION_ID
-------------
------------------------------ ---------- -----------
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
277 Marketing 1700
