Re: supplemental logging questions and golden gate
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Tue, 19 Jul 2011 10:53:26 -0700 (PDT)
Message-ID: <1311098006.46316.YahooMailNeo_at_web65415.mail.ac4.yahoo.com>
"However, isn't the before image already captured? " Only of the updated columns, iirc. Supplemental logging ensures that every supplementally logged column has its image captured before the update, either always (whether or not a supplemental column is updated) or conditionally (only when at least one of the supplemental columns is updated). The redo logs track the rowid and all updated column images; the addition of the pk ensures the correct records are processed across systems as ROWID values are not the same between two databases having the same tables, tablespaces and data. "not having a key on a table wth alot of columns can significantly increase redo generation." Yes, and no, depending upon which supplemental logging scheme you're using as likely you'll have all columns of a table supplementally logged. Recovery should be minimally impacted by this. David Fitzjarrell From: Dba DBA <oracledbaquestions_at_gmail.com> To: ORACLE-L <oracle-l_at_freelists.org> Sent: Tuesday, July 19, 2011 7:48 AM Subject: supplemental logging questions and golden gate We are going to use supplemental logging to work with Golden Gate. Golden Gate is going to be used to stream data to other Oracle Databases and to Teradata. I have some questions about supplemental loggings. http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/logminer.htm#sthref2000 1. I believe the documentations says that during updates supplemental logging adds redo for 2 things -- primary or unique key (assuming we have a key and do not need to do all records) -- before image of the update However, isn't the before image already captured? Whern you do an update, the befoer image is copied to the undo tables space which is then written to redo. This is necessary for rollback. I think this is saying that the before image is needed after a commit is done, so that downstream Golden Gate can tell which data to apply? I am not sure why. If you have the key and the after image, isn't that enough? Or is this for some kind of rollback downstream? 2. Inserts/Deletes: just adds the primary or unique key (assuming you have one and do not add need to log all rows). I believe redo logs normally just track the rowid right? 3. not having a key on a table wth alot of columns can significantly increase redo generation. Since if you are updating 1 row, you need to write every column to the redo logs so that downstream golden gate can do a diff to apply the changes. Does this affect recovery at all other than you have more redo. Or will the racle recovery mechanism ignore the supplemental logging?
Date: Tue, 19 Jul 2011 10:53:26 -0700 (PDT)
Message-ID: <1311098006.46316.YahooMailNeo_at_web65415.mail.ac4.yahoo.com>
"However, isn't the before image already captured? " Only of the updated columns, iirc. Supplemental logging ensures that every supplementally logged column has its image captured before the update, either always (whether or not a supplemental column is updated) or conditionally (only when at least one of the supplemental columns is updated). The redo logs track the rowid and all updated column images; the addition of the pk ensures the correct records are processed across systems as ROWID values are not the same between two databases having the same tables, tablespaces and data. "not having a key on a table wth alot of columns can significantly increase redo generation." Yes, and no, depending upon which supplemental logging scheme you're using as likely you'll have all columns of a table supplementally logged. Recovery should be minimally impacted by this. David Fitzjarrell From: Dba DBA <oracledbaquestions_at_gmail.com> To: ORACLE-L <oracle-l_at_freelists.org> Sent: Tuesday, July 19, 2011 7:48 AM Subject: supplemental logging questions and golden gate We are going to use supplemental logging to work with Golden Gate. Golden Gate is going to be used to stream data to other Oracle Databases and to Teradata. I have some questions about supplemental loggings. http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/logminer.htm#sthref2000 1. I believe the documentations says that during updates supplemental logging adds redo for 2 things -- primary or unique key (assuming we have a key and do not need to do all records) -- before image of the update However, isn't the before image already captured? Whern you do an update, the befoer image is copied to the undo tables space which is then written to redo. This is necessary for rollback. I think this is saying that the before image is needed after a commit is done, so that downstream Golden Gate can tell which data to apply? I am not sure why. If you have the key and the after image, isn't that enough? Or is this for some kind of rollback downstream? 2. Inserts/Deletes: just adds the primary or unique key (assuming you have one and do not add need to log all rows). I believe redo logs normally just track the rowid right? 3. not having a key on a table wth alot of columns can significantly increase redo generation. Since if you are updating 1 row, you need to write every column to the redo logs so that downstream golden gate can do a diff to apply the changes. Does this affect recovery at all other than you have more redo. Or will the racle recovery mechanism ignore the supplemental logging?
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 19 2011 - 12:53:26 CDT