DM207 Procedures for Enabling Replication Between ASE and an Oracle DB

1
DM207Procedures for Enabling Replication Between
ASE and an Oracle DB
Wes Stephens Consultant ESD stephenw_at_sybase.com
2
Topics Covered

• Main Topics
• Project background and architecture
• Implementing replication from Sybase ASE 11.9.2
  to Oracle 8i
• Implementing replication from Oracle 8i to Sybase
  ASE 11.9.2
• Acknowledgments
• Q A

3
Project Background

• Project Description
• The Naval Air Systems Command (NAVAIR) of the
  U.S. Navy has contracted Sybase Professional
  Services (SPS) to design and implement a
  replication system which would transmit data from
  an ASE database to an Oracle database and vice
  versa.
• SPS has delivered the architecture and
  implemented the first phase.

4
Project Background
Project Description (continue) The clients
system is Naval Aviation Logistics Command
Management Information System (NALCOMIS)
Optimized Organizational Maintenance Activity
(OOMA). The overall purpose of NALCOMIS OOMA is
to report and track naval airplane maintenance.
5
Replication Architecture Sybase Solution
6
Replication Architecture Top Tier Level at
NAVAIR
7
Replication Architecture Sybase to Oracle
8
Replication Architecture Oracle to Sybase
Host nt_host
ASE_POC
syb_db (PDB)
Host aix_node01
SPOC_RS_RSM
SPOC_RS (PRS, RRS)
Host aix_node02
RAO_POC
(Oracle Staging Database)
ora_db
9
Implementing Replication from Sybase to Oracle

• Procedures
• Preparation of ASE (Adaptive Server Enterprise)
• Preparation of Rep Server
• Preparation of Oracle
• Installation and configuration of DCO (Direct
  Connect for Oracle)
• Establish Replication
• Test Replication

10
Implementing Replication from Sybase to Oracle

• Preparation of ASE Primary Database
• Unload the ASE software and build the primary
  database server ASE_POC.
• Create the primary database syb_db.
• Create the primary table in the primary database.
  For example
• CREATE TABLE test_syb_ora (
• col1 int not null primary key,
• col2 char(10) not null,
• col3 datetime not null)
• go

11
Implementing Replication from Sybase to Oracle

• Preparation of ASE Primary Database
• Grant permissions on the table to the end-users.
  For example
• GRANT ALL ON test_syb_ora TO public
• go

12
Implementing Replication from Sybase to Oracle

• Preparation of ASE CIS Database
• Unload the ASE software and build the database
  server SPOC_DS.

13
Implementing Replication from Sybase to Oracle

• Preparation of ASE CIS Database
• Create the CIS database cis_db.
• As a rule of thumb, the data devices for this db
  can be small (i.e., 16-32 MB) because the proxy
  tables will not store any data. Each proxy table
  will have only one extent of space allocation.
• The log device should be larger to allow the
  transactions to be passed to DCO. For example, 96
  MB to 128 MB.

14
Implementing Replication from Sybase to Oracle

• Preparation of Rep Server
• Unload the Rep Server software.
• Create the Rep Server SPOC_RS via running
  rs_init.
• Setup the primary database syb_db within Rep
  Server.
• Setup the replicate database cis_db within Rep
  Server.

15
Implementing Replication from Sybase to Oracle

• Preparation of Oracle
• Create a maintenance user (e.g., sybase) and
  grant it the system role DBA.
• First, you must login with a user ID that already
  has the DBA role such as the system user
  (equivalent to sa in ASE).
• CREATE USER sybase
• IDENTIFIED BY TW2000 -- TW2000 is the password
• DEFAULT TABLESPACE ora_sybase
• GRANT DBA TO sybase

16
Implementing Replication from Sybase to Oracle

• Preparation of Oracle
• Create the Oracle table (e.g., test_syb_ora).
• CREATE TABLE test_syb_ora (
• col1 number(5) not null,
• col2 char(10) not null,
• col3 date not null)

17
Implementing Replication from Sybase to Oracle

• Preparation of Oracle
• The owner of the table should be the maintenance
  user.
• If not, either create a public synonym or qualify
  the table with the owner name.
• CREATE PUBLIC SYNONYM test_syb_ora FOR
  owner.test_syb_ora
• INSERT INTO owner.test_syb_ora
• VALUES (100, TW2000, 01-Aug-00)

18
Implementing Replication from Sybase to Oracle

• Installation and Configuration of DCO
• A UNIX user id (e.g., sybase) is required for the
  Sybase products.
