Hi,
I am getting the below error after adding Oracle subscriber.
"Agent message code 20029. The required parameter '-SubscriberDB' is missing. "
(Snapshot agent able to generate a snapshot under snapshot folder successfully. But only Distributor agent giving this error)
Could you please suggest me.
Details:
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 name>_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 (from subscriber property): SQL Server Authentication(user should have access on Oracle DB)
TNSPing : succesfull. (Tnsnames.ora file validated)
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 ).
