We're here to help!

Second Replication from Same Source to Different Target DB

Follow
Problem Description

We have  a current replication  from SOURCEDB to TARGET1 DB. Now we want to setup a second replication going from SOURCEDB to TARGET2 database. How do we do this without messing up the first replication?

Solution

This can be done quite easily. The basic steps are:

  1. Create a new directory for this replication configuration.
  2. Make sure you DO NOT accept the default user, dbvrep, if you are using it for your original replication. The user MUST be different, for example: dbvrepT2. This way the metadata is kept separate from the other replication configuration.
  3. You will need to run replication on different ports than the first replication which defaults to 7901 & 7902. In this example 7903 & 7904 is used. You can choose whatever ports you want but ensure firewalls are opened for those ports in advance.
Steps Performed

Now for the actual steps shown in an example:

  1. First create another directory. If the first replication is running out of /u03/oracle/replicate/TARGET1, create a new directory for the second replication on both the source server & target: /u03/oracle/replicate/TARGET2

  2. You may need to open a second set of firewall ports for the second replication. As the original running replication is using 7901 & 7902, for this exercise we will use 7903 for MINE & 7904 for APPLY. Any port is fine as long as they are not used in the first replication.

  3. Run setup wizard from the new directory you created:
    $ cd /u03/oracle/replicate/TARGET2
    $ dbvrep

    When the setup wizard asks for the name of the replication it must be different from your running replication. This example uses TARGET2.

Before starting the actual configuration, some basic information is needed. The DDC name and script path determines where all files created by the wizard go (and where to reread them from if the wizard is rerun). The license key determines which options are available for this configuration.

(DDC_NAME) - Please enter a name for this replication (suggestion: use the name of the source database): TARGET2

When prompted for the location of the configuration scripts, use the new source name (i.e. TARGET2) so as not to overwrite the running replication directory:

Directory for location of configuration scripts on this machine: [/home/oracle/TARGET1] /u03/oracle/replicate/TARGET2

Note: The first step asks for a Dbvisit Replicate owner. This MUST be different from your running replication (i.e. TARGET1) which is likely using the default dbvrep owner. For this example the user dbvrepDZ is used for the second replication owner.

Step 1: Describe Databases

Enter the Dbvisit Replicate owner (this user will be created by this script): [dbvrep] dbvrepDZ
Please enter password for this user: [dbvpasswd]

Step 2 & Step 3 are same as usual...

Step 4: Process Configuration

Step 4 configures the replication processes for each replication. Note: although the hostname defaults are filled in, the default ports are shown. These need to be changed. The format is: hostname:port#. See the following responses to add the port numbers:

Following processes are defined:
1: MINE on SOURCEDB
Host: testhost210.dbvisit.com, SMTP: no, SNMP: no

2: APPLY on TARGET2
Host: testhost211.dbvisit.com, SMTP: no, SNMP: no

Enter number of process to modify it, or "done": [done] 1

Fully qualified name of the server for the process (usually co-located with the database, unless mine is offloaded using fetcher): [testhost210.dbvisit.com] testhost210.dbvisit.com:7903

Server type (Windows/Linux/Unix): [Linux]

Enable email notifications about problems (yes/no)? [no]

Enable SNMP traps/notifications about problems (yes/no)? [no]

Directory with DDC file and default where to create log files etc. (recommended: same as global setting, if possible)? [/u03/oracle/replicate/TARGET2]
Following settings were pre-filled with defaults or your reloaded settings:
[MINE_REMOTE_INTERFACE]: Network remote interface: testhost210.dbvisit.com:7903

[MINE_DATABASE]: Database TNS: SOURCEDB

[TNS_ADMIN]: tnsnames.ora path: /u01/app/oracle/product/11.2.0/db_1/network/admin

[MINE_PLOG]: Filemask for generated plogs: /u03/oracle/replicate/TARGET2/mine/%S.%E (%S is sequence, %T thread, %F original
filename (stripped extension), %P process type, %N process name, %E default extension)

[LOG_FILE]: General log file: /u03/oracle/replicate/TARGET2/log/dbvrep_%N_%D.%E

[LOG_FILE_TRACE]: Error traces: /u03/oracle/replicate/TARGET2/log/trace/dbvrep_%N_%D_%I_%U.%E
Checking that these settings are valid...

Do you want change any of the settings? [no]
Following processes are defined:

1: MINE on SOURCEDB

Host: testhost210.dbvisit.com:7903, SMTP: no, SNMP: no

2: APPLY on TARGET2

Host: testhost211.dbvisit.com, SMTP: no, SNMP: no

Enter number of process to modify it, or "done": [done] 2

Fully qualified name of the server for the process (usually co-located with the database,
unless mine is offloaded using fetcher): [testhost211.dbvisit.com] testhost211.dbvisit.com:7904

