11.3 Creating a Database

---

Like initial Oracle binary software installation, well-structured and thought out initial database creation is essential to a properly physically tuned Oracle database. There are two different methods of creating a database. The first is using the Database Configuration Assistant and the second manually using command line scripts. The Database Configuration Assistant is easy to use and can be utilized to create a set of adjustable scripts. Let's look at the Database Configuration Assistant first.

11.3.1 The Database Configuration Assistant

I generally use the Database Configuration Assistant in two ways: firstly, when creating a database initially using a new version of Oracle Database and secondly, as a database creation script generator.

Tip 

Selecting the New Database option as shown in Figure 11.2 will allow selection of explicit options for database creation, one of the most important being the removal of all the sample tablespaces as shown in Figure 11.3. Extra options shown in Figure 11.4 such as Oracle JVM and Intermedia can also be removed and added later using $ORACLE_HOME/rdbms/admin scripts if required.

Figure 11.2: Choosing the Appropriate Database Template

Figure 11.3: Options and Examples when Creating a Database

Figure 11.4: More Optional Database Features

Many of the steps involved in database creation are database administration and not tuning functions. However, in the interest of creating a properly physically and configured database architecture it is prudent to go through some of the steps of the Database Configuration Assistant.

Make sure you select the correct database creation template. An OLTP database is equivalent to a Transaction Processing database as shown in Figure 11.2. However, in order to create a database and remove the example schemas it is best to select the New Database option.

Figure 11.3 shows various options which can be removed from a database creation. All options can be retained. Bear in mind that general use of added options could incur higher Oracle software licensing fees.

Figure 11.4 allows selection of standard included optional features. None of these options absolutely has to be retained; their selection depends on application requirements.

Even for highly active, concurrent, very large OLTP databases dedicated servers are still commonly used, sometimes with as many as 1,000 concurrent connections. A shared server configuration will ultimately be more scalable. Once again if your database concurrency is high it may be more cost effective in the long term to invest in a connection pooling application or web server machine, allowing more performance capacity for your database itself. The choices of dedicated and shared server modes are shown in Figure 11.5.

Figure 11.5: Choosing Dedicated or Shared Servers

Oracle Installer tends to maximize the use of memory resources on a database server, to the detriment of everything else on that server. Figures 11.6 and 11.7 show that I have altered buffer values to very low values. I am running Oracle Database on a low-powered, single CPU, Pentium II, Intel Windows 2K box. Additionally I have multiple databases running on that machine.

Figure 11.6: Oracle Database Memory Buffer Parameters

Figure 11.7: The Sort Buffer

Note in Figure 11.6 that PGA is automatically entered. If this value is set to 0 the tool will prompt to set the PGA value to an amount greater than 0 when clicking the next tab. Note that in Figure 11.5 that Shared Server mode is selected. Shared servers do not allow use of automatically managed PGA cache in Oracle9i Database.

I have two gripes with using this tool to create databases.

My first gripe is as shown in Figure 11.8 showing the database creation tool creating a large number of tablespaces containing numerous different examples and Oracle options. These take up space and too many tablespaces is simply confusing. If you were to count all the users after creating a database with this tool you should find that Oracle9i Database Release 2 (9.2) creates almost 30 users, using all the extra tablespace datafiles shown in Figure 11.8. The most essential usernames in a newly created Oracle database are SYS, SYSTEM, probably OUTLN, and perhaps DBSNMP and maybe RMAN.

Figure 11.8: Creation of Tablespaces and Datafiles

Note 

 Oracle Database 10 Grid   My first gripe has been resolved by the addition of a system-level auxiliary tablespace. This is very pleasing indeed. The SYSAUX tablespace will be created automatically along with the SYSTEM tablespace. The SYSAUX tablespace contains options and tools such as RMAN tools, Auditing, an Oracle Enterprise Manager repository (like the RMAN repository, best not included with the production database), plus various add-on Oracle options such as OLAP, Data Mining, XML, and many others.

My second gripe with the Database Configuration Assistant is as shown in Figure 11.9, where only three large redo log files are created. Rarely can an OLTP database survive with less than at least five and sometimes even as many as 10 redo log files. Also the redo logs are created at 100 Mb each. 100 Mb is a very big redo log file and can take time to archive, especially on a Windows box.

Figure 11.9: Creation of Redo Logs

A very active DML OLTP database with only three very large redo log files can result in the database periodically halting service whilst all redo log files are archived. Additionally redo log files are not duplexed and they probably should be for the sake of recoverability.

Current Trends

One particularly useful aspect of the Database Configuration Assistant is that it can be used to show current trends for Oracle database creation. New things recently introduced definitely work properly when used for the SYSTEM tablespace. Some of these physical aspects affect much of physical architecture even to the level of logical objects such as tables and indexes. Oracle8i Database defaulted to creation of all tablespaces as dictionary-managed except for the temporary sorting tablespace. Oracle9i Release 1 (9.1) made all tablespaces locally managed except for the SYSTEM tablespace. As can be seen in Figure 11.10 the recommended physical structure in Oracle9i Release 2 (9.2) is for all tablespaces to be locally managed with automatic allocation and automatic segment space management.

Figure 11.10: Physical Datafile Default Structure

Now let's take a look at manual database creation using command line instructions and scripting.

11.3.2 Manual Database Creation

The best way to show how to create a database manually is to present a set of scripts and describe the pieces. These scripts are by no means executable as they are, as with any of the scripts in this book. However, these scripts should give an overall picture as to how to create a physically neat and properly structured database which is easy to tune.

Tip 

Scripts presented here were originally built for a Solaris box Oracle8i Release 2 (8.1.7) installation and upgraded for Oracle9i Release 2 (9.2). The Database Configuration Assistant can be used to generate scripts like these, which can then be altered to suit requirements. They are presented merely to show low-level physical structural details and are not necessarily applicable to the latest version of Oracle Database.

Create the Directory Structure

This script creates the directory structure for an Oracle database. This script is generally in compliance with the Oracle Optimal Flexible Architecture (OFA). A few other little bits and pieces are included such as extra variables, SQL*Net configuration files, most importantly a pre-prepared configuration parameter file, and the Oracle password file.

Tip 

Once again scripts contained in this book are "as is". Please do not simply type them in without testing first, especially any Unix or Perl scripting.

#!/bin/ksh ORACLE_BASE=/<mp1>/oracle ORACLE_HOME=$ORACLE_BASE/ora92 TNS_ADMIN=/<mp1>/oracle/ora92/network/admin ORACLE_SID=<SID> ORACLE_DBF1=/<mp1>/oracle/oradata/$ORACLE_SID ORACLE_DBF2=/<mp2>/oracle/oradata/$ORACLE_SID ORACLE_BACKUPS=/<mp2>/backups ORACLE_SBIN=$ORACLE_HOME/sbin ORACLE_ALERT=$ORACLE_BASE/admin/$ORACLE_SID/bdump PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr /ucb:/usr/local/bin:/usr/sbin:/usr/X/bin PATH=${PATH}:$ORACLE_HOME/bin:$ORACLE_HOME/sbin:$JAVA_HOME USAGE="$0: Incorrect arguments, Usage: $0 <password>" if [ -z "$1" ]; then       echo "$USAGE"       exit 1 fi if [ '/usr/ucb/whoami' != "oracle" ]; then       echo "Must be oracle"       exit 1 fi orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=$1 entries=2 ln -s $ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora mkdir $ORACLE_BASE/admin mkdir $ORACLE_BASE/admin/$ORACLE_SID mkdir $ORACLE_BASE/admin/$ORACLE_SID/adhoc mkdir $ORACLE_BASE/admin/$ORACLE_SID/arch mkdir $ORACLE_BASE/admin/$ORACLE_SID/bdump mkdir $ORACLE_BASE/admin/$ORACLE_SID/cdump mkdir $ORACLE_BASE/admin/$ORACLE_SID/create mkdir $ORACLE_BASE/admin/$ORACLE_SID/exp mkdir $ORACLE_BASE/admin/$ORACLE_SID/pfile mkdir $ORACLE_BASE/admin/$ORACLE_SID/udump      mkdir $ORACLE_BASE/oradata mkdir $ORACLE_BASE/oradata/$ORACLE_SID mkdir $ORACLE_BASE/oradata/$ORACLE_SID/archive mkdir /<mp2>/oracle/oradata mkdir /<mp2>/oracle/oradata/$ORACLE_SID mkdir $ORACLE_BACKUPS/$ORACLE_SID     cp ./listener.ora $TNS_ADMIN cp ./tnsnames.ora $TNS_ADMIN cp ./init$ORACLE_SID.ora $ORACLE_BASE/admin/$ORACLE_SID/pfile $ORACLE_HOME/bin/lsnrctl start

