Creating an Oracle10G Multi-Terabyte Data Warehouse Database

Database Creation Hints and Tips
Before we review the CREATE DATABASE DDL statement we'll be using to create our warehouse, let's cover a few guidelines that will increase our chances of successfully creating a database on the first try.   Hey, no SGTs (Sissy GUI Tools) like DBCA for us, we'll be creating the database the old-fashioned DBA way - manually!  I have always been a proponent of learning command line first.  I will admit that DBCA provides you with a lot of neat "bells and whistles" (templates, error-checking, automatically creating services and password files, etc.) but for this discussion, we'll be using the manual method (insert series of Tim the Toolman Taylor grunts here).

During my career as an Oracle instructor, I have created dozens of Oracle databases and have watched hundreds created by my students.  The Create Database Lab was always one of the most challenging exercises in any of the intro classes.  I've seen a LOT of mistakes made and I am not afraid to say I have made quite a few myself.   I think that's one of the reasons I received good reviews from my students.  I tried to impart 15 years of tips, tricks and techniques so that my students wouldn't make the same mistakes I did. If you believe the adage "We all learn from our mistakes", then you can truly classify me as an Oracle expert.  So, before we review our DDL, I'll provide you with a few hints and tips that will help you improve your chances of successfully creating a database in one attempt:

• Read the "Creating an Oracle Database" Chapter in the 10G Administrator's Guide.  This chapter will provide you with the majority of information you will need to know to successfully create an Oracle10G database.  Follow the "Steps to Create the Database" section like a cookbook and you will be well on your way to success. 
• For those of you creating a database on UNIX and LINUX systems, re-read the "Installation Guide for XXXX Systems" replacing the XXXX with the flavor of the UNIX/LINUX operating system you will be using.   After reviewing the installation guides, turn your attention to the "Administrator's Reference for UNIX Systems". Pay close attention to the sections titled "Setting Environment Variables."  These system variables (ORACLE_HOME, ORACLE_BASE, etc. )will need to be set correctly to ensure a successful database create.
• Avoid common mistakes that are obvious but often overlooked. The first step is to verify that the names of all the directories you'll be using in your CREATE DATABASE DDL statement are created on the system. While you're at it, verify that the directories have adequate space allocated to them.  Then review all of the directory and file names you are referencing in your parameter file. Ensure that they also exist and have enough space.  If you are using UNIX/LINUX, verify that the directories have the proper permissions. In addition, you'll have to make sure you have enough semaphores and shared memory allocated.  The "Administrator's Reference for UNIX Systems" will provide you with the details. If you are manually creating a database  on Windows systems, don't forget to execute the ORADIM command to create the Oracle database service.  Last thing - don't forget to set YOUR ORACLE SID!
• Follow OFA naming conventions.  OFA stands for Optimal Flexible Architecture. The OFA standard is a set of naming conventions and configuration guidelines that are designed to improve database performance by distributing the data to reduce I/O overloads and protect against drive failures.   In addition, because OFA standards are well documented by the Oracle Corporation, newly hired DBA and consultants are able to more quickly assume administration responsibilities.
  
• I almost forgot!  If you will be using a password file (to allow remote startups and shutdowns) you'll need to create a password file.   For those of you running Windows systems, ORADIM will do that for you.  DBAs using UNIX/LINUX will run the ORAPWD utility to create the password file. Of course, the DBCA SGT does all that for you...

CREATE DATABASE DDL Statement
Let's take a look at the database DDL statement that DBA Dojonovic executed.

