310 likes | 475 Views
REP701 : Connecting the Enterprise: Heterogeneous Replication for Various Database Sources. Nanci Reinhart Senior Consultant, Sybase reinhart@sybase.com August 15-19, 2004. What Will I Need?. Sybase Products Needed Adaptive Server (ASE) Replication Server Replication Agent
E N D
REP701 : Connecting the Enterprise: Heterogeneous Replication for Various Database Sources Nanci Reinhart Senior Consultant, Sybase reinhart@sybase.com August 15-19, 2004
What Will I Need? • Sybase Products Needed • Adaptive Server (ASE) • Replication Server • Replication Agent • Direct Connect • jConnect AKA: Replication Server with Options
in out Oracle Prim Direct Connect ASE Rep Agent Rep Server Rep Server Rep Agent out in ASE Direct Connect Oracle Rep
in out Oracle Prim Direct Connect ASE Rep Agent Rep Server Rep Server ASE
Installation • JDBC Driver for Oracle • ASE Server • Replication Server • Direct Connect for Oracle • Replication Agent for Oracle
Direct Connect Install (Oracle Option) • Create tnsnames.ora for Oracle connection string • Variable TNS_ADMIN – specify location • Default 12.0 - $SYBASE/DCO-12_0 • Default 12.5 - $SYBASE/DCO-12_5/network/admin
Direct Connect Install (Oracle Option) • Customize the serv_name.cfg file • Version 12.0 – connect string: • @user.dbname.prod • Version 12.5 – connect string: • User.dbname.prod Test connection with some sql!
Replication Agent Install Path/Classpath variable must include: vs 12.0 /path_to_jdbc/lib/classes111.zip vs 12.5 /path_to_jdbc/lib/classes12.zip Create the Replication Agent with ra_admin –c name –t oracle –p port_num Start the Replication Agent % administrator Configure (from worksheets)
Replication Agent - JDBC version 12.5 Replication Agent for Oracle requires JDBC 2.0 Compliant Driver versions of Replication Agent of 12.1 and older used JDBC 1.0 Compliant Driver
Heterogeneous Database Support HDS: Scripts in $SYBASE/REP-12_x/scripts
HDS Run on Oracle Server (Using Direct Connect or SQL Plus as the maintenance user) hds_oracle_setup_for_replicate.sql Change the use rssd_name line on the following scripts and run on ASE hds_oracle_udds.sql hds_oracle_funcstrings.sql hds_clt_ase_to_oracle.sql
in out Oracle Prim Direct Connect ASE Rep Agent Rep Server Rep Server Rep Agent out in ASE Direct Connect Oracle Rep
Connections In the Replication Server Create user that Direct Connect will use User must have permissions on the Oracle Database Grant connect source to this user Create the connection between Replication Server and Replication Agent Create the connection between Replication Server and Direct Connect
Routes Create route between Replication Servers
What About A Transaction Log? DB2 - Read from transaction Log Directly Informix, MS SQL, and Oracle: Create objects in the database to support (mimic) transaction log activity
What About A Transaction Log? Sybase log behavior imitated by Oracle Tables Create tables and objects using the Rep Agent: pdb_xlog create Verify: pdb_xlog (will echo back items created) Mark table for replication: pdb_setreptable tableName, mark
Can I See These Scripts? Scripts located in: $SYBASE/rax-12_x/ra_name/scripts
Replication Definitions & Subscriptions Use Standard Replication Definitions Use Standard Subscriptions Take it for a test drive!
Gotchas: Wide columns Version 12.5 and higher – no problem! Good up to 32K Version 12.0 – work around: Put an extra table in the mix…… LOBS and CLOBS
Gotchas: Is This Normal? Replication Agent and/or DSI threads are down: It is normal for the 2 DSI threads to the Rep Agent to be down or suspended The ones to Direct Connect should be up!
Gotchas: Down Replication Agents Replication Agent for Oracle is down: Run the “resume connection” command in the Rep Server Run the “resume” command in the Rep Agent Replication Agent for the RSSD is down: In the RSSD (ASE) Run sp_start_rep_agent
Gotchas: Didn’t I Drop That???? The case of the reappearing Replication Definition and/or subscription: During testing ONLY, in Rep Agent: ra_config use_rssd, false
What Is It Doing? (Direct Connect) Direct Connect Trace Flags: In the config file: network tracing = 1 traces = 1,2,3,4,5,6,10 Messages will be in DCO.log
What Is It Doing? (Replication Agent) Replication Agent Trace Use isql and execute: trace all, true Output will be in $SYBASE/rax-12_x/name/log directory
What Is It Doing? (Replication Server) trace “on”, dsi, dsi_buf_dump
Replication: Diagnostic Server Copy the Replication Server runfile Change the ../../repserver line to: ../../repserver.diag Shutdown and restart the Replication Server View execution commands by: trace ‘on’, “exec”, exec_trace_commands Output in Rep Server log file
What’s in the Queue? sysadmin sqt_dump_queue, 103, 1, 0 Or sysadmin dump_file, “/tmp/myDumpFile” sysadmin dump_queue, 103, 0, -1, -2, -2 sysadmin dump_file
How Do I Get Rid of It? Empty it out! sysadmin purge_all_open, 103, 1
Commands “Stuck” In Rep Agent Find the shadow table in the Oracle database and delete the entries. In the Replication Agent run: quiesce ra_locator zero
??????????????????????????? Questions?
SYBASE Replication with Options Making connections and communications easier.