We're here to help!

Data Instantiation for MySQL (step-by-step guide)

Follow

Abstract This section describes the process of instantiation of MySQL database using CSV files as of SCN prior to starting the replication.

Prerequisites This document assumes that reader has the following knowledge:

  • Basic experience with Dbvisit Replicate, familiar with running the setup wizard. If not, please follow the step by step guide in Oracle to MySQL using Swingbench example.
  • Knowledge of Oracle and MySQL, able to connect to the database and know how to execute scripts, know the login credentials.
  • Some knowledge of Unix OS, i.e. is able to run commands and navigate on the filesystem, view/edit files.

In this document replication name (can be referred as REPLICATION_NAME) is oramysql.

Preparation steps

1. Set up the Oracle to MySQL replication. It is important to choose single-scn and ddl_file on the following questions of Step 2 - Replicate Pairs:

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) [one-by-one] single-scn

and

What data instantiation script to create?
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
(ddl_file/ddl_run/load/none) [ddl_file] ddl_file

2. Exclude the unsupported columns, if any. The process described in detail in Oracle to MySQL using Swingbench guide.
3. Run the -all.sh or -all.bat script depending on your OS. Check the output and remember or write down the SCN from the prepare section. Lines of interest will look the following way:

Table SOME_SCHEMA.SOME_TABLE instantiated at SCN 4575498

If for some reason these lines haven't been noticed at the time of script execution, it is always possible to find them in the -all.log file.
4. If the previous step is successfull, the APPLY.sql file contains DDL statements to create objects on the target MySQL database. The output of the APPLY.sql should be examined to make sure that generated statements satisfy the requirements.
5. The APPLY.sql should be executed on the target MySQL database to create objects prior to instantiation.

Initial Data Load

Dbvisit Replicate offers several ways to load the initial data from Source database to Target database (perform data instantiation). While load method is being convenient method (it is described in Oracle to MySQL using Swingbench), we feel that it is necessary to describe the manual data instantiation procedure which, while being time consuming, but gives more control of the process. The method described in this section will use CSV-format files as an intermediate storage for data transfer, however — it is possible to use any tools that are available on the market to transfer the data from Oracle objects bound for replication to newly created MySQL objects, depending on customers' preference.

Exporting the Data to CSV Format

In this section the csv_exp tool will be used to export data from Oracle to CSV files, which is available on Github.

Please note that csv_exp is provided AS-IS, without any warranty and/or support (see LICENSE for more details).

Exporting of the whole schema in the format compatible with mysql CSV importer can be done using the following command:

csv_exp -s MY_SCHEMA --scn  USER/PASSWORD@DATABASE --xc  --null-as '\N'

MY_SCHEMA should be replaced with the desired schema name and SCN_number — with the SCN number captured earlier while running the -all.sh (or bat) script (see step 2 of preparation). This will generate a number of files with the names MY_SCHEMA.*.csv in the current folder.

IMPORTANT: The parameter --null-as '\N' is required for MySQL CSV import to work correctly, otherwise rows containing any NULL columns will yield warning messages and might not be imported. With aforementioned parameter, all NULL values will be exported as \N, as per example below.

Example

Execution without parameter --null-as (NULLS are exported as nothing):

[oracle@oel712csrc 2]$ csv_exp.py --sql \
  "SELECT 'VALUE1' as COLUMN1,NULL as NULL_COLUMN2, 'VALUE3' AS COLUMN3, NULL as NULL_COLUMN4  from DUAL" \ 
  test1/test1@unitsrc

COLUMN1,NULL_COLUMN2,COLUMN3,NULL_COLUMN4
VALUE1,,VALUE3,

With parameter --null-as "\N" (NULLS are exported as \N):

[oracle@oel712csrc 2]$ csv_exp.py --sql \
  "SELECT 'VALUE1' as COLUMN1,NULL as NULL_COLUMN2, 'VALUE3' AS COLUMN3, NULL as NULL_COLUMN4  from DUAL" \
  --null-as '\N' \
  test1/test1@unitsrc 

COLUMN1,NULL_COLUMN2,COLUMN3,NULL_COLUMN4
VALUE1,\N,VALUE3,\N

Importing data from CSV

Import data using MySQL conventional methods described in MySQL documentation (see the Example below).

Finalizing steps

Verification

It is always a good idea to verify the loading results, such as - comparison of row counts on Source and Target, selective comparison of data rows, making sure that some tricky values have made it to target database (if there are such).

Startup

