Oracle8i Administrator's Guide
Release 2 (8.1.6)

Part Number A76956-01

Library

Product

Contents

Index

Go to previous page Go to next page

2
Creating an Oracle Database

This chapter discusses the process of creating an Oracle database, and includes the following topics:

Considerations Before Creating a Database

Database creation prepares several operating system files so they can work together as an Oracle database. You need only create a database once, regardless of how many datafiles it has or how many instances access it. Creating a database can also erase information in an existing database and create a new database with the same name and physical structure.

The following topics can help prepare you for database creation.

Planning for Database Creation

Consider the following actions as you plan for database creation:

Additionally, become familiar with the principles and options of starting up and shutting down an instance and mounting and opening a database. These are the topics of Chapter 3. Other methods may be discussed in your Oracle operating system-specific documentation.

Creation Prerequisites

To create a new database, the following prerequisites must be met:

All of these are discussed in the Oracle installation guide specific to your operating system. Additionally, the Oracle Universal Installer will guide you through your installation and provide help in setting up environment variables, directory structure, and authorizations.

Deciding How to Create an Oracle Database

Creating a database includes the following operations:

You use the CREATE DATABASE statement to perform these operations, but other actions are necessary before you have an operational database. A few of these actions are creating user and temporary tablespaces, building views of the data dictionary tables, and installing Oracle built-in packages. This is why the database creation process involves executing a prepared script. But, you do not necessarily have to prepare this script yourself.

You have the following options for creating your new Oracle database:

The Oracle Database Configuration Assistant (DBCA)

DBCA is a graphical user interface (GUI) tool that interacts with the Oracle Universal Installer, or can be used stand-alone, to simplify the creation of a database. It is described in the following sections:

Advantages of Using DBCA

Here are some of the advantages of using DBCA.

Descriptions of the types of databases created by DBCA (OLTP, Warehousing, and Multipurpose) are presented in "Identifying Your Database Environment".

You can create, delete, or modify databases using DBCA. The modify option is to allow you to enable options that are not already enabled. Only the create database option is discussed in this section.

DBCA Modes for Database Creation

When you run DBCA from the Oracle Universal Installer at installation, the installation type that you select for the Oracle Universal Installer affects the type of database (OLTP, Warehousing, or Multipurpose) that you can create. Here are the installation types that the Oracle Universal Installer presents you with.

Installation Types  User Input Required for Database Creation 
  Minimal  Extensive 
  • Typical

 

 

  • Minimal

 

 

  • Custom

 

"Selecting the Database Creation Method", outlines the type of databases that can be created based upon your choice of installation type.

Identifying Your Database Environment

Oracle Universal Installer enables you to create an Oracle8i database that operates in one of the following environments. Identify the environment appropriate for your Oracle8i database:

Environment  Description 

Online Transaction Processing (OLTP)  

Many concurrent users performing numerous transactions requiring rapid access to data. Availability, speed, concurrence, and recoverability are key issues.

Transactions consist of reading (SELECT statements), writing (INSERT and UPDATE statements), and deleting (DELETE statements) data in database tables.  

Warehousing

(or DSS)

 

Users perform numerous, complex queries that process large volumes of data. Response time, accuracy, and availability are key issues.

These queries (typically read-only) range from a simple fetch of a few records to numerous complex queries that sort thousands of records from many different tables.

Warehousing environments are also known as Decision Support System [DSS]) environments 

Multipurpose 

Both types of applications can access this database. 

Selecting the Database Creation Method

The types of Oracle databases (OLTP, Warehousing, and Multipurpose) created with the Typical, Minimal, and Custom installation types and the amount of user input required are described below. Review these selections and identify the database that best matches your database requirements and database creation expertise:

If You Perform These Steps...  Then... 
  1. Select the Typical installation type.


 

Oracle Database Configuration Assistant automatically starts at the end of installation and creates a preconfigured, ready-to-use Multipurpose starter database with:

  • Default initialization parameters

  • Automatic installation and configuration of Oracle options and interMedia components1

  • Advanced replication capabilities

  • Database configuration of multi-threaded server mode2

  • Archiving mode of NOARCHIVELOG

No user input is required. 

  1. Select the Minimal installation type.

  2. Select Yes when prompted to create a starter database.

    Note: If you select No, all server components except a database are installed. You can create your database later by manually running Oracle Database Configuration Assistant or with a SQL script. See the Oracle8i Administrator's Guide for Windows NT for instructions.

    Note: A Multipurpose database is also installable through the Oracle Internet Directory installation type. That database is only for storing Oracle Internet Directory information.

 

