Replication Quick Start Guide for Oracle

SQL Server Technical Article

Writers: Matt Hollingsworth; Michael Blythe

Technical Reviewer: Sandra Ward; Deepak Kumar; Gopal Ashok

Project Editor: Jeannine Nelson-Takaki

Designer: Kristie Smith

Published: March 2006

Applies To: SQL Server 2005

Summary: This paper provides a hands-on introduction to publishing data from Oracle to SQL Server. You will be led through a series of tools and wizards to demonstrate the steps that you must follow to configure, test, and monitor Oracle publishing.

On This Page

Introduction
Installing Oracle Client Software and Testing the Connection to the Oracle Server
Configuring the Distributor and Creating the Publication
Verifying the Status of Replication Agents
Configuring Permissions for the Distribution Agent
Creating a Subscription
Monitoring the Publication and Subscription
Verifying That Changes at the Publisher are Replicated to the Subscriber
Conclusion

Introduction

With SQL Server 2005, you can include Oracle Publishers in your replication topology, starting with Oracle version 8.0.5. This new feature is built on the well-established foundation of SQL Server snapshot replication and transactional replication, providing both performance and usability. Publishing servers can be deployed on any Oracle supported hardware and operating system. Publishing data from Oracle is useful in the following scenarios:

Scenario

Description

Microsoft .NET Framework application deployments

Develop applications in Microsoft Visual Studio and SQL Server while operating on data replicated from an Oracle database.

Data warehousing staging servers

Keep SQL Server staging databases synchronized with an Oracle database.

Migration to SQL Server

Test your application in real time against SQL Server while replicating the source system's changes. Switch to SQL Server when satisfied with the migration.

This paper provides a hands-on introduction to publishing data from Oracle to SQL Server. You will be led through a series of tools and wizards to demonstrate the steps that you must follow to configure, test, and monitor Oracle publishing. At the end of the paper, you should have an understanding of Oracle publishing and will have set up a functional replication system with data changes flowing from an Oracle Publisher to a SQL Server Subscriber.

Installing Oracle Client Software and Testing the Connection to the Oracle Server

This section of the paper covers the steps that are required before configuring replication between Oracle and SQL Server:

  1. Installing the Oracle client software and OLE DB provider on the Microsoft SQL Server Distributor.

  2. Verifying that you can make a connection between SQL Server and Oracle.

  3. Creating a replication administrative user in the Oracle database.

  4. For each table that you will publish, granting SELECT permission directly (not through a role) to the Oracle administrative user that you created in step one.

For more information, see "Configuring an Oracle Publisher" in SQL Server Books Online.

First, install the Oracle client software on the instance of SQL Server that will serve as the Distributor:

  1. Open the Oracle Universal Installer from the Oracle client disk. For more information, see the Oracle documentation.

  2. Follow the steps in the Oracle Universal Installer to install the components required by replication. You can select an installation type of Administrator, Runtime, or Custom.

  3. Restart the server.

Figure 1. Installing the Oracle client software

Figure 1. Installing the Oracle client software

Verify the service account under which SQL Server is running on the Distributor. You must grant to this account read and execute permissions for the directory (and all subdirectories) in which the Oracle client networking software is installed.

  1. In Control Panel, open Administrative Tools, and then open Services.

  2. Click the Standard tab and locate SQL Server (MSSQLSERVER).

  3. Right-click the SQL Server service and click Properties.

  4. Click the Log On tab and make a note of the account name.

Figure 2. Verifying the SQL Server service account

Figure 2. Verifying the SQL Server service account

Verify that you can connect from the Distributor to the Oracle database by using Oracle’s SQL*Plus tool. By default for Oracle 10g, the installation directory is <install drive>:\Oracle\product\10.1.0\Client_1\BIN.

The user who runs SQL*Plus must have read and execute permissions for the directory (and all subdirectories) in which the Oracle client networking software is installed. The account used to connect to the Oracle database must have sufficient permissions to execute the script in the next step. In this example, we connect to the Oracle database using the built-in Oracle account system.

  1. Open a Command Prompt window.

  2. Connect to the Oracle database by using SQL*Plus. In the following illustration, "orcl" is the network name of the Oracle database we are connecting to. We use the runas command to run SQL*Plus under an appropriate user, using the following syntax:

    Runas /user: "Adventure-Works\admin1" "sqlplus system/system@orcl"
  3. Leave the Command Prompt window open for the next step.

