Oracle-L: Re: Standby/Replicated database for reporting functionality

From: Patterson, Mark <Mark.Patterson_at_organon.ie>
Date: Wed, 07 May 2003 04:41:41 -0800
Message-ID: <F001.0059219F.20030507044141@fatcity.com>

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

Received on Wed May 07 2003 - 07:41:41 CDT