Oracle Database Configuration Assistant automatically starts at the end of installation and creates the same Oracle8i database that you receive with Typical, with the following exceptions:

  • No installation and configuration of Oracle options and interMedia components is available

  • Database configuration of dedicated server mode

 
  1. Select the Custom installation type.

  2. Select Oracle Server and additional products in the Available Products window.

  3. Select Yes when prompted to create a starter database.

  4. Oracle Database Configuration Assistant prompts you to select either of two database creation methods:

 

If You Select the Custom database creation method...

Oracle Database Configuration Assistant guides you in the creation of a database fully customized to match the environment (OLTP, Warehousing, or Multipurpose) and database configuration mode (dedicated server or multi-threaded server) you select. Options and interMedia components (if installed) and advanced replication (if selected) are also automatically configured. Select this option only if you are experienced with advanced database creation procedures, such as customizing:

  • Data, control, and redo log file settings

  • Tablespace and extent sizes

  • Database memory parameters

  • Archiving modes, formats, and destinations

 

 

  • Custom

  • Typical

 
  • Trace file destinations

  • Character set values

 

 

 

If You Select the Typical database creation method...

You have two choices. Oracle Database Configuration Assistant's role in database creation depends on your selection: 

 

 

If you select... 

 

 

 

 

Copy existing files from CD

Oracle Database Configuration Assistant creates the same Oracle8i database as described under Typical on the previous page. Options and interMedia components (if installed) are also automatically configured. No user input is required.3 

 

 

 

 

Create new database files

Oracle Database Configuration Assistant prompts you to answer several questions, including selecting a database environment (OLTP, Warehousing, or Multipurpose) and specifying the number of concurrent connections. Oracle Database Configuration Assistant then dynamically creates a database. Options and interMedia components (if installed) and advanced replication (if selected) are also automatically configured.3, 4 

1 Oracle Database Configuration Assistant only configures options that were installed through Oracle Universal Installer.
2 See Chapter 5 of Oracle8i Administrator's Guide for Windows NT for descriptions of dedicated server mode and multi-threaded server mode (also known as shared server mode).
3 If you selected Oracle JServer for installation, the database is created in multi-threaded server mode for IIOP clients.
4 If you select OLTP as your database environment and enter 20 or more for the number of concurrent database connections, your database is created in multi-threaded server mode. Otherwise, the server mode is dedicated.

Manually Creating an Oracle Database

Manually creating a database can best be illustrated by examining a sample database creation script. But you should also be aware of the steps to follow in creating your database, and what to do if things go wrong or you change your mind.

This section discusses:

Steps for Creating an Oracle Database

These steps, which describe how to create an Oracle database, should be followed in the order presented. You will previously have created an environment for creating your Oracle database, including operating-system-dependent environmental variables, as part of the Oracle software installation process.

