In this section you'll experience the strength and flexibility of OGG to replicate disparate systems. In this recipe, we'll be using SQL Server 2008 as the source database and Oracle 11gR2 as the target database.
Once again, you can obtain the OGG software for SQL Server from http://otn.oracle.com (click on the DOWNLOADS tab, scroll down to the Middleware section, and click on GoldenGate) for trial purposes; alternatively, if you have a valid email / password account go to My Oracle Support and buy a license to use OGG, from http://edelivery.oracle.com.
One thing we need to cover quickly is the steps to install OGG for SQL Server, which are as follows:
Download and extract the OGG for SQL Server to a location of your choice, for example,
C:\GG.Open the command prompt and launch
ggsciby running the following commands:C:\ggsci.exe GGSCI> create subdirs
Next we want to add the manager process as a Windows service; otherwise the manager process will stop upon session exit. Add it by using the following command:
GGSCI>install addserviceThe output of the preceding command will be as follows :
Service 'GGSMGR' created.Edit the
Managerparameter file as follows:GGSCI> edit param mgrAdd the following entries to the file:
PORT 7809Save the file and exit.
Next start the manager by using the following command:
GGSCI> start manager
Next we'll be creating an Open DataBase Connectivity (ODBC) connection on the source (SQL Server) database by using the following steps:
Create an ODBC connection for the data source name for userid
oggand passwordoggCreating an ODBC connection is beyond the scope of this recipe. However, use the Windows ODBC wizard to guide you through with similar settings as follows:
Data Source Name: sample Data Source Description: Sample_schema Server: servername\instance Database: sample Language: (Default) Translate Character Data: Yes Log Long Running Queries: No Log Driver Statistics: No Use Regional Settings: No Prepared Statements Option: Drop temporary procedures on disconnect Use Failover Server: No Use ANSI Quoted Identifiers: Yes Use ANSI Null, Paddings and Warnings: Yes Data Encryption: No
Add supplemental logging to the capture table changes on
tcustmeras follows:C:\GG>ggsci.exe GGSCI> dblogin sourcedb sample GGSCI> add trandata dbo.tcustmer
Create a definitions file as follows:
C:> cd C:\GG\dirprm C:\GG> notepad tcustmer.prm
Add the following lines to the file:
defsfile c:\GG\dirdef\tcustmer.def sourcedb sample table dbo.tcustmer
Save the file and exit.
Generate the definitions file as follows:
C:\GG\defgen paramfile c:\GG\dirprm\tcustmer.prmTransfer the generated file
c:\GG\dirprm\tcustmer.defto the target server under the OGG installation/u01/app/oracle/gg/dirprm. Use either the FTP or SFTP protocol to transfer the file.For setting up the initial data load from SQL Server (source) use the following command:
GGSCI> edit param initloadAdd the following lines to the file:
SOURCEISTABLE SOURCEDB sample, USERID "ogg", PASSWORD "ogg" RMTHOST unix_server_name, MGRPORT 7809 RMTFILE /u01/app/oracle/gg/dirdat/xp TABLE dbo.tcustmer;
Save the file and exit.
We will now do the target setup (Oracle database server) as follows:
As we saw in the recipes Installing Oracle GoldenGate (Simple) and Creating one-way replication (Simple), create a
Managerparameter file and ensure you use port 7809 and start it. We only need the port number in themgr.prmfile for this task.Create a one-time replicat parameter file called
tcust_ldand start the replicat using the following commands:$ cd /u01/app/oracle/gg/dirprm $ vi tcust_ld.prm
Add the following lines to the file:
SPECIALRUN ENDRUN SETENV (ORACLE_SID="TRG101") SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_1") SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8") USERID ogg PASSWORD ogg EXTFILE /u01/app/oracle/gg/dirdat/xp # must match the rmtfile from the # source parameter file. SOURCEDEFS /u01/app/oracle/gg/dirdef/tcustmer.def MAP dbo."TCUSTMER" , TARGET SCOTT.TCUSTMER ;
The procedure for running the initial load from SQL Server is as follows:
Initiate the load from the SQL Server database by using the following command:
C:\GG\extract paramfile dirprm\initload.prm reportfile dirrpt\initload.rptInitiate the replication on the target server by using the following commands:
$ cd /u01/app/oracle/gg $ ./replicat paramfile dirprm/tcust_ld.prm
This entire setup is a one-time special run to get the initial table data from the source to the target; in other words, this is one method to instantiate to the target when a source database table has existing data. Once the table data has been completely copied, you will need to perform the same setup we've completed in Installing Oracle GoldenGate (Simple).
The assumption here is that you already have a SQL server database in place. The first part during the preparation of the SQL server setup is to create a data source name via the ODBC connection for SQL Server authentication.
During the preparation on the source, you might have wondered about the source definition file in step 4. This file is absolutely necessary especially for data type mapping between different RDBMS types. It can also be used within the same RDBMS types if there are column mappings, transformation, data type size differences, database character set differences, and so on.
Steps 4 to 6 demonstrate how to create and generate the tcustmer table definition. The defgen utility takes as input the contents of tcustmer.prm. The result of running defgen, is a metadata file called tcustmer.def which you need to transfer to the target server and add it to either the replicat parameter file or the one-time special run parameter file so that OGG can perform appropriate data mapping on the target database. An example is as follows:
SOURCEISTABLESOURCEDB sample, USERID "ogg", PASSWORD "ogg"
SOURCEISTABLE, states that the source is the actual SQL Server table rather than the trail files. And finally the connection string to a SQL Server database uses the additional keyword SOURCEDB to identify the data source name.
On the target setup (Oracle database server), in the parameter file tcust_ld.prm we see a few new keywords we have not yet seen for an initial load:
SPECIALRUNENDRUN
SPECIALRUN implements an initial-load replicat as a one-time run that does not use checkpoints. ENDRUN directs the initial-load replicat to terminate when the load is finished.
EXTFILE in the Replicat process specifies the receiving files from the RMTFILE file in the Extract parameter file at the source. Finally, SOURCEDEFS must reference the tcusmer.def file which is the file you transferred from the source site to the target server. This is the definitions file that Oracle has to use in order to correctly interpret the SQL Server data types. In contrast, if you recall, in the recipe, Implementing design considerations (Simple) and the recipe, Installing Oracle GoldenGate (Simple) we used ASSUMETARGETDEFS because both source and target table definitions had identical data types, same National Language Support (NLS) language and character set.
Running the initial load and initiating the replication is a different method of invoking the OGG executables extract and replicat from the command line rather than within the OGG command line interface.