Question

Tablespace and Datafiles --- Too Many ??

Asked by: and_dev

Hi,
I am planning to move a DW database from a HP-UX 64-bit to a solaris 64-bit environment. The new environment has a SAN with RAID 5.
In my old HP-UX box, I have the usual tablespaces SYSTEM,TOOLS,TEMP,RBS and USERS
and DATA and INDEX tablespaces.

There are about 50 DATA tablespaces and 30 INDEX tablespaces all for the same application. Altogether I have about 104 tablespaces.

My question is :

1) Do I really need the 50 data and 30 Index tablespaces or can I just consolidate all of them into one data and one index tablespaces or atmost 2.(since they all belong to the same application).

2) Does the placement of datafiles and tablespaces really matter on SAN ? I mean does it really matter that I place my data and index files on a different set of disks with a different controller for concurrent accesses ? There are so many conflicting notes on this that I am confused.

3) Does it buy me anything in performance by reducing the number of tablespaces ?

4) How should I layout these tablespaces along with online log and controlfiles assuming that I have about 10 mountpoints for Oracle files.(Lets assume I am going with all 104 tablespaces)

5) The size of the database is 800GB. So another point to consider is if I consolidate all the data and index tablespaces into one/two data and index TS, I will have to probably use large datafiles. Are there any issues if I create a datafile of size 8/16GB ? I will be moving to Solaris 8,64-bit.Other than recovery issues are they any performance issues with large datafiles ?

Any ideas ?

Thanks,

Anand Devaraj

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2003-05-10 at 05:22:40ID20612080
Tags

oracle

,

anand

,

consolidate

,

datafile

,

tablespace

Topic

Oracle Database

