GOLDEN Gate mssql to Oracle Uni-Directional Replication
MS SQL Server: 10.65.7.58
Oracle Server : 10.65.7.151
Source Database : (MsSql Server) RCF_HR -- windows
Targete Database : (Oracle) IMOBDB --- Linux
Source Server:
Unzip Golden Gate setup (E.g d:\ OGG for MSSQL.zip) in Any Directory(E.g d:\ GGMSSQL)
Then Goto Command Prompt
From
GG Installation Path Invoke GGSCI command prompt
Create Subdirectories by executing “ Create Subdirs” as shown .
As Is It Windows Server We Have to
Create Service for GG Manager.
To Create Service Execute “ install addservice”It Will create a service, we can check in services.msc
Target
Server:
Unzip Golden Gate
setup (E.g /u01/ OGGLinx64.zip) in Any Directory(E.g /u01/oragg/)
Create Subdirectories as shown
above.It will Create following directories in current location
Configuration for Initial Load and
Initial Replication:
On Source Databse from GGSCI command
prompt Create a parameter file for manager which will consist of Port for
manager service.
Here I am using 15000 as port for
source manager, Once we execute “ Edit Params MGR
“ It will open A text file add line as shown below save and close the text
file.
Start manager service using command
GGSCI >
start manager
GGSCI >
info manager
We can check status of manager by
executing info manager command
Create a ODBC connection as system DSN
on source server to connect MSSQL Database
Follow Below steps
Under system dsn tab Click on add button to add a new dsn
Select SQL Server driver and Click on Finish Button.
Select Sql server authentication enter user id and password and then click on next button
Select required database name from drop down list. Then next, and Finish
We can Test our Data source connection is establishing connection or not By clicking on
Test Data Source Button..
Now on Source Server Using ODBC DSN as
Source Database Name Connect to Source Database as below.
GGSCI> dblogin
sourcedb GGRCFHR userid sa password ams#2008
To add a table for replication from
MSSQL Server
GGSCI> Add
Trandata dbo.employee_profile
To check whether trandata for this
table added or not
GGSCI> info
trandata dbo.employee_profile
Create definition file on source and
this files has to be copied to target server
After adding above lines in the text
file, save and close the notepad.
Then exit from GGSCI Command prompt.
Execute Below command as show in image which will create a source.def file which has to be copied to target
server /u01/oragg/defgen/ direcotory
Output will be like as shown below
Copy
Above generated file (Source.def) to Target Server (GGPATH/dirdef)
As Source
and Target Tables Structures already created by application team, we cannot
change existing definitions ..
SQL SERVER DATABSE TABLE:
[dbo].[Employee_Profile]([AutoID] [int] IDENTITY(1,1) NOT NULL,
[Employee_Code] [nvarchar](255) NULL,
[Employee_Name] [nvarchar](255) NULL,
[Date_of_Birth] [datetime] NULL,
[Date_of_anniversary] [datetime] NULL,
[Date_of_Joining] [datetime] NULL,
[Month] [datetime] NULL,
[Employee_Department] [nvarchar](255) NULL,
[PRODUCT(sub PSA)] [nvarchar](255) NULL,
[Grade] [nvarchar](255) NULL,
[HR DESIGNATION] [nvarchar](255) NULL,
[Employee_Designation] [nvarchar](255) NULL,
[ZONE] [nvarchar](255) NULL,
[Region_Name] [nvarchar](255) NULL,
[State] [nvarchar](255) NULL,
[Location (PA)] [nvarchar](255) NULL,
[Branch_Name] [nvarchar](255) NULL,
[Direct Reporting Name] [nvarchar](255) NULL,
[Reporting_Employee_ID_PK] [nvarchar](255) NULL,
[Functional Reporting name] [nvarchar](255) NULL,
[Functional Reporting SAP ID] [nvarchar](255) NULL,
[HR Manager Name] [nvarchar](255) NULL,
[HR Manager SAP ID] [nvarchar](255) NULL,
[Contact_No] [nvarchar](255) NULL,
[EMail] [nvarchar](255) NULL,
[Gender] [nvarchar](255) NULL,
[Status] [nvarchar](50) NULL,
[Branch_Code] [nvarchar](255) NULL,
[Region_Code] [nvarchar](255) NULL,
[Zone_Code] [nvarchar](255) NULL,
[Last_Working_Date] [nvarchar](255) NULL,
[MACID] [nvarchar](255) NULL
)
Oracle Database Table:
Desc empdetails
|
Name
|
Null?
|
Type
|
|
AUTOID
|
VARCHAR2(4)
|
|
|
EMPLOYEE_CODE
|
VARCHAR2(11)
|
|
|
EMPLOYEE_NAME
|
VARCHAR2(32)
|
|
|
DATE_OF_BIRTH
|
VARCHAR2(23)
|
|
|
DATE_OF_ANNIVERSARY
|
VARCHAR2(23)
|
|
|
DATE_OF_JOINING
|
VARCHAR2(23)
|
|
|
MONTH
|
VARCHAR2(23)
|
|
|
EMPLOYEE_DEPARTMENT
|
VARCHAR2(20)
|
|
|
PRODUCT_SUB_PSA
|
VARCHAR2(51)
|
|
|
GRADE
|
VARCHAR2(4)
|
|
|
HR_DESIGNATION
|
VARCHAR2(24)
|
|
|
EMPLOYEE_DESIGNATION
|
VARCHAR2(56)
|
|
|
ZONE
|
VARCHAR2(9)
|
|
|
REGION_NAME
|
VARCHAR2(29)
|
|
|
STATE
|
VARCHAR2(14)
|
|
|
LOCATION_PA
|
VARCHAR2(11)
|
|
|
BRANCH_NAME
|
VARCHAR2(29)
|
|
|
DIRECT_REPORTING_NAME
|
VARCHAR2(29)
|
|
|
REPORTING_EMPLOYEE_ID_PK
|
VARCHAR2(11)
|
|
|
FUNCTIONAL__REPORTING_NAME
|
VARCHAR2(25)
|
|
|
FUNCTIONAL_REPORTING_SAP_ID
|
VARCHAR2(11)
|
|
|
HR_MANAGER_NAME
|
VARCHAR2(22)
|
|
|
HR_MANAGER_SAP_ID
|
VARCHAR2(11)
|
|
|
CONTACT_NO
|
VARCHAR2(16)
|
|
|
EMAIL
|
VARCHAR2(38)
|
|
|
GENDER
|
VARCHAR2(6)
|
|
|
STATUS
|
VARCHAR2(6)
|
|
|
BRANCH_CODE
|
VARCHAR2(4)
|
|
|
REGION_CODE
|
VARCHAR2(4)
|
|
|
ZONE_CODE
|
VARCHAR2(4)
|
|
|
LAST_WORKING_DATE
|
VARCHAR2(4)
|
|
|
MACID
|
VARCHAR2(4)
|
On Target Database Create A User for GG and grant below privs:
create
user oragg identified by oragg ;
grant
connect, resource to oragg;
grant
select any table, select any dictionary to oragg;
grant
create session, alter session to orgg;
grant
flashback any table to orgg;
grant all rights to oragg user on table which is participating in
replication
grant all
on IMOBPROD.EMPDETAILS to oragg;
On Target
Server Login to Database
Add
following one more tnsalias for GG Database
ggimobprod
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.65.7.153)(PORT
= 1865))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = IMOBPROD)
)
)
GGSCI>
dblogin userid oragg@ggimobprod password oragg
Create
check point table, which will create 2 table (checkpoint, checkpoint_lox) on
target database
GGSCI>
add checkpointtable oragg.checkpoint
Create
manager services on target Server
GGSCI>
Edit Params MGR
Which
will open a new file, add lines as shown above ( PORT 7809) , save and quit.
Start
manger services
GGSCI>
START MANGER
GGSCI> info manager
Source:
Select
count(*) from dbo.employee_profile;
Target:
Select
count(*) IMOBPROD.EMPDETAILS;
COUNT(*)
-------------
0
If target table doesnot contain a primary
key or unique index , Please Create a Unique index on any column that had
unique values.
Create unique Index empdetails_idx01 on
empdetails(autoid);
Configure the initial load:
Which
will insert, all records from source to target.
GGSCI>
add extract ILOAD01, SOURCEISTABLE
GGSCI>
edit params ILOAD01
GGSCI>
info extract ILOAD01
Now on
Target Add replicat,
GGSCI>
ADD REPLICAT IREPL01,SPECIALRUN
GGSCI>
edit params IREPL01
As there
are Many Column Names are different from source table to Target Table,
Here we
are using COLMAP to overcome the issue, But to accept the quotes we need to add
a parameter
(“ USEANSISQLQUOTES “) in GLOBALS File as below,
If we do
not use this parameter Golden Gate Understands these COLMAP as values for those
columns.
GGSCI>
edit params ./GLOBALS
IN Globals file add above lines and save it.
Now Start
Extract and Replicat,
ON Source
GGSCI>
start extract ILOAD01
Target
GGSCI> Start replicat IREPL01
Now (After
Some time If records are More)
Select
count(*) from empdetails;
Count(*)
---------------
1382
Now make configuration for Incremental load:
To Enable
incremental load, we have to create 2 extract processes on source one will create a trail for online processing on a
local system, and another on remote system.
And
Create on replicat on Target Server.
Source:
GGSCI>
add extract EXHR2IMB, tranlog,begin now
GGSCI>
edit params EXHR2IMB
GGSCI>
add exttrail .\dirdat\em , extract exhr2imb, megabytes
10
Pump Process:
GGSCI> add extract pmhr2imb,exttrailsource .\dirdat\em,Description
"Read and Pump Process for RCFHR to IMOBPROD"
GGSCI>
Edit Params PMHR2IMB
GGSCI>
add rmttrail ./dirdat/ex, extract PMHR2IMB, megabytes
10
Target:
GGSCI> add replicat RPINCIMB, exttrail ./dirdat/ex,Description
"Replicate process for
RCF_HR TO IMOBUAT", checkpointtable oragg.checkpoint
Now
start Both extract processes on Source and replicat process on target.
Source:
GGSCI> start extract exhr2imb
GGSCI
> start extract pmhr2imb
Target:
GGSCI> start replicat RPINCIMB





























