Topic
  • 6 replies
  • Latest Post - ‏2012-12-05T02:09:33Z by AbhilashJoseph
AbhilashJoseph
AbhilashJoseph
36 Posts

Pinned topic Inserted Records in Target DB

‏2012-11-27T06:21:09Z |
 Hi 
Is there a way to  find until what source SCN has the record been inserted to target  in case of oracle to oracle subsription or atleast until what timestamp? 
 
Regards 
Abhilash 
Updated on 2012-12-05T02:09:33Z at 2012-12-05T02:09:33Z by AbhilashJoseph
  • Rphilo
    Rphilo
    391 Posts

    Re: Inserted Records in Target DB

    ‏2012-11-27T21:37:03Z  
     Hi
     
    You could use the &TIMSTAMP to capture the timestamp of the last transaction applied, mapping it to a column in the target table. &TUMSTAMP is known as a journal control column. There is also &SEQNO which may also give the SCN, depending on database type.
     
    Thanks
     
    Robert
  • AbhilashJoseph
    AbhilashJoseph
    36 Posts

    Re: Inserted Records in Target DB

    ‏2012-11-28T04:58:15Z  
    • Rphilo
    • ‏2012-11-27T21:37:03Z
     Hi
     
    You could use the &TIMSTAMP to capture the timestamp of the last transaction applied, mapping it to a column in the target table. &TUMSTAMP is known as a journal control column. There is also &SEQNO which may also give the SCN, depending on database type.
     
    Thanks
     
    Robert
     Hi Robert ,
     
    I am already using this timestamp field but probably i didnt ask the question correctly . I wanted to know without interrogating the target table . 
    I had a typical scenario where we are not supposed to query the source table as its in few billions and source system application team confirmed that records were processed into their table from their application log but none of the records came to our area for the whole day and scrapping was showing as current ( ie online log) .  I couldnt see any error in dm console also .
  • Rphilo
    Rphilo
    391 Posts

    Re: Inserted Records in Target DB

    ‏2012-11-29T08:40:02Z  
     Hi Robert ,
     
    I am already using this timestamp field but probably i didnt ask the question correctly . I wanted to know without interrogating the target table . 
    I had a typical scenario where we are not supposed to query the source table as its in few billions and source system application team confirmed that records were processed into their table from their application log but none of the records came to our area for the whole day and scrapping was showing as current ( ie online log) .  I couldnt see any error in dm console also .
     Abhilash
     
    You could use the performance monitor to confirm that replication is occurring by reviewing the replication statistics.
     
    It is also conceivable that a job could run on the source database and perform multiple transactions for a prolonged period before issuing a commit.In the default behaviour CDC will not then replicate the transactions until the commit is issued.
     
    Regards
     
    Robert
     
     
  • AbhilashJoseph
    AbhilashJoseph
    36 Posts

    Re: Inserted Records in Target DB

    ‏2012-11-29T09:02:31Z  
     Hi Robert 
     
    Thanks. In this case source system job was a batch job which has already committed in Target tables .This is the 9th day we are repeating the same test with rolling back source system using a databse backup backing up to do the performance testing .All the 7 days data moved from source system to our area but from 8th run we are facing this issue. The only change we have done is changing the NFS mount to v3 instead of v4 used in prior runs. dmshowbookmark , dmshowlogdependecy etc gives current results where are dmstagingstorestatus shows a huge latency. I went into  install/instance_name/perf/subscripion_name and checked the Xml files and its shows that no of transactions as zero where are as we had around 25 redo logs generated within 6 hours .We had around  9 million rows in our main tables during initial runs . I am really struck .Do you think I should raise a PMR ?
  • Rphilo
    Rphilo
    391 Posts

    Re: Inserted Records in Target DB

    ‏2012-11-30T07:09:25Z  
     Hi Robert 
     
    Thanks. In this case source system job was a batch job which has already committed in Target tables .This is the 9th day we are repeating the same test with rolling back source system using a databse backup backing up to do the performance testing .All the 7 days data moved from source system to our area but from 8th run we are facing this issue. The only change we have done is changing the NFS mount to v3 instead of v4 used in prior runs. dmshowbookmark , dmshowlogdependecy etc gives current results where are dmstagingstorestatus shows a huge latency. I went into  install/instance_name/perf/subscripion_name and checked the Xml files and its shows that no of transactions as zero where are as we had around 25 redo logs generated within 6 hours .We had around  9 million rows in our main tables during initial runs . I am really struck .Do you think I should raise a PMR ?
     Abhilash
     
    Yes I think you need to get support involved. You can also enable tracing on the source (refer to my last post to the SQL Server thread for details) to see if there are any indications of what the problem is.
     
    Regards
     
    Robert
  • AbhilashJoseph
    AbhilashJoseph
    36 Posts

    Re: Inserted Records in Target DB

    ‏2012-12-05T02:09:33Z  
    • Rphilo
    • ‏2012-11-30T07:09:25Z
     Abhilash
     
    Yes I think you need to get support involved. You can also enable tracing on the source (refer to my last post to the SQL Server thread for details) to see if there are any indications of what the problem is.
     
    Regards
     
    Robert
     HI Robert,
     
    I could work around with this by clearing the data store, disabling continuous capture and then manually setting an SCN in the current log .
     
    Regards 
    Abhilash