Steps overview:
1. Install SharePlex
2. Run ora_setup
3. Setup ODBC
4. Run mysql_setup
5. Prepare and activate config
6. Check Replication
Steps in detail:
1. Installing SharePlex
To simplify this demo, I am assuming that you have SharePlex installed. If you need help with install, please refer to the 9.0 Install guide.
2. Running ora_setup:
ora_setup will establish SharePlex as an Oracle user and create the required SharePlex database objects.
Example:
oracle@ip-172-31-19-129 /u01/app/oracle/product/11.2.0/db1/network/admin> ora_setup
Welcome to the Oracle SharePlex setup process for port 8729.
This process creates tables and user accounts needed to run
Oracle SharePlex replication.
Will the SharePlex install be using a BEQUEATH connection? (Entering 'n' implies a SQL*net connection) [y] : n
Please note the following:
** In response to prompts, a carriage return will choose the default
given in brackets. If there is no default, a reply must be entered.
** To exit the program while the program is waiting for input, use the
CTRL-C key sequence.
This sequences can be entered by holding down the CONTROL key and
pressing the C key.
Enter the TNS alias for which SharePlex should be installed [ora11gr2] : ora11gr2
Verifying TNS supplied ... done.
In order to create the SharePlex tables and user account, we must
connect to the database as a DBA user
Enter a DBA user for rdsorcl12 : syam
NOTE: Appending the tns alias to the password via @ sign is not needed.
Enter password for the DBA account, which will not echo :
connecting--This may take a few seconds.
validating user name and password. . . This may take a few seconds.
SharePlex objects will need to be created under a special
account. You can pick an existing user or create a new one.
Would you like to create a new SharePlex user ? [y] : y
NOTE: Appending the tns alias to the password via @ sign is not needed.
Enter username for new user [splex/splex] : syam90
Enter password for new user :
Re-enter password for new user :
Warning: This user is now being granted unlimited tablespace.
This privilege will remain in effect until it is explicitly changed.
Granting select on sys.user$ to syam90
Do you want to enable replication of tables with TDE? [n] : n
To enable replication of tables with TDE in the future, please rerun ora_setup.
Setup will now install SharePlex objects.
These are the existing tablespaces.
SYSTEM SYSAUX UNDO_T1 TEMP USERS RDSADMIN
Enter the default tablespace for use by SharePlex [USERS] :
Enter the temporary tablespace for use by SharePlex [TEMP] :
Enter the index tablespace for use by SharePlex [ ] : USERS
Creating SharePlex objects [Installation type: Fresh]. . .
Creating SharePlex Oracle-timezone-region map . . . Done.
Creating Conflict Resolution Package . . . Done.
Creating SharePlex Dataequator package . . .
Loading Compare Package from "/u01/splex/ora/90/prod/util/sp_deq_pkg.plb"...Done.
Note: Cannot install Compare Varray package because Oracle Spatial datatype is not installed.
Do you want to continue? [n] : y
Will the current setup for sid: [rdsorcl12] be used as source (including cases as source for failover or master-master setups)? [y] : y
Setup of SharePlex objects successful . . .
Changing SharePlex connection database . . .
Setup completed successfully
3. Setting up ODBC:
Setting up ODBC in SharePlex is not different than how you would set any MySQL ODBC Connection.
a) Install or verify if Driver Manager Installed(unixODBC : http://www.unixodbc.org/ or iODBC : http://www.iodbc.org)
When using unixODBC, to verify Driver manager info:
oracle@ip-172-31-19-129 ~> odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
b) Install or verify if Supported MySQl ODBC Driver(mysql-connector) is installed
Verify that the driver is installed and registered using the ODBC manager, or the myodbc-installer utility:
Syntax: /usr/local/lib> myodbc-installer -d -l
c) Setup DSN
Example:
oracle@ip-172-31-19-129 /u01/splex/ora/90/var/odbc> cat /etc/odbcinst.ini
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 = /usr/lib64/libodbcmyS.so
FileUsage = 1
oracle@ip-172-31-19-129 /u01/splex/ora/90/var/odbc> cat /etc/odbc.ini
[rdsmysql]
Driver = MySQL
Server = rdsmysql.cr4taxu9kscr.us-east-1.rds.amazonaws.com
Port = 3306
d) Verify ODBC Connection:
Syntax: isql -v my_dsn username password
oracle@ip-172-31-19-129 /u01/splex/ora/90/var/odbc> isql rdsmysql syam XXXXXXXXXX
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
NOTE:
If you have an existing DSN , and you want to use it for the SharePlex connection, copy or link the ODBC files in which that DSN is defined (odbc.ini and odbcinst.ini) to the odbc subdirectory of the SharePlex variable-data directory.
SharePlex checks its odbc folder before it tires default location. If you do not have a DSN defined but want to use one, you can create it in the template files provided in the odbc subdirectory.
4. Running mysql_setup:
mysql_setup will establish SharePlex as an MySQL user and create the required SharePlex database objects.
We are running mysql_setup on the source server as this is a RDS target and we decided not to use intermediate server, If you have a separate target, this will be executed from that target.
Example:
oracle@ip-172-31-19-129 /u01/app/oracle/product/11.2.0/db1/network/admin> mysql_setup -p8729
Welcome to SharePlex for MySQL setup.
This process creates the SharePlex tables, login and user accounts needed to run
SharePlex replication.
To create these items, we must connect to the database as an Administrator.
Enter the MySQL connection string [] : DRIVER=/usr/lib64/libmyodbc5.so;socket=/var/lib/mysql/mysql.sock;characterset-server=utf8;collation-server=utf8_general_ci;max_allowed_packet=2G;wait_timeout= 000;Server=rdsmysql.cr4taxu9kscr.us-east-1.rds.amazonaws.com
Enter the MySQL Administrator name : syam
Enter the password for the Administrator account :
Enter the replication target database name : rdsmysql
Would you like to create a new SharePlex user ? [y] : y
Enter the name of the new SharePlex user : syam90
Enter the password for the new Shareplex user :
Re-enter the password for the new Shareplex user :
Completed SharePlex for MySQL database configuration
SharePlex User name: syam90
Database name: rdsmysql
Target specification in SharePlex configuration: r.rdsmysql
5. Preparing and activating configuration:
Prepare Config:
If target owner or table name is defined in the database as anything other than UPPERCASE, be certain to:
Type the name in the correct case and enclose the name in quotation marks, for example "MySchema"."MyTable".
Example:
sp_ctrl (ec2-34-196-209-65:8729)> view config testrdsmysql
datasource:o.ora11gr2
#source tables target tables routing map
syam90.demo_src "rdsmysql"."DEMO_SRC" ec2-34-196-209-65.compute-1.amazonaws.com@r.rdsmysql
From above example :
Source SID: ora11gr2
Source Table: syam90.demo_src
Target Table: "rdsmysql"."DEMO_SRC"
ODBC DSN: rdsmysql
Target Host: ec2-34-196-209-65.compute-1.amazonaws.com ( If you have a separate target, this is where you mention it)
Activate config:
Example:
sp_ctrl (ec2-34-196-209-65:8729)> activate config testrdsmysql
6. Checking Replication:
Perform DML on syam90.demo_src table source oracle database and verify if the changes are being replicated to "rdsmysql"."DEMO_SRC" table on MySQL Database.
