Wednesday, 27 August 2014

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/)

Then Goto /u01/oragg/ and invoke ggsci command prompt



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.

 Click on Next 


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