To Create a New Database and Make It Available for System Use

  1. Decide on your instance identifier (DB_NAME and SID).

    The Oracle instance identifier should match the name of the database (the value of DB_NAME). This identifier is used to avoid confusion with other Oracle instances that you may create later and run concurrently on your system.

    See your operating system-specific Oracle documentation for more information.

  2. Create the initialization parameter file.

    The instance (System Global Area and background processes) for any Oracle database is started using an initialization parameter file. To create a parameter file for the database you are about to make, use your operating system to make a copy of the initialization parameter file that Oracle provides on the distribution media. Give this copy a new filename. You can then edit and customize this new file for the new database. See "Installation Parameters" for suggestions on which parameters you may want to edit. Also see "Using Initialization Parameter Files".

    Each database on your system should have at least one customized initialization parameter file that corresponds only to that database. Do not use the same file for several databases.


    Note:

    In distributed processing environments, Enterprise Manager is often executed from a client machine of the network. If a client machine is being used to execute Enterprise Manager and create a new database, you need to copy the new initialization parameter file (currently located on the computer executing Oracle) to your client workstation. This procedure is operating system dependent. For more information about copying files among the computers of your network, see your operating system-specific Oracle documentation.

    Enterprise Manager is not discussed in this book. It is described briefly in "Using Oracle Enterprise Manager" 


  3. Start SQL*Plus and connect to your Oracle instance as SYSDBA.

    This example assumes that you have proper authorization.

    $ SQLPLUS /nolog
    CONNECT username/password AS sysdba
    
    
  4. Start an instance.

    You can start an instance without mounting a database; typically, you do so only during database creation. Use the STARTUP statement with the NOMOUNT option. If no PFILE is specified, the initialization parameter file is read from an operating system specific default location.

    STARTUP NOMOUNT;
    
    

    The STARTUP statement is discussed in Chapter 3, "Starting Up and Shutting Down".

    At this point, there is no database. Only an SGA and background processes are started in preparation for the creation of a new database.

  5. Create the database.

    To create the new database, use the SQL CREATE DATABASE statement, optionally setting parameters within the statement to name the database, establish maximum numbers of files, name the files and set their sizes, and so on.

    To make the database functional, you will need to create additional files and tablespaces. This is usually done by running a database creation script. See "Examining a Database Creation Script".

  6. Run the scripts necessary to build views, synonyms, etc.

    The primary scripts that you must run are:

    • CATALOG.SQL--creates the views of data dictionary tables and the dynamic performance views

    • CATPROC.SQL--establishes the usage of PL/SQL functionality and creates many of the PL/SQL Oracle supplied packages

    See your Oracle installation guide for your operating system for the location of these scripts.

    The scripts that you run are determined by the features and options you choose to use or install. Many of the scripts available to you are described in the Oracle8i Reference.

  7. Back up the database.

    You should make a full backup of the database to ensure that you have a complete set of files from which to recover if a media failure occurs. For information on backing up a database, see the Oracle8i Backup and Recovery Guide.

    See Also:

    These steps provide general information about database creation on all operating systems. See your operating system-specific Oracle documentation for information about creating databases on your platform. 

Examining a Database Creation Script

This section examines and explains a database creation script, similar to sample scripts distributed with your operating system.

The Database Creation Script

Here is a sample database creation script which creates database RBDB1. See the next section, "Interpreting the Script", for a narrative interpreting the script.

--  Create database
CREATE DATABASE rbdb1
    CONTROLFILE REUSE
    LOGFILE '/u01/oracle/rbdb1/redo01.log' SIZE 1M REUSE,
            '/u01/oracle/rbdb1/redo02.log' SIZE 1M REUSE,
            '/u01/oracle/rbdb1/redo03.log' SIZE 1M REUSE,
            '/u01/oracle/rbdb1/redo04.log' SIZE 1M REUSE
    DATAFILE '/u01/oracle/rbdb1/system01.dbf' SIZE 10M REUSE 
      AUTOEXTEND ON
      NEXT 10M MAXSIZE 200M 
    CHARACTER SET WE8ISO8859P1;

-- Create another (temporary) system tablespace
CREATE ROLLBACK SEGMENT rb_temp STORAGE (INITIAL 100 k NEXT 250 k);

-- Alter temporary system tablespace online before proceding
ALTER ROLLBACK SEGMENT rb_temp ONLINE;

-- Create additional tablespaces ...
-- RBS: For rollback segments
-- USERs: Create user sets this as the default tablespace
-- TEMP: Create user sets this as the temporary tablespace
CREATE TABLESPACE rbs
    DATAFILE '/u01/oracle/rbdb1/rbs01.dbf' SIZE 5M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;
CREATE TABLESPACE users
    DATAFILE '/u01/oracle/rbdb1/users01.dbf' SIZE 3M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;
CREATE TABLESPACE temp
    DATAFILE '/u01/oracle/rbdb1/temp01.dbf' SIZE 2M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;

-- Create rollback segments.  
CREATE ROLLBACK SEGMENT rb1 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb2 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb3 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb4 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;

-- Bring new rollback segments online and drop the temporary system one
ALTER ROLLBACK SEGMENT rb1 ONLINE;
ALTER ROLLBACK SEGMENT rb2 ONLINE;
ALTER ROLLBACK SEGMENT rb3 ONLINE;
ALTER ROLLBACK SEGMENT rb4 ONLINE;

ALTER ROLLBACK SEGMENT rb_temp OFFLINE;
DROP ROLLBACK SEGMENT rb_temp ;

Interpreting the Script

The above database creation script is interpreted here.

The CREATE DATABASE Statement