After all the described steps has been completed, the Replication can be started. Please refer to other sections of this User Manual.


Example

Environment:

  • Source
    • Hostname: oel712csrc
    • Database: Oracle 12.1, TNS: UNITSRC
  • Target
    • Hostname: oel712ctrg
    • Database: MySQL 5.7
  • Replication name: oramysql
  • Home directory: /home/oracle/oramysql
  • Source Oracle schema: OE
  • Target MySQL database: OE

Set up steps

  1. Run setup wizard (see Appendix A for transcript);
  2. Change the -setup.dbvrep file. 

    #!/bin/sh
    cd /home/oracle/oramysql
    ed config/*-onetime.ddc <<< $'a\nset _APPLY_MYSQL_CFG /home/oracle/.my.cnf\n.\nw'
    ed config/*-setup.dbvrep <<< $'H\n/prepare script\ni\nEXCLUDE COLUMN OE.PRODUCT_INFORMATION.WARRANTY_PERIOD
    EXCLUDE COLUMN OE.ORDER_ITEMS.CONDITION
    EXCLUDE COLUMN OE.ORDERS.ORDER_DATE\n.\nw'

    3. Execute _-all.sh script (_see Appendix B* for transcript);
    4. Copy replication files to the target system:

    scp -R /home/oracle/oramysql oel712ctrg:/home/oracle
    

    5. Executing APPLY.sql on the target (oel712ctrg):

    mysql -u root -p < /home/oracle/oramysql/APPLY.sql
    

    6. Exporting the OE Swingbench schema at SCN = 6909974, excluding the same columns as are excluded in the replication:

    $ csv_exp.py -s OE --scn 6924650 OE/oe@unitsrc \
        -xc OE.PRODUCT_INFORMATION.WARRANTY_PERIOD \
        -xc OE.ORDER_ITEMS.CONDITION \
        -xc OE.ORDERS.ORDER_DATE \
        --null-as "\N"
    
    EXPORTING: OE.ADDRESSES...
    EXPORTING: OE.CARD_DETAILS...
    EXPORTING: OE.CUSTOMERS...
    EXPORTING: OE.INVENTORIES...
    EXPORTING: OE.LOGON...
    EXPORTING: OE.ORDERENTRY_METADATA...
    EXPORTING: OE.ORDERS...
    EXPORTING: OE.ORDER_ITEMS...
    EXPORTING: OE.PRODUCT_DESCRIPTIONS...
    EXPORTING: OE.PRODUCT_INFORMATION...
    EXPORTING: OE.WAREHOUSES...
    

    This will result in the following set of files:

     15M OE.ADDRESSES.csv             39M OE.ORDER_ITEMS.csv
    9.9M OE.CARD_DETAILS.csv          17M OE.ORDERS.csv
     17M OE.CUSTOMERS.csv            112K OE.PRODUCT_DESCRIPTIONS.csv
     16M OE.INVENTORIES.csv          176K OE.PRODUCT_INFORMATION.csv
     13M OE.LOGON.csv                 28K OE.WAREHOUSES.csv
    4.0K OE.ORDERENTRY_METADATA.csv
    

    5. Transfer them to the target system:

    [oracle@oel712csrc /tmp]$ scp *.csv oel712ctrg:/tmp
    OE.ADDRESSES.csv                              100%   14MB  14.4MB/s   00:00    
    OE.CARD_DETAILS.csv                           100%   10MB   9.9MB/s   00:00    
    OE.CUSTOMERS.csv                              100%   16MB  16.1MB/s   00:00    
    OE.INVENTORIES.csv                            100%   15MB  15.1MB/s   00:00    
    OE.LOGON.csv                                  100%   12MB  12.1MB/s   00:01    
    OE.ORDERENTRY_METADATA.csv                    100%  126     0.1KB/s   00:00    
    OE.ORDER_ITEMS.csv                            100%   37MB  36.7MB/s   00:00    
    OE.ORDERS.csv                                 100%   16MB  15.8MB/s   00:00    
    OE.PRODUCT_DESCRIPTIONS.csv                   100%  111KB 110.7KB/s   00:00    
    OE.PRODUCT_INFORMATION.csv                    100%  176KB 175.6KB/s   00:00    
    OE.WAREHOUSES.csv                             100%   27KB  27.4KB/s   00:00    
    

    8. Import CSV files into MySQL using the command line utility:

    for f in *.csv; do
        echo ">>> IMPORTING: $f"
        #
        TABLE_NAME=`basename "$f" .csv`
        COLLIST=`head -1 "$TABLE_NAME.csv"`
        #
        mysql --execute="LOAD DATA LOCAL INFILE '$f' INTO TABLE $TABLE_NAME CHARACTER SET UTF8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES ($COLLIST); SHOW WARNINGS;"
    done
    
    

    Examine the warnings and verify that all the required data has been loaded.
    Sample output:

    >>> IMPORTING: OE.ADDRESSES.csv
    >>> IMPORTING: OE.CARD_DETAILS.csv
    >>> IMPORTING: OE.CUSTOMERS.csv
    >>> IMPORTING: OE.INVENTORIES.csv
    >>> IMPORTING: OE.LOGON.csv
    >>> IMPORTING: OE.ORDERENTRY_METADATA.csv
    >>> IMPORTING: OE.ORDER_ITEMS.csv
    >>> IMPORTING: OE.ORDERS.csv
    >>> IMPORTING: OE.PRODUCT_DESCRIPTIONS.csv
    >>> IMPORTING: OE.PRODUCT_INFORMATION.csv
    >>> IMPORTING: OE.WAREHOUSES.csv
    

    9. Comparison of row counts with the following SQL Script.

    SET LIN 160
    SET PAGESIZE 0
    SET TAB OFF
    SET HEAD OFF
    SELECT 'SELECT '''||TABLE_NAME||''' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.'||TABLE_NAME||' UNION ALL' from USER_TABLES ORDER BY TABLE_NAME;
    
    SELECT 'ADDRESSES' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.ADDRESSES UNION ALL
    SELECT 'CARD_DETAILS' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.CARD_DETAILS UNION ALL
    SELECT 'CUSTOMERS' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.CUSTOMERS UNION ALL
    SELECT 'INVENTORIES' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.INVENTORIES UNION ALL
    SELECT 'LOGON' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.LOGON UNION ALL
    SELECT 'ORDERENTRY_METADATA' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.ORDERENTRY_METADATA UNION ALL
    SELECT 'ORDERS' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.ORDERS UNION ALL
    SELECT 'ORDER_ITEMS' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.ORDER_ITEMS UNION ALL
    SELECT 'PRODUCT_DESCRIPTIONS' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.PRODUCT_DESCRIPTIONS UNION ALL
    SELECT 'PRODUCT_INFORMATION' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.PRODUCT_INFORMATION UNION ALL
    SELECT 'WAREHOUSES' as TABLE_NAME, COUNT(1) as ROW_COUNT from OE.WAREHOUSES;
    

    Oracle:

    ADDRESSES                169423
    CARD_DETAILS             169170
    CUSTOMERS                119170
    INVENTORIES              898587
    LOGON                    378187
    ORDERENTRY_METADATA           4
    ORDERS                   194080
    ORDER_ITEMS              560733
    PRODUCT_DESCRIPTIONS       1000
    PRODUCT_INFORMATION        1000
    WAREHOUSES                 1000
    

    MySQL:

    +----------------------+-----------+
    | TABLE_NAME           | ROW_COUNT |
    +----------------------+-----------+
    | ADDRESSES            |    169423 |
    | CARD_DETAILS         |    169170 |
    | CUSTOMERS            |    119170 |
    | INVENTORIES          |    898587 |
    | LOGON                |    378187 |
    | ORDERENTRY_METADATA  |         4 |
    | ORDERS               |    194080 |
    | ORDER_ITEMS          |    560733 |
    | PRODUCT_DESCRIPTIONS |      1000 |
    | PRODUCT_INFORMATION  |      1000 |
    | WAREHOUSES           |      1000 |
    +----------------------+-----------+
    

    Output match.
    10. Replication start up

Appendixes

Appendix A: Setup wizard

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: [] oramysql
(LICENSE_KEY) - Please enter your license key: [(trial)] trial
Which Replicate edition do you want to trial (LTD/XTD/MAX): [MAX] MAX
(SETUP_SCRIPT_PATH) - Please enter a directory for location of configuration scripts on this machine: [/home/oracle/Documents/oramysql] /home/oracle/oramysql

Network configuration files were detected on this system in these locations:
/u01/app/oracle/product/12.1.0.2/db_1/network/admin

(TNS_ADMIN) - Please enter TNS configuration directory for this machine: [/u01/app/oracle/product/12.1.0.2/db_1/network/admin]


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] 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] oracle
Please enter database TNS alias: [] unitsrc
Please enter SYSDBA user name: [SYS] SYS
Please enter password for this user: [change_on_install] ******
Please enter user with DBA role: [SYSTEM] SYSTEM
Please enter password for this user: [manager] ******


Connecting to database unitsrc 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] USERS

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

Following databases are now configured:
1: Oracle unitsrc, SYS/***, SYSTEM/***, dbvrep/***, USERS/TEMP, dbvrep/, ASM:No, TZ: -07:00

Enter the number of the database to modify it, or "add", or "done": [add] add
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] mysql
Please enter database hostname: [localhost] oel712ctrg
Please enter user name of an administrator: [root] root
Please enter password for this user: [] ****
Enter the user to log into apply database: [root] root
Please enter password for this user: [password] ****
Enter the database (schema) to use for Dbvisit Replicate internal data (will be created in the script): [dbvrep]


Following databases are now configured:
1: Oracle unitsrc, SYS/***, SYSTEM/***, dbvrep/***, USERS/TEMP, dbvrep/, ASM:No, TZ: -07:00
2: MySQL host=oel712ctrg, root/***, root/***, root/***, /, dbvrep/, ASM:n/a, TZ:

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

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: unitsrc (Oracle)
2: host=oel712ctrg (MySQL) (cannot be source, is not Oracle)
Select source database: [1] 1
Select target database: [2] 2
Will limited DDL replication be enabled? (Yes/No) [Yes] Y
Use fetcher to offload the mining to a different server? (Yes/No) [No] No
Should where clauses (and Event Streaming) include all columns, not just changed and PK? (Yes/No) [No] No
Would you like to encrypt the data across the network? (Yes/No) [No] No
Would you like to compress the data across the network? (Yes/No) [No] No
How long do you want to set the network timeouts. Recommended range between 60-300 seconds [60] 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?
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
(ddl_file/ddl_run/load/none) [ddl_file]


Following replication pairs are now configured:
1: unitsrc (Oracle) ==> host=oel712ctrg (MySQL), DDL: YES, fetcher: NO, process suffix: (no suffix), compression: NO, encryption: NO, network timeout: 60, prepare type: single-scn, data load: ddl_file

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

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: unitsrc (Oracle) ==> host=oel712ctrg (MySQL), DDL: YES, suffix: (no suffix), prepare: single-scn
  No tables defined.

Enter number of replication pair to modify it, or "done": [1] 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: [] OE

Selected schemas: OE
Add more tables or schemas? (Yes/No) [No] No

You can also specify some advanced options:
1. Exclude some tables from schema-level replication
2. Rename schemas or tables.
3. Specify filtering conditions.
4. (Tables only) Configure Event Streaming; 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, filter condition or audit for any of the specified schemas? (Yes/No) [No] No
No
(PREPARE_SCHEMA_EXCEPTIONS) - Specify tables to exclude from PREPARE SCHEMA, if any: []

Following tables are defined for replication pairs:
1: unitsrc (Oracle) ==> host=oel712ctrg (MySQL), DDL: YES, suffix: (no suffix), prepare: single-scn
  OE(tables)

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

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

Following processes are defined:
1: MINE on unitsrc
  Not configured.
2: APPLY on host=oel712ctrg
  Not configured.

Enter number of process to modify it, or "done": [1] 1
Fully qualified name of the server for the process (usually co-located with the database, unless mine is offloaded using fetcher): [oel712csrc]
Server type (Windows/Linux/Unix): [Linux] 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/oramysql]

Following settings were pre-filled with defaults or your reloaded settings:
----------------------------------------
[MINE_REMOTE_INTERFACE]: Network remote interface: oel712csrc:7901
[MINE_DATABASE]: Database TNS: unitsrc
[TNS_ADMIN]: tnsnames.ora path: /u01/app/oracle/product/12.1.0.2/db_1/network/admin
[MINE_PLOG]: Filemask for generated plogs: /home/oracle/oramysql/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/oramysql/log/dbvrep_%N_%D.%E
[LOG_FILE_TRACE]: Error traces: /home/oracle/oramysql/log/trace/dbvrep_%N_%D_%I_%U.%E

Checking that these settings are valid...
Do you want to change any of the settings? [No] No
No

Following processes are defined:
1: MINE on unitsrc
  Host: oel712csrc, SMTP: No, SNMP: No
2: APPLY on host=oel712ctrg
  Not configured.

Enter number of process to modify it, or "done": [2] 2
Fully qualified name of the server for the process (usually co-located with the database, unless mine is offloaded using fetcher): [oel712ctrg] oel712ctrg
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/oramysql]

Following settings were pre-filled with defaults or your reloaded settings:
----------------------------------------
[APPLY_REMOTE_INTERFACE]: Network remote interface: oel712ctrg:7902
[APPLY_DATABASE]: Database MySQL connection string: database=dbvrep;host=oel712ctrg
[TNS_ADMIN]: tnsnames.ora path: /u01/app/oracle/product/12.1.0.2/db_1/network/admin
[APPLY_SCHEMA]: Dbvisit Replicate database (schema): dbvrep
[APPLY_STAGING_DIR]: Directory for received plogs: /home/oracle/oramysql/apply
[LOG_FILE]: General log file: /home/oracle/oramysql/log/dbvrep_%N_%D.%E
[LOG_FILE_TRACE]: Error traces: /home/oracle/oramysql/log/trace/dbvrep_%N_%D_%I_%U.%E

Checking that these settings are valid...
Do you want to change any of the settings? [No] No

Following processes are defined:
1: MINE on unitsrc
  Host: oel712csrc, SMTP: No, SNMP: No
2: APPLY on host=oel712ctrg
  Host: oel712ctrg, SMTP: No, SNMP: No

Enter number of process to modify it, or "done": [done] done
Created file /home/oracle/oramysql/oramysql-APPLY.ddc.
Created file /home/oracle/oramysql/oramysql-MINE.ddc.
Created file /home/oracle/oramysql/config/oramysql-setup.dbvrep.
Created file /home/oracle/oramysql/config/oramysql-dbsetup_unitsrc_dbvrep.sql.
Created file /home/oracle/oramysql/config/oramysql-dbsetup_database_dbvrep_host_oel712ctrg_root.sql.
Created file /home/oracle/oramysql/config/oramysql-grants_unitsrc_dbvrep.sql.
Created file /home/oracle/oramysql/config/oramysql-grants_database_dbvrep_host_oel712ctrg_root.sql.
Created file /home/oracle/oramysql/config/oramysql-onetime.ddc.
Created file /home/oracle/oramysql/start-console.sh.
Created file /home/oracle/oramysql/oramysql-run-oel712csrc.sh.
Created file /home/oracle/oramysql/scripts/oramysql-oel712csrc-start-MINE.sh.
Created file /home/oracle/oramysql/scripts/oramysql-oel712csrc-stop-MINE.sh.
Created file /home/oracle/oramysql/scripts/oramysql-oel712csrc-dbvrep-MINE.sh.
Created file /home/oracle/oramysql/scripts/systemd-dbvrep-MINE_oramysql.service.
Created file /home/oracle/oramysql/scripts/upstart-dbvrep-MINE_oramysql.conf.
Created file /home/oracle/oramysql/oramysql-run-oel712ctrg.sh.
Created file /home/oracle/oramysql/scripts/oramysql-oel712ctrg-start-APPLY.sh.
Created file /home/oracle/oramysql/scripts/oramysql-oel712ctrg-stop-APPLY.sh.
Created file /home/oracle/oramysql/scripts/oramysql-oel712ctrg-dbvrep-APPLY.sh.
Created file /home/oracle/oramysql/scripts/systemd-dbvrep-APPLY_oramysql.service.
Created file /home/oracle/oramysql/scripts/upstart-dbvrep-APPLY_oramysql.conf.
Created file /home/oracle/oramysql/Nextsteps.txt.
Created file /home/oracle/oramysql/oramysql-all.sh.
======================================================================

Dbvisit Replicate wizard completed

Script /home/oracle/oramysql/oramysql-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] No
dbvrep> exit
OK-0: Completed successfully.

Appendix B: oramysql-all.sh script output

Setting up Dbvisit Replicate configuration
Configure database unitsrc...

This check fails if the DBID is not the expected one...


Ok, check passed.
Configure database database=dbvrep
mysql: [Warning] Using a password on the command line interface can be insecure.
Object grants for database unitsrc...

Object grants for database database=dbvrep
mysql: [Warning] Using a password on the command line interface can be insecure.
Setting up the configuration
Initializing......done
DDC loaded from database (0 variables).
Dbvisit Replicate version 2.8.04
Copyright (C) Dbvisit Software Limited.  All rights reserved.
DDC file /home/oracle/oramysql/config/oramysql-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: 6921701 (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 SETUP PAIR MINE AND APPLY
Applier SCN set (start=6921714, current=6921714).
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> memory_set IGNORE_APPLY_DDL_DIFFERENCES Yes
Variable IGNORE_APPLY_DDL_DIFFERENCES set to YES for process *.
dbvrep> SET PREPARE_SCHEMA_EXCEPTIONS none
Variable PREPARE_SCHEMA_EXCEPTIONS set to none for process *.
dbvrep> PROCESS SUPPLEMENTAL LOGGING SCHEMA "OE" ENABLE PRIMARY KEY
dbvrep> PROCESS SWITCH_REDOLOG
Redo log switch requested.dbvrep> PROCESS WAIT_SCN_FLIP
Waited 1 seconds until scn_to_timestamp changed.
dbvrep> #single-scn instantiation: lock all tables and schemas
dbvrep> PROCESS LOCK SCHEMAS "OE"
Locking all schemas.
...locked 11 of 11 tables from OE schema.
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 SCHEMA "OE"
Table OE.ADDRESSES instantiated at SCN 6924650
Table OE.CARD_DETAILS instantiated at SCN 6924650
Table OE.CUSTOMERS is special: has system-generated column names.
Table OE.CUSTOMERS instantiated at SCN 6924650
Column SYS_NC00017$ is special: virtual column. Excluding.
Column SYS_NC00017$ is special: hidden column. Excluding.
Column SYS_NC00017$ is special: system-generated column. Excluding.
Column SYS_NC00017$ is special: virtual column. Excluding.
Column SYS_NC00018$ is special: virtual column. Excluding.
Column SYS_NC00018$ is special: hidden column. Excluding.
Column SYS_NC00018$ is special: system-generated column. Excluding.
Column SYS_NC00018$ is special: virtual column. Excluding.
Table OE.INVENTORIES instantiated at SCN 6924650
Table OE.LOGON instantiated at SCN 6924650
Table OE.ORDERENTRY_METADATA instantiated at SCN 6924650
Table OE.ORDERS instantiated at SCN 6924650
Table OE.ORDER_ITEMS instantiated at SCN 6924650
Table OE.PRODUCT_DESCRIPTIONS instantiated at SCN 6924650
Table OE.PRODUCT_INFORMATION instantiated at SCN 6924650
Table OE.WAREHOUSES instantiated at SCN 6924650
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> EXCLUDE COLUMN OE.PRODUCT_INFORMATION.WARRANTY_PERIOD
Connecting to running mine [OE.PRODUCT_INFORMATION.WARRANTY_PERIOD] 6: Mine does not seem to be running.
Exclude Column [OE.PRODUCT_INFORMATION.WARRANTY_PERIOD] set.dbvrep> EXCLUDE COLUMN OE.ORDER_ITEMS.CONDITION
Connecting to running mine [OE.ORDER_ITEMS.CONDITION] 9: Mine does not seem to be running.
Exclude Column [OE.ORDER_ITEMS.CONDITION] set.dbvrep> EXCLUDE COLUMN OE.ORDERS.ORDER_DATE
Connecting to running mine [OE.ORDERS.ORDER_DATE] 2: Mine does not seem to be running.
Exclude Column [OE.ORDERS.ORDER_DATE] set.dbvrep> #prepare script for instantiation
dbvrep> PROCESS PREPARE_DP WRITE DDL_FILE FILE
/home/oracle/oramysql/APPLY.sql USERID SYSTEM/odb10g@unitsrc
File /home/oracle/oramysql/APPLY.sql has been written successfully.
Created DDL script /home/oracle/oramysql/APPLY.sql.
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 oramysql-all.sh script runs:

1) Create the necessary directory(ies) on the servers:
oel712ctrg: /home/oracle/oramysql

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

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

4) Copy the run script to the server(s) where the processes will run:
oel712csrc: /home/oracle/oramysql/oramysql-run-oel712csrc.sh
oel712ctrg: /home/oracle/oramysql/oramysql-run-oel712ctrg.sh

5) Ensure firewall is open for listen interfaces 0.0.0.0:7902, 0.0.0.0:7901 used by the processes.

6) Ensure firewall is open for remote access to mysql (usually port 3306) on oel712ctrg.

7) 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:

Create referenced database links (if any) before running the scripts.
/home/oracle/oramysql/APPLY.sql

8) Start the replication processes on all servers:
oel712csrc: /home/oracle/oramysql/oramysql-run-oel712csrc.sh
oel712ctrg: /home/oracle/oramysql/oramysql-run-oel712ctrg.sh

9) Start the console to monitor the progress:
/home/oracle/oramysql/start-console.sh

The above list is stored in /home/oracle/oramysql/Nextsteps.txt.
Have more questions? Submit a request

Comments