Participating Experts
4
Points
20
Comments
8

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. reducing tablespace size
    I've got Oracle 7.3 on HP-UX. I've got a tablespace with about 5 datafiles, each of which are filled only half way (at the most). How do I sortta coalesce the data into the first 1 or 2 datafiles, so that I can drop the others? Or is there another way? Thanks
  2. Deleted datafile before dropping tablespace...help
    Hello, I deleted a datafile before dropping the tablespace - from my sun solaris 8.1.6 oracle databse. Now when I try and drop the tablespace it complains about the datafile... how can I drop the tablespace without the datafile?
  3. tablespaces
    Hi to all again. I am having a problem with my tablespaces. When i created a database i used: create database *name* datafile `/oracle/oradata/system01.dbf` xxm size logfile group 1 ................ after creation i did this create tablespace users datafile `/locatio...
  4. Tablespaces and datafiles
    I would like to know more about the purpose of having datafiles in a tablespace. In other words, if we have a tablespace with two datafiles and we create a table in this tablespace..is the table going to be stored in one of the datafiles?.. and how does oracle go about which ...
  5. Datafile move between different servers
    Hi I have one database on HP-UX box which is about 800GB in size. We are planning to move this to a Solaris box. I am wondering what would be the best way to move this database to a New Solaris Box. I can export the data and import into into a New Database that I woul...
  6. Tablespace and Datafile Layouts
    Hi, I am planning to move a DW database from a HP-UX to a solaris enviroment. The new environment has a SAN with RAID 5. In my old HP-UX box, I have the usual tablespaces SYSTEM,TOOLS,TEMP,RBS and USERS and DATA and INDEX tablespaces. About 50 DATA tablespaces a...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: schwertnerPosted on 2003-05-10 at 06:06:31ID: 8499896

SUN is the name of computr, not SAN.

It is better when the index tablespaces resides on different drive from the table tablespace.

It is easier to manage (backup, recover, change increase parameters, etc') tablespaces that have a lower number of tables.

There is a boundary in Oracle systems, as I remember 4 GB. If you cross this boundary you will run in trouble - see the note bellow:

Does Oracle support files of this size?    
 
Is this a problem with backups?  
 
 
Search Words:  
=============  
 
2GB, 4GB, datafile, big, tablespace
 
Solution Description:  
=====================  
 
Can you create a 4 GB file if your Unix file size limit is 4 GB instead of 2  GB?    
 
No, you must create smaller data file sizes.  
 
Does Oracle support files of this size?    
 
No, not for this version.  
 
Is this a problem with backups?  
 
No, with Oracle handling file sizes larger than 2 GB.  
 
 
Solution Explantion:  
====================  
 
Oracle still uses data files no larger than 2 gigabytes in size.  When allocating a large file use multiple equal size data files.  You only need to create the first, then you can add others to meet your space requirements.  In  
a big tablespace (2GB) create 4 data files of 500 MB each.    
 
Also consider this:  Unix systems have trouble backing up data files that are  larger than 1 gigabyte in size.  So allocate a tablespace's physical data  files in standard sizes.  This will make swapping the data files an easy task  when you begin the tuning cycle for I/O balancing.

 

by: zvfs221Posted on 2003-05-10 at 06:54:12ID: 8500017

Your question is not simple. First of all SAN consists from a transport layer, storage controllers and disks. There are no difference from application point of view where storage controllers resides. Disk is disks - they have limited read and write throughoutput and latency. Of course storage controllers in SAN have internal caches but at end of data travel we see good old disks. Thats why carefully placement policy is important thing.
About tablespace/datafile problems - try to don't mix tables from one application with another in one tablespace.
Performance and backup strategy is arguments for
decision about number of tablespaces/datafile.
BTW this is a good paper about oracle and RAID:
http://www.fors.com/orasupp/unix/38281_1.HTM
2schwertner: 64-bit oracle don't have 4GB limit in datafile size.

 

 

by: csarPosted on 2003-05-10 at 07:04:52ID: 8500045

If think he intentionally wrote SAN which stands for Storage-Area-Network.

If my assumption is right then:

2) No, not really. The idea of SAN is to make the physical layout of the volumes transparent to its users. High End systems perform a dynamic allocation of the data to its physical drives to optimise throughput and response time

To 1)
Of course you can, but the only benefit will be that you need less datafiles. You will loose some flexiblity for example at backup

To 3)
the performance impact is diffcult to predict. But the tendency will be that you'll LOOSE something in performance

To 4)
Not one of my favourie topics. Try putting them all on the SAN and perform some tests. Use the guidelines Oracle gives (Optimimum Flexible Architecure). SANs are usually not the best solution for Oracle DBs, especially for High-Traffic files like the REDOs

To 5)
You haven't stated which Version of Oracle you use. Read the Admin Guides for your Platform carefully. I prefer equally sized datafiles of a reasonable size that are easy to handle. Since you will abolish backup of the RAIDed data many reasons for using small DBFs are not valid any more. But think abput moving your application in 5 years or so. The monster DBFs might backfire on you then!

 

by: schwertnerPosted on 2003-05-10 at 07:53:32ID: 8500170

The placement of datafiles on different disks is a security measure in SAN (not only performance).

Right now I am working in such project as DBA. The security officer asked my company to divide the DB in 5 smaller pieces and to place them in 5 different parts (in fact rooms in the building) of SAN. Why? He suspects that the disks can be physically get stollen. So the assumption is that the intruder will not have enough information to "visit" all 5 rooms and deinstal the drives. So he will not have the whole DB in his hands. In fact the task is much more complex, I dont want to bother you.

But from DBA point of view disks are disks. They crashed, as our friend zvfs221 pointed above. When tracks crashed, the recovery of SMALL dataspaces is easier as of big one. I remember some remarks of Markgeer (the leading expert here) - he first said this here and I agree with him.

 

by: and_devPosted on 2003-05-10 at 13:55:10ID: 8501552

Hi,
  Thanks all for your responses. Yes I meant SAN (Storage Area Network).
Sorry to forget about Oracle Version. We have 8174,but will be moving to 9202.
To gather all the bits and pieces this is what I am concluding. Please correct me If I am off track.

1) I should probably go with the same number of tablespaces even though they belong to the same application. However How it will give me more flexibility in Backups is something that i dont get. I am planning to use RMAN which has nothing to do with number of tablespaces.I will not even have any benefits as to the number of open files. With 50 data tablespaces, if I have 2 files of 4GB for each tablespace, then with 2 data tablespace I will probably have 50 files of 4GB for each tablespace.The recovery granularity for each datafile still remains the same.

With 64-BIT I can create large files (8-16Gb) with absolutely no issues ? Am i right ?

2)Placement of datafiles still remains contentious. I think I will go with the tradition. Split up data and index tablespaces into different set of disks controlled by different controllers.
But will SAN respect this or will it just scatter the file everywhere ?

4)csar , why do you say that SANs are not best for DB ? if you had any issues before please share with us.

Thanks for updating.

Anand

 

by: LWolfsonPosted on 2003-05-12 at 16:13:22ID: 8512842

I came in this a little late, but a few comments.
Your Unix admin has to create your file systems as "large" files systems and he hsa to mount them as "large" file systems to get files greater than 2G created.
Like it was said earlier you should try to get your redo files somewhere other than Raid 5.

You should really consider usiong locally managed tablespaces if you haven't already.  If you're using export don't compress the tables unless you're sure you can allocate something of that size if you're not LMT.

When you're loading the new database you should consider using a special initora file to take advantage of all the resources you'll have available.  Like using a very large sort_area_size and large buffer_pool.




20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...