Using Toad for Oracle with SharePlex 9.0

    Jun 13, 2018 8:32:00 AM by Deepak Vohra

    SharePlex is a database replication tool for replication in real-time. SharePlex does not incur a downtime for the source database, which makes it suitable for a high availability environment. SharePlex supports Oracle and SQL Server as source databases. SharePlex supports Oracle database’s different versions (10g, 11g, 12c) and editions (Standard and Enterprise) in different setups (single-instance, cluster, remote, cloud) and environments (ASM, RAC, Exadata).  For replicating Oracle database data, SharePlex supports several data sources as target in addition to Oracle database – MySQL, SQL Server, PostgreSQL, EDB (EnterpriseDB) Postgres Advanced server, SAP Adaptive Server Enterprise (ASE), Teradata, JMS queues, SQL and XML file, and Apache Kafka. For replicating SQL Server the only supported target data sources are SQL Server and Oracle database. 

    In this tutorial we shall discuss using Toad for Oracle for database management with SharePlex. We shall replicate Oracle Database 12c running on Windows 2012 on AWS. To demonstrate Oracle database replication we shall replicate a table to another table in the same Oracle database instance.  We shall use Toad for Oracle for the following database administration tasks.

    • Set DBA User (SYSTEM) Password
    • Enable Minimal Supplemental Logging
    • Explore Database Schemas and Tables created by SharePlex
    • Add Data to a SharePlex Demo Source Table
    • Explore Replicated Data in a SharePlex Demo Target Table

    This tutorial has the following sections.

    • Setting the Environment
    • Installing Oracle Database 12c
    • Enabling Minimal Supplemental Logging in Oracle Database
    • Running SharePlex Database Utility for Oracle Database
    • Exploring the Shareplex Database Schemas and Tables
    • Creating a SharePlex Configuration File
    • Activating the Configuration File
    • Adding Data to Shareplex DEMO_SRC  Oracle Database  Table
    • Listing Replicated Data in DEMO_DEST Table
    • Deactivating the Replication

     

    Setting the Environment

    SharePlex supports various distributions of Windows, Linux, AIX, HP-UX and Solaris. We shall be using Windows 2012 R2 installed on AWS EC2. For AMI choose the 1-Click Secured Windows File Server on Windows 2012 R2 shown in Figure 1.

    Figure 1. Selecting an AMI

    An EC2 instance for Windows 2012 is shown in the Dashboard in Figure 2.

    Figure 2. EC2 Instance running Windows 2012

    Next, configure the Security Group associated with the EC2 instance to allow All Inbound/Outbound traffic if not already configured when launching the instance. If the EC2 instance is required to connect with database instances running remotely, the Inbound/Outbound rules need to be configured.

    The Chrome RDP (Remote Desktop) is used to access a remote Windows instance running on EC2 from a local Chrome browser.  To install Chrome RDP, search for “Chrome RDP” on Chrome Web Store. Click on Add To Chrome as shown in Figure 3.

    Figure 3. Installing Chrome RDP

    The following software must be installed on the Windows 2012 instance on AWS.

    The Windows 2012 running in a Chrome RDP (Remote Desktop) is shown in Figure 4.

    Figure 4. Chrome RDP (Remote Desktop) for Windows 2012

     

    Installing Oracle Database 12c

    Download Oracle Database 12c R2 (12.2.0.1.0) for Windows (x64) winx64_12201_database.zip from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-windows-3633015.html as shown in Figure 5.

    Figure 5. Downloading winx64_12201_database.zip

    Extract the zip file to a directory (C:Oracle). To install Oracle Database 12c double-click on the setup application in the database directory as shown in Figure 6. 

    Figure 6. Using the setup Application to install Oracle Database 12c

    Install Oracle Database with the Oracle Universal Installer. In Installation Option select Create and configure a database. In System Class choose Desktop Class. In Oracle Home User choose Use Virtual Account. The parameter or setting that we shall be using to connect to Oracle Database from SharePlex is the database name. The Global database name is set to orcldb as shown in Figure 7.

    Figure 7. Global Database Name is orcldb

    After the Prerequisite checks, a Summary of the installation gets displayed. Click on Install as shown in Figure 8.

    Figure 8. Install

    Oracle Database 12c gets configured and installed, as shown in Figure 9.

    Figure 9. Configuring and Installing Oracle Database

    Oracle Database gets installed, as shown in Figure 10.

    Figure 10. Oracle Database Installed

    In a subsequent section we shall use the SharePlex Database Utility for Oracle (ora_setup) to configure Oracle Database to be used with SharePlex. When the ora_setup utility is run, an Oracle user needs to be provided; and we shall use SYSTEM user. To be able to use the SYSTEM user we need to set a password for the user and open the account.  To update the SYSTEM user login with user SYS as SYSDBA with Toad for Oracle.  First, add a Login Record for SYS user in Toad for Oracle as shown in Figure 11.

    Figure 11. Connecting with User SYS AS SYSDBA in Toad for Oracle

    A connection for user SYS gets added, as shown in Figure 12.

    Figure 12. Connection Added for User SYS

    Open a SQL Editor and add the following command to set password for user SYSTEM and unlock the account.

    ALTER USER SYSTEM IDENTIFIED BY <PASSWORD> ACCOUNT UNLOCK

    Click on Execute as script as shown in Figure 13.

    Figure 13. Execute as Script

    The SYSTEM user gets modified, as shown by the output User altered in Figure 14.

    Figure 14. Setting Password for SYSTEM User

    To be able to run the ora_setup database utility for Oracle Database we also need to install the Oracle Instant Client for Microsoft Windows (x64). Download and extract the instantclient-basic-windows.x64-12.2.0.1.0.zip. Copy the directory path for the directory in which the Oracle Instant Client for Microsoft Windows (x64) zip file is extracted and add the directory path at the beginning of the PATH environment variable. (The directory path should have sub-directories VC10, VC11, VC12).

     

    Enabling Minimal Supplemental Logging in Oracle Database

    For Oracle database replication the Oracle minimal level supplemental logging needs to be enabled. First, find whether minimal supplemental logging is already enabled with the following command in a SQL Editor.

    SELECT supplemental_log_data_min FROM v$database;

    The output is No, as shown in Figure 15.

    Figure 15. Oracle minimal level supplemental logging is not enabled

    Enable the Oracle minimal supplemental logging with the following command.

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

    Click on Execute script to run the command and the output Database altered should get displayed, as shown in Figure 16.

    Figure 16. Adding Minimal Supplemental Logging

    Subsequently, run the command to find whether minimal supplemental logging is enabled again. The output is YES, as shown in Figure 17.

    Figure 17. Oracle minimal level supplemental logging is enabled

    If the Oracle minimal level supplemental logging is not enabled an error message gets generated when the configuration file for replication is activated, as shown in Figure 18.

    Figure 18. Oracle “Minimal supplemental logging is not enabled” error message

     

    Running SharePlex Database Utility for Oracle Database

    In this section we shall run the SharePlex Database Utility (ora_setup application) for Oracle Database to configure Oracle database for SharePlex. The ora_setup application is included in the bin directory of the SharePlex installation. The procedure to run the SharePlex Database Utility for Oracle Database is as follows.

    1. Shutdown SharePlex processes
    2. Run ora_setup
    3. Start SharePlex processes

    First, stop the SharePlex processes using the SharePlex Utilities, which is SpUtils in Figure 4. Double-click on the SpUtils icon to start the SharePlex Utilities. Select the SharePlex Services tab. The Current State should be “Service Running”. Click on Stop as shown in Figure 19.

    Figure 19. SharePlex Utilities>SharePlex Services>Stop

    The Current State should become “Service Stopping. Please Wait”, as shown in Figure 20.

    Figure 20. SharePlex Services Stopping

    When SharePlex gets stopped the Current State becomes Service Stopped, as shown in Figure 21.

    Figure 21. SharePlex Service Stopped

    Next, run the ora_setup utility from the Windows 2012 OS command line.

    Microsoft Windows [Version 6.3.9600]
    (c) 2013 Microsoft Corporation. All rights reserved.
    C:UsersAdministrator>ora_setup

    The Oracle SharePlex setup process for port 2100 gets started, as shown in Figure 22. As the setup message indicates, "This process creates tables and user accounts needed to run Oracle SharePlex replication". Subsequently, the user is prompted to add the various parameters required for the setup. The first command prompt is "Will the SharePlex install be using a BEQUEATH connection?" A “BEQUEATH” connection is a connection to a local installation of Oracle Database. As we are indeed using a local Oracle Database, select the default setting of y, for "yes". Click on Enter after adding each configuration parameter. Next command prompt is to provide the Oracle SID. Specify the SID orcldb as shown in Figure 22. The next command prompt is to provide a DBA user for orcldb. Specify DBA user as SYSTEM and click on Enter.

    Figure 22. Running ora_setup

    Specify the password for the SYSTEM user at the next command prompt. The username and password get validated and a connection gets established. A Current SharePlex user name gets displayed. At the command prompt "Would you like to create a new SharePlex user?" select y, the default setting. At the command prompt "Enter username for new user" select the default user splex with password splex as shown in Figure 23. If some other user is specified, the password needs to be specified twice for confirmation. The user gets granted unlimited tablespace privilege.

    Figure 23. Selecting Default user splex

    At the command prompt for Transparent Data Encryption, "Do you want to enable replication of tables with TDE?" select the default setting of "n". Next, provide the tablespaces for use by SharePlex. For the default tablespace select the default setting of USERS. For the temporary tablespace select the default setting TEMP. For the index tablespace, the tablespace must be a PERMANENT tablespace. Specify the SYSTEM tablespace.

    SharePlex objects get created. At the command prompt, "Will the current setup for sid: [orcl] be used as source" select the default value of "y". The database setup gets completed, as shown in Figure 24.

    Figure 24. ora_setup Complete

    The complete output from the ora_setup is listed:

    Microsoft Windows [Version 6.3.9600]

    (c) 2013 Microsoft Corporation. All rights reserved.
     
    C:UsersAdministrator>ora_setup
     
    Welcome to the Oracle SharePlex setup process for port 2100.
    This process creates tables and user accounts needed to run
    Oracle SharePlex replication.
     
    Will the SharePlex install be using a BEQUEATH connection? (Entering 'n' implies
     a SQL*net connection) [ y ] :
    Please note the following:
    ** In response to prompts, a carriage return will choose the default
       given in brackets.  If there is no default, a reply must be entered.
     
    ** To exit the program while the program is waiting for input, use the
       CTRL-C key sequence.
       This sequences can be entered by holding down the CONTROL key and
       pressing the C key.
     
     
    Enter the Oracle SID for which SharePlex should be installed [orcl] : orcldb
     
    In order to create the SharePlex tables and user account, we must
    connect to the database as a DBA user
     
    Enter a DBA user for orcldb  :
     
    ora_setup: Program exit by user request.
     
     
    C:UsersAdministrator>ora_setup
     
    Welcome to the Oracle SharePlex setup process for port 2100.
    This process creates tables and user accounts needed to run
    Oracle SharePlex replication.
     
    Will the SharePlex install be using a BEQUEATH connection? (Entering 'n' implies
     a SQL*net connection)  [ y ] :
    Please note the following:
    ** In response to prompts, a carriage return will choose the default
       given in brackets.  If there is no default, a reply must be entered.
     
    ** To exit the program while the program is waiting for input, use the
       CTRL-C key sequence.
       This sequences can be entered by holding down the CONTROL key and
       pressing the C key.
     
     
    Enter the Oracle SID for which SharePlex should be installed [orcl] : orcldb
     
    In order to create the SharePlex tables and user account, we must
    connect to the database as a DBA user
     
    Enter a DBA user for orcldb  : SYSTEM
     
    Enter password for the DBA account, which will not echo :
     
    connecting--This may take a few seconds.
     
    validating user name and password. . . This may take a few seconds.
    shim ora_home C:appAdministratorvirtualproduct.2.0dbhome_2
    SharePlex objects will need to be created under a special
    account.  You can pick an existing user or create a new one.
    Would you like to create a new SharePlex user ? [ y ] :
    Enter username for new user [splex/splex] :
     
    Warning:  This user is now being granted unlimited tablespace.
    This privilege will remain in effect until it is explicitly changed.
    Granting select on sys.user$ to splex
     
    Do you want to enable replication of tables with TDE? [ n ] :
     
    To enable replication of tables with TDE in the future, please rerun ora_setup.
     
    Setup will now install SharePlex objects.
     
    These are the existing tablespaces.
     
    SYSTEM SYSAUX UNDOTBS1 TEMP USERS
     
    Enter the default tablespace for use by SharePlex [USERS] :
     
    Enter the temporary tablespace for use by SharePlex [TEMP] :

    Enter the index tablespace for use by SharePlex [ ] : SYSTEM
     
    Creating SharePlex objects [Installation type: Fresh]. . .
     
    Creating SharePlex Oracle-timezone-region map . . . Done.
     
    Creating Conflict Resolution Package . . . Done.
     
    Creating SharePlex Dataequator package . . .
    Loading Compare Package from "C:Program FilesQuest SoftwareSharePlexutilsp_
    deq_pkg.plb"...Done.
     
    Loading Compare Varray Package from "C:Program FilesQuest SoftwareSharePlexu
    tilsp_deq_v_pkg.plb"...Done.
     
    Will the current setup for sid: [orcldb] be used as source (including cases as s
    ource for failover or master-master setups)?  [ y ] :
     
    Setup of SharePlex objects successful . . .
     
    Changing SharePlex connection database . . .

    Setup completed successfully
     
     
    C:UsersAdministrator>

     

    Having configured Oracle database for use with SharePlex, start the SharePlex service.  Click on Start as shown in Figure 25.

    Figure 25. Starting SharePlex

     

    The SharePlex service gets started, as shown in Figure 26. Click on Close.

    Figure 26. SharePlex Service Running

     

    Exploring the Shareplex Database Schemas and Tables

    SharePlex creates the required tables and users in the Oracle Database. In this section we shall explore the database schema/s and tables created by SharePlex. Create a new connection for user splex by selecting New Connection as shown in Figure 27.

    Figure 27. New Connection

    In the Toad for Oracle Login click on Add Login Record to add a new login record as shown in Figure 28.

    Figure 28. Add Login Record

    In Add Login Record specify User/Schema as splex and Password as splex. Select the Direct tab and specify Host as localhost, Port as 1521, and SID as orcldb as shown in Figure 29. Click on Test to test the connection.

    Figure 29. Testing a Login Record for splex

    If the connection gets established a Success message gets displayed, as shown in Figure 30.

    Figure 30. Success Message

    Click on OK to complete the login configuration as shown in Figure 31.

    Figure 31. Completing Login Record

    A new Login Record gets added for splex as shown in Figure 32. Click on Connect to connect using the splex.

    Figure 32. Connecting with the Splex User

    A connection for the splex user gets added, as shown in Figure 33.

    Figure 33. Splex User Connection

    Next, we shall explore the database schemas and tables created by SharePlex. Click on Database>Schema Browser as shown in Figure 34.

    Figure 34. Database>Schema Browser

    The Schema Browser displays the database tables in the SPLEX schema as shown in Figure 35.

    Figure 35. Database Tables in Schema SPLEX

    To list the definition of the DEMO_SRC table right-click on the table and select Describe as shown in Figure 36.

    Figure 36. DEMO_SRC>Describe

    The description for the SPLEX.DEMO_SRC table gets displayed, as shown in Figure 37.The Columns tab lists the table columns.

    Figure 37. SPLEX.DEMO_SRC Table Description

    To display the data in the DEMO_SRC table click on the Data tab as shown in Figure 38. The table is empty initially. In a later section we shall add data to the table for replication to the DEMO_DEST table.

    Figure 38. SPLEX.DEMO_SRC Table Data

    The demo table SPLEX.DEMO_DEST is for replicating the data in the SPLEX.DEMO_SRC table. To describe the DEMO_DEST table right-click on the table in the Schema Browser and select Describe as shown in Figure 39.

    Figure 39. SPLEX.DEMO_DEST>Describe

    The DEMO_DEST table description gets displayed, as shown in Figure 40.

    Figure 40. SPLEX.DEMO_DEST Table Description

    Click on the Data tab to display the data in the SPLEX.DEMO_DEST table as shown in Figure 41. Initially the table is empty.

    Figure 41. SPLEX.DEMO_DEST Table Data

    Next, set auto refresh on the DEMO_SRC and DEMO_DEST tables so that when data is added to DEMO_SRC the schema browser automatically refreshes the data displayed by the Data tab. Click in the Auto Refresh checkbox to set auto refresh as shown in Figure 42. In the Auto Refresh Data Grid dialog specify interval for auto-refreshing as 5 and click on OK.

     

    Figure 42. Setting Auto Refresh on DEMO_SRC Table

    Similarly, set auto-refresh on the DEMO_DEST table as shown in Figure 43.

    Figure 43. Setting Auto Refresh on the DEMO_DEST Table

    The Shareplex created Tables may be explored by selecting Describe Objects in the Tool bar as shown in Figure 44.

    Figure 44. Selecting Describe Objects

    We shall be using the demo tables splex.DEMO_SRC and splex.DEMO_DEST to demonstrate replication.  The DEMO_SRC and DEMO_DEST table have the same table structure, each consisting of the following columns (Name and Type).

    Name        Type
    ----------------
    NAME        VARCHAR2(30)
    ADDRESS     VARCHAR2(60)
    PHONE       VARCHAR2(12)

     

    Creating a SharePlex Configuration File

    Having set up Oracle database for SharePlex user splex, next we shall create a SharePlex configuration file and start the replication. A replication is started by activating a configuration file. Configuration files are listed with the list config command as shown in Figure 46. Create a configuration file orcl-orcl with the following command.

    create config orcl-orcl

    The create config command does not generate any output.

    Figure 45. Listing Configs and creating a Config

    The create config command opens a text editor WordPad for Windows to add a configuration file, as shown in Figure 46.

    Figure 46. WordPad for Configuration File

    Add the following configuration file listing to the WordPad.

    Datasource:o.orcl
    splex.DEMO_SRC splex.DEMO_DEST WIN-RGUC9T28GO6@o.orcl

    The WordPad with the configuration file is shown in Figure 47.

    Figure 47. Configuration File in WordPad

    The SharePlex user could be different for different users; splex is shown in WordPad in Figure 47. The first non-commented line of a configuration must be the database specification, which consists of the “Datasource” keyword followed by o.orcldb. The o.orcldb is for the Oracle database SID orcldb.

    Click on File>Save to save the configuration file as shown in Figure 48.

    Figure 48. Saving Configuration File

    Exit WordPad by selecting File>Exit as shown in Figure 49.

    Figure 49. File>Exit

    The create config command returns with no output when the configuration file WordPad is closed, as shown in Figure 50. List the configuration files with the list config command and the orcl-orcl configuration file gets listed. Initially the config file State is Inactive.

    Figure 50. Listing the orcl-orcl Configuration File

    The configuration file may be viewed with the view config command, as shown in Figure 51. Before activating the configuration file the file must be verified with the verify config command.

    verify config orcl-orcl

    If the config is valid the following output should get generated (Figure 51).

    Figure 51. Viewing and Verifying the Config File

    If the configuration file is not valid an error message such as the message shown in Figure 52 gets output.

    Figure 52. If configuration file is not valid an error message gets output

     

    *******************************************************
    * SharePlex for Oracle Command Utility
    * Copyright 2017 Quest Software Inc.
    * ALL RIGHTS RESERVED.
    * Protected by U.S. Patents: 7,461,103 and 7,065,538
    *******************************************************
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> list config
     
    File   Name                                         State       Datasource
    --------------------------------------------------  ----------  ---------------
    ORA_config                                          Inactive    o.SOURCE_SID
    Last Modified At: 06-Jul-17 15:21    Size: 157
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> create config orcl-orcl
    sp_ctrl (WIN-RGUC9T28GO6:2100)> list config
     
    File   Name                                         State       Datasource
    --------------------------------------------------  ----------  ---------------
    orcl-orcl                                           Inactive    o.orcldb
    Last Modified At: 27-Aug-17 21:27    Size: 77
     
    ORA_config                                          Inactive    o.SOURCE_SID
    Last Modified At: 06-Jul-17 15:21    Size: 157
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> view config orcl-orcl
     
    Datasource:o.orcldb
    splex.DEMO_SRC splex.DEMO_DEST WIN-RGUC9T28GO6@o.orcldb
    sp_ctrl (WIN-RGUC9T28GO6:2100)> verify config orcl-orcl
     
      Config orcl-orcl is valid
      View detailed config in C:Program FilesQuest SoftwareSharePlexvardir2100/t
    emp/orcl-orcl
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> activate config orcl-orcl
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> list config
     
    File   Name                                         State       Datasource
    --------------------------------------------------  ----------  ---------------
    ORA_config                                          Inactive    o.SOURCE_SID
    Last Modified At: 06-Jul-17 15:21    Size: 157
     
    orcl-orcl                                           Active      o.orcldb
    Last Modified At: 27-Aug-17 21:28    Size: 77      Internal Name: .conf.25
     
     

    sp_ctrl (WIN-RGUC9T28GO6:2100)>



    *******************************************************
    * SharePlex for Oracle Command Utility
    * Copyright 2017 Quest Software Inc.
    * ALL RIGHTS RESERVED.
    * Protected by U.S. Patents: 7,461,103 and 7,065,538
    *******************************************************
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> list config
     
    File   Name                                         State       Datasource
    --------------------------------------------------  ----------  ---------------
    ORA_config                                          Inactive    o.SOURCE_SID
    Last Modified At: 06-Jul-17 15:21    Size: 157


    sp_ctrl (WIN-RGUC9T28GO6:2100)> create config orcl-orcl
    sp_ctrl (WIN-RGUC9T28GO6:2100)> list config
     
    File   Name                                         State       Datasource
    --------------------------------------------------  ----------  ---------------
    orcl-orcl                                           Inactive    o.orcldb
    Last Modified At: 27-Aug-17 21:27    Size: 77
     
    ORA_config                                          Inactive    o.SOURCE_SID
    Last Modified At: 06-Jul-17 15:21    Size: 157
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> view config orcl-orcl
     
    Datasource:o.orcldb
    splex.DEMO_SRC splex.DEMO_DEST WIN-RGUC9T28GO6@o.orcldb
    sp_ctrl (WIN-RGUC9T28GO6:2100)> verify config orcl-orcl
     
      Config orcl-orcl is valid
      View detailed config in C:Program FilesQuest SoftwareSharePlexvardir2100/t
    emp/orcl-orcl
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> activate config orcl-orcl
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> list config
     
    File   Name                                         State       Datasource
    --------------------------------------------------  ----------  ---------------
    ORA_config                                          Inactive    o.SOURCE_SID
    Last Modified At: 06-Jul-17 15:21    Size: 157
     
    orcl-orcl                                           Active      o.orcldb
    Last Modified At: 27-Aug-17 21:28    Size: 77      Internal Name: .conf.25
     
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> DEACTIVATE CONFIG ORCL-ORCL
       File not found
    sp_ctrl (WIN-RGUC9T28GO6:2100)> deactivate config orcl-orcl
    sp_ctrl (WIN-RGUC9T28GO6:2100)> list config
     
    File   Name                                         State       Datasource
    --------------------------------------------------  ----------  ---------------
    orcl-orcl                                           Inactive    o.orcldb
    Last Modified At: 27-Aug-17 21:27    Size: 77
     
    ORA_config                                          Inactive    o.SOURCE_SID
    Last Modified At: 06-Jul-17 15:21    Size: 157
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)> view config orcl-orcl
     
    Datasource:o.orcldb
    splex.DEMO_SRC splex.DEMO_DEST WIN-RGUC9T28GO6@o.orcldb
    sp_ctrl (WIN-RGUC9T28GO6:2100)> activate config orcl-orcl
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)>
    sp_ctrl (WIN-RGUC9T28GO6:2100)> list config
     
    File   Name                                         State       Datasource
    --------------------------------------------------  ----------  ---------------
    ORA_config                                          Inactive    o.SOURCE_SID
    Last Modified At: 06-Jul-17 15:21    Size: 157
     
    orcl-orcl                                           Active      o.orcldb
    Last Modified At: 27-Aug-17 21:43    Size: 77      Internal Name: .conf.26
     
     
    sp_ctrl (WIN-RGUC9T28GO6:2100)>

     

    Activating the Configuration File

    To start the replication the configuration file must be activated. The activate config command is used to activate the configuration file, and the command syntax is shown in Figure 53.

    Figure 53. Command Syntax for activate config

    The command does not generate any output if the activation has started successfully.

    sp_ctrl (WIN-RGUC9T28GO6:2100)> activate config orcl-orcl
    sp_ctrl (WIN-RGUC9T28GO6:2100)>

    Subsequent to activating the configuration file, list the configuration files with list config and an active configuration file orcl-orcl gets listed, as shown in Figure 54.

    Figure 54. Active Configuration orcl-orcl

    After a config file has been activated data added to the DEMO_SRC table gets replicated to the DEMO_DEST table. Next, we shall add data to the DEMO_SRC table to demonstrate replication.

     

    Adding Data to Oracle Database DEMO_SRC Table

    By default the DEMO_SRC table does not include any data. Connect to Oracle Database with user SYS as SYSDBA as shown in Figure 55.

    Figure 55. Connecting with User SYS as SYSDBA

    A connection gets created and a SQL Editor worksheet gets opened, as shown in Figure 56.

    Figure 56. SQL Editor Worksheet for Connection with User SYS

    Add some data to the DEMO_SRC table with INSERT statements.

    INSERT INTO splex.DEMO_SRC(NAME,ADDRESS,PHONE) VALUES('Deepak Vohra','dvohra10@yahoo.com','555-555-555');
    INSERT INTO splex.DEMO_SRC(NAME,ADDRESS,PHONE) VALUES('D Vohra','dvohra17@yahoo.com','555-555-555');

    Copy the QL statements to the SQL Editor and click on Execute as script as shown in Figure 57.

    Figure 57. Running SQL Script to add Data to DEMO_SRC Table

    Two rows of data get added, as shown by the output in Figure 58.

    Figure 58. Data Added to DEMO_SRC Table

    By default, transactions in Toad for Oracle are set to AutoCommit Off and the DEMO_SRC table still lists Data as empty, as shown in Figure 59.

    Figure 59. DEMO_SRC Table Empty even after running SQL Script

    To commit the transactions click on the Commit button as shown in Figure 60.

    Figure 60. Committing Transactions

    Subsequently, the DEMO_SRC table lists data, as shown in Figure 61.

    Figure 61. DEMO_SRC Table lists Data

     

    Listing Replicated Data in DEMO_DEST Table

    With the SharePlex replication configured in the config file orcl-orcl, which has been activated, the data added to DEMO_SRC table gets replicated to DEMO_DEST table.  The Data tab in the DEMO_DEST table lists the data replicated from DEMO_SRC, as shown in Figure 62.

    Figure 62. DEMO_DEST Data Replicated from DEMO_SRC

     

    Deactivating the Replication

    To deactivate the replication, deactivate the config orcl-orcl with the deactivate config command.  Subsequently list the configs; the orcl-orcl config is listed as Inactive.

    sp_ctrl (WIN-RGUC9T28GO6:2100)> deactivate config orcl-orcl
    sp_ctrl (WIN-RGUC9T28GO6:2100)> list config
     
    File   Name                                         State       Datasource
    --------------------------------------------------  ----------  ---------------
    orcl-orcl                                           Inactive    o.orcldb
    Last Modified At: 27-Aug-17 21:27    Size: 77
     
    ORA_config                                          Inactive    o.SOURCE_SID
    Last Modified At: 06-Jul-17 15:21    Size: 157

     

    Conclusion

    In this tutorial we discussed using Toad for Oracle with SharePlex for Oracle Database 12c R2 replication on Windows 2012. Toad for Oracle database management simplifies the task of exploring schemas and tables used by SharePlex.  For the complete SharePlex 9.0 documentation including command syntax refer to https://support.quest.com .

    Tags: Toad for Oracle

    Deepak Vohra

    Written by Deepak Vohra

    Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.