Configure replication with fetcher option

Dbvisit Replicate, in the XTD and MAX editions has as option called fetcher.
The fetcher is a process that copies the redo logs in real time and sends the logs to another server.  The MINE process will be on the midtier server and work from there.

 

 

Server/DatabaseName
Source databaseSOURCEDB
Source serverdbvisit210
Target databaseTGTDB
Target serverdbvisit250
TNS alias for source database:   SOURCEDB
TNS alias for target databaseTGTDB
Mid Tier serverdbvisit230

Setup Wizard

$ dbvrep
Initializing......done
Dbvisit Replicate version 2.7
Copyright (C) Dbvisit Software Limited.  All rights reserved.
No DDC file loaded.
Run "setup wizard" to start the configuration
wizard or try "help" to see all commands available.

dbvrep> setup wizard
This wizard configures Dbvisit Replicate.

The setup wizard creates configuration scripts, which need
to be run after the wizard ends. No changes to the databases are made before that.
The progress is saved every time a list of databases,
replications, etc. is shown. It will be re-read if wizard is restarted and the
same DDC name and script path is selected.
Run the wizard now? [yes] yes
Accept end-user license agreement? (view/yes/no) [view] yes

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 if wizard is rerun)
and 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): [] TEST_FETCH

(LICENSE_KEY) - Please enter your license key: [(trial)]

Which Replicate edition do you want to trial (LTD/XTD/MAX):[MAX]
(SETUP_SCRIPT_PATH) - Please enter a directory for location
of configuration scripts on this machine: [/home/oracle/TEST_FETCH]

Network configuration files were detected on this system in these locations:
/u01/app/oracle/product/11.2.0/db_1/network/admin
/u01/app/oracle/product/10.2.0/db_1/network/admin
/u01/app/oracle/product/11.2.0/db_1/network/admin
(TNS_ADMIN) - Please enter TNS configuration directory for
this machine: [/u01/app/oracle/product/11.2.0/db_1/network/admin]


Step 1

Step 1 - Describe databases
========================================
The first step is to describe databases used in the
replication. There are usually two of them (source and target).
Store SYSDBA and DBA passwords? Passwords only required
during setup and initialization? (yes/no) [yes]
Let's configure the database, describing it's type,
connectivity, user names etc.
What type of database is this? (Oracle/MySQL/SQL
Server/Oracle AWS RDS/CSV/Hadoop): [Oracle]
Please enter database TNS alias: [] SOURCEDB
Please enter SYSDBA user name: [SYS]
Please enter password for this user: [change_on_install] *****
Please enter user with DBA role: [SYSTEM]
Please enter password for this user: [manager] ******
Connecting to database SOURCEDB as SYSTEM to query list of
tablespaces and to detect ASM (by looking whether any redo logs or archived
logs are stored in ASM).
Enter the Dbvisit Replicate owner (this user will be created
by this script): [dbvrep]

Please enter password for this user: [dbvpasswd]

Permanent tablespaces detected on the database: USERS.
Please enter default permanent tablespace for this user: [USERS]
Temporary tablespaces detected on the database: TEMP.
Please enter default temporary tablespace for this user: [TEMP]