CREATE DATABASE rbdb1
    CONTROLFILE REUSE
    LOGFILE '/u01/oracle/rbdb1/redo01.log' SIZE 1M REUSE,
            '/u01/oracle/rbdb1/redo02.log' SIZE 1M REUSE,
            '/u01/oracle/rbdb1/redo03.log' SIZE 1M REUSE,
            '/u01/oracle/rbdb1/redo04.log' SIZE 1M REUSE
    DATAFILE '/u01/oracle/rbdb1/system01.dbf' SIZE 10M REUSE 
      AUTOEXTEND ON
      NEXT 10M MAXSIZE 200M 
    CHARACTER SET WE8ISO8859P1;

When you execute a CREATE DATABASE statement, Oracle performs the following operations:

The values of the MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXLOGHISTORY, and MAXINSTANCES options in this example assume the default values, which are operating system-dependent. The database is mounted in the default modes NOARCHIVELOG and EXCLUSIVE and then opened.

The items and information in the example statement above result in creating a database with the following characteristics:

Creating Another System Rollback Segment

CREATE ROLLBACK SEGMENT rb_temp STORAGE (INITIAL 100 k NEXT 250 k);
ALTER ROLLBACK SEGMENT rb_temp ONLINE;

These statements create a temporary system rollback segment to use while other database tablespaces are being created. For a discussion of rollback segments, see Chapter 11, "Managing Rollback Segments".

Creating a Tablespace for Rollback Segments

 CREATE TABLESPACE rbs
    DATAFILE '/u01/oracle/rbdb1/rbs01.dbf' SIZE 5M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;

This statement creates the tablespace to hold rollback segments. See Chapter 9, "Managing Tablespaces" and Chapter 11, "Managing Rollback Segments".

Creating a Users Tablespace

CREATE TABLESPACE users
    DATAFILE '/u01/oracle/rbdb1/users01.dbf' SIZE 3M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;

This statement creates a tablespace that can be assigned as a default tablespace in user profiles. See Chapter 9, "Managing Tablespaces" and "Assigning a Default Tablespace".

Creating a Temporary Tablespace

CREATE TABLESPACE temp
    DATAFILE '/u01/oracle/rbdb1/temp01.dbf' SIZE 2M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;

A temporary tablespace has a special usage for sort operations. A user can be assigned this temporary tablespace in a user profile. See Chapter 9, "Managing Tablespaces" and "Assigning a Default Tablespace".

Creating Rollback Segments

CREATE ROLLBACK SEGMENT rb1 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb2 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb3 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb4 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;

-- Bring new rollback segments online and drop the temporary system one
ALTER ROLLBACK SEGMENT rb1 ONLINE;
ALTER ROLLBACK SEGMENT rb2 ONLINE;
ALTER ROLLBACK SEGMENT rb3 ONLINE;
ALTER ROLLBACK SEGMENT rb4 ONLINE;

ALTER ROLLBACK SEGMENT rb_temp OFFLINE;
DROP ROLLBACK SEGMENT rb_temp ;

This series of statements creates the rollback segments to be used for user transactions. When initially created, they are OFFLINE. They must explicitly be brought online. Also, the temporary system rollback segment now is taken offline and then dropped.

For more information, see Chapter 11, "Managing Rollback Segments".

Troubleshooting Database Creation

If for any reason database creation fails, shut down the instance and delete any files created by the CREATE DATABASE statement before you attempt to create it once again.

After correcting the error that caused the failure of the database creation, try running the script again.

Dropping a Database

To drop a database, remove its datafiles, redo log files, and all other associated files (control files, parameter files, archived log files).

To view the names of the database's datafiles and redo log files, query the data dictionary views V$DATAFILE and V$LOGFILE.

See Also:

For more information about these views, see the Oracle8i Reference

Installation Parameters

As stated in the steps for creating a database, you will want to edit the Oracle supplied initialization parameter file. Oracles intent is to provide appropriate values in this starter initialization parameter file; it is suggested that you alter a minimum of parameters. As you become more familiar with your database and environment, you can dynamically tune many of these parameters with the ALTER SYSTEM statement. Any of these altered parameters that you wish to make permanent, should be updated in the initialization parameter file.

The following topic are discussed in this section:

A Sample Initialization File

Listed here is a sample of an Oracle supplied initialization parameter file that has been edited as the parameter file that can be used with the RBDB1 database. You will note that, within the script, Oracle has provided guidance for the settings of the initialization parameters.

