RE: Design/Strategy question
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <Rui.Amaral@xxxxxxxxxxxxxxxx>, <ksmadduri@xxxxxxxxx>, "oracle Freelists" <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 2 Dec 2010 10:39:31 -0500
Having been there in the past, Yes RAC may well keep your app and reporting
happy, especially is you use different nodes for each, but RAC is
1) Expensive,
2) Adds complexity to the database
3) Can become a real problem is implemented wrong (happen here, the worst
behaving and most frequently crashing database was on RAC)
Yes having a separate reporting database does add additional disk space needs,
but if you really don't need everything in the source database consider
materialized views in a separate instance, lessens the disk requirements. Also
you might consider a standby database that's in READ ONLY mode with redo apply
(11g feature) that way you have both a reporting database and a backup copy
should the original server or disk farm crash.
Dick Goulet
Senior Oracle DBA
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Amaral, Rui
Sent: Thursday, December 02, 2010 9:40 AM
To: 'ksmadduri@xxxxxxxxx'; oracle Freelists
Subject: RE: Design/Strategy question
You can also add GoldenGate to offload to another reporting database (I know
some think of the GoldenGate product as being a fad but keep in mind that
Streams will be phased out for GoldenGate in future releases).
I agree with you in terms of it being a bad idea - don't mix the two. One of
the things that can happen (at least that I have experienced) is when a report
writer decides to work on tables being loaded (such as "select for update" or
other things of similar nature that can lock a table or partition) it can cause
the nodes to crash or cause the whole RAC to hang on a deadlock.
Rui Amaral
Database Administrator
ITS - SSG
TD Bank Financial Group
220 Bay St., 11th Floor
Toronto, ON, CA, M5K1A2
(bb) (647) 204-9106
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kumar Madduri
Sent: Thursday, December 02, 2010 9:28 AM
To: oracle Freelists
Subject: Design/Strategy question
Background:
We were single node (database) Oracle Ebiz shop. There was another database
that was created off the Ebiz database that was used for reporting (this
reporting database was built every day).
Management and few senior DBAs thought RAC was 'cool' and that is the way to go
(RAC looks good on resume :) )
In my opinion
1. This is a bad choice. Dont mix OLTP and Reporting.
2. You are accessing the same database and the same data blocks in the end
probably. You would gain in terms of not having additional storage (prior to
this, there were 2 databases and storage requirements were double because the
entire database was recreated eventhough only a small set of schemas were used
for reporting. Another bad design I think but dont want to go there now) but
users of different requirements are competing for the same resources
3. Our ebiz is not really high availabilty (one of the reasons why rac is
implemented is HA) because of the above way in which rac is implemented here.
Plus, in addition, ebiz does not support TAF (in 11i. May be in R12 it does but
I have to check). We can do application load balancing but we are not even
doing that
4. When CPU is pegged on OLTP (ebiz) node, we are trying to move some of the
applications to node 2. But unless done properly this can be disastrous
(example, users go to node 2 for login (pls application controlled through
wdbsvr or dads.conf and again come back to node 1 for launching forms or open
an apex application using pls goes to node2 and user does some DML on the apex
application going to node 2 and comes back to the main page and decides to
launch forms trying to use the data from the apex application which uses node 1
)
Proposed solution:
1. un-rac (go back to non-rac ). RAC is not the right solution for our
requirements because of our requirements to have a ebiz oltp application and a
reporting database. DBAs are opposed to this idea because it is viewed as a
step backward and viewed as chickening out from RAC.
2, For reporting requirement
(a) use streams
(b) use active data guard (additional cost)
(c) use Materialized views which take data off the primary ebiz database
because reporting dont need to use all the 200 + schemas that exist in oracle
applications and may need 4 or 5 schemas. Developers/Users should be able to
give the requirements on exactly what tables are required.
(d) Change data capture.
Are there any other solutions that can be suggested. I wanted to put my ideas
and get a thought from the list before I go to management and propose my
solution (regardless of outcome).
Thank you for your time
Kumar
NOTICE: Confidential message which may be privileged. Unauthorized
use/disclosure prohibited. If received in error, please go to www.td.com/legal
for instructions.
AVIS : Message confidentiel dont le contenu peut être privilégié.
Utilisation/divulgation interdites sans permission. Si reçu par erreur, prière
d'aller au www.td.com/francais/avis_juridique pour des instructions.
Other related posts:
