Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm a programmer that's being thrown to the DBA/Sysadmin island all by myself with a volleyball that I named Wilson. I'm trying to survive here.

I'm supposed to create a database for an application. The server where this will be running previously had a database for a pretty similar app. I don't know much about Oracle so I reused the ZFS filesystems and left them how they were created (because honestly, I didn't knew why they were created that way, but I'm pretty sure it was for a good reason).

app/oradata_smart_ora1    858M  12.2G   858M  /oradata/SMART/ora1
app/oradata_smart_ora2   7.18M  18.0G  7.18M  /oradata/SMART/ora2
app/oradata_smart_ora3   7.18M  36.0G  7.18M  /oradata/SMART/ora3
app/oradata_smart_ora4   60.6K   400G  60.6K  /oradata/SMART/ora4
app/oradata_smart_redo1   400M  2.61G   400M  /oradata/SMART/redo1
app/oradata_smart_redo2   200M  2.80G   200M  /oradata/SMART/redo2
app/oradata_smart_redo3   200M  2.80G   200M  /oradata/SMART/redo3

Since I reused the filesystems I created my database and placed the controlfiles in the same places where the old database files were (/oradata/SMART/ora1,/oradata/SMART/ora2,/oradata/SMART/ora3). Thinking like MySQL works I created app/oradata_smart_ora4 60.6K 400G 60.6K /oradata/SMART/ora4 specifically to store the database there.

The databases startups and mounts no problem. Now is where I'm stuck. I've read this, this,this,this,this and much more but I still have doubts.

Note that this server will manage with millions/billions records throughout its lifetime.

  1. Now that my Database is created, whats the next step? Create the Schema or Tablespace?
  2. Tablespace Questions: Tablespace datafile(s) is where actual data from tables is stored? how many are needed? Default or Temporary? How much space will I need for it? Autoextend?

I'm really sorry for all these questions but I really want to do this right while following the best practices for Oracle. If you need more information for your answer let me know.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

You've missed one place to get an overview of Oracle: the Concepts Guide. It covers all the major topics (including backup and recovery, which is quite important and doesn't appear in the list of links you've posted).

Whats the next step? Create the Schema or Tablespace?

Both! They're orthogonal. Users are logical entities that access your database. Tablespaces are a storage concept. A user can have access to multiple tablespaces, and a tablespace can store data from multiple schemas. You need both, and you need to grant access to the appropriate tablespace to the users you create. (See e.g. here for the difference between user and schema.)

Tablespace datafile(s) is where actual data from tables is stored?

Yes, all your database's data and indexes are stored in tablespaces. The main storage structures are:

  • Ordinary tablespaces store normal, persistent data. That's going to be the largest part of your database, space-usage wise.
  • Temporary tablespaces store non-persistent data - global temporary tables that get purged at the end of sessions or transactions, temporary storage for things like on-disk sorts, etc.
  • Undo tablespace(s) and redo log files: that's what Oracle uses to provide ACID guarantees.
  • Control files: they describe your database (name, files, log sequence and checkpoint information, even some backup info).

(The system tablespace is an ordinary tablespace, except that you shouldn't store anything in it - consider it as Oracle internal and off-limits for ordinary use.)

In addition, your should take great care of your redo log files, the "most crucial structure for database recovery". They are "hot" (lots of writes) and should be on their own disks/luns.

How many [tablespaces/datafiles] are needed?

As much as you need. There's no general rule here. The number of datafiles will depend on how much data you need to store, operating system limits, Oracle datafile size limits, your storage (hard disks/volumes) constraints, backup/recovery considerations (e.g. having only one humongous Bigfile datafile might not be the best idea), ...

How you structure your tablespaces is up to you too. Having a tablespace per "application" in your tablespace can be good approach to get started. You can always create more tablespaces later if needed (but keep in mind that moving an object from one tablespace to another can be time-consuming, and might require either downtime or pretty complex operations).

Default or Temporary?

Both! You need space to store your data persistently, and you also need some amount of temporary storage for your database's operation.

How much space will I need for it?

Anywhere between a few megabytes and several terabytes – only you can know here. To estimate the space you need for a table, create a table with the same structure, fill it up with some sample data (should be more or less statistically representative of what you'll be storing in it) and measure the space usage. Then extrapolate. Don't forget to include the space required indexes (and materialized views)!

Autoextend?

I'd say yes, use autoextend features, but set limits. You probably shouldn't let Oracle try to autoextend past the actual available space on your filesystems. And monitor space usage. (Keep in mind that datafile extension is relatively costly. Don't set the autoextend size too small.)

For ZFS specifically, Oracle has a whitepaper you might be interested in: Configuring ZFS for an Oracle Database (270k PDF).

share|improve this answer
    
Great answer. And also, do not even think of separating indexes and tables into different tablespaces for performance reasons ;) –  David Aldridge Apr 30 '13 at 6:37
    
@DavidAldridge: unless those tablespaces live on different (phyiscal) harddrives. –  a_horse_with_no_name Apr 30 '13 at 7:21
    
@a_horse_with_no_name Not even then! Index and table access are sequential in an operation -- they are not both accessed at the same time during query execution. Where there might be a difference is if segments are more or less partial to multiblock or singleblock reads, but that's too fine a distinction for 99.999% of systems. –  David Aldridge Apr 30 '13 at 9:12
    
@DavidAldridge: interesting. I always thought that would distribute the IO load (and thus improve performance). But thinking about it, it would probably only show an improvement if the query was using parallel execution. –  a_horse_with_no_name Apr 30 '13 at 9:23
1  
@a_horse_with_no_name An old and persistent myth I'm afraid, but the good news is to feel free about putting everything in the same tablespace. Segmenting into different tablespaces is mostly a matter of management -- for example, if you're planning on using tablespace transportation. –  David Aldridge Apr 30 '13 at 9:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.