Cc966428.repl_quickstart_fig3(en-us,TechNet.10).jpg

Figure 3. Verifying the connection between SQL Server and Oracle

After you connect, run the oracleadmin.sql script against the Oracle database. This script creates a replication administrative user and grants the minimum necessary rights to perform replication.

  1. On the SQL Server Distributor, open a Command Prompt window.

  2. Type the following syntax to use SQL*PLUS to connect to the Oracle database and execute the oracleadmin script from its default install directory:

    Note: The line has been split into multiple lines for readability. However, while trying it out on a system you must enter it as one line without breaks.

            sqlplus system/system@orcl @"c:\ Program Files\Microsoft SQL
            Server\<InstanceName>\MSSQL\Install\oracleadmin.sql"
          
  3. Specify the user name, user password, and default table space.

Cc966428.repl_quickstart_fig4(en-us,TechNet.10).jpg

Figure 4. Creating a replication administrative user

For the tables you want to publish, use SQL*PLUS to grant SELECT permissions on an Oracle table to the replication administrative user that you created in the previous step.

  1. On the SQL Server Distributor, open a Command Prompt window.

  2. Type the following syntax to connect to the Oracle database using the scott sample user provided by Oracle:

    sqlplus scott/tiger@orcl
  3. At the SQL command prompt, type the GRANT statement followed by the table and user name:

            grant select on dept to sql_replication_user;
            grant select on emp to sql_replication_user;
          
  4. Close the Command Prompt window.

Cc966428.repl_quickstart_fig5(en-us,TechNet.10).jpg

Figure 5. Granting SELECT permissions on Oracle tables

Now that you have prepared the Oracle database and SQL Server Distributor, in the next section you will use the tools in SQL Server 2005 to configure replication.

Configuring the Distributor and Creating the Publication

This section of the paper describes how to create a publication, using SQL Server 2005 replication and data from an Oracle database. If the instance of SQL Server 2005 is not already configured as a Distributor, it will be configured when the publication is created.

First, you must connect to the SQL Server Distributor:

  1. Open SQL Server 2005 Management Studio.

  2. If you are not already connected to the instance of SQL Server that will serve as the Distributor, click Object Explorer and select the instance.

Figure 6. Connecting to the Distributor

Figure 6. Connecting to the Distributor

After you have connected to the instance, start the New Publication Wizard.

  1. Expand the server node.

  2. Expand the Replication folder.

  3. Right-click the Local Publications folder and then click New Oracle Publication.

The rest of this section covers the steps in the wizard in more detail.

Figure 7. Starting the New Publication Wizard

Figure 7. Starting the New Publication Wizard

The New Publication Wizard introduction page is displayed.

  • Click Next.

Figure 8. New Publication Wizard introduction page

Figure 8. New Publication Wizard introduction page

If the SQL Server instance that you connected to has not been previously configured as a Distributor, the Distributor page is displayed. If you have already configured the instance, you will skip this step and the next step.

  • Verify that the instance that you are connected to is selected as the Distributor and click Next.

Figure 9. Specifying the Distributor

Figure 9. Specifying the Distributor

If the SQL Server instance has not been previously configured as a Distributor, the SQL Server Agent Start page is displayed. By default, SQL Server Agent runs the replication agents that are used to track changes and distribute data. Therefore, in most cases, you want to make sure that SQL Server Agent is always running on that instance.

  • Select the option to start SQL Server Agent automatically. Click Next.

Figure 10. Specifying how SQL Server Agent starts

Figure 10. Specifying how SQL Server Agent starts

On the Oracle Publisher page, you must select an Oracle database to act as the Publisher. If the instance of SQL Server has not been previously configured as a Distributor, the wizard will also configure the instance, and display a confirmation dialog box.

  1. Click Add Oracle Publisher.

  2. Click Yes to allow configuration of the Distributor.

Cc966428.repl_quickstart_fig11(en-us,TechNet.10).jpg

Figure 11. Adding an Oracle Publisher

