RE: Data modeling Tablespace mapping, Re-org

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of BN
Sent: Tuesday, November 30, 2004 9:10 AM To: oracle-l_at_freelists.org
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