21.1.7.2.1. Replication Admin User Creation

The replication framework creates different control schema objects such as shadow tables, triggers, sequences, and packages in the publication source database that are used to record the replicated table transactions and maintain the replication life cycle. The replication user needs certain privileges to create the control schema objects during replication configuration as described below.

It is recommended to create a separate replication admin user (e.g., rrepadmin) in Oracle to host the control schema objects so that the published tables source schemas are not over-populated with redundant information. The replication admin user should be granted the required privileges as listed below:

• Create a user (e.g., "rrepadmin") in the source Oracle database server.

• Grant the following privilege to rrepadmin user:

  GRANT CREATE ANY TRIGGER TO rrepadmin

• For each of the tables that need to be published, grant SELECT privilege. For example to publish all the "scott" schema tables, issue the following commands:

  	 GRANT SELECT ON scott.dept TO rrepadmin 
           GRANT SELECT ON scott.emp TO rrepadmin 
           GRANT SELECT ON scott.bonus TO rrepadmin
  	 

21.1.7.2.2. Start Replication Console

Select the "Replication Console (for Oracle)" menu option from the EnterpriseDB program menu to launch the replication console desktop application.

21.1.7.2.3. Managing Publication/Subscription Servers

Right click on the Replication Servers tree node and select the Register Management Server menu option. In the Register Management Server dialog box, provide the IP address and port of the server on which the DBA Management Server is running.

Since the replication cluster may be configured in a distributed Intranet or Internet environment, authentication is enforced to eliminate any security issues. As part of the registration process, the user is required to provide super user login credentials for the DBA Management Server. Once the required information is entered click Register to register the DBA Management Server.

The DBA Management Server hosts one publication and one subscription service. The following sections describe how to configure and run publication and subscription services:

21.1.7.2.4. Start Publication Service

The publication service is in "stop" mode by default. Right click on the Publication Service node and select Start Service to start the service.

Choose the Add Database context menu option by right clicking on the Publication Service node that displays the Add Database dialog box. Specify publication database connectivity information, as per your system environment.

Host=10.90.1.121 Port=1521 User=rrepadmin Password=rrepadmin SID=XE

After providing the required information for adding the database, test the connection to the database and then save it.

21.1.7.2.5. Publication Service Configuration

After the database has been added, it can then be verified by using the Publication Databases tab as shown in the following screenshot:

Next, proceed to create a publication. EnterpriseDB allows the user to create two different types of publications:

• Transactional Publication

• Snapshot-Only Publication

21.1.7.2.6. Transactional Publication

To create a transactional publication, choose the Create Publication context menu option by right clicking on the Publication Service node that displays the Create Publication dialog box. Specify the database tables that are to be published. This is shown in the following screenshots:

The EnterpriseDB replication console also provides the user with the ability to provide a Filter Clause for a specific table that is to be published while creating a publication as shown in the following screen shot:

21.1.7.2.7. Snapshot-Only Publication

To create a snapshot-only publication, choose the Create Publication context menu option by right clicking on the Publication Service node that displays the Create Publication dialog box. Specify the database tables that are to be published. To create a snapshot-only publication, check the Snapshot-only replication check box. This is shown in the following screenshots:

The user can also publish Oracle "VIEWS". This option is available for a "snapshot-only" publication.

21.1.7.2.8. Start Subscription Service

• The subscription service is stopped by default. Right click on the Subscription Service node and select Start Service to start the service.

• Choose the Add Database context menu option by right clicking on the Subscription Service node that displays the Add Database dialog box. Specify the subscription database connectivity information including the IP address of the system running the subscription service, as per your system environment.

  Host=10.90.1.17 Port=5444 User=enterprisedb Password=edb Database=edb

21.1.7.2.9. Subscription Service Configuration

After the database has been added, it can then be verified by using the Subscription Databases tab as shown in the following screenshot:

After adding a database, create a subscription.

21.1.7.2.10. Creating a Subscription

To create a subscription, choose the Create Subscription context menu option by right clicking on the Subscription Service node that displays the Create Subscription dialog box. Specify the publication to which the user is subscribing. This is shown in the following screen shots:

After taking a snapshot in a snapshot-only transaction, the replication history for a specific table can be viewed:

For a transactional publication, after the initial snapshot has been taken, the synchronize operation is performed by right clicking on the subscription and choosing the "synchronize" option as shown in the following screenshot:

21.1.7.2.11. Replication History

The replication framework maintains replication history for all publications. The replication history is a record of when and how many times a replication (snapshot and/or synchronize) was performed over a certain period of time for a given publication. The summary history is maintained and accessible at the publication node level with a detailed table level history available against each table node as described below:

Viewing Publication Replication History

EnterpriseDB provides the user with the facility of viewing the "publication-level replication history". Publication-level replication history is a sum of all the replications that are performed for each table in that specific publication and tells about the replication performed with respect to a publication. These replications could either be transactional/synchronize or snapshot only:

Viewing Table Replication History

EnterpriseDB also provides the user with the facility of viewing the table-level replication history. Table-level replication history is separate for each table and tells about the replication performed with respect to an individual table:

21.1.7.2.12. Scheduling a Replication

After creating a subscription, the user can also schedule a replication so that the replication operation is performed repetitively after specific intervals. The schedule is configured by right clicking on the subscription and choosing the "Configure Schedule" option from the menu as shown below:

In the Scheduled Task Wizard, various parameters are set for the replication to be scheduled:

21.1.7.2.13. Shadow Table Cleanup Job

The transactions processed against the subscription tables are kept in the shadow tables to maintain synchronization replication history. Since the shadow tables may grow to contain large volumes of data, a cleanup job (implemented as an Oracle database job using the DBMS_JOB package) is scheduled to run every Sunday at 12:00 AM (midnight) by default. The user can disable this job or customize its default scheduling period via the Replication Console.

Shadow table cleanup is performed using either the DELETE command or the TRUNCATE command. The cleanup job performs a TRUNCATE on the shadow table if there are no pending transactions in the shadow table; otherwise, the cleanup job uses a DELETE operation.

Shadow table cleanup can be explicitly performed by the user from the EnterpriseDB Replication Console. The Cleanup Shadow Table History option deletes processed transactions from the shadow table(s). This functionality is shown in the following screenshots:

There is a subtle difference between replication history and shadow table history. Replication history is the record of when and how many times a replication (snapshot and/or synchronization) was performed over a certain period of time. The shadow table history is a record of the actual transactions processed against a subscription table when a synchronization occurs. These transaction records are viewable by clicking on the View Data link of a table's Replication History tab.

If the user opts to cleanup shadow table history, the replication history will still be available. However when the user selects the View Data option, the replicated rows will not be available for view as these are deleted from the underlying shadow tables.

21.1.7.3.1. KNOWN ISSUES

    -Djava.rmi.server.hostname=192.168.0.1 
    

	192.168.0.1 demo-server
        
	127.0.0.01 localhost.localdomain