create database "dwqual2"
    user sys identified by XXXXXXX   - (you didn't think I was going to show you these, did you?)
    user system identified by XXXXXX
    maxinstances 1
    maxlogmembers 5
    maxlogfiles  15
    maxdatafiles 2000
    character set "UTF8"
logfile
        GROUP 1 ('/dev/rdwq2redo1_01.01')      size 767m,
        GROUP 2 ('/dev/rdwq2redo2_01.01')      size 767m,
        GROUP 3 ('/dev/rdwq2redo3_01.01')      size 767m,
        GROUP 4 ('/dev/rdwq2redo4_01.01')      size 767m,
        GROUP 5 ('/dev/rdwq2redo5_01.01')      size 767m,
        GROUP 6 ('/dev/rdwq2redo6_01.01')      size 767m,
        GROUP 7 ('/dev/rdwq2redo7_01.01')      size 767m,
        GROUP 8 ('/dev/rdwq2redo8_01.01')      size 767m
 datafile
        '/dev/rdwq2system1.01' size 825m
         extent management local
 sysaux datafile
        '/dev/rdwq2sysaux1.01' size 325m
         extent management local
 default tablespace user_default datafile
        '/dev/rdwq2default.01' size 250m
        extent management local
        uniform size 1m
 default temporary tablespace temp01
   tempfile '/dev/rdwq2temp_01.01' size 51199m
        extent management local
        uniform size 4m
 undo tablespace undo01 datafile
        '/dev/rdwq2undo_01.01'   size 25599m
 

If our file names look a little odd to you, it's because we have found that raw logical volumes continue to provide us with a distinct performance advantage over both JFS and JFS2 file systems.  I know that IBM's JFS2 concurrent I/O feature is supposed to provide I/O performance that is comparable to RAW but we have found no reason (performance or otherwise) that would compel us to migrate from RAW to JFS2.

Since this is our 10G testing database and we utilize EMC storage here at Giant Eagle, we won't mirror our online redo logs.  If we lose or corrupt a member (and its associated EMC mirror), we'll recover the database from the previous night's backup. That's if the trusty ALTER DATABASE CLEAR LOGFILE or ALTER DATABASE DROP LOGFILE GROUP XXXX  commands don't work.  

The SYSTEM, SYSAUX, DEFAULT and TEMPORARY tablespaces are all locally managed.  You'll notice that some of the file sizes are quite large.  Since we will be working with lots of data, we'll need big UNDO and TEMPORARY sort files to support the work we'll be pumping through this environment.  As stated previously, we use EMC which allows our disk administrators to distribute our files on multiple drives to ensure that we don't overheat any single disk device.   With the amount of I/O we push through these systems, I'm surprised that we haven't melted any of them… 
  

Default User Tablespace
Oracle10G allows administrators to specify a DEFAULT TABLESPACE clause in the CREATE DATABASE statement. The clause creates a default tablespace for the database.   Oracle will assign all users to this tablespace that do not have a default tablespace explicity assigned to them during user creation. If this clause is omitted, the SYSTEM tablespace will continue to be used as the default tablespace for the database. If you forget to specify a default tablespace during database creation, you can use the ALTER DATABASE command to set the databases's default tablespace to a non-SYSTEM tablespace.


SYSAUX Tablespace
SYSAUX is a new tablespace that is required in 10G.  The SYSAUX tablespace will store the objects for Oracle features that used to be stored in multiple, different tablespaces. The SYSAUX tablespace consolidates the objects into one easy-to-manage storage area. 

The listing below provides you with the Oracle feature and the name of the tablespace its components used to be stored in:

• Analytical Workspace Object Table (SYSTEM)
• Enterprise Manager Repository (OEM_REPOSITORY)
• LogMiner (SYSTEM) 
• Logical Standby (SYSTEM) 
• OLAP API History Tables (CWMLITE)
• Oracle Data Mining (ODM)
• Oracle Spatial (SYSTEM) 
• Oracle Streams (SYSTEM) 
• Oracle Text (DRSYS)
• Oracle Ultra Search (DRSYS)
• Oracle interMedia ORDPLUGINS Components (SYSTEM) 
• Oracle interMedia ORDSYS Components (SYSTEM) 
• Oracle interMedia SI_INFORMTN_SCHEMA Components (SYSTEM) 
• Server Manageability Components (New in Oracle Database 10G)
• Statspack Repository (User-defined)
• Unified Job Scheduler (New in Oracle Database 10G)
• Workspace Manager (SYSTEM)

Each set of objects that support a particular feature is known as a "Component" in Oracle10G.  Oracle provides a new view, V$SYSAUX_OCCUPANTS that allows administrators to view the amount of space used by each component and the name of the Oracle-supplied stored procedure that can be used to move its objects into and out of the SYSAUX tablespace. These stored procedures allow administrators to store objects for a particular feature in tablespaces other than SYSAUX. 

The query below will provide you with the name of the feature, the schema name that owns the objects, the space used by the feature and the Oracle-supplied stored procedure that can used to migrate the component to a different tablespace.

SELECT occupant_name, schema_name, space_usage_kbytes, move_procedure FROM v$sysaux_occupants;

Next Up
 In our next blog, we'll take a look at the messages generated by our CREATE DATABASE DDL statement and create our application data tablespaces.