Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

3.2 Creating a Database

As
we discussed in Chapter 2, an Oracle installation can have many
different databases. You should take a two-step approach for any new
databases you create. First, understand the purpose of the database,
and then create the database with the appropriate parameters.

3.2.1 Planning the Database

As with installing the Oracle software, you should spend some time
learning the purpose of an Oracle database before you create the
database itself. Consider what the database will be used for and how
much data it will contain. You should understand the underlying
hardware that you'll usethe number and type
of CPUs, the amount of memory, the number of disks, the controllers
for the disks, and so on. Because the database is stored on the
disks, many tuning problems can be avoided with proper capacity and
I/O subsystem planning.

Planning
your database and the supporting hardware requires insights into the
scale or size of the workload and the type of work the system will
perform. Some of the considerations that will affect your database
design and hardware configuration include the following:

How many users will the database have?

How many users will connect simultaneously and how many will
concurrently perform transactions or execute queries?

Is the database supporting OLTP applications or data warehousing?

This distinction leads to different types and volumes of activity on
the database server. For example, online transaction processing
(OLTP) systems usually have a larger number of users performing
smaller transactions, while data warehouses usually have a smaller
number of users performing larger queries.

What are the expected size and number of database objects?

How large will these objects be initially and what growth rates do
you expect?

What are the access patterns for the various database objects?

Some objects will be more popular than others. Understanding the
volume and type of activity in the database is critical to planning
and tuning your database. Some people employ a so-called
CRUD
matrix that contains Create, Read, Update, and Delete
estimates for each key object used by a business transaction. These
estimates may be per minute, per hour, per day, or for whatever time
period makes sense in the context of your system. For example, the
CRUD matrix for a simple employee update transaction might be as
shown in Table 3-1, with the checkmarks indicating that each
transaction performs the operation against the object shown.

How much hardware do I have now, and how much will I add as the database grows?

Disk drives tend to
get cheaper and cheaper. Suppose you're planning a
database of 100 GB that you expect to grow to 300 GB over the next
two years. You may have all the disk space available to plan for the
300 GB target, but it's more likely that
you'll buy a smaller amount to get started and add
disks as the database grows. It's important that you
plan the initial layout with the expected growth in mind.

Prior to Oracle9i,
running out of tablespace in the middle of
a batch operation meant that the entire operation had to be rolled
back. Oracle9i
introduced the concept of resumable space
allocation. When an operation encounters an out-of-space
condition, if the resumable statement option has been enabled for the
session, the operation is suspended for a specific length of time,
which allows the operator to correct the out-of-space condition. You
even have the option to create an AFTER SUSPEND trigger to fire when
an operation has been suspended.

With Automatic Storage
Management (ASM), new in Oracle Database
10g, you can add additional disk space or take
away disks without interrupting database service. Although you should
still carefully estimate storage requirements, the penalty for an
incorrect judgment, in terms of database downtime, is significantly
reduced with ASM.

What are the availability requirements?

What elements of redundancy, such as additional disk drives, do you
need to provide the required availability? ASM also provides
automatic mirroring for data, which can help to provide data
resiliency.

What are my performance requirements?

What response times do your users expect, and how much of that time
can you give them? Will you measure performance in terms of average
response time, maximum response time, response time at peak load,
total throughput, or average load?

What are my security requirements?

Will the application, the operating system, or the Oracle database
(or some combination of these) enforce security?

3.2.2 The Value of Estimating

Even if you are unsure of things such as
sizing and usage details, take your best guess as to initial values
and growth rates, and document these estimates. As the database
evolves, you can compare your initial estimates with emerging
information to react and plan more effectively. For example, suppose
you estimate that a certain table will be 5 GB in size initially and
will grow at 3 GB per year, but when you are up and running you
discover that the table is actually 3 GB, and six months into
production you discover that it has grown to 8 GB. You can now revise
your plans to reflect the higher growth rate and thereby avoid space
problems. Comparing production measures of database size, growth, and
usage patterns with your initial estimates will provide valuable
insights to help you avoid problems as you move forward. In this way,
documented guesses at an early stage are useful later on.

The same is true for key requirements such as availability and
performance. If the exact requirements are not clear, make some
assumptions and document them. These core requirements will heavily
influence the decisions you make regarding redundancy and capacity.
As the system evolves and these requirements become clearer, the
history of these key decision criteria will be crucial in
understanding the choices that you made and will make in the future.

The Automatic Workload Repository (AWR),
new in Oracle Database
10g, maintains a history of workload and
performance measurements, which are used by the Automatic Database
Diagnostic Monitor (ADDM) to spot performance anomalies. You can also
use AWR to track ongoing changes in workload.

3.2.3 Tools for Creating Databases

There are two basic ways to create an Oracle database:

Use the GUI Oracle Database Configuration Assistant.

Run character-mode scripts.

Oracle
ships with a GUI utility called the Oracle Database Configuration
Assistant. It is written in Java and therefore provides the same look
and feel across platforms. The Assistant is a quick and easy way to
create, modify, or delete a database. It allows you to create a
typical preconfigured database (with minimal input required) or a
custom database (which involves making some choices and answering
additional questions). The Database Configuration Assistant is
typically initially accessed as part of a standard installer session.
The Assistant is shown in Figure 3-2.

Figure 3-2. Oracle Database Configuration Assistant

If you choose to create a database, you can then select the type of
database you want to create, as shown in Figure 3-3 for Oracle
Database 10g. The different types of databases
will be created with different default configuration values.

Figure 3-3. Selecting a database to create

The alternative method for creating a
database is to create or edit an existing SQL script that executes
the various required commands. Most Oracle DBAs have a preferred
script that they edit as needed. In Oracle7 and Oracle8, you executed
the script using a character-mode utility called Server Manager;
since Oracle8i, you could use SQL*Plus. The
Oracle software CD-ROM also includes a sample script called
BUILD_DB.SQL,
described in the Oracle documentation. Today, most users choose to
create the database with the standard installer interface.