In the Distributor Properties dialog box, configure the Distributor with the connection information necessary to connect to the Oracle database.

  1. On the Publishers page, click Add, and then click Add Oracle Publisher.

  2. In the Connect to Server dialog box, enter the Oracle Net alias for the Oracle database in the Server instance field.

  3. Select Oracle Standard Authentication (recommended) or Windows Authentication.

    1. If you select Oracle Standard Authentication, enter the login and password of the replication administrative user schema you created on the Oracle Publisher during configuration.

    2. If you select Windows Authentication: the Oracle server must be configured to allow connections using Windows credentials (for more information, see the Oracle documentation); and you must be currently logged in with the same Microsoft Windows account you specified for the replication administrative user schema.

  4. (Optional). To change the publishing type, click Options.

    For Oracle publishing, replication offers two publishing types: Complete or Oracle Gateway, with a default of Oracle Gateway. After a Publisher is identified, this option cannot be changed without dropping and reconfiguring the Publisher. The Complete option is designed to provide snapshot and transactional publications with the complete set of supported features for Oracle publishing. The Oracle Gateway option provides specific design optimizations to improve performance for cases where replication serves as a gateway between systems.

  5. Click Connect to verify connectivity.

Figure 12. Specifying the connection properties

Figure 12. Specifying the connection properties

After the connection succeeds, the Oracle Publisher is added to the list of Publishers in the Distributor Properties dialog box. If the connection fails, verify that all previous setup steps completed successfully. For more information about troubleshooting connection issues, see "Troubleshooting Oracle Publishers" in SQL Server Books Online.

  • Click OK to save changes and close the dialog box.

Figure 13. Saving the Distributor configuration

Figure 13. Saving the Distributor configuration

On the Oracle Publisher page, click Next.

Figure 14. Adding the Oracle Publisher

Figure 14. Adding the Oracle Publisher

On the Publication Type page, you select the type of replication to use. Select Snapshot publication if you want all the published data to be copied every time replication executes. Select Transactional publication if you want to start by copying all the published data and then continuously stream subsequent data changes to SQL Server in near real time.

Note   Transactional replication for Oracle creates triggers on the published Oracle tables. For more information, see the topic "Transactional Replication Workflow for Oracle Publishers" in SQL Server Books Online.

  • Select the publication type and click Next.

The rest of this paper assumes that you selected Transactional publication.

Figure 15. Specifying the publication type

Figure 15. Specifying the publication type

On the Articles page, select the tables that you want to publish from the Objects to publish list. If there are columns of data that you do not want to replicate, you can remove the columns from the published table by clearing the check box next to each column.

Note    A table can appear among the Objects to publish only if SELECT permissions for the table have been granted directly to the replication administrative user.

Figure 16. Selecting the tables to replicate

Figure 16. Selecting the tables to replicate

If a column contains an Oracle data type that might result in data loss when it is converted to a SQL Server data type, a yellow warning sign is displayed next to the column. In the following illustration, the HIREDATE column contains date data that might result in data loss. You can optionally select an alternative to the default SQL Server data type. First, access the properties for the published table.

  1. Select the table in the Objects to publish list and then click Article Properties.

  2. Click Set the Properties of Highlighted Table Article.

In the next step, you can select an alternative data type.

Figure 17. Accessing properties of published tables

Figure 17. Accessing properties of published tables

You can modify the data type that is used at the Subscriber by selecting a different SQL Server data type from the Subscriber data type list.

  1. In the Article Properties dialog box, click the Data Mapping tab, and modify the data types for individual columns as necessary.

  2. Click OK.

  3. On the Articles page, click Next.

Figure 18. Changing data types for Subscriber data

Figure 18. Changing data types for Subscriber data

You can optionally specify that you require only a subset of the data to be published. To specify a filter for a table, you specify a WHERE clause using Oracle-compliant syntax.

  1. On the Filter Table Rows page, click Add.

  2. In the Add Filter dialog box, select a table to filter from the list.

  3. In the Filter statement text area, type a WHERE clause.

  4. Click OK.

  5. Click Next.

Figure 19. Filtering data

Figure 19. Filtering data

On the Snapshot Agent page, select whether you want to start creating snapshot data files immediately after the wizard finishes, or at a later time.

  1. Select the option to create a snapshot immediately.

  2. Click Next.

Figure 20. Scheduling the snapshot

Figure 20. Scheduling the snapshot

The Snapshot Agent and Log Reader Agent pull data from the Oracle Publisher. On the Agent Security page, you specify the Windows user under which each agent should run.

  • Each user must be a member of the sysadmin fixed server role on the SQL Server Distributor.

  • Additionally, the Windows user under which the Snapshot Agent runs must have write permissions on the snapshot folder. By default, the snapshot folder is located at <drive>\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\.

  1. Click Security Settings to specify settings for each agent. If you want to specify the same settings for the Log Reader Agent and Snapshot Agent, specify the Snaphot Agent settings first and then select Use the security settings from the Snapshot Agent.

  2. Click Next.

