Testing Replication

Connect to the primary Oracle instance as a regular user, rather than as the maintenance user.

At the primary Oracle database, create a table named ptab1 to replicate:

CREATE TABLE ptab1
(idno NUMBER PRIMARY KEY,
name VARCHAR2(20));

Grant permission to the Replication Agent to select data from the ptab1 table for the direct_load subscription.

grant all on ptab1 to public;

Connect to Replication Agent through isql, and mark the ptab1 table for replication:

pdb_setreptable ptab1, mark
go

Add rows to the ptab1 table:

insert into ptab1 values (1, 'Burt Gringlesby');
insert into ptab1 values (2, 'Douglas Wong');
insert into ptab1 values (3, 'Tom Servo');
insert into ptab1 values (4, 'Innes del Castillo');
insert into ptab1 values (5, 'Akiko Yokomoto');
commit;

In Replication Agent, create a replication definition against the primary Oracle database:

rs_create_repdef ptab1
go
Table/Procedure Name     RepDef Name    Status
------------------------ -------------- --------
<OracleTableOwner>.PTAB1 "<repdefname>" Created
(1 row affected)

Record the name of the replication definition for use later.

At the replicate SAP HANA database instance, create a table named PTAB1:

CREATE TABLE <tableowner>.PTAB1 
(IDNO INT PRIMARY KEY, 
NAME VARCHAR(20));

Note: SAP HANA database is not case-sensitive and uses uppercase characters if lowercase characters are provided.

If an owner is not specified with the table name, the owner of the table—<tableowner>—is the user who is logged in when the table is created.

Grant permissions to any new or existing object to be replicated in the replicate database so that the Replication Server maintenance user can update this table:

grant all privileges on <tableowner>.PTAB1 to public

Log in to Replication Server:

isql –Usa –Psa_pass –SSAMPLE_RS

If you have not set up the sample Replication Server instance, enter your Replication Server instance name in place of SAMPLE_RS.

Alter the replication definition that you created earlier to point to the correct replicate table:

alter replication definition <repdefname>
with replicate table named <tableowner>.ptab1
go

Note: SAP HANA database is not case-sensitive and uses uppercase characters if lowercase characters are provided.

Create a subscription against the replicate connection to materialize the PTAB1 table on the SAP HANA database.

Create the ptab1_sub subscription with direct_load option for the <repdefname> replication definition. Replication Server logs in to Replication Agent for Oracle, and Replication Agent issues a SELECT command to select data from the Oracle database, enter:

create subscription ptab1_sub
for <repdefname>
with replicate at rds.rdb
without holdlock
direct_load
user puser password ppwd
go

where:

rds.rdb is the replicate SAP HANA database connection created earlier.
puser is the user who connects to Replication Agent for Oracle.
ppwd is the password of the puser. You must enter a password.

Note: The interfaces file used by the replicate Replication Server must contain an entry for the Replication Agent for Oracle that is used to select data from the Oracle database.

To create the subscriptions at the replicate Replication Server using the no materilization method, use the create subscription command with the without materialization clause.

For information about using the create subscription command in direct_load materialization, see Replication Server Reference Manual.

Check the subscription status to ensure there are no errors:

check subscription ptab1_sub
for <repdefname>
with replicate at rds.rdb
go

If there are errors, see Troubleshooting Subscription Problems.

Connect to the Oracle database as a non-maintenance user with the ability to insert rows into the table:

insert into ptab1 values (10, 'Michel DeFrance');
insert into ptab1 values (11, 'Dirk Stringer');
commit;

On the Replication Server, check the status of the subscription created with the direct_load option by using the check subscription command. When the subscription is in the VALID state, materialization is complete:

check subscription ptab1_sub
for <repdefname>
with replicate at rds.rdb
go
Subscription ptab1_sub is VALID at the replicate.

After the status is VALID at the primary and replicate database, on the replicate database, verify that the data is replicated to the replicate PTAB1 table:

SELECT * FROM <tableowner>.PTAB1