• Unload DCO and Open Client from the distribution
  CD with either sybload or sybsetup.

19
Implementing Replication from Sybase to Oracle

• Installation and Configuration of DCO
• Change to the SYBASE variable and extract the
  DCO and the Open Client tar files.
• cd SYBASE
• tar -xvpf dco.tar
• tar -xvpf oc.tar

20
Implementing Replication from Sybase to Oracle

• Installation and Configuration of DCO
• Research if there are any EBFs for DCO. For DCO
  version 11.1.1 on AIX 4.3, there is the EBF 8437.
• cd SYBASE
• tar -xvpf EBF8437.tar
• Copy the bin executable files and the link files
  to the DCO bin and link directories,
  respectively.

21
Implementing Replication from Sybase to Oracle

• Installation and Configuration of DCO
• Identify the Oracle home directory (ORACLE_HOME)
  and the Oracle SID (system ID) (ORACLE_SID).
• Add the DCO server to the interfaces file on the
  host where the Oracle server resides.
• dsedit
• or
• vi interfaces

22
Implementing Replication from Sybase to Oracle

• Installation and Configuration of DCO
• Modify the DCO configuration file.
• Set the connect_string variable to the value of
  the Oracle network connection parameter, which is
  in the file ORACLE_HOME/network/admin/tnsnames.or
  a
• It will probably be _at_host name_oracle sid.
• connect_string _at_aix_node02_ora_db

23
Implementing Replication from Sybase to Oracle

• Installation and Configuration of DCO
• Start DCO.
• nohup dcparent -SDCO Server Name
• e.g. nohup dcparent -SDCO_POC
• Log into the DCO server and test a select
  command.
• UNIX Promptgt isql -SDCO_POC -Usybase
• Password
• 1gt SELECT from test_syb_ora
• 2gt go

24
Implementing Replication from Sybase to Oracle

• Installation and Configuration of DCO
• To shutdown DCO, log into it as the maintenance
  user and execute exec sp_shutdown.
• UNIX Promptgt isql -SDCO_POC -Usybase
• Password
• 1gt exec sp_shutdown
• 2gt go

25
Implementing Replication from Sybase to Oracle

• Establish Replication
• Add the DCO server to the interfaces file on the
  host where Rep Server and ASE reside.
• dsedit
• or
• vi interfaces
• Add the DCO server as a remote server in ASE.
• sp_addserver DCO_POC, access_server, DCO_POC
• go

26
Implementing Replication from Sybase to Oracle

• Establish Replication
• Add logins for the external DCO and CIS system
  users.
• sp_addexternlogin DCO Server, ASE login,
  external user, external password
• e.g.
• sp_addexternlogin DCO_POC, sa, sybase, TW2000
• go
• sp_addexternlogin DCO_POC, cis_db_maint, sybase,
  TW2000
• go

27
Implementing Replication from Sybase to Oracle

• Establish Replication
• Enable CIS within ASE by setting the enable cis
  configuration parameter to 1 and rebooting ASE.
• sp_configure enable cis, 1
• go
• sp_configure cis rpc handling, 1
• go
• sp_shutdown SYB_BACKUP with wait
• go
• shutdown with wait
• go

28
Implementing Replication from Sybase to Oracle

• Establish Replication
• Map the proxy table to the Oracle table.
• sp_addobjectdef test_syb_ora, DCO_POC..sybase.tes
  t_syb_ora, table
• go

29
Implementing Replication from Sybase to Oracle

• Establish Replication
• Create the proxy table in the CIS db and grant
  permissions to the replication maintenance user.
• create existing table test_syb_ora (
• col1 int not null,
• col2 char(10) not null,
• col3 datetime not null)
• go
• grant all on test_syb_ora to cis_db_maint
• go

30
Implementing Replication from Sybase to Oracle

• Establish Replication
• Create the replication definition. For example
• create replication definition stab_repdef
• with primary at ASE_POC.syb_db
• with all tables named test_syb_ora
• (col1 int,
• col2 char(10),
• col3 datetime)
• Primary Key (col1)
• Replicate Minimal Columns
• go

31
Implementing Replication from Sybase to Oracle

• Establish Replication
• Mark the primary table for replication.
• sp_setreptable test_syb_ora, true
• go

32
Implementing Replication from Sybase to Oracle

• Establish Replication
• Define the subscription and check it
• define subscription sub_test_syb_ora
• for repdef_test_syb_ora
• with replicate at SPOC_DS.cis_db
• go
• check subscription sub_test_syb_ora
• for repdef_test_syb_ora
• with replicate at SPOC_DS.cis_db
• go