##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site.  Important system parameters
# are discussed, and example settings given.
#
# Some parameter settings are generic to any size installation.
# For parameters that require different values in different size
# installations, three scenarios have been provided: SMALL, MEDIUM
# and LARGE.  Any parameter that needs to be tuned according to
# installation size will have three settings, each one commented
# according to installation size.
#
# Use the following table to approximate the SGA size needed for the
# three scenarious provided in this file:
#
#                     -------Installation/Database Size------
#                      SMALL           MEDIUM           LARGE
#  Block         2K    4500K            6800K           17000K
#  Size          4K    5500K            8800K           21000K
#
# To set up a database that multiple instances will be using, place
# all instance-specific parameters in one file, and then have all
# of these files point to a master file using the IFILE command.
# This way, when you change a public
# parameter, it will automatically change on all instances.  This is
# necessary, since all instances must run with the same value for many
# parameters. For example, if you choose to use private rollback segments,
# these must be specified in different files, but since all gc_*
# parameters must be the same on all instances, they should be in one file.
#
# INSTRUCTIONS: Edit this file and the other INIT files it calls for
# your site, either by using the values provided here or by providing
# your own.  Then place an IFILE= line into each instance-specific
# INIT file that points at this file.
#
# NOTE: Parameter values suggested in this file are based on conservative
# estimates for computer memory availability. You should adjust values upward
# for modern machines.
#
###############################################################################

db_name = RBDB1

db_files = 1024                                                       # INITIAL
# db_files = 80                                                       # SMALL
# db_files = 400                                                      # MEDIUM
# db_files = 1500                                                     # LARGE

control_files = ("/u01/oracle/rbdb1/control01.ctl", 
                 "/u01/oracle/rbdb1/control02.ctl")

db_file_multiblock_read_count = 8                                     # INITIAL
# db_file_multiblock_read_count = 8                                   # SMALL
# db_file_multiblock_read_count = 16                                  # MEDIUM
# db_file_multiblock_read_count = 32                                  # LARGE

db_block_buffers = 8192                                               # INITIAL
# db_block_buffers = 100                                              # SMALL
# db_block_buffers = 550                                              # MEDIUM
# db_block_buffers = 3200                                             # LARGE

shared_pool_size = 15728640                                           # INITIAL
# shared_pool_size = 3500000                                          # SMALL
# shared_pool_size = 5000000                                          # MEDIUM
# shared_pool_size = 9000000                                          # LARGE

log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

processes = 59                                                        # INITIAL
# processes = 50                                                      # SMALL
# processes = 100                                                     # MEDIUM
# processes = 200                                                     # LARGE

parallel_max_servers = 5                                              # SMALL
# parallel_max_servers = 4 x (number of CPUs)                         # MEDIUM
# parallel_max_servers = 4 x (number of CPUs)                         # LARGE

log_buffer = 32768                                                    # INITIAL
# log_buffer = 32768                                                  # SMALL
# log_buffer = 32768                                                  # MEDIUM
# log_buffer = 163840                                                 # LARGE

#audit_trail = true  # if you want auditing
#timed_statistics = true  # if you want timed statistics
max_dump_file_size = 10240  # limit trace file size to 5M each

# Uncommenting the line below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true
# log_archive_dest_1 = "location=/u01/oracle/rbdb1/archive"
# log_archive_format = "%%RBDB1%%T%TS%S.ARC"

# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
rollback_segments = (rb1, rb2, rb3, rb4)

# If using public rollback segments, define how many
# rollback segments each instance will pick up, using the formula
#   # of rollback segments = transactions / transactions_per_rollback_segment
# In this example each instance will grab 40/5 = 8
# transactions = 40
# transactions_per_rollback_segment = 5

# Global Naming -- enforce that a dblink has same name as the db it connects to
global_names = true

# Edit and uncomment the following line to provide the suffix that will be
# appended to the db_name parameter (separated with a dot) and stored as the
# global database name when a database is created.  If your site uses
# Internet Domain names for e-mail, then the part of your e-mail address after
# the '@' is a good candidate for this parameter value.
db_domain = us.acme.com 
#global database name is db_name.db_domain
compatible = 8.1.0
 

Editing the Initialization Parameter File

To create a new database, these are some of the initialization parameters that you will want to edit. Depending upon your configuration and options, and how you want to tune your database, there can be other initialization parameters for you to edit or add. Many of these other initialization parameters are discussed throughout this book.

You should also add the appropriate license initialization parameter(s).

These parameters are described in the following sections:

DB_NAME and DB_DOMAIN

