Target CSV (flat files)

In all replications, the source database must be an Oracle database. Therefore, two-way replication with CSV (flat files) is not supported.

NOTE

Replicating DDL is not currently supported with CSV as a target.
Adding (and preparing) new tables to the replication set is not currently supported when using Target CSV replication.
In CSV output mode Dbvisit Replicate does its own buffering, and this is performed in memory. It essentially always operates according to a pessimistic commit model. This means that both the STAGING and TARGET directories only contain records of committed transactions, so you should not find the DATE_COMMIT column empty. However, at any point in time even for committed operations, some files are too small, or too early in the transaction process to be written.
Ordering is preserved in transactions, and transaction order is also preserved. But transactions can overlap.
In other words - if the source has:
TX1 CH1
TX2 CH2
TX1 CH3
TX2 CH4
Then we create the output such that:
if you look at TX1, you will see CH1, CH3
if you look at TX2, you will see CH2, CH4
if you look at TXs, you will see TX1, TX2.
But in reality you will see TX1 CH1, TX1 CH3, TX2 CH2, TX2 CH4.

Setup Wizard Example

This example shows an Oracle-to-CSV one-way replication using the following information. We will be configuring the MINE and the APPLY on the same server in this example.

Server/Database	Name
Source database	ORCL (Oracle)
Source server	source (Linux)
Target database	n/a
Target server	source (Linux)
TNS alias for source database	ORCL

Throughout the wizard, defaults are shown in [brackets]. Just press enter to accept them. The replication name in this example is "reptest1".

First of all start the Dbvisit Replicate command line console.

[oracle@source replicate]$ ./dbvrep
Initializing......done
Dbvisit Replicate version 2.8
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>

Then start the setup wizard and follow the instructions. Most of the defaults can be chosen.

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]
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: [] test
(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: [/u01/app/oracle/test] /home/oracle/test
Network configuration files were detected on this system in these locations:
/u01/app/oracle/product/11.2.0/network/admin
(TNS_ADMIN) - Please enter TNS configuration directory for this machine: [/u01/app/oracle/product/11.2.0/network/admin]

Specify databases participating in the replication in the first step of setup wizard. Two things to note: This configuration is using the SOURCE database for the target schema repository, also the schema MUST be a different name than the schema used earlier, as they are on the same database.

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 its type, connectivity, user names etc.
What type of database is this? (Oracle/MySQL/Google Cloud SQL/SQL Server/Oracle AWS RDS/CSV/Hadoop):[Oracle]
Please enter database TNS alias: [] ORCL
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 ORCL 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: REPOE_DATA, USERS.
Please enter default permanent tablespace for this user: [REPOE_DATA] USERS
Temporary tablespaces detected on the database: TEMP.
Please enter default temporary tablespace for this user: [TEMP]
Following databases are now configured:
1: Oracle ORCL, 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 its type, connectivity, user names etc.
What type of database is this? (Oracle/MySQL/Google Cloud SQL/SQL Server/Oracle AWS RDS/CSV/Hadoop): [Oracle] CSV                              
File-based output selected. Questions regarding file locations and format will be asked in Step 4 - apply process(es).
Dbvisit Replicate needs to store its metadata in a database. Please configure it now. You can also use the source database, but
use different DBVREP user in such case.
What type of database to use for the repository? (Oracle/MySQL/Google Cloud SQL/SQL Server/Oracle AWS RDS/CSV/Hadoop): [Oracle]                              
Please enter database TNS alias: [] ORCL                                                                                        
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 ORCL 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] dbvrep2                                   
Please enter password for this user: [dbvpasswd2] 
Permanent tablespaces detected on the database: DATA, USERS.
Please enter default permanent tablespace for this user: [DATA]                                                                  
Temporary tablespaces detected on the database: TEMP.
Please enter default temporary tablespace for this user: [TEMP]                                                                  