33
Implementing Replication from Sybase to Oracle

• Establish Replication
• BCP the data out in character format.
• bcp syb_db..test_syb_ora out
  test_syb_ora.bcp.orig -c -t!_at_ -r\\n
  -SASE_POC -Usa
• Convert the format of the datetime values.
• orig. format MON DD YYYY HHMMSSSSS
• new format MON DD YYYY HHMMSS
• Store the output of the conversion to a new flat
  file.

34
Implementing Replication from Sybase to Oracle

• Establish Replication
• Load the new flat file to the Oracle table using
  Oracles SQLLoader utility.
• sqlldr username/password CONTROLCTRLDIR/test
  _syb_ora.ctrl DIRECTTRUE LOGLOGDIR/test_syb_or
  a.ldlog BADLOGDIR/test_syb_ora.ldbad
  DISCARDLOGDIR/test_syb_ora.lddis

35
Implementing Replication from Sybase to Oracle

• Establish Replication
• Activate the subscription and check it
• activate subscription sub_test_syb_ora
• for repdef_test_syb_ora
• with replicate at SPOC_DS.cis_db
• go
• check subscription sub_test_syb_ora
• for repdef_test_syb_ora
• with replicate at SPOC_DS.cis_db
• go

36
Implementing Replication from Sybase to Oracle

• Establish Replication
• Validate the subscription and check it
• validate subscription sub_test_syb_ora
• for repdef_test_syb_ora
• with replicate at SPOC_DS.cis_db
• go
• check subscription sub_test_syb_ora
• for repdef_test_syb_ora
• with replicate at SPOC_DS.cis_db
• go

37
Implementing Replication from Sybase to Oracle

• Test Replication
• Execute transactions against the primary table.
• INSERT INTO test_syb_ora (col1, col2, col3)
• VALUES (1, ABC123, 4/7/2000)
• go
• UPDATE test_syb_ora
• SET col2 DEF456,
• col3 4/14/2000
• go
• DELETE test_syb_ora
• go

38
Implementing Replication from Sybase to Oracle

• Test Replication
• After each transaction, query the primary, proxy,
  and Oracle tables to view the results.
• For the Primary DB, CIS DB, and DCO Server,
  execute
• SELECT FROM test_syb_ora
• go
• In Oracle
• SELECT FROM test_syb_ora

39
Implementing Replication from Oracle to Sybase

• Procedures
• Preparation of ASE
• Preparation of Rep Server
• Preparation of Oracle
• Installation and configuration of RAO
• Establish Replication
• Test Replication

40
Implementing Replication from Oracle to Sybase

• Preparation of ASE
• Create the replicate Sybase table.
• CREATE TABLE test_ora_syb (
• col4 int not null,
• col5 varchar(10) not null,
• col6 date not null)
• go
• Grant select, insert, update, and delete
  permissions on the table to public.
• GRANT SELECT, INSERT, UPDATE, DELETE ON
  test_ora_syb TO public
• go

41
Implementing Replication from Oracle to Sybase

• Preparation of Rep Server
• Create a connection for the Oracle database.
• The username and the password for the connection
  must be specified in the RAO configuration file
  for the SQL_user and SQL_pw parameters.
• The username must have a login to the primary
  Oracle database and must have been granted the
  DBA system role.

42
Implementing Replication from Oracle to Sybase

• Preparation of Rep Server
• For example
• CREATE CONNECTION Oracle.ora_db
• set error class rs_sqlserver_error_class
• set function string class rs_sqlserver_function_cl
  ass
• set username sybase
• set password TW2000
• with log transfer on, dsi_suspended

43
Implementing Replication from Oracle to Sybase

• Preparation of Oracle
• Create an Oracle tablespace for the virtual log
  because RAO stores the virtual log tables and
  system tables in a tablespace (equivalent to a
  database in ASE).
• CREATE TABLESPACE ora_sybase
• DATAFILE file_definition , file_definition
• options

44
Implementing Replication from Oracle to Sybase

• Preparation of Oracle
• The DCO maintenance user that was created in the
  previous section will also be used for this
  replication.
• The syntax is the following
• CREATE USER sybase
• IDENTIFIED BY TW2000 -- TW2000 is the password
• DEFAULT TABLESPACE ora_sybase
• GRANT DBA TO sybase

45
Implementing Replication from Oracle to Sybase

