nandini, March 16, 2001 - 11:41 am UTC
Pichaimani, July 15, 2001 - 11:20 pm UTC
Snapshot Review
Deanna, August 22, 2001 - 11:24 am UTC
I needed to find additional information regarding the syntax of how to create a snapshop, and whether the snapshot log was automatically created or if the user had to create that as well.
The other information presented in the article was definately worth reading
bidirectional replication using triggers
raj bhalla, February 05, 2002 - 6:07 am UTC
hi tom,
in your answer you only explan the way but i found difficulty will doing replication bidirectional using triggers . so pl write some sample code for that .and also explain another possiblities if one server fail then we connected with other server & did work. on such condition how we get transcation data in between this days to another server
Pull from snapshot site
Abu Ahmadh, October 24, 2002 - 4:25 am UTC
"Asyncronous advanced replication uses a store and forward mechanism. After we commit, Oracle will PUSH copy of transaction to the remote site and replay it".
Does it mean the master site requires update/insert privilege on the target snapshot table? Or could it be other way that target site pull from the master site for fast refresh? The reason for this is master site should not update any tables in the snapshot site. Please throw some light on this.
October 24, 2002 - 6:58 am UTC
Using ADVANCED peer to peer replication -- we push.
o you do transaction on database a
o database a queues a copy of the transaction
o you commit
o database A pushes the transaction to database B
o database B applies the transaction
Using snapshots we PULL -- database B would "pull" the changes from database A.
replication
Sanchit Jindal, October 17, 2003 - 12:34 am UTC
Excllent Explanation.
Replication
atul, November 24, 2003 - 4:15 am UTC
Hi,
I'm getting following o/p for following query..
select last_date,this_date,broken from dba_jobs;
LAST_DATE THIS_DATE B
--------- --------- -
23-NOV-03 N
24-NOV-03 N
24-NOV-03 24-NOV-03 N
24-NOV-03 N
24-NOV-03 N
24-NOV-03 N
04-OCT-03 Y
24-NOV-03 N
You can see broken =Y for one row,
How to resolve that,and it shows for old date,why?
Thanks.
Atul
November 24, 2003 - 8:18 am UTC
use dbms_job.broken( :job_no, FALSE )
that is the date the job was last scheduled for, before it got broken.
Good
Satyaki, January 08, 2004 - 12:17 am UTC
Tom
I have got oracle 8.1.7 loaded on two linux servers in different locations.
can u suggest me how can I go for replication between both?
what the script will be?
thanx
Checking it
Satyaki, January 09, 2004 - 12:44 am UTC
thnax
I am checking it, but little confuse. are u telling me to check parallel server setup.
but is it wisable to setup parallel server where both the servers at physically two differnet location connected with VSAT.
January 09, 2004 - 8:28 am UTC
meant:
</code>
http://docs.oracle.com/docs/cd/A97329_03/web.902/a90855/toc.htm <code>
Distributed Databases and Replication
Oracle8i Distributed Database Systems HTML PDF
Oracle8i Replication * HTML PDF
Oracle8i Replication Management API Reference * HTML PDFthe other link was the top level for all 8i docs.
Doubt
Badri, January 10, 2004 - 12:23 am UTC
Hi tom,
Do we use replication for only DML statements?? If we execute a DDL statement, will it automatically execute the same statement in the replicated database too?? Please let me know.
regards
January 10, 2004 - 10:49 am UTC
read the replication guides. with n-way master to master replication you can replicate a schema (ddl) as well as data. you would not simply issue "create", you use the replication api and it'll keep it in sync.
Missing link
Satyaki, January 10, 2004 - 4:43 am UTC
Tom
Sorry to say but the link given by you is of 9i app server documentation.
any way I got those documentations from search and taking notes from it.
But one thing is that they are of oracle 8.1.6 and they are not for linx. Should I use Unix configuration mode.
thanx
Can I do this type of Replication
Piyush, March 01, 2004 - 12:32 am UTC
Hi Tom,
I am some what comfortable with Oracle Server Technology,
I want to learn Replication, for that I have a Windows Machine, and two instance belonging to two databases, Piyush and Piyush1 on same machine. in order to learn i have kept small sizes of db in both, only one tablespace user with four tables, emp, dept, job, grade_mst.
I want to replicate from piyush to Piyush1.
What exactly i should do now..can you please explain me or can you please inform me some link..
thanx in advance..
snapsho with sequence number
Mahomed, April 25, 2004 - 11:34 am UTC
Hi Tom,
I would like to include a sequence number as a new field
in my snapshot. How do I do this?
My snapshop is as follows:
create snapshot snp_palhis
tablespace live_data
build immediate
using index tablespace live_data
refresh fast
on demand
with rowid
as select agvtrk, bastyp, batref, bbfdat
,calwgt, cltcde, cltpon, copdat
,datrcv, dstasl, dstblk, dstcol
from palhis;
So basically, I would like to add a sequnece number ( e.g hstseq.nextval) . How would I do that? My platform is 8.1.7 Standarad Edition.
Thanks
April 26, 2004 - 6:09 am UTC
well, it would not work -- the sequence number would "change" each time (the view would be non-deterministic). the refresh fast would "really not work" if you think about it -- since every row would have to get a new sequence number each and every time.
what are you trying to do - why do you think you need a sequence number there. Instead of the solution -- give us the question or the goal and maybe I can suggest something.
Snapshot with sequence number- followup
Mahomed, April 26, 2004 - 8:21 am UTC
I am trying to identify the entries that are in the snapshot, that were not there before the refresh. This is because I cannot guarantee the uniqueness of entries in the base table which is constantly being updated, deleted or inserted into.
April 26, 2004 - 9:23 am UTC
you cannot do that really. you cannot tell how a refresh happens. even if we put a seq on there, well, you couldn't use it. is an update an update? or a delete+insert? it would be different depending on the implementation.
i don't understand what the uniqueness has to do with anything? you'll be doing rowid based snapshots then (and they will be unique)
still don't have the goal.
Snapshot with sequence number - followup(2)
Mahomed, April 26, 2004 - 11:02 am UTC
Sorry, I have not told you the full story.
I am collecting all the history from the base table into another table called hst_palhis via the snapshot.
So I need to copy all records from snp_palhis that do not yet exist in hst_palhis before I refresh the snapshot. This is because records older than a certain defined period get deleted from the base table (palhis) and this base table is managed by an application that I have no control and I cannot add a new field to the base table.
April 26, 2004 - 2:14 pm UTC
you do not want to use a snapshot then, you have a "custom I need to do it myself" need here -- snapshots are not doing what you want. they cannot keep a history, they contain only rows from the master.
You'll need to "roll your own" in my opinion. are there things that might work in version x.y.z.a.b.c? maybe, but the underlying implementation of the refresh can and will change over time.
Snapshot Replication with Rowid
Suneel Kumar Veeramachaneni, September 08, 2004 - 8:22 am UTC
Hi Tom,
My database is flat Oracle database(No primary Keys for all tables)of version 8.1.7. I want to replicate some tables from source database to target database. I opt for the Snapshot Replication. When i am Generating the replication support or when iam creating the snapshot replication object getting the following error, since iam not having the primary keys on the tables
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
3 GNAME => 'pspdmes_mg',
4 SNAME => 'pspdmes',
5 ONAME => 'carrier',
6 TYPE => 'SNAPSHOT',
7 DDL_TEXT => 'pspdmes.carrier',
8 MIN_COMMUNICATION => TRUE);
9* END;
SQL> /
BEGIN
*
ERROR at line 1:
ORA-23346: primary key is undefined for table or snapshot "PSPDMES"."CARRIER"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_REPCAT_SNA_UTL", line 5386
ORA-06512: at "SYS.DBMS_REPCAT_SNA", line 82
ORA-06512: at "SYS.DBMS_REPCAT", line 1076
ORA-06512: at line 2
I want to do replication with Rowid for all my tables.
I created the snapshot logs and snapshots with rowid option.
Please can you help me in how to replicate the tables which are not having the Primary keys from source to target using snapshot replication.
September 08, 2004 - 10:15 am UTC
[tkyte@xtkyte-pc tkyte]$ oerr ora 23346
23346, 00000, "primary key or object ID is undefined for table or materialized view %s"
// *Cause: Trying to generate replication support for a table or materialized
// view without a primary key (as defined by a constraint or
// dbms_repcat.set_columns) or an object ID.
// *Action: For a table, add a primary key constraint or define a primary
// key using dbms_repcat.set_columns or use object tables.
// For a ROWID materialized view, set min_communication to false
// or use primary key or object ID materialized views.
//
Replication DEF$_AQCALL Issue
M. Masood Qaisar, March 09, 2005 - 6:06 am UTC
I have configured Multi-Master Advanced Replication for 2 master sites (Using Oracle Enterprise Edition 9.2.0.1.0 on MS Windows2K). But I have observed a problem during this week that the size of SYSTEM tablespace has been growing rapidly. After further analysis it was observed that a major portion of the tablespace is covered by DEF$_AQCALL. It has nearly 21,763,626 rows on master definition site and increasing day by day while there are just less that 5,320,400 rows in participating other master sites. Due to this larger size when I try to observe the Error Queue using OEM, It takes more than half hour to just show the Errors transactions list. Please have a look on the following script, I have run:-
SQL> select count(*) from def$_aqcall;
COUNT(*)
----------
21763626
SQL> select count(*) from deferror;
COUNT(*)
----------
3
SQL> select count(*) from deftrandest;
COUNT(*)
----------
1466
Transactions are being propagated normally. But If I have to see the Error Queue to check the error transactions for possible data conflicts, it takes a long time. I have moved this table to USERS tablespace and then move again to SYSTEM tablespace but alas its size doesn't vary. Can I truncate this table i.e. DEF$_AQCALL. As its size is going to be bigger and bigger due to increasing number of rows in it. what should I do ?
Replication
Parikshit Paul, March 21, 2005 - 1:37 am UTC
TOM:
You said
[quote]
1) delete by primary key every row in your copy that was UPDATED or DELETED
2) insert all of the rows who have a row in the snapshot log with an INSERT or
UPDATED
[/quote]
Does it mean that:
1) It will delete which were updated only and not deleted?
2) For every row which was both inserted and updated (according to you two separate rows are kept for the same row with different status), there will two rows in the copy??
Or am i confused??
March 21, 2005 - 10:22 am UTC
it means that in the copy you start by
a) removing all rows from the copy that had been updated or deleted in the primary
b) adding to the copy all rows that had been updated or inserted in the primary
a and b happen to the COPY based on what happened to the PRIMARY
Replication
Jamil, April 25, 2005 - 10:20 am UTC
Hi,
I need some clarification,
What is the difference between replication and metarializes views.
Jamil
Default column values in replication
Khalid, August 02, 2005 - 10:38 am UTC
Tom,
We are trying to set up a master to snapshot replication between two databases. The issue that we are facing is that most of the tables on the Master site will have some default column value. I searched on MetaLink to find out what problems this could create. apparently they have suggested to do an alter command on the materialized view and it is also mentioned that this particular issue has been documented from Oracle 8i onwards. I am not able to find any reference about this problem in 9i documentation. Can you give some suggestions.
The same case seems to be working fine on Master to Master setup.
August 02, 2005 - 1:49 pm UTC
are you talking about updatable snapshots?
default column values in replication
Khalid, August 02, 2005 - 11:27 pm UTC
Thanks Tom,
Currently the snapshots are not updateable. But can you tell me about both the cases i.e. Updateable snapshots and not updateable snapshots.
August 03, 2005 - 10:07 am UTC
if they are not updatable, you won't be inserting into them so DEFAULT doesn't "matter", it is not relevant at all.
Updateable snapshots
Khalid, August 03, 2005 - 10:14 am UTC
Thanks tom,
The MVs are not updateable but I just wanted to know how to handle the issue in case of updateable MVs. If you can give me a link to some relevant documentation then that would be great.
August 03, 2005 - 11:54 am UTC
well, you would just add the default if it were not there and you want it? It is not a big issue. What note specifically are you refering to
Updateable snapshots
Khalid, August 04, 2005 - 1:13 am UTC
Thanks Tom for the response.I just wanted to know whether altering the Snapshots to have default values for columns would work. Now that i know they can be altered so my doubts are resolved.
The resource that i was asking for in particular was a link to the documentation which says exactly what you have said in your previous response.
Thanks a lot!
Updateable Snapshots and Multimaster setup
A reader, August 17, 2005 - 7:28 pm UTC
Hi
I am setting up advanced replication between two sites for some tables.
I am deciding what to use, Asynchronous Multimaster or Master site and a Snapshot site with updateable snapshots. In fact I dont understand the difference between them, let´s say I want to refresh every 4 hours, if I set up my asynchronous replication to refresh every 4 hours what is the difference between that and a snapshot site with updateable snapshots which get refreshed every 4 hours? Is updateable snapshots like multimaster asynchronous setup?
August 18, 2005 - 3:28 pm UTC
Have you read the
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96567/toc.htm <code>
Advanced replication guide?
It details how each is implemented and what they do and how they do it and the material differences between the two techniques.
For a "every 4 hours - DO UPDATE", you are likely looking at materialized views (snapshots)
Which method of replication will best suit me
Syed, September 24, 2005 - 3:50 am UTC
Hi Tom,
My scenario is this: database A has 300 tables of Linux server which is oracle8.1.7 and frequent DML and DDL operation happens in database A.
Database A has to be replicated to database B on Solaris of oracle version 9.2. Which method of replication will best suit? Creating materialized views or asynchronus replication or any other method? I request you to advice me or refer me any link of a best suited method and steps for this scenario.
Thanks in advance.
September 24, 2005 - 8:04 am UTC
materialized views are asynchronous...
I think the best way to do this is "not" actually... but if you really believe you need to replicate from an UNSUPPORTED version (keep that in mind, you are adding a NEW FEATURE to an UNSUPPORTED database, could be the cause of many problems, you might rethink that) basic replication via materialized views is fairly straightforward.
But don't forget you will be impacting that source system A, it'll have materialized view logs to deal with and will be active during the refresh of some 300!!!!! tables)
300+ snapshot log refresh
Syed, September 25, 2005 - 3:08 am UTC
Thanks Tom for the reply. But any user may create any more table at any time in database A which should also be replicated to database B( Currently 300 tables in Database A). Will it be good to create the database B as Standby database for Database A? Will it be better than snapshots?
As 300 snapshot log refresh and any new table added should also has to created with snapshot logs in database A. how user added tables will be snapshoted to database B without manually creating snapshot by DBA?
September 25, 2005 - 9:13 am UTC
I'm going to ask one question "why would you want to do this" - wow? This seems a little "uncontrolled" don't you think?
Agree
Syed, September 26, 2005 - 2:31 am UTC
Tom i agree but the business need is that. Several critical Applications are running pointing to database A. Any time user may create a table or may not. database A is at UK. To enable faster access and to avoid no of users connecting to UK, database B to be created in US for users in US.
And it is decided that database A will be migrated to 9.2. So there is no version difference between Database A and Database B.
September 26, 2005 - 9:14 am UTC
the business need is (in my humble opinion) to access data.
for that, you need not have copies of data.
In any case, there is nothing automagic and thank goodness. This is going to be unmanageable, it will add to the load of BOTH systems (replication isn't free, it has it's associated overheads), this will be dangerous (you are using un-supported software).
Sorry, no magic.
Agree
Syed, September 26, 2005 - 8:31 am UTC
Tom i agree but the business need is that. Several critical Applications are running pointing to database A. Any time user may create a table or may not. database A is at UK. To enable faster access and to avoid no of users connecting to UK, database B to be created in US for users in US.
And it is decided that database A will be migrated to 9.2. So there is no version difference between Database A and Database B.
Generic question on replication
A reader, February 02, 2006 - 10:36 am UTC
Tom,
we need your advice. We have lot of different systems with customer information. Majority of them are Oracle based, some MSSQL, some MYSQL and even mainframe. They were built long ago and there was no plans to have this data synchronized originally. As a result, surely, all the systems have their own version of customer data and, of course, our bosses do not like it. We were asked to do something (and - we have to do it quickly and inexpensively and real time transactional :-)). As a very first goal we have to, at least, synchronize termination status ("y" or "n") for customer's accounts, which can be considered primary key for nearly all of the systems. We started with Oracle based systems and tried to implement synchronious triggers based replication. Very soon we found that any update in any system becomes extremely time consuming. To be honest, we think that these requirements (quickly and inexpensively and real time transactional) are not possible at all. Could you please provide us with your opinion and may be point us to the right direction?
February 02, 2006 - 12:13 pm UTC
... we have to do it quickly and inexpensively and real time
transactional :-) ....
(thanks for the laugh...)
... synchronious triggers based replication...
not only time consuming but it basically means your systems are never up since the failure of ONE system makes the rest inoperable. If you have a need for syncronous replication - you really have a need for CONSOLIDATION.
your conclusion is, in my opinion, dead on.
quickly
cheaply
real time
pick one and only one. quickly will have everything that quickly implies (it'll be ugly, you won't like it, it'll be hard to maintain, you haven't thought it out).
cheaply - see quickly, depending on the scope of the problem - sounds like at least 9 or 10 databases?
real time is not realistic in my opinion at all, unless you consolidate down to a single database.
Generic question on replication
A reader, February 02, 2006 - 12:29 pm UTC
Thanks a lot, Tom! Unfortunately, you are not my boss :-)))
reda, March 14, 2006 - 2:09 am UTC
Hello Tom,
I need to Replication
data from database A to database B. I need to replication Objects Tables
Indexes
Views
Packages and Package Bodies
Procedures and Functions
Triggers
Sequences
Synonyms
,i want to do with Multimaster Replication
which one is the best way?
March 14, 2006 - 10:49 am UTC
which one what? You already picked a single choice here - multi-master replication.
Replication
reda, March 15, 2006 - 2:11 am UTC
Hello Tom,
I need to Replication with multi-master
but i need how setp by step
vip
thanks
March 15, 2006 - 4:36 pm UTC
documentation works for me, how about you?
</code>
http://otn.oracle.com/ <code>=> documentation, it is all there just waitnig to be read.
Can Basic Replication replicate DDL
A reader, May 16, 2006 - 5:23 pm UTC
Hi
I have to setup a Read Only Snapshot Site, replicating around 30 tables.
I have been looking the documentation but does not mention DDL in basic replication environment. Does it mean whenever I add a column in a table in the Master Site I have to recreate the corresponding Snapshot?
Using 9.2.0.7
Thanks
May 16, 2006 - 6:20 pm UTC
No, that is part of advanced replication only.
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (copy_rows => TRUE )
Mike, September 18, 2006 - 1:29 pm UTC
Hi Thom,
I have a question regarding MASTER-MASTER advanced replication configuration.
Let consider this scenario
I have created cold backup of DB1 and restore it to DB2. Let say that this happened at time t1.
At time t2 I want to configure master->master replication.
Let assume that there is a table TABLE1, which has to be included in the replication environment. In point t1 TABLE1 (DB1) had 10 records, but in point t2 it has 15 records. The same table at DB2 has 10 records.
When I run DBMS_REPCAT.CREATE_MASTER_REPOBJECT with copy_rows => TRUE is it going to delete whole content of TABLE1 at DB2 and move all rows from DB1 to DB2, or Advanced Replication is clever enough to determine which record has to be created, updated or deleted.
Thanks,
September 19, 2006 - 2:21 am UTC
you would not start replication that way, you would start with an emtpy "db2" and populate it using replication (or use offline instantiation)
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (copy_rows => TRUE )
Mike, September 19, 2006 - 11:25 am UTC
Thom,
Thanks for your answer.
I havenÂ’t mentioned that DB1 will be 8i little-endian OS and DB2 will be 10g on big-endian OS, therefore it might be difficult, maybe almost impossible to implement offline instantiation.
From the other hand DB1 is pretty big (about 500GB) and most of the tables have to be included in the replication environment. If DB2 is empty, what would be the time required for the data to be moved from DB1 to DB2 over GigE?
What is the way Oracle is using to populate target database table? Is it performing just select over source table and move data over to target table or it is something more sophisticated?
Thanks
September 19, 2006 - 2:50 pm UTC
trace it on a small set of data and all will be revealed (the method, the speed...)
DBMS_REPCAT.CREATE_MASTER_REPOBJECT
Mike, September 19, 2006 - 3:20 pm UTC
Thom,
Thanks for your answer.
That's what I am currently doing and it is very nice though.
Also I think that with this approach we might solve the upgrade challenge that we have (8i->10g) :-)
It seems to me that we can kill two flies with one shot :-)
September 19, 2006 - 3:29 pm UTC
then one is curious why you asked about "what it will do", seems you already know?
DBMS_REPCAT.CREATE_MASTER_REPOBJECT
Mike, September 19, 2006 - 3:59 pm UTC
Thom,
Thanks for your response.
Honestly, I didn't know yesterday.
It is amazing how much a person can learn by reading your columns and comments.
Thanks a lot.
Streams Vs replication
A reader, September 19, 2006 - 4:15 pm UTC
Hi Tom,
Just want to know the difference between Replication vs Oracle Streams.
1. what is the major difference between these two.
2. Are they both update replicate site real time.
3. If i had to use Streams for replication,
Are the rules/prerequistes followed for replication remains the same for Streams too ?
For example, tables must have Primary Key in replication.
September 20, 2006 - 2:39 pm UTC
Oracle streams can be used to replicate data.
Advanced Replication can be used to replicate data.
Into the future (9iR2 and beyond), Streams is going to receive the brunt of attention in the way of new features and so on.
1) streams is redo based, it does not piggy back onto the original transaction, rather it mines the information generated by the original transaction.
Advanced replication piggy backs on the original transaction via triggers (internal triggers). If you insert a row with advanced replication - you really insert a row PLUS queue a message to reinsert that row elsewhere. If you update, you update PLUS queue a message with the before and after images of the row to update. Advanced replication adds to the work the original transaction did, streams - not so.
2) real time is a slippery slope here. They are both capable of NEAR REAL TIME copies - they are BOTH asyncronous (which is a really good thing - it is)
3) pretty much, if by rules/prereqs you mean "I've designed my data to be replicated"
And yes, you should have primary keys and such. But it really goes back to "I designed to replicate", you cannot replicate stuff (update anywhere replicate stuff) that wasn't designed to do so.
How to post a question
Xiaoping Wang, September 19, 2006 - 4:31 pm UTC
I know I should not ask question here, but I just couldn't find the link where I can post my question. You help with this will be highly appricated.
Thanks
September 20, 2006 - 2:41 pm UTC
read the box on the home page - the one that says "sorry, not taking any new questions right now..."
if that box isn't there, you will see the link, if you cannot see the link - that box must be there.
Replication of Developtment environment
A reader, September 19, 2006 - 10:24 pm UTC
Hello Tom. Just would like to know your oppinion about this.
What would be your preferred method to replicate a development environment from production if you have to do it on a daily basis ?
Thanks!
September 20, 2006 - 2:52 pm UTC
restore production to test!
absolutely hands down.
that'll prove to you every single day that you can restore your backups, think about how much better you'll all sleep that way!!
One-way multi-master replication
Mike, September 25, 2006 - 12:09 pm UTC
Tom,
Is it possible to create one-way multi-master replication?
Let's say some tables have to be in bi-directional replication and some not. Is this possible to achieve with Multi-Master replication?
Thanks,
September 25, 2006 - 4:36 pm UTC
sure, just update them in one place
it'll be one way?
did I miss something? (I ask because my answer is not tongue in cheek, it is just "obvious"?)
if you want to "enforce" that (only one place can update), put triggers there to make updates fail on all but one site unless the update is via replication.
One-way multi-master replication
Mike, September 25, 2006 - 5:19 pm UTC
Tom,
Thanks for your response.
Let's consider this situation.
There are two databases DB1 and DB2.
I want to replicate EMP, DEPT, BONUS from DB1 to DB2.
But, from DB2 I want to replicate only changes for table EMP. (This was just an example, maybe not the best one)
If I put triggers on DEPT and BONUS at DB2, I might prevent end users to execute DMLs on these tables, but what about the replication itself. Is it still going to be able to transfer changes from DB1 to DB2 for tables DEPT and BONUS?
So basically DEPT and BONUS should be just "read-only" at DB2, but in sync with DB1.
Thanks,
Mihajlo
September 26, 2006 - 2:22 am UTC
check out the replication guide (a must read before you even START)
Yes, you can write a trigger that incorporates logic that says "let it happen when replication does it, fail it when a user does it"
One-way multi-master replication
Mike, September 26, 2006 - 10:41 am UTC
Tom,
Thanks for your response.
Probably, I haven't read some important things regarding Advanced Replication.
In the Replication Guide I found the solution you were writing about.
It works fine.
Thanks a lot.
snapshot storage systems
abz, December 19, 2006 - 9:14 am UTC
I have recently heard about replicating using the
SNAPSHOT STORAGE SYSTEMS technology. Can you explain
how it works with Oracle?
December 19, 2006 - 10:16 am UTC
why not ask the vendors of such a solution - they would be the experts.
but it won't really replicate (replication typically involves "distance" if you ask me), it'll "clone" a database fast locally.
thanks
abz, December 19, 2006 - 10:37 am UTC
Thank you.
I have read a document from IBM and another document
from ORACLE, titled Guidelines for using Snapshot Storage
Systems for Oracle Databases written by Nabil Osorio and Bill lee from Oracle corporation.
It says that it clones a database while the source is up and running. After cloning you just of to startup the target
database.
I am surprised that how is it possible, because when
the Oracle is up and running , all the data files and other
file are inconsistent, the files are copied from source
to target while source database is up and running which
means files after being copied on target are in inconsistent
state then how will the targe db startup?
December 19, 2006 - 10:49 am UTC
when they break off the snapshot - it is done as an atomic operation - all of the "snaps" are as of the same exact instant.
So, you have a database instance running happily along on its database.
And then you have a database instance running happily along and another set of datafiles (the snap) that just looks like the result of an instance crash. Start up another instance, perform recovery and there you go.
Very good explanation
abz, December 19, 2006 - 11:06 am UTC
Very nice explanation, now I understand it.
But few question:-
1- In the case when my source AIX is using buffered I/O,
the redolog files may have been not actually physically
written, and also the other files, and if the snap is
taken at this time, the target db will not recover.
2- Can the spfile parameter of both database be different
or should it also be copied from source. I am asking
this because my target machine has lesser RAM, and
therefor my target spfile sga parameters have to be
different from source sga parameter. There may be more
different parameters.
December 19, 2006 - 12:50 pm UTC
1) wrong, the writes are not buffered because we open those files with "sync".
If that were true (think about this...) we would NEVER be able to recover after an instance failure - snap or no snap!
2) that is up to you - again, think about how this works. Could you shutdown your production database, change the memory settings, and start it up again? If so, then sure, this snap clone can do the same!
fractured blocks
abz, February 15, 2007 - 5:02 am UTC
Does this snap have chance of having fractured block
at target?
February 15, 2007 - 11:55 am UTC
fractured blocks are only an issue with media recovery, not instance recovery.
fractured block
abz, February 16, 2007 - 1:32 am UTC
During hot backp, the datafiles are copied while
its being read/write, therefore there is a chance that
the state of datafile on our backup device is such that
it may contain fractured blocks, thats why we have
to ALTER DATABASE BEGIN BACKUP before copying datafiles,
so that more redo is generated that will help us recover
from such fractured block containing datafiles.
Why doesnt same is applied to instance recovery,
because an instance may fail while the datafile is
in being read/write.
Very confusing
abz, February 19, 2007 - 2:26 am UTC
A)Can I adopt the following backup startegy.
1-Run Oracle with ARCHIVELOG ON.
2-Make a closed/offline full database backup. (Once only).
3-From now onwards, make hot/online backups of the
database files and control files as follows
a) ALTER DATABASE BEGIN BACKUP
b) Make the snap of the datafiles and control
files using snap technology.
c) ALTER DATABASE END BACKUP.
B) How about we continuously run our database
in ALTER DATABASE BEGIN BACKUP mode.?
February 19, 2007 - 9:28 am UTC
1) yes
2) never necessary, a hot is as good as cold - better even...
3) use rman
and no need for 'begin/end' backup
fractured blocks
abz, February 19, 2007 - 2:44 am UTC
You said
"
that should not happen during a system failure and if it does - it would require media recovery
"
so if it does, we cannot even recover through media
recovery, because its an instance failure, and there
fore the redo entries are still in ONLINE redo logs
which are entries while the database was in END BACKUP
mode. So this means "IF IT DOES" then we have lost data
right?
February 19, 2007 - 9:28 am UTC
you can recover through media recovery - just like you lost a disk.
Oracle XE
A reader, February 28, 2007 - 7:35 am UTC
Can we setup a master site running Oracle express edition, with one or more slave/matreialized view running XE?
February 28, 2007 - 3:44 pm UTC
basic replication is supported (read only materialized views) yes.
Active Active Database Architecture
shahid, March 03, 2007 - 4:53 pm UTC
Hi Tom,
Lately lot of people including IT Managers are talking about Active Active Database scenerios for load balancing, leveraging costs, HA, scalability etc. This is becoming more apparant now that we have high tech network lines and HA machines like IBM P590 series. I have gone through your site and do concur with what you have explained, however I will need your valuable suggestions that a non-dba person can also understand. DBAs are usually (correct me of am wrong) seen as people who at first upfront likes to say No to a question which needs setup which is more likely to affect db performance, difficult to managed, overhead on admin. But these sxcuses are no more accepted from management point of view.
Question1:
You(dba) have a typical OLTP database with say avg load 200-300 trans per sec. The database server is an AIX high end server with abduntant of resources. You are then given another similar server and asked to implement an active active solution from all angles(os, instance, disks etc).
The network bandwidth between these two is 1GB and no issues because the two buildings are only 2 km away from each other. Now what solutions can one use to implement this type of configuration. I know you will question the very need to have such a requirement but sometimes you need to perform POC to let the obvious known.
Solution1:
I am thinking of implementing Oracle master master replication between two sites. Having implemented this and if all goes well, I should not have an issue. But can you please tell me in plain english what sort of problem i will encounter. Give good no of examples. One example is that i would have conflicting updates which needs to be tacled by the application. Suppose application guys find a solution for this, in that they will accept the contratn violation and accept that record is invalid and will take care of such violations as part of reconcilation to be perfomed after the application trading hrs are completed. Besides this what other issues can come. I mean purely application/business issues not tech issues.
We can also develop a solution where a monitor will check the replication speed and if there is any issue in the network, it will then make the users only connect to site 1 until site2 qued trans are appled or rejected.
We do not have sequences as well.
People are talking a lot abt app changes but actually dont say what is the change(s) like adding a column in every table foe the site value or what??
Solution2:
RAC but with extension, i,e, stretched clusters.
This solution still entails recovery when the site 2 goes down and comes up after issue is solved, but atleast in this case we do not have any issue with data integirty and no need to change app design.
Solution3:
Can you think of other solutions?
In a nutsheel,
The ides is simple, we do not want single point of failure and at the same time we want a HA solution in that which does not make the second node lies idle and only wakes up when there is a switch over(read only of second node for reports is accepted).
Regards
PS: Have you used Golden Gate replication solution as they claim it can be used for an active active?
March 04, 2007 - 7:00 pm UTC
... DBAs are usually (correct me of am wrong) seen as people who at first upfront likes to say No to a question ...
Oh, that won't be confrontational at all. It is wrong - in general. There are good, bad and mediocre people in every line of work.
Active active works best in a room for HA. There are and will forever be the concept of DR (disaster recovery) sites and they should be hundreds of miles away from each other.
2KM - no DR there.
2KM - clustering - why?
in a room - clustering
across countries - disaster recovery
replication - do not consider it. There is RAC for clustering, there is data guard for DR. Replication - don't go there.
disaster recovery should not be confused with "being up and running". DR is an insurance policy - they cost money, if you need it, do it - and do it right.
shahid, March 05, 2007 - 1:06 am UTC
Hi Tom,
A true DR should be spread over 1000km away. However lest consider that we have a second data center which is 2 km away and we had already deployed machines there which act as our DRC1 + Data centre site 2.
The real drc is indeed in another city 1200 km away and there we have data propagation but with a delay i,e, some databases get repliected via a tape which is sent there due to criticality of application. Some databases are updated at end of every day with a a veritas duplicate method over a leased line. In short that drc will have loss of data in our current scenerio.
Now coming back to the drc1 (which may not satisfy criteria for a drc) but lets say it is a data center 2 for many reasons; which does not mean whole bldg of site is out but means partially. In that case we switch our applications to point to site2(drc1).
Having said that , we already have a data guard async running on that 2km away site. What we are investigating as to make that site also workable by making it active along with main data centre. Solutions are replication and/or clustering.
Now with this in mind, can you reply to my prevous questions pls?
March 05, 2007 - 12:41 pm UTC
true DR doesn't require a pick up truck to physical move equipment.
answer stands: test.
shahid, March 05, 2007 - 2:44 pm UTC
In other words what you are saying is that HA and DR solution can not be combined in one box which I beg to differ.
Anyway If I succeed I will update you on the progress if interested.
Cheers
March 05, 2007 - 4:18 pm UTC
I'd love to see DR in a single box, that would be, well - an indestructable box wouldn't it?
Advanced Replication and before insert triggers
Arun Mathur, June 21, 2007 - 6:20 am UTC
Hi Tom,
I've setup a test table for multi-replication:
SQL> desc t;
Name Null? Type
----------------------------------------- -------- ---------------
T_PK NOT NULL NUMBER(11)
T_VALUE VARCHAR2(50)
The table has a before insert row trigger that populates its primary key value from a sequence:
SQL> select text from user_source
2 where name='T_BIR';
TEXT
-----------------------------------------------------
trigger t_bir before insert on t for each row
begin
select t_seq.nextval into :new.t_pk from dual;
end;
When attempting to multi-master replicate the table between two sites, I see that I'll run into foreseeable problems by having a before insert row trigger in place. I say this because if an application/database session does something like this:
insert into t(t_value) values ('test');
This will result in a 'test' record being present on 2 sites. However, they will have differing primary keys. Therefore, it will not be possible to update the row on one site based on its primary key value, and see the changes occur on the "same" row on the other site. I get an ORA-01403 error on the defcall queue.
I would conclude then that before insert row triggers should not be put in place in tables that are meant for multi-master replication?
Regards,
Arun
June 21, 2007 - 10:41 am UTC
I would conclude that triggers should not be used - period (I have this thing against triggers)
as documented:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14226/repmaster.htm#sthref261 you need to code your triggers "special" so as to NOT DO ANYTHING when being invoked by replication - they already 'fired' on the other site, you shouldn't have them do it again.
two of my least favorite features - replication and triggers - together, ugh.
I answered my own question
Arun Mathur, June 21, 2007 - 10:06 am UTC
Nothing like waking up in the morning with a multi-master replication question pending :)
Regards,
Arun
Interesting
Arun Mathur, June 21, 2007 - 2:46 pm UTC
>> two of my least favorite features - replication and triggers - together, ugh.
I'm familiar with your stance on triggers. However, I didn't realize replication is one of your least favorite features. What is it you don't like about it?
Regards,
Arun
June 22, 2007 - 10:08 am UTC
Difference between Advanced Replication and Updatable Snapshots(MVs)
sansmalli, February 13, 2009 - 12:28 am UTC
Hi Tom,
What is the difference between Advanced Replication and Updateable Snapshots(MVs) with respect to Oracle 10g? And Please explain when to use these technologies.
Thanks
Sansmalli
February 16, 2009 - 10:11 am UTC
updateable snapshots are part of advanced replication.
Advanced replication allows for "update anywhere". I think we should have called it "a really bad idea" instead of advanced replication.
need to replicate subset of table
mohammad, March 24, 2010 - 12:29 pm UTC
I need to replicate subset table data at site db1(bank center)
to site db2(branch) according to branch id, and replicate all table data from site db2 to db1,i mean for same table
what is the best replication can be used
stream ,advanced rep or what
thanks for your help
March 26, 2010 - 11:05 am UTC
update anywhere?
If you are not already intimately familiar with replication and all that is available, your chances of being able to successfully implement update anywhere replication - which is what this sounds like - are very near zero, you'd want to hire someone that has been there, done that to educate you and your team (seriously, update anywhere replication is hard - it impacts every thing about the design of your application and if you are even thinking about using 3rd party off the shelf software - stop).
If you are not update anywhere, just read about materialized views.
Senthil K Shanmugam, November 23, 2010 - 2:51 pm UTC
Hi Tom
I could see that you are giving your valuable time in answering to a group of people to clear their technical doubts they encounter. We appriciate it. We are looking for your kind help whether you would be able to help us in proving any refrence to close an excellent Golden Gate opportunity that we have with our client at CA.
If you feel this is out of the way from what you do, We appologise.
Thanks for your cosideration
Senthil K Shanmugam
November 23, 2010 - 3:43 pm UTC
Your local sales contacts would definitely be able to help you there - they would be your best approach.
replication
Sam, November 25, 2010 - 10:08 am UTC
Tom:
1) Is replication mostly used for Disaster Recovery purposes or it can serve many other purposes?
2) You mention several solutions i list below. Is there a most popular solution now or it *depends*.
1) triggers
2) Store and forward (queuing/streams).
3) Snapshot Log
4) Materialized Views
There is also a product called DataGuard which I think does replication. I am not sure where this falls.
Happy Thanksgiving!
November 25, 2010 - 8:56 pm UTC
1) replication is not disaster recovery.
disaster recovery is not replication.
the two concepts are completely different.
2) It depends is always the only correct answer given a question such as you have posed.
If there were a single best way to do something, we would not have implemented anything else.
DataGuard is a disaster recovery solution. It provides for failover, switchover, switchback and a host of other things that "replication" does not.
replication
Sam, November 26, 2010 - 7:53 am UTC
November 28, 2010 - 2:57 pm UTC
replication has a specific connotation in DB (database) speak. It is a logical copy of a structure from one source to another - done via triggers or log sniffing. It is either one way (someone subscribes to a set of tables, and a set of rows within a table and maybe even a set of columns in a set of rows in a set of tables) or bi-directional (update anywhere). it's goal is to make some subset of one database available in one or more databases.
DR (disaster recovery) has a specific connotation in DB speak. It means in the even of failure, you have a set of tools in place that allow you to instantly resume all of your feature set against a physically consistent set of data elsewhere on the network. It comes with a ton of failover options (that replication does not), it should be done physically (bit for bit, byte for byte) on a homogeneous platform (not requirements of simple replication)
DR is a lot larger than replication. A teeny tiny, minuscule bit of DR looks like replication, but that is about it.
replication is not DR
DR is not replication
they are separate and distinct.
Replication or Materialized View updatable
Carl, September 14, 2011 - 3:32 am UTC
Hi,
I have a scenario for which I have little idea about the feasibility.
In Databases "A1" and "A2", I have the a table A (same table structure, index et all). Now I am want to use A@A1 as master and A@A2 as child in such a way that All changes made to A1 must reflect at A2 whereas all changes made at A2 must not get reflected at A1 but also be available/untouched after push/pull.
is it possible? I do understand this kindof requirement makes data consistency an issue. can you kindly give an idea as to how I can achieve this.
Database is Oracle 10.2.0.4 Enterprise edition.
Many Thanks in Advance.
Best Regards,
Carl
September 14, 2011 - 7:28 pm UTC
it is not with out of the box replication, the sole entire goal of replication is - to ensure that all copies are synchronized.
Think about this - how could this even work if you write it yourself.
I update row 1 at A1 (that has to go to A2).
I update row 1 at A2 (that cannot go to A1).
Now, what happens?
Replication or Materialized View updatable
Carl, September 15, 2011 - 10:09 am UTC
Hi,
Thank you very much for the reply. Since this is an application based scenario, we know for certain that certain column modifications in one particular table(A@A2) and table inserts in another table(B@A2) will only be applicable in the child instance(A2) and not at Master instance(A1) and similar updates can happen in databases A3,A4,A5.
to give an example, AA councelling centre is HQed in New York(NY) and they have two branches in California and Detroit.
In the present application scenario I go to California branch of AA Councelling Centre and register myself for a class. After a few months, I go to Detroit branch of AA Councelling and I register myself and my partner for a joint session.
The details are stored in the database as follows.
The California backoffice logs into NY application(database/central instance) and updates the general customer details(Tables A,B,C,D). The data gets replicated to both California and Detroit database by End Of Day and the next day they update the details of the class. This detail is stored only in California DB, but it updates the number of sessions registered coumn by 1 in the Table A replicated from central instance.
Similarly for Detroit, they update the number of sessions as well along with that the Detroit address is inserted into Table D replicated from central instance but with a different key related to this application.
There are three applications which have common customer details.
Ideally I would move these dependencies to a separate table and use read-only MVs, but this being an
application based scenario, I am stuck with this but I guess I will be able to make a strong case for change.
Assuming I convince the client with the two way replication/MV, do u think use of updatable MVs in this scenario is a wise decision. I read mixed reviews about the use of Materialized views in a live application environment.
There is a possibility addition/modification of 8000 customers per day.
Thank you.
Best Regards,
Carl
September 16, 2011 - 1:19 pm UTC
You'll have to code this yourself. You could use streams and choose which logical change records to apply at each site. You could write your own routines from scratch. But materialized views will not work at all. It will take code.
the sole goal of a materialized view is to make them all the same - they cannot diverge.
Not Good
A reader, September 16, 2011 - 11:18 am UTC
Not great. Just kidding