Looking for something? Try here..

Thursday, May 12, 2016

I’ll
go through the rest of the steps quickly here with explanations wherever required. Rest of
the explanation can be read from this link.

5.Param
Files Setup

5.1Source
files:

Place all these files in the dirprm
directory under source GoldenGate home.

mgr.prm

PORT 11000

DYNAMICPORTLIST 15010-15020

msd1e.prm

EXTRACT MSD1E

SOURCEDB GG

TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT

RMTHOST 192.168.56.131, MGRPORT
12000

RMTTRAIL
/u01/app/oracle/product/12.1/oggcore_1/dirdat/za

TABLE GG_USER.EMP;

When you enable supplemental logging with the ADD TRANDATA command
for at least one table in a SQL Server database, a secondary truncation point
is created in the transaction log that has to be moved for log space to be
released as needed, following subsequent log backups.

MANAGESECONDARYTRUNCATIONPOINT
- Extract will not be running concurrently (for the same source database) with
SQL Server transactional replication and/or CDC that is configured for
applications other than Oracle GoldenGate. To read more about this parameter
see this link.

msd1i.prm

SOURCEISTABLE

SOURCEDB GG

RMTHOST 192.168.56.131, MGRPORT
12000

RMTFILE
/u01/app/oracle/product/12.1/oggcore_1/dirdat/zi

TABLE gg_user.emp;

SOURCEISTABLE should be
used on initial load extract alone which dictates the process to extract data
directly from the table and use created DSN name for SOURCEDB to connect to the database (GG in our case).

Note: In this demo I have
not used the local trail and pump processes unlike I have used in the Oracle to
Oracle replication as explained in this link. It is always best
practice to use local trails for real time capture and send to remote trail
using data pump process.

5.2Target
Files:

I would like to replicate the data on to EMPLOYEE table under OPENSUSE
user. I already have a table with the below definition. ID column has a unique
constraint enabled.

If you take a look at the above command line 3, you might notice I
have altered the replicat to start at the exact time the initial extract is
started (can be obtained from initial extract report file). This is because,
the initial extract will take all the records from the table directly and
trying to applying the real time changes before this point is unnecessary and
would be an overhead for GoldenGate process as it has to handle any collisions
of data. That’s the reason, the real time replicat has inserted only one record
that it captured after the mentioned time.

Now let’s see what would have happened if we didn’t alter the begin
time of the replicat.

In the above scenario, you can see that we have 4 collisions that the
GoldenGate process has handled and inserted only the record which is missing.
Once after all the records has been applied, you can stop the replicat process,
edit the parameter to remove the HANDLECOLLISIONS and then start the replicat
again to reduce the work overhead.

The parameter file would look like below.

msd1r.prm

REPLICAT MSD1R

SOURCEDEFS
/u01/app/oracle/product/12.1/oggcore_1/dirdef/emp.def

USERID ggs_dba, PASSWORD ggs_dba

MAP GG_USER.emp, TARGET
opensuse.employee;

This completes the replication setup between the source MS SQL Server
database and the target Oracle database. Hope you have enjoyed the article.

A person with hard working ability, will power and passion to finish what he starts.
An Oracle database administrator trying to explore every nook and corner possible in Oracle technology. A blogger, a story and poem writer at leisure. A guy who maintain at most patience even when the whole world rejects him.

This post provides a simple step by step procedure on how to configure and achieve data replication between MS SQL Server database and Oracle database using Oracle GoldenGate software

Note: I
have provided the simple demo on how to perform a replication from an Oracle to
Oracle database in my previous blog post Oracle
GoldenGate simple hands-on demo. Most of the Oracle related steps will
be redirected to refer the blog for details. This post is to provide
information mostly on the prerequisites and how to configure GoldenGate
software on the MS SQL Server database and perform replication (minimum
mandatory setup to achieve replication where you have multiple options on the
parameters available to be used).

Since the post is long, I'll be breaking it to two parts and I have also provided a downloadable copy of the post in this link. Click on images to view full size if required.Part 1 covers all the prerequisites required to be met before attempting Oracle GoldenGate replication and part 2 covers the param file set up along with the actual steps on achiveing replication.

Take a backup of database with transaction logs. Transaction logs
backup is mandatory to be taken and it will be taken along with the database
backup by default in the Full recovery mode. Oracle GoldenGate gets the changed
data from transaction logs in most cases and if it not found in the logs then
it would get it from the transaction log backup and for this reason the backup
should be of native MS SQL Server backup and Oracle GoldenGate doesn’t support
third party backup.

Use the graphical method to take backup by Right click on database (GGATE
in our case) à
Tasks à
Backup as shown below.

Edit the fields as desired and click OK to perform the backup.

If you would like to use command line to take the backup, open New Query in the SQL Server Management
Studio and execute the below commands.

3.4Target
environment setup

Once software installation is completed on the target system, please
refer to Oracle
GoldenGate simple hands-on demo,
section 3. Prerequisites and Oracle GoldenGate software set up and
perform the On Target part of 3.2 OGG software setup

Now once this is done, we have set up our source and target
environment for the GoldenGate replication and let’s continue with the rest of
the operations.

4.Enable
Trandata

Enabling trandata
on the source database would require login to the database and hence use the
created DSN name (Command line 1) to connect to the database using GGSCI. The
defgen parameter file will be created in the command line 3.

We can see that the definition file has been created in the dirdef directory. You can check the
contents of the definition file create if you would like to…

This definition file is needed for Oracle to understand and translate
the data types from MS SQL to Oracle data types. Transfer the generated
definition file E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef\emp.def
to the target server under dirdef
directory of the GoldenGate home. I’m using Filezilla client to transfer the
file and you are free to use any of your tools such as Filezilla, Winscp, etc.

Once the above said steps has been completed, then the rest of the
steps are of same like the Oracle to Oracle replication as described in this post.

I’ll go through the steps quickly with explanations wherever
required. Rest of the steps is available in the next part of the blog post and is available in this link

A person with hard working ability, will power and passion to finish what he starts.
An Oracle database administrator trying to explore every nook and corner possible in Oracle technology. A blogger, a story and poem writer at leisure. A guy who maintain at most patience even when the whole world rejects him.