RE: replication

From: àãø éçéàì <adary_at_mehish.co.il>
Date: Sun, 13 Jan 2002 04:55:32 -0800
Message-ID: <F001.003EEBEA.20020113043018@fatcity.com>

Hello Bill

Replication is not so hard to do.
Just call for Oracle expert.
I needed to do replication and got nowhere FAST. After An oracle expert came, we worked for about 6 hours and I got a script that does synchronic replication
between 2 dB's.

Basically you have to do the following:

GLOBAL NAMES = TRUE (meaning: dblink is the same as global database name, found in v$_database).
KISS: use one username for administrator, propagator and whatever users there is in the docs.
Create replication group.
Create dblink's from each DB to the other.
Use a script like the following to create the admin requests for all your tables: set linesize 200 set heading off set termout off set pagesize 0 set feedback off spool genrep.sql SELECT 'EXECUTE DBMS_REPCAT.CREATE_MASTER_REPOBJECT(oname => ''"'|| TABLE_NAME || '"'' , gname => ''"TEST_REP_HH"'', type => ''TABLE'', sname => ''"HH"'', copy_rows => TRUE, use_existing_object => TRUE);' FROM ALL_TABLES WHERE OWNER = 'HH';

select 'EXECUTE DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(oname => ''"' || TABLE_NAME ||
'"'' , type => ''TABLE'', sname => ''"HH"'', min_communication => TRUE); ' from all_tables
WHERE OWNER = 'HH'; SPOOL OFF 6) You can do replication for existing tables. See parms in the scripts.

7) you can do sync or async replication.

Good luck (you are going to need it!!!!)

Yechiel Adar, Mehish Computer Services
adary_at_mehish.co.il

> -----Original Message-----
> From: Bill Becker [SMTP:beckerb_at_mfldclin.edu]
> Sent: Thu, January 10, 2002 10:56 PM
> To: Multiple recipients of list ORACLE-L
> Subject: replication
>
> Greetings,
>
> I am looking for advice regarding Oracle replication. We are on
> 8.1.6 EE, and will be upgrading to Oracle9 later this year. At that
> time, we also plan to establish another Oracle instance on a
> separate sun machine; 1 instance will serve as a staging area, the
> second will be a production reporting database. We need a way to quickly
> move processed data from the stage instance to the production instance
> on a daily basis.
>
> Methods we have discussed, pros and cons (please feel free to comment):
>
> Export/Import and flat file transfers have been ruled out due to speed.
>
> Transportable Tablespaces:
> Pros: fastest method of moving large amounts of data
> Cons: Constraints - our tables are very integrated, lots of foreign keys,
> just about every tablespace set would have to include a core set of
> reference tables, or the entire thing (500GB) would need to be in
> the
> same tablespace set; not including constraints means re-building
> them
> in the production instance, including indexes for PKs and UKs (I
> think)
> and probably other problems. How do others handle these problems?
> Also, this transfers all data, when only a very small percentage of
> rows (< 1% of total rows) has actually changed that day. Seems
> inefficient.
>
> Oracle Replication:
> Pros: The documentation seems to address our situation, replicating a
> small
> (relative to total db size) batched amount of data daily. (2-4 GB)
> Cons: Looks complex, 2 books (~760 pgs, ~360 pages in Oracle9), 13
> packages.
> No experience with this - How well does it work? Is it difficult to
> set up? Any comments regarding speed? Can replication be set up for
> existing
> tables, or do they need to be re-created and re-loaded as a
> materialized view?
>
> We are also considering another solution, basically borrowing many of
> the ideas from Oracle replication and writing it ourselves. This would be
> a home-grown solution involving table triggers, additional tables to store
> the daily changes, and scripts to propagate the changes over database
> links.
> But before we decide, I wanted to hear what others had to say
> regarding Oracle replication.
>
> Thanks for any advice.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Bill Becker
> INET: beckerb_at_mfldclin.edu
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> This e-mail was scanned by the eSafe Mail Gateway
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
  INET: adary_at_mehish.co.il

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sun Jan 13 2002 - 06:55:32 CST