• Preparation of Oracle
• Since the DCO/RAO maintenance user has the DBA
  system role, it implicitly has select privilege
  on the v_lock, v_transaction, and v_session
  system views.
• Select privilege on these system views is
  required in order for replication from Oracle to
  operate.

46
Implementing Replication from Oracle to Sybase

• Preparation of Oracle
• Create an end user ID and grant it the system
  roles connect and resource. For example,
• The syntax is the following
• CREATE USER stephenw
• IDENTIFIED BY orlando
• DEFAULT TABLESPACE default_tblsp
• GRANT connect, resource TO stephenw

47
Implementing Replication from Oracle to Sybase

• Preparation of Oracle
• Create the primary table test_ora_syb on the
  ora_sybase tablespace. It is highly recommended
  that the owner is the maintenance user.
• CREATE TABLE test_ora_syb (
• col4 number(5) not null,
• col5 varchar(10) not null,
• col6 date not null)
• Create a public synonym for the primary table.
• CREATE PUBLIC SYNONYM test_ora_syb FOR
  sybase.test_ora_syb

48
Implementing Replication from Oracle to Sybase

• Preparation of Oracle
• Grant select, insert, update, and delete
  permissions on the primary table to the end user.
  
• GRANT ALL ON sybase.test_ora_syb TO stephenw
• Grant select, insert, update, and delete
  permissions on the synonym to the end user.
• GRANT ALL ON test_ora_syb TO stephenw

49
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO Unload
  Software
• Unload the RAO and Open Client software from the
  distribution CD. (The previous Open Client tar
  file can be reused.)
• Extract the rao.tar file.
• cd SYBASE
• tar -xvpf rao.tar
• The tar operation creates the directories rao
  and rao/install under SYBASE.

50
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO Unload
  Software
• Now, un-tar the tar file RAO_1003.tar.
• cd SYBASE/rao
• tar -xvpf install/RAO_1003.tar
• This tar operation creates the directories bin,
  charsets, doc, locales, and vtx under SYBASE/rao.

51
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO Unload
  Software
• Research if there are any EBFs for RAO. For RAO
  version 10.0.3 on AIX 4.3, there is the EBF
  I0213. Apply the EBF
• cd SYBASE/rao/tar
• tar -xvpf EBFI0213.tar
• cd SYBASE/rao
• chmod w bin/rao bin/rao.diag bin/rao_backup
  bin/rao_dump bin/rao_restore bin/rao_setreplicate
  bin/rao_setup_db

52
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO Unload
  Software
• Apply the EBF. (continue)
• cd SYBASE/rao/tar
• cp -p raop1_99.tar ../rao
• tar -xvpf raop1_99.tar
• chmod -w bin/rao bin/rao.diag bin/rao_backup
  bin/rao_dump bin/rao_restore bin/rao_setreplicate
  bin/rao_setup_db
• Unload Open Client.
• cd SYBASE/rao
• tar -xvpf oc.tar

53
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO Access
  Server
• Add a port number for Access Server (AS) to the
  /etc/services file. The default port for AS is
  1958.
• vtxnet 1958/tcp Access Server, Sybase RAO
• Start the listening daemon.
• cd SYBASE/rao/vtx
• nohup vtxnetd -p1958

54
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO Access
  Server
• Create the Access Server.
• Make a backup copy of the file vtxlink8.ora.
• cd SYBASE/rao/vtx
• cp -p vtxlink8.ora vtxlink.org
• Edit the vtxlink8.ora file.
• vi vtxlink8.ora
• Change the value of the ORACLE_HOME variable to
  the actual Oracle home path.

55
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO Access
  Server
• Create the Access Server. (continue)
• Remove the libraries dcelibc_r and dcepthreads
  from the cclibs variable.
• Now, execute vtxlink8.ora to create the Access
  Server.
• vtxlink8.ora
• Check the Access Server status.
• vtxping host name
• e.g. vtxping aix_node02

56
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO RAO Main
  Module
• Add an entry for RAO to the interfaces file.
• dsedit
• or
• vi interfaces
• Create the RAO configuration file.
• Copy the sample.cfg file that is included with
  the software to rao.cfg.
• Change the values to match the RAO setup
  worksheet.

57
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO RAO Main
  Module
• Initialize Oracle for replication using the
  rao_setup_db utility.
• rao_setup_db -ON -Crao.cfg
• Start RAO.
• Warning Do not start more than one RAO process
  on a single host.