Create the Database

This script creates a SYSTEM tablespace and a single set of five duplexed redo log files of 10 Mb each. These redo log files are enough for a small-load OLTP database, perhaps even a little on the large size. A highly active large OLTP or data warehouse database would have many more, much larger redo logs.

Additionally I am not using a binary parameter file (SPFILE) in this case. Using a binary parameter file is very useful for maintaining high availability as it allows for changing of parameters online using the ALTER SYSTEM command. A deeper discussion on the subject of the SPFILE is database administration, not tuning, and therefore inapplicable to this book. The only problem I have found using a binary parameter file is if the file gets lost. Some clients tend to forget to back up binary files or make textual parameter file copies.

When something goes wrong with parameter settings they sometimes forget what they have changed online using the ALTER SYSTEM command.

Lastly, I create a temporary rollback segment. I am not using automated undo segments on my test database at this point. My test database is a very old server and has a serious lack of disk space; manual rollback segments are easier to control in this respect. Both automated undo and manual rollback are being covered in this book due to the existence of many pre-Oracle Database 10g installations.

spool ../logs/system.log; connect sys/<password> as sysdba startup nomount pfile =    "/<mp1>/oracle/admin/<SID>/pfile/init<SID>.ora" CREATE DATABASE "<SID>"         maxdatafiles 400         maxinstances 4         maxlogfiles 16         maxlogmembers 4         maxloghistory 200         character set US7ASCII         national character set US7ASCII         archivelog DATAFILE '/<mp1>/oracle/oradata/<SID>/system01.dbf' SIZE 450M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE       '/<mp1>/oracle/oradata/<SID>/temp01.dbf' SIZE 250M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       LOGFILE GROUP 1 ('/<mp1>/oracle/oradata/<SID>/redo01a.log',       '/<mp1>/oracle/oradata/<SID>/redo01b.log') SIZE 10M,       GROUP 2 ('/<mp1>/oracle/oradata/<SID>/redo02a.log',       '/<mp1>/oracle/oradata/<SID>/redo02b.log') SIZE 10M,       GROUP 3 ('/<mp1>/oracle/oradata/<SID>/redo03a.log',       '/<mp1>/oracle/oradata/<SID>/redo03b.log') SIZE 10M,       GROUP 4 ('/<mp1>/oracle/oradata/<SID>/redo04a.log',       '/<mp1>/oracle/oradata/<SID>/redo04b.log') SIZE 10M;       GROUP 5 ('/<mp1>/oracle/oradata/<SID>/redo05a.log',       '/<mp1>/oracle/oradata/<SID>/redo05b.log') SIZE 10M;     create rollback segment rbtemp tablespace system storage (initial 64k next 64k minextents 2 maxextents 300); alter rollback segment rbtemp online;     disconnect; spool off; 

Note 

 Oracle Database 10 Grid   Manual rollback is deprecated.

If using automated undo include the following command in the previous script immediately following the command creating the temporary tablespace.

UNDO TABLESPACE UNDOTBS1 DATAFILE       '/<mp1>/oracle/oradata/<SID>/undotbs01.dbf' SIZE 250M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED

Tablespace and Rollback Creation

The script in this section creates a lot of tablespaces plus manual rollback segments. In general, I have created a tablespace structure based on an expected rapid database growth rate rather than potential application functionality. This script is out of date intentionally to show the basics and is commented inline for clarity.

spool ../logs/tbs.log; connect sys/<password> as sysdba

In the latest version of Oracle9i Database all tablespaces are recommended as being locally managed. The command following assumes the SYSTEM tablespace is dictionary managed.

Tip 

Creation of a logical standby database requires use of a backup control file, which is problematic in Oracle9i Database with a default temporary tablespace created for a database with a locally managed SYSTEM tablespace.

Dictionary managed tablespaces will be phased out in a future version of Oracle Database but if not then they still have potential uses for very fine physical tuning. For both tablespace types, but more so for dictionary managed tablespaces, consistent sizes for all datafiles tends to minimize space wastage by allowing reuse of freed extents. New larger extents will not fit into smaller, older extents. This can result in a lot of wasted space through fragmentation not even recoverable by coalescence. Even automatic extent allocations for locally managed tablespaces have default uniform sizes of 1 M. I have not used automatic allocation of extents in these scripts.

