Oracle to Oracle Replication issue

[Former Member]

Hi,

I am trying to setup a replication between Oracle (primary) to another Oracle (Replicate) server. The environment is as below:

Primary:

Oracle: 11g Enterprise Edition Release 11.2.0.2.0 - 64

OS: Microsoft Windows Server 2008 R2 Enterprise, x64

tnsnames.ora:

SOURCEORA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = sourceHost)(PORT = 1521))

    (CONNECT_DATA =

    (SERVER = DEDICATED)

(SID = TARGETORA)

      (SERVICE_NAME = sourceora.xxx.xxx.corp)

    )

  )

Replicate :

Oracle: 11g Enterprise Edition Release 11.2.0.2.0 - 64bit

OS: Microsoft Windows Server 2008 R2 Enterprise, x64

tnsnames.ora:

TARGETORA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = targetHost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = targetora.xxx.xxx.corp)

    )

  )

Sybase Replication Server is installed on a third server whose details are:

OS Name: Microsoft® Windows Server® 2008 Enterprise,x64

Replication Server/15.7.1/EBF 20408 ESD#1 rsebf1571

Installed Express connect for Oracle in the same path as of the Sybase folder.

Copied the tnsnames.ora of target replicate db to %SYBASE%\REP-15_5\connector\oraoci\network\admin

But when trying to create a connection using this command:

create connection to TARGETORA.TARGETORA

using profile rs_oracle_to_oracle;eco

set username to myUser

set password to "myPasswd"

go

i get the error::

ERROR #1027 DSI(107 TARGETORA.TARGETORA) - seful\cm.c(5266)

Open Client Client-Library error: Error: 22, Severity 5 -- '[ ExpressConnect for Oracle|statement ] ..\..\generic\src\ociconnsql.cpp:178 ociConnection::ProcessStmt Execute failed, OCI retrun code = 0 for SQL [commit].'.

I. 2014/08/28 09:03:47. Message from server: Message: 3127, State 0, Severity 5 -- '[ connection ][ TARGETORA ] OCIHandleFree(HTYPE=SVCCTX) ORA-03127: no new operations allowed until the active operation ends'.

I get the same error when i try to create a connection without ECO profile:

create connection to TARGETORA.TARGETORA

set error class oracle_error_class

set function string class rs_oracle_function_class

set username myUser

set password myPasswd

set batch to 'off'

set dsi_proc_as_rpc to 'on'

Can you please help?

Regards,

Lalu George

[Former Member]

Hi Mark,

Thanks for your suggestion, but i have already tried using the dsi_connector_type and make, with not much difference, the error still persists.

Regards,

Lalu George

[Former Member · Accepted solution]

Hi Lalu,

You may have to download and install Oracle Instant Client Libraries (10.2.0.5) on your Windows box where Replication Server is installed (your third server one)

This is documented in ExpressConnect for Oracle 15.7.1 SP200 Configuration Guide:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01873.1571200/doc/html/sw...

Regards

Yvan

[Former Member · Accepted solution]

Hi Yvan,

Thanks for your reply. It did indeed solve our problem (reported in the first post) .

Now we are able to create a connection, a replication definition. But after creating a subscription,

the rep server log reports :

T. 2014/09/03 14:09:25. (17): Last command to 'SOURCEORA.SOURCEORA':

T. 2014/09/03 14:09:25. (17): Output command:　 commit

I. 2014/09/03 14:09:25. Message from server: Message: 933, State 0, Severity 5 -- '[ statement ][ SOUR ] ociStmt::Execute ORA-00933: SQL command not properly ended'.

E. 2014/09/03 14:09:25. ERROR #5046 DSI(108 SOURCEORA.SOURCEORA) - \dsioqid.c(2049)

　　　　　　　　　　　　　　　 When executing the rs_get_lastcommit function in database 'SOUR.SOUR', received data server errors. See logged data server errors for more information.

I. 2014/09/03 14:09:25. The DSI thread for database 'SOURCEORA.SOURCEORA' is shutdown.

A bit of googling on ORA-00933 informed us that a command issued by the RepServer might be of wrong syntax. Is this because of the version mismatch that we have now between the primary DB and the Oracle client part of ECO?

Thanks for all the help till now

Regards,

Lalu George

[Former Member · Accepted solution]

Hi Lalu,

How do you end to create your Oracle connections (both, primary and replicate), using "profile" option?

This latest error seems to be reported from your "Primary" connection, for your source Oracle server.

You may have to create this primary connection using "with [log transfer on,] dsi_suspended" option(s) or suspend the DSI using "suspend connection to SOURCEORA.SOURCEORA" now.

For Primary "Oracle" connection, the DSI must be (usually) kept "suspended".

Regards

Yvan

[Former Member · Accepted solution]

Hi Yvan,

Our Primary connection create command is :

create connection to SOURCEORA.SOURCEORA

set error class rs_oracle_error_class

set function string class rs_oracle_function_class

set username myUser

set password "myPassword"

with log transfer on, dsi_suspended

And the Replicate connection create command is :

create connection to TARGETORA.TARGETORA

using profile rs_oracle_to_oracle;eco

set username to myUser

set password to "myPassword"

Regards,

Lalu George

[Former Member · Accepted solution]

Hi Lalu,

Your attempt to create the first connection ("SOURCEORA.SOURCEORA") is fine; because you use "with dsi_suspended" option).

Now, your latest error show you had tried to "resume" (restart) the DSI of your primary Oracle server connection.

=> keep this DSI in a "suspended" state (re-run a "suspend connection to SOURCEORA.SOURCEORA"), this will have no effect on the incoming data flow.

Regards

Yvan

[Former Member]

Hi Stephan and Mark,

Thanks for your suggestions, i shall be working on as per your advices now and post back my findings.

P.S. It was a three day holiday here in India, hence for the late reply.

Regards,

Lalu George

[Former Member]

If you google the 3127 error, you'll see there are some posts related to a mismatch between ODBC driver and OCI library version.

Since your Rep Server is installed on a different host than your Oracle, you might review what version of OCI libraries are installed there.

Here's the link to the Sybase doc.  Not sure if this is exactly the version you are using, but you might verify you are using a suggested OCI version.

ExpressConnect for Oracle 15.7.1 Installation and Configuration Guide

SyBooks Online

Regards,
Stephen