Re: Standby/Replicated database for reporting functionality

Hi,
For 8i
Basic Replication :
Oracle8i server fully supports bi-directional replication with automated conflict detection and resolution. Supported configurations include a single updatable master site with multiple updatable or read-only snapshot sites. Advanced :
includes basic replication functionality plus multi-master replication, Replication Manager, and parallel propagation to maximize throughput.

9i :
Advanced:
Includes basic replication plus multi-master replication functionality. Advanced Replication includes online DML during replication environment changes, Replication Manager, and parallel propagation to maximize throughput.

Alessandro Guimaraes

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, May 08, 2003 7:57 AM

> Arup
> Yes, basic vs. advanced replication is pretty clear on the extremes. But
> there are features in between, like updateable snapshots. I'm just searching
> for something in writing from Oracle that spells out which replication
> features fall into the "Basic" category and which fall in the "Advanced"
> category.
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Wednesday, May 07, 2003 11:52 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Dennis,
>
> You answered the question. The same page contains information on the
> differences. Basic Replication is Snapshot replication; Advanced is
> Multimaster. And Standard contains BAsic only, not Advanced.
>
> Arup
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, May 07, 2003 11:46 PM
>
>
> > Mark, take a look at the chart at the bottom of this page:
> > http://technet.oracle.com/products/oracle9i/pdf/9idb_rel2_prod_fam.pdf
> > According to this, advanced replication is only for EE, but SE has
> something
> > called basic replication. I've never been able to figure out where the
> > boundary lies.
> >
> > Dennis Williams
> > DBA, 80%OCP, 100% DBA
> > Lifetouch, Inc.
> > dwilliams_at_lifetouch.com
> >
> >
> > -----Original Message-----
> > Sent: Wednesday, May 07, 2003 9:27 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Mark,
> >
> > I searched in vain for the information on whether or not Adv Repl is in
> > Standard Edition. We have all Enterprise Editions, so we had it; but you
> > want to search more in Oracle website or inquire with your (friendly?)
> > neighorhood Oracle Sales Rep to ascertain that.
> >
> > I will address the responses of Ade in this mail too. The usage of MVs in
> > reporting instance will make the process simpler, but in your case, you
> > mentioned the app creates the tables on the fly. Using a snapshot
> > replication environment will not be possible in that case. The trick is to
> > use MM replication and use the dbms_repcat to create these tables in
> > separate nodes automatically via a DDL trigger on the database. The only
> > thing you have to be aware of is the need to quiesce the master, i.e. to
> > stop all transactions for a brief time when the operation is performed on
> > the master table. Use the script provided by Ade, except change
> > copy_rows=>FALSE to copy_rows=>TRUE and eliminate the offline
> instantiation
> > part; it's not necessary and not possible in your case.
> >
> > > In relation to our DDL issue with replication i.e. creating database
> > objects
> > > via the overlying application, could we simply create the objects in
> > > question manually in the reporting node or does the DBMS_REPCAT package
> > need
> >
> > I was under the impression that the table creation process was controlled
> by
> > the app and you had no control over it. No, you do NOT have to create the
> > objects on the reporting node manually; dbms_repcat will do that
> > automatically, and that is why you would automate that ussing DDL trigger.
> > However, if you can use a manual approach for creating tables, you could
> > use snapshot replication. The steps involved are a bit complicated, but
> they
> > can be proceduralized.
> >
> > HTH.
> >
> > Arup Nanda
> > www.proligence.com
> >
> >
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, May 07, 2003 11:07 AM
> >
> >
> > > Hi Mark,
> > >
> > > I'm pretty sure advanced replication is not supported by the standard
> > > edition. However if you are just doing reporting I'd say adv. rep. is
> > > overkill. Have you looked into the use of snapshots ?
> > >
> > > Regarding the DDL question, you can create objects manually and then
> > > integrate them into the replication framework, see below...
> > >
> > > execute
> > >
> >
> dbms_repcat.create_master_repobject(gname=>'REP_GROUP',type=>'TABLE',oname=>
> > >
> >
> 'YOUR_TABLE',sname=>'YOUR_SCHEMA',copy_rows=>FALSE,use_existing_object=>TRUE
> > > );
> > >
> > > Then do offline instantiation (see Metalink for this, looks more complex
> > > than it really is). Or you can use execute
> > > dbms_repcat.create_master_repobject to create the target objects for you
> > and
> > > shift the data across, however you have to do this during out of hours
> > > otherwise your source/target will get out of sync due to DML changes.
> > >
> > > Ade
> > >
> > > -----Original Message-----
> > > Sent: 07 May 2003 13:42
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Thank you Arup for your information. No decision made yet.
> > >
> > > You mentioned that using multi-master replication you can use different
> > > Oracle versions on the "reporting" node. Does this include Editions too
> > i.e.
> > > Enterprise Edition versus Standard Edition? Using the Standard Edition
> > will
> > > obviously reduce our licensing costs considerably but this is not an
> > option
> > > if we use a Standby Database as Standby Databases are not supported by
> the
> > > Standard Edition.
> > >
> > > In relation to our DDL issue with replication i.e. creating database
> > objects
> > > via the overlying application, could we simply create the objects in
> > > question manually in the reporting node or does the DBMS_REPCAT package
> > need
> > > to be used to notify the replication layer that all DML on this table in
> > the
> > > primary node must be replicated. As tables are created through a
> > controlled
> > > process this would simply be an additional step to the process and
> should
> > be
> > > easy to manage.
> > >
> > > Looking forward to your reply!
> > >
> > > By the way, the overlying application we're using is JDEdwards OneWorld.
> > > Does anybody know if this can be integrated with Oracle's DBMS_REPCAT
> > > package when generating DDL and creating tables/indexes etc.
> > >
> > > Thanks,
> > > Mark.
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: "Arup Nanda" <orarup_at_hotmail.com>
> > > Date: Tue, 6 May 2003 08:49:48 -0400
> > > Subject: Re: Standby/Replicated database for reporting functionality
> > >
> > > Sorry, I saw this late. You might have already decided on an option. If
> > so,
> > > please disregard this message. To create the standby database, you don't
> > > have to use RMAN. However, standby database may not answer your
> > > requirements. You are looking for a read only copy of master database
> for
> > > reporting in as real time as possible. In a managed recovery enabled
> > standby
> > > database, you must stop the recovery and open the database in read only
> > mode
> > > for reporting. For the entire period of reporting, the standby remains
> out
> > > of sync with the production database, which may not be acceptable to
> you.
> > > Besides, if you want to create additional indexes and tables to
> facilitate
> > > the reporting, it's not possible in standby database.
> > >
> > > I feel the best bet is to use a multi-master replication solution where
> > you
> > > will use only one node for production, leaving the other node to
> > reporting.
> > > While the application creates the tables, etc., you could create some
> > > database triggers to capture the events and call appripriate replication
> > > packages to propagate the changes to the other node, transparent to the
> > > application. This will make it as real time as possible. Besides, you
> will
> > > be able to create additionals indexes, summary tables, etc. in the
> > reporting
> > > database; you could even run a different Oracle version on that node.
> > Since
> > > you use only one node for production changes you will not need
> > sophisticated
> > > conflict resolution routines.
> > >
> > > The best option is of course, 9i logical standby database; but I see
> that
> > > you don't have 9i, yet. In the same boat as I am in, for some of my
> canned
> > > applications here.
> > >
> > > HTH.
> > >
> > > Arup Nanda
> > >
> > > > Folks,
> > > >
> > > > many thanks for your comments, tips and pointers.
> > > > Logical standby databases in 9i sound ideal (if stable) but we do not
> > > intend
> > > > to upgrade to 9i for some time. Creating a standby via RMAN is totally
> > new
> > > > to me a sounds interesting too, guess this is a 9i feature? Will look
> > > > further into this also.
> > > >
> > > > Thanks all again.
> > > > Mark.
> > > >
> > > >
> > > > > -----Original Message-----
> > > > > From: Patterson, Mark
> > > > > Sent: 02 May 2003 12:34
> > > > > To: 'ORACLE-L_at_fatcity.com'
> > > > > Subject: Standby/Replicated database for reporting functionality
> > > > >
> > > > > Hello All,
> > > > >
> > > > > I am looking into options for creating a copy of a production
> database
> > > > > (Oracle 8.1.7 Enterprise Edition, Windows 2000) for
> reporting/querying
> > > > > purposes. The database should be hosted on a separate server and the
> > > data
> > > > > needs to be as real-time as possible.
> > > > >
> > > > > The options I've looked into are database replication and a standby
> > > > > database. Custom development of triggers, database links etc is
> > > > > unfortunately not an option.
> > > > >
> > > > > I've ruled out database replication as the overlying application
> must
> > > > > create tables via its own management interface and this cannot be
> > > > > integrated with Replication Manager or DBMS_REPCAT.
> > > > >
> > > > > This leaves me with the standby database option where the database
> can
> > > be
> > > > > in read-only mode throughout the day and managed recovery though the
> > > > > night.
> > > > >
> > > > > Does anybody know if there are other options available to create a
> > such
> > > > > reporting read-only database or similar. (Read-only is not mandatory
> > but
> > > > > is acceptable as updates to this database are not envisaged)
> > > > >
> > > > > Are there licensing implications when using a standby database? The
> > > > > production "master" database is licensed under the processor
> licensing
> > > > > model but the intention is to simply purchase a named user license
> for
> > > the
> > > > > standby "reporting" database as there will be a very limited number
> of
> > > > > users using this database.
> > > > > Does anybody know if this is possible/approved by Oracle?
> > > > >
> > > > > In a nutshell, I am looking to create a real-time copy of my
> > production
> > > > > database as cheaply as possible for reporting purposes only.
> > > > >
> > > > > If anybody has answers to the above or experience of other methods
> of
> > > > > replicating a database it would be great to hear from you.
> > > > >
> > > > > Thanks,
> > > > > Mark.
> > > --------------------------------------------------------------------
> > > This message, including attached files, may contain confidential
> > > information and is intended only for the use by the individual
> > > and/or the entity to which it is addressed. Any unauthorized use,
> > > dissemination of, or copying of the information contained herein is
> > > not allowed and may lead to irreparable harm and damage for which
> > > you may be held liable. If you receive this message in error or if
> > > it is intended for someone else please notify the sender by
> > > returning this e-mail immediately and delete the message.
> > > --------------------------------------------------------------------
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Patterson, Mark
> > > INET: Mark.Patterson_at_organon.ie
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > 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).
> > >
> > >
> >
> > --------------------------------------------------------------------------
> > ----
> > > Live Life in Broadband
> > > www.telewest.co.uk
> > >
> > >
> > > The information transmitted is intended only for the person or entity to
> > which it is addressed and may contain confidential and/or privileged
> > material.
> > > Statements and opinions expressed in this e-mail may not represent those
> > of the company. Any review, retransmission, dissemination or other use of,
> > or taking of any action in reliance upon, this information by persons or
> > entities other than the intended recipient is prohibited. If you received
> > this in error, please contact the sender immediately and delete the
> material
> > from any computer.
> > >
> > >
> > >
> >
> ============================================================================
> > ==
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Adrian Roe
> > > INET: Adrian.Roe_at_telewest.co.uk
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > 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).
> > >
> > >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Arup Nanda
> > INET: orarup_at_hotmail.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > 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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: DENNIS WILLIAMS
> > INET: DWILLIAMS_at_LIFETOUCH.COM
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > 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).
> >
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Arup Nanda
> INET: orarup_at_hotmail.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?Q?Alessandro_Guimar=E3es?=
  INET: alessandro_at_applysolutions.com.br

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).