Server type (Windows/Linux/Unix): [Linux]

Enable email notifications about problems (yes/no)? [no]

Enable SNMP traps/notifications about problems (yes/no)? [no]

Directory with DDC file and default where to create log files etc. (recommended: same as global setting, if possible)? [/u03/oracle/replicate/TARGET2]
Following settings were pre-filled with defaults or your reloaded settings:
[APPLY_REMOTE_INTERFACE]: Network remote interface: testhost211.dbvisit.com:7904

[APPLY_DATABASE]: Database TNS: TARGET2

[TNS_ADMIN]: tnsnames.ora path: /u01/app/oracle/product/11.2.0/db_1/network/admin

[APPLY_STAGING_DIR]: Directory for received plogs: /u03/oracle/replicate/TARGET2/apply

[LOG_FILE]: General log file: /u03/oracle/replicate/TARGET2/log/dbvrep_%N_%D.%E

[LOG_FILE_TRACE]: Error traces: /u03/oracle/replicate/TARGET2/log/trace/dbvrep_%N_%D_%I_%U.%E
Checking that these settings are valid...

Do you want change any of the settings? [no]
Following processes are defined:

1: MINE on SOURCEDB

Host: testhost210.dbvisit.com:7903, SMTP: no, SNMP: no

2: APPLY on TARGET2

Host: testhost211.dbvisit.com:7904, SMTP: no, SNMP: no
Enter number of process to modify it, or "done": [done]
Optionally, the script can be invoked now by this wizard.

Run this script now? (yes/no) [NO]

dbvrep> exit

Complete all the instructions contained in Nextsteps.txt that follow. After running the -all.sh script, the NextSteps.txt specifies the firewall ports that need to be open. This procedure is demonstrated in step 5.

Step 5

Ensure the firewall is listening on the interfaces used by the processes: 0.0.0.0:7904 and 0.0.0.0:7903.

In the MINE.ddc
memory_set MINE.MINE_LISTEN_INTERFACE testhost210.dbvisit.co.nz:7903

In the APPLY.ddc
memory_set APPLY.APPLY_LISTEN_INTERFACE testhost211.dbvisit.co.nz:7904

Note: The script built to start the MINE & APPLY processes will now include the port#. For example:

TARGET2-run-testhost210.dbvisit.com_7903.sh -- source server to run MINE
TARGET2-run-testhost211.dbvisit.com_7904.sh -- target2 server to run APPLY

You can also do one last check before running your APPLY.sh to instantiate the target. That is to verify the config settings reflect the ports for this instantiation:

$ cd config

Verify MINE will listen on the alternate port (i.e. 7903):

$ grep 7903 *
TARGET2-onetime.ddc:SET MINE.MINE_LISTEN_INTERFACE 0.0.0.0:7903
TARGET2-onetime.ddc:SET MINE.MINE_REMOTE_INTERFACE testhost210.dbvisit.com:7903
TARGET2-wizard-ddc.cfg:MINE_LISTEN_INTERFACE: 0.0.0.0:7903
TARGET2-wizard-ddc.cfg:MINE_REMOTE_INTERFACE: testhost210.dbvisit.com:7903

Verify APPLY will listen on the alternate port (i.e. 7904:

$ grep 7904 *
TARGET2-onetime.ddc:SET APPLY.APPLY_LISTEN_INTERFACE 0.0.0.0:7904
TARGET2-onetime.ddc:SET APPLY.APPLY_REMOTE_INTERFACE testhost211.dbvisit.com:7904
TARGET2-wizard-ddc.cfg:APPLY_LISTEN_INTERFACE: 0.0.0.0:7904
TARGET2-wizard-ddc.cfg:APPLY_REMOTE_INTERFACE: testhost211.dbvisit.com:7904
TARGET2-wizard-ddc.cfg:hostname: testhost211.dbvisit.com:7904

Verify no references to port 7901 or 7902 exist. The following commands should return nothing:

$ grep 7901
$ grep 7902

cd back to your working directory (/u03/oracle/replicate/TARGET2):

$ cd ../

Complete your steps including (APPLY.sh / APPLY.bat) as indicated in Nextsteps.txt to instantiate your target. You can startup MINE on the source server with the provided script while getting the target instantiated with data. For example:

TARGET2-run-testhost210.dbvisit.com_7903.sh

Once instantiation is complete, start APPLY on the target host. For example:

TARGET2-run-testhost211.dbvisit.com_7904.sh

Hope this helps.

Donna Zehl March 14, 2015 04:04

Have more questions? Submit a request

Comments

  • Avatar
    Charmaigne Bezuidenhout

    Awesome article Donna - I used it and it worked 100%.