Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, David .

Asked: December 26, 2000 - 5:53 pm UTC

Last updated: September 16, 2011 - 1:19 pm UTC

Version: Oracle 8/8i

Viewed 1000+ times

You Asked

Hi, Tom

I have a question regarding to synchronous bidirectional replication and advanced replication in Oracle 8i, what is the
difference between them, and how do you decide which one to use?
What about snapshot, can you explain it in your own words and distinguish the usage compare to the above. Thank you.

David Chen.


and Tom said...

Syncronous replication uses triggers to maintain two copies of the data in two different databases at the same time. When I insert into my "local" copy of table T, a trigger will insert that data into the "remote" copy of T as part of my transaction. When I commit -- Oracle will actually do a two phase commit ensuring the data is commited in either both locations or NEITHER location.

Syncronous replication demands that both databases be available, the network is up, and so on. I would not recommend using it. If both sites need some piece of data -- have one of them use a database link to query it from the other database, don't impede all transactions (introducing another potential point of failure for your application) by using syncronous replication.

Asyncronous advanced replication uses a store and forward mechanism. When I insert a row into my local copy of table T, Oracle will store in a queue on the same machine a copy of that transaction. After we commit, Oracle will PUSH that copy of that transaction to the remote site and replay it. Hence advanced async replication uses a store and forward mechanism. If the network is down or the remote database is unavailable, my transactions are just queued up locally and will get propagated when the systems are all available again.

Snapshots are similar to the above but are not stored with "transactional" boundaries. We do not replay each and every insert/update to a row -- we just push over the final result. For this, we use a snapshot log -- this snapshot log is placed on the "master" table. When you insert a row, a row is inserted into the snapshot log with the PRIMARY KEY of the new row and the letter "I" for insert. If you update this row, another row is placed into the log with the primary key and the letter "U". If you delete a row -- its primary key and the letter "D" is stored. When we refresh a snapshot, we use this log to effectively:

o delete by primary key every row in your copy that was UPDATED or DELETED

o insert all of the rows who have a row in the snapshot log with an INSERT or UPDATED

That syncronizes the snapshot copy with the master as of that point in time.

See

</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76959/repover.htm#14080 <code>
for a further discussion of the two types of replication.

Rating

  (65 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.





Tom Kyte
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

Tom Kyte
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


Tom Kyte
January 08, 2004 - 1:00 pm UTC

the script starts with

a) read the documentation

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/index.htm <code>


see the bottom of that page.

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.




Tom Kyte
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 PDF


the 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

Tom Kyte
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

Tom Kyte
January 10, 2004 - 10:55 am UTC

arg, cut and paste issues to the max. ok, here goes, third try

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/index.htm <code>

thats the right stuff and everything is platform independent as far as the database goes.

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..

Tom Kyte
March 01, 2004 - 7:37 am UTC

well, you'd have to pick one of

a) streams
</code> https://docs.oracle.com#index-STR

b) basic read only replication
c) advanced update anywhere replication
https://docs.oracle.com#index-ADV

first -- see

https://docs.oracle.com <code>

for all of the docs.

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

Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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. 

Tom Kyte
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??

Tom Kyte
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

Tom Kyte
April 25, 2005 - 10:42 am UTC

Replication can be achieved via

o materialized views over a dblink (read or read/write mv's)
o master to master replication (peer to peer replication), no mv's
o streams (redo log based), no mv's


MV's can be used with query rewrite, transparently, much like an index. In a single database.


so, replication can use MV's.
MV's can be used to replicate data.

but each have capabilities outside of eachothers domains.

see
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96567/toc.htm
for more replication information and
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#721 <code>
for more MV information

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.

Tom Kyte
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.


Tom Kyte
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.

Tom Kyte
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?



Tom Kyte
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.

Tom Kyte
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?

Tom Kyte
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.




Tom Kyte
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?

Tom Kyte
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?



Tom Kyte
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


Tom Kyte
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

Tom Kyte
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,


Tom Kyte
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


Tom Kyte
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 :-)

Tom Kyte
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.

Tom Kyte
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

Tom Kyte
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!

Tom Kyte
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,

Tom Kyte
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

Tom Kyte
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?


Tom Kyte
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?




Tom Kyte
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.


Tom Kyte
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?

Tom Kyte
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.
Tom Kyte
February 17, 2007 - 8:33 am UTC

that should not happen during a system failure and if it does - it would require media recovery.

there was a discussion about this on oracle-l a while back
http://www.freelists.org/archives/oracle-l/12-2005/msg00537.html

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.?


Tom Kyte
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?

Tom Kyte
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?
Tom Kyte
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?









Tom Kyte
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?

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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

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
Tom Kyte
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

Tom Kyte
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
Tom Kyte
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!
Tom Kyte
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

Tom:

I thought DR plan requires to replicate a database.

If the main database gets knocked out, the real-time *replicated* DB will pick up.

http://blogs.sun.com/dap/entry/deploying_remote_replication
Tom Kyte
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
Tom Kyte
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
Tom Kyte
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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library