A database's global database name (name and location within a network structure) is created by setting both the DB_NAME and DB_DOMAIN parameters before database creation. After creation, the database's name cannot be easily changed, as you must also recreate the control file. The DB_NAME parameter determines the local name component of the database's name, while the DB_DOMAIN parameter indicates the domain (logical location) within a network structure. The combination of the settings for these two parameters should form a database name that is unique within a network. For example, to create a database with a global database name of TEST.US.ACME.COM, edit the parameters of the new parameter file as follows:

DB_NAME = TEST
DB_DOMAIN = US.ACME.COM

DB_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for DB_NAME is recorded in the datafiles, redo log files, and control file of the database. If during database instance startup the value of the DB_NAME parameter (of the parameter file) and the database name in the control file are not the same, the database does not start.

DB_DOMAIN is a text string that specifies the network domain where the database is created; this is typically the name of the organization that owns the database. If the database you are about to create will ever be part of a distributed database system, pay special attention to this initialization parameter before database creation.

See Also:

For more information about distributed databases, see Oracle8i Distributed Database Systems

CONTROL_FILES

Include the CONTROL_FILES parameter in your new parameter file and set its value to a list of control filenames to use for the new database. If you want Oracle to create new operating system files when creating your database's control files, make sure that the filenames listed in the CONTROL_FILES parameter do not match any filenames that currently exist on your system. If you want Oracle to reuse or overwrite existing files when creating your database's control files, make sure that the filenames listed in the CONTROL_FILES parameter match the filenames that currently exist.


WARNING:

Use extreme caution when setting this option. If you inadvertently specify a file that you did not intend and execute the CREATE DATABASE statement, the previous contents of that file will be overwritten. 


If no filenames are listed for the CONTROL_FILES parameter, Oracle uses a default filename.

Oracle Corporation strongly recommends you use at least two control files stored on separate physical disk drives for each database. Therefore, when specifying the CONTROL_FILES parameter of the new parameter file, follow these guidelines:

When you execute the CREATE DATABASE statement (in Step 7), the control files listed in the CONTROL_FILES parameter of the parameter file will be created.

See Also:

The default filename for the CONTROL_FILES parameter is operating system-dependent. See your operating system-specific Oracle documentation for details.  

DB_BLOCK_SIZE

The default data block size for every Oracle server is operating system-specific. The Oracle data block size is typically either 2K or 4K. Generally, the default data block size is adequate. In some cases, however, a larger data block size provides greater efficiency in disk and memory I/O (access and storage of data). Such cases include:

Each database's block size is set during database creation by the initialization parameter DB_BLOCK_SIZE. The block size cannot be changed after database creation except by re-creating the database. If a database's block size is different from the operating system block size, make the database block size a multiple of the operating system's block size.

For example, if your operating system's block size is 2K (2048 bytes), the following setting for the DB_BLOCK_SIZE initialization parameter would be valid:

DB_BLOCK_SIZE=4096

DB_BLOCK_SIZE also determines the size of the database buffers in the buffer cache of the System Global Area (SGA).

See Also:

For details about your default block size, see your operating system-specific Oracle documentation. 

DB_BLOCK_BUFFERS

This parameter determines the number of buffers in the buffer cache in the System Global Area (SGA). The number of buffers affects the performance of the cache. Larger cache sizes reduce the number of disk writes of modified data. However, a large cache may take up too much memory and induce memory paging or swapping.

Estimate the number of data blocks that your application accesses most frequently, including tables, indexes, and rollback segments. This estimate is a rough approximation of the minimum number of buffers the cache should have. Typically, 1000 to 2000 is a practical minimum for the number of buffers.

See Also:

For more information about tuning the buffer cache, see Oracle8i Designing and Tuning for Performance

PROCESSES

This parameter determines the maximum number of operating system processes that can be connected to Oracle concurrently. The value of this parameter must include 5 for the background processes and 1 for each user process. For example, if you plan to have 50 concurrent users, set this parameter to at least 55.

ROLLBACK_SEGMENTS

This parameter is a list of the rollback segments an Oracle instance acquires at database startup. List your rollback segments as the value of this parameter.


Note:

After installation, you must create at least one rollback segment in the SYSTEM tablespace in addition to the SYSTEM rollback segment before you can create any schema objects. 


License Parameters

Oracle helps you ensure that your site complies with its Oracle license agreement. If your site is licensed by concurrent usage, you can track and limit the number of sessions concurrently connected to an instance. If your site is licensed by named users, you can limit the number of named users created in a database. To use this facility, you need to know which type of licensing agreement your site has and what the maximum number of sessions or named users is. Your site might use either type of licensing (session licensing or named user licensing), but not both.