Figure 21. Setting security options for replication agents

Figure 21. Setting security options for replication agents

On the Wizard Actions page, you specify when the publication will be created, and whether the publication configuration is saved in a script. We recommend that you select the check box for scripting so that you will have a copy of the replication configuration for future reference.

  1. Select both check boxes.

  2. Click Next.

Figure 22. Scripting and creating the publication

Figure 22. Scripting and creating the publication

On the Complete the Wizard page, you can verify that all options are correct, and provide a name for the publication.

  1. Type a name for the publication.

  2. Verify options, and click Back to change your selections if necessary.

  3. Click Finish.

Figure 23. Completing the New Publication Wizard

Figure 23. Completing the New Publication Wizard

The New Publication Wizard now saves the configuration information that you have entered and creates the publication.

  • Click Close after the steps are finished.

Figure 24. Creating the publication

Figure 24. Creating the publication

The publication that you created appears under the Replication folder in SQL Server Management Studio.

Figure 25. Viewing the new publication

Figure 25. Viewing the new publication

Verifying the Status of Replication Agents

If you configured the Snapshot Agent to start immediately as recommended, you can now verify the progress of the Snapshot Agent by using Replication Monitor. This section of the paper describes how to configure Replication Monitor and how to view the status of the Snapshot Agent.

  • In SQL Server Management Studio, right-click the Local Publications folder and then click Launch Replication Monitor.

Figure 26. Starting Replication Monitor

Figure 26. Starting Replication Monitor

Before you can monitor the agents associated with an Oracle Publisher, you must add the Publisher to Replication Monitor.

  • In Replication Monitor, right-click My Publishers, and then click Add Publisher.

Figure 27. Adding a Publisher to Replication Monitor

Figure 27. Adding a Publisher to Replication Monitor

In the Add Publisher dialog box, you can specify the Publisher type and optionally add the Publisher to a group. A Publisher group provides a convenient way to organize Publishers within Replication Monitor. Groups do not affect the replication of data or the relationship among servers in a replication topology.

  1. Click Add and select Add Oracle Publisher.

  2. Optionally create a group for the Oracle Publisher by clicking New Group. By default, all Publishers are displayed under the My Publishers group node in Replication Monitor.

Cc966428.repl_quickstart_fig28(en-us,TechNet.10).jpg

Figure 28. Specifying a Publisher to monitor

A dialog box is displayed; it warns you that you must specify the SQL Server Distributor when configuring monitoring for an Oracle Publisher.

  • Click OK.

Figure 29. Choosing a Distributor

Figure 29. Choosing a Distributor

Oracle Publishers are monitored through the SQL Server Distributor; therefore you must connect to the Distributor to configure monitoring. In the Connect to Server dialog box, specify the connection information for the SQL Server Distributor.

  1. Select the name of the instance that you specified as the Distributor.

  2. Select the authentication method.

  3. Click Connect.

Figure 30. Connecting to the Distributor

Figure 30. Connecting to the Distributor

The Net alias for the Oracle Publisher that you specified in the first section of this paper is listed in the Connection grid. Optionally, you can add the Publisher to a group.

  • Click OK.

Figure 31. Saving the monitoring configuration

Figure 31. Saving the monitoring configuration

The Oracle Publisher is now listed under the My Publishers node of Replication Monitor. The right pane of Replication Monitor is populated, but does not show any subscriptions to the publication at this time. You will create a subscription later in this paper.

  1. Expand the Publisher node (ORCL (Oracle) in the diagram), and then select the newly created publication.

  2. Click the Warnings and Agents tab.

Cc966428.repl_quickstart_fig32(en-us,TechNet.10).jpg

Figure 32. Viewing the new publication in Replication Monitor

Verify the status of the Log Reader Agent and the Snapshot Agent. If the status of either agent is incorrect, verify that SQL Server Agent is running and that the Windows user and password you specified for each agent are correct before you continue.

  1. In the lower pane, titled Agents and jobs related to this publication, verify that the status of the Log Reader Agent is Running.

  2. In the same pane, verify that status of the Snapshot Agent status is Running or Completed.

  3. Double-click the Snapshot Agent row to open a dialog box that shows the history of the snapshot process.

Figure 33. Viewing status of the Snapshot Agent and Log Reader Agent