Following databases are now configured:
1: Oracle SOURCEDB, SYS/***, SYSTEM/***, dbvrep/***,
USERS/TEMP, dbvrep/, ASM:NO, TZ: +00:00
Enter the number of the database to modify it, or "add", or "done": [add]

Let's configure the database, describing it's type,
connectivity, user names etc.
What type of database is this? (Oracle/MySQL/SQL
Server/Oracle AWS RDS/CSV/Hadoop): [Oracle] Oracle 
Please enter database TNS alias: [] TGTDB
Please enter SYSDBA user name: [SYS]
Please enter password for this user: [change_on_install] *****
Please enter user with DBA role: [SYSTEM]
Please enter password for this user: [manager] ******

Connecting to database TGTDB as SYSTEM to query list of
tablespaces and to detect ASM (by looking whether any redo logs or archived
logs are stored in ASM).

Enter the Dbvisit Replicate owner (this user will be created by this script): [dbvrep] dbvrepf

Please enter password for this user: [dbvpasswd]
Permanent tablespaces detected on the database: RDSADMIN,USERS.
Please enter default permanent tablespace for this user:[RDSADMIN] USERS
Temporary tablespaces detected on the database: TEMP.
Please enter default temporary tablespace for this user: [TEMP]
Following databases are now configured:
1: Oracle SOURCEDB, SYS/***, SYSTEM/***, dbvrep/***,USERS/TEMP, dbvrep/, ASM:NO, TZ: +00:00
2: Oracle TGTDB, SYS/***, SYSTEM/***, dbvrepf/***, USERS/TEMP, dbvrepf/, ASM:NO, TZ: +00:00
Enter the number of the database to modify it, or "add", or "done": [done]

 

During Step 2 of the setup wizard is where the question about if you want to use fetcher is asked.

Step 2 - Replication pairs
========================================
The second step is to set source and targets for each
replication pair.
Let's configure the replication pair, selecting source and target.
Following databases are described:
1: SOURCEDB (Oracle)
2: TGTDB(Oracle)
Select source database: [1]
Select target database: [2]
Will be DDL replication enabled? [yes]
Use fetcher to offload the mining to a different server? (yes/no) [no] yes
Would you like to encrypt the data across the network (yes or no) [no]
Would you like to compress the data across the network (yes or no) [no]
How long do you want to set the network timeouts.
Recommended range between 60-300 seconds [60]
Lock and copy the data initially one-by-one or at a single SCN?
one-by-one : Lock tables one by one and capture SCN
single-scn : One SCN for all tables
ddl-only   : Only DDL script for target objects
resetlogs  : Use SCN from last resetlogs operation (standby activation, rman incomplete recovery)  
no-lock    : Do not lock tables. Captures previous SCN of oldest active transaction. Requires pre-requisite running of pre-all.sh script
    (one-by-one/single-scn/ddl-only/resetlogs/no-lock)[single-scn]
What data instantiation script to create?
dp_networklink : Data Pump with network link. No export
datafile is created (APPLY.sh)
dp_exp: Data Pump with export datafile. Creates an export datafile (APPLY.sh)
exp: Pre-datapump exp/imp with export datafile (APPLY.sh)
ddl_file: DDL file created (APPLY.sql)
ddl_run : DDL is automatically executed on target
load : All replicated data is created and loaded automatically
none                                                                                                                                          
(dp_networklink/dp_exp/exp/ddl_file/ddl_run/load/none) [dp_networklink]

Following replication pairs are now configured:

1: SOURCEDB (Oracle) ==> TGTDB (Oracle), DDL:
yes, fetcher: YES, process suffix: (no suffix), compression: no, encryption: no,
network timeout: 60, prepare type: single-scn, data load: dp_networklink
Enter number of replication pair to modify it, or "add", or "done": [done]

 

 Step 3 

Step 3 - Replicated tables
========================================
The third step is to choose the schemas and tables to be
replicated. If the databases are reachable, the tables are checked for
existence, datatype support, etc., schemas are queried for tables. Note
that all messages are merely hints/warnings and may be ignored if issues are
rectified before the scripts are actually executed.

Following tables are defined for replication pairs:
1: SOURCEDB (Oracle) ==> TGTDB (Oracle), DDL: yes, suffix: (no suffix), prepare: single-scn

  No tables defined.
Enter number of replication pair to modify it, or "done": [1]

Please enter list of all individual tables to be replicated.
Enter schema name(s) only to replicate all tables in that schema. Use comma or
space to delimit the entries.

Enter the tables and schemas: [] JDOE
Selected schemas: JDOE
Add more tables or schemas? (YES/NO) [NO]
To replicate changes of PL/SQL objects in schema(s), please
enter the schemas to be replicated. Note that specifying any entry will cause
additional privileges to be granted to dbvrep. Enter through a comma or space-delimited list.

Enter the list of schemas (PL/SQL): []
You can also specify some advanced options:
1. Rename schemas or tables.
2. Specify filtering conditions.
3. (Tables only) Configure Change Data Capture; this does
not maintain a copy of the source table, but logs all operations as separate
entries. This is useful for ETL or as an audit trail. This
usually requires adding of new columns (timestamps, old/new values etc.) to the
target table.

Specify rename name or filter condition for any of the specified schemas (YES/NO): [no]
Following tables are defined for replication pairs:

1: SOURCEDB (Oracle) ==> TGTDB (Oracle), DDL: yes, suffix: (no suffix), prepare: single-scn
  JDOE(tables)

Enter number of replication pair to modify it, or "done": [done]

 

Step 4

Step 4 - Process configuration
========================================
The fourth step is to configure the replication processes
for each replication.
Following processes are defined:
1: FETCHER on SOURCEDB
  Not configured.
2: MINE on SOURCEDB
  Not configured.
3: APPLY on RDSTGT
  Not configured.

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

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

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)? [/home/oracle/TEST_FETCH]

Following settings were pre-filled with defaults or your reloaded settings:
----------------------------------------
[FETCHER_REMOTE_INTERFACE]: Network remote interface: dbvisit210.dbvisit.co.nz:7901

[FETCHER_DATABASE]: Database TNS: SOURCEDB

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

[LOG_FILE]: General log file: /home/oracle/TEST_FETCH/log/dbvrep_%N_%D.%E

[LOG_FILE_TRACE]: Error traces: /home/oracle/TEST_FETCH/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: FETCHER on SOURCEDB
  Host: dbvisit210.dbvisit.co.nz, SMTP: no, SNMP: no
2: MINE on SOURCEDB
  Not configured.
3: APPLY on RDSTGT
  Not configured.

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

Fully qualified name of the server for the process (usually
co-located with the database, unless mine is offloaded using fetcher):   [dbvisit230.dbvisit.co.nz]
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)? [/home/oracle/TEST_FETCH]

Following settings were pre-filled with defaults or your reloaded settings:
----------------------------------------
[MINE_REMOTE_INTERFACE]: Network remote interface: dbvisit230.dbvisit.co.nz:7902

[MINE_DATABASE]: Database TNS: SOURCEDB

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

[MINE_STAGING_DIR]: Directory for files from fetcher: /home/oracle/TEST_FETCH/mine_stage

[MINE_PLOG]: Filemask for generated plogs: /home/oracle/TEST_FETCH/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:/home/oracle/TEST_FETCH/log/dbvrep_%N_%D.%E

[LOG_FILE_TRACE]: Error traces: /home/oracle/TEST_FETCH/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: FETCHER on SOURCEDB
  Host: dbvisit210.dbvisit.co.nz, SMTP: no, SNMP: no

2: MINE on SOURCEDB
  Host: dbvisit230.dbvisit.co.nz, SMTP: no, SNMP: no

3: APPLY on RDSTGT
  Not configured.
Enter number of process to modify it, or "done": [3]
Fully qualified name of the server for the process (usually
co-located with the database, unless mine is offloaded using fetcher):             [ip-10-139-39-29] dbvisit250.dbvisit.co.nz
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)? [/home/oracle/TEST_FETCH]

Following settings were pre-filled with defaults or your reloaded settings:
----------------------------------------
[APPLY_REMOTE_INTERFACE]: Network remote interface: dbvisit250.dbvisit.co.nz:7903

[APPLY_DATABASE]: Database TNS: TGTDB

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

[APPLY_STAGING_DIR]: Directory for received plogs: /home/oracle/TEST_FETCH/apply

[LOG_FILE]: General log file: /home/oracle/TEST_FETCH/log/dbvrep_%N_%D.%E
[LOG_FILE_TRACE]: Error traces: /home/oracle/TEST_FETCH/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: FETCHER on SOURCEDB
  Host: dbvisit210.dbvisit.co.nz, SMTP: no, SNMP: no
2: MINE on SOURCEDB
  Host: dbvisit230.dbvisit.co.nz, SMTP: no, SNMP: no
3: APPLY on RDSTGT
	Host: dbvisit250.dbvisit.co.nz, SMTP: no, SNMP: no

Enter number of process to modify it, or "done": [done]
Created file /home/oracle/TEST_FETCH/TEST_FETCH-APPLY.ddc.
Created file /home/oracle/TEST_FETCH/TEST_FETCH-FETCHER.ddc.
Created file /home/oracle/TEST_FETCH/TEST_FETCH-MINE.ddc.
Created file /home/oracle/TEST_FETCH/config/TEST_FETCH-setup.dbvrep.
Created file /home/oracle/TEST_FETCH/config/TEST_FETCH-dbsetup_SOURCEDB.sql.
Created file /home/oracle/TEST_FETCH/config/TEST_FETCH-dbsetup_RDSTGT.sql.
Created file /home/oracle/TEST_FETCH/config/TEST_FETCH-grants_SOURCEDB.sql.
Created file /home/oracle/TEST_FETCH/config/TEST_FETCH-grants_RDSTGT.sql.
Created file /home/oracle/TEST_FETCH/config/TEST_FETCH-onetime.ddc.
Created file /home/oracle/TEST_FETCH/start-console.sh.
Created file /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin210.dbvisit.co.nz.sh.
Created file /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin230.dbvisit.co.nz.sh.
Created file /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin250.dbvisit.co.nz.sh.
Created file /home/oracle/TEST_FETCH/Nextsteps.txt.
Created file /home/oracle/TEST_FETCH/TEST_FETCH-all.sh.
=============================================================================================================================================
Dbvisit Replicate wizard completed

Script /home/oracle/TEST_FETCH/TEST_FETCH-all.sh created.
This runs all the above created scripts. Please exit out of dbvrep, review and
run script as current user to setup and start Dbvisit Replicate.
=============================================================================================================================================

 

Run the *all.sh script

 $ ./TEST_FETCH-all.sh
 Setting up Dbvisit Replicate configuration
 Configure database SOURCEDB...
 This check fails if the DBID is not the expected one...
 
 Ok, check passed.
 Configure database TGTDB...
 This check fails if the DBID is not the expected one...
 
 Ok, check passed.
 Object grants for database SOURCEDB...
 Object grants for database TGTDB...
 Setting up the configuration
 Initializing......done
 WARN-1850: No DDC DB available, dictionary table does not exist.
 DDC loaded from database (0 variables).
 Dbvisit Replicate version 2.7
 Copyright (C) Dbvisit Software Limited. All rights reserved.
 DDC file /home/oracle/TEST_FETCH/config/TEST_FETCH-onetime.ddc loaded.
 MINE: Cannot determine Dbvisit Replicate dictionary version. (no
 dictionary exists)
 APPLY: Cannot determine Dbvisit Replicate dictionary version. (no
 dictionary exists)
 dbvrep> #clear the no-DDC-DB-available warning
 dbvrep> engine clear previous warnings
 dbvrep> set ON_WARNING SKIP
 Variable ON_WARNING set to SKIP for process *.
 dbvrep> set ON_ERROR EXIT
 Variable ON_ERROR set to EXIT for process *.
 dbvrep> ENGINE SWITCH_REDOLOG
 dbvrep> ENGINE SETUP MINE DROP DICTIONARY
 0 dictionary objects dropped.
 dbvrep> ENGINE SETUP MINE CREATE DICTIONARY
 dbvrep> ENGINE SETUP MINE LOAD DICTIONARY
 Supplemental logging on database set.
 Loading dictionary table DBRSCOL$
 Loading dictionary table DBRSIND$
 Loading dictionary table DBRSOBJ$
 Loading dictionary table DBRSTAB$
 Loading dictionary table DBRSUSER$
 Loading dictionary table DBRSV_$DATABASE
 ID of mine proces is 32B6B292-AF74-11E3-A3E3-AA6951CB262D. If not using
 DDC in database, set MINE_UNIQUE_ID to this value.
 dbvrep> ENGINE SETUP APPLY DROP DICTIONARY
 0 dictionary objects dropped.
 dbvrep> ENGINE SETUP APPLY CREATE DICTIONARY
 dbvrep> ENGINE SETUP APPLY LOAD DICTIONARY
 dbvrep> ENGINE PREPARE_DP SETUP CLEAR
 dbvrep> ENGINE SETUP PAIR MINE AND APPLY
 1 applier SCN set.
 dbvrep> SET APPLY.INSTANTIATE_SCN NOW
 Variable INSTANTIATE_SCN set to NOW for process APPLY.
 dbvrep> ENGINE SUPPLEMENTAL LOGGING SCHEMA JDOE ENABLE PRIMARY KEY
 dbvrep> ENGINE SWITCH_REDOLOG
 dbvrep> ENGINE WAIT_SCN_FLIP
 Waited 2 seconds until scn_to_timestamp changed.
 dbvrep> #single-scn instantiation: lock all tables and schemas
 dbvrep> ENGINE LOCK SCHEMAS JDOE
 Locking all schemas.
 Lock done.
 dbvrep> #single-scn instantiation: unlock all tables and schemas, but
 keep the SCN
 dbvrep> ENGINE LOCK RELEASE LOCKS
 dbvrep>
 dbvrep> #prepare the tables (we use OFFLINE as neither MINE not APPLY
 is running; with OFFLINE we won't wait on network timeout)
 dbvrep> PREPARE OFFLINE SCHEMA JDOE
 Table JDOE.LOADTEST instantiated at SCN 1753802
 dbvrep> #single-scn instantiation: unlock all tables and schemas,
 forget the SCN (so it does not affect any further PREPARE statements)
 dbvrep> ENGINE LOCK CLEAR SCN
 dbvrep> ENGINE SWITCH_REDOLOG
 dbvrep> #prepare script for instantiation
 dbvrep> ENGINE PREPARE_DP WRITE DP_NETWORKLINK DIRECTORY DATA_PUMP_DIR
 FILE /home/oracle/TEST_FETCH/APPLY.sh DBLINK SOURCEDB USERID
 system/****@TGTDB
 Created Data Pump script /home/oracle/TEST_FETCH/APPLY.sh, using
 network import.
 dbvrep> create ddcdb from ddcfile
 DDC loaded into database (340 variables).
 dbvrep> set ON_WARNING SKIP
 Variable ON_WARNING set to SKIP for process *.
 dbvrep> set ON_ERROR SKIP
 Variable ON_ERROR set to SKIP for process *.
 OK-0: Completed successfully.
 These steps are required after the TEST_FETCH-all.sh script runs:
 
 1) Create the necessary directory(ies) on the servers:
 dbvlin230.dbvisit.co.nz.sh: /home/oracle/TEST_FETCH
dbvlin250.dbvisit.co.nz.sh: /home/oracle/TEST_FETCH
 
 2) Copy the DDC files to the server(s) where the processes will run:
 dbvlin230.dbvisit.co.nz.sh: /home/oracle/TEST_FETCH/TEST_FETCH-APPLY.ddc
 dbvlin250.dbvisit.co.nz: /home/oracle/TEST_FETCH/TEST_FETCH-FETCHER.ddc
 dbvlin210.dbvisit.co.nz.sh: /home/oracle/TEST_FETCH/TEST_FETCH-MINE.ddc
 
 3) Review that path to dbvrep executable is correct in the run scripts:
 /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin210.dbvisit.co.nz.sh
 /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin230.dbvisit.co.nz.sh
 /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin250.dbvisit.co.nz.sh
 
 4) Copy the run script to the server(s) where the processes will run:
 dbvlin210.dbvisit.co.nz: /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin210.dbvisit.co.nz.sh
dbvlin230.dbvisit.co.nz: /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin230.dbvisit.co.nz.sh
dbvlin250.dbvisit.co.nz: /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin250.dbvisit.co.nz.sh
 
 5) Ensure firewall is open for listen interfaces 0.0.0.0:7903, 0.0.0.0:7901, 0.0.0.0:7902 used by the processes.
 
 6) Make sure the data on apply are in sync as of time when setup was run.
 Scripts for Data Pump/export/DDL were created as requested:
 /home/oracle/TEST_FETCH/APPLY.sh
 Create referenced database links (if any) before running the scripts.
 
 7) Start the replication processes on all servers:
 dbvlin210.dbvisit.co.nz: /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin210.dbvisit.co.nz.sh
 dbvlin230.dbvisit.co.nz: /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin230.dbvisit.co.nz.sh
 dbvlin250.dbvisit.co.nz.sh: /home/oracle/TEST_FETCH/TEST_FETCH-run-dbvlin250.dbvisit.co.nz.sh
 
 8) Start the console to monitor the progress:
 /home/oracle/TEST_FETCH/start-console.sh
 
 The above list is stored in /home/oracle/TEST_FETCH/Nextsteps.txt.

 

Copy the appropriate files as noted above.
Start the FETCHER , MINE and APPLY on each server.
Start the Console.Â