Most OLTP databases grow at rapid rates. Small, variable extent sizes can lead to huge numbers of extents in time. Smaller tables can be placed into smaller extent sized tablespaces if so desired.

alter tablespace system default storage (initial 1M next 1M minextents 1 maxextents unlimited   pctincrease 0); alter tablespace system minimum extent 1M;

Temporary sort space is allocated to the database using the default temporary sort space. This is an Oracle9i Database change and a very good one at that. In the past many databases used the SYSTEM tablespace for sorting. Many database administrators and developers were unaware of having to assign a user a temporary table-space for on disk buffer overflow sorting purposes when creating a user.

-Temporary sort create temporary tablespace temp       tempfile '/<mp1>/oracle/oradata/<SID>/temp01.dbf'       size 300M autoextend on next 1M maxsize unlimited       extent management local uniform size 1M; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Oracle Enterprise Manager requires its own tablespace. It is best to install the Oracle Enterprise Manager repository in a separate database on a separate machine but licensing and security issues could make this a problem. Perhaps a development or a test database is a good place for an Oracle Enterprise Manager repository or even in the same database as an RMAN repository. An RMAN repository should be in a database separate to that of a production database; an RMAN database must be backed up separately from production databases. RMAN requires a backup of itself when not using a control file for the RMAN repository.

Note 

 Oracle Database 10 Grid   The Oracle Enterprise Manager repository is included in the SYSAUX tablespace.

-Oracle Enterprise Manager create tablespace oem datafile       '/<mp1>/oracle/oradata/<SID>/oem01.dbf'       size 500M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;     -Main rollbacks create tablespace rbs1 datafile       '/<mp1>/oracle/oradata/<SID>/rbs01.dbf'       size 300M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Note 

 Oracle Database 10 Grid   Manual rollback is deprecated.

Note the different block size specifications of the next two large rollback and LOB tablespaces. A large block size would be relevant to the content of these tablespaces. The DB_32K_CACHE_SIZE parameter would have to be specified in the configuration parameter file, requiring bouncing of the database prior to creation of these tablespaces.

Tip 

A 32K block size is not available for Win2K SP3 running Oracle9i Database Release 2 (9.2).

-Batch rollbacks create tablespace rbs2 datafile        '/<mp1>/oracle/oradata/<SID>/rbs02.dbf'       size 320M BLOCKSIZE 32K       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M;

Note 

 Oracle Database 10 Grid   Manual rollback is deprecated.

-LOBs create tablespace objects datafile        '/<mp1>/oracle/oradata/<SID>/objects01.dbf'       size 96M BLOCKSIZE 32K       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

RMAN functionality is often placed into a tablespace called Tools by the Oracle Installer. As already stated RMAN is always best installed into a separate database on a separate server. Putting the RMAN repository on a production server in a production database can risk losing both production database and backup recoverability. In other words, everything!

-Stored procedures create tablespace tools datafile       '/<mp1>/oracle/oradata/<SID>/tools01.dbf'       size 100M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Small static data tables go into this tablespace. Note the nonstandard block size of 2K. If the DB_2K_CACHE_SIZE parameter was not set, the database may have to be bounced prior to the creation of this tablespace. The tablespace cannot be created without the existence of a value in the correct cache parameter.

-Static datafiles create tablespace datastat datafile       '/<mp1>/oracle/oradata/<SID>/datastat01.dbf'       size 1M BLOCKSIZE 2K       AUTOEXTEND ON NEXT 32K MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32K;

Let's say that transaction datafiles are expected to grow rapidly and will need to reuse space. There are multiple transaction table-spaces shown below. The separation of these tablespaces is based on growth rates and not application functionality. Many Internet startup companies using OLTP databases never got past the starting post. The startup companies that did succeed often had physical storage structure problems within a short period of time, especially when their databases grew rapidly. Some companies had rapid growth from a few megabytes up to a few hundred gigabytes and even more. There is one often forgotten fact. In the past migration between versions of Oracle Database required exporting from the old version and into the new version. The Oracle export and import utilities allow restructuring into a single extent for each datafile. It all depends on how fast a database grows. Is a database growing uncontrollably between new Oracle Database version releases?

