Get Data Moving 1
Results 1 to 4 of 4

Thread: CDC fails to position after Oracle failover

  1. #1
    grant is offline Junior Member
    Join Date
    Jan 2015
    Posts
    2
    Rep Power
    0

    CDC fails to position after Oracle failover

    Hi

    We encountered a problem with Oracle CDC for SSIS when we failed over our Oracle database to our secondary site. All of the transaction logs are available on the secondary site however the CDC service remained stuck on LOGGER status.

    We tried checking the log files etc. but couldn't work out how to fix the problem. We therefore had to reset the CDC service and do an initial load because of the large table size this took about 30 hours.

    We would like to ensure that when we fail back to the primary site that we do not get the same issue occurring.

    I have attached some results from Oracle queries on the system views, and have also attached other logging that we had enabled while we were trying to fix the problem. Can anyone provide insight as to what might have caused the CDC service to lose its position after the failover?

    Also FYI - One thing that did happen on Oracle was that the timezone presented to the application clients was incorrect after the failover, not sure whether this would cause the CDC to get out of sync or not?

    Logs: CDC_Logs_Forum.zip

    thanks

    Grant

  2. #2
    mikedba is offline Junior Member
    Join Date
    Nov 2014
    Posts
    8
    Rep Power
    0
    Was it a failover (ie did the standby database get activated) or was it a switch over?

    If it was a failover then the Oracle database is a new incarnation with the archivelog sequence number set back to 1, there is a possibility of dataloss in this scenrio and I would assume attunity has picked up on this and stopped replicating. Query v$database on the Oracle DB and look at the resetlogs_time column as this would give an indiciated if it was a switch over or a controlled fail over.

    As for the timezone issue, how was the standby database activated? Through Oracle Enterprise Manager? It may of been the agent having the wrong timezone set up. I have noticed Attunity is using GMT so I don't think would of been the issue but that is only a "think".

    For what it is worth, I have done a switch over in my environment and had no problems.

  3. #3
    grant is offline Junior Member
    Join Date
    Jan 2015
    Posts
    2
    Rep Power
    0
    Quote Originally Posted by mikedba View Post
    Was it a failover (ie did the standby database get activated) or was it a switch over?

    If it was a failover then the Oracle database is a new incarnation with the archivelog sequence number set back to 1, there is a possibility of dataloss in this scenrio and I would assume attunity has picked up on this and stopped replicating. Query v$database on the Oracle DB and look at the resetlogs_time column as this would give an indiciated if it was a switch over or a controlled fail over.

    As for the timezone issue, how was the standby database activated? Through Oracle Enterprise Manager? It may of been the agent having the wrong timezone set up. I have noticed Attunity is using GMT so I don't think would of been the issue but that is only a "think".

    For what it is worth, I have done a switch over in my environment and had no problems.
    Thanks mikedba - it was a forced failover due to the primary site losing airconditioning.

    I think you might be right - the logs seem to indicate that CDC is looking for an archivelog sequence that no longer exists. I also think that the timezone issue might be a red herring.

    We will be changing back to the primary site soon but in a more graceful manner according to the DBA. Does this mean a switchover? I'm not sure what the difference is between switchover and failover.

    We were planning on stopping the CDC service before the change and then restarting it afterwards - do you think this is necessary or can we leave the service running?

  4. #4
    mikedba is offline Junior Member
    Join Date
    Nov 2014
    Posts
    8
    Rep Power
    0
    Quote Originally Posted by grant View Post
    We will be changing back to the primary site soon but in a more graceful manner according to the DBA. Does this mean a switchover? I'm not sure what the difference is between switchover and failover.
    A failover is activating the standby database typically when the primary site is unavailable, the database becomes a new incarnation (so it is open with a restlogs) and the log sequence is reset back to 1. It basically indicates that there is a potential data loss because there is a chance the primary database and the standby database where not in 100% sync which is the scenario you had.

    A switchover is graceful and the primary database and the standby database make sure they are in sync with each before they switch roles (ie what was the standby database becomes the primary database and vice versus).

    Quote Originally Posted by grant View Post
    We were planning on stopping the CDC service before the change and then restarting it afterwards - do you think this is necessary or can we leave the service running?
    You will need to at least restart the Oracle CDC Instance (from CDC Designer for Oracle by Attunity, I am not referring to the service) after a switch over (or stop before and start after the switch over) as I have found CDC does not try to reconnect Oracle db if CDC looses connection to the database. I have actually created a SQL Agent job I call watch dog that check CDC is running and if it is not start CDC back up again because I am running over a WAN so with this watch dog job I didn't need to restart the service during my testing, I had just had to check the tnsnames.ora file was updated to point to the correct primary oracle database after switch over. Your tnsnames.ora may have high availability type connections so this may not be necessary.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •