We're here to help!

Add new apply process (new target database) to an existing replication.

Follow
Problem Description

The article explains the step by step procedure to add a new APPLY process to a existing replication setup without having to disturb the Repository , or the MINE process. 


Steps Perfomed

 1) Setup normal one way replication MINE ---> APPLY

| Dbvisit Replicate 2.8.02(MAX edition) - Evaluation License expires in 30 days
MINE is running. Currently at plog 3840 and SCN 204288611 (12/09/2016 12:02:33).
APPLY is running. Currently at plog 3840 and SCN 204288596 (12/09/2016 12:02:29).
Progress of replication ZD8622:MINE->APPLY: total/this execution
-------------------------------------------------------------------------
TEST.DEPT:   100%  Mine:1/1   Unrecov:0/0   Applied:1/1  Conflicts:0/0   Last:09/12/2016 12:02:31/OK
TEST.EMP:    100%  Mine:1/1   Unrecov:0/0   Applied:1/1  Conflicts:0/0  Last:09/12/2016 12:02:31/OK
-------------------------------------------------------------------------
2 tables listed.

2) Shutdown all processes.

3) Backup the following files on MINE before going to step4:

  • all.sh
  • onetime.ddc
  • setup.dbvrep

4) Run setup wizard again to configure the second pair MINE->APPLY1 (i.e DSRC->TRG11G).

