ORACLE GOLDEN GATE
CONFIGURATION
On oracle 9.2.0.8
and 11.2.0.4
Using GG 11.1.1 and
12.1.1
What is Golden Gate
Oracle GoldenGate enables the exchange and manipulation of data at the transaction level among multiple, heterogeneous platforms across the enterprise. It moves committed transactions with transaction integrity and minimal overhead on your existing infrastructure. Its modular architecture gives you the flexibility to extract and replicate selected data records, transactional changes, and changes to DDL (data definition language) across a variety of topologies.
Oracle GoldenGate Logical Architecture
Why Golden Gate?
with the help of Golden Gate we can support numerous business requirements:
- Business continuance and high availability.
- Initial load and database migration.
- Data integration.
- Decision support and data warehousing.
Oracle GoldenGate can be configured for the following purposes:
- A static extraction of data records from one database and the loading of those records to another database.
- Continuous extraction and replication of transactional Data Manipulation Language (DML) operations and data definition language (DDL) changes (for supported databases) to keep source and target data consistent.
- Extraction from a database and replication to a file outside the database.
Oracle GoldenGate Supported Topologies
Necessary components of Golden Gate
Oracle GoldenGate is composed of the following components:
- Extract
- Data pump
- Replicat
- Trails or extract files
- Checkpoints
- Manager
- Collector
out of various topologies in this post will see only about unidirectional Golden Gate configuration.
PRECHECK for both source and target
***********************************************
1. Download the oracle Golden
Gate(GG) software for source and target as per your environment our source
is oracle 9.2.0.8(GG version 11.1.1) and
target is oracle 11.2.0.4-RAC ASM (GG version 12.1.1) from oracle E-delivery
site.
2. Unzip the downloaded
software where you want to configure the Golden Gate(GG)
$ unzip V22664-01.zip (source)
$ unzip 121210_fbo_ggs_Linux_x64_shiphome.zip
(target)
3. untar the tar file got
after unzipping the downloaded file.
$ tar -xvof
ggs_OS_sparc_ora9i_64bit_v11_1_1_0_0_078.tar (source)
For 12c we need
to install the GG using GUI.
$ cd fbo_ggs_Linux_x64_shiphome/Disk
$
fbo_ggs_Linux_x64_shiphome/Disk1$ ls
install response
runInstaller stage
use xhost + ( or
any other GUI tool)
$
fbo_ggs_Linux_x64_shiphome/Disk1$./ runInstaller
Follow the GUI
instructions and install the GG on your required path.
4.
Create table-space for GG user both on source and target.
Source
*******
create tablespace
ggs_data datafile 'PATH/GG_datafiles/ggs_data01.dbf' size 1024M;
create user
ggs_owner identified by ggs default tablespace ggs_data temporary tablespace
temp;
grant
connect,resource to ggs_owner;
grant
select any dictionary, select any table to ggs_owner;
grant
create table to ggs_owner;
grant
flashback any table to ggs_owner;
grant
execute on dbms_flashback to ggs_owner;
grant
execute on utl_file to ggs_owner;
grant
SELECT ANY TRANSACTION to ggs_owner;
grant
dba to ggs_owner;
exec
dbms_streams_auth.grant_admin_privilege('ggs_owner');
grant insert on system.logmnr_restart_ckpt$
to ggs_owner;
grant update on
sys.streams$_capture_process to ggs_owner;
grant become user to ggs_owner;
Target
*******
create tablespace
ggs_data datafile '+DISK_GROUP' size 1024M;
create user
ggs_owner identified by ggs default tablespace ggs_data temporary tablespace
temp;
grant
connect,resource to ggs_owner;
grant
select any dictionary, select any table to ggs_owner;
grant
create table to ggs_owner;
grant
flashback any table to ggs_owner;
grant
execute on dbms_flashback to ggs_owner;
grant
execute on utl_file to ggs_owner;
grant
SELECT ANY TRANSACTION to ggs_owner;
grant
dba to ggs_owner;
exec
dbms_streams_auth.grant_admin_privilege('ggs_owner');
grant insert on system.logmnr_restart_ckpt$
to ggs_owner;
grant update on
sys.streams$_capture_process to ggs_owner;
grant become user to ggs_owner;
5. Create sub directories
(only on source because 12c(target) creates sub directories by itself when we
install)
Source
*******
unset LD_LIBRARY_PATH_64
LD_LIBRARY_PATH=/ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:
export
PATH=$PATH:/gg_home
export
LD_LIBRARY_PATH=$ORACLE_HOME/lib/:/gg_home
[HOST]:(/gg_home)>./ggsci
Oracle GoldenGate Command Interpreter for
Oracle
Version 11.1.1.0.0 Build 078
OS, sparc, 64bit (optimized), Oracle 9
on Jul 28 2010 15:19:31
Copyright (C) 1995, 2010, Oracle and/or its
affiliates. All rights reserved.
GGSCI (HOST) 1> CREATE SUBDIRS
Check for connectivity to database both on source and target
***************************************************
GGSCI (HOST) 2> dblogin userid ggs_owner, password ggs
Successfully logged into database.
On target side it’s not required to create sub directories as
12 will take care of it
6.
Enable minimum supplementary logging on source database This step is not required for
target database
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN
FROM V$DATABASE;
SUP
---
NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUP
---
YES
For DDL ENABLING we need to execute below
scripts on both SOURCE and TARGET.
[HOST]:(/gg_home/
sqlplus ‘/as sysdba’
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
@ddl_enable.sql
@ddl_pin.sql
7. Enable force login on
source database, so that if any user are connected as no logging log should be
generated.
SQL> ALTER DATABASE FORCE LOGGING;
NOTE:
For each and every tables primary key or
unique key or unique index is mandatory in order to replicate all the data at
source side, if you don’t have then we have to use a parameter of GG know as
KEYCOLS for those tables with a unique column on those tables or use those
columns which developers use more in “where” clause.
8.
Enable supplementary logging on
source for each table which is used for replication this should be done at low
working hours or when there is no activity on database.
Using the ADD TRANDATA command we now enable
it
Connect to gg
If you get error
as below
[HOST]:(/gg_home)>./ggsci
ld.so.1: ggsci:
fatal: libclntsh.so.9.0: open failed: No such file or directory
Killed
Do the following:
unset LD_LIBRARY_PATH_64
LD_LIBRARY_PATH=ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:
export
PATH=$PATH:/gg_home
export
LD_LIBRARY_PATH=$ORACLE_HOME/lib/:/gg_home
[HOST]:(/gg_home)>./ggsci
Oracle GoldenGate Command Interpreter for
Oracle
Version 11.1.1.0.0 Build 078
os, sparc, 64bit (optimized), Oracle 9
on Jul 28 2010 15:19:31
Copyright (C) 1995, 2010, Oracle and/or its
affiliates. All rights reserved.
GGSCI (HOST0) 2> dblogin userid ggs_owner, password ggs
Successfully logged into database.
GGSCI (HOST) 2> ADD TRANDATA
(For the tables which has primary or unique key)
GGSCI (HOST) 2> ADD TRANDATA USER.TABLE, COLS
(COL1), NOKEY
GGSCI (HOST) 2> ADD TRANDATA USER.TABLE, COLS (COL1,COL2,COL3,COL4,COL5), NOKEY
(For the tables which not have
primary or unique key)
Use
below script to generate script.
SQL> select 'add trandata
'||owner||'.'||object_name||'' from dba_objects
To check whether supplemental
logging is enable or not for a table.
GGSCI (HOST) 2>info TRANDATA USER.TABLE
If you want to delete/disable the supplemental logging for any table
GGSCI (HOST) 2> delete TRANDATA
Execution
**********
1.
Initial load should be done
before starting extract we are doing using data pump.
2.
Create check point table on
source and target side.
GGSCI (HOST) 2> dblogin userid ggs_owner, password ggs
GGSCI (HOST) 2> add checkpointtable ggs_owner.chkpt
Successfully created checkpoint table ggs_owner.chkpt
3.
Configure GLOBALS file both on
source and target
GGSCI (HOST) 2> edit params
./GLOBALS
GGSCHEMA GGS_OWNER
CHECKPOINTTABLE GGS_OWNER.CHKPT
4.
Configure the manager on source
side.
PORT 7809
USERID ggs_owner, PASSWORD ggs
Check the status of manager and start it.
GGSCI (HOST) 2> info all
GGSCI (HOST) 2> start manager mgr
Prepare the extract parameter file on source side.
The extract parameters are as below attached
ADD EXTRACT EXT1, tranlog, begin now
ADD EXTTRAIL /GG_path/, EXTRACT EXT1, MEGABYTES 100
ADD RMTTRAIL /GG_path/, EXTRACT EXT1
EXTRACT EXT1
SETENV (ORACLE_HOME="ORACL_HOME_PATH")
SETENV (ORACLE_SID="SID")
USERID ggs_owner@SERVICE, PASSWORD ggs
--EXTTRAIL /gg_home/dirdat/ra, MEGABYTES 100
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA,REPORT
TRANLOGOPTIONS CONVERTUCS2CLOBS
TABLE USER.TABLE;
TABLE USER.TABLE;
TABLE USER.TABLE;
TABLE USER.TABLE;
TABLE USER.TABLE;
TABLE USER.TABLE;
TABLE USER.TABLE;
Start all the extract process.
GGSCI (HOST) 2> start extract EXT1
use
above command to start each extract process by changing extract process
name(EXT1) with the name of your extract process.
To check the process status
GGSCI (HOST) 2> info all
GGSCI (HOST) 2> info EXT1
To check the status of extract happening.
GGSCI (HOST) 2> stats EXT1
Prepare the pump parameter files on source side
Below is the pump parameter file.
add extract PUMP1, EXTTRAILSOURCE /EXTRACT_TRIAL_PATH/,
add rmttrail //REMOTE_TRIAL_PATH/, extract PUMP1, MEGABYTES 100
extract PUMP1
passthru
RMTHOST IP, MGRPORT 7809
rmttrail /REMOTE_TRIAL_PATH/
TABLE USER.TABLE;
TABLE USER.TABLE;
TABLE USER.TABLE;
TABLE USER.TABLE;
TABLE USER.TABLE;
TABLE USER.TABLE;
TABLE USER.TABLE;
To start the pump use below command
GGSCI (HOST) 2> start extract
To check
GGSCI (HOST) 2> info all
Target side
***********
We have already created check point and configured GLOBALS file.
1.
Configure the manager on target
side.
PORT 7809
USERID ggs_owner, PASSWORD ggs
Check the status of manager and start it.
GGSCI (HOST) 1> info all
GGSCI (HOST) 1> start manager mgr
1.
Prepare the replication
parameter file on target side.
add replicat REP1, exttrail /gg_dir
replicat REP1
--SETENV (ORACLE_HOME="ORACLE_HOME")
--SETENV (ORACLE_SID = "SID")
userid ggs_owner, password ggs
dboptions suppresstriggers
ASSUMETARGETDEFS
--HANDLECOLLISIONS
DISCARDFILE /gg_home/dirrpt/discard_ra.dsc, APPEND
DDLERROR DEFAULT IGNORE RETRYOP
DDL INCLUDE ALL
--DDLOPTIONS ADDTRANDATA, REPORT
--TRANLOGOPTIONS CONVERTUCS2CLOBS
MAP USER.TABLE, TARGET USER.TABLE;
MAP USER.TABLE, TARGET USER.TABLE KEYCOLS (COL_NAME);
MAP USER.TABLE, TARGET USER.TABLE;
Check the status of each replication and start each of them.
GGSCI (HOST) 1> info all
GGSCI (HOST) 1> start REPLICAT ACCUS
And monitor the log and check the status.
The log will be for both extract and replication at the home of GG
on each server.
/ggs/gg_home$ tail -f ggserr.log
To start the replicaton from as specific SCN number we can use below
commands
To check SCN number:
On source side
SQL> col CURRENT_SCN format 99999999999999999999999
SQL> SELECT dbms_flashback.get_system_change_number as
current_scn
FROM DUAL;
CURRENT_SCN
------------------------
XXX123
With the help of above SCN we can start the replication either at
the point of SCN or AFTER SCN.
Start replication after SCN:
*********************
GGSCI (HOST) 22> start REPLICAT REP2
AFTERCSN
Start replication at the point of SCN:
*****************************
GGSCI (HOST) 22> start REPLICAT REP2 ATCSN
There you good to go.................................................
Ref:https://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/wu_about_gg.htm#GWUAD115
It was an excellent Blog to see from you which is very useful. Thank you so much for gathering all this information about <Oracle Goldengate, it’s very clever and will be extremely helpful for all people.
ReplyDelete