Following databases are now configured:
1: Oracle ORCL, SYS/***, SYSTEM/***, dbvrep/***, DATA/TEMP, dbvrep/, ASM:No, TZ: +00:00
2: CSV(Oracle) ORCL, SYS/***, SYSTEM/***, dbvrep2/***, DATA/TEMP, dbvrep2/, ASM:No, TZ: +00:00

Enter the number of the database to modify it, or "add", or "done": [done]

In the second step of setup wizard,we configure replication pair by specifying source and target databases. Only Oracle databases can be the source here.

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: ORCL (Oracle)  
2: ORCL (CSV-Oracle) (cannot be source, is not Oracle) 
Select source database: [1]                                                                                                      
Select target database: [2]                                                                                                      
Will DDL replication be enabled? (Yes/No) [No]                                                                                  
Use fetcher to offload the mining to a different server? (Yes/No) [No]                                                           
Should where clauses (and change auditing) include all columns, not just changed and PK? (Yes/No) [No]                           
Would you like to encrypt the data across the network? (Yes/No) [No]                                                             
Would you like to compress the data across the network? (Yes/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) [none] 
Following replication pairs are now configured:
1: ORCL (Oracle) ==> ORCL (CSV-Oracle), DDL: No, fetcher: No, process suffix: (no suffix), compression: No, encryption:
No, network timeout: 60, prepare type: single-scn, data load: none
Enter number of replication pair to modify it, or "add", or "done": [done]

We will now choose the tables that we want to replicate.

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: ORCL (Oracle) ==> ORCL (CSV-Oracle), DDL: No, 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: [] SCOTT.EMP, SCOTT.DEPT                                                                    
Selected tables: SCOTT.DEPT,SCOTT.EMP
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): []                                                                                    

File-based output implies Change Data Capture.

Configure table SCOTT.DEPT:
Capture DELETE operations?(Yes/No) [Yes] 
Capture UPDATE operations - old values? (Yes/No) [Yes]                                                                    
Capture UPDATE operations - new values? (Yes/No) [Yes]                                                                    
Capture INSERT operations? (Yes/No) [Yes]                                                                                 

The columns at the target table can be called the same as on the source table, or they can be prefixed to indicate
whether they contain old or new values. As both new and old values for update are specified, at least one prefix has to
be defined.                                                                                                               
Prefix for columns with OLD values: [] OLD_
Prefix for columns with NEW values: [] NEW_                                                                               
Dbvisit Replicate can include additional information into the target table; this can be used to identify type of change,
when it occurred, who initiated the change etc.
Add basic additional information about the changes (SCN, time, operation type)? (Yes/No) [Yes]                            
Add more transactional information (transaction id, commit time)? (Yes/No) [No]                                           
Add auditing columns (login user, machine, OS user...)? (Yes/No) [No] Yes                                                 
Setup wizard chose following default names for the columns; you can use them or choose own names. Note that empty answer
confirms the proposed default; use "-" (minus) to remove the column from the audit.
CLIENT_INFO: Client info (cliinfo)
CURR_USER: Current user (cuser)
LOGON_USER: Logon user (luser)
MACHINE: Client machine name (machine)
OPERATION: Operation code (U/I/D) (opcol)
OS_PROC_ID: OS process id (osproc)
OS_PROG: OS program name (osprog)
OS_TERM: OS terminal (osterm)
OS_USER: OS user (osuser)
SCN: SCN at source (scn)
SERIAL: Oracle session serial# (serial)
SID: Oracle session ID (sid)
DATE_CHANGE: Date and time of the change (timestamp_change)
TRANSACTION_NAME: Transaction name (xidname)
Accept these settings? (Yes/No) [Yes]

 
Configure table SCOTT.EMP:
Configure table in a different way? Use COPY to copy settings from previous table? (Yes/COPY) [COPY] 

Following tables are defined for replication pairs:
1: SDB11 (Oracle) ==> SDB11 (CSV-Oracle), DDL: NO, suffix: (no suffix), prepare: single-scn
  SCOTT.DEPT, SCOTT.EMP
Enter number of replication pair to modify it, or "done": [done]

In the fourth and the final step of setup wizard, we can configure MINE and APPLY process parameters.

Step 4 - Process configuration
========================================
The fourth step is to configure the replication processes for each replication.

Following processes are defined:
1: MINE on ORCL
  Not configured.
2: APPLY on ORCL
  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): [dbvlin701.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]                                                                                                    
Following settings were pre-filled with defaults or your reloaded settings:
----------------------------------------
[MINE_REMOTE_INTERFACE]: Network remote interface: source:7901 
[MINE_DATABASE]: Database TNS: ORCL 
[TNS_ADMIN]: tnsnames.ora path: /u01/app/oracle/product/11.2.0/network/admin 
[MINE_PLOG]: Filemask for generated plogs: /home/oracle/test/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/log/dbvrep_%N_%D.%E 
[LOG_FILE_TRACE]: Error traces: /home/oracle/test/log/trace/dbvrep_%N_%D_%I_%U.%E 

Checking that these settings are valid...
Do you want to change any of the settings? [No]                                                                           
Following processes are defined:
1: MINE on ORCL
  Host: source, SMTP: No, SNMP: No
2: APPLY on ORCL
  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): [source]                                                                                      
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]                                                                                                    

Following settings were pre-filled with defaults or your reloaded settings:
---------------------------------------
[APPLY_REMOTE_INTERFACE]: Network remote interface: source:7902 
[APPLY_DATABASE]: Database TNS: ORCL
[TNS_ADMIN]: tnsnames.ora path: /u01/app/oracle/product/11.2.0/network/admin 
[APPLY_STAGING_DIR]: Directory for received plogs: /home/oracle/test/apply 
[LOG_FILE]: General log file: /home/oracle/test/log/dbvrep_%N_%D.%E 
[LOG_FILE_TRACE]: Error traces: /home/oracle/test/log/trace/dbvrep_%N_%D_%I_%U.%E 
[APPLY_FILEOUT_STAGING_DIR]: Staging filesystem directory for output files:
/home/oracle/test/out_stage/stage-%O.%A-%I_%C_%X.%E 
[APPLY_FILEOUT_TARGET_DIR]: Target filesystem directory for output files: /home/oracle/test/out_target/%O.%A-%I_%C_%X.%E 
[APPLY_FILEOUT_FILE_CSV_COMMA]: For CSV: field delimiter: , 
[APPLY_FILEOUT_FILE_CSV_NEWLINE]: For CSV: record delimiter: \n 
[APPLY_FILEOUT_FILE_BATCH_SIZE]: Size of output file batch: 10485760 
Checking that these settings are valid...

Do you want to change any of the settings? [No]                                                                           
Following processes are defined:
1: MINE on ORCL
  Host: source, SMTP: No, SNMP: No
2: APPLY on ORCL
  Host: ORCL, SMTP: No, SNMP: No
Enter number of process to modify it, or "done": [done] 
Created file /home/oracle/test/test-APPLY.ddc.
Created file /home/oracle/test/test-MINE.ddc.
Created file /home/oracle/test/config/test-setup.dbvrep.
Created file /home/oracle/test/config/test-dbsetup_ORCL_dbvrep.sql.
Created file /home/oracle/test/config/test-dbsetup_ORCL_dbvrep2.sql.
Created file /home/oracle/test/config/test-grants_ORCL_dbvrep.sql.
Created file /home/oracle/test/config/test-grants_ORCL_dbvrep2.sql.
Created file /home/oracle/test/config/test-onetime.ddc.
Created file /home/oracle/test/start-console.sh.
Created file /home/oracle/test/test-run-source.sh.
Created file /home/oracle/test/scripts/test-source-start-MINE.sh.
Created file /home/oracle/test/scripts/test-source-stop-MINE.sh.
Created file /home/oracle/test/scripts/test-source-dbvrep-MINE.sh.
Created file /home/oracle/test/scripts/systemd-dbvrep-MINE_test.service.
Created file /home/oracle/test/scripts/upstart-dbvrep-MINE_test.conf.
Created file /home/oracle/test/scripts/test-source-start-APPLY.sh.
Created file /home/oracle/test/scripts/test-source-stop-APPLY.sh.
Created file /home/oracle/test/scripts/test-source-dbvrep-APPLY.sh.
Created file /home/oracle/test/scripts/systemd-dbvrep-APPLY_test.service.
Created file /home/oracle/test/scripts/upstart-dbvrep-APPLY_test.conf.
Created file /home/oracle/test/Nextsteps.txt.
Created file /home/oracle/test/test-all.sh.
========================================================================================================================
Dbvisit Replicate wizard completed
Script /home/oracle/test/test-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.
========================================================================================================================
Optionally, the script can be invoked now by this wizard.
Run this script now? (Yes/No) [No]

Exit out of Dbvisit Replicate and start the script that has been generated by the setup wizard. These scripts configure the source database and target for replication and create the dbvrep schema in Oracle in the dbvrep and the dbvrep2 schema that in this case is also in the Oracle database. Out of all scripts, the only script to be executed is <replication-name>-all.sh (in this case the test-all.sh script) as this actually executes all the other scripts in the appropriate order.

[oracle@source test]$ ./test-all.sh
Setting up Dbvisit Replicate configuration
Configure database ORCL...
This check fails if the DBID is not the expected one...

Ok, check passed.

Configure database ORCL...
This check fails if the DBID is not the expected one...

Ok, check passed.

Object grants for database ORCL...
Object grants for database ORCL...
Setting up the configuration

Initializing......done
DDC loaded from database (0 variables).
Dbvisit Replicate version 2.8
Copyright (C) Dbvisit Software Limited.  All rights reserved.
DDC file /home/oracle/test/config/test-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> process 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> 
dbvrep> # Configuring default processes
dbvrep> choose process MINE
Process type MINE set to: MINE.
dbvrep> choose process APPLY
Process type APPLY set to: APPLY.
dbvrep> PROCESS SWITCH_REDOLOG
Redo log switch requested.dbvrep> PROCESS SETUP MINE DROP DICTIONARY
0 dictionary objects dropped.
dbvrep> PROCESS SETUP MINE CREATE DICTIONARY
dbvrep> PROCESS SETUP MINE LOAD DICTIONARY
Oldest active transaction SCN: 148241087 (no active transaction)
Supplemental logging on database set.
Loading dictionary table DBRSCCOL$
Loading dictionary table DBRSCDEF$
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
dbvrep> PROCESS SETUP APPLY DROP DICTIONARY
0 dictionary objects dropped.
dbvrep> PROCESS SETUP APPLY CREATE DICTIONARY
dbvrep> PROCESS SETUP APPLY LOAD DICTIONARY
dbvrep> PROCESS PREPARE_DP SETUP CLEAR
dbvrep> PROCESS SETUP PAIR MINE AND APPLY
Applier SCN set (start=148241108, current=148241108).
dbvrep> SET APPLY.INSTANTIATE_SCN NOW
Variable INSTANTIATE_SCN set to NOW for process APPLY.
dbvrep> SET MINE._PREPARE_SUPLOG_TYPE PK
Variable _PREPARE_SUPLOG_TYPE set to PK for process MINE.
dbvrep> EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_UNCMP #Ignore tables created by Compression Advisor
Exclude rule created.
dbvrep> EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_CMP #Ignore tables created by Compression Advisor
Exclude rule created.
dbvrep> EXCLUDE CREATE TABLE %.SCHEDULER$_% #Ignore tables created by Oracle scheduler (also used by schema/full expdp/impdp)
Exclude rule created.
dbvrep> EXCLUDE CREATE TABLE %.CMP1$% #Ignore tables created by Compression Advisor since 11.2.0.4
Exclude rule created.
dbvrep> EXCLUDE CREATE TABLE %.CMP2$% #Ignore tables created by Compression Advisor since 11.2.0.4
Exclude rule created.
dbvrep> EXCLUDE CREATE TABLE %.CMP3$% #Ignore tables created by Compression Advisor since 11.2.0.4
Exclude rule created.
dbvrep> EXCLUDE CREATE TABLE %.CMP4$% #Ignore tables created by Compression Advisor since 11.2.0.4
Exclude rule created.
dbvrep> PROCESS SUPPLEMENTAL LOGGING TABLE "SCOTT"."DEPT" ENABLE PRIMARY KEY
dbvrep> PROCESS SUPPLEMENTAL LOGGING TABLE "SCOTT"."EMP" ENABLE PRIMARY KEY
dbvrep> PROCESS SWITCH_REDOLOG
Redo log switch requested.
dbvrep> PROCESS WAIT_SCN_FLIP
Waited 3 seconds until scn_to_timestamp changed.
dbvrep> #single-scn instantiation: lock all tables and schemas
dbvrep> PROCESS LOCK TABLES "SCOTT"."DEPT" "SCOTT"."EMP"
Locking all tables.
Lock done.
dbvrep> #single-scn instantiation: unlock all tables and schemas, but keep the SCN
dbvrep> PROCESS LOCK RELEASE LOCKS
dbvrep>  
dbvrep> #prepare the tables (we use OFFLINE as neither MINE nor APPLY is running; with OFFLINE we won't wait on network timeout)
dbvrep> PREPARE OFFLINE TABLE "SCOTT"."DEPT" NODDL
Table SCOTT.DEPT instantiated at SCN 148251500
dbvrep> #CDC/Audit settings
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" INSERT Yes
CDCAUDIT Table SCOTT.DEPT INSERT Yes set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" DELETE Yes
CDCAUDIT Table SCOTT.DEPT DELETE Yes set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" UPDATE YES
CDCAUDIT Table SCOTT.DEPT UPDATE YES set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" OLDCOL_PREFIX OLD_
CDCAUDIT Table SCOTT.DEPT OLDCOL_PREFIX OLD_ set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" NEWCOL_PREFIX NEW_
CDCAUDIT Table SCOTT.DEPT NEWCOL_PREFIX NEW_ set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" OPCOL OPERATION
CDCAUDIT Table SCOTT.DEPT OPCOL OPERATION set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" SCN SCN
CDCAUDIT Table SCOTT.DEPT SCN SCN set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" TIMESTAMP_CHANGE DATE_CHANGE
CDCAUDIT Table SCOTT.DEPT TIMESTAMP_CHANGE DATE_CHANGE set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" SID SID
CDCAUDIT Table SCOTT.DEPT SID SID set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" SERIAL SERIAL
CDCAUDIT Table SCOTT.DEPT SERIAL SERIAL set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" CUSER CURR_USER
CDCAUDIT Table SCOTT.DEPT CUSER CURR_USER set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" LUSER LOGON_USER
CDCAUDIT Table SCOTT.DEPT LUSER LOGON_USER set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" CLIINFO CLIENT_INFO
CDCAUDIT Table SCOTT.DEPT CLIINFO CLIENT_INFO set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" OSUSER OS_USER
CDCAUDIT Table SCOTT.DEPT OSUSER OS_USER set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" MACHINE MACHINE
CDCAUDIT Table SCOTT.DEPT MACHINE MACHINE set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" OSTERM OS_TERM
CDCAUDIT Table SCOTT.DEPT OSTERM OS_TERM set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" OSPROC OS_PROC_ID
CDCAUDIT Table SCOTT.DEPT OSPROC OS_PROC_ID set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" OSPROG OS_PROG
CDCAUDIT Table SCOTT.DEPT OSPROG OS_PROG set.
dbvrep> CDCAUDIT TABLE "SCOTT"."DEPT" XIDNAME TRANSACTION_NAME
CDCAUDIT Table SCOTT.DEPT XIDNAME TRANSACTION_NAME set.
dbvrep> 
dbvrep> PREPARE OFFLINE TABLE "SCOTT"."EMP" NODDL
Table SCOTT.EMP instantiated at SCN 148251500
dbvrep> #CDC/Audit settings
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" INSERT Yes
CDCAUDIT Table SCOTT.EMP INSERT Yes set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" DELETE Yes
CDCAUDIT Table SCOTT.EMP DELETE Yes set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" UPDATE YES
CDCAUDIT Table SCOTT.EMP UPDATE YES set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" OLDCOL_PREFIX OLD_
CDCAUDIT Table SCOTT.EMP OLDCOL_PREFIX OLD_ set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" NEWCOL_PREFIX NEW_
CDCAUDIT Table SCOTT.EMP NEWCOL_PREFIX NEW_ set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" OPCOL OPERATION
CDCAUDIT Table SCOTT.EMP OPCOL OPERATION set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" SCN SCN
CDCAUDIT Table SCOTT.EMP SCN SCN set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" TIMESTAMP_CHANGE DATE_CHANGE
CDCAUDIT Table SCOTT.EMP TIMESTAMP_CHANGE DATE_CHANGE set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" SID SID
CDCAUDIT Table SCOTT.EMP SID SID set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" SERIAL SERIAL
CDCAUDIT Table SCOTT.EMP SERIAL SERIAL set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" CUSER CURR_USER
CDCAUDIT Table SCOTT.EMP CUSER CURR_USER set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" LUSER LOGON_USER
CDCAUDIT Table SCOTT.EMP LUSER LOGON_USER set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" CLIINFO CLIENT_INFO
CDCAUDIT Table SCOTT.EMP CLIINFO CLIENT_INFO set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" OSUSER OS_USER
CDCAUDIT Table SCOTT.EMP OSUSER OS_USER set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" MACHINE MACHINE
CDCAUDIT Table SCOTT.EMP MACHINE MACHINE set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" OSTERM OS_TERM
CDCAUDIT Table SCOTT.EMP OSTERM OS_TERM set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" OSPROC OS_PROC_ID
CDCAUDIT Table SCOTT.EMP OSPROC OS_PROC_ID set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" OSPROG OS_PROG
CDCAUDIT Table SCOTT.EMP OSPROG OS_PROG set.
dbvrep> CDCAUDIT TABLE "SCOTT"."EMP" XIDNAME TRANSACTION_NAME
CDCAUDIT Table SCOTT.EMP XIDNAME TRANSACTION_NAME set.
dbvrep> 
dbvrep> #single-scn instantiation: unlock all tables and schemas, forget the SCN (so it does not affect any further PREPARE statements)
dbvrep> PROCESS LOCK CLEAR SCN
dbvrep> PROCESS SWITCH_REDOLOG
Redo log switch requested.
dbvrep> #prepare script for instantiation
dbvrep> create ddcdb from ddcfile
DDC loaded into database (405 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.
WARN-1850: No DDC DB available, dictionary table does not exist.
These steps are required after the test-all.sh script runs:

1) Create the necessary directory(ies) on the servers:

2) Copy the DDC files to the server(s) where the processes will run:
source: /home/oracle/test/test-APPLY.ddc
source: /home/oracle/test/test-MINE.ddc

3) Review that path to dbvrep executable is correct in the run scripts:
/home/oracle/test/test-run-source.sh

4) Copy the run script to the server(s) where the processes will run:

source: /home/oracle/test/test-run-source.sh


5) Ensure firewall is open for listen interfaces 0.0.0.0:7902, 0.0.0.0:7901 used by the processes.
6) Make sure the data on apply are in sync as of time when setup was run.
7) Start the replication processes on all servers:
source: /home/oracle/test/test-run-source.sh
8) Start the console to monitor the progress:
/home/oracle/test/start-console.sh

The above list is stored in /home/oracle/test/Nextsteps.txt.
oracle@source[test]:

Starting the MINE and APPLY process requires their respective DDC config files and run scripts that actually starts these processes in background. ( On Windows system, MINE and APPLY act as services). These steps are outlined in Nextsteps.txt. Because we are running both MINE and APPLY on one machine we only need to start one script.

Starting MINE and APPLY on source

oracle@source[test]: /home/oracle/test/test-run-source.sh
Initializing......done
DDC loaded from database (405 variables).
Dbvisit Replicate version 2.8.00_unreleased_156_gb80efb9
Copyright (C) Dbvisit Software Limited.  All rights reserved.
DDC file /home/oracle/test/test-APPLY.ddc loaded.
Starting process APPLY...Created directory /home/oracle/test/ddc_backup
Created directory /home/oracle/test/log/trace/
Created directory /home/oracle/test/apply
started
Initializing......done
DDC loaded from database (405 variables).
Dbvisit Replicate version 2.8
Copyright (C) Dbvisit Software Limited.  All rights reserved.
DDC file /home/oracle/test/test-MINE.ddc loaded.
Starting process MINE...Created directory /home/oracle/test/mine/
started
oracle@source[test]:

Dbvisit Replicate has now been configured and started in the background. To monitor replication progress start the console on source by executing start-console.sh script.

[oracle@source test]$ ./start-console.sh
Dbvisit Replicate 2.8(MAX edition) - Evaluation License expires in 30 days
MINE is running. Currently at plog 5905 and SCN 148265866 (06/01/2016 20:40:47).
APPLY is running. Currently at plog 5905 and SCN 148265848 (06/01/2016 20:40:44).
Progress of replication test:MINE->APPLY: total/this execution
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
0 tables listed.
dbvrep>

When starting the MINE and APPLY processes for the first time they enter the INITIALIZATION state. In this phase Dbvisit Replicate replicates the contents of its internal dictionary and repository from the source to the target schema (in this case dbvrep2).

Replication proper does not commence until the initialization phase has been completed. This is completed when the console show both MINE and APPLY are running.
We will now insert a row, update a row and delete a row in the SCOTT.DEPT table

SQL> insert into dept values (53,'test', 'here', 53);
1 row created.


SQL> commit;
Commit complete.

You can see in the console that things have now changed.

- Dbvisit Replicate 2.8(MAX edition) - Evaluation License expires in 30 days
MINE is running. Currently at plog 5905 and SCN 148269449 (06/01/2016 20:48:35).
APPLY is running. Currently at plog 5905 and SCN 148269366 (06/01/2016 20:48:29).
Progress of replication test:MINE->APPLY: total/this execution
-------------------------------------------------------------------------------------------------------------------------
SCOTT.DEPT:                   100%  Mine:1/1             Unrecov:0/0         Applied:1/1         Conflicts:0/0       Last:27/05/2016 20:46:32/OK
-------------------------------------------------------------------------------------------------------------------------

1 tables listed.

On the operating system you can see the file that was created and what the output looks like. The output file will be in the out_target directory in the APPLY install (by default).

oracle@source[test]: ls 
SCOTT.DEPT-87106_148267870_0007.018.00020f4a.csv
oracle@source[test]: more SCOTT.DEPT-87106_148267870_0007.018.00020f4a.csv
OLD_DEPTNO,OLD_DNAME,OLD_LOC,OLD_BLAH,NEW_DEPTNO,NEW_DNAME,NEW_LOC,NEW_BLAH,OPERATION,DATE_CHANGE,SCN,SID,SERIAL,CURR_USER
,LOGON_USER,CLIENT_INFO,OS_USER,MACHINE,OS_TERM,OS_PROC_ID,OS_PROG,TRANSACTION_NAME
,,,,53,test,here,53,I,2016.05.27 20:45:12,148267870,23,1643,SYS,SCOTT,,oracle,source,pts/1,21043,sqlplus@
source (TNS V1-V3),

.