Configure Replication Server for replication from the primary database

This section describes how to configure for replication from the primary database:

• Create a Replication Server connection to the primary database.

• Create a database replication definition.

• Create the database replication subscription.

• Set up sequence replication support.

• Grant create object permission to Replication Agent user.

• Create a Replication Server user for Mirror Replication Agent to use.

To create a Replication Server connection to the primary database

1. Verify that you are at the $SYBASE/MA-15_0/scripts/oracle directory.

2. Make a copy of the oracle_create_rs_primary_connection.sql:

   cp oracle_create_rs_primary_connection.sql myma_oracle_create_rs_primary_connection.sql
   

3. Before executing the myma_oracle_create_rs_primary_connection.sql script against your Replication Server, change all occurrences of value {pds}.{pdb} to the name of the Replication Server connection used to connect to Oracle, where:

   • pds is rs_source_ds (located in $SYBASE/MA-15_0/init/oracle.rs).

   • pdb is rs_source_db (located in $SYBASE/MA-15_0/init /oracle.rs).

   For example, NY.NYora92.

4. Change sys and sys_pwd to the user ID and password of the Oracle user who will have permission to apply DML operations against all user tables that will be replicated, where:

   • sys is the user ID of the Oracle user (sys).

   • sys_pwd is the password of the Oracle user (change_on_install).

   While not immediately used, this user must be a valid user in the Oracle database.

   This command creates a Replication Server connection to the primary database example in step 3:

   create connection to NY.NYora92set error class rs_sqlserver_error_classset function string class rs_oracle_function_classset username sysset password change_on_installwith log transfer on, dsi_suspendedgo
   

5. Execute the script in Replication Server:

   isql –Usa –P –SSAMPLE_RS -i myma_oracle_create_rs_primary_connection.sql
   

   A message appears indicating that the Replication Server connection to the primary database is created.

To create the database replication definition

1. Go to the $SYBASE/MA-15_0/scripts/sybase directory:

   cd $SYBASE/MA-15_0/scripts/sybase
   

2. Make a copy of the create_rs_db_repdef.sql file:

   cp create_rs_db_repdef.sql myma_create_rs_db_repdef.sq
   

3. Before executing the myma_create_rs_db_repdef.sql script, change the value of “{pds}.{pdb}” to the name of the connection string you defined for the primary database:

   • pds – rs_source_ds (located in $SYBASE/MA-15_0/init/oracle.rs)

   • pdb – rs_source_db (located in $SYBASE/MA-15_0/init/oracle.rs)

   For example,

   NY.NYora92
   

   Here is an example of database replication definition:

   create database replication definition NY_repdef1
   with primary at NY.NYora92
   replicate DDL
   go
   

4. Connect to the Replication Server and execute this command:

   isql -Usa -P –SSAMPLE_RS -i myma_oracle_create_rs_db_repdef.sql
   

   A message appears indicating that the database replication definition is created.

To create the database replication subscription

1. Make a copy of the create_rs_db_sub.sql file:

   cp create_rs_db_sub.sql myma_create_rs_db_sub.sql
   

2. Before executing the myma_create_rs_db_sub.sql script, change the “{pds}.{pdb}” and “{rds}.{rdb}'”to the appropriate connection name:

   • pds is rs_source_ds (located in $SYBASE/MA-15_0/init/oracle.rs).

   • pdb is rs_source_db (located in $SYBASE/MA-15_0/init/oracle.rs).

   • rds is the DCO server name (DCOServer).

   • rdb is any valid identifier. (Sybase recommends that you use the Oracle SID name.)

   Here is an example of create database replication subscription:

   create subscription NY_sub1
   for database replication definition NY_repdef1
   with primary at NY.NYora92
   with replicate at DCOServer.oratest2
   without materialization
   go
   

3. Connect to Replication Server and execute this command:

   isql -Usa -P –SSAMPLE_RS -i myma_oracle_create_rs_db_sub.sql
   

   A message appears indicating that the subscription is being created.

To set up sequence replication support

1. Go to the $SYBASE/MA-15_0/scripts/oracle directory:

   cd $SYBASE/MA-15_0/scripts/oracle
   

2. Install the rs_update_sequence stored procedure. Use SQLPLUS to run the oracle_create_replicate_sequence_proc.sql stored procedure at the standby site:

   sqlplus maintuser/password @oracle_create_replicate_sequence_proc.sql 
   

   This is returned:

   SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 20 21:30:25 2007Copyright (c) 1982, 2005, Oracle. All rights reservedConnected to:
   Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining optionsProcedure created.Grant succeeded.
   

   Exit sqlplus.

   For more information about sequence replication, see the Mirror Replication Agent Primary Database Guide.

3. To create a function replication definition for rs_update _sequence, copy the oracle_create_rs_sequence_repdef.sql script:

   cp oracle_create_rs_sequence_repdef.sql myma_oracle_create_rs_sequence_repdef.sql
   

4. Before executing the myma_oracle_create_rs_sequence_repdef.sql script, edit all occurrences of the value {pds}.{pdb} to the name of the Mirror Replication Agent connection used by your Mirror Replication Agent, where:

   • pds is rs_source_ds (located in $SYBASE/MA-15_0/init/oracle.rs).

   • pdb is rs_source_db (located in $SYBASE/MA-15_0/init/oracle.rs).

   Here is the function replication definition for sequence replication after editing:

   create function replication definition REP_UPDATE_SEQUENCE
   with primary at NY.NYora92
   deliver as RS_UPDATE_SEQUENCE
   (
   @"SEQUENCENAME"  varchar(32768),
   @"SEQUENCEVALUE" numeric,
   @"INCREMENT"     numeric
   )
   send standby all parameters
   go
   

5. Connect to the Replication Server and execute this command:

   isql -Usa -P –SSAMPLE_RS -i myma_oracle_create_rs_sequence_repdef.sql
   

   A message appears indicating that a functional sequence replication definition is created.

6. Use isql to log in to the Replication Server:

   isql -Usa -P -SSAMPLE_RS
   

7. Verify that the database replication definition database subscription exists:

   check subscription NY_sub1
   for database replication definition NY_repdef1
   with primary at NY.NYora92
   with replicate at DCOServer.oratest2
   go
   

   A message appears indicating that a subscription is valid for the primary and replicate databases.

8. Replication Server comes with users who have the permissions necessary for Replication Agents. However, because Mirror Activator has the capability to create replication definitions, the Replication Agent user must also have CREATE OBJECT permission:

   This user will be entered in the rs_username parameter when building the Mirror Replication Agent for Oracle instance. If you want a user other than the default replication agent user, make sure that the user is granted CONNECT SOURCE and CREATE OBJECT permission.

   grant create object to SAMPLE_RS_ra
   

   go
   

   Permission is granted to user “SAMPLE_RS_ra.”

---

Copyright © 2007. Sybase Inc. All rights reserved.

View this document as PDF