Home » Server Options » Streams & AQ » what does add supplemental log data do
what does add supplemental log data do [message #260607] Mon, 20 August 2007 07:57 Go to next message
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
Re: what does add supplemental log data do [message #260622 is a reply to message #260607] Mon, 20 August 2007 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: what does add supplemental log data do [message #262740 is a reply to message #260607] Tue, 28 August 2007 03:38 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Michel, I think your doubt is false.
Re: what does add supplemental log data do [message #262743 is a reply to message #262740] Tue, 28 August 2007 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: what does add supplemental log data do [message #262746 is a reply to message #260607] Tue, 28 August 2007 03:58 Go to previous messageGo to next message
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.
Re: what does add supplemental log data do [message #262761 is a reply to message #262746] Tue, 28 August 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: what does add supplemental log data do [message #264654 is a reply to message #262743] Tue, 04 September 2007 02:19 Go to previous messageGo to next message
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.
Re: what does add supplemental log data do [message #264679 is a reply to message #264654] Tue, 04 September 2007 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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....



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


Re: what does add supplemental log data do [message #280598 is a reply to message #260607] Wed, 14 November 2007 03:28 Go to previous messageGo to next message
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.
Re: what does add supplemental log data do [message #280612 is a reply to message #280598] Wed, 14 November 2007 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback and information.

Regards
Michel
Re: what does add supplemental log data do [message #541046 is a reply to message #260607] Fri, 27 January 2012 04:58 Go to previous messageGo to next message
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.


Re: what does add supplemental log data do [message #541061 is a reply to message #541046] Fri, 27 January 2012 06:32 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Adding new Column in CDC table
Next Topic: what is right point in time restore procedure
Goto Forum:
  


Current Time: Thu Mar 28 18:18:10 CDT 2024