Tip 

Oracle9i Database and Oracle Database 10g have excellent interversion upgrade facilities.

-Transaction datafiles are separated based on expansion    potential create tablespace datatrn1 datafile       '/<mp1>/oracle/oradata/<SID>/datatrn101.dbf'       size 50M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; create tablespace datatrn2 datafile       '/<mp1>/oracle/oradata/<SID>/datatrn102.dbf'       size 100M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M; create tablespace datatrn4 datafile       '/<mp1>/oracle/oradata/<SID>/datatrn104.dbf'       size 200M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

A tablespace such as this logging tablespace has rapid growth and generally involves only DML insertion and deletion activity plus of course selection of data. Thus its extent sizes are even larger than the transactional tablespaces.

create tablespace logs datafile       '/<mp1>/oracle/oradata/<SID>/logtrn01.dbf'       size 1000M BLOCKSIZE 32K       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M;

Tables and indexes should usually be separated into different tablespaces. Even when RAID arrays are used, index datafiles should be accessed sequentially and table datafiles should be accessed mostly in a random fashion from the index datafile. Some applications and database types require full scans of indexes. Potentially full scanned indexes could be placed into randomly accessed storage media structures as long as you are sure that those indexes are always accessed with physical fast full index scans.

The ratio between sizes of table and index tablespaces depends on how you index your data model with respect to Referential Integrity, use of sequence identifiers and how much alternate indexing there is. A properly designed OLTP data model will have a minimum of indexing and will likely use much less space for indexing than for tables. A data warehouse, more traditional or perhaps poorly designed OLTP database could need a lot more index space than table space. Some data models have many indexes for each table and most of those indexes are often composite-column indexes. These types of data models will usually have much bigger index tablespaces than table tablespaces. This structure tends to partially negate the performance benefits of indexing. As a side issue physical space problems are often common in OLTP databases using bitmap and not BTree indexes.

Once again the non-standard block size of 2K is used. The DB_2K_CACHE_SIZE parameter is required.

-Static indexes create tablespace indxstat datafile       '/<mp1>/oracle/oradata/<SID>/indxstat01.dbf'       size 1M BLOCKSIZE 2K       AUTOEXTEND ON NEXT 32K MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32K; -Transactional indexes create tablespace indxtrn1 datafile       '/<mp1>/oracle/oradata/<SID>/indxtrn101.dbf'       size 50M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; create tablespace indxtrn2 datafile       '/<mp1>/oracle/oradata/<SID>/indxtrn201.dbf'       size 100M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M; create tablespace indxtrn4 datafile       '/<mp1>/oracle/oradata/<SID>/indxtrn401.dbf'       size 200M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

It is likely that a logging index tablespace will be smaller than a logging table tablespace. Thus this tablespace has a smaller initial size and extent incremental size than the logging table tablespace.

create tablespace logsidx datafile       '/<mp1>/oracle/oradata/<SID>/logtidx01.dbf'       size 200M       AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M; alter user sys temporary tablespace temp; alter user system temporary tablespace temp;

In general, OLTP databases require many small rollback segments. Data warehouses and batch processing environments require a smaller number of the much larger rollback segments. The larger the transactions are, the larger rollback segments should be. Note that the MAXEXTENTS parameter is set for all rollback segments to prevent runaway transactions. If your customers are executing ad hoc queries or development is being done on a production database this is prudent.

create public rollback segment rb00 tablespace rbs1 storage       (initial 256K next 256K optimal 512K minextents 2          maxextents 64); create public rollback segment rb01 tablespace rbs1 storage       (initial 256K next 256K optimal 512K minextents 2          maxextents 64); create public rollback segment rb02 tablespace rbs1 storage       (initial 256K next 256K optimal 512K minextents 2          maxextents 64); . . . create public rollback segment rb49 tablespace rbs1 storage       (initial 256K next 256K optimal 512K minextents 2          maxextents 64);

Explicitly force all rollback segments online to make sure that Oracle Database is more likely to spread transaction load across all of those rollback segments.

alter rollback segment rb00 online; alter rollback segment rb01 online; alter rollback segment rb02 online; . . . alter rollback segment rb49 online;

Creating large rollback segments can help separate small OLTP functionality from batch processing.

