Data modelling Tablespace mapping, Re-org
- From: BN <bnsarma@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 30 Nov 2004 10:09:40 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Data modelling Tablespace mapping, Re-org