For more information about managing licensing, see "Session and User Licensing".

LICENSE_MAX_SESSIONS and LICENSE_SESSIONS_WARNING

You can set a limit on the number of concurrent sessions that can connect to a database on the specified computer. To set the maximum number of concurrent sessions for an instance, set the parameter LICENSE_MAX_SESSIONS in the parameter file that starts the instance, as shown in the following example:

LICENSE_MAX_SESSIONS = 80

In addition to setting a maximum number of sessions, you can set a warning limit on the number of concurrent sessions. Once this limit is reached, additional users can continue to connect (up to the maximum limit), but Oracle sends a warning for each connecting user. To set the warning limit for an instance, set the parameter LICENSE_SESSIONS_WARNING. Set the warning limit to a value lower than LICENSE_MAX_SESSIONS.

For instances running with the Parallel Server, each instance can have its own concurrent usage limit and warning limit. However, the sum of the instances' limits must not exceed the site's session license.

See Also:

For more information about setting licensing limits when using the Parallel Server, see the Oracle8i Parallel Server Administration, Deployment, and Performance and Oracle8i Parallel Server Setup and Configuration Guide.  

LICENSE_MAX_USERS

You can set a limit on the number of users created in the database. Once this limit is reached, you cannot create more users.


Note:

This mechanism assumes that each person accessing the database has a unique user name and that no people share a user name. Therefore, so that named user licensing can help you ensure compliance with your Oracle license agreement, do not allow multiple users to log in using the same user name. 


To limit the number of users created in a database, set the LICENSE_MAX_USERS parameter in the database's parameter file, as shown in the following example:

LICENSE_MAX_USERS = 200

For instances running with the Parallel Server, all instances connected to the same database should have the same named user limit.

Considerations After Creating a Database

After you create a database, the instance is left running, and the database is open and available for normal database use. If more than one database exists in your database system, specify the parameter file to use with any subsequent database startup.

If you plan to install other Oracle products to work with this database, see the installation instructions for those products; some products require you to create additional data dictionary tables. See your operating system-specific Oracle documentation for the additional products. Usually, command files are provided to create and load these tables into the database's data dictionary.

The Oracle server distribution media can include various SQL files that let you experiment with the system, learn SQL, or create additional tables, views, or synonyms.

A newly created database has only two users, SYS and SYSTEM. The passwords for these two usernames should be changed soon after the database is created. For more information about the users SYS and SYSTEM see "Database Administrator Usernames".

For information about changing a user's password see "Altering Users".

Initial Tuning Guidelines

You can make a few significant tuning alterations to Oracle immediately following installation. By following these instructions, you can reduce the need to tune Oracle when it is running. This section gives recommendations for the following installation issues:

Allocating Rollback Segments

Proper allocation of rollback segments makes for optimal database performance. The size and number of rollback segments required for optimal performance depends on your application. Oracle8i Designing and Tuning for Performance contains some general guidelines for choosing how many rollback segments to allocate based on the number of concurrent transactions on your Oracle server. These guidelines are appropriate for most application mixes.

To create rollback segments, use the CREATE ROLLBACK SEGMENT statement. The size of your rollback segment can also affect performance. Rollback segment size is determined by the storage parameters in the CREATE ROLLBACK SEGMENT statement. Your rollback segments must be large enough to hold the rollback entries for your transactions.

Choosing the Number of DB_BLOCK_LRU_LATCHES

Contention for the LRU (least recently used) latch can impede performance on symmetric multiprocessor (SMP) machines with a large number of CPUs. The LRU latch controls the replacement of buffers in the buffer cache. For SMP systems, Oracle automatically sets the number of LRU latches to be one half the number of CPUs on the system. For non-SMP systems, one LRU latch is sufficient.

You can specify the number of LRU latches on your system with the initialization parameter DB_BLOCK_LRU_LATCHES. This parameter sets the maximum value for the desired number of LRU latches. Each LRU latch will control a set of buffers and Oracle balances allocation of replacement buffers among the sets.

Distributing I/O

Proper distribution of I/O can improve database performance dramatically. I/O can be distributed during installation of Oracle. Distributing I/O during installation can reduce the need to distribute I/O later when Oracle is running.

There are several ways to distribute I/O when you install Oracle:


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index