Ogg:sybase to Oracle Personal

Summary:

• Sybase version: Sybase15 (source side)
• Oracle version: 11g (target side)
• Goldengate version: 11.2.0.1.0 (source side), 12c (target side)
• Personal blog: www.czhiying.cn

Goldengate Synchronization principle:

Oracle goldengate Software is a log-based, structured data replication backup software that enables the source database to synchronize with the target database by parsing the source database online logs or archive logs for incremental changes to the data, and then applying those changes to the target database. The reference figure is as follows:

As you can see, there are two ways to implement Ogg's synchronization:

1. Establish the extract process at the source to capture log changes, the formation of Ogg itself defined trail files, trail files are not stored locally, through the network, directly to the destination, and then the destination through the REPLICAT Process parsing trail file, the change to the target database;
2. Establish the extract process at the source to capture log changes, the creation of the Ogg itself defined trail files, trail files are saved locally, and then set up the data pump process on the source side (actually also extract process, but the function is different, we distinguish it in terms of names) The trail file generated by the data pump process is then transferred to the destination, and then the destination is parsed by the REPLICAT process and the trail file is paid to the target database;

Since there are two ways of synchronizing, there is justification, but I recommend synchronizing the data in the second way. Because this is a local save trail file, there is a guarantee. So the following introduction is also based on the second way to synchronize.

Synchronization ideas:

1. Because the source database and the target database are heterogeneous and the table structure is different, it is necessary to use the table structure Definition tool provided by OGG to define the table structure of the source database and provide it to the target database for parsing.
2. Data synchronization exists with data initialization and data Delta synchronization. Therefore, when preparing for data initialization, it is necessary to ensure that the capture process of the source database is enabled by OGG. This enables data consistency for the source/target database.

Synchronization steps:

1. The source-side Sybase establishes the Goldengate synchronization user, needs the authority sa_role,replication_role;
2. On the library that needs to be synchronized, turn on the second transaction truncation point;

Assuming that the library that needs to be synchronized is test, when you use isql to enter the Sybase command line, execute Use test Go DBCC SETTRUNC (LTM,VALID) Go Objective: The capture process of Ogg can successfully capture the guarantee of transaction log changes, and if this option is not turned on, the extract process start will fail, you can experiment. In fact, if this option is not turned on, when you use Ggsci to add additional logs, Ggsci will also remind you that you do not have a second transaction truncation point option open for a library. Note: The LTM option here can be said to love and hate ah, why say so, please read my other article.

3. Enter the Ggsci console, use Dblogin to access the database, add table-level additional logs;

After entering the GGSCI console, use the command: Dblogin sourcedb data source, userid username, password password Note: Because GG is Oracle's product, other databases need to use ODBC to establish a data source, so that Oracle can access its database, how to set up a data source, you can go to the Niang, there is no introduction. Add additional logs using commands after success: Add Trandata test.test; (The test.test here means adding additional logs to the test table under the Test library.) ） If you add it successfully, you will see the information about enable, and if you don't have the LTM option on the Sync library, you'll see a reminder, but don't worry, you'll be able to open it again, anyway, before the extract process starts.

4. Configure Ogg capture process for the source database (Sybase database);

prm file: EXTRACT Ext_test--Capture process name Sourcedb Test,userid Sa,password 123456 Exttrail Dirdat/te Discardfile DIRRPT/EXT_TEST.DSC, APPEND Reportcount every 10000 RECORDS, rate Gettruncates Table dbo.test; --Declare a table that needs to capture data changes Ggsci Console: Edit params ext_test (a EXT_TEST.PRM parameter file will be created to copy the above parameters into the file) Add extract Ext_test,tranlog,begin Now (create extract process) Add Exttrail dirdat/te,extract ext_test,megabytes 100 (Create Exttrail, record to local trail file after fetching data) To start the extract process: Start Ext_test If there is no accident, the process starts successfully. If this fails to start, please use the command, view report ext_test, review the error reports, and if not, leave a comment and the blogger will see to help you solve it.

5. Source-side configuration, start data pump process

Purpose : Because Ogg records the captured data changes as a trail file of its own and is stored in a local file, the trail files need to be delivered remotely to the target using a data pump process. Ggsci Console: Edit params Dp_test Parameter file configuration: EXTRACT Dp_test Sourcedb Test,userid Sa,password 123456 Rmthost 127.0.0.1 (remote host IP ready for delivery), Mgrport 7810 (port number of the remote host MGR Process) Rmttrail D:\ogg112101_ggs_Windows_x86_ora11g_32bit\dirdat\du Discardfile dirrpt/dumpdsc.dsc,append,megabytes 1024 TABLE dbo.test; Add extract Dp_test,exttrailsource dirdat/te (here to create the data pump process, trail source is the trail path that just captured the process settings) Add Rmttrail d:\ogg112101_ggs_windows_x86_ora11g_32bit\dirdat\du,extract dp_test,megabytes 100 (Create Rmttrail definition here) Start Dp_test If there is no accident, the process opens successfully. One thing to note here is that the computer that is ready to deliver the past must have Ogg installed, because the blogger is using the same computer here, so the blogger installs two Ogg, a for Sybase, a for Oracle, and two different ports.

6. Target side configuration, start application process

objective : To decrypt and analyze the trail files delivered from the source side, and update the incremental data of the source side to the target database synchronously. Target End Ggsci: Edit params Rep_test Parameter file configuration: Replicat Rep_test Sourcedefs Dirdef/def_test.def USERID ogg, PASSWORD 123456 Discardfile dirrpt/rep_test.dsc,append,megabytes 1024 Reperror (DEFAULT, discard) Map dbo.test, TARGET ogg. TEST; dblogin userid ogg, password 123456 (access to Oracle database for the purpose of building a checkpoint table) ADD checkpointtable ogg.chkpt_test (table name is arbitrary, meaningful on the line) Add Replicat rep_test,exttrail dirdat/du,checkpointtable ogg.chkpt_test (here Exttrail, refers to the trail file sent from the source) Start Rep_test If you start successfully, you can try inserting data, updating data, deleting data, and synchronizing it on the Sybase side.

Ogg:sybase to Oracle Personal