Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Standby/Replicated database for reporting functionality
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
> 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: 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).Received on Wed May 07 2003 - 23:51:42 CDT