OraFAQ Forum: Streams & AQ » what does add supplemental log data do

Home » Server Options » Streams & AQ » what does add supplemental log data do

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

what does add supplemental log data do [message #260607]

Mon, 20 August 2007 07:57

toshidas2000
Messages: 120
Registered: November 2005

Senior Member

What does the following command do...

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
(PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

I am putting one table in data stream and that table doesnot have PRIMARY KEY, UNIQUE, FOREIGN KEY than what I do??

Thanks
Toshi

Report message to a moderator

Re: what does add supplemental log data do [message #260622 is a reply to message #260607]

Mon, 20 August 2007 08:40

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

But I doubt this will work if all columns are not a primary key without speaking about log generation.

Regards
Michel

Report message to a moderator

Re: what does add supplemental log data do [message #262740 is a reply to message #260607]

Tue, 28 August 2007 03:38

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Michel, I think your doubt is false.

Report message to a moderator

Re: what does add supplemental log data do [message #262743 is a reply to message #262740]

Tue, 28 August 2007 03:52

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Arju, as you have a Streams environment please make a test with a table with no primary key and duplicated rows to see what happens when you modify (update/delete) them.

Regards
Michel

Report message to a moderator

Re: what does add supplemental log data do [message #262746 is a reply to message #260607]

Tue, 28 August 2007 03:58

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Michel I will do test it soon. I deleted streams environment and now I am busy also with another job.
Hopefully,I will inform you whatever the result.

Report message to a moderator

Re: what does add supplemental log data do [message #262761 is a reply to message #262746]

Tue, 28 August 2007 04:24

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OK, thanks if you'll make the test.
If you don't have time, don't bother, maybe someone else will have the Streams environment.

Regards
Michel

Report message to a moderator

Re: what does add supplemental log data do [message #264654 is a reply to message #262743]

Tue, 04 September 2007 02:19

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Michel Cadot wrote on Tue, 28 August 2007 14:52

Arju, as you have a Streams environment please make a test with a table with no primary key and duplicated rows to see what happens when you modify (update/delete) them.

Regards
Michel

Yes they replicate in Streams Environment when I modify or delete they make changes in other end.

Be ok Michel.

Report message to a moderator

Re: what does add supplemental log data do [message #264679 is a reply to message #264654]

Tue, 04 September 2007 03:04

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you have 2 duplicated rows I wonder how Streams knows which one to delete or update.
But, after all, this does not matter as they are anonymous (no primary key).

Thanks for the feedback.

Regards
Michel

[Updated on: Tue, 04 September 2007 03:05]

Report message to a moderator

Re: what does add supplemental log data do [message #280597 is a reply to message #260607]

Wed, 14 November 2007 03:27

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

And it was done because, in 10.2 there is the parameter allow_duplicate_rows....

Code: [Select all] [Show/ hide]

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_stream',
parameter => 'allow_duplicate_rows',
value => 'y');
END;
/

Report message to a moderator

Re: what does add supplemental log data do [message #280598 is a reply to message #260607]

Wed, 14 November 2007 03:28

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Michel, from Streams Concepts,

Quote:

Apply Processes Allow Duplicate Rows
In releases prior to Oracle Database 10g Release 2, an apply process always raises an
error when it encounters a row LCR that changes more than one row in a table. In
Oracle Database 10g Release 2, the new allow_duplicate_rows apply process
parameter can be set to true to allow an apply process to apply a row LCR that
changes more than one row.

Report message to a moderator

Re: what does add supplemental log data do [message #280612 is a reply to message #280598]

Wed, 14 November 2007 04:13

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback and information.

Regards
Michel

Report message to a moderator

Re: what does add supplemental log data do [message #541046 is a reply to message #260607]

Fri, 27 January 2012 04:58

Prashant.Namdeo1
Messages: 1
Registered: January 2012
Location: Pune, India

Junior Member

If the table does not have any primary keys or unique index defined, Oracle will write all scalar columns from the table to identify the row. This may significantly increase the size of redo logs and will impact the log apply services on the logical standby site.

Report message to a moderator

Re: what does add supplemental log data do [message #541061 is a reply to message #541046]

Fri, 27 January 2012 06:32

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Oh you are expert in copying and pasting others work without naming them or pointing to their site or article.

Thieves are not welcome in this forum.

Regards
Michel

Report message to a moderator

Previous Topic:	Adding new Column in CDC table
Next Topic:	what is right point in time restore procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 29 08:10:25 CDT 2025