| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Standby/Replicated database for reporting functionality
If you are using 8i. I think the standby database mounted read only for
reporting is, by far, the simplest approach that would meet your need for a
reporting database. The production database will archive the redo logs to
the standby database, so you will just have to develop scripts to automate
the nightly recovery and resume read only status. In 9i, if you are only
using the DB for reporting, the logical standby model might make for a
better solution. The logical standby is NOT a disaster recovery tool. It
should only be used to do precisely what you are proposing.
On another note. Our Oracle licensing review required that our standby database, used only for DR, be licensed by the CPU model. The reason, it may not have any/many users logged in, but it is fed by our produciton database and it's many users. In their words, Licensing requirements are determined by evaluating usage at the widest applicable scope. It is a bear, but that is the way our local Oracle mizer interpreted the situation.
Good Luck,
Steve McClure
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Alessandro
Guimarães
Sent: Thursday, May 08, 2003 11:02 AM
To: Multiple recipients of list ORACLE-L
Subject: 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
> 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-----
> > > > > 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
> > >
> ---------------------------------------------------------------------> >
> > > 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: Steve McClure INET: smcclure_at_usscript.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 Thu May 08 2003 - 13:44:16 CDT
 |
 |