replication as ha option
ana, March 08, 2004 - 7:34 am UTC
Tom, Correct me if I am wrong. My understanding is that for a Dataguard failover, manual intervention of the DBA is required. But if I have a replicated database (2 masters - synchronous from primary to replicated database and asynchronous the other way around - and only the primary being updated in normal circumstances), the failover would be automatic and does not require the DBA to be on site immediately.
Thanks
Anandhi
March 08, 2004 - 8:21 am UTC
the problem is you have to design your entire system from day 1 to be replicated since when you "failover" (lose the ability to connect to db1) there will be QUEUED transactions that have not yet taken place on db2 (eg: your users will say "hey, I know i did that already and do it all over again") when db1 "recovers" it'll push its transactions and db2 will push its transactions. bamm -- update conflicts.
So, replication is a tool developers can use to build a replicated database.
dataguard is a tool DBA's can use to set up a highly available environment.
they are not to be confused - you cannot replicate 3rd party applications like Oracle Apps, people soft, SAP, etc. You cannot replication most custom developed applications without major design/coding efforts.
you can data guard ANYTHING.
and yes, when failover is to take place, you want a human deciding that. failover is something that happens in minutes, it is in response to a disaster (hence the name DR). It is a fire, an unrecoverable situation. You do not want to failover because a system blue screened (wait for it to reboot). You do not want to failover some people but not others (as would happen with db1, db2 and siteA, siteB if siteA cannot route to db1 but can route to db2 but siteB can still route to db1 - bummer, now you have transactions taking place on BOTH and unless you designed the systems to be "replicatable" you are in a hole world of hurt)
DR is something you want a human to be involved in. They need to pull the trigger.
A reader, March 08, 2004 - 9:48 am UTC
difference between streams and change data capture
Nemec, March 25, 2004 - 5:35 pm UTC
Hi Tom,
can you please provide a classification of streams and change data capture.
I guess the main difference is that streams covers event capture, transport (transformation) and consumption. CDC only the capture.
But if you consider only event capture, are there technical differences between streams and change data capture? What was the main reason to made CDC as a separate product?
thx
Jaromir
</code>
http://www.db-nemec.com <code>
March 26, 2004 - 9:17 am UTC
think of streams like a brick.
think of CDC like a building made of brick.
streams can be used to build CDC. CDC is built on top of streams (async CDC is anyway, sync CDC is trigger based).
they are complimentary, not really competing.
Great information, always - but one more question
Juann Chiang, April 12, 2004 - 11:43 pm UTC
We got streams replication working and tweaked; much better than master-master replication. One big concern is that Streams replication only utilizes 10% shared_pool memory. We need to have flexibility of increasing the shared_pool allocation more than 10% for streams replication. Metalink Oracle support responded that this is impossible. Do you know any handle to change share_pool usage for streams? If not, could you pass this to Streams developer? We need to know if this is possible in the future release.
Thanks!!!
Good news!! STREAMS_POOL_SISZE
juchiang, April 14, 2004 - 10:43 pm UTC
Very glad to hear about streams_pool_size in 10g. Without streams_pool_size you really can't scale up the streams replication without wasting much shared_pool memory.
Thanks again!!
Repliaction and MV are dead?
Tony, April 23, 2004 - 8:24 am UTC
I think Oracle Streams has lot of features.
Can I say that with the invent of Streams, conventional replication and materilaized views, etc are dead?
April 23, 2004 - 1:25 pm UTC
It is recommended going forward that Streams be used to do replication.
MV's, a big data warehouse feature, are definitely far far from "dead"
Streams
Syed F. Hashim, July 27, 2004 - 9:39 am UTC
Tom you are doing a great job... Excellent
My personal thanks to you, I got help several times from these pages.
But why you are not taking new question?
Regards,
Syed
July 27, 2004 - 9:52 am UTC
i take new questions.
ask_tom@ASKUS> select trunc(timestamp), count(*)
2 from WWC_ASK_SUBMITTED_QUESTIONS$
3 where timestamp >= sysdate-20
4 group by trunc(timestamp)
5 order by 1;
TRUNC(TIM COUNT(*)
--------- ----------
07-JUL-04 8
09-JUL-04 18
17-JUL-04 9
18-JUL-04 2
19-JUL-04 12
24-JUL-04 9
26-JUL-04 9
7 rows selected.
it is just that between following up the followups -- travel, talks, doing my job...... well
streams/mv's to heterogenous environment..
A reader, August 10, 2004 - 8:34 am UTC
Tom,
Can you please provide a link for streams/mv's to heterogenous environment..
I have db's in sqlserver, db2 and master is oracle. i need to replicate data to other's db's from oracle.
I searched, i could not get.
Thanks,
Pravesh Karthik
August 10, 2004 - 8:56 am UTC
we can do refresh complete mv's from sqlserver/db2 -- just use the hetergenous gateways, they let you query sqlserver/db2 as if it were Oracle.
For streams, the sqlserver/db2 application would have to send a message, the application would participate.
Streams - replication procedure but not replicate each row of data
reader, August 27, 2004 - 6:04 pm UTC
Tom,
.
I am trying to migrate from advanced master-master replication to Streams replication (2-way) and have not figured out how to simulate advanced replication's procedural replication.
.
The problem is that if I call a procedure in which execute immediate a statement, say 'update BigTable .. where ...', where this BigTable is replicated (Streams) and it updates 200k records. Unfortunately this single update command will be processed with 200k LCRs (replicated transactions, one for each updated row). The apply process on the target database will execute 200k LCRs.
.
My question is, can Streams repliate a command (procedure) instead of replicating all rows?
.
Please help me with some hints. Thanks...
performance of streams
Raymond, August 31, 2004 - 1:30 am UTC
Could I add to the last question, the following question.
If streams converts a dml which updates many rows to an individual record update, will this not influence the performance on the destination database in a potentially very negative way. A single update statement like update emp set sal=sal*1.1 where dept=20 (lets says updates 20000 rows) must be faster then update emp set sal=sal*1.1 where empno = :empno (repeat 20000 times)?
August 31, 2004 - 8:57 am UTC
yes, that would be correct -- but that is the price of replication via logs. a logfile just has change vectors.
You can with streams add a message yourself, post a message to the "middleware" if you will, saying "hey, do this process". That is, you can add your own messages to streams at will, your own "logical change records"
this is how Streams works..
reader, August 31, 2004 - 12:11 pm UTC
Raymond, your question is exactly my point. That's how Streams (with logmining) works now. When the 'update emp' statement updates 20000 rows, Streams will generates 20000 LCRs on the target database, and it will operate through the 20000 LCRs. This is a performance hit. That's what I am trying to figure out a way to get around it. In advanced replication (master-master) you can use the procedural replication to handle it.
The only possible workaround that I can think of is what Tom said; you have to customize the messages and create your own DML handler. That would be ugly.
From what I have learned so far, I don't think Steams replication can replace the advanced replication.
Replication and Streams.
Alvin, November 22, 2004 - 9:14 pm UTC
When would you know which to use ?
When you need to copy or record changes from a specific SCN onwards Streams is the technology to be used or connection to other DB's are required.
Else its replication most of the time right ?
November 22, 2004 - 9:31 pm UTC
if you are just implementing for the first time (have no investment in either technology) you should take into consider that advanced replication is not going to be majorly "enhanced" in the future and streams is the way going forward.
that is, if you can use streams -- use it. it is the stated direction going forward for replication technology. Advanced replication will be enhanced, it will be supported -- it is not the focus however.
making more than 10% of shared pool available in 9i
Vaughn, December 21, 2004 - 3:01 pm UTC
In response to the April 12, 2004 question above:
You can use the hidden parameter "_first_spare_parameter" in 9.2.0.5 and above to specify what percentage of the shared pool you would like to use for streams.
A reader, December 24, 2004 - 11:37 am UTC
Tom,
I am currently working on a project which needs 99.9999% availability throughout the year.
Oracle consultants have suggested RAC and Streams having 2 sites, each site will have 2 nodes and they will be replicated using Streams.
After going through this thread i am not sure about this approach , Tom please provide your expertise on this.
Thanks.
December 24, 2004 - 1:49 pm UTC
you've given yourself 5.2 minutes of downtime in non-leap years.
anyway, I don't see streams as the exposed technology -- RAC in a room to keep the data center up and running -- data guard to provide for failover in the event of a catastropic failure.
replication isn't an "availability" thing really.
Oracle Streams
Ibrahim Tarla, January 04, 2005 - 8:58 am UTC
Very useful replication stuff. Glad to read it.
A reader, January 20, 2005 - 7:39 am UTC
January 20, 2005 - 10:32 am UTC
seems unambigous to me - why not ask the people who said that to clarify?
streams would need archives for a couple of reasons, that is one of them (streams failure would be another - it would have to catch up)
A reader, January 20, 2005 - 8:26 pm UTC
Thanks Tom. Time to look for new consultants :-)
A reader, January 21, 2005 - 5:56 pm UTC
Tom,
Are uncommited transactions also captured and propagated to the apply queue.
Thanks.
January 21, 2005 - 8:14 pm UTC
no, the rollback that is logged will cause it to say "nope, don't need this"
Captured but not enqueued
Anil, January 22, 2005 - 1:10 am UTC
Hi
Why CAPTURE_MESSAGE_NUMBER and TOTAL_MESSAGES_ENQUEUED increases when we make some changes in the database and before being commited. So when does oracle decides that this messages need not be propagated to the destination. How can I see actual number messages that oracle propagated to the destination. Interesting thing is when we rollback a tranasction then both CAPTURE_MESSAGE_NUMBER and TOTAL_MESSAGES_ENQUEUED increases. Is this shows the undo information is also captured and enqued to the queue???. Then who filters this messages that rollbacked messages are need not be propagated to the destination?
select TOTAL_MESSAGES_CAPTURED ,to_char(CAPTURE_MESSAGE_NUMBER),to_char(CAPTURE_TIME,'ddmonyyyy hh24:mi:ss'),TOTAL_MESSAGES_ENQUEUED from V$STREAMS_CAPTURE
Rgds
Anil
January 22, 2005 - 9:49 am UTC
things come and things go -- the redo is processed sequentially and then a rollback is eventually encountered. work is done and then work is undone. Rollbacks are hugely expensive (streams, no streams, any configuration). The database is built to commit.
Streams 10g R1
A reader, January 26, 2005 - 9:04 pm UTC
Tom,
1. What be your recommendation for setting the number of capture and apply processes if we are doing bidirectional streams.
2. Will there be any performance impacts if we turn on supplemental logging for database.
Thanks.
January 27, 2005 - 8:11 am UTC
1) don't really have one, you'll want to benchmark in your envionrment, with your network, against your data, using your transactions.
2) depends -- if you are totally jammed on redo generation now, if logs are your point of high contention -- supplemental logging will obviously "add more redo" increasing that issue.
On the other hand, many systems would not even notice the increased redo generation.
License Requirement
Balajiv, February 12, 2005 - 5:47 am UTC
Does Streams Require a License / Software like RAC for Configuration ?
February 12, 2005 - 12:45 pm UTC
streams and rac are not related to eachother, they are totally separate features.
you have one without the other.
no, you need not license rac for streams.
License Requirement
Balajiv, February 13, 2005 - 2:16 am UTC
Lighting Speed !!! What Mhz is your Processor Speed ???? Great !!! Motivating People. We really like the way you do. None of the Forums replies so FAST ....
...
We generally procure RAC software with License for RAC implementation. Similarly do we need License / Software related to STREAMS? One of the Famous Sites Quote this for their backup strategy service ? :|
:) I Got your Answer !!! Streams does not require any license or software for configuration.
Thanks,
Balaji.v
February 13, 2005 - 9:17 am UTC
it is catch as catch can on this site. I will tend to get onto airplanes and fly long distances, or just be on the road all day long.
streams comes with enterprise edition of the database, that is what you need to have licensed.
see the 10g license guide for details.
A reader, February 13, 2005 - 1:06 pm UTC
Hi Tom,
Is Oracle advanced queuing(AQ) is renamed as Oracle Stream in 10g?
Thanks
February 13, 2005 - 4:17 pm UTC
no, AQ is a foundation technology used in the streams implemenation (and advanced replication), but streams is not AQ.
Emptying the contents of an Oracle Stream
Mike, February 17, 2005 - 8:13 am UTC
Hopefully this isn't too off topic...
We are using streams (9iR2) to "upload" changes made to a personal DB to Central one. It is a requirement for the user to be able to "throw away" the changes they have made to date, and to get a new recent copy of the central data.
We can drop and re-create the MV's no problem, but I am struggling of finding a way to remove the entries from the AQ supporting the Streams process, I've looked in DBMS_AQ, DBMA_AQADM, DBMS_STREAMS_ADM, DBMS_CAPTURE_ADM and not forgetting DBMS_APPLY_ADM but they don't seem to be willing.
It was suggested to me that I could "just" drop the AQ (i.e. strmadmin.streams_queue) and re-create it, but I have found that messing with things underneath Streams can cause all sorts of terminal problems and so would rather not push my luck as part of the design.
February 17, 2005 - 9:28 am UTC
are you using a default or custom apply process?
Good Question...
Mike, February 17, 2005 - 9:39 am UTC
We have added out our DML Handler to the streams processing, but it (Streams) is dequeuing the LCR's for us and passing them into our DML handler.
February 17, 2005 - 10:44 am UTC
ok, you need another handler that discards. it would discard until there are no more, then you can put back the other one (maybe it is the same handler, it just reads a flag somewhere you set to say do it or ignore it), "copy" the data and resume processing. perhaps it could be as easy as "put a 'ignore records until this date'" flag in there
in 10g there is a new purge API as well.
you might consider posting this on the streams discussion forum on otn.oracle.com -> discussion -> products -> database -> oracle streams as well. (i've not a ton of practical experience with it)
Streams and Sequences
Anil, February 17, 2005 - 2:43 pm UTC
Hi tom
Is there any way to propage the changes to sequences in a stream replication.
Rgds
Anil
February 17, 2005 - 3:08 pm UTC
sequences, no.
the way you would deal with that is:
site1: create sequence s start with 1 increment by 1000;
site2: create sequence s start with 2 increment by 1000;
...
siteN: create sequence s start with N increment by 1000;
the entire concept of a sequence is a highly scalable, concurrent unique number generator. "replication" of such a thing would remove the 'highly scalable, concurrent' atributes entirely (eg: if you tried to use the sequence at two sites -- that would have to be "serialized" globally else you generate the same numbers, that would kill availability, scalability and concurrency totally)
what is the risk of using streams as Disaster Recovery purpose?
Lauren Ning, February 24, 2005 - 2:58 pm UTC
Tom,
I understand that you stongly recommended as streams is designed for replication, not for DR. However, we are facing a request of fail-over/back in 5 minutes. Data Guard can't provide this speed due to application limitation. The best solution for us now is oracle streams. Do you see any major risk of using streams to fail-over/back?
Another concern is how do you think about using logical standby database as DR?
Thanks in advance!
February 24, 2005 - 5:38 pm UTC
what application limitation could impose that on data guard?
and how could streams fix it? (since well streams is a foundation technology used in some data guard scenarios itself...)
A reader, February 24, 2005 - 7:44 pm UTC
Tom,
I think many people are considering due to the fact that you can use other site as well, rather than sitting idle or just for reporting purpose.
This is one of the factors that we are considering for leaning towards Streams, Tom whats your suggestion on this.
Thanks.
Lauren Ning, February 25, 2005 - 10:08 am UTC
Tom,
Thanks for your quick response. The 3rd party application is BroadVision. In order to fail-over to our physical standby, we have to re-pointing the application to the new primary database. The minimum time for the whole process is around 30 - 40 minutes (our best practice). By using streams, it will be be close to 5 - 10 minutes by resetting DNS only. The Key is the application on DR site has to point to an opened database, while a physical statndby database can't be open to apply logs... I was thinking of logical standby, which is very similar to streams, but wondering how to to fail-back quickly. any good ideas? Thanks!
February 25, 2005 - 5:52 pm UTC
I don't understand why though? I can get a standby up and running in minutes, where is the 30-40 minutes going???????
Don't even need DNS changes, TNSNAMES.ora would take care of it all.
you'll need to explain more why a standby cannot be opened in minutes in your environment?
Replication
Raj, February 25, 2005 - 6:27 pm UTC
Tom,
I want to use materialized view groups for performance and consistency, but don't know how much, if any, transformation can be done with the materialized views when using advanced replication. I want to define the materialized views with some flexibility (derivation and lightweight transformation) as described in the Data Warehouse guide, but want to manage propagation with the advanced replication features (materialized view group.)
How much functional cross-over there is in using DW-style materialized views vs. MV's for Advanced Replication? Does advanced replication support complex materialized view definitions, or does the MV have to structurally match the source system (aside from row and column subsetting)? Can I put DW MV's in a MV group and refresh without generating replication support?
Thanks for your insight.
February 25, 2005 - 7:11 pm UTC
it is mostly the refresh methods that different (on commit -- no in distributed for example).
there are other subtle differences with regards to fast (incremental is what I prefer to call them, as they are not always "faster" than full) refreshes:
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#25391 <code>
you do not need replication supported generated for read only snapshots (mv's)
Lauren Ning, February 28, 2005 - 10:37 am UTC
Tom,
It is not the database issue. It is the application issue. If just for switching the stnadby database to primary, few minutes is enough. However our application recycle time is much longer. That is why we are looking for a solution of pointing our applcation to an opened database. In this way, we will not need to recycle the application. I am comparing streams and logical standby and is not sure which is better/worse for this. Hope this give you better explaination of our system. Thanks!
February 28, 2005 - 10:54 am UTC
I'm not at all able to understand why your application can get repointed to an "opened database that was not a standby" faster than it can get repointed to an "opened database THAT WAS A standby"
Tell me how would your application *even know???*
What are the differences? (there are none).
You would set up a tnsnames.ora entry, it would have connect time failover setup in it. The primary server goes down. Right now, nothing is happening, you are failing over (whether it is to an opened 'another database' or a getting opened 'standby'). The client application just keeps trying to reconnect -- at some point the standby is there and they get reconnected.
I'm missing something? I don't see the difference between:
o we have to make a dns change
o we bring the standby out of recovery mode and open it.
from the application perspective?
Lauren Ning, March 01, 2005 - 6:29 pm UTC
The application I am talking about is the application Server, not client. It will take a long time to explain our system. It is not my point. What I am trying to learn here is has anyone used oracle streams or logical standby to fail-over or switch-over. What is the impact of using streams or logical standby as DR purpose? Thanks for your time.
March 01, 2005 - 6:39 pm UTC
logical standby to fail over switch over -- YES.
logical standby is designed to do that.
logical standby is built ON TOP OF streams.
anything you do with streams to build a fail over system would be a copy of existing, already in place work.
Lauren Ning, March 03, 2005 - 10:14 am UTC
Thank you very much! It is very helpful.
Streams
Raj, March 24, 2005 - 2:32 pm UTC
Hi Tom,
I need to generate a surrogate key in a target table before apply process occurs using Oracle Streams. I'm not sure how to achieve this?
e.g.
Source table@db1
---------------
empno (P.K.)
ename
salary
Target table@db2
---------------
empid (Surrogate key)
empno (P.K.)
ename
salary
Thanks.
March 24, 2005 - 4:03 pm UTC
before the apply process happens, there is no row to surrogage?
you would have to write a custom apply routine that would get the logical change record and do the insert/whatever.
but -- why do you need a surrogate when you have a natural key?
Streams
Raj, March 24, 2005 - 5:10 pm UTC
Hi Tom,
Actually, our client created surrogate keys in the target database but excluded
natural keys that is causing big time joins and hence performance issues.
Now I've been asked to add natural keys but also leave surrogate keys
in order to avoid impact on other production applications that're referring to it.
I figured out how to add a new column with surrogate key in metalink, but
my requirement is little bit more complex than that.
1. In Source database:
table a
table b
table c
join three tables.
2.table d
table e
table f
join three tables.
3. Result 1
Union all
Result 2
4. Take a result of 3, Generate surrogate key and dump it to a final target table
into the target database.
I know there are much easier ways to do it. e.g. simple PL/SQL code. I could also
have done it using MVs but surrogate keys are what's stopping me.
But, is it doable using Streams?
I have replicated tables a,b,c,d,e,f individually into the target database
using Streams but can't think of what next?
Appreciate you help.
March 24, 2005 - 5:30 pm UTC
confusion here, surrogate keys are normally used to reduce the complexity of a join, you are saying "they are causing join issues"??
Streams
Raj, March 24, 2005 - 6:15 pm UTC
Well, I should say the target database is over normalized.
Since target database is a read only environment, we need to denormalize it little bit.
They have created surrogates even in the lookups where only code could have been used as a key column which requires extra joins every time. Code is meaningful to the users but surrogate keys aren't.
We want to bring only the natural keys over along with surrogate keys generated.
Hope, I explained it right.
Thanks.
March 24, 2005 - 6:37 pm UTC
well, I am just back to "you will be writing a custom apply" to do whatever you need. the default apply won't permit you to do this.
Streams
Raj, March 24, 2005 - 6:51 pm UTC
As I mentioned earlier I don't know how to join tables, union all and generate surrogate keys in a Streams environment. All I have done so far is, replicated the tables a,b,c,d,e,f.
Can you show me an example? It doesn't have to be working. I
just need your directions after that.
I can't find anything in Metalink that helps me.
March 25, 2005 - 7:42 am UTC
have you read about creating a custom apply? you'll write a plsql routine that gets the logical change record, view the contents thereof, and do whatever processing you want to.
your input
A reader, April 07, 2005 - 6:30 pm UTC
Tom,
In our meeting today, the decision was made to put forth the
recommendation that Oracle Streams be the technology used
for replication purposes. However, the following were noted
as risks:
* Recovery
* Archiving
* Multiple points (areas) in the Streams process for failure
and it is challenging to troubleshoot.
I want to make sure what your take on this is. Do you think
these risks can easily be mitigated before we pull the final
trigger on this?
Appreciate your valuable input.
April 07, 2005 - 7:07 pm UTC
what were the concerns with regard to recovery and archiving specifically?
as for the last point, test to destruction as Jonathan Lewis wrote once. That'll be the only way to overcome that for any technology.
history data
A reader, April 08, 2005 - 1:50 pm UTC
Hi Tom,
I'm working on Oracle Streams (9iR2) to replicate the tables.
Based on Ch14-Page15 of the Streams manual, I've created
HISTORY_ROW_LCRS table to store history DMLs. I'm trying to
select old_values, new_values and other columns from this table
but not sure how to view the data.
strmadm@DWD.WORLD> select h.timestamp, hh.column_name, hh.data
2 from history_row_lcrs h, table(h.old_values) hh
3 /
TIMESTAMP COLUMN_NAME DATA()
--------------- ---------------------- -----------------------
08-APR-05 PROD_ID ANYDATA()
08-APR-05 SERIAL_NBR ANYDATA()
08-APR-05 PROD_SEQ ANYDATA()
08-APR-05 LOC ANYDATA()
08-APR-05 WHSE_CD ANYDATA()
Following query above shows anydata() but I want to see the
actual data values. I need your help in this query. Thanks.
April 08, 2005 - 2:07 pm UTC
history data
A reader, April 08, 2005 - 4:31 pm UTC
It works Tom. But I've noticed that it only records the primary key, old column value that has changed and a new column value in the history table. I want to store the before and after snapshot of the entire record including columns that haven't changed. How can I do that?
April 08, 2005 - 4:44 pm UTC
if you are replicating the data -- you already have the rest of the values? you would be updating an existing row?
If you don't want to update an existing row, you still have the existing row, you have all of the Old values there ready to be queried up.
Transfer data incrementally
A reader, April 08, 2005 - 7:26 pm UTC
Tom,
We would like to incrementally transfer data from one oracle database(source) to the other(destination). Some of the tables in the source database have the column last_updated_date, being updated to sysdate each time a change is made. Changes made in the source db are being queried on the last_updated_column and then inserted to the destination db. The problem is for the tables that don't have the column last_updated_date.
My question
What is the best way(Streams, materialized views, ...) to transfer data at certain intervals(say every 24 hrs ...) of time without depending on columns like the last_updated_date.
Thank you
April 09, 2005 - 7:15 am UTC
I hope you know that last_update_date and multi-versioning trick.....
At 11:59am, a user updates record ID=42, trigger sets the last_update_date. User does NOT commit yet.
At 12:00 noon, you start a refresh, you first remember "12:00 noon is the start of the last refresh", then you pull all records since the prior last refresh. This pull STARTING at 12:00 noon will not see the modified 11:59am record (because it was not a committed change when your query to pull started).
At 12:01pm, the 11:59am transaction commits. But your 12:00 noon transaction will NOT see it.
At 1:00pm, you start a refresh again. You remembered "12:00 noon" and ask for all changes since that time. You will forever MISS the 11:59 record....
Now, there are two approaches to fix this
a) use v$transaction, not sysdate to figure out what the "last pull time" should be. So at 12:00 noon, instead of just asking for sysdate, you query v$transaction to find the age of the oldest open transaction, that is your pull time. You have to (in all cases, with or without this v$transaction technique) remember to be able to deal with pulling the same record multiple times!
b) use replication of some sort.
I would point you to look at streams first. That way, you don't pull, you get pushed the changes AND you don't have to deal with the same record 2, 3, 4 times...
Can streams apply surrogate PK values
Arun Gupta, April 19, 2005 - 12:58 pm UTC
Tom,
In most of our application, surrogate primary keys are generated using trigger/sequence combination. The typical trigger is coded like:
CREATE OR REPLACE TRIGGER TR_T1
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
SELECT SQ_PK_COLUMN_T1.NEXTVAL
INTO :NEW.PK_COLUMN_T1
FROM DUAL;
END;
Can this type of data change be captured and applied by streams?
Thanks
April 19, 2005 - 1:13 pm UTC
streams will replicate the data, the triggers will not fire (as they should not since they already did)
A reader, May 12, 2005 - 9:15 am UTC
A reader, May 16, 2005 - 7:40 pm UTC
May 16, 2005 - 8:56 pm UTC
would not appear so
Jim T, May 31, 2005 - 12:09 pm UTC
Tom,
We are on Oracle version 9.2.0.5.0, and exploring options regarding extracting some low volume OLTP data from many (>100) sites to a single master. These table updates will be unidirectional. A small set of separate lookup tables will be distributed from the master to the many locations, again unidirectional.
For this implementation, low complexity and ability to run in a lights out mode are more critical than performance. Additions/deletions to the many distributed sites occur much more frequently than ddl against these tables.
Questions:
1.) Is there an integration point with dbms_cdc_publish/subscribe created subscriber_view with Oracle Streams, i.e. can the trigger captured change records available in the subscriber_view be posted to a Streams queue? Has this been done?
2.) Would you consider a set of mviews against these 40 odd tables an option?
3.) Is there a Streams configuration that would allow for easier additions/subtractions to the distributed sites? From the documentation there was a reference to using a pass-through queue separate from the master to break a dependency, however exactly what that was accomplishing wasnt clear.
June 01, 2005 - 7:37 am UTC
well, cdc and streams are two technologies that accomplish somewhat similar goals. Why have cdc feed streams when streams can feed streams?
for the lookups going OUT from the single master DOWN to the N sites, materialized views make sense.
if the data from the N sites is to be ROLLED UP into a single schema at the central site, materialized views won't be very useful, you'd have to use updatable snapshots and now you are talking about some setup.
don't know what you mean by "easier" in the last paragraph. Well, I know what easier means, but what is the perceived "hard" part.
(actually, i would question a design that leads me to have >100 small sites instead of a single centralized site, you want lights out, ease of maintainence. >100 sites isn't going in the right direction)
A reader, June 29, 2005 - 2:34 am UTC
Tom,
We are in the process of configuring RAC+Streams, 2 sites and each site having 2 nodes each.
Does it matter(streams configuration) if the instances having the same name across sites?
Ex:
Site1
Inst1
Inst2
Site2
Inst1
Inst2
Thanks.
June 29, 2005 - 8:53 am UTC
nope
Can I use streams to refresh development system from a production system
Mohan, June 29, 2005 - 10:17 pm UTC
We are running an ERP application using Oracle 9i. The total size of the DB is around 500GB. We are refreshing the development database from production using transportable tablespaces. Will it be a better approach to use Oracle Streams?.
While refreshing using transportable tablespaces we are omitting certain tablespaces. While using streams is it possible to avoid some tables from copying to the target DB.
Please advise.
June 30, 2005 - 9:25 am UTC
streams would be replication. that would turn your development into a production system as all machines involved in a replication scenario would be production machines.
I would use RESTORE myself. I would restore production to test/dev (makes sure you actually CAN). It would not require making production read only for a bit of time either.
A reader, July 13, 2005 - 11:31 am UTC
A reader, July 30, 2005 - 4:26 pm UTC
Tom,
While creating dblinks for configuring streams why do i need to supply password for links. I have the same password on both the environment, why does it need password for dblinks?
Thanks.
July 30, 2005 - 5:55 pm UTC
depends on how the dblink was created, but -- at streams runtime, you won't "be there" - it won't have your credentials.
Oracle Stream Replication
Hoffman, August 27, 2005 - 4:47 pm UTC
Oracle Stream Error
Hoffman, August 28, 2005 - 12:50 am UTC
August 28, 2005 - 2:55 am UTC
Please utilize support for an issue like this.
Temporarily, you could turn off parallel in the parameter file, if it is parallel execution giving you the problem
Oracle Stream OEM
Hoffman, August 28, 2005 - 1:18 pm UTC
Hi Tom...
Why can't I see the Stream option in the Maintenance section in Oracle EM?
Thanks...
August 28, 2005 - 1:37 pm UTC
Please contact support for configuration assistance, they'll gather your version and other releavant information and help you get going.
Oracle Stream Replication
Hoffman, August 29, 2005 - 2:54 pm UTC
Sorry...
I need a doc that describe how configuring Oracle Stream.
I need replicate data from dbs1.net(ORACLE) to dbs2.net(MSSQL).
Thanks...
August 30, 2005 - 12:52 am UTC
</code>
https://docs.oracle.com#index-STR <code>
you'd be writing a custom dequeue and then apply process, streams can capture and stage the changes from Oracle.
Missed archived log can render Streams totally unusable
Quadro, August 30, 2005 - 7:58 am UTC
In the process of evaluation Oracle Streams in our test environment (before we can consider Streams as our primary replication method) i stuck at the point where someone purged all archived logs. Now i can't start streams capture process - it says it needs archived log what no longer available. After dropping and re-creating capture - still no go.
I wonder if this situation can render Streams totally unusable FOREVER - i can't figure from anywhere how to get capture process running again without this "cannot open archived log '......' " ...
Any workarounds... ?
August 30, 2005 - 9:32 am UTC
wow, you lost archives forever? and what pray tell happened to your backup and recovery then???????? (seeing as how they are sort of vital to that process)
Tell me, you are using advanced replication (for example) and your dba "truncates" the queue. OH NO, it is lost forever???
I mean, you just broke the system here -- there are changes that you lost *forever*, you cannot just restart, you have to sort of start over for the replication, else they (the two systems) are out of sync.
But - why not restore the archives from your backup?
Streams got broken FOREVER
Quadro, August 30, 2005 - 6:49 pm UTC
Ok, first of all - it's a TEST system (test test test). Someone purged archive logs because we never backup them on test (if test system became broken - we just restore them for prod).
And even if it was not the test system, how about:
1. Full DB backup
2. Purge archive logs not needed anymore
3. Streams again got broken (FOREVER!!!) ?
And even with all above - why why why i can't reset the whole Streams thing back? Suppose i don't want to continue Streams replication - i just want to get thing working again and start a new replication.
August 31, 2005 - 1:00 pm UTC
if you are in production production production - you would not do such a thing.
and you would have many days worth of archives, because you know you need to keep at least 3-5 backups out there anyway (so you have archives for many many days).
Quadro, August 30, 2005 - 9:34 pm UTC
"you have to sort of start over for the
replication"
What is exactly what i'm trying to do - restart over the whole Streams replication (NOT to continue).
I'm understanding what without archived logs i cannot continue. But i don't want to continue - i want to start all relication over again.
And i can't do what :-) The capture process says it needs archived log and it seems there is no way of resetting it back... or there is?
August 31, 2005 - 1:07 pm UTC
please utilize support for this one
Streams - refresh frequency
A reader, November 15, 2005 - 7:46 pm UTC
Tom,
I would apprecate your response on the using Streams
1) I would like to know if Streams is supported in DB version 9.2.0.6?
2) Can we have data replicated (using streams) from the source system to the destination system with a 15 minute or less time interval? If yes, will this time interval have any impact on the source system?
Thank you
November 16, 2005 - 8:37 am UTC
1) yes
2) yes and yes. the source system will be mining its redo logs and queuing the message.
Streams - refresh frequency
A reader, November 16, 2005 - 10:29 am UTC
Tom,
Would appreciate it if I can get your comments the 15 minutes or less time interval would have on the performance on the source system.
(a) What kind of performance impact should we anticipate on OLTP database?
(b) Are there any thumb-rules for calculating additional memory/disk etc to set up streams?
Thank you
November 16, 2005 - 6:01 pm UTC
the 15 minutes isn't the impact, it is the fact that more work than before (processing the redo stream) is now also taking place and the fact that you might have to add suplemental logging (or not, depends on your needs) which could add to the load as well.
benchmarking... It always comes back to that (or - how much money do you have to buy more stuff? use it all and hope for the best :)
I would suggest setting up a simulation. this'll accomplish two things
a) you'll get reasonable sizing estimates
b) you'll learn a ton about how to setup, use, configure, tune this new feature.
A reader, December 06, 2005 - 7:42 pm UTC
Tom,
According to Streams Rep Admin Guide 10G R2 Chapter 3
<QUOTE>
Note:
Prebuilt update conflict handlers do not support LOB, LONG, LONG RAW, and user-defined type columns. Therefore, you should not include these types of columns in the column_list parameter when running the SET_UPDATE_CONFLICT_HANDLER procedure.
</QUOTE>
which seems to be inaccurate as LONG and LONG RAW works for OVERWRITE and DISCARD just tested it(did not test other prebuilt methods).
Regds,
-Asif.
A reader, December 06, 2005 - 8:07 pm UTC
Tom,
Please ignore my previous post as the test was not correct.
Thanks,
Asif.
Streams and Data guard
A reader, December 07, 2005 - 11:45 am UTC
Tom,
Between Streams and Data guard - what are the appropriate conditions or situations for each of them to be used. Is one better than the other? The purpose I am looking at is, to set up a reporting instance with data comng from an OLTP instance.
Thank you
December 08, 2005 - 1:22 am UTC
data guard is a product built on top of a foundation technology called streams.
think of data guard like a packaged application that does something.
thing of streams like a bit of technology you can use yourself to build something.
data guard is like buying a HR package.
streams is like buying a sql database you could build an hr package with.
So, do you want to build or buy, and if you buy, is what you are buying sufficient to meet your needs.
A non-answer if ever I gave one, but the only one you can give :)
I had a manager who proposed exactly what you had replied but...
Daniel, December 09, 2005 - 12:05 pm UTC
Tom,
I have an ironic situation, working as a dba, my ex dba manager (who waas fond of you, name is keith martin) was very cooperative, and He had build the DR site with me which was setup using 9i dataguard 'High performance' mode but using logwr to propogate changes so asynchronously changes are being propogated and most of the time we have almose zero % data loss as our dr is 2 km away and netwrk bandwidth is 200mega bits(25 MBYTE) per sec. He has left and another manager now comes in.
Reading this post of your gave me a quick thought to discuss this. I have been trying for post my question for past 2 weeks(even at night times, week ends, to get a slot to post my question but all in vain, since this post is near to what my manager is trying to achieve, i am hoping you would reply.
He is saying:
Since company had invested a lot of investment in DR to build the infra structure(oracle 10g as apps clusters, aix regatta machine to host 9i database, gateway to country's central bank etc) since this is same in prod site. Since users are from internet doing selling, buying shares, trans are 300 per sec. Regatta machine has 12 cpu, 25 gb ram at both pr and dr site. Users are 100,000 registered, concurrent users are 10,000 increasing.
So he has presented to ceo that we are wasting our money in dr and he proposed to use ACTIVE-ACTIVE scenerio, saying this will Leverege the cost and also Load balance the ongoing load.
He has asked me to turn off the data guard and deploy Oracle advance replication. He wants the dr database to be online and active. He is confident that by doing this, he would have internet users who come to our site via url which goes to cicso content switch which currently distributes the load to existing 10gas clusters at primary site, which then pass requests to the unix aix db at prinary site, will be able to route requests to dr 10gas servers which will conntect to the dr site and pass trans there.
So he wants two way replication, pr will propgate to dr and reverse in real time, synch mode.
I have been telling him that this is not good(I quote him a sentence which is yours "You should not walk but run away from such replication".I told him this is financail apps invlves stocks trans and not like a general store having branches say in east cost and west coast, so we can segment the data per region. I magine a cutomer puts his stock to sell at pr, that tran now will become twice(2pc) and deadlocks can come, so even if we achive the performance will go down and out competators will gain this and customers will leave. But he says adv replication can solve all problems. He also does not want to change a single code and says this is possible.
My question:
1. Am i right, if yes how do i convince him, he has asmed me to prepare a presention for board of directors?
2. I am thinking of putting for sake of presentation, options:
1. Streams but point that it is only asynch in 9i.
so not usefull
2. CDC(change data capt) also not useful since we want both ways replication
3. Advanced synch replication
Saying that this possible but there might be deadlocks,
perf will be slow(2pc) and that maint overhead will be
there.
Pls help me, can i still go ahead and try adv replication , offcourse we wil have a test/uat so the strss testing would prove to them that although adva replication can solve their "dr money leverage" or load balancing but at expense of perfor?? am i right or do you think adv replic can solve? maint overhead is not issue for him as he is preprared to hire more dbas, send us to courses etc.
I have heard that in 10g, there is asm mirroring?
Can that be a solution other than advanced repli?
What if i (offcourse i wil study more but need ur ok..)
propose to upgrade to 10g(which is anyway happen) then suggest to use 10g rac,(since 9i rac can still have many nodes but 1 db) but in 10g rac, can i combine asm mirroring, so the 10g rac will consist of 1 node at pr and another node at dr, while writing to a database at pr which is asm enabled so its actually mirrogin to dr?
In this way can 10grac node at dr writes to dr db? or pr?
And can asm mirror to different location like dr?
Another question: EMC or veritas soln which offers h/w mirroring like peer to pper copy or online mirror, i think the are ok for dr (if oracle support) but even in them, the dr mirrored db can not be used unless online right?
as active active??
Pardon my typo as i am in rush to prepare the slides.
Cheers
daniyal
December 09, 2005 - 1:12 pm UTC
Let's say you have a machine that can do 100 somethings per second. (lets call them 100 UOW's - units of work).
That machine is running at capacity. It is doing as much as it can.
Ok, so now you turn on advanced replication. Now each UOW becomes *at least* 2 UOW (insert is insert + insert into AQ queue, update is update + insert into AQ, delete is delete + insert into AQ). That machine cannot do 100 UOW anymore.
Ok you say, it only needs to do 50 (since we have the other machine), we'll do 50 here and 50 there.
But wait, to do 50 that requires 2x the effort - means each of the machines is now running at capacity - and they have not yet begun applying the OTHER GUYS WORK!!! So, they not only have to do their 50, queue the 50 for the other guy, they have to do the other guys 50 UOW too!
So the machine that was maxed at 100 UOW's before replication must do now 150 UOW's just to keep up??
You'd need two machines that are bigger than what you have currently. It is "math".
data guard is there for failure, you have made an investment, like insurance, that if the production site fails, you can keep running.
If you want to INCREASE your investment in hardware, stop using data guard, start using replication - but don't forget - you must wait quite a while as you have to go back and redesign all of your applications and if you are using 3rd party apps - convince your vendor to rewrite theirs - to support bi-directional update anywhere. Oh what, they won't? (I wouldn't either, in fact - we don't....)
Further confused,,,,,
Daniel, December 09, 2005 - 4:18 pm UTC
Tom,
I do follow your excellent example/judgement of 150uow per machine, that is, if our existing machine for example does 50uow, it will have to do 150uows in advance replication; 50 uow(actual as of now), 50uow for AQue(i guess this que is for adv replication log which is like tran log for the tables), then additional 50 uow(for the other guy when that guy will send its own uow as replicated info). But that does not mean we will tripple our resources, meaning if the current machine is having 12 cpu and 25gb ram, we will not necessarily require 36 cpu and 75 gb of ram.
Do you mean that the uow for required for adv que is like actually running the insert against the table, it would be like a redo/tran log , like we now have in data guard.
Yes we would definitely need twice the uow atleast 50 when the primary applies the uow which is meant/originate from itself, and 50 which comes from other site and thats the same uow that it has done for itself.
But the uow require for advance queue is not equivalent to the other 2 uow, or am i wrong?
But still its having atleast twice the work and have more investment, its like asking for trouble and defeats the purpose too of saving invetment and load balancing, so thats a lot for the example.
I know whats in mind/misunderstood by my manager, he thinks that the current data guard is like 1 form of replication so whats wrong in having a two way rpelication, he does not know the basic diff in data guard which works via tran logs as opposed to replication which actuall runs the trans against the db (both ways) as if we have both db in recovery mode always as opposed to the dr db in recovery mode in data guard case.
The second part of your reply says about chaning the application?? which is very imp point?
But why would we change our application?
I read in metalink if the replication is sync, there will be no replication conflicts generated(which would generate if we had asynch replication sites), the metalink note does however says in syncg adv replication, we may have deadlocks although i did not get how come?
Anyway if yo can please elaborate more why would there MUST be app code change, this perhaps will make another solid case in my favour, if you could please give me a basic simple example(like u did) with statements that such and such code would have to be changed by vendor if we go adv replication, and yes the code is 3rd party(your abd rightly guessed).
Laslty: RIght on the target when you said drc is an insurance investment, thats wonderful sentence.
Can i suggest something, what if i say that if yo guys really worried about investment usage done in drc, then
pls do not apply this to the drc db need to be used,
Instead why not have the 10gas application servers lying in drc run against the primary db(since the network badwidth is not an issue). This way atleast they will be using good part of drc investment).
I would say further if you really want to load balance(bec i know if i convince them from invesmt poitn of view) they would jump \to load balancing), then use rac (either at pr site or both) but against one db storage (pr) only. leave the db storage from active active, just use 1 db storage of pr and leave dr storage being data guarded only.
Infact i can even suggest to use the dr aix machine which has db instance mounted for the dr db(as it does data guard) to have another instance as one of the clustered node of rac in case they want to use the rac, this way they leverage the cost and use load balance? what do you think?
Cheers
Cheers
December 10, 2005 - 5:14 am UTC
I made the assumtion "your machine can do 100 UOW right now" at capacity (it is maxed out). Many people say "therefore, we can to 50 UOW on machine 1 and 50 UOW on machine 2 -yippee - free, idle capacity for growth". I say "back up the bus, you will really need to do 150 UOW on each and therefore, neither of your existing machines are big enough - go buy more hardware"
I never said you will have to triple your capacity, I said you need MORE capacity to do replication than to do data guard. You don't get anything for free here.
Replication is asynchronous. You would NEVER in a million years use synchronous replication (why bother, if one machine is down, BOTH are - sort of defeats the high availability aspect doesn't it - and if you want slow, synchronous replication would be a really good approach to achieve it). Therefore update conflicts are always a real possibility and if the connectivity is removed for a small time - it goes up really fast (the odds of update conflict) so you must code in anticipation of it.
Just think about your tables with primary keys right now, you have to change something somewhere to ensure they generate globally unique keys. That is but the tiny tip of a huge iceberg of issues to consider.
There are no cases I've ever seen of an application replicating bi-directionally whereby the application itself did not require changes - and lots of them - to do it. And most of the times, people give up - in particular with 3rd party stuff (sort of makes it unsupportable, un-upgradeable)
thanks
daniel, December 10, 2005 - 7:30 am UTC
Thanks I have understood the wisdom in your answers.
However for the code to change, our tables do not generate primary keys themselves (i know u meant by sequences, identitiy columns etc).
So if thats not the case; whats the differenct from single table point of view, it is in any way receiving multiple updates from different clients simultaneoulsy now and in the replication case, one more client which is the other guy is added? so what will the difference??
Can you please provide a live example where code is required to change?
December 10, 2005 - 10:42 am UTC
think about having the same record in two places.
At the time you change it on place1 - I change it on place2. Now what happens when these changes pass eachother on the network.
What do you use for primary keys, what would prevent two people on different machines from generating duplicate ones and what happens when they do.
Think about what it means to have two copies of the SAME data being modified in TWO locations at the same time.
ok got it
daniel, December 10, 2005 - 12:49 pm UTC
Ok I got it, i was so silly to miss te point that the data will actually have two copies as opposed to one , sometimes you tend to ignore the obvious reasoning.
However today i have met with these people and explained what i understood, seems one person still sticking to the idea that why oracle does not support ACTIVE-ACTIVE scenerio when a company has the same infra structure at two closely located places, to him it seems like a big flaw and he gave an example of sybase two way replication which according to him is far superior then oracle,
I did not debate on that as i know changing rdbms is out of questions even if he is right which is not the case.
I told him that to me load balancing means balancing the machines cpu and mem etc(and not the storage), so if we want we can try using the Oracle 10g Apps SERVERS located in drc to configure to use the head office database over the fast lan in real time, this way we will have those servers also share the load, what do you think?
And then it will leave the db server at the drc only from not being used.
Tom some one suggested to me that until 9i, oracle rac works on multiple nodes accessing same database, but in 10g rac, one can combine 10grac with asm mirroring to achieve what we want.
I mean the db node at dr and node as pr site could be linked in 10g ra accessing pr database which is managed by asm and mirrored by asm to the dr storage, This way we will also use the dr db server.
He was not sure whenther the asm will use the mirroed data for trans or only use the pr and keep the mirroed for replication only and if that can be synch.
Also if the above is posisble, can this work for two sites having bandwidt of 250 mbyes per sec?
cheers
December 11, 2005 - 2:56 am UTC
we do support active active, who said we did not?
I said:
a) it'll take more resources than you are using now. Meaning, you don't get something for nothing here. If you are currently at capacity - you need to buy more first.
This is true for sybase, true for oracle, true for informix. It is called "math", add it up, you are doing the full transaction load AT BOTH PLACES (so you are doing the full workload on machine 1 you are already doing on machine 1) PLUS you are doing whatever extra work you need to do to replicate (trust me, sybase doesn't replicate "for free, with zero work". It takes *work* and that is extra work the machines are not doing now).
b) it'll IMPACT YOUR APPLICATION design.
c) YOU HAVE TO MAINTAIN it.
In short, it generates work, it affects your application (all of them, sybase too you see), it adds to the administrative overhead.
As compared to what you have now.
You want disaster recovery - that is not RAC (clustered computers are together in a single place), that is not ASM (asm is disk mirroring, in a single array, not over a distance).
network file systems
daniel, December 11, 2005 - 3:36 pm UTC
Thanks, I think i have asked and gained enough, just these last bits and pieces to put toghether please.
You said 'we do support active active but it needs more resources'
So in oracle terms, when is the REAL Need and circumstances when one would go for active active like the person at my place is pushsing for?
I mean if it is not for ha and only for load balancing at mucher higher cost , why would some one go for such solution? What was the logic when oracle provide such solution for two way synch replication? when all who tried failed and have lots of issues, what was oracle aiming to achive ?? when tech in real life its vitually impossible
for oltp systems.
Is there a real example you can provide where people have actually done what WE are talking here for oltp apps?
ASM as you said works only within one array, i take it that means one san and not across another storage at another place? but what would stop asm from doing so? when there are other similar solutions from vendors like ibm shark flash copy, remote copy etc? what is the hurdle in asm doing that if the network between two sites support?
RAC is multiple nodes accessing one single database. Why cant we have these nodes of rac spread over different locations connected by high speed lan or wan?
is there any tech restriction, becuase this was another question asked, since oracle provide rac, why cant we crerate a rac cluster having nodes in pr and dr site provided these sites have 1gb lan?
Is there the same restriction that the node at dr will not be able to see or configure the raw device defined at storage present at prim site?
Laslty, if the adv replication is out(bec we cant afford to have perf issues, deadlocks or app code changes).
and if the rac is out because we cane have node of rac across dr and pr site due to some tech restrictions, then
can you suggest any valuable use of thge servers we are going to put at drc(three 10gas clusters , 1 regatta 12 cpu, 24 gb machine running just dataguard recovery), 1 unix gateway. Can you suggest a better solution to atleast use that h/w some how into same app scope pls?
cheers
December 11, 2005 - 5:37 pm UTC
... So in oracle terms, when is the REAL Need and circumstances when one would go
for active active like the person at my place is pushsing for? ...
I have written many times myself "I'm not a fan of replication, it is complex (I Don't Care Who You Buy It From - IT IS COMPLEX). It adds to the administrative overhead. It adds to the program complexity. It adds to the resources needed by the entire system - again, not caring WHO you buy it from - it is pure "math", it requires MORE resources than a single system with a failover site"
Replication might have made some little sense in it's heyday - back in the early nineties, with less "robust" networks - but even then, not usually.
Disconnected clients - yeah, maybe (but even that is becoming more rare these days - I am personally almost never disconnected anymore - but there are cases).
Sure, lots of people have paid the extremely high price of replication, designing it, maintaining it, running it - I don't know of many that would recommend it to their friends however. Can it be done - sure, should it be done just to "not have a machine sitting idle for failover which makes us feel guilty for some reason" - NO, NOT A CHANCE.
What is the reason you WANT to replicate - give me that and I'll talk to that point. Many people think "we'll just bi-directionally replicate and make use of both machines!!" - not realizing they would need a hardware upgrade first.
ASM is a filesystem, like Veritas is a filesystem, like NTFS, like UFS. ASM is not "distance based disk mirroring", it is a filesystem that provides striping and mirroring in a "box".
You can have what is known as a stretch cluster - but guess what, one set of disks (oh.... one set of disks). And remember, the longer the cable - the longer the latency, the longer the latency, the slower the response time. 1 gb sounds "fast", until you put a couple hundred feet of cable between the machines. Physics and the speed of light start getting in the way.
We run RAC in a room to make it so the room "stays up". We use dataguard to a failover site as an insurance policy in case the room "goes away". Nothing saying the data guard site needs to be as HUGE a machine (it is insurance). You can probably get a much smaller machine over there if you want it and "upgrade it on the fly in the event of a catastrophe"
High Availability is neither "free, cheap, or trivial" to implement.
How about using Streams for moving platform
Bipul Kumar, December 23, 2005 - 11:48 am UTC
Hi Tom.
We run a RAC + Data Guard environment on Sun solaris machine. We use Sun Cluster 3.1 for RAC environment. data Guard is single node. Cost of running the database on Sun is significantly high [mostly due to Sun consultancy whenever we need to upgrade sun boxes/storage and also its expensive to maintain Sun Cluster]. We have been thinking to move away from Sun to Intel based linux platform.
Our websites [nearly 150 of them] needs very high uptime [as they earn revenue for us !]. Now one of the major challenges in moving from Sun to Linux is to minimize downtime during the move. So far I have found only two solutions to move between O/S platforms. These are
1. Transportable tablespace.
2. Data Pump.
Both of these methods will require significant downtime in our environment [we have nearly 500 GB of data and we are using Oracle 10.1.0.4].
I have couple of questions for you
1. How stable is Linux RAC + data guard ? Is it as stable/reliable/scalable as Sun ? Is there any performance issues in high OLTP environment on Linux? From various forums of Linux and Linux+ Oracle, I get this mixed information regrading the reliablity of Oracle on linux.
2. In your opinion, is it worth investigating Oracle Streams as an alternative to Data Pump or Transportable tablespace for migrating from Sun to Linux ? So my plan is : we will set up a Oracle streams environment [there will be no bi-directional data movement]. On a sunday morning, we stop all web/app servers, so there will be no data change in live DB. Ensure that all DML/DDL queues have been applied to DB managed by Oracle streams and then point the web/app servers to this DB, discarding the original live DB.
I will appreciate, if you can provide any other suggestion on "How to move from one O/S platform to another ? e.g. Sun to Linux ".
Have a merry Xmas.
Thanks
bipul
December 23, 2005 - 12:55 pm UTC
1) you'll get the same "mixed" stuff from a bunch of Sun forums I would guess as well - there will be people with good, bad and indifferent experiences on everything I would guess.
I'd say - after testing, using best practices, everything can be very stable. Stability mostly is a function of your environment and practices (windows can be a very stable platform, if you approach it with the same sense of seriousness that you might approach a mainframe implementation).
2) streams or even materialized views on prebuilt tables can definitely be used - if you fit within the data type limits - to reduce the duration of the cutover time.
Has someone implemented Streams in 9i production system
Satish Mehta, January 08, 2006 - 2:14 am UTC
Tom,
Have you actually implemented or seen implemented Streams in 9i in production system? Has this been done in heavy OLTP environment? Why I ask this is because for last one month I have been struggling to get Streams running on my systems. We have 9.2.0.6 implemented and streams data flows from Solaris to Linux. For small number of changes, it works pretty good (like DMLs on 10,000 records or so). The moment I take this number to 100,000 records DMLs, Streams takes forever (I have waited and watched CAPTURE process capturing LCRs for 3 days yet no able to propagate).
I have tried increasing the number of slaves capture and apply process could spawn, increased the concurrent number of logminer sessions, enhanced memory structures to help streams, yet success is elusive.
Thanks, Tom!
January 08, 2006 - 11:39 am UTC
we have many people using logical standby, logical standby is build on streams.
Yes, there are people using streams "natively" as well to perform replication.
Streams and logical standby
Michel Cadot, January 08, 2006 - 1:10 pm UTC
Tom,
There is a major difference between Streams and Logical standby in that Streams can make a lot of work and overload the source server especially when you have filters and transformations on LCR whereas in logical standby architecture much of the work is done on target server.
About Streams, we had a project 16 months and checked during 3 months if Streams was an option. Let me share this experience (on 9.2).
1) I had to open several TAR to achieve my tests.
2) If you have many filters and transformations on LCR (say adding columns, suppressing others and changing type for others as in our case), no one can say you in which order these transformations have to be made. One can think that this order is irrelevant as long as the resulting LCR is the same but this is not the case. If you don't make them in a specific order then your transformation process fails. (I opened a TAR and we finally find an order that works for our case but Oracle does not guarantee it will work for another case.)
3) If your capture/transformation/apply chain stops for any reason but the common ones you can't make a diagnosis (even querying all the Streams views and following Metalink notes on the subject).
4) If finally you want to suppress Streams from your db, there is no documented way to do it. You have to open a TAR to get a mysterious package from Oracle that not only suppress whole Streams environment but have side and unwanted effects (Metalink note 276648.1 for an introduction the rest is internal only).
Finally, we settled not to use it until a more mature version. How does it come in 10g?
Regards
Michel
January 09, 2006 - 7:55 am UTC
that does not mean that streams isn't part of logical standby. Are there differences? sure - but one uses the other to implement itself. logical standby is an application built on top of streams. Can you do lots more stuff using streams directly? Certainly - because you are using the same toolkit the standby guys did.
Streams and logical standby
Michel Cadot, January 09, 2006 - 9:57 am UTC
Hi Tom,
What i meant in the first part of my previous post is that this is not because many use successfully logical standby (and we are in these ones) that Streams works as well.
(Satish question was: "Have you actually implemented or seen implemented Streams in 9i in production system? Has this been done in heavy OLTP environment?" and you answered "we have many people using logical standby, logical standby is build on streams.")
For me it's a major point that works are not done on the same server (source or target) in the two architectures and i surely didn't want to say they are not built upon the same components.
Regards
Michel
A reader, January 23, 2006 - 10:25 pm UTC
Tom,
I am trying to setup streams following the Metalink document. I have a question. In the following statements, when we refer to the source_database, how would Oracle resolve the database name? Is it the actual SID or a TNS alias?
This is what I run on the source database ABC.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'LTSADMIN',
streams_type => 'CAPTURE',
streams_name => 'STREAM_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'ABC');
END;
/
This is what I run on the destination database XYZ.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'LTSADMIN',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'ABC');
END;
/
In this statement, how will Oracle resolve the source database name? Do I have to create a TNS alias on the destination database?
Thanks
January 23, 2006 - 11:19 pm UTC
<quote src=documentation>
</code>
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_strm_a.htm#sthref8660 <code>
source_database
The global name of the source database. If NULL, then the procedure does not add a condition regarding the source database to the generated rules.
For capture process rules, specify NULL or the global name of the local database if you are creating a capture process locally at the source database. If you are adding rules to a downstream capture process rule set at a downstream database, then specify the source database of the changes that will be captured.
For apply process rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured messages, then the apply process can apply messages from only one capture process at one source database.
For messaging client rules, specify NULL if you do not want the rules created by this procedure to have a condition for the source database. Specify a source database if you want the rules created by this procedure to have a condition for the source database. The source database is part of the information in an LCR, and user-constructed LCRs might or might not have this information.
If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then the procedure specifies DBS1.NET automatically.
</quote>
A reader, January 24, 2006 - 10:37 am UTC
I did read this documentation but I am still confused. Do I need to create a tns entry in the destination database server for the global database name of the source database or not?
Does the destination database apply process try to log in to the source database using this global name or does this source_database name just used as an identifier?
The reason I am asking is that after setting up streams, on the source database I can see LCRs going into the buffered queue but are not getting applied at the destination database. The apply process seems to be running on the destination database. The only place where I deviated from the Metalink note was that in source_database, I had used a tns alias for the source database instead of using actual database name. I was thinking that the destination database apply process will somehow try to log in to the source database. Looks like I was wrong. Can you please confirm.
Thanks very much for your help. Hope I will get this to work with your guidance.
January 24, 2006 - 8:43 pm UTC
can you select * from dual@that.target.database ?
you never "need" a tns entry for database links.
Failed Propagation
Yuan, January 25, 2006 - 12:02 pm UTC
I am trying to uses Streams as a message queue between different databases. I am encountering propagation errors and I need help diagnosing the cause. The only feedback that I know how to find regarding why the propagation fails is this:
IDT_SYS@EWEBDEV>select last_error_msg from dba_queue_schedules;
LAST_ERROR_MSG
--------------------------------------------------------------------------------
ORA-02063: preceding 2 lines from ESL.IDT_SYS
How can I see what the "preceding 2 lines" are?
A reader, January 31, 2006 - 10:54 pm UTC
Tom,
1. I am reading Oracle® Streams Concepts and Administration
10g Release 2 (10.2) Chapter 2 Streams Capture Process somehow the concept of first_scn and start_scn is still not clear to me can you please explain using your terminology.
2. As per the docs to reset first_scn we have to query REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE (maintenance) Why can't Oracle do it automatically since it has already scanned the redo log?
TIA.
Streams for Database Auditing
Avid follower, March 04, 2006 - 9:45 am UTC
Hi Tom,
We have put triggers on our production database which log (audit) record of each DML on our tables. Insertions get recorded on the main table for the username, terminal, and timestamp, Updations and deletions (if any) are moved to Audit Schemas through :OLD/:NEW values of triggers along with the status of transactions (UPD/DEL) and the information of who did it at what time and from which terminal.
My question is can I use streams to replicate my whole primary database and put all these Auditing triggers on the second database (refreshed using streams), thus taking this extra load away from the server. We can't do it with replication I guess because its snapshots on the Replicated database, where as in streams its the tables.
And then would it be possible to use this Streams database for DSS purposes, putting most of the indexes on it rather than the production database?
Just wanted to know if such an approach is even possible, or has any up/down sides?
Thank you for your time.
March 05, 2006 - 1:28 pm UTC
you would not use auditing triggers (since the user would not really be the same, nor would the time)
you would have to use a custom apply and inspect the LCR's (logical change records).
Overhead of Supplemental Log
Jay, May 22, 2006 - 4:39 pm UTC
Is there any measure of overhead of supplement logs relative to normal logs in 10g? For example, if I have about 10GB redo logs a day, what's the ballpark estimate of the size of supplemental logs if all changed tables have supplemental logs turned on?
May 23, 2006 - 6:48 am UTC
I don't have a ballpark estimate since "it depends". How much stuff do you do on your unique system that requires additional information to be logged?
No primary keys - entire rows logged (well, most of it in general).
Big primary keys? - you'll do more than the other guy over there.
Few updates - mostly inserts? you'll do less than them over there.
</code>
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ls.htm#SBYDB00308 <code>
Query
AB, May 31, 2006 - 8:33 am UTC
Tom,
We have to built a system which will involve 4 databases , which needs to be in sync (2-3 mins delay ). There are 1000 tables in database. There can be times when one database is moved to different location(system will be down), so all the changes should be applied (master site). Also the change should be bidirectional. If I update one database it should update other and vice versa
1)Is it possible to do it using streams?
2)If we use dataguard, is it possible to make the standby in sync with production (when diff can be as long as 15 days of changes).
Currently there is one database on 8i. Client is ready to upgrade it to 9i or 10g.
Tom, could you guide us what can be the various alternatives or the best solution.
May 31, 2006 - 10:29 am UTC
sounds like you want to build a single system to me, honest, not being even a TINY bit sarcastic here.
</code>
http://asktom.oracle.com/Misc/so-what-was-answer-part-ii.html <code>
The best solution: forget this 4 database idea.
Your current solution is optimal, why would you make it
a) really hard to manage
b) almost impossible to program (forget using ANY 3rd party applications, period, this will be 100% custom development, and it'll take 10 times as long to design anything, 50 times as long to test it and many times the cost to manage, maintain it)
Exact requirements
AB, June 02, 2006 - 3:06 am UTC
Tom I had a discussion with client.
We have been asked to build a system which caters to following business requirement.(Sorry its a new system, somebody was trying to make it in oracle 8i with AQ's)
Please note that these have to be active databases (Not DR)
Client requires 4 databases geographically located at different locations, each one of which will have different set of users accessing same tables. All these four sites will be connected through wireless network(2mbps).Site will have a database and app server conected using LAN. Client requires replication between all the four sites every 2-3 minutes.
There can be conflicts which have to be resolved using some business rules.
Secondly if connectivity of any of database goes then users should be able to work independently on particular site (Even for months) and when database is back in cluster all the databases should be in sync again.
Is there any tool or functionality in Oracle which would allow the following points
1) Allow bidirectional updates.
2) When one of the database goes out of cluster, then all the changes are recorded and when it comes back in cluster the these changes should be propagated to other three databases. (Other might have also changed)
3) The replication should support the object relational database model. Meaning thereby that it should cater for replication of objects like BLOBS, CLOBS, BFILE, NCLOB and other user defined objects along with the relational objects like tables, views, synonyms, indexes, clusters etc.
4) Strategies for handling conflicts (like update, uniqueness, delete, transaction ordering, referential integrity etc.) should be inbuilt into the solution provided. In case of unresolved conflicts, an e-mail has to be sent to the system administrator, with the attachment of transaction. So that he can take a decision and accordingly do/undo the changes made.
June 02, 2006 - 10:44 am UTC
best multiply your best estimates on time and cost by at least 10 or more to cover yourself then. It'll take that much longer and cost at least that much more. And if you have to maintain this, take that into consideration as you cost that out for your client as well.
1) yes, advanced replication or streams.
</code>
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14226/toc.htm http://docs.oracle.com/docs/cd/B19306_01/server.102/b14228/toc.htm <code>
2) that is part of replication, yes.
3) see documentation
4) see documentation
but I'll stress, you better really really really want this and be really willing to pay the cost (time, energy, design <<<==== crucial, you'll be doing A LOT of that, test, maintain)
I would never suggest this personally. Update anywhere is *really really hard*.
supplement logging
mal, June 19, 2006 - 4:43 pm UTC
Is supplement logging manadatory for stream?
June 19, 2006 - 6:50 pm UTC
not mandatory, but useful for many things.
Please don't ignore my query
Arindam Mukherjee, July 01, 2006 - 5:18 am UTC
Respected Mr. Tom,
Right now I am working on Oracle 9i and like to know very much three things
1. Advanced Replication
2. Streams
3. Advanced Queue
Without having an ounce of knowledge on these there arenas, I could not understand what item I should start reading first. And the same time, since the version and technology goes so fast, its really tough for me to keep pace with it. I have already read from cover to cover of your book Effective Oracle by Design but in the roadmap to oracle documentation required reading, there is no mention about these three.
Please keep in mind I am not Mr. Tom. Hence these three documents (Oracle PDF)reading from 1st to last page will take me more than 1 year. You please advice me what the start point is to have very very authentic knowledge on these three topics and what version (9i or 10g)as well? I wish to have VERY positive answer becuase I would like to be authentic on Oracle Platform.
Regards,
Arindam Mukherjee
July 01, 2006 - 8:04 am UTC
Well, you asked this else where as well.
It would not take anyone a year to read three documents - I don't care what your name is.
I would suggest skipping advanced replication, focus on streams if you want replication.
And AQ if you want messaging. Replication is build using Streams and Streams uses AQ - but Streams and AQ are not synonymous.
If you need replication: streams
If you need messaging: AQ
Thanks a lot
Arindam Mukherjee, July 02, 2006 - 2:55 am UTC
I am truly grateful to you for your precise response.
What do you think of this?
A reader, July 04, 2006 - 11:48 am UTC
Tom,
This review was posted on amazon.com by Mr. Burleson about the book: Oracle Streams : High Speed Replication and Data Sharing (Oracle In-Focus series) (Paperback)
by Madhu Tumma
-----
1 of 12 people found the following review helpful:
The right technology at the right time, May 15, 2005
Reviewer: Donald Burleson (North Carolina) - See all my reviews
(REAL NAME)
As the editor for this book I was able to read the in-depth utility of Oracle Streams and see how it is going to be a super-hot technology. Oracle Streams is a great replacement for the older standby-database technology and it's is also a great alternative to Oracle RAC clusters when you need a synchronized database at a remote location.
Best of all, Oracle Streams is a great way to set-up a disaster recovery server, and also use the standby server to accept database traffic. Tumma did an exceptional job on this book and I highly recommend it for anyone considering Oracle Streams as a failover or disaster recovery solution.
Madhu's text goes far-beyond the nuts-and-bolts and he shares real-world secrets and techniques for using Oracle Streams in a mission-critical environment. If you need to understand high-speed Oracle replication, this is a must-have book.
-------------------------------------------------------
Tom,
You are an expert in Oracle technology and you said DR=Data Guard, DR!=Streams, Replication=Streams. Mr. Burleson said above "Oracle Streams is a great way to set-up a disaster recovery server", "I highly recommend it for anyone considering Oracle Streams as a failover or disaster recovery solution".
Who should we listen to? Are you both correct?
And then we(everyday DBAs) get blamed for believing in myths.
I have great respect for you! However, I cannot say so about the other guy.
July 07, 2006 - 8:56 pm UTC
Streams is an "building block technology"
Part of Data Guard (physical and logical standby) is in fact the underlying streams "building blocks".
But, you do not use streams to do failover.
You use streams to build "data sharing" and replication (as the title of the book indicated "Oracle Streams : High Speed Replication and Data Sharing").
Oracle developers (the database developers, the people that build the database itself) used a bit of streams technology to implement data guard.
Data Guard - that is failover.
Streams - that is replication, that is data sharing.
Data sharing != failover
Replication != failover
Data Guard - that is a failover solution. I do not see someone using streams to build failover any more than I see someone using replcation to build failover. You would need lower level access to streams (which the database developers have) in order to use it to build a failover solution (which they did).
Streams = building block, upon which we've built Replication and Data Guard. Upon which you can build your own custom "stuff".
If you want a failover solution, you would not be looking at streams (sort of like looking at Oracle the database for a Human Resources solution, instead of Oracle HR or PeopleSoft HR or whatever...)
Opinion on Metalink Streams script
AR, July 17, 2006 - 1:32 pm UTC
Tom,
I'd like your opinion on a Metalink article and script. Note:259609.1 on Metalink talks about how to automate "stopping capture process when the number of outstanding messages are above a threshold and then restarting capture when the messages come within the threshold."
If I understand it correctly - Doesn't this mean that changes on the Source database will not get captured in the interim?? If so, why would anyone implement this script? They should probably mention this consequence clearly in the "Limitations" section, you think?
Sorry if I should be opening a TAR about this.
Thank you for your time.
Note:259609.1 - Got answer from Oracle Support
AR, July 20, 2006 - 2:30 pm UTC
To others interested -
Just got the answer to the previous question on Streams script/Note: 259609.1.
Oracle support says "When you resume capture, it's going to start from the scn when it was previously stopped. We're going to use that same archive log and continue to mine forward so no changes will be lost.... there is nothing that needs to be done to guarantee we look at online redos as well as archivelogs. This will be done automatically."
Very heartening indeed!
Using Streams in the same DB
Reader, August 16, 2006 - 7:31 am UTC
We have a process (using DBMS_SCHEDULER) which starts every 15 mins and processes the records in Table A and then inserts them into B if they meet certain conditions.
Now ,we have plenty of jobs.
We are exploring if it would be a good idea to use STREAMS within the same DB ?
The concerns are --
1.Is it a good idea to move data from one table to another within the same DB ?
2.Where will we write the conditions to move the data from table A to table B ?
Thanks
August 16, 2006 - 8:45 am UTC
does not really seem applicable. More likely would be AQ (advanced queues) where table "A" becomes a message queue and you have dequeue processes to process the messages
Stream Configuration
Pradeep Anirudhan, September 09, 2006 - 9:51 am UTC
Hi Tom
I am trying to setup up stream configuration and I am getting the following error while trying to set up the table_rule on the source database .
The corresponding oracle error only says
SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
3 table_name => 'scott.emp',
4 streams_type => 'CAPTURE',
streams_name => 'CAPTURE_SIMP',
queue_name => 'stradmin.streams_queue',
5 6 7 include_dml => true,
8 include_ddl => true);
9 END;
10 /
BEGIN
*
ERROR at line 1:
ORA-25238: too many recipients specified for message destination
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 75
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 21
ORA-06512: at line 2
The corresponding oracle error only says
"Reduce the number of recipients to 32 or less, and retry the call."
Please let me know what might be the cause of this error and what parameter should I change to get this problem fixed
Thanks
Pradeep
Alexander the ok, September 11, 2006 - 9:37 am UTC
Hi Tom,
We have a HA app that we use streams to keep another database in sync in case of disaster. The backup is 1500 miles away. We had to take down the local app to patch, failover to the backup. Once patched, we brought it up and had to resync the data in the time it was down. In a nutshell, we had to transfer 2.5 gig 1500 miles. It took about 4.5 hours. Do you know of the best way to do this?
September 11, 2006 - 10:35 am UTC
what is wrong with your current approach? (although, 2.5 gb in 4.5 hours seems to be a slow connection?)
Alexander the ok, September 11, 2006 - 11:06 am UTC
The problem is this; I would like to hear your great ideas for identifying the differences between two databases and then resyncing them. By differences I mean the data that has changed in the online system and bringing the backup up to date. Currently the process is to export the new data in import to the backup. Import as you know is slow. Just like to hear your ideas, I'm sure you've done stuff like this before. Thanks as always.
September 11, 2006 - 11:12 am UTC
you need to tell me the goal
a) goal = disaster recovery, then solution is likely data guard.
I don't know what goal you would be meeting with your "sync up" since it is not a backup in the common sense at all.
Alexander theok, September 11, 2006 - 11:36 am UTC
Ok I clearly have not explained myself well.
There is an app with high availability. We use streams to keep a backup database ready. It is 1500 miles away.
We needed to patch the online system. We fail it over to the backup database.
While we are patching the local now offline system, people are using the backup db.
When we have completed patching, we must grab the changes from the backup system to bring the patched system up to date.
I am asking a good way to identify the changed data, and load it into the primary db. Are current process is to exp/imp from the failover system to the primary. I want to know what you would do. Thanks.
September 11, 2006 - 11:41 am UTC
this is what data guard does, why are you using replication - which is NOT for failover/high availability, but is for data sharing - to try and perform a function that is builtin to the database itself??
Alexander the ok, September 11, 2006 - 1:43 pm UTC
Apparently someone came here to train people (one is my dba now) and told them streams has replaced dataguard in 10g.
Does this mean that when we do a "switchover" in dataguard, and switch back after the maintenance is done, it will automatically makes the changes to the primary db? The documentation says it creates sql statements for you but I wasn't sure if it does that behind the scenes or what.
September 11, 2006 - 1:56 pm UTC
ummm, NO
that is so totally not true. data guard uses streams technology, but streams is not a replacement for data guard.
That would be like saying "the Oracle database is a replacement for your HR system".
An HR system can be written that uses Oracle as a foundation technology.
Oracle the database cannot replace a HR system.
HR system = data guard in the analogy.
Oracle the database = streams in the analogy.
switchover and switchback automates the conversion of production into standby, and standby into production and vice versa. It does precisely what you are asking for.
Alexander the ok, September 11, 2006 - 4:06 pm UTC
So streams is just for replication? Wouldn't you just use clone for that in RMAN?
September 11, 2006 - 4:17 pm UTC
you want a failover site right?
that is data guard.
you want a copy for testing?
that is rman plus duplicate database
you want a copy of data for data sharing?
that is streams for replication.
Streams for replicating audit logs...
Craig, September 14, 2006 - 3:56 pm UTC
Tom,
If I wanted to replicate the SYS.AUD$ table to another database to provide real-time remote audit logging, would Oracle Streams be the answer?
Regards,
Craig
September 14, 2006 - 4:48 pm UTC
Using streams
Sonia, September 16, 2006 - 9:05 pm UTC
We are getting data from a different system into a table.
We have kept a field in the table eg process_flag and at a regular frequency a job comes and picks up the data and updates the flag so the subsequent jobs don't pick the same records.
If we use streams/AQ we can get rid of this update process and job .
Is it a good idea of moving data from one table to another table by using streams/AQ or use a process_flag and dbms_scheduler .
The volume is 1000 records / min
Thanks for your help
September 17, 2006 - 8:10 am UTC
why not just use builtin materialized views?
Dont know how you do it
Sharon, September 21, 2006 - 7:16 am UTC
I am appalled at the number of times people ask the same basic question over and over again regards data guard and replication;
You tirelessly repeat yourself over and over again. How do you do it? I would go insane.
These are IT people who should by now read and understand what you have written previously before asking the same questions
Just shocking at the level of brain work here!
I applaud you Tom and your effor and patience at keeping at it;
Again: DataGuard versus Stream
Sacha, September 22, 2006 - 10:50 am UTC
Sharon,
I am agreed with you, particular with "I applaud you Tom and your effor and patience".
At the other side, you know, this is not IT question only.
Take a responsability for 1 billion$ fluid traffic is not as easy as pie and sometime just one word at the wrong place and...
+ suggestion from someone who you realy respect: priceless.
Hi Tom,
I read about Data Guard (already used for physycal standby database), Stream, views, snapshots, replication... but I am more stupid that I was on the beginning: to many different opinions, suggestions, arguments for and against...
Here is my problem:
Today: We have Oracle 9i Enterprise Edition Release 9.2.0.4.0 with physical standby database at different location. Our database support about 1500 users situated at 25 different sites all over the globe.
Tomorrow: We have 2 sites with terribly unstables connections (even T1/T3 are used) so we need 3 databases located at different locations each one should be able to work independently on his site during connection crash and when it comme back all databases should be sync. When everything works well we will sync all databases at night. Yes: we are O.K. with a day old data in "out of site" databases.
Our standby database will continue his failover role on headquarter's database. Yes: we are O.K. with some dataloss in "out of site" databases.
Cost is not a question (more resources), the time when all will be up is.
You tell us: "...So, do you want to build or buy, and if you buy, is what you are buying sufficient to meet your needs..." Yes, I want to buy but... this is a question now: Is what we buying sufficient to meet our needs?
Now when you know our needs I will appreciate your suggestion.
Thanks,
Sacha
Oracle Streams in non-archive log mode
Rao, October 05, 2006 - 10:14 am UTC
Tom,
I understand that oracle streams rely on archive log to replicate date to target systems. But what I am trying to setup reharsal in my test box , which is non-archive log mode. Is there any way to setup a complete cycle of Oracle stream in non-archive log mode? what I am looking for that I create all necessary components( like capture process, queue, queue table etc..) and just demonstrate that oracle stream can capture the changes which are in online redo.Before I can go in prod.(not sure if oracle streams can pickup from online redos). I was getting an error message something like this.
executed this
SQL> exec sys.dbms_streams_adm.add_table_rules('stream_test','capture','stream_test_capture_1','streams_queue');
*
ERROR at line 1:
ORA-01325: archive log mode must be enabled to build into the logstream
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 75
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 21
ORA-06512: at line 1
Thanks
Rao
October 05, 2006 - 1:14 pm UTC
you need to be in archive log mode. period.
Geographically Distributed Grid Database
Dan Clamage, October 20, 2006 - 4:20 pm UTC
Tom,
What do you think about IBM's announcement that they've developed a geographically distributed grid database?
This was something I had talked with a couple of Oracle engineers about in the recent past. They told me it wasn't feasible, primarily due to network latency beyond 22 miles.
It sure would make failover a snap. Nothing to do, really; if one whole node went down, the distributed grid would load-balance the remaining servers. Then you'd be able to use your full complement of server blades fully, instead of having some of them in standby.
As a compromise, I was interested in having one grid at one site segmented so that some CPUs were assigned to a Dev instance, and the rest for a production standby database. Then if the primary grid at our primary data center went down, the standby database could be brought up as the primary. You could also reprovision some of the dev CPUs to the production database. This scenario is doable with current technology. Couldn't convince the DBA team to look into it, though. We have primary and secondary data centers about 26 miles apart.
October 20, 2006 - 5:02 pm UTC
wanna do that 22mile symetric disk replication too?
Streams Natively compiled?
Christo Kutrovsky, November 13, 2006 - 7:11 pm UTC
Hello Tom,
Can streams be compiled nativelly ? Is there any performance gain from it ?
If yes, can you compile nativelly and freely both event handlers and the streams packages ?
November 14, 2006 - 4:18 am UTC
it is doubtful you would see any measurable "speed up" unless your apply routines were really computationally expensive.
Streams and user-defined types
Jasbir, December 21, 2006 - 1:58 pm UTC
I have two questions for 10.2g:
1) Is there a way around the limitation of streams not capturing user-defined types?
2) Does data guard replicate user-defined types?
Thanks for your time.
December 22, 2006 - 6:07 am UTC
physical standby's support all types - it is based on the application of redo, much like a "recovery" is.
Data Guard using streams
Jasbir, December 24, 2006 - 2:48 pm UTC
If a stand-by database that is replicated using data guard can capture all data changes even on columns of user-defined object types and if data guard is built using Oracle streams I don't understand why Oracle streams' documentation says:
"...A capture process does not capture the results of DML changes to columns of the following datatypes: BFILE,
ROWID, and user-defined types (including object types,
REFs, varrays, nested tables, and Oracle-supplied types)..."
Is there actually a way to use streams to replicate tables that have user-definied object types?
Thanks for your help and happy holidays :)
December 24, 2006 - 3:55 pm UTC
physical standby applies REDO, it does not change redo into logical change records.
Abstract Data Types
Jasbir, January 07, 2007 - 2:38 pm UTC
Is the reason that streams cannot capture abstract data types is because streams uses the log miner MINE_VALUE function and this function does not support abstract data types?
Sync users between 2 databases
mooks, March 23, 2007 - 5:53 am UTC
Hi Tom
I want to synchronize the table dba_users and all the grants and privileges between 2 databases. Is oracle streams the answer to my problem or is there a better solution?
Thank you in advance.
Best Regards.
March 23, 2007 - 9:51 am UTC
how about using centralized identity management? LDAP?
To mooks
Michel Cadot, March 23, 2007 - 11:01 am UTC
In addition to Tom's answer. This can't be done with Streams.
From "Streams Concepts and Administration", Chapter 1 "Introduction to Streams", section "Overview of the Capture Process":
Note:
A capture process does not capture some types of DML and DDL changes, and it does not capture changes made in the SYS, SYSTEM, or CTXSYS schemas.
Regards
Michel
A reader, April 03, 2007 - 1:13 pm UTC
Hi Tom - I don't seem to find a good documentation on down stream setup/config on 10.2.0.x. Could you please help directing with some samples?
Thanks.
April 04, 2007 - 9:49 am UTC
how about you tell us what you read - so we can make sure you read the right stuff.
Did you read the documentation - the streams guide itself.
To "A reader"
Michel CADOT, April 04, 2007 - 5:02 am UTC
Alexander the ok, April 09, 2007 - 1:54 pm UTC
Hello Tom,
Couple of questions about streams. We have a RAC system that replicates a schema containing CLOBS 1500 miles away bi-directionally. There is a DR site that is active that also replicates to keep the two in sync. Is there any recommended limit to how much you should try to push using streams? (Either your recommendation or Oracle's).
I ask because it pretty much doesn't work at all. It crashes and throws horrible errors all the time. We don't have data guard because someone wanted two active sites.
We are talking about 30 gig of data. Thanks.
April 09, 2007 - 1:58 pm UTC
... We don't have data guard because someone wanted two active sites ...
why - did they realize that in order to have two active sites - you need two sites larger than the "we have one site and a failover"??
both sites need to be able to do 100% the work they themselves do
PLUS
the work of replication
PLUS
the work the other site does (they are replicating each other)
data guard would result in you needing less hardware over all, easier set up, less code (no conflict resolution code needed), easier maintenance, less moving pieces, easier fail over (actually - just "failover" since replication is NOT failover).
I cannot address "it crashes and throws horrible errors all of the time", it is what we might call 'too vague'. I can only suggest working via support to identify what the issue might be and resolve it.
Alexander the ok, April 09, 2007 - 2:22 pm UTC
So I guess your answer is not to use streams for any amount then?
My comment about it crashes and throwing errors was meant to be vague. I know you can't help with that (ORA-600 a plenty) so there was no reason for me to be specific. I was just trying to get the point across that we have serious issues. We have to work with support often.
April 09, 2007 - 2:48 pm UTC
No, that is not at all what I said.
I was commenting that 'using bi-directional replication for failover just so someone gets a good feeling about using all of their hardware' - implies you just bought alot more hardware and humans to manage it all and keep it running
than if you just use a failover. Less cost, in hardware, in software (developed) and maintaining....
Alexander the ok, April 10, 2007 - 9:37 am UTC
Why do you say that implies we bought more hardware than we needed? It's the same amount whether it's sitting there as a standby waiting for a disaster, or actively being used? It has to be a duplicate of production either way doesn't it?
April 10, 2007 - 11:20 am UTC
nope...
replication = you do your 50% of the work, you do their 50% of the work PLUS you do the work of replication. So, each machine is bigger than big enough to do 100% of the load PLUS all of the stuff of replication.
data guard = you do your 100% of the work. (silence on the rest). Failover machine can be much smaller (it is there for failover, like insurance, it does not necessarily have to support 100% of your peak load - just needs enough to get you by until you drop in big hardware).
you have conflict detection and resolution code running, you mine the redo (instead of just ship it - very very lightweight), you apply their SQL (row by row!!!! slow by slow, the applied SQL is less efficient than the originating SQL)
You need more hardware (and everything else) to do bi-directional replication.
replication makes a really bad choice to achieve failover. it complicates everything (and failover should be SIMPLE). It complicates everything every day. it takes more resources. it takes more work.
use replication when you - well - need replication (which is really rare)
use dataguard when you want FAILOVER.
Alexander the ok, April 10, 2007 - 11:47 am UTC
Thanks a lot Tom this is very useful information. I'm especially interested in how streams applys sql (as you said row by row) vs data guard. I thought that's what it did was ship logs and apply them (very fast I thought). I guess I made an incorrect assumption that since that's how DG works, and DG uses streams, therefor streams applys sql in the same fashion.
Can you provide either a little more detail on how streams does row by row processing or where this is documentated? Thanks again.
Stream - Different Schema
Reader, May 16, 2007 - 4:21 pm UTC
Tom,
Is it posible to use Stream to replicate schema SCOTT on Source DB to another schema (ex: SANDY) on Destination DB ?
I'm experiencing with Stream. I can replicate from source db to destination db with SAME SCHEMA (they're both SCOTT) but it doesn't work when I try to replicate into different schema.
If your answer is yes, can you please give me some example?
Thank you very much
May 17, 2007 - 10:56 am UTC
not out of the box, that is not what it is intended to do...
you would have to write a custom apply process.
Reader, May 17, 2007 - 11:44 am UTC
Thank you for your reply
I will look into custom apply process
Have a nice day
A reader, August 22, 2007 - 2:27 pm UTC
I'm currently experiencing how to set up oracle streams environment and create simple replication job -- such as capturing changes on scott.dept and replicating these changes to destination database. This part was successful. So I wanted to add one table to the capture process. Would that be a good idea (practical) to capture changes of several tables in one capture process, propagation process and apply process? Or create separate processes to different tables?
Besides that, I tried to created user-defined rule, but failed. The thing I wanted to accomplished is only capture changes with column shipper_insert='N' and apply those changes.
Both of source and destination databases are ORACLE 10.2.0.3
1 begin
2 dbms_streams_adm.add_subset_rules(
3 table_name => 'scott.jrnl_trade',
4 dml_condition => 'jrnl_trade.shipper_insert="N"',
5 streams_type => 'capture',
6 streams_name => 'STRMADMIN_CAPTURE',
7 queue_name => 'STRMADMIN.STREAMS_QUEUE',
8 include_tagged_lcr => false,
9 source_database => 'spring.atdesk.com');
10* end;
SQL> /
begin
*
ERROR at line 1:
ORA-00904: "N": invalid identifier
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 1032
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 1181
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 1073
ORA-06512: at line 2
I couldn't find solution. Can you help me please?
Thanks in advance!
August 22, 2007 - 2:53 pm UTC
you mean to use ..._insert = ''N'' '
not a double quote but two single quotes to get a single single quote in a string.
as for the process and propagation setup - practical, sure, given certain data volumes - it depends.
HA
Richard, August 23, 2007 - 10:09 am UTC
Streams is definitely being touted, in Oracle's docs, as an HA tool.
But, doesn't Streams "bypass" SYS & SYSTEM objects?
I've also seen folk saying Streams makes DataGuard redundant. Can't be so, can it?
August 23, 2007 - 1:11 pm UTC
streams is part of data guard actually, one of the foundation technologies (the log mining aspect)
streams replicates DDL - DDL affects sys - it gets done as a side effect.
A reader, August 23, 2007 - 11:23 am UTC
Thank you very much. The rule was compiled successfully. But when I tried to insert into jrnl_trade with shipper_insert = 'Y', the destination table was also applied the changes. Should the rule I created fill out the rows that shipper_insert = 'Y'?
Thanks!
Exclude Specific DML Operations From The Capture Process
A reader, August 23, 2007 - 4:15 pm UTC
I really have trouble to filter out the rows. I tried to tell the capture process to only capture those row with dept.loc='N'. But couldn't get that work. Could you please tell what's wrong? Thanks!
QL> begin
2 dbms_rule_adm.create_rule_set(
3 rule_set_name => 'scott.dept_test',
4 evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT');
5 end;
6 /
PL/SQL procedure successfully completed.
1 begin
2 dbms_rule_adm.create_rule(
3 rule_name => 'scott.dept_dml_test',
4 condition => ' :dml.get_object_owner() = ''SCOTT'' AND ' ||
5 ' :dml.get_object_name() = ''DEPT'' AND ' ||
6 ' :dml.get_command_type()=''INSERT'' AND ' ||
7 ' :dml.get_value(''NEW'',''LOC'').AccessChar()=''N'' ');
8* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_rule_adm.add_rule(
3 rule_name => 'scott.dept_dml_test',
4 rule_set_name => 'scott.dept_test');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_streams_adm.add_table_rules(
3 table_name => 'scott.dept',
4 streams_type => 'CAPTURE',
5 streams_name => 'STRMADIN_CAPTURE',
6 queue_name => 'STRMADMIN.STREAMS_QUEUE',
7 include_dml => TRUE,
8 include_ddl => FALSE,
9 source_database => 'spring.atdesk.com');
10 end;
11 /
PL/SQL procedure successfully completed.
1 begin
2 dbms_capture_adm.alter_capture(
3 capture_name => 'STRMADMIN_CAPTURE',
4 rule_set_name => 'scott.dept_test');
5* end;
SQL> /
PL/SQL procedure successfully completed.
Then Cofusion Reigns.
Richard, August 24, 2007 - 1:52 am UTC
Is Streams now a viable alternative to DataGuard? You seem to be saying that it is.
August 24, 2007 - 2:54 pm UTC
No, Dataguard is a finished product.
It provides for FAILOVER
streams is a bits and bytes technology that you could take and after writing tons and tons of code - re-implement.....
dataguard
a finished product.
Calling Streams dataguard would be like calling the Oracle RDBMS a Human Resources (HR) system.
You could certainly use Oracle to build an HR system. It even comes with an EMP and DEPT table.
But it would take a little bit more code written by you to do so (just a little :)
STREAMS VS. DATAGUARD
A reader, August 24, 2007 - 12:37 pm UTC
There is an article talked about the different between Oracle Streams and Oracle Data Guard on metalink
Doc. ID: 300223.1
Thank You Both!
Richard, August 31, 2007 - 4:55 am UTC
Thanks Tom & A Reader.
I never really doubted that DG was the way to go. It's just that I have seen a few "Streams is a viable alternative to DG" quotes on the old Interweb.
Alexander, November 28, 2007 - 12:03 pm UTC
Hi Tom,
Are commands like shrink space; and things of that nature considered ddl?
We have ddl turned off for streams but it appears to be replicating our commands to reclaim space after a purge job.
Thanks
Oracle Streams vs Advanced Replication
Peter, May 02, 2008 - 5:42 pm UTC
Hello Tom.
I need a document or some reference where can find comparisons among Oracle Streams and Advanced Replication.
Thank You Tom.
May 03, 2008 - 8:43 am UTC
well, the statement of direction from Oracle going forward is "advanced replication will continue to be supported, but streams is our stated way going forward for replication of data"
They both have the same end result right now - the support of bi-directional update anyway data. But streams would be the only choice to make today for a new implementation.
Alexander, June 09, 2008 - 12:21 pm UTC
Hello Tom,
I need help managing streams. I've been supporting a streams environment for almost 2 years now and it's still a mystery to me.
This is what happened; I reclaimed space on a table that is being replicated to (alter table move). If you are wondering why, I thought it would be totally harmless (this is test) and I was trying to do some investigation into the size of the clobs we are storing and the fragmentation of the table was giving misleading space information.
So now the capture process is aborted, and when I restart it will stay enabled for a few mintues then fail. The error that keeps popping up is
ORA-26744: STREAMS capture process "ORAC01X_EBR_CAPTURE" does not support "EBR_R
"."SESSION_CR" because of the following reason:
ORA-26773: Invalid data type for column "malformed redo"
I'm not sure if that's relevant, this type of thing happens a lot and the error seems to be whatever problem happened at the time to cause the process to abort. Then for x number of records in the queue it will throw that until it clears up? Does that sound right?
My problem is I have no idea how to tell if that's the case, or what to do when this happens. We have automatic restart jobs but they are insufficient. The move command I ran took only a couple of minutes, but yet streams has been failing for 3-4 days now and we restart every 5 minutes.
Is there a way to tell how many records behind I am until I'm caught up? I don't even know if I'm on the right trail.
Thanks as always.
June 09, 2008 - 2:12 pm UTC
please utilize support for something like this.
Capture / process history
Mahesh, July 08, 2008 - 3:05 pm UTC
In Oracle 9i, I think there is no way of seeing the history of capture / apply process.
I wonder if is its present in Oracle 10g. Any Idea if I want to see what all errors have occured, how many times capture/process was stopped/started in past?
AWR may be the right place at I think if at the time snapshot was taken capture/apply process was down, it does not appear in the report.
Is it any how possible to view the history of capture/apply process ?
multimaster vs stream
Raul, August 25, 2008 - 9:11 pm UTC
Tom
I need one document, that justifies the use of stream rather than Multimaster, for replication of data, and explain the advantages of stream vs multimaster
Thanks
raul
August 26, 2008 - 9:17 pm UTC
no, what you need is a short document that says "in the year 2008, replication should be so rare as to be observed as often as a dodo bird". And you have it (just print this page)
You'll need to do your own justification - they are different, in their implementations, in the way they way, in the features they support. You'll want to understand both
and decide hopefully to use neither. Replication is *hard*, period
oracle streams latency
Vikas, September 05, 2008 - 4:58 am UTC
We are using Oracle 10g 10.2.0.4 database. There are two databases, OLTP and ARCHIVE.
Data from OLTP database to Archive is replicated using Oracle Down Stream capture method.
In our test environment we are experiencing a latency of 45 min. whereas on production it is 3 hrs. This happens during peak hours.
We are trying to figure out reason behind,
1. Such a huge latency (45 min in test and 3 hrs in Production). Ideally there should be latency below 300 sec (approx).
2. The latency on both the environment is different
could you please let us know what could be the cause for this? and any tricks that may work
Streams Performance Prediction
Bob B, September 17, 2008 - 10:27 am UTC
Excellent topic.
I have looked for Streams performance prediction and have found none except for a zdnet.uk whitepaper using a heavy-duty server setup (which we do not have).
We have an established production OLTP which also serves external systems (incl. reports). Since it is pretty normalized, there are many joins while retrieving data. We want to minimize the reads on the OLTP by denormalizing our data to 5 simple OLAP tables using Oracle Streams.
I have only just received the hardware to set up a proof of concept but the client wants to know NOW what the expected latency will be for these external systems to see new OLTP changes.
If our current transactional performance is very good, and we implement a well-tuned Streams setup on the exact same RAC system (incl. custom transform to OLAP, new Streams db instance) - do you think we can say OLTP changes will be available in the OLAP in less than 1 minute? No plan to attempt downstream capture.
Thanks
Bob
September 17, 2008 - 11:51 am UTC
your mileage will necessarily vary.
No, you cannot say OLTP changes will be there in one minute - it depends on the volume of changes, the amount of redo the complexity of your custom transforms, the speed of your machine, your IO capabilities, etc etc etc etc.
Simple Streams Demo/Example
Robert, September 25, 2008 - 11:55 am UTC
Tom,
Can you please provide me with a *simple* streams demo/example which will 'stream' table changes UNI-directional from one 10.2 Oracle database to another?
I can't find a simple example anywhere... I tried doing this via Grid Control (10.2) but it doesn't seem to work :)
The goal is to send some tables (changes) from production environement to QA environment, transforming some of the data on the way (e.g. credit card numbers, email addresses).
Thanks,
Robert.
September 25, 2008 - 3:47 pm UTC
not really, it takes two databases, some setup and a couple of pages of text to do it.
... The goal is to send some tables (changes) from production environement to QA
environment, transforming some of the data on the way (e.g. credit card
numbers, email addresses).
...
that should read "from a production environment to another production environment"
and actually I don't see how this is going to work, for in the QA environment I'm sure they'll update data - but now it won't match production and hence replication (whose only goal is to make data the same in two places) will cease to function.
this is about as simple and step by step as I can get:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14229/pt_admin.htm#i996796
A reader, January 21, 2009 - 6:59 am UTC
We are going to separate our report and transaction servers. We are planning to use streams the update reports server. Is it right approach?
January 21, 2009 - 1:12 pm UTC
with the amount of data supplied, my off the cuff answer is:
why? why would you make things much more complicated?
A reader, January 22, 2009 - 12:00 am UTC
Thanks for your prompt response. In order to reduce work load due to heavy reports we were thinking to make a seperate server for Reports. This report server will contain the latest data through streams.
January 22, 2009 - 8:48 am UTC
I'd rather look at a slightly larger machine or RAC first - keep a single database if at all possible (using things like the resource manager to reign in the reports and give the transactional side their fair or fairer share).
Streams will replicate, sure, just do it unidirectional (else you have to redesign your entire application). Maybe what you could do a logical standby a couple hundred miles away in order to get your reporting system and a failover site?
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/concepts.htm#i1033706 If you are going to build this reporting system, might as well get some benefit out of the fact you have to maintain it.
Hmmm.. almost on the subject..
Another reader, January 23, 2009 - 3:02 am UTC
Hi Tom, thanks for a great place to spend left over minutes, really enjoy your sense of humor, at least since I haven¿t been subject to it.
First of all I agree with you Tom, that replication and separation should be avoided as a general approach, I¿m also a supporter of keeping few (1) instances (when possible) in the same environment with the general principle that Oracle should control and distribute resources, not the OS, still I find myself currently developing a near real time separated reports/warehouse solution since the system and customer requires it, and I believe there are ways to push up the benefits of the extra efforts of maintaining database integration through Streams.
Assignment: separate reports from the current TB sized transactional system which is struggling under its own weight.
Source: partition for purge, optimize for transactional purposes, purge (drop) static (old) data in large ¿fact¿ tables, which are at least 90% of total size, get a slimmed system that enables for easy maintenance, easy releases and meets the tough SLA on all levels: upgrades, recoveries, response times, backups exports etc..
Target: partition for query, model star schema, bitmap indexes, and you have yourself a near real time warehouse, from there the options of m-views, compression and the rest of the toolbox is open for use.
Integration: One direction stream, single stream (for consistency), no DDL:s, no transformation beyond filtering on dimension tables and changes to table names. And if you want a second report server, just split your stream.
Simplify integration: in order to simplify integration especially when refreshing test environments we built packages for dynamic SQL:s which after stopped integration can rebuild the integration and re sync (dynamically) the most current data with source system swiftly these would also make things easier during incomplete recovery scenarios in the main system if you fail to manage Streams by the book.
Simplify change management in integration: releases with added columns we make sure to add them on the target system way before the release at the source side, removed columns we just leave be at the target site, no real reason to remove them there, so the effort of maintaining the integration is really minimal effort.
Partitioning on both sides are dynamically maintained through packages as well by rules set in a control table. Ummm also of course purge is NOT automated.
Thoughts?
January 23, 2009 - 8:57 am UTC
thoughts would be:
if I could do it with logical standby and meet most of my immediate needs, that would be nice as I would have a failover site in addition to a place to offload queries.
krishna, February 09, 2009 - 12:11 am UTC
Hi Tom,
Can you please explain major differences between Oracle streams & Quest shareplex? I appreciate your response. Thank you.
--Krishna
February 09, 2009 - 7:03 pm UTC
one of them comes with the database - no extra license
one of them doesn't require finger pointing when something goes wrong
one of them won't limit your ability to upgrade, patch or decide what operating systems you run on.
If streams satisfies your requirements, it seems like a no brainer.
Note: we don't really compete with shareplex, we have eqivalent or better that comes with the database.
Bi directional replication between Oracle 10g and multiple Sql Server 7.0 using oracle streams
Hemalatha, February 09, 2009 - 1:41 am UTC
Hi Tom,
I want steps to bi directional replication between oracle 10g and sqlserver7.0 using oracle streams.
Thank You
February 09, 2009 - 7:08 pm UTC
no you don't.
please - think about this, really. Between two homogeneous databases - bi directional update anywhere replication is beyond the capabilities of many (conflict detection - we do that, conflict resolution - you do that, that is hard).
between N where N > 2, it is beyond most of the ones that 2 way would be possible for.
When you add in a database that doesn't do update conflict detection - all bets are off.
I'm not even going to consider touching this, if you can find a vendor that says "no problem" and is willing to sell you something- go ahead, you'll be somewhat "very sorry" later, but I cannot stop you.
Bi-directional db synchronization.
Mallikarjuna, February 11, 2009 - 6:38 am UTC
Hi Tom,
Suppose there are 300 stores maintaining local DBs(Oracle 10g) and connected to a main Server DB(Oracle 10g).
Product rates and taxes and rates are different in different locations and maintaining in local DBs with an application.
Main server has the same DB Schema but contains all products information in tables.
If any changes happend in a specific location db that needs to update data in Main server db.
In the same way If any changes happened in main server that needs to sync in node Db. specific location DB only. I amp planning to implement this with Oracle Streams. But, how to sync data from the main server to a specific node DB? Please guide me
February 11, 2009 - 10:58 am UTC
I hate replication, especially when it is not necessary - and it isn't. Not in the 21st century anyway.
this doesn't sound like streams, this sounds more like updateable snapshots in a hub and spoke, the 300 (ouch, that'll hurt) stores would each define their snapshot with a where clause to retrieve their rows. In that fashion, only the data of interest would be sent to them.
but, I would strongly encourage you to rethink this early 1990's design pattern... Replication is non-trivial and adds immensely to the design and maintenance of an application.
Realtime two-way DB Synchronization
Mallikarjuna, February 11, 2009 - 1:06 pm UTC
Hi Tom,
As I posted that 300 stores (local Oracle 10g DBs) are connected with intranet to the main server (Oracle 10g). Data should be synchronized with real-time updates/inserts/deletes in a two-way DB synchronization.
AS you told Oracle Streams are not good for the above scenario. From local stores to Main DB all data and from Main DB to local DBs only specific location data only needs to be synchronized.
Please suggest me the good technology in Oracle 10g to achieve the above situation.
Thanks
Mallik
February 11, 2009 - 1:22 pm UTC
a single database would be the best way - especially if you say "real time"
I'm at a loss as to understand why you would want 300+ databases.
but if you re-read my answer above, I actually did tell you what would be used, please take another look at it.
... this sounds more like updateable snapshots ...
Two-way DB Synchronization
Mallikarjuna, February 11, 2009 - 2:08 pm UTC
Hi Tom, Thanks for quick reply.
Basically all 300 stores has Sybase DBs and are connected with Main DB Server in Oracle 10g DB. Now, customers wanted to replace Sybase with Oracle 10g only. But every store has different prices of products that should be synchronized with Main Server DB(near real-time). You mean to say that shall I go for Updateable Snapshots? or Please suggest me good approach to handle this problem.
Thanks
Mallik
February 11, 2009 - 2:58 pm UTC
I mean you should go with A SINGLE SERVER, PERIOD.
else, I did point you to updateable snapshots, but only go there if you want your life to be miserable (only replicate if you want your life to be miserable).
It just isn't necessary.
Two-way DB Synchronization
Mallikarjuna, February 11, 2009 - 10:18 pm UTC
Hi Tom,
What do you mean by A SINGLE SERVER, PERIOD? Please explain.
Data is different in different locations DBs and all are connect to the Main Server DB with the same schema. Data should be synced with the main server with near real-time availability. Which method is good in Oracle ?
Thanks
Mallik
February 12, 2009 - 12:03 pm UTC
I mean by a single server -
ONE COMPUTER
throw out the 300 little computers.
I fully understand that each "site" has their own data. That is not any reason for 300 databases. Not in the 21st century.
The best method IN ALL DATABASES for this would be ONE COMPUTER.
Two-way DB Synchronization
Mallikarjuna, February 12, 2009 - 12:16 pm UTC
Hi Tom,
Yes your right each "site" has their own data but are located in far distant locations and connected with Intranet to a Main DB Server. How can it be managed with ONE COMPUTER? Please suggest me a good approach to achieve data synchronization in Oracle.
Thanks
Mallik
February 12, 2009 - 4:20 pm UTC
if you can replicate, you must have this thing called a network.
How are you able to ask me a question? I'm 2,500 miles away from my computer right now - you are much further than that. Yet *somehow*, we are using a single computer.
Two-way DB synchronization
Mallikarjuna, February 13, 2009 - 5:25 am UTC
Hi Tom, Thanks for your reply.
As you told with the Single Computer we can solve the problem. But the problem is that Application is designed such a way that can connect to the local DBs (300+ sites) and transactions are recorded in local DBs. All sites are connected to the Main Server (Oracle 10g DB). The data should be synchronized in bi-directional way. If the data changed in main server for a particular site, that data should be synchronized to the corresponding site only.
And all changes from all sites should also be synchronized with the Main server DB. Which method is suitable to achieve this situation. Oracle Streams AQ or Updatable Snapshots? Please clarify.
Thanks for spending time to answer the queries.
Mallik
February 16, 2009 - 10:38 am UTC
you are switching databases, you will be recoding everything. Fix the bad design, do it right for the year 2009, cut your costs down to a mere fraction of what they are now. do it right. This 'sunk cost' stuff - I don't buy it. Take the 300 computers you are running in the field and auction them off. You'll save on administrative cost, maintenance cost, electricity, decreased downtime - the saving would be amazing - simply amazing. And recoding the application will take a lot less time. Deployment will be a snap.
I already told you which of the awful replication techniques I would use if forced (actually, I'd leave, walk away). But I have told you more than once - did you not see it above? I said it twice.
Where to find Streams packages?
A reader, April 15, 2009 - 10:19 am UTC
Tom, I am planning to play around with Streams for some testing and I got stuck at 'DBMS_STREAMS_ADM package not declared' error. Obviously it's not installed on my database..so where can I find a installation package that can install all relatd Streams packages [oracle supplied]?
I checked in \rdbms\admin\..but couldn't find one related to streams. Any help is greatly appreciated.
Thanks
April 15, 2009 - 2:01 pm UTC
what version and edition are you using.
in 9ir2 and above (only validate versions) $oracle_home/rdbms/admin/dbmsstr.sql has it - but it should already be installed
You are correct!!
A reader, April 15, 2009 - 3:06 pm UTC
I am using Oracle 10g rel 2, but I found dbmsstr.sql in the same location [I saw that before, but was thinking there will be something like dbmsstreams.sql..
But it helps. [no it did not install by default] When I ran this dbmsstr.sql, I got all the required packages.
Also, Can I ask you what's the difference between the following:
Oracle Streams,AQ's and Change notification [Can I use one in place of others?]
Thanks.
April 15, 2009 - 4:38 pm UTC
AQ - sort of a foundation technology, Streams for example will use AQ. AQ is for messaging.
Streams - mines redo and turns redo into logical change records to which you can apply custom apply routines or let streams just apply the changes itself (no code really to replicate - just 'turn it on')
Change notification - have a process you code get notified when data is modified in the database, would be very manual and not suitable for replicating data if that is your goal
Change Notification
A reader, April 15, 2009 - 5:28 pm UTC
Thanks for your responses Tom. So, If my requirement is to update database B whenever there was a change on Database A [not exactly replication or entire database, but few tables], then Can I go with change notification instead of Streams? Can Change Notification be used between databases or is it just between Client [.NET] and a database?
April 15, 2009 - 7:43 pm UTC
but that is exactly replication? "If my requirement is to update database B
whenever there was a change on Database A" - that is exactly 'replication' - whether it be the entire database, a few tables or just a few rows of a few tables or a few columns of a few rows of a few tables - that is 'replication'
And I said change notification would be entirely "not appropriate", materialized views, streams - they would be. Change data capture (CDC) might be.
Change notification
A reader, April 16, 2009 - 8:54 am UTC
Tom, can you elaborate a little more when you said Change Notification is not appropriate for my requirement? we had been having some discussions yesterday and our technical management are more inclined towards change Notifications than using Streams [as they are worried about giving access to Log files] and wanted all developers to go with change notification route. I am not really sure how exactly it will work. If you can give some pointers why we should not use that instead of Streams /CDC, that would be very helpful.
thanks,
April 16, 2009 - 10:01 am UTC
say your process crashes.
you do not get notified.
no one does.
so you miss changes.
game over.
change notification, whereby the database tells your program "this row changed" is not even REMOTELY appropriate.
Change first_scn for an existing capture
streamadmin, May 07, 2009 - 7:30 am UTC
Hi Tom
Could you please shed more light on the subject of changing first_scn value for an existing capture?
We have streams replication environment set up with Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 (archived-log downstream capture). Once in a while archived logs from the source DB are lost or corrupted, so we have to ask the remote office to build the dictionary, provide us with relevant table dumps and tell us necessary SCNs. However, for now that also implies recreating our capture processes from scratch along with all the rules and tedious details as there seems to be no way of explicitly changing first_scn parameter due to restrictions mentioned in the docs (It must be less than or equal to the required checkpoint SCN for the capture process). So, is there any way to tell existing captures they should now start from the new first SCN belonging to the archied log with the dictionary?
May 11, 2009 - 11:32 am UTC
... Once
in a while archived logs from the source DB are lost or corrupted, ....
how so? how does that happen????
and why would you not go to the source system (which MUST have these, else they are not able to recover from media failures) and get them again?????
Change first_scn for an existing capture
streamadmin, May 12, 2009 - 3:50 am UTC
Unfortunately we have no control over the source DB, we can only communicate with its owners by mail/phone. As far as I know, source DB archived logs rotation time is very small so they live fast and die young I'd say :) That's because the source DB is OLTP system with really huge amount of information stored.
Regarding source DB recover from possible media failures - may be the owners employ some other way to prevent this (extensive backups, standby, etc), I'm not aware of the solution...
> and why would you not go to the source system and get them again?????
Most of the time we do exactly that - ask the owners for missing/corrupted logs re-transfer, but sometimes (mostly on weekends and it's not very frequent) there's a situation when needed source DB archived logs aren't accessible anymore, so in this case we have to ask for dumps/SCNs of relevant tables and after having restored the data we have to recreate our replication environment from scratch as we are not aware of any way to just shift first SCN for capture. My googling/metalinking on the subject was for naught, so as a last resort I came here with my question :)
May 13, 2009 - 10:23 am UTC
... we can only communicate
with its owners by mail/phone. ...
you are doomed to a life of misery and complexity. This bird is not going to fly.
In a data sharing (keyword = SHARING) environment, you need more cooperation than that. Period.
... (extensive backups, standby, etc),...
extensive backups without archive log mode are USELESS for media recovery. USELESS (unless of course you never actually open the database - as soon as you OPEN the database - you just lost the ability to do media recovery unless you are in archive log mode)
standby => ARCHIVELOG MODE - you cannot do standby without it.
You will need to "impose" a tad more on the source system - period, nothing else - nothing but nothing else - is going to work.
Change first_scn for an existing capture
streamadmin, May 14, 2009 - 4:42 am UTC
Thanks Tom for your answers. I'm afraid you're right :)
In fact, we have several "external" DBs in our replication environment which use real-time downstream method of transferring redo information and we have never encountered any issue with them. The only troublesome DB is the one where archived logs are registered explicitly (by script) after getting them over FTP connection. Unfortunately we cannot do anything about it, as this method was forced upon us by some mysterious "security-related" decisions made by source DB owners... So, life of misery and complexity goes on and we're bound to stuck with a dead bird for a while, trying to make it fly by applying an unholy resurrective replication every now and then :))
"Viveka", May 21, 2009 - 8:53 am UTC
Hi Tom,
I have bidirectional replication between 3 Databases. When one of them is down suddenly for an extended period of time, the schedule of the propagation job on that DB is seen as DISABLed. (I used the query : select * from v$propagation_sender )
Is there a way to run this schedule continuously even when the DB goes down & is up after, for example, 24 hours? I do not want the schedule to be disabled after 16 attempts. (I read about 16 attempts being the maximum number of attempts)
Thanks.
Followup to "Streams Performance Prediction" above
Bob B, May 28, 2009 - 9:14 pm UTC
For Streams developers:
I just want to say that Streams is plenty FAST for us (all happening in one DB - no downstream DB so no wait for log shipping). Using normal SPs (argument = LCR), we are performing some pretty heavy transformations of data from our OLTP structures to our denormalized OLAPish tables and usually include querying the OLTP DB (big advantage of single DB configuration if possible). Using one Capture and one Apply process. During testing, we are loading our database many times faster than our OLTP's expected loading. Average latency from source commit to target commit is approximately 7 seconds but many LCRs are processed during any 7 second period.
My recommendation to anyone developing a system with heavy transformations - research/test the fastest ways of doing things and write your code to be efficient the first time (10g has some great new features). By doing this, you have additional "room" to add additional transformations at the target DB should you need them.
The bottom line is, at any point in the day where your transactions on the source DB are coming in faster than the Streams LCRs are being processed on the target DB, your Apply queue is going to fill up which of course increases your latency for any change held up in the queue. When transactions slow down again, the queue will empty out.
May 29, 2009 - 8:31 am UTC
My recommendation to anyone developing a system with heavy transformations -
research/test the fastest ways of doing things and write your code to be
efficient the first time (10g has some great new features). By doing this, you
have additional "room" to add additional transformations at the target DB
should you need them.
I loved that.
You could write it as:
My recommendation to anyone developing a system with <any sort of workload> -
research/test the <best> ways of doing things <given your context, your situation, your goals> and write your code to be efficient the first time (10g has some great new features). By doing this, you have additional "room" to add additional <features/functions/whatever> at the target <thing> should you need them.
network latency
ap, June 24, 2009 - 3:25 pm UTC
Tom
I understand your explanations about using Data Guard
vs bi-directional update anywhere replication.
What if there is a latency between the two databases
and I want the users to be able to write to the database
that is closer to them and then have the updates asynchronously propagate to the other site?
Would you still recommend DG in this case?
June 26, 2009 - 10:14 am UTC
I would first ask "why the heck do you have two databases". I would definitely talk you out of two databases.
data guard is for failover, disaster recovery. That is what it does.
bi-directional replication and data guard should never be considered "replacements or alternatives for eachother" They are very orthogonal in nature, they are not equivalent, the same, or even comparable.
ap, June 28, 2009 - 8:34 pm UTC
Thanks Tom.
The reason I'm asking is the Active/Active Data Centers
session from last year's OOW, which seems to suggest that Streams is the "optimal" technlogy.
I understand the complexities as I have implemented both Replicated and Data Guard systems (and I also understand that Data Guard is built on top of Streams), but I keep finding myself having to explain to people who have read that presentation, that replication is not as simple or as robust in terms of data convergence as it seems.
Streams between 10g
A reader, August 20, 2009 - 6:32 am UTC
Hello Tom,
Please help me, and give me instruction implement streams between databases (Oracle 10g)
I have implemented streams between schema table on (10g) and result was success.
Firstly I want to know some thing, such as:
1) Is it possible make streams with conditions, (make only DML or DDL i know) but make DML and not DELETE operation, just INSERT and UPDATE operations on the table?
2) After changes was applied on target database, can I delete that records which copied (replicated) on source database?
I have 2 databases and one of them is for archive purpose (I want use it as target database for streams). Other one is PROD database, applications make DML operation.
I) record insert with null status
II) processing (status 1)
III) if success (status 2) unsuccess (status 3)
For now, I have cron script on (Linux host) and in this script has PLSQL anonymous block and it works couple times during the day. This script works as archive.
My task is: Make it via Oracle streams.
Thank you beforehand.
August 25, 2009 - 8:42 am UTC
streams between 9i and 10g databases
Hashmi, February 24, 2010 - 9:25 am UTC
Dear Tom,
I have a query regrading implememtation of oracle streams among databases of 9.0.0.1 and 10.2.0.1 databases.
Actually I need to update database A on hourly basis from 29 different databases[9.0.0.1]. The problem is when i try to run below code as sysdba on 9.0.0.1, i get no rows selected.
SQL> select owner,object_name,object_type from dba_objects where object_name like ‘%STREAMS%’;
If I do
1 BEGIN
2 DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS();
3* end;
SQL> /
It throws the below error:
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS();
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier ‘DBMS_STREAMS_ADM.MAINTAIN_SCHE’ must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
I know its not the correct way to call the package but just to check whether oracle recognizes DBMS_STREAMS_ADM package or not.
Is it possible to apply oracle streams between 10.2.0.1 and 9.0.0.1 databases? There are 29 different 9.0.0.1 databases that i need fetch data and insert them on 10.2.0.1 database on hourly basis.
Are there any other solutions available? I need a solution that should capture every DML occuring in those 29 different databases and update target database with those changes.
I have heard about Oracle data integrator. Is it possible to use in my scenario?
Many thanks
March 01, 2010 - 8:58 am UTC
streams was introduced in 9iR2 - 9.2.x.
any other solution
Hashmi, March 02, 2010 - 4:32 am UTC
Dear Tom,
Can you guide for any other solution w.r.t. my requirements?
Thanks
March 02, 2010 - 8:31 am UTC
9.0 is not supported software, you will be at huge risk if something goes wrong (and we all know what Murphy as to say about that - google murphy's law).
Anyway, in 9.0 you had advanced mulit-master replication and materialized views, read about those
But, I would really encourage you look at the design. Replicating 29 sites in the year 2010 is not really something you want to do. Maybe in 1990 - before networking. In 2010 - you shouldn't be doing this.
Streams, goldengate or materialized views
oxbow, March 10, 2010 - 1:51 pm UTC
Hi Tom,
I need to replicate one table in several databases (three 11g and one SQL Server). Not all the DB, only one table.
Users can modify the table in any database and changes must be replicated in all databases.
Which is the best option to do that Streams, Goldengate, or materialized views?
Many thaks in advance
March 11, 2010 - 8:07 am UTC
as soon as you add heterogeneous database, goldengate is likely the only answer of the three.
Alexander, March 12, 2010 - 1:55 pm UTC
I can't see using streams ever again now that Oracle has goldengate. Maybe if you really need to customize something since goldengate lives outside the database.
March 12, 2010 - 4:11 pm UTC
perhaps because everyone that owns Oracle owns streams
and GoldenGate is another line item.
I would not count streams out - it does a lot of stuff, it comes with, and if all you need to do is Oracle to Oracle data movement - you probably have everything you need.
Streams
oxbow, March 18, 2010 - 4:06 am UTC
How about customize Oracle Streams to works with SQL Server?
Is that possible? Is an easy work or it is too complicated?
March 18, 2010 - 8:49 am UTC
solories orcale
aruna, March 19, 2010 - 1:42 am UTC
is their any difference b/w simple orcale and solories orcal e if yes plz give me the view . and difference b/w local sol and remote sql
March 19, 2010 - 7:02 am UTC
huh??
what is b/w simple orcale? black and white simple <something?>
plz? german postal codes? what does that have to do with anything?
solories?
Solaris Oracle?
Duke Ganote, March 19, 2010 - 7:30 pm UTC
March 20, 2010 - 9:23 am UTC
I know what he probably meant. Still have no clue what "simple oracle" is.
Ram, March 23, 2010 - 10:19 pm UTC
Hi Tom,
Please advise how to setup streams in real application cluster database. Whether the downstream data capture is the best method for replication in RAC database? It seems I could not find much documents on streams setup in RAC.
Please throw some light on this.
March 24, 2010 - 5:54 am UTC
to: Ram also known as Richard from both FL and china apparently....
from: me
please see the other place you asked this same thing.
Richard, March 25, 2010 - 11:38 pm UTC
Sorry for the duplicate post. One of my friend told me that tom will answer only one question per person. That's why I have posted with another name. Apologize for that.
March 26, 2010 - 3:21 pm UTC
... One of my friend told me that tom will answer
only one question per person. ...
they made that up. It isn't true.
And I know who you were anyway :)
GoldenGate or Oracle Streams
Franklin, March 29, 2010 - 1:07 pm UTC
Hi Tom,
I would like to know your opinion on certain use cases I have
1)Looking at a data synchronization solution, where in which data(comprising of BLOBs also) will be synchornoized between two ORACLE databases.I can use any version of DB since its a new project probably 10g and above.
a)It is NOT a Table-to-Table replication/synching(probably a subset of columns)
b)Would it be advisable to go for Oracle GoldenGate(although the DBs are homogeneous)?
c)Would it be advisable to go for Oracle Streams?
d)There will be expected network outages between the Oracle-to-Oracle databases and hence who would perform better, Oracle GoldenGate or Oracle Streams?
e)Oracle clearly states that it will take the best of Oracle Streams and converge it with Oracle GoldenGate. So on the long run would it be advisable to use GoldenGate?
f)Does DB-to-DB Streams/GoldenGate replication take place with the help of DBLinks(Assuming the Databases are connected over the WAN or Internet)
2)If I have a homogeneous DB environment, which replication should I choose?
a)Oracle GoldenGate(When and Why)
b)Oracle Streams(When and Why)
Sorry for the lengthy question.
Thanks & Regards,
Franklin
April 05, 2010 - 10:09 am UTC
How about this:
if you can do it with streams easily (little to no developed code on your part), probably streams is all you need - since you already have streams.
where Golden Gate comes in nicely is when you have something to do that goes "outside of the box". When you start having to write code to perform things, or start doing things that streams just cannot do (heterogeneous replication for example) - that is where the extra layer above the building block that is streams would come in handy.
either one handles network outages.
either one handles homogeneous databases.
either one can do column subsetting.
so in this case, probably streams is all you need.
actually, if this is a new project and I was involved I would be starting every single design meeting with
"Ok, so tell me again - why are we replicating data in the year 2010? Why are we making things so much more complex than they should be? Whose idea was this again?"
eyad
eyad, March 29, 2010 - 1:39 pm UTC
hi tom,,,
For example, a banking system would require that all databases update each other simultaneously.
Example, when a customer withdrawing money from a branch other than the branch, it must be deducted the amount of sections at the same time, what better way to apply that.
@Eyad
Franklin, March 29, 2010 - 1:59 pm UTC
Having a diffrent database per bank branch seems to be really strange. However I know cases where data is partioned based on branches(physical locations) which make some sense to me. Probably never came across situations where in which you would have a database per branch.
Or am I understanding something wrong here.....
Banking System
eyad, March 30, 2010 - 1:53 am UTC
Thank you for your response,,,
Well, I will have two branches of the Bank of each branch in the city, and also there will be a central database containing all the data section, and in the event that the customer to withdraw the amount of non-branch in the original, it will send the request to the central determining branch customer is in any city, and will withdraw the amount and conservation in both places at the same time, the idea is short, it's in the case of the customer to withdraw an amount from another branch, if you have an idea on the work of other banking systems to help me.
Link the sketch shows the intended
http://www.picamatic.com/show/2010/03/30/06/50/6491564_864x540.png
April 05, 2010 - 10:33 am UTC
it will not work unless you use synchronous replication - at which point you have to really ask yourself "and I'm replicating WHY exactly"??
this screams out for a SINGLE DATABASE. Period. Replication for this would not only unnecessarily complicate the design, it would result in a hugely fragile system that needs care and feeding 24x7. And - it would be pretty slow too. I see nothing to be gained from replication here, only negatives. What positives do you think you might see?
And I have a serious question here: why in the year 2010 are we still writing banking applications? i seem to get questions like this a lot - it seems to me, that these systems already exist and writing one in the year 2010 would be a lot like writing your own HR system from scratch.
@Eyad
Franklin Antony, March 30, 2010 - 12:51 pm UTC
My 2 cents..... I believe , as long as the debit/credit requests are routed to the Central Database there really isnt a problem.
All the branches can just have a View/Materialized view of the customers of their respective branch in their locality. Any new branch comes up, all you do is add in a View/MV or ideally even use the same/existing view.(There is some kind of row level security that Oracle provides, but I cant remeber the name now).
Imagine adding a new database for each branch. I really think in a normal scenario , you wouldnt have to add a new database per branch thereby defeating the purpose of having a database.
Skipping changes in Streams
A reader, April 23, 2010 - 1:55 am UTC
Tom,
We're using CDC (built on Streams from what I can tell) to replicate changes from OLTP system to a data warehouse. There is a need to correct a value in a column as a mass update which will affect many rows, but we don't want to send those changes to the warehouse. So what we're stuck on is how to prevent CDC capturing those changes or make it skip them.
We're thinking we would issue dbms_capture_adm.stop_capture, perform the update, then issue dbms_capture_adm.start_capture, but that doesn't work as it just seems to 'pause' the capture and when it restarts, all the changes are mined from the redo logs.
I'm guessing before we restart, we need to issue dbms_capture_adm.alter_capture to manipulate the SCNs, is that correct, and if so which SCN and how ?
Thanks.
prevent streams from capturing changes
A reader, May 05, 2010 - 3:06 am UTC
Tom, do you know the answer to below and can you point me in the right direction, eg. in manuals or books that can help me ?
Tom,
We're using CDC (built on Streams from what I can tell) to replicate changes from OLTP system to a
data warehouse. There is a need to correct a value in a column as a mass update which will affect
many rows, but we don't want to send those changes to the warehouse. So what we're stuck on is how
to prevent CDC capturing those changes or make it skip them.
We're thinking we would issue dbms_capture_adm.stop_capture, perform the update, then issue
dbms_capture_adm.start_capture, but that doesn't work as it just seems to 'pause' the capture and
when it restarts, all the changes are mined from the redo logs.
I'm guessing before we restart, we need to issue dbms_capture_adm.alter_capture to manipulate the
SCNs, is that correct, and if so which SCN and how ?
Thanks.
Streams apply parallelism and integrity constraints
Venkatesh M, May 11, 2010 - 2:28 pm UTC
We want to increase apply parallelism to 4. There is a referential integrity constraint between the two tables that are being replicated through streams.
Would setting the apply server parallelism to 4 have an impact on the referential integrity ? Is there any chance that this could apply errors ?
Thank you
Venkatesh
Streams vs. Goldengate followup
Kim, May 21, 2010 - 10:30 am UTC
Tom,
I understand your basic reponse to Franklin's question on using Streams or Goldengate - basically being if you need to replicate from Heterogenous databases, then use Goldengate. My question is in regards to your comments about replicating in 2010? For a data warehouse architecture implementing CDC from source to staging area, doesn't that make sense still? Just clarifying...
May 24, 2010 - 12:39 pm UTC
In some cases, flowing data from a read/write system to a read only/reporting system can make sense, yes.
syncronizing data (to me) infers update anywhere - like a palm pilot sync.
lost phone
Stine Angelica Engeskaug, June 13, 2010 - 11:15 am UTC
Daniel Ismael Olsen have lost his phone when we was out in Hamar city in Norway around 04.00 in the morning. if it's are helpful to know that we left in a care to Rudshøgda.and we need to find it before this week are over. can you please help us to find it?
thanks,
Stine Angelica
Good example
Reddy Reddy Gandla, July 22, 2010 - 2:03 pm UTC
Good example
Oracle Streams
Bharat K, September 16, 2010 - 12:30 pm UTC
Hi Tom,
There is an Audit Table of size 50+ GB. We are supposed to implement archive/purge policy for this Audit table. Data <180 days remains in OLTP and data <3yrs should stay in OLAP. I'am wondering which method(streams or materialized views)to adopt, inorder to sync the two databases.
Can you suggest me in picking up the best method(advanced replication through updatable materialized views or Oracle streams) for this case? If so why?
thanks,
Replication in 11gR2,
A reader, October 25, 2010 - 10:15 am UTC
Hello,
I am trying to replicate (one-way) from STRMTST to STRMB databases. I have performed the following steps on STRMTST:
1. Removed streams configuration on both source and target using pl/sql procedure DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION
2. On Source database, I add supplemental logging on all the tables belonging to HR schema (in fact,
it was already setup when I first started working on this)
3. Set up capture queue on source using DBMS_STREAMS_ADM.SET_UP_QUEUE
4. Capture propagation rules for schema HR on source using DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES - disabled it for the time being
5. Add schema capture rule on source using DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
6. Instantiate schema manually by running the following procedure:
DECLARE
iscn NUMBER; -- variable to hold instantiation SCN value
BEGIN
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(schema_name=>'HR',
supplemental_logging =>'all' -- default value is keys
);
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
dbms_output.put_line('iscn value is: '||to_char(iscn));
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@STRMB(source_schema_name => 'HR',
source_database_name => 'STRMTST',
instantiation_scn => iscn,
recursive => TRUE);
END;
7. Create the apply queue at the destination site using DBMS_STREAMS_ADM.SET_UP_QUEUE & DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
8. Start apply process at destination using DBMS_APPLY_ADM.START_APPLY
9. Start the propagation on source using DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE
10. Start the capture process on source using DBMS_CAPTURE_ADM.START_CAPTURE
11. Verified about these jobs:
select apply_name || ' is ' ||status apply_status from dba_apply@STRMB
where apply_name like upper('%HR%')
/
APPLY_STATUS
------------------------------------------
HR_APPLY is ENABLED
select propagation_name || ' is ' ||status apply_status from dba_propagation
where propagation_name like upper('%HR%')
/
APPLY_STATUS
------------------------------------------
HR_PROPAGATION is ENABLED
select capture_name || ' is ' ||status apply_status from dba_capture
where capture_name like upper('%HR%')
/
APPLY_STATUS
------------------------------------------
HR_CAPTURE is ENABLED
However, replication is not happening (both DML and DDL) until I executed the following PL/SQL block:
BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => 'HR',
source_directory_object => NULL,
destination_directory_object => NULL,
source_database => 'STRMTST',
destination_database => 'STRMB',
capture_name => 'HR_CAPTURE',
capture_queue_table => 'STRMADMIN_HR_CAPTURE_QT',
capture_queue_name => 'STRMADMIN_HR_CAPTURE_Q',
capture_queue_user => null,
apply_name => 'HR_APPLY',
apply_queue_table => 'STRMADMIN_HR_APPLY_QT',
apply_queue_name => 'STRMADMIN_HR_APPLY_Q',
apply_queue_user => null,
propagation_name => 'HR_PROPAGATION',
log_file => 'exp.log',
bi_directional => false,
include_ddl => true,
instantiation => dbms_streams_adm.instantiation_schema_network,
perform_actions => true
);
END;
After executing this, I could now see both DML and DDL replicating on to STRMB database.
My question: is calling DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS part of the process? If so, do I still need to perform the CAPUTRE, PROPAGATE and APPLY steps mentioned above?
Thanks,
replication
Ravi B, November 11, 2010 - 5:09 pm UTC
Hi Tom,
We have a database in china and want to replicate the database in USA after close of business in china. Currently our sysadmins do a full export/import by ftp'ing export dump over the network. As our database is growing very large it is always a challenge to transport big files over the network and do an import. Could you please suggest a better option?
Thanks!
November 12, 2010 - 8:40 am UTC
why not just backup and restore???? export/import would be about the slowest way to do it.
Or - use actual replication - like streams, just send the changes over? Send the changes over constantly as they happen so as to not create a huge bottleneck at one point in time
Or - use data guard and have a failover site as well as a reporting site?
replication
Ravi B, November 12, 2010 - 11:14 am UTC
Tom,
If I pick backup/restore option, are you suggesting to take a cold backup of all the data files/control files etc and ftp over and restore in the destination server? I also noticed that our production server is not on archive log mode. I am not a DBA but I was asked to explore different options.
Thanks!
November 12, 2010 - 1:10 pm UTC
cold - no way, hot every time, a hot backup is all it would take.
... I also noticed that our production server is not on archive
log mode. ...
DOH, you will lose all of your work someday - not might, but WILL - it will happen. Maybe tomorrow, maybe next week, maybe next month - but sometime it will happen.
that would preclude you from doing a hot backup of course.
Your DBA team needs to be called out on this big time, this is a huge problem if this data is of any importance, they have let you down.
Alexander, February 16, 2011 - 2:56 pm UTC
Hi Tom,
I wanted to ask you if you think it's possible, or even likely, that repeated WARNING: inbound connection timed out (ORA-3136) messages on an instance that is being replicated to, can be caused by heavy activity on the source instance?
I've checked everything, I don't know what is causing this. There's very few user connections, the listener log has mostly streams messages.
The alert log has stuff like this in it:
VERSION INFORMATION:
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
Time: 16-FEB-2011 13:32:54
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=146.150.41.17)(PORT=37792))
WARNING: inbound connection timed out (ORA-3136)
Wed Feb 16 14:06:39 2011
AP01: warning -- apply server 2, sid 503 waiting for event (since 506 seconds):
Wed Feb 16 14:23:31 2011
AP01: warning -- apply server 2, sid 503 waiting for event (since 1518 seconds):
Wed Feb 16 14:40:10 2011
AP01: warning -- apply server 2, sid 503 waiting for event (since 314 seconds):
Wed Feb 16 14:51:03 2011
AP01: warning -- apply server 2, sid 503 waiting for event (since 967 seconds):
Wed Feb 16 15:10:22 2011
AP01: warning -- apply server 2, sid 503 waiting for event (since 2126 seconds):
Wed Feb 16 15:17:53 2011
AP01: warning -- apply server 2, sid 503 waiting for event (since 2577 seconds):
Wed Feb 16 15:41:58 2011
February 16, 2011 - 3:24 pm UTC
sure, if the source system was so slow responding to the target system - it could time out and that could be caused by high load on the source system making it too slow to respond.
you can up the inbound_connect_timeout in the sqlnet.ora on the target system if you believe it is load related and would like to permit a connection to take more time.
Alexander, February 16, 2011 - 3:32 pm UTC
I felt like a minute (the default) should be plenty of time to establish a connection.
Upon further investigation, I found bug 10297197 but who knows if that's it since there's no information about it.
February 16, 2011 - 5:24 pm UTC
that is not a bug - it has a bug number (everything does), but it isn't a bug.
And it has no bearing on the inbound connection timing out - at all.
Alexander, February 17, 2011 - 8:06 am UTC
Really? "Bug 10297197: APPLY RELATED WARNING MESSAGE APPEAR CONTINUOUSLY" isn't a bug? Interesting. Maybe they're talking about an insect. Also has the exact same messages I posted, and says "WARNING MESSAGE" right in the title, which is what the inbound timeout is. So, did you even look at metalink before writing that last reply?
February 17, 2011 - 11:35 am UTC
yes, I read the entire bug - which you cannot see, it isn't a bug. The 'bug text' says as much. Warnings are written into the alert log all of the time.
That - and there is the fact we were talking about inbound connections timing out - and that has nothing to do with the warnings you see in the alert log at all.
The inbound connection timeout and the warning messages in the alert log are not related and there is nothing in the 'bug' that is not a bug about inbound connections timing out.
the word inbound appears nowhere in that 'bug'
the number 3136 appears nowhere in that 'bug'
the two messages are not related.
Alexander, February 17, 2011 - 11:43 am UTC
So it's coincidental that it appears in the error stack I posted? It sure looks like it's related.
February 17, 2011 - 12:14 pm UTC
You always have lots of stuff intermingled in your alert log.
See the timestamp in front of each of the "warnings", there is probably one in front of the inbound connection too - they represent "i am a new message" - there is a timestamp between the inbound connection and the apply warning - they happened at different times.
Alexander, February 17, 2011 - 12:18 pm UTC
The new 11g xml formats might be messing me up. It looked weird when I viewed it, not what I'm use to seeing (with vi).
Scott Mattes, April 20, 2011 - 10:16 am UTC
I hope that this question is on topic and not destined for the bit bucket!
Is it possible to use downstream archivelog capture to propagate changes between DBs that have an air gap (unidirectional propagation that is)?
April 20, 2011 - 12:40 pm UTC
yes, you can - you'd have to move the archived redo logs yourself and do a bit of adjustment here and there to let us know it has happened. I don't have the step by steps at hand however.
Advanced Queues or Streams
Sumanth, April 23, 2011 - 12:22 am UTC
We have a requirement like whenever user does some operation on a table(insert/update/delete), it should replicate the same into some another tables based on some application functionality. Sometimes it can be 100 records and sometimes it will be some million records. Here all tables will be in the same schema and user is a single user for our testing and at client site there will be some 100-250 users.
Which option you suggest to achieve this, Streams or Advanced Queues?
April 25, 2011 - 11:07 am UTC
sounds more like materialized views, but you don't give much information.
why would you simply copy rows into another table, what is the purpose. You must be doing "something" with them, why do they exist.
upgrade in streams environment
aliyar, April 23, 2011 - 9:36 am UTC
Dear Tom ,
Thanks for your valuable suggestions for US .. it helps us really lot to improve our knowledge in oracle ..
Could you please give me your suggestions on the following requirement
we have BI-Direction streams configuration .
version : 10.2.0.3 and o/s : Solaris sparc 64 Bit. this is not RAC ..
now we decided to upgrade to either 10.2.0.4 or 11gR2
Searched in google and metalink .. could not find out any related links of how to upgrade database which is BI -directional streams enabled ..
can you please share various metods and links
Thanks once again for your service for DBA world
aliyar
April 25, 2011 - 11:44 am UTC
streams works in a multiple version environment, you are able to update one and not the other. So you would just pick one to upgrade.
You should of course test this in your testing environment to ensure there are no unforeseen issues with your pair of versions.
BI - Directional strems upgrade
aliyar, April 25, 2011 - 8:26 pm UTC
Dear Tom ,
Thanks for your suggestions.. we dicided to upgrade both databases to 10.20.5 . steps as follows
1 ) shutdown database01. stop propagation and break db link in database02.so that from database02 no change will be propagated. but capture process will continue to capture chnages in database02 .
2 ) upgrade to 10.2.0.5 and startup database01.start probagation and re-create db link in database02. so now all chnages will be probagated to database01 and both will be in sync ...
after making sure that all changes propagated to database01 proceed with 3rd step as follows
3 ) now shutown the database02. stop propagation and break db link in database01.capture process will continue to capture chnages in database01 .
4 ) upgrade to 10.2.0.5 and startup database02. start probagation and re-create db link in database01.
now both databases upgraded and in sync. BI-directional stremas will continue to work as usually
above steps are sufficient ? or anything i am missing ?..
if possible can you please give me some metalink notes which discuss about steps for database upgrade which bi-directional strams enabled
Thanks for your Help Tom
Aliyar
April 26, 2011 - 7:16 am UTC
Please consult with support on something like this, I cannot really help you develop your upgrade plan. I don't know you or your environment.
I think you are missing the most important step however. That step is:
After doing all of this in TEST, we'll do these steps in production.
I didn't see that step 0 taking place??
Scott Mattes, April 28, 2011 - 9:24 am UTC
I am getting an ORA-01333 when I start my capture. There are 4 archive log files that I manually moved from source to destination (and then I registered them). The third log file (by sequence number) contains the build information (DICTIONARY_BEGIN = 'YES' and DICTIONARY_END = 'YES').
I have tried using both the SCN from before the build (which results in a status of 'waiting') the build SCN (which results in a status of 'aborted').
Should I
1) unregister the two logs before the build and restart, or
2) use a different user for the apply_user (one with more GRANTed permission? if so, which ones are needed?)
Thank you
Streams with Dataguard
A reader, July 27, 2011 - 3:07 pm UTC
I know Dataguard (DG) uses Streams, but can you DG an environment which is already using Streams?
Say I'm already using Streams to replicate table SCOTT.EMP to PAUL.EMP on database1, what happens if I then create a standby database2 using DG?
Will the Streams processes on DB1 get dataguarded to DB2? If so, what happens when I do an insert on SCOTT.EMP on DB1? Streams would replicate this to PAUL.EMP on DB1, but would DG then send inserts to DB2 for SCOTT.EMP and PAUL.EMP? If so, would the streams process on DB2 then try to replicate SCOTT.EMP to PAUL.EMP on DB2 and get a primary key constraint error (assuming EMPNO is PK)?
July 28, 2011 - 7:12 pm UTC
data guard knows it is sending things to a standby and the standby knows it is a standby and it knows not to do things like replicate the changes again - since they've already been replicated.
DG and Streams
A reader, August 03, 2011 - 5:13 am UTC
Thanks Tom, that's reassuring.
Just for my understanding, is this the correct sequence of events?
1) insert into scott.emp on db1
2) redo log captured and applied to paul.emp on db1 (that change also gets recorded in the redo log)
3) redo entry for 1) is applied on db2
4) redo entry for 2) is applied on db2
5) As db2 knows its a standby, change to scott.emp is not "streamed" to paul.emp
6) When db2 switches over to primary, the stream between scott and paul will become activated.
August 03, 2011 - 7:47 am UTC
Yes.
As long as the configuration for the standby matched production at the time of failover (so streams etc are runable)
schema level replication on RAC 11.2 datawarehouse database
yed, August 24, 2011 - 4:02 pm UTC
Hi Tom,
Totally agree replication is used for replication and RAC and DG are used for HA, having said that what options (other than Oracle streams) we can consider to replicate one schema to second schema (one-way) within same RAC 11.2.0.2 datawarehouse database.
First schema is used for ETL and
Second schema is predominantly used for reporting purpose
yed
August 30, 2011 - 3:56 pm UTC
you have materialized views (easiest in my opinion)
you have streams
you have golden gate
you have cdc (change data capture)
I don't understand "First schema is used for ETL ", wouldn't you just be ETL'ing the data and then reporting off of it - why copy it to yet another schema?
DG and Streams
A reader, October 15, 2011 - 1:41 am UTC
1) insert into scott.emp on db1
2) redo log captured and applied to paul.emp on db1 (that change also gets recorded in the redo
log)
3) redo entry for 1) is applied on db2
4) redo entry for 2) is applied on db2
5) As db2 knows its a standby, change to scott.emp is not "streamed" to paul.emp
6) When db2 switches over to primary, the stream between scott and paul will become activated.
Tom,
For the above, we're working a a DR scenario whereby DB1 suffers from failure and we switch over to DB2. This failure occurred after 3).
At this point DB2 has the change applied to scott.emp and now that it has become the "PRIMARY", it will start streaming the change over to paul.emp ?
What happens then when DB1 is brought back up, will the remaining redo log now be sent to DB2 as in 4) ? If so, the apply will fail as DB2 paul.emp already has this change?
October 16, 2011 - 10:03 pm UTC
I don't see any commits in there. So, I'll just assume that all will be well since the insert into scott.emp didn't really happen and will undone.
if I assume every statement is autocommit - that means you lost data - you are not running in "no data loss" mode. When you open your standby, it would pick up where it left off - at that "prior point in time". It will be as if the future history that happened on db1 never happened (it would be like doing a point in time recovery of db1 to the point of time #1 in the above list, the insert into scott.emp has happened). It should be OK
If you run in a no data loss mode, this becomes moot - you would not get into that situation.
(think about distributed transactions - they could be a problem in a "lost data mode" situation though...)
table level replication - schema names are different,
A reader, October 26, 2011 - 1:55 pm UTC
Hello,
I have two databases (D1 and D2) and I want to replicate (one-way) few tables out of a schema (say S1) from database D1 to database D2. However, the schema name in database D2 is different (say S2) but the name of the tables are identical.
Is it possible in streams? I have implemented Streams successfully but in all those cases, the schema name in both the databases were identical.
Thanks,
Initial bulk load,
A reader, January 19, 2012 - 4:14 pm UTC
Hello,
We have set up a one-way replication between Source and Target database for a set of tables within a schema. The initial load of data to the source need to happen before the business starts accessing them. This initial load is up to 200 million rows. The plan is to commit frequently in the batch job (not sure after how many rows but not more than 10,000 rows). This frequent commit is for the purpose of Streams.
Is it good to keep Streams active and replicate all the 200 million rows to target?
The other plan is to turn off Streams (capture, propagation and apply) till we load the 200 million rows on source and manually load the same data in Target. Here, the commit can happen on a bigger set of data. Later turn on Streams. If we chose this plan, will Streams try to catch up with the previous 200 million rows or will it be concerned only the transactions that happen later?
Thanks,
follow up,
A reader, January 20, 2012 - 11:23 am UTC
Thank you. If step 3 doesn't happen automatically, then I can instantiate with the most recent SCN manually.
Going forward, the requirement is to have the replication to happen in less than a second (for each transaction). I can see that happening with small amount of data but when millions of transactions happen daily, do you think it still maintains the pace?
January 20, 2012 - 11:28 am UTC
Going forward, the requirement is to have the replication to happen in less
than a second (for each transaction).
you'll be at the whim of the network gods - if you have such a requirement, you really want a single system - one database - no replication.
follow up,
A reader, January 20, 2012 - 1:22 pm UTC
Thanks. I agree it mainly depends on the network.
Other question.. The current SCN of my source database is way ahead than what capture process is asking for.
SQL> select required_checkpoint_scn cgnum from dba_capture;
CGNUM
------------------
10947060244680
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER cgnum from dual;
CGNUM
------------------
10947243688061
How to tell my capture process to pick up from the most recent SCN? I tried to instantiate the latest SCN on my target database but my capture is requesting for the old SCN.
Thanks,
follow up,
A reader, February 01, 2012 - 9:00 am UTC
Hello,
I haven't encountered this scene before. I found a note in Metalink about recreating capture process when the required_checkpoint_scn is way below the current SCN.
This is what I have:
The current SCN of my source database is way ahead than what capture process is
asking for.
SQL> select required_checkpoint_scn cgnum from dba_capture;
CGNUM
------------------
10947060244680
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER cgnum from dual;
CGNUM
------------------
10947243688061
How to tell my capture process to pick up from the most recent SCN? I tried to instantiate the
latest SCN on my target database but my capture is requesting for the old SCN.
Appreciate your comments.
Thanks,
February 01, 2012 - 9:51 am UTC
tell me what your step by steps where and what the requisite scns are at those steps. Just because the scn's are far apart numerically does not mean they are far apart timewise.
follow up,
A reader, February 01, 2012 - 10:19 am UTC
Thanks for following up.
The required SCN by capture process is dated back in Dec 2nd week 2011. I have noticed that the propagation process was aborted at the same time(probably due to inaccessibility of target database which was down for a while). Later, we noticed archivelog destination on the source database getting filled up. This was due to rman thought Streams requires all those archivelog files.
The DBA manually deleted all those archivelog files (accumulated in the month of December). Now, we want Streams to start capturing from the present moment ignoring all the SCNs in the past.
Appreciate your thought on this.
Thanks,
February 01, 2012 - 11:04 am UTC
but - are the two data sources actually "in sync" or not?
follow up,
A reader, February 01, 2012 - 11:55 am UTC
not in sync and we don't want it to be in sync (since on source, we will be truncating the data and start reloading it again). Only those new data need to be replicated on the other side.
February 01, 2012 - 4:49 pm UTC
umm, so tell me why you want to use streams? You have real data on one end and garbage on the other? Not sure what the goal here is - streams ( replication in general ) is about synchronizing data.
can you backup and at a high level tell us the "goal" - the end state you wish to achieve?
follow up,
A reader, February 02, 2012 - 9:05 am UTC
The goal is to replicate changes to the target database. There are about 8 tables that need replication.
However, the users were doing bunch of testing on the source database which they don't want to replicate to target. The propagation process was aborted and no changes were even attempted to replicate.
Now, we need to start replicating to the target but the capture process is still looking for old SCNs (back in December). Now what I want is to tell the capture process to start replicating from the present moment.
Another question: If I disable all the 3 processes (capture, propagation and apply) and enable them back later (say after a day or so), will the capture process extracts from the time it was enabled or will it go back from the time when it got disabled?
Thanks,
February 02, 2012 - 9:16 am UTC
reinstantiate the copy, the goal of replication is to - well - replicate, syncronize. If you want to use streams out of the box, it will syncronize the two objects.
What you have right now isn't a candidate for replication. You have rows in the source that have no mates in the destination and you have rows in the destination that likely don't have a mate in the source. It will be causing errors constantly.
will the capture
process extracts from the time it was enabled or will it go back from the time
when it got disabled?
the goal of streams is syncronization - it'll want it all.
follow up,
A reader, February 02, 2012 - 9:31 am UTC
Thanks. Re-instantiate the copy? Is that done in the form of recreating the capture process - Steps to Recreate a Capture Process [ID 471695.1] ?
This work apparently happening due to implementing Streams when app team really doesn't require at this time. They should have asked us to implement when they are ready to synchronize the data (not during their testing phase).
Thanks,
February 02, 2012 - 1:57 pm UTC
you basically start over - you want to "skip" everything that happened between now and then - so basically - you want to start "now"
you go through the steps they went through in testing.
Kirtikumar Deshpande, February 02, 2012 - 12:29 pm UTC
If that is the case, I would strongly recommend removing Streams replication completely from current environment. Search MOS for docs that explain how to do it in your version of Oracle.
Configure Streams from scratch when your are ready. Check out the MAINTAIN_* procedures in your version of Oracle to do that.
Also review Streams documentation and MOS notes on how to use the Streams Tag field. It can come very handy to temporarily suspend replication of transactions, if you could execute it separately (and possibly simultaneously) at source and destination database (for example, loading massive data into tables).
Hope this helps..
Kirti
follow up,
A reader, February 02, 2012 - 4:09 pm UTC
Thanks Kirti and Tom for your suggestions.
Kirti - I am curious to know more about TAG feature. Let me find out more on that.
Streams latency,
A reader, February 13, 2012 - 4:09 pm UTC
Hello,
In a one-way replication environment, there are numerous transactions that happen on the source database which eventually be replicated to the target. I want to measure the time taken by Streams to replicate its changes. The tables that I replicate doesn't have timestamp. Are there any other ways (probably using data dictionary) that I can tell Streams took "n" milliseconds to replicate a transaction? The transaction in our case could be more than 1 DML but not more than 100.
Thanks,
follow up,
A reader, February 16, 2012 - 10:35 am UTC
Hello,
Do you have a chance to look at my previous question in this thread? I appreciate your help and time on this.
Thanks,
February 17, 2012 - 5:14 am UTC
not that I am aware of - meaning - there could be, but not that I'm aware of. You might want to ask that one on the streams discussion forum on otn.oracle.com
For stream latency
A reader, February 22, 2012 - 3:12 am UTC
February 23, 2012 - 12:45 am UTC
thanks!
Alexander, March 08, 2012 - 2:40 pm UTC
Tom,
Do you know how I can track down why Oracle reports this when running archivelog backups with delete input:
RMAN-08137: WARNING: archive log not deleted as it is still needed
I have a couple databases bi-directionally replicating to each other from different data centers. I suspect it's for streams somehow but I can't find anywhere to explain it.
Alexander, March 14, 2012 - 11:30 am UTC
Hey Tom, any chance I can get your thoughts on the above? I'm totally stumped. Thank you.
March 14, 2012 - 3:28 pm UTC
see metalink notes:
602960.1
373066.1
dharmendra, March 27, 2012 - 7:43 am UTC
Oracle Streams can be used to share data and messgaes between two oracle databases at different levels. Unlike Oracle dataguard logical standby database ,Oracle streams will have two or more databases independent of each other and similar at replication level only. Concept of Oracle Streams is similar to logical standby but streams is more popular.
more on
http://www.dharamdba.blogspot.in/2012/03/oracle-streams-replication-at-schema.html
streaming
khan, May 21, 2012 - 2:27 am UTC
hi, i am new DBA and on our DB streaming is fully functional, we have 3 major table and how can i check apply process increment or updation?
Alexander, June 05, 2012 - 10:21 am UTC
Hi Tom,
So I've learned a series of web services I support I think has a broken data model. We have bidirectional streams setup for 5 schemas. They allow a user to update a row from either site. So I'm wondering, what would happen if user a updates a row, and during that time user b updates the same row on the other side but makes a different change. Would they both 'lost update' each other?
June 05, 2012 - 1:14 pm UTC
that is call update conflicts.
it is really really REALLY hard for a developer, in a system that automatically detects update conflicts for them, to actually design all of the necessary routines and implement them in a manner that ensures eventual (EVENTUAL) convergence of data at all sites.
it is IMPOSSIBLE to do is the system doesn't detect them, as it sounds like yours does not. You have garbage. You have worse then lost updates, you have incorrect (data integrity wise) updates.
http://docs.oracle.com/cd/E11882_01/server.112/e10706/repconflicts.htm#REPLN410 if you had a bunch of java programmers build an update anywhere replication implementation outside of the database using web services, I feel really bad for you - and even worse so for your data.
Monitoring and managing Stream Performance
Akash, July 08, 2012 - 11:38 pm UTC
Hi Tom,
I am new in Oracle stream. I have a few questions:
1. How can we monitor Oracle stream performance?
2. When can I say that there is a performance problem related to strem.
3. Can you suggest any link for managing streams in Oracle 10g
Thanks,
Akash
July 11, 2012 - 10:52 am UTC
Oracle Stream
Akash, July 23, 2012 - 5:29 pm UTC
Thanks Tom
Oracle Stream
Akash, July 24, 2012 - 1:34 am UTC
Hello Tom,
Following are the wait event statistics in my system:
--------------------------------------------------------------------------------------------------------------------
WAIT TYPE EVENT TOTAL_WAITS TIME_WAITED_SEC PERCENTAGE
--------------------------------------------------------------------------------------------------------------------
CPU DB CPU 10666795.55 36.64
User I/O db file sequential read 783336504 5908001.22 20.29
Other Streams capture: waiting for archive log 866800 5438437.33 18.68
SELECT STATE, capture_name FROM gV$STREAMS_CAPTURE;
-------------------------------------------------------------
STATE CAPTURE_NAME
-------------------------------------------------------------
CAPTURING CHANGES CAPT_RC_PR_LIVE
How to find the real reasons for this wait event(Streams capture: waiting for archive log) ?
Thanks,
Akash
stream replication
A Reader, October 18, 2012 - 7:29 am UTC
Dear Tom,
The scenario we have is
table T is being replicated from database A to database B using.
-- downstream stream capture
-- Table Level replication.
Now table T partitioning scheme is being changed from Range to Hash in database A.
We planned to use Online redefinition to do this partitioning change.
Question/
What is the best method to employ so that there is minimal work, required to be carried out in this stream setup post partition change ?
i.e. is there some way we can avoid redoing the stream setup work for table T post partitioning change to Hash in database A
regards
October 18, 2012 - 8:41 am UTC
you would probably want to just reinstantiate the new table in B - turn off replication, reorg, finish reorg, and then rebuild the copy with the new partitioning scheme.
.. stream replication contd.
A Reader, November 20, 2012 - 6:47 pm UTC
Thanks Tom,
continuing above...
you said
.....rebuild the copy with the new partitioning scheme.
What would be quickest way to re-instantiate the table in standby DB?
table is around 400Gb size.
1. ruled out out exp/imp ( seeing the outage constraint)
2. TTS? ( provided the table is in seprate tablespace).. would it be possible to port the datafile and put that into standby..import the definition.
regards
November 21, 2012 - 9:23 am UTC
you could transport it out of your backups (no downtime) as of a specific SCN and then set the instantiation SCN of the table using DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN
...stream replication contd.
A Reader, November 21, 2012 - 6:00 pm UTC
thanks Tom,
.....you could transport it out of your backups (no downtime) as of a specific SCN
backups you refer here - export dump of the table ?
regards
November 29, 2012 - 6:16 am UTC
..Stream replciation contd.
A Reader, November 21, 2012 - 7:09 pm UTC
hi Tom
further
at time t0 : stop stream replication -- Outage starts here
at time t1 : change the partitioning scheme of the table at source using online redifination
at time t2 : rebuild the copy with the new partitioning scheme the table into target
at time t3 : set the instantiation SCN of the table using DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN at target
at time t4 : starts the stream replciation -- Outage ends here
Outage could be minimized, if , we do pre-work for the step which is done at time t2. ( i.e. taking source table backup)
you mention transporting table from backup using as of a specific SCN.
can this backup of table using as of a specific SCN be done before time t0 ?
suppose answer is "yes"
in that case..
at time t3 if we set the instantiation SCN for the table at target .. it would see source table changed ( recreated ) and would it be possible to sysnc up the target table from the source table in such a scenario?
Hope I have made my understaning clear
regards
Alexander, March 05, 2013 - 1:20 pm UTC
Tom,
Whenever I have some kind of problem the effects streams, I have a terrible time trying to get it working again. Can you tell me (for all processes)
1) How can I prevent the processes from aborting if they hit a few problems that are temporary?
I used
exec dbms_apply_adm.set_parameter('EBR_APPLY','disable_on_error','n');For example, but it hasn't helped for the APPLY, it still dies at the drop of a hat.
2) What views/columns can I look at to tell me whatever it is that's holding it up from becoming functional again?
Example, my capture process keeps aborting, I keep having to run
exec dbms_capture_adm.start_capture('MGT_CAPTURE');Over and over, I have no idea how many problems are preventing it from working.
March 05, 2013 - 2:28 pm UTC
Alexander, March 05, 2013 - 3:05 pm UTC
I could spend the rest of my life clicking around in the documentation's links about streams. There's 50 capture related views/tables. Concise is not in Oracle's vocabulary. I haven't found what I'm looking for.
Alexander, March 06, 2013 - 10:11 am UTC
I don't want to sound ungrateful for your help, but that's not really what I'm looking for. For one thing all of that documentation is only for the apply process, I'm looking for a simple systematic way to determine what's wrong with any process (that's why I gave the capture example).
For the specific question about the apply, yes I've been aware of the DBA_APPLY_ERROR view for a long time. I don't find it useful. That information stays in there forever. Not to mention its unclear to me what each row represents. I also checked my parameters for the apply and it's set to N (which I presume to be no, but they don't tell you what it means) for the disable parameters, so I don't know why it still aborts after problems.
March 06, 2013 - 11:06 am UTC
do you have OEM grid control and do you use it?
If not - you will have to basically read the sections on "how to monitor X" - learn about the relevant views and create a script that satisfies your needs.
I don't have a set of custom scripts for you - short of grid control
ONLY INSERT, UPDATE RULES
javi, September 25, 2013 - 8:44 am UTC
Hi Tom,
I want to know if there is the posibility to create a rule that only allow to propagate a change to the destination table when we make an insert or an update.
I mean, I want to delete a row in the source table and don't aplly the change in the destination table. Is it posible?
I'm trying to do it with advanced rules but but I did not succeed.
Thanks For all,
Javi.
Active-Active database replication with GoldenGate
Charlie 木匠, July 22, 2014 - 1:25 am UTC
Hi Tom,
It looks like the GoldenGate solved the active-active bidirectional replication problem. ha-ha.
"For systems that need near 100 percent of availability, Oracle GoldenGate's active-active database replication is a very strong solution to consider. Active-active replication enables multiple database instances to support the same application. These databases can be located in different regions without distance limitations and can be used for read and write operations. GoldenGate's bidirectional replication capabilities keeps these databases in synch with sub-seconds latency. If one database fails, the other ones can take over processing its transactions seamlessly. In addition to meeting very strict recovery time objectives and recovery point objectives, it enables significant performance and scalability benefits by distributing transactions across multiple databases."
Thanks,
Charlie
Active-Active database replication with GoldenGate
Charlie 木匠, July 22, 2014 - 1:26 am UTC