• nohup rao -Crao.cfg -SRAO_POC -ISYBASE/rao/interf
  aces

58
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO RAO Main
  Module
• To shutdown the RAO server, log into the RAO
  server as the maintenance user sa and execute the
  command shutdown.
• UNIX Promptgt isql -SRAO_POC -Usa
• Password
• 1gt shutdown
• 2gt go

59
Implementing Replication from Oracle to Sybase

• Installation and Configuration of RAO RAO Main
  Module
• After the RAO server has been shutdown, the
  Access Server can be shutdown.
• cd SYBASE/vtx
• vtxkill -p1958 -- syntax vtxkill
  -pport_number
• or
• vtxkill aix_node02 -- syntax vtxkill host name

60
Implementing Replication from Oracle to Sybase

• Establish Replication
• Create the replication definition.
• create replication definition repdef_test_ora_syb
• with primary at Oracle.ora_db
• with all tables named test_ora_syb
• (col4 int,
• col5 char(10),
• col6 datetime)
• Primary Key (col4)
• Replicate Minimal Columns
• go

61
Implementing Replication from Oracle to Sybase

• Establish Replication
• Define the subscription and check it
• define subscription sub_test_ora_syb
• for repdef_test_ora_syb
• with replicate at ASE_POC.syb_db
• go
• check subscription sub_test_ora_syb
• for repdef_test_ora_syb
• with replicate at ASE_POC.syb_db
• go

62
Implementing Replication from Oracle to Sybase

• Establish Replication
• Prevent transactional activity on the primary
  Oracle table.
• REVOKE ALL ON test_ora_syb FROM stephenw
• Execute the rao_dump utility for dumping the data
  in the Oracle table to a flat file.
• rao_dump -Crao.cfg -Dtest_ora_syb.bcp

63
Implementing Replication from Oracle to Sybase

• Establish Replication
• Execute the bcp utility to load the data file
  into the replicate ASE table.
• bcp syb_db..test_ora_syb in test_ora_syb.bcp -c
  -SASE_POC -Usa
• Only if the replicate database is ASE.
• For Oracle to Oracle replication, use the Oracle
  utilities exp and imp to export and import data
  between Oracle databases.

64
Implementing Replication from Oracle to Sybase

• Establish Replication
• Initialize the primary Oracle table for
  replication using rao_setreplicate.
• UNIXgt rao_setreplicate test_ora_syb -ON -Crao.cfg

65
Implementing Replication from Oracle to Sybase

• Establish Replication
• Activate the subscription and check it
• activate subscription sub_test_ora_syb
• for repdef_test_ora_syb
• with replicate at ASE_POC.syb_db
• go
• check subscription sub_test_ora_syb
• for repdef_test_ora_syb
• with replicate at ASE_POC.syb_db
• go

66
Implementing Replication from Oracle to Sybase

• Establish Replication
• Validate the subscription and check it
• validate subscription sub_test_ora_syb
• for repdef_test_ora_syb
• with replicate at ASE_POC.syb_db
• go
• check subscription sub_test_ora_syb
• for repdef_test_ora_syb
• with replicate at ASE_POC.syb_db
• go

67
Implementing Replication from Oracle to Sybase

• Establish Replication
• Allow transactional activity on the primary
  Oracle table.
• GRANT ALL ON test_ora_syb TO stephenw

68
Implementing Replication from Sybase to Oracle

• Test Replication
• Execute transactions against the primary table.
• INSERT INTO test_ora_syb (col4, col5, col6)
• VALUES (1, XYZ987, 4/30/2000)
• COMMIT
• UPDATE test_ora_syb
• SET col5 UVW654,
• col6 5/31/2000
• COMMIT
• DELETE test_ora_syb
• COMMIT

69
Implementing Replication from Sybase to Oracle

• Test Replication
• After each transaction, query the primary and
  replicate tables to view the results.
• In the primary Oracle DB, execute
• SELECT FROM test_ora_syb
• In the replicate ASE, execute
• SELECT FROM test_ora_syb
• go

70
Acknowledgments

• Sybase Professional Services (SPS)
• Dan Bucchioni, Practice Manager
• Marc Wangel, Principal Consultant
• Christopher Voohrees, Sales Rep.
• Ed Custer, Sales Principal Consultant
• Thuy Truong, Sybase Technical Support
• Wes Stephens, Consultant

71
Acknowledgments

• U.S. Navy (NAVAIR)
• Lou Cain
• Ellen Marra
• Doug Jahn
• John Mishler

72
Q A