Data Instantiation for MySQL (step-by-step guide)
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 <REPLICATION_NAME>-all.sh or <REPLICATION_NAME>-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 <REPLICATION_NAME>-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 <SCN_number> USER/PASSWORD@DATABASE --xc <SCHEMA.TABLE.COLUMN_TO_EXCLUDE> --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 <REPLICATION_NAME>-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
- Run setup wizard (see Appendix A for transcript);
Change the
-setup.dbvrepfile.#!/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.shscript (_see Appendix B* for transcript);
4. Copy replication files to the target system:scp -R /home/oracle/oramysql oel712ctrg:/home/oracle
5. Executing
APPLY.sqlon the target (oel712ctrg):mysql -u root -p < /home/oracle/oramysql/APPLY.sql
6. Exporting the
OESwingbench schema atSCN = 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;" doneExamine 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.