[Laskey99] 2.7. Post-Installation Tasks

2.7. Post-Installation Tasks
Once the Oracle software has beeninstalled on the machine, there are a number of tasks you need toperform to create a functioning Oracle database. For example, on Unixsystems, the root user must run a script calledroot.sh after installation.
We also recommend that you run the installer to check the list ofcomponents actually installed. In some cases, the Oracle Installerwill install components that you did not request and that are notnecessary. For example, the Oracle Web Server is automaticallyinstalled on some platforms, even though it was not requested andrequires separate licensing. If you find an unnecessary component, itcan be removed with the installer.
2.7.1. Creating a Database
The first task you mustperform after ensuring that the Oracle software has been correctlyinstalled is creation of the database. This task consists, in turn,of several discrete subtasks, described in the following sections.
2.7.1.1. Choosing a blocksize
While Oracle has defined a defaultblocksize for every operating system environment (often 2048), thisblocksize is often not correct for the database being created. Fewcharacteristics of the database are as important to overallperformance as the database blocksize. The blocksize is specified inthe INIT.ORA file with the DB_BLOCK_SIZE parameter, and once adatabase is created with a particular blocksize, it cannot bechanged. It is, therefore, important to consider the options fordatabase blocksize and define one that will provide good performancefor your site. A few factors to consider are the following:
The blocksize must be at least as big as a single block or disk sector on the host hardware.
The blocksize should be a multiple of the host operating system and/or hardware blocksize. For example, many systems write 512-byte blocks to disk, so the blocksize should be a multiple of 512.
The blocksize should not be bigger than the largest amount of data that the host operating system and/or hardware can read or write in one operation. For example, an operating system may be able to transfer 8192 bytes in a single operation to disk.
Small blocksizes require less data to be transferred to and from disk, and may result in better I/O operation.
Small blocksizes hold fewer rows of data and require more overhead (seeChapter 11, for detailed information on block structures).
Small blocksizes may require more blocks to be read in order to return all data for a query.
Small blocksizes use less redo log space for the update of a single row when the tablespace is in backup mode.
Large blocksizes require that larger amounts of data be transferred to and from disk, and may be less I/O efficient as a result. However, modern controllers with disk caching can often negate this fact.
Large blocksizes hold more rows of data per block, and as a result, less overhead is required.
Large blocksizes require fewer blocks to be read in order to return all data for a query.
Large blocksizes require more redo log space for the update of a single row when the tablespace is in backup mode.
While every database and application environment is different, we canmake the following general recommendations for choosing a databaseblocksize:
Choose a small blocksize (2048 or 4096) for transaction systems where there are frequent queries, inserts, and updates that involve a single row.
Choose a large blocksize (8192 or larger) for data warehouses and other large database applications where most data is bulk loaded, there are few updates, and most queries return multiple rows or involve full table scans.
2.7.1.2. Choosing the name
Beforeyou create an Oracle database, you must choose a name for it as wellas for the Oracle instance which will mount and open it. We recommendthat the database name and the instance name be the same, unless youare running Oracle Parallel Server. In that case, we recommendchoosing a database name and appending the instance number to it toform a unique name for each instance. For more information about thedatabase and the instance, seeChapter 10, andChapter 11.
While the names can be somewhat arbitrary, it is usually best tochoose meaningful names—this will pay dividends later. Theinstance name must be unique on the host machine (that is, if you runmore than one Oracle instance on a single machine, each must have adifferent name) for the following reasons:
Oracle appends the instance name to a standard prefix in order to create known filenames that are used by default. For example, the default name for the initialization file is created by adding the instance name to the prefix INIT. So, if the instance name is TEST, then Oracle will expect an initialization file called initTEST.ora (unless the pfile parameter is explicitly specified when starting the instance). The alert log filename is generated in a similar manner: the alert file for the TEST instance will be alert_TEST.log.
On most platforms, the internal process names used for the background processes are created by appending the instance name to a standard process name. For example, the PMON process for the TEST instance will be ora_pmon_TEST on a Unix system.
While it is not required, we recommend that database andcorresponding instance names be unique across your entire enterprisein order to eliminate later confusion. We also recommend that namesbe four characters long, because Oracle appends the instance name toa standard prefix to create known filenames, and some operatingsystems have an eight-character filename limit.

