2
Creating an Oracle Database

This chapter lists the steps necessary to create an Oracle database, and includes the following topics:

• Considerations Before Creating a Database

• Creating an Oracle Database

• Parameters

• Considerations After Creating a Database

• Initial Tuning Guidelines

Considerations Before Creating a Database

This section includes the following topics:

• Creation Prerequisites

• Using an Initial Database

• Migrating an Older Version of the 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.

Creating a database includes the following operations:

• creating new datafiles or erasing data that existed in previous datafiles

• creating information structures that Oracle requires to access and use the database (the data dictionary)

• creating and initializing the control files and redo log files for the database

Consider the following issues before you create a database:

• Plan your database tables and indexes, and estimate how much space they will require.

• Plan how to protect your new database, including the configuration of its online and archived redo log (and how much space it will require), and a backup strategy.

• Select the database character set. You must specify the database character set when you create the database. After the database is created, you cannot change the character set choices without re-creating the database. Hence, it is important that you carefully consider which character set(s) to use. All character data, including data in the data dictionary, is stored in the database character set. If users access the database using a different character set, the database character set should be the same as, or a superset of, all character sets they use.

Also become familiar with the principles and options of starting up and shutting down an instance, mounting and opening a database, and using parameter files.

See Also: The Oracle8i National Language Support Guide.

For information about tables, indexes, and space management, see Chapters 12 through 19.

For information about the online and archive redo logs, and database backup and recovery see Chapter 6, "Managing the Online Redo Log" and Chapter 7, "Managing Archived Redo Logs".

Creation Prerequisites

To create a new database, you must have the following:

• the operating system privileges associated with a fully operational database administrator

• sufficient memory to start the Oracle instance

• sufficient disk storage space for the planned database on the computer that executes Oracle

Using an Initial Database

Depending on your operating system, a database might have been created automatically as part of the installation procedure for Oracle. You can use this initial database and customize it to meet your information management requirements, or discard it and create one or more new databases to replace it.

Migrating an Older Version of the Database

If you are using a previous release of Oracle, database creation is required only if you want an entirely new database. Otherwise, you can migrate your existing Oracle databases managed by a previous version of Oracle and use them with the new version of the Oracle software.

See Also: Oracle8i Migration manual for information about migrating an existing database.

For more information about migrating an existing database, see your operating system-specific Oracle documentation.

Creating an Oracle Database

This section includes the following topics:

• Steps for Creating an Oracle Database

• Creating a Database: Example

• Troubleshooting Database Creation

• Dropping a Database

Steps for Creating an Oracle Database

These steps, which describe how to create an Oracle database, should be followed in the order presented.

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

1. Back up any existing databases.

2. Create parameter files.

3. Edit new parameter files.

4. Check the instance identifier for your system.

5. Start SQL*Plus and connect to Oracle as SYSDBA.

6. Start an instance.

7. Create the database.

8. Back up the database.

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.

Step 1: Back up any existing databases.

Oracle Corporation strongly recommends that you make complete backups of all existing databases before creating a new database, in case database creation accidentally affects some existing files. Backup should include parameter files, datafiles, redo log files, and control files.

Step 2: Create parameter files.

The instance (System Global Area and background processes) for any Oracle database is started using a parameter file.

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

To create a parameter file for the database you are about to make, use your operating system to make a copy of the parameter file that Oracle provided on the distribution media. Give this copy a new filename. You can then edit and customize this new file for the new database.

See Also: For more information about copying the parameter file, see your operating system-specific Oracle documentation.

Step 3: Edit new parameter files.

To create a new database, inspect and edit the following parameters of the new parameter file:

DB_NAME
DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
DB_BLOCK_BUFFERS
PROCESSES
ROLLBACK_SEGMENTS

You should also edit the appropriate license parameter(s):

LICENSE_MAX_SESSIONS
LICENSE_SESSION_WARNING
LICENSE_MAX_USERS

Step 4: Check the instance identifier for your system.

If you have other databases, check the Oracle instances identifier. The Oracle instance identifier should match the name of the database (the value of DB_NAME) to avoid confusion with other Oracle instances that are running concurrently on your system.

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

Step 5: Start SQL*Plus and connect to Oracle as SYSDBA.

Connect to the database as SYSDBA.

