"Invalid Operation" when adding Oracle subscriber to SQL Server 2016 replication publication

Question

Hi,

I'm trying to set up transactional replication from SQL Server 2016 to Oracle 12c.  (I realize that heterogeneous replication is deprecated but this should be for a limited time period).  I am following the general guidance here: https://msdn.microsoft.com/en-us/library/aa337389.aspx  and the Oracle-specific info here: https://msdn.microsoft.com/en-us/library/ms151864.aspx.  I am finding that when I try to add the non-SQL Server subscriber, I get this error:

Invalid operation. The connection is closed. (System.Data)

------------------------------
Program Location:

   at System.Data.SqlClient.SqlConnection.GetOpenConnection()
   at System.Data.SqlClient.SqlConnection.get_ServerVersion()
   at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.GetServerVersion()
   at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.get_IsServer130OrLater()
   at Microsoft.SqlServer.Management.UI.SubWizardSubscriber..ctor(String name, SubscriberType subscriberType, Boolean isSelected, Boolean isRegistered, ReplicationSqlConnection sqlConn, WizardInputs inputs)
   at Microsoft.SqlServer.Management.UI.PageChooseSubscribers.AddNonSqlSubscriber_OnClick(Object sender, EventArgs e)

I have tried to follow the Oracle/heterogenous recommendations, including

1) Installing recent 64-bit Oracle client and verifying basic connectivity to database

2) Ensuring the the distribution account has read access to the Oracle OLEDB provider directory

3) Setting public snapshot format to Character

4) Setting publication's subscription options to Allow non-SQL Server subscribers

5) Configuring my source SQL Server as distributor

Any other suggestions?  Any known guides or walk-throughs for this scenario?

thanks!

Martin

Answer 1

Hi Martin,

We are currently looking into this issue and will give you an update as soon as possible.

Thank you for your understanding and support.

Regards,
Lin

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

"Invalid Operation" when adding Oracle subscriber to SQL Server 2016(13.0.5102.14) replication publication"

Hello,

I am also facing the same issue 

I have installed Oracle 19.0.0 full client on Distributor server.

Through SQLPlus I can able to connect to Oracle Database successfully.

Also through ODBC 64 bit DSN test connection done successfully.

I have given Read & Exec permission to the SQL Server Distributor Agent account.

TNSPing command also verified successfully.

But when I try to add Data Source Name in "Add non SQL Server Subscriber under "New Subscriber Wizard" it is giving error like "Invalid operation. The connection is closed."

Please suggest.

Thanks. 
Keeran kumar

Answer 3

Hi,


Looking at my old notes, it seems one key step (undocumented as far as I know) was:


From cmd prompt, connected using dedicated administrator connection  

sqlcmd -S <SQLserverName>-E –A

 

And Executed

use msdb

go

exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'

go

exec sys.sp_MSrepl_MSSQL_ORA_datatypemappings @source_dbms = N'MSSQLSERVER', @destination_dbms = N'ORACLE', @destination_version = '12'


My notes show that I then created the subscription using  sp_addsubscription as follows, but I don't remember anymore if that was actually necessary or if the GUI would have worked just as well


first "use" the database that is enabled for publication, then

exec sp_addsubscription @publication = N'<subscription_name>', @subscriber = N'<oracle_TNS_NAME>, @destination_db = N'(default destination)', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 3;



Hope this helps!


Martin

Answer 4

Hi,

After following above steps, I am getting below error.

 "Agent message code 20029. The required parameter '-SubscriberDB' is missing. "

Could you please suggest me.

SQL server 2016 sp2 : Publisher & Distributor

Oracle 12102 : Oracle subscriber. I have installed full client of Oracle 12c1.

For adding Oracle subscriber:

From sqlcmd I ran,

>exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'

>exec sys.sp_MSrepl_MSSQL_ORA_datatypemappings @source_dbms = N'MSSQLSERVER', @destination_dbms = N'ORACLE', @destination_version = '12'

From SSMS

use [DBname]

exec sp_addsubscription @publication = N'DB_To_oracle_<TNS>_Trans_Publication', @subscriber = N'ORCLDC', @destination_db = N'(default destination)', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 3;
go

After that by default, replication created new Linked server with provided @subscriber name and it is using the provider "Microsoft OLE DB Provider for SQL Server"

After that added subscriber connection: SQL Server Authentication(user should have access on Oracle DB)

TNSPing : succesfull.

Provided Read & exec permissions to Distributor agent service account on Oracle directory.

Environment variable: Added Oracle home & bin path, TNS_admin path.

Publication Access list: Added agent service account & distributor_admin

Snapshot folder: service account has access to it.

Snapshot format: Character 

Allow non-SQL server subscribers : True

ODBC Data source Admin 64bit : System DSN configuration : Test successful 

If I configure new Linked server (with provider " Oracle Provider for OLE DB" and Remote login & password): It is successfully connecting to Oracle DB (on Linked server ).

Answer 5

Hello,

Sorry, I do not have an idea about that error message.  It's been years since I set this up.

Good luck!

Martin