The database and instance names are case sensitive on most platforms, so TEST is not the same as test or Test.
The database name is specified in the CREATE DATABASE command and in theDB_NAME parameter in theINIT.ORA file. These names must match. Theinstance name is specified in an operating system specific manner,usually using an environment variable.Table 2.1shows where the instance name is defined for some popular systems.
Table 2.1. Sources of Oracle Instance Names for Popular Operating Systems
Operating System Instance Name Comes From
Windows (pre-95) ORACLE_SID environment variable
Windows 95/98 ORACLE_SID entry in the registry
Windows NT ORACLE_SID entry in the registry
Unix ORACLE_SID environment variable
VMS ORASID logical name
2.7.1.3. Creating the parameter file
Everydatabase must have an associated initialization parameter file(usually known as INIT.ORA), which providesinformation on the configuration of the database (seeChapter 3, andChapter 12, for moreinformation). When first creating a database, theINIT.ORA file only needs to contain parameterassignments, which should include:
DB_NAME
This parameter specifies the name of the database, and mustcorrespond to the name used in the CREATE DATABASE statement.
DB_BLOCK_SIZE
As discussed earlier, this parameter specifies the blocksize to beused for this database.
DB_FILES
This parameter sets the maximum number of datafiles that can beopened for this database. This parameter should be set no higher thanthe value of the MAXDATAFILES parameter specified during databasecreation.
CONTROL_FILES
This parameter specifies the names and locations of all controlfiles. We recommend that at least two (and, if possible, more thantwo) control files be created on different disk devices (and, ifpossible, on different controllers). Oracle automatically mirrorscontrol file information to each file specified (seeChapter 11, for more information on control files). Bycreating multiple, mirrored control files, you reduce the risk ofbeing unable to start your database if a single control file is lostor damaged.
Once you have decided on the parameters and values to be placed inthe initial INIT.ORA file, the file can becreated using any standard text editor. The file is expected to be inthe dbs directory ofORACLE_HOME (on Unix systems, this would be$ORACLE_HOME/dbs). For some systems, theINIT.ORA file is found in thedatabase directory ofORACLE_HOME (on Windows NT, it is found inorant\database). SeeChapter 12, for other variants.