$ SQLPLUS /nolog
connect username/password as sysdba

Step 6: Start an instance.

You can start an instance without mounting a database; typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT option:

STARTUP NOMOUNT;

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

Step 7: 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.

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

• creates the datafiles for the database

• creates the control files for the database

• creates the redo log files for the database

• creates the SYSTEM tablespace and the SYSTEM rollback segment

• creates the data dictionary

• creates the users SYS and SYSTEM

• specifies the character set that stores data in the database

• mounts and opens the database for use

  WARNING: Make sure that the datafile and redo log file names that you specify do not conflict with files of another database.

See Also: You can also create a database with a locally managed SYSTEM tablespace; for more information, see "Creating a Database with a Locally Managed SYSTEM Tablespace" .

Step 8: 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.

See Also: The Oracle8i Backup and Recovery Guide.

For more information about parameter files see "Using Parameter Files".

For information about the CREATE DATABASE statement, character sets, and database creation see the Oracle8i SQL Reference.

Creating a Database: Example

The following statement is an example of a CREATE DATABASE statement:

CREATE DATABASE test
     DATAFILE 'test_system' SIZE 10M
     LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K,
     GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K;

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:

• The new database is named TEST.

• The SYSTEM tablespace of the new database is comprised of one 10 MB datafile named TEST_SYSTEM.

• The new database has two online redo log groups, each containing two 500 KB members.

• The new database does not overwrite any existing control files specified in the parameter file.

  Note: You can set several limits during database creation. Some of these limits are also subject to superseding limits of the operating system and can affect each other. For example, if you set MAXDATAFILES, Oracle allocates enough space in the control file to store MAXDATAFILES filenames, even if the database has only one datafile initially; because the maximum control file size is limited and operating system-dependent, you might not be able to set all CREATE DATABASE parameters at their theoretical maximums.

See Also: For more information about setting limits during database creation, see the Oracle8i SQL Reference.

See your operating system-specific Oracle documentation for information about operating system limits.

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, return to "Creating a Database: Example".

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.

Parameters

As described in Step 3 of the section "Creating an Oracle Database", Oracle suggests you alter a minimum set of parameters. These parameters are described in the following sections:

• DB_NAME and DB_DOMAIN

• CONTROL_FILES

• DB_BLOCK_SIZE

• PROCESSES

• ROLLBACK_SEGMENTS

• License Parameters

• DB_BLOCK_BUFFERS

• LICENSE_MAX_SESSIONS and LICENSE_SESSIONS _WARNING

• LICENSE_MAX_USERS

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. 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.

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:

• List at least two filenames for the CONTROL_FILES parameter.

• Place each control file on a separate physical disk drives by fully specifying filenames that refer to different disk drives for each filename.

  Note: The file specification for control files is operating system-dependent. Regardless of your operating system, always fully specify filenames for your control files.

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:

• Oracle is on a large computer system with a large amount of memory and fast disk drives. For example, databases controlled by mainframe computers with vast hardware resources typically use a data block size of 4K or greater.

• The operating system that runs Oracle uses a small operating system block size. For example, if the operating system block size is 1K and the data block size matches this, Oracle may be performing an excessive amount of disk I/O during normal operation. For best performance in this case, a database block should consist of multiple operating system blocks.

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 Tuning.

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.

See Also: For more information about how many rollback segments you need, see Oracle8i Tuning.

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.

See Also: 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 these limits when using the Parallel Server, see Oracle8i Parallel Server Concepts and Administration.

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.

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.

See Also: For more information about setting this limit when using the Parallel Server see Oracle8i Parallel Server Concepts and Administration.

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.

See Also: 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

• Choosing the Number of DB_BLOCK_LRU_LATCHES

• Distributing I/O

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 Tuning 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.

See Also: For information about the CREATE ROLLBACK SEGMENT statement, see the Oracle8i SQL Reference.

Choosing Sizes for Rollback Segments

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.

See Also: For information about choosing sizes for your rollback segments, see Oracle8i Tuning.

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.

See Also: For more information on LRU latches, see Oracle8i Tuning.

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:

• redo log file placement

• datafile placement

• separation of tables and indexes

• density of data (rows per data block)

See Also: For information about ways to distribute I/O, see Oracle8i Tuning.

---