Figure 33. Viewing status of the Snapshot Agent and Log Reader Agent

In the Snapshot Agent dialog box, view the history of the snapshot process.

  1. In the pane titled Sessions of the Snapshot Agent, verify that the status is Completed.

  2. Close the Snapshot Agent dialog box

  3. Close Replication Monitor.

Cc966428.repl_quickstart_fig34(en-us,TechNet.10).jpg

Figure 34. Viewing details of Snapshot Agent history

Configuring Permissions for the Distribution Agent

Now that the publication has been created and the Snapshot Agent has been configured to pull data from the Oracle database into a set of snapshot files, you will configure permissions for the Distribution Agent, which delivers changes to the SQL Server Subscriber. Make sure that there is a Windows user on the Distributor computer under which the Distribution Agent can run.

The snapshot files are stored in a snapshot folder. By default the snapshot folder is located at <drive>\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\. We recommend that you configure a Windows share for this folder. The Windows user that runs the Distribution Agent must have read permissions on the share. For more information about how to configure shares and assigning permissions to shares, see the Windows documentation.

The following illustration shows the default folder used by SQL Server 2005 replication for storing snapshots.

  1. In My Computer, right-click the snapshot folder, and click Sharing and Security.

  2. Select Share this folder.

  3. Type a name and description for the share.

Figure 35. Configuring snapshot folder properties

Figure 35. Configuring snapshot folder properties

Configure the snapshot share so that the Windows user under which the Distribution Agent runs has read permissions to the folder:

  1. Click Permissions.

  2. In the Permissions for repldata dialog box, click Add and locate the Windows user under which the Distribution Agent runs.

  3. Select the Read check box.

  4. Click OK.

Figure 36. Configuring permissions to the snapshot folder

Figure 36. Configuring permissions to the snapshot folder

The next step is to add the Windows user to the Publication Access List (PAL). The PAL contains all Windows users and groups that have permission to synchronize with the publication.

  1. In SQL Server Management Studio, in Object Explorer, expand Replication.

  2. Expand Local Publications, and right-click the publication.

  3. Click Properties.

Figure 37. Accessing the Publication Properties dialog box

Figure 37. Accessing the Publication Properties dialog box

In the Publication Properties dialog box, make sure that the Windows user is listed in the PAL.

  1. Click the Publication Access List page.

  2. If the Windows user is not in the Publication access list pane, click Add.

  3. Locate the user and click OK to add the user to the PAL.

  4. Click OK to close the Publication Properties dialog box.

Note The Windows user must be a valid SQL Server login that is already associated with a database user in the publication database.

Figure 38. Adding a user to the PAL

Figure 38. Adding a user to the PAL

Creating a Subscription

This section of the paper describes how to create a subscription to the publication you created. You create a subscription to the Oracle publication by using the New Subscription Wizard.

  1. In SQL Server Management Studio, right-click the publication you created.

  2. Click New Subscriptions.

Figure 39. Starting the New Subscription Wizard

Figure 39. Starting the New Subscription Wizard

The New Subscription Wizard introduction page is displayed.

  • Click Next.

Figure 40. New Subscription Wizard introduction page

Figure 40. New Subscription Wizard introduction page

On the Publication page, the publication you created is displayed and selected by default.

  • Click Next.

Figure 41. Selecting the publication

Figure 41. Selecting the publication

On the Distribution Agent Location page, select the location at which the Distribution Agents will run. If there are less than fifty Subscribers, we recommend the default of running the Distribution Agent at the Distributor.

  1. Select Run all agents at the Distributor (push subscriptions)

  2. Click Next.

Figure 42. Specifying the Distribution Agent location

Figure 42. Specifying the Distribution Agent location

On the Subscribers page, select the Subscriber instance that will receive the replicated data from the Oracle Publisher. You must also specify whether the Subscriber is another instance of SQL Server or a different type of database provider.

  1. Click Add Subscriber.

  2. Select Add SQL Server Subscriber.

  3. Click Next.

Figure 43. Adding a Subscriber

Figure 43. Adding a Subscriber

Specify the connection information for the SQL Server Subscriber.

  1. In the Connect to Server dialog box, select the instance of SQL Server that will be the Subscriber.

  2. Specify the authentication method that is used to connect to the Subscriber.

  3. Click Connect to save the connection information.

Note   In the configuration used for this paper, the Distributor and Subscriber are on the same instance of SQL Server. Therefore the connection settings in the following illustration are the same as those shown earlier in the paper.