1: Oracle dsrc, SYS/***, SYSTEM/***, dbvrep/***, USERS/TEMP, dbvrep/, ASM:No, TZ: +13:00
2: Oracle dtrg, SYS/***, SYSTEM/***, dbvrep/***, OEDATA/TEMP, dbvrep/, ASM:No, TZ: +00:00
3: Oracle trg11g, SYS/***, SYSTEM/***, dbvrep/***, USERS/TEMP, dbvrep/, ASM:No, TZ: +00:00

 Use same dbvrep repository.

Following processes are defined:

1: MINE on dsrc
Host: dbvlin822.dbvisit.co.nz, SMTP: No, SNMP: No
2: APPLY on dtrg
Host: dbvlin823.dbvisit.co.nz, SMTP: No, SNMP: No
3: MINE1 on dsrc
Not used, work done by MINE.
4: APPLY1 on trg11g
Host: dbvlin821.dbvisit.co.nz, SMTP: No, SNMP: No

Finish setup wizard.

This will generate new all.sh, onetime.ddc, setup.dbvrep. Do not run the newly generated all.sh yet!

5) Edit the new all.sh -> it should contain only configuration of 3rd db (i.e. TRG11G).

oracle@dbvlin822[/home/oracle/ZD8622]: cat ZD8622-all.sh
#!/usr/bin/env sh
# This auxiliary function is used to ensure that if the setup code fails then># the error is not masked by `tee' and this script returns a non-zero exit code.
_pipefail_run() {
        j=1
        while eval "\${pipestatus_$j+:} false"; do
                unset pipestatus_$j
                j=`expr $j + 1`
        done
 
        j=1
        com=
        k=1
        l=
        for a in "$@"; do
                if [ "x$a" = 'x|' ]; then
                        com="$com { $l "'3>&-
                                        echo "pipestatus_'$j'=$?" >&3
                                  } 4>&- |'
                        j=`expr $j + 1`
                        l=
                else
                        l="$l \"\$$k\""
                fi
                k=`expr $k + 1`
        done
        com="$com $l"' 3>&- >&4 4>&-
             echo "pipestatus_'$j'=$?"'
 
        exec 4>&1
        eval "`exec 3>&1; eval "$com"`"
        exec 4>&-
 
        j=1
        while eval "\${pipestatus_$j+:} false"; do
                eval "[ \$pipestatus_$j -eq 0 ]" || return 1
                j=`expr $j + 1`
        done
 
        return 0
}
 
_setup_script() {
echo Setting up Dbvisit Replicate configuration
TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin
export TNS_ADMIN
echo Configure database TRG11G...
echo 'change_on_install' | sqlplus -S 'SYS@TRG11G as sysdba' @/home/oracle/ZD8622/config/ZD8622-dbsetup_TRG11G_dbvrepZD8622.sql
if [ $? -ne 0 ]; then echo "Error encountered, not starting Dbvisit Replicate."; return 1; fi
echo Object grants for database TRG11G...
echo 'manager' | sqlplus -S SYSTEM@TRG11G @/home/oracle/ZD8622/config/ZD8622-grants_TRG11G_dbvrepZD8622.sql
if [ $? -ne 0 ]; then echo "Error encountered, not starting Dbvisit Replicate."; return 1; fi
 echo Setting up the configuration
/usr/bin/dbvrep --ddcfile /home/oracle/ZD8622/config/ZD8622-onetime.ddc @/home/oracle/ZD8622/config/ZD8622-setup.dbvrep
if [ $? -ne 0 ]; then echo "Error encountered, not starting Dbvisit Replicate."; return 1; fi
cat /home/oracle/ZD8622/Nextsteps.txt
echo The above list is stored in /home/oracle/ZD8622/Nextsteps.txt.
}
_pipefail_run eval "_setup_script 2>&1" \| tee /home/oracle/ZD8622/ZD8622-all.log

6) Edit newly generated onetime.ddc - below is actual content.

oracle@dbvlin822[/home/oracle/ZD8622/config]: cat ZD8622-onetime.ddc
####################################################
# This file is used only during the setup, then it's contents is loaded as DDC DB into mine database
####################################################
@/home/oracle/ZD8622/ZD8622-MINE.ddc
#please update SETUP_SCRIPT_PATH if you move the setup scripts somewhere else - this will help SUPPORT command to find them when creating Support packages
SET APPLY1.APPLY_DATABASE TRG11G
SET APPLY1.APPLY_DATABASE_DBID 1655055027
SET APPLY1.APPLY_FILEOUT_FILE_FORMAT OFF
SET APPLY1.APPLY_LISTEN_INTERFACE 0.0.0.0:7904
SET APPLY1.APPLY_LOOP_PREVENTION YES
SET APPLY1.APPLY_PASSWORD 53616c7465645f5f73243d15ada3ff5024f95d11ffb520afa62ea4917e0652ef
SET APPLY1.APPLY_RDBMS Oracle
SET APPLY1.APPLY_REMOTE_INTERFACE dbvlin821.dbvisit.co.nz:7904
SET APPLY1.APPLY_STAGING_DIR /home/oracle/ZD8622/apply1
SET APPLY1.APPLY_USER dbvrepZD8622
SET APPLY1.DDC_BACKUP_DIR /home/oracle/ZD8622/ddc_backup
SET APPLY1.LOG_FILE /home/oracle/8622v2/log/dbvrep_%N_%D.%E
SET APPLY1.LOG_FILE_TRACE
/home/oracle/ZD8622/log/trace/dbvrep_%N_%D_%I_%U.%E
SET APPLY1.MINE_LOOP_PREVENTION NO
SET APPLY1.MINE_PEER MINE
SET APPLY1.NCHARSET AL16UTF16
SET APPLY1.NLS_LANG AMERICAN_AMERICA.AL32UTF8
SET APPLY1.PREPARE_CHECK_APPLY No
SET APPLY1.PREPARE_CHECK_MINE Yes
SET APPLY1.PROCESS_TYPE APPLY
SET APPLY1.SETUP_SCRIPT_PATH /home/oracle/ZD8622
SET APPLY1.SOURCE_TIMEZONE +13:00
set APPLY1.NETWORK_QUALITY MANUAL
set APPLY1._NETWORK_TRAFFIC_KEY_ALLOWED_PLOG 01
set APPLY1._NETWORK_TRAFFIC_KEY_USE_PLOG 0
set APPLY1._NETWORK_COMPRESSION NONE
set APPLY1._NETWORK_CHUNKSIZE 10000000
set APPLY1._TCP_CONNECT_TIMEOUT 60
set APPLY1._TCP_SEND_TIMEOUT 60
set APPLY1._TCP_RECEIVE_TIMEOUT 60

 7) Edit setup.dbvrep - below is actual file.

oracle@dbvlin822[/home/oracle/ZD8622/config]: cat ZD8622-setup.dbvrep
#clear the no-DDC-DB-available warning
process clear previous warnings
set ON_WARNING SKIP
set ON_ERROR EXIT
 # Configuring non-default processes
choose process MINE
choose process APPLY1
PROCESS SWITCH_REDOLOG
PROCESS SETUP APPLY DROP DICTIONARY
PROCESS SETUP APPLY CREATE DICTIONARY
PROCESS SETUP APPLY LOAD DICTIONARY
PROCESS PREPARE_DP SETUP CLEAR
PROCESS SETUP PAIR MINE AND APPLY
SET APPLY1.INSTANTIATE_SCN NOW
EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_UNCMP #Ignore tables created by Compression Advisor
EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_CMP #Ignore tables created by Compression Advisor
EXCLUDE CREATE TABLE %.SCHEDULER$_% #Ignore tables created by Oracle scheduler (also used by schema/full expdp/impdp)
EXCLUDE CREATE TABLE %.CMP1$% #Ignore tables created by Compression Advisor since 11.2.0.4
EXCLUDE CREATE TABLE %.CMP2$% #Ignore tables created by Compression Advisor since 11.2.0.4
EXCLUDE CREATE TABLE %.CMP3$% #Ignore tables created by Compression Advisor since 11.2.0.4
EXCLUDE CREATE TABLE %.CMP4$% #Ignore tables created by Compression Advisor since 11.2.0.4
SET PREPARE_SCHEMA_EXCEPTIONS none
PROCESS SUPPLEMENTAL LOGGING SCHEMA "TEST" ENABLE PRIMARY KEY
PROCESS SWITCH_REDOLOG
PROCESS WAIT_SCN_FLIP
#single-scn instantiation: lock all tables and schemas
PROCESS LOCK SCHEMAS "TEST"
#single-scn instantiation: unlock all tables and schemas, but keep the SCN
PROCESS LOCK RELEASE LOCKS

#prepare the tables (we use OFFLINE as neither MINE nor APPLY is running; with OFFLINE we won't wait on network timeout)
PREPARE OFFLINE SCHEMA "TEST"
PROCESS SETUP MINE SCHEMA TEST MINE_PLSQL YES
 
#single-scn instantiation: unlock all tables and schemas, forget the SCN (so it does not affect any further PREPARE statements)
PROCESS LOCK CLEAR SCN
PROCESS SWITCH_REDOLOG
#prepare script for instantiation
PROCESS PREPARE_DP WRITE DP_NETWORKLINK DIRECTORY DATA_PUMP_DIR FILE /home/oracle/ZD8622/APPLY1.sh DBLINK DSRC USERID SYSTEM/manager@TRG11G
create ddcdb from ddcfile
set ON_WARNING SKIP
set ON_ERROR SKIP

8) Once all the 3 files are modified correctly, run the modified all.sh. Make sure there are no errors.

9) Run APPLY1.sh

10) NB : On MINE, start the console (make sure all processes are still down).

These parameters are key in making this setup work properly - map mine and apply processes

set MINE.APPLY_PEER = APPLY:APPLY1 
set APPLY1.MINE_UNIQUE_ID = <should be the same value with MINE.MINE_UNIQUE_ID>

 Show the unique id for the processes

dbvrep> show unique
*.MINE_UNIQUE_ID =
APPLY.MINE_UNIQUE_ID = BDA24890-BD99-11E6-AFD2-ED9C8A07B357
MINE.MINE_UNIQUE_ID = BDA24890-BD99-11E6-AFD2-ED9C8A07B357

 Set the unique id for APPLY1

dbvrep> set APPLY1.MINE_UNIQUE_ID = BDA24890-BD99-11E6-AFD2-ED9C8A07B357
Variable MINE_UNIQUE_ID set to BDA24890-BD99-11E6-AFD2-ED9C8A07B357 for process APPLY1

 Show the unique id for the processes, now includes APPLY1

dbvrep> show unique
*.MINE_UNIQUE_ID =
APPLY.MINE_UNIQUE_ID = BDA24890-BD99-11E6-AFD2-ED9C8A07B357
APPLY1.MINE_UNIQUE_ID = BDA24890-BD99-11E6-AFD2-ED9C8A07B357
MINE.MINE_UNIQUE_ID = BDA24890-BD99-11E6-AFD2-ED9C8A07B357

11) Start MINE, APPLY, APPLY1

12) Start console on MINE again and make sure the parameters are set correctly.

Below are the parameters to take note of.

dbvrep> show peer
*.APPLY_PEER = APPLY
APPLY.APPLY_PEER = APPLY
APPLY1.APPLY_PEER = APPLY1
MINE.APPLY_PEER = APPLY:APPLY1
*.FETCHER_PEER =
*.MINE_PEER = MINE
APPLY.MINE_PEER = MINE
APPLY1.MINE_PEER = MINE
MINE.MINE_PEER = MINE
*.NOTIFY_PEER_DOWN = ALL
dbvrep> show interface
*.APPLY_LISTEN_INTERFACE = undefined:1
APPLY.APPLY_LISTEN_INTERFACE = 0.0.0.0:7902
APPLY1.APPLY_LISTEN_INTERFACE = 0.0.0.0:7904
*.APPLY_REMOTE_INTERFACE = undefined:1
APPLY.APPLY_REMOTE_INTERFACE = dbvlin823.dbvisit.co.nz:7902
APPLY1.APPLY_REMOTE_INTERFACE = dbvlin821.dbvisit.co.nz:7904
*.FETCHER_LISTEN_INTERFACE = undefined:1
*.FETCHER_REMOTE_INTERFACE = undefined:1
*.MINE_LISTEN_INTERFACE = undefined:1
MINE.MINE_LISTEN_INTERFACE = 0.0.0.0:7901
*.MINE_REMOTE_INTERFACE = undefined:1
MINE.MINE_REMOTE_INTERFACE = dbvlin822.dbvisit.co.nz:7901

Console :

- Dbvisit Replicate 2.8.02(MAX edition) - Evaluation License expires in 30 days
MINE is running. Currently at plog 3843 and SCN 204302323 (12/09/2016 13:00:35).
APPLY is running. Currently at plog 3843 and SCN 204302288 (12/09/2016 13:00:28).
APPLY1 is running. Currently at plog 3843 and SCN 204302297 (12/09/2016 13:00:31).
Progress of replication ZD8622:MINE->APPLY: total/this execution
----------------------------------------------------------------------
TEST.DEPT:  100% Mine:2/2 Unrecov:0/0 Applied:2/1 Conflicts:0/0 Last:09/12/2016 12:50:01/OK
TEST.EMP:   100% Mine:2/2 Unrecov:0/0 Applied:2/1 Conflicts:0/0 Last:09/12/2016 12:59:52/OK
--------------------------------------------------------------------
2 tables listed.
Progress of replication ZD8622:MINE->APPLY1: total/this execution
--------------------------------------------------------------------
TEST.DEPT:100% Mine:2/2 Unrecov:0/0 Applied:2/2 Conflicts:0/0 Last:09/12/2016 12:50:01/OK
TEST.EMP: 100% Mine:2/2 Unrecov:0/0 Applied:2/2 Conflicts:0/0 Last:09/12/2016 12:59:50/OK
-------------------------------------------------------------------
2 tables listed.
dbvrep> list status
MINE is running. Currently at plog 3843 and SCN 204302470 (12/09/2016 13:00:58).
APPLY is running. Currently at plog 3843 and SCN 204302472 (12/09/2016 13:01:01).
APPLY1 is running. Currently at plog 3843 and SCN 204302381 (12/09/2016 13:00:50).

 

Have more questions? Submit a request

Comments