In all replications, the source database must be an Oracle database. Therefore, two-way replication with CSV (flat files) is not supported.
| Info | ||
|---|---|---|
| ||
In other words - if the source has:
Then we create the output such that:
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.
| No Format | ||
|---|---|---|
| ||
[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> |
...
| No Format | ||
|---|---|---|
| ||
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.
| No Format | ||
|---|---|---|
| ||
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.
| No Format | ||
|---|---|---|
| ||
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.
| No Format | ||
|---|---|---|
| ||
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.
| No Format | ||
|---|---|---|
| ||
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.
...
| No Format | ||
|---|---|---|
| ||
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.
| No Format | ||
|---|---|---|
| ||
[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).
...
| No Format | ||
|---|---|---|
| ||
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.
...
| No Format | ||
|---|---|---|
| ||
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), |
.