Figure 44. Specifying connection properties for the Subscriber

Figure 44. Specifying connection properties for the Subscriber

Next, you select the database on the Subscriber that will receive the replicated data. If the subscription database does not exist, you can create it by selecting New database.

  1. In the Subscription Database list, select a database, or click New database.

  2. (Optional) To add other Subscribers, click Add Subscriber and repeat the process. For each Subscriber, you must connect to a Subscriber and specify a subscription database.

  3. Click Next.

Figure 45. Specifying a subscription database

Figure 45. Specifying a subscription database

The Distribution Agent Security page of the wizard lets you configure Agent security for each Subscriber that you have added, to ensure that the Distribution Agents have the correct permissions.

  • Click the browse button (...) for each Subscriber to open the Distribution Agent Security dialog box.

Figure 46. Reviewing connection properties

Figure 46. Reviewing connection properties

In the Distribution Agent Security dialog box, select the Windows user that you configured before you started the New Subscription Wizard. The Windows user that you select must meet the following conditions:

  • The user is included in the PAL.

  • The user is a member of the db_owner fixed database role in the subscription database.

  • The user has read permissions on the snapshot share.

Specify the Windows user under which the Distribution Agent will run.

  1. Type the domain and user name.

  2. Click OK to save changes and close the dialog box

  3. On the Distribution Agent Security page, click Next.

Figure 47. Specifying Distribution Agent security

Figure 47. Specifying Distribution Agent security

By default, the Distribution Agent runs continuously and delivers changes from the Publisher to the Subscribers as changes become available.

  1. Verify that Run Continuously is selected in the Agent Schedule column.

  2. Click Next.

Figure 48. Scheduling synchronization

Figure 48. Scheduling synchronization

Use the Initialize Subscriptions page of the wizard to specify how the publication is initialized. When the Distribution Agent first runs for a subscription, by default it delivers the complete snapshot of initial data to the Subscriber. This initialization occurs immediately after the New Subscription Wizard finishes.

  1. If the Subscriber already contains all the initial tables and data, clear the Initialize option on the Initialize Subscriptions page.

  2. Select Immediately in the Initialize When list to specify when initialization will occur.

  3. Click Next.

Figure 49. Setting initialization options

Figure 49. Setting initialization options

On the Wizard Actions page, you specify when the subscription will be created, and whether the subscription configuration is saved in a script. We recommend that you select the check box for scripting so that you will have a copy of the replication configuration for future reference.

  1. Select both check boxes.

  2. Click Next.

Figure 50. Generating the publication and script file

Figure 50. Generating the publication and script file

On the Complete the Wizard page, verify that all options are correct.

  • Click Finish.

Figure 51. Verifying wizard options

Figure 51. Verifying wizard options

The New Subscription Wizard now saves the configuration information that you have entered and creates the subscription.

  • Click Close after the steps are complete.

Figure 52. Creating the subscription

Figure 52. Creating the subscription

After the wizard has completed, you can view the subscriptions you created in SQL Server Management Studio.

  1. In Object Explorer, expand Replication.

  2. Expand Publications.

  3. Right-click the publication you created and click Refresh.

  4. Expand the publication.

Figure 53. Viewing the new subscription

Figure 53. Viewing the new subscription

Monitoring the Publication and Subscription

This section of the paper describes how to use Replication Monitor to monitor the publication and subscription you created.

  1. In SQL Server Management Studio, expand Replication.

  2. Expand Local Publications.

  3. Right-click the Local Publications folder and then click Launch Replication Monitor.

    Figure 54. Starting Replication Monitor

    Figure 54. Starting Replication Monitor

  4. In Replication Monitor, expand My Publishers.

  5. Expand the Publisher node (ORCL (Oracle) in the figure).

  6. Select the newly created publication.

Subscription status is displayed on the All Subscriptions tab in the right pane. The time it took for data to replicate from the Oracle Publisher to the Subscriber is listed in the Latency column, and a relative rating of this performance is listed in the Performance column. Because initial snapshots take more time to generate and deliver than incremental changes, the initial latency is typically much greater than the latency for incremental changes.

Figure 55. Viewing subscription status in Replication Monitor

Figure 55. Viewing subscription status in Replication Monitor

Latency between the Publisher and Subscriber is calculated by measuring two intervals in replication processing:

  • The time that is required from when a change is made at the Publisher until the Log Reader Agent delivers the change to the distribution database.

  • The time that is required for the Distribution Agent to deliver the change from the distribution database to the subscription database.

