Re: 2 questions about transactions.
Date: Thu, 15 Dec 2011 12:26:20 -0500
Message-ID: <CAE-dsOKqKJJjxKwXfr0suahLDP5TqOHuzyT8ONVRJRp8tQshfw_at_mail.gmail.com>
thanks guys and sorry about the late response. I really don't know how Golden Gate works. Another group handles golden gate. The engineer who handles golden gate is not an Oracle DBA, but she is very good at what she does. When you have 2 technical people with different skill sets sometimes its hard to communicate because you don't totally understand each other.
Jonathans Comment:
"doesn't acquire an undo segment header, and doesn't generate any redo, so golden gate must be doing something more than simply scraping the redo log to deal with transaction activity."
I think my two questions together were confusing. This is what I think is going on. When you issue a 'set transaction' you get an SCN stamp for that transaction that goes to the log. Golden Gate reads the archive logs for the tables that it cares about and then copies the transactions in the logs to what the engineer I work with calls 'trail files' (I don't know if this is golden gate terminology'). Golden Gate does not know what the transaction is doing. So it needs to hold onto trail files until the transaction is closed with a commit or rollback before processing them. (or I think that is what its doing). So Golden Gate seems to have issues with long running transactions.
There is some parameter in golden gate that says how long you can hold onto trail files before it complains. How far you set it is based on how much space you have locally on your server.
The undo question was that I am hoping I can write a script that can reasonably estimate how long it would take to rollback. Most of our long running transactions are idle. They issue a set transaction and sit there. The code that does this is 15+ years old. However, I was looking for a way to reasonably estimate whether it is safe to kill the session to let it rollback or whether I need to have the code page someone who managers the servers to see what is going on. I think I will also look at the wait events. If 99% of them are 'sqlnet from user' and the logical and physical IO is minimal, its probably safe to kill it. This will probably be trial and error for a while in Dev.
In general Golden Gate is a pretty nice tool. It lets us load data directly to a Teradata data warehouse without having to wait for it to make it to a reporting database and then run code to extract and load it. So the data gets there in almost real time. It can also handle very high volume OLTP data.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 15 2011 - 11:26:20 CST