Be careful to use a plain text editor to create the INIT.ORA file. Do not use a word processing program, since these programs often embed control codes and other characters that may cause errors in the INIT.ORA file. If you must use a word processing program, be sure to save the file as plain text, or the equivalent, to ensure a usable file.
2.7.2. Building Database Creation Scripts
Although you may enter thecommands to create a database directly from the keyboard using OracleServer Manager, we advise you to create a script containing the SQLstatements required to create the database. By creating a script andsaving it on disk, you will:
Have an opportunity to review your creation statements for accuracy before executing them
Automate the actual database creation process
Be able to create a log file with a record of the statements executed
Document the exact method used to create the database
Be able to recreate the database in the future, if required
It is possible to generate a script containing all the databasecreation statements from an existing database. OracleScripts, by Brian Lomasky and David C. Kreines(O'Reilly Associates, 1998), describes a utility that performsthis function.
The script may be created using any plain text editor, and should besaved to disk in a known, standard location. The OFA standardspecifies a directory calledcreate belowORACLE_HOME for this purpose.
The database creation script must do the following:
Create the database, specifying the filename and size for the SYSTEM tablespace. The SYSTEM tablespace does not usually need to be very big—50 to 80 megabytes will usually do—and it should be on a different disk from other datafiles, if possible. You will need to define the names, locations, and sizes of the redo log files. You should define at least three redo log files, and they should be sized properly. SeeChapter 11, for more information on redo log files.
Create the data dictionary views using the Oracle-supplied script catalog.sql. This script is normally found in the rdbms/admin directory below ORACLE_HOME.
Create the objects required by Oracle's procedural components (i.e., PL/SQL) using the Oracle-supplied script catproc.sql. This script is normally found in the rdbms/admin directory below ORACLE_HOME.
Create a rollback segment in the SYSTEM tablespace, and place it online. A rollback segment is required in order to create any additional tablespaces. This rollback segment can be taken offline and removed later, if desired.
Create a rollback tablespace. Rollback segments should be created in a tablespace created for this purpose. Make the tablespace large enough to hold all of your rollback segments (allow space for growth). Ideally, the file for the rollback tablespace will reside on a disk separate from other database files, in order to avoid I/O contention. We recommend that this tablespace be called ROLLBACK and that the default storage be defined with equal-size INITIAL and NEXT extents (remember that rollback segments must have at least two extents).
Create one or more rollback segments. Specify a size that will accommodate your expected transaction load, and consider using the OPTIMAL parameter to allow rollback segments to shrink back to a predetermined size. Be sure to add the names of the rollback segments to the INIT.ORA file before the next database startup; otherwise, they will not be used.
Create a TEMPORARY tablespace. By default, Oracle will use the SYSTEM tablespace as the temporary tablespace for each user. This should be avoided, since performance will be negatively impacted. Create a separate tablespace for temporary segments. We recommend that this tablespace be called TEMP.
Create any additional tablespaces required. You will probably want to create one or more tablespaces for tables and one or more for indexes. Ideally, the data and index tablespaces should be on different disks in order to avoid I/O contention.
Run any other required Oracle-supplied scripts. These scripts will be located in the same directory as the catalog.sql script and will be used to create data and objects to support the particular set of Oracle features installed.
Modify the SYSTEM account to set default and temporary tablespaces. By default, both the DEFAULT tablespace and TEMPORARY tablespace are set to SYSTEM for this account, and you will not want to create any new objects there. You should also change the TEMPORARY tablespace for the SYS account, since it will be set to SYSTEM by default.
2.7.2.1. A sample script
The following SQL script will create a database called DB1 using theOFA architecture. You will find a copy of this script at theO'Reilly web site (see the Preface fordetails).
Code View:Scroll/Show All
REM * ***********************************************************
REM * Script to create DB1 instance with db_block_size = 8192
REM *
REM * Created: Dave Kreines - 10/18/98
REM *
REM *
REM * ***********************************************************
spool /disk00/oracle/software/7.3.4/dbs/crdbDB1.log
REM * Start the instance (ORACLE_SID must be set to ).
REM *
connect internal
startup nomount pfile=/disk00/oracle/software/7.3.4/dbs/initDB1.ora
REM * Create the database.
REM *
create database "DB1"
maxinstances 2
maxlogfiles 32
maxdatafiles 1000
character set "US7ASCII"
datafile '/disk00/oracle/oradata/DB1/system01.dbf' size 50M
logfile '/disk01/oracle/oradata/DB1/log01.log' size 512K,
'/disk01/oracle/oradata/DB1/log02.log' size 512K,
'/disk01/oracle/oradata/DB1/log03.log' size 512K,
'/disk01/oracle/oradata/DB1/log04.log' size 512K;
REM * Now perform all commands necessary to create
REM * the final database after the CREATE DATABASE command has
REM * succeeded.
REM * install data dictionary:
@/disk00/oracle/software/7.3.4/rdbms/admin/catalog.sql
REM * install procedural components:
@/disk00/oracle/software/7.3.4/rdbms/admin/catproc.sql
REM * Create additional rollback segment in SYSTEM since
REM * at least one non-system rollback segment is required
REM * before creating a tablespace.
REM *
create rollback segment SYSROLL tablespace system
storage (initial 25K next 25K minextents 2 maxextents 99);
REM * Put SYSROLL online without shutting
REM * down and restarting the database.
REM *
alter rollback segment SYSROLL online;
REM * Create a tablespace for rollback segments.
REM *
create tablespace ROLLBACK
datafile '/disk01/oracle/oradata/DB1/rbs01.dbf' size 25M
default storage (
initial 500K
next 500K
pctincrease 0
minextents 2
);
REM * Create the "real" rollback segments.
REM *
create rollback segment RBS01 tablespace ROLLBACK
storage (initial 500K next 500K minextents 2 optimal 1M);
create rollback segment RBS02 tablespace ROLLBACK
storage (initial 500K next 500K minextents 2 optimal 1M);
create rollback segment RBS03 tablespace ROLLBACK
storage (initial 500K next 500K minextents 2 optimal 1M);
create rollback segment RBS04 tablespace ROLLBACK
storage (initial 500K next 500K minextents 2 optimal 1M);
REM * Use ALTER ROLLBACK SEGMENT ONLINE to put rollback segments online
REM * without shutting down and restarting the database.
REM *
alter rollback segment RBS01 online;
alter rollback segment RBS02 online;
alter rollback segment RBS03 online;
alter rollback segment RBS04 online;
REM * Since we've created and brought online 4 more rollback segments,
REM * we no longer need the rollback segment in the SYSTEM tablespace.
REM * We could delete it, but we will leave it here in case we need it
REM * in the future.
alter rollback segment SYSROLL offline;
REM * Create a tablespace for temporary segments.
create tablespace TEMP
datafile '/disk02/oracle/oradata/DB1/temp01.dbf' size 25M
default storage (
initial 100K
next 100K
maxextents UNLIMITED
pctincrease 0
);
REM * Create a tablespace for database tools.
REM *
create tablespace TOOLS
datafile '/disk03/oracle/oradata/DB1/tools01.dbf' size 25M
default storage (
initial 50K
next 50K
maxextents UNLIMITED
pctincrease 0
);
REM * Create tablespaces for user activity.
REM *
create tablespace DATA
datafile '/disk04/oracle/oradata/DB1/data01.dbf' size 100M
default storage (
initial 250K
next 250K
maxextents UNLIMITED
pctincrease 0
);
REM * Create tablespaces for indexes.
REM *
create tablespace INDEXES
datafile '/disk05/oracle/oradata/DB1/index01.dbf' size 100M
default storage (
initial 250K
next 250K
maxextents UNLIMITED
pctincrease 0
);
REM * Alter SYS and SYSTEM users, because Oracle will make SYSTEM
REM * the default and temporary tablespace by default, and we don't
REM * want that.
REM *
alter user sys temporary tablespace TEMP;
alter user system default tablespace TOOLS temporary tablespace TEMP;
REM * Now run the Oracle-supplied scripts we need for this DB.
REM *
@/disk00/oracle/software/7.3.4/rdbms/admin/catexp.sql
@/disk00/oracle/software/7.3.4/rdbms/admin/dbmspool.sql
@/disk00/oracle/software/7.3.4/rdbms/admin/prvtpool.plb
REM * Now run the Oracle-supplied script to create the DBA views
REM * for the SYSTEM account. Change to SYSTEM first.
REM *
connect system/manager
@/disk00/oracle/software/7.3.4/rdbms/admin/catdbsyn.sql
REM * All done, so close the log file and exit.
REM *
spool off
exit

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint
Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next
Related Content

Methods for Database Creation
From: e-Commerce Applications Using Oracle8i and Java from Scratch
The Oracle Database
From: Oracle Database 10g Linux Administration
Databases and Instances
From: Oracle Essentials, 4th Edition
Installation Checklists
From: Oracle® Database 10g Insider Solutions
Software Installation
From: Oracle Database 10g DBA Handbook
Logical Data Storage
From: Configuring and Tuning Databases on the Solaris™ Platform
Types of Database Files
From: Oracle Security
Data Files
From: Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
The DBA Toolkit and Security
From: Oracle Security
Basic Oracle8i Backup and Recovery
From: Oracle8i™ from Scratch