RE: Data modeling Tablespace mapping, Re-org

[DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>]

I still keep forgetting to reply to all. I sent this reply last night, but
then realized that I needed to post it to the list in case someone could
provide a better answer.

BN
  Since I don't see where anyone has replied to your posting yet, I will
offer a few comments for what they are worth.
1. What your management is saying is that they want to keep the physical
model in the data modeling tool. This is okay, provided they understand how
to map logical attributes to a physical implementation.
2. It is normal practice to store the largest tables in their own
tablespace. Most of us do that as a normal practice. It makes for easier
manageability, like moving a table.
3. You didn't mention whether constraints were enabled on these tables. Not
a big point, but keep that in mind when you are planning any moves.
4. What I would really recommend to management is to purchase the
partitioning option. That offers most of the advantages you are trying to
accomplish, and many more advantages. If you are going to the trouble of
shifting the data around, moving it into partitions would make the effort
more worthwhile.
5. Most of the reasons you give for moving this data relate to shortening
recovery time and you only slightly mention performance. In my mind if you
could provide performance improvement estimates, that would strengthen your
argument. It is also worth checking out to ensure your project wouldn't hurt
performance.
6. You don't mention what the business requirements for recovery time are.
Obviously the OLTP systems must be recovered quickly, but sometimes the
business is willing to tolerate a longer downtime for a reporting server.
Your arguments to management will be much stronger if you understand the
business requirements. 
7. The tone of your discussion seems to imply that failure is very likely.
Is this true in your situation? Heck, I've personally had systems run for
years without a failure. Be sure you've done everything you can to reduce
the possibility of failure. Check out the practices that can help avoid
failure and make some suggestions of this to management. This includes
hardware (like RAID) and configuration (multiplexing control files and redo
logs) and practices (do not make a change on the production server unless
you've made the change on the test server first). If the system is going to
fail each month, then changes to reduce the downtime might be worthwhile. On
the other hand, if you can reduce the probability of failure to once every
10 years, then who cares if it takes an extra day to get the system going
again.
8. Bear in mind that moving data around isn't exactly a zero risk endeavor. 

Dennis Williams
DBA
Lifetouch, Inc.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of BN
Sent: Tuesday, November 30, 2004 9:10 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Data modelling Tablespace mapping, Re-org

Greetings,

I need my fellow DBA's suggestion /feedback with  my plan.

We have 5 online OLTP (24x7)  Database servers  they push the data to
2 reporting Servers.
The reporting Servers have 5 Schema to represent 5 Online servers.
They could have used partitioning, but they are not using one. The 5
schema is like partitioning for us.

The actual problem is in the  reporting servers. Most of the LARGE
tables are located in One  or Two Big Tablespaces (Dictionary Manged).
We are on Oracle Version 817, HP UX boxes.

These tablespaces a have more than 50 datafiles and the number of
datafiles keep growing.
The size of these 2 TBS is more than 150GB, and It can grow. 

I think Recovery of these 2 TBS could be a nightmare, The Recovery
(from Tape) time can easily span to days. During Recovery We have to
stop online servers pushing the data to Reporting servers. Once the
Recovery is complete, we should Syncup the reporting servers with
online servers, more time and effort.

BTW, we  are Archive Log Enabled and we have done DR Tests for Online
servers not for Reporting. I dont think we have that much (Disk)
capacity  to do such a test for Reporting Server with the existing
layout.

Here is what I am proposing (SPLIT EVERY THING to a manageable CHUNK).

(At the End of My Re-org Plan, I want to Group Tables/Tablespaces
based up on Transactions, Size, Type (history, LOGS, Reference tables,
detail Tables etc.,) to minimize Complete Application Down time , Easy
of Administration .)

1. We have already Iidentified these LARGE Tables. Move these LARGE
tables into their own LMTS (TABLENAME_SCHEMA) on on-line and Reporting
Servers. Need to check this in a test box to get an estimate of time
it takes to move (Alter table move, rebuild indexes)

2. Now after the re-org, On-line and Reporting Servers structure looks
alike (Physical and Logical) for a given Large Table or group of
Tables , If I need to recover a LARGE Table, What I need to do is
TRANSPORT the TBS (TTS) as my first option.

3. For some reason If I have to read from Tape backup, I still will be
reading only one TBS (Plus SYSTEM and RBS ), definitely not   150 Gig
Tablespace with 50 Datafiles.

4. Also This helps me for Better maintenance and performance. 

5. At any given point of time, when a TBS  needs Recovery, only Part
of Application services will be down.

6. There are lot more Advantages I see with my Proposal.

7. With one or two TBS/Datafiles Down, only part of the App will be 
down not entire App.

8. While recovering, I can do parallel Recovery of
Tablespaces/Datafiles, saving time

9. If a particular TBS is READ ONLY (HISTORY or LOGS), savings in Backup
.......

10. Partitioning of Tables , later , one step at a time approach....

Like I said before  the advantages listing goes on and on.....

What I don't see is :

Are there any Issues/cons I have to watch out with this Plan. 

Here is what I am hearing from the Management:

<< We cannot move forward with any tablespace reallocations unless it
starts within the DDL process from development all the way to
production.  Tablespaces are defined from within the data model
process, starting from when a table is created. >>

I sort of disagree with this, I feel You should  not include
Tablespace and storage  details in the Data Model. Please correct me
If I am wrong.

I highly appreciate your feed back.


-- 
Regards & Thanks
BN
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Other related posts:

RE: Data modeling Tablespace mapping, Re-org

RE: Data modeling Tablespace mapping, Re-org

RE: Data modeling Tablespace mapping, Re-org