Hi,
I'd appreciate any help i can get here. I'm working with an MS SQL Server 2005 SP2 db on a Windows 2003 OS. I set up replication from an Oracle 10g instance a few months ago, and everything was working until i installed SP2 for SQL Server.
I get the following error:
2007-06-06 16:06:22.488 Status: 4096, code: 20024, text: 'Initializing'.
2007-06-06 16:06:22.488 The agent is running. Use Replication Monitor to view the details of this agent session.
2007-06-06 16:06:23.082 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.
2007-06-06 16:06:23.082 ORA-01008: not all variables bound
2007-06-06 16:06:23.082 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.
I can get snapshot replication to work just fine, but transactional replication throws up the error above.
Anyone with any ideas on how i can proceed?
Thanks in advance!
Regards,
Rama.
Some additonal log info:
2007-06-06 17:46:26.492 Microsoft SQL Server Log Reader Agent 9.00.3042.00
2007-06-06 17:46:26.492 Copyright (c) 2005 Microsoft Corporation
2007-06-06 17:46:26.492
2007-06-06 17:46:26.492 The timestamps prepended to the output lines are expressed in terms of UTC time.
2007-06-06 17:46:26.492 User-specified agent parameter values:
-Publisher TCADEV
-Distributor ATDSEADSQL1
-DistributorSecurityMode 1
-Continuous
-OutputVerboseLevel 4
2007-06-06 17:46:26.571 Parameter values obtained from agent profile:
-pollinginterval 5000
-historyverboselevel 1
-logintimeout 15
-querytimeout 1800
-readbatchsize 500
-readbatchsize 500000
2007-06-06 17:46:26.571 Connecting to OLE DB Publisher at datasource: 'ATDSEADSQL1', location: '', catalog: 'distribution', providerstring: '' using provider 'SQLNCLI'
2007-06-06 17:46:26.571 OLE DB Publisher: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.196 Status: 4, code: 22261, text: 'Request to publisher TCADEV: select DISTRIBUTOR_PUBLISHERNAME, DISTRIBUTOR_DISTRIBUTORNAME, DISTRIBUTOR_DISTRIBUTIONDB, DISTRIBUTOR_STATUS, DISTRIBUTOR_PRODUCTVERSION FROM MSSQLSERVERDISTRIBUTOR '.
2007-06-06 17:46:27.196 OLE DB Publisher: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.196 OLE DB Publisher 'ATDSEADSQL1': select @.@.SERVERNAME
2007-06-06 17:46:27.196 Connecting to OLE DB DISTOLE at datasource: 'ATDSEADSQL1', location: '', catalog: 'distribution', providerstring: '' using provider 'SQLNCLI'
2007-06-06 17:46:27.211 OLE DB DISTOLE: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.211 OLE DB DISTOLE: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'TCADEV')
2007-06-06 17:46:27.211 OLE DB Publisher 'ATDSEADSQL1': sp_MSgetversion
2007-06-06 17:46:27.211 Status: 4096, code: 20024, text: 'Initializing'.
2007-06-06 17:46:27.211 The agent is running. Use Replication Monitor to view the details of this agent session.
2007-06-06 17:46:27.211 Repl Agent Status: 3
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': sp_MSget_last_transaction @.publisher_id = 2, @.publisher_db = N'distribution', @.for_truncate = 0x0
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': sp_MSquery_syncstates 2, N'distribution'
2007-06-06 17:46:27.211 Status: 4, code: 22262, text: 'Request to distributor ATDSEADSQL1: begin declare @.match int, @.retcode int execute @.retcode = sys.sp_checkOraclepackageversion 'TCADEV', @.match OUTPUT if (@.retcode <> 0 or @.@.error <> 0 or @.match = 0) select 0 else select 1 end'.
2007-06-06 17:46:27.227 Status: 4, code: 22262, text: 'Request to distributor ATDSEADSQL1: begin SET DEADLOCK_PRIORITY LOW exec sys.sp_IH_LR_GetCacheData 'TCADEV' end'.
2007-06-06 17:46:27.242 Status: 4, code: 22261, text: 'Request to publisher TCADEV: select Publisher_Name, Publisher_ID, RAWTOHEX(Publisher_LSN), Publisher_HasChanges, Publisher_PollInProcess, Publisher_SyncDones, Publisher_InReconciliation, Publisher_SyncInits, Publisher_RowCntValidations, Publisher_TableChanges, Publisher_XactSetBeginTime, Publisher_Timestamp, Publisher_GUID, Publisher_TracerTokens, Publisher_CurrentPollid from HREPL_Publisher'.
2007-06-06 17:46:27.242 Status: 4, code: 22261, text: 'Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq'.
2007-06-06 17:46:27.242 Status: 0, code: 22261, text: 'Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq'.
2007-06-06 17:46:27.242 Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq
2007-06-06 17:46:27.242 Repl Agent Status: 6
2007-06-06 17:46:27.242 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.
2007-06-06 17:46:27.242 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.
2007-06-06 17:46:27.242 Disconnecting from OLE DB DISTOLE 'ATDSEADSQL1'
2007-06-06 17:46:27.258 Disconnecting from OLE DB Publisher 'ATDSEADSQL1'
Before we say it's an upgrade bug can you tell us what happened up to the point you saw the failure? Can you also answer the following questions:
1. At any time, was there any failures during upgrade?
2. At any time immediately before or after upgrade, were there any other logreader agent errors?
3. Before you upgraded to SP2, did you stop Write activity at the Oracle publisher db? Or were there writes happening during the upgrade of the publisher?
|||Hi Greg,
Thanks for your time. As for the sequence of events, I set up the Oracle Publisher, Distributor and Publication and the error popped up. No Subscriptions up yet. I noticed that the data is actually copied from Oracle into the replication folder. I can see the .bcp, .idx and .sch files and the .bcp file has records in it.
1. At any time, was there any failures during upgrade?
No failures, the upgrade went through smoothly.
2. At any time immediately before or after upgrade, were there any other logreader agent errors?
Well, i wouldnt know. i basically deleted the existing replication, upgraded and then attempted to get replication going. This means everything in the set up is new - the Oracle publisher, Distributor (plus db) and Publication.
3. Before you upgraded to SP2, did you stop Write activity at the Oracle publisher db? Or were there writes happening during the upgrade of the publisher?
I did not stop the writes, so there may have been writes happening. But the writes would have aborted when SP2 stopped the instance at some point. But that shouldnt be a factor - i'm setting replication up from scratch.
Regards,
Rama.
|||Hi,
As a workaround, i've implemented an ETL job that periodically transfers data from Oracle into SQL Server. This works for the time being, but i dont think my tech lead will be happy without the real time replication between the databases.
I'd appreciate any help i can get.
Regards,
Rama.
|||I'm stumped with your issue especially since you started from scratch, it looks like you may have to call CSS and open a support case to dig deeper into the problem. If I find anything new, I'll post here.|||Hi Greg,
Thanks. I may not have a solution, but i at least have a way forward. The problem's on my development box (I work for Intel by the way), so i'm not sure what kind of mileage i have. Is there a standard procedure i should follow in contacting CSS? I am sure Intel has some support arrangement with Microsoft, but how do i even begin?
Regards,
M. Ramanathan
No comments:
Post a Comment