To view the details of the replication processing involved in these steps, you can double-click the subscription row in the right pane. The Distributor To Subscriber History tab shows the history for the selected Distribution Agent.

Figure 56. Viewing Distribution Agent history

Figure 56. Viewing Distribution Agent history

The Publisher To Distributor History tab displays the history for the Log Reader Agent.

Cc966428.repl_quickstart_fig57(en-us,TechNet.10).jpg

Figure 57. Viewing Log Reader Agent history

The Undistributed Commands tab displays information about the number of commands in the distribution database that have not been delivered to the selected Subscriber, and the estimated time to deliver those commands.

  • Close the dialog box and return to the main Replication Monitor interface.

Figure 58. Viewing the number of commands in the distribution database

Figure 58. Viewing the number of commands in the distribution database

Tracer tokens are used to diagnose the current performance of transactional replication. A token, which is a small amount of data, is written to the transaction log of the publication database, marked as if it were a typical replicated transaction, and then sent through the system. The elapsed time is then measured.

You can create and monitor tracer tokens by using Replication Monitor.

  1. In Replication Monitor, click the Tracer Tokens tab.

  2. Click Insert Tracer.

Cc966428.repl_quickstart_fig59(en-us,TechNet.10).jpg

Figure 59. Adding a tracer token for a publication

After a short time, Replication Monitor displays three numbers as indicators of transactional replication performance: the time to replicate the tracer token from the publication database to the distribution database; the time to replicate the tracer token from the distribution database to the subscription database; and the total elapsed time. If one of the agents is stopped and the tracer cannot be replicated, Replication Monitor displays Pending until the agent is restarted.

  1. Verify that a value is displayed in the Total Latency column.

  2. Close Replication Monitor.

Cc966428.repl_quickstart_fig60(en-us,TechNet.10).jpg

Figure 60. Viewing elapsed time for the tracer token

Verifying That Changes at the Publisher are Replicated to the Subscriber

You have now successfully configured replication from Oracle to SQL Server, and you have used Replication Monitor to verify the status and performance of the system. If you created a transactional publication, you can make changes to data at the Oracle Publisher and then observe the successful replication of those changes to the SQL Server Subscriber.

  • Open Oracle SQL*Plus and connect to the Oracle Publisher database. For more information, see the Oracle documentation.

Figure 61. Opening Oracle SQL*Plus

Figure 61. Opening Oracle SQL*Plus

To test replication, make changes to one or more published tables by using SQL*Plus command line statements.

The example in the illustration updates one row in the DEPT table, and inserts a new row into the same table.

The changes must then be explicitly committed. Changes cannot be replicated until they are committed, because by default Oracle does not implicitly commit SQL statements.

Cc966428.repl_quickstart_fig62(en-us,TechNet.10).jpg

Figure 62. Making changes in the Oracle database

To verify that these changes are replicated, you can view the new data in the subscription database.

  1. Open SQL Server Management Studio.

  2. Click New Query and connect to the Subscriber.

Figure 63. Creating a query at the Subscriber

Figure 63. Creating a query at the Subscriber

In the Query window, type the following SELECT statement to verify that the rows modified at the Oracle Publisher have been replicated to the SQL Server Subscriber.

        USE test
        SELECT * from dept
      

The query returns all rows from the replicated table. The name of DEPT 30 has been changed to HQSALES and a new record has been added for WEST SALES. These results show that replication was successful.

Figure 64. Verifying changes in the subscription database

Figure 64. Verifying changes in the subscription database

Conclusion

This paper provided an introduction to Oracle publishing, with a focus on how to use graphical tools and wizards. If you completed all the steps, you should have an understanding of Oracle publishing; additionally, you have configured a functional replication system with data changes flowing from an Oracle Publisher to a SQL Server Subscriber

We recommend reading SQL Server Books Online for more information about Oracle publishing, with attention to the following topics:

  • Configuring an Oracle Publisher

  • Design Considerations and Limitations for Oracle Publishers

  • Administrative Considerations for Oracle Publishers

  • Data Type Mapping for Oracle Publishers

  • Troubleshooting Oracle Publishers

To manage replication programmatically, we recommend that you review the application programming interface (API) references.

Download

DownloadRepl_Quickstart_for_Oracle.doc
2.14 MB
Microsoft Word file