create public rollback segment large00 tablespace rbs2    storage (initial 8M next 8M optimal 16M minextents 2    maxextents 32); create public rollback segment large01 tablespace rbs2 storage       (initial 8M next 8M optimal 16M minextents 2          maxextents 32); alter rollback segment large00 offline; alter rollback segment large01 offline;     alter rollback segment rbtemp offline; drop rollback segment rbtemp;     disconnect; spool off;

Note 

 Oracle Database 10 Grid   Note Manual rollback is deprecated.

The Catalog and the SPFILE

Creating the database catalog and the binary SPFILE parameter file is not directly relevant to tuning. A binary parameter file makes administration easier. The only danger is that it makes random "guesstimate" changes to configuration parameters easier to do. This is always dangerous. Research any changes first.

spool ../logs/catalog.log; connect sys/<password> as sysdba; -database creation of data dictionary views amongst many    other scripts @/<mp1>/oracle/ora92/rdbms/admin/catalog.sql; -oracle lock views @/<mp1>/oracle/ora92/rdbms/admin/catblock.sql; -database creation of PL/SQL @/<mp1>/oracle/ora92/rdbms/admin/catproc.sql; -export utility ovm_sys user @/<mp1>/oracle/ora92/rdbms/admin/owminst.plb; -pinning objects @/<mp1>/oracle/ora92/rdbms/admin/dbmspool.sql; -heterogeneous services @/<mp1>/oracle/ora92/rdbms/admin/caths.sql; -prepare database for direct path SQL*Loader @/<mp1>/oracle/ora92/rdbms/admin/catldr.sql; -audit trails views @/<mp1>/oracle/ora92/rdbms/admin/cataudit.sql; -parallel server views @/<mp1>/oracle/ora92/rdbms/admin/catparr.sql; -tkprof use @/<mp1>/oracle/ora92/rdbms/admin/utltkprf.sql; connect system/manager; -sqlplus user help files    @/<mp1>/oracle/ora92/sqlplus/admin/pupbld.sql; connect sys/<password> as sysdba; -pl/sql recompilation @/<mp1>/oracle/ora92/sqlplus/admin/utlrp.sql;     shutdown immediate;     create spfile='/<mp1>/oracle/database/spfile<SID>.ora'  FROM       pfile='/<mp1>/oracle/admin/pfile/init<SID>.ora'; startup; disconnect; spool off; 

I have provided these sample installation scripts here, and particularly the variable physical sizes for the tablespaces, for one reason only. Many of the client sites I have worked at in the last 3 years had serious problems with uncontrollable physical data-base growth. OLTP databases and data warehouses are becoming larger and larger all the time. This is one possible approach. Your structure does not have to be the same. Additionally there are many new features and changes in the latest versions of Oracle Database that will help with respect to rapid growth. Oracle Corporation generally responds very vigorously to current industry commercial requirements of its software.

Tip 

Once again scripts presented here were originally built for a Solaris box Oracle8i Release 2 (8.1.7) installation and upgraded for Oracle9i Release 2 (9.2). The Database Configuration Assistant can be used to generate scripts like these, which can then be altered to suit requirements. They are presented to merely show low-level physical structural details and are not necessarily applicable to the latest version of Oracle Database.

11.3.3 Automated Storage Management

 Oracle Database 10 Grid   What was Oracle Managed Files in Oracle9i Database is now Automated Storage Management in Oracle Database 10g. Various new features were introduced in Oracle9i Database with expansion in Oracle Database 10g. Automated Storage Management allows complete automated management of all physical Oracle Database files; this includes control files, datafiles, and all log files. When creating and dropping a tablespace, for instance, datafiles no longer have to be added or removed, respectively, physically in the operating system anymore. Creation of files will involve automatic file naming.

Only when using Automated Storage Management are Diskgroups introduced, allowing grouping of tablespaces and datafiles into groups such as allocation of multiple temporary tablespaces to a user from a Diskgroup.

Additionally Automated Storage Management can automatically balance and distribute files for optimal performance when configuration changes are made.

Tip 

Automated Storage Management or Oracle Managed Files is reputed to have quite severe adverse effects on overall performance.

This brief introduction to the Oracle installation and database creation process I hope gives a quick glimpse into physical and configuration tuning. The next chapter will start to dig into physical tuning in more depth by examining datafiles, tablespaces, control files, redo and archive logs, manual rollback and automated undo plus temporary sorting on disk.

---