'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += '
'; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 2 - Creating a Database

Using Oracle8

Chapter 2

Creating a Database

Prerequisites for Creating a Database

Before you can create an Oracle database, you need to configure the kernel for shared memory. In UNIX, shmmax needs to be set properly to allow for the total SGA set in the initialization file. In Windows NT, make sure that virtual memory isn't more than twice the physical memory on the system.

You also need to make certain decisions regarding how the database will be used and configured. These decisions should include the following:

Protect the password for SYS
Because SYS is the owner of the data dictionary, you should protect that password. Allowing the pass-word for SYS to get into the wrong hands can lead to tremendous damage to the database to the point that all data can be lost. The default password for SYS is CHANGE_ ON_INSTALL, whereas the default password for SYSTEM is MANAGER.

Prepare the operating system environment for database creation
The operating system memory parameters should also be set properly. Check your operating system-specific documentation for the parameters to set.

Prepare for and create a database (general steps)

  1. Create the initSID.ora parameter file.
  2. Create the configSID.ora file.
  3. Create the database script crdbSID.ora.
  4. Create the database.
  5. Add rollback segments.
  6. Create database objects for tools.

The DBA's operating system privileges
Your database administrator login should have administrator privileges on the operating system to be able to create a data-base.

Choosing Initialization Parameters for your Database

The instance for the Oracle database is started by using a parameter file (initSID.ora) that should be customized for the database. You can use the operating system to create this file by making a copy of the one provided by Oracle on the distribution media, or by using the init.ora file from the seed database (if installed) as a template. Rename this file as initSID.ora (for example, for the SID ABCD, the name of the initialization file would be initABCD.ora), and then edit it to customize it for your database.

Change these parameters from their default values
Most people make the mistake of leaving the initialization parameters to their default value. These default values aren't ideal for most systems. You need to carefully choose the initialization parameters with your data-base environment in mind.

The parameter file is read-only at instance startup. If it's modified, you need to shut down and restart the instance for the new values to take effect. You can edit the parameter file with any operating system editor. Most parameters have a default value, but some parameters need to be modified with uniqueness and performance in mind. Table 2.1 lists parameters that should be specified.

Table 2.1  Initialization parameters that you should modify
DB_NAMEDatabase identifier (maximum of eight characters). To change the name of an existing database, use the CREATE CONTROLFILE statement to recreate your control file(s) and specify a new database name.
DB_DOMAINThe network domain where the database is created.
CONTROL_FILESNames of the control files. If you don't change this parameter, the control files of other databases can be overwritten by the new instance, making the other instances unusable.
DB_BLOCK_SIZESize in bytes of Oracle database blocks.
SHARED_POOL_SIZESize in bytes of the shared pool.
BACKGROUND_DUMP_DESTLocation where background trace files will be placed.
USER_DUMP_DESTLocation where user trace files will be placed.
DB_BLOCK_BUFFERSNumber of buffers in the buffer cache.
COMPATIBLEVersion of the server that this instance is compatible with.
IFILEName of another parameter file included for startup.
MAX_DUMP_FILE_SIZEMaximum size in OS blocks of the trace files.
PROCESSESMaximum number of OS processes that can simultaneously connect to this instance.
ROLLBACK_SEGMENTSRollback segments allocated to this instance. Refer to the Oracle8 tuning manual for information and guidelines on determining the number and size of rollback segments based on the anticipated number of concurrent transactions.
LOG_BUFFERNumber of bytes allocated to the redo log buffer in the SGA.
LOG_ARCHIVE_STARTEnable or disable automatic archiving if the database is in ARCHIVELOG mode.
LOG_ARCHIVE_FORMATDefault filename format used for archived logs.
LOG_ARCHIVE_DESTLocation of archived redo log files.
LICENSE_MAX_USERSMaximum number of users created in the database.
LICENSE_MAX_SESSIONSMaximum number of concurrent sessions for the instance.
LICENSE_SESSIONS_WARNINGWarning limit on the concurrent sessions.

Database names should be unique
Attempting to mount two databases with the same name will give you the error ORA-01102: cannot mount database in EXCLUSIVE mode during the second mount.

Setting the parameters
The ideal values for these parameters are application dependent and are discussed in more detail in Chapter 21, "Identifying and Reducing Contention," and Chapter 22, "Tuning for Different Types of Applications." Setting these values is based on trial and error. For DSS systems, it's recommended that you choose a large value for these parameters; for OLTP systems, choose a small value for these parameters.

The following is a sample init.ora file:

db_name = SJR
db_files = 1020
control_files = (E:\ORANT\database\ctl1SJR.ora,
db_file_multiblock_read_count = 16
db_block_buffers = 550
shared_pool_size = 9000000
log_checkpoint_interval = 8000
processes = 100
dml_locks = 200
log_buffer = 32768
sequence_cache_entries = 30
sequence_cache_hash_buckets = 23
#audit_trail = true
#timed_statistics = true
background_dump_dest = E:\ORANT\rdbms80\trace
user_dump_dest = E:\ORANT\rdbms80\trace
db_block_size = 2048
compatible =
sort_area_size = 65536
log_checkpoint_timeout = 0
remote_login_passwordfile = shared
max_dump_file_size = 10240

Create an initialization file
  1. Copy the template file. In UNIX, copy $ORACLE_HOME/rdbms/install/rdbms/initx.orc to $ORACLE_HOME/dbs/initSID.ora. In Windows NT,
    copy $ORACLE_HOME\database\initorcl.ora to
  2. Edit the initSID.ora by changing the following parameters:

UNIX Setting
Windows NT Setting
%pfile_dir%?/dbs ?/database
%config_ora_file%configSID.ora (created next) configSID.ora (created next)
%rollback_segs%r01, r02, … r01, r02, …
%init_ora_comments%# #

Create configSID.ora

  1. In UNIX, copy ?/rdbms/install/rdbms/cnfg.orc to ?/dbs/configSID.ora. In Windows NT, copy configorcl.ora to configSID.ora.
  2. Edit the configSID.ora file with any ASCII text editor and set the following parameters: control_files, background_dump_dest, user_dump_dest, and db_name.

Create the database script

  1. Copy $ORACLE_HOME/rdbms/install/rdbms/crdb.orc to $ORACLE_HOME/dbs/crdbSID.sql.
  2. Modify the crdbSID.sql file to set the following to the appropriate values: db_name, maxinstances, maxlogfiles, db_char_set, system_file, system_size, log1_file, log1_size, log2_file, log2_size, log3_file, and log3_size.

When it's run, the crdbSID.sql does the following:

Getting Ready to Create a Database

Creating a database is the first step in organizing and managing a database system. You can use the following guidelines for database creation on all operating systems. Check your operating system-specific documentation for platform-specific instructions.

Before creating a database, take a complete backup of all your existing databases to protect against accidental modifications/deletions of existing files during database creation. The backup should contain parameter files, data files, redo log files, and control files.

Mirror your control and redo log files
The control files and redo log files help you recover your database. To keep from losing a control file, keep at least two copies of it active on different physical devices. Also, multiplex the redo log files and place the log group members on different disks.

Also decide on a backup strategy and the size that will be required for online and archived redo logs. Backup strategies are discussed in Chapter 13, "Selecting and Implementing a Backup Strategy."

Organizing the Database Contents

You organize the database contents by using tablespaces. On some platforms, the Oracle installer creates a seed database, which has a number of predefined tablespaces. The tablespace structure should be carefully chosen by considering the characteristics of the data to minimize disk contention and fragmentation, and to improve overall performance.

In addition to the SYSTEM tablespace provided with the installation, Table 2.2 describes several other suggested tablespaces. You can create these tablespaces by using the CREATE TABLESPACE command, as shown later in the section "Using the CREATE DATABASE Command."

Use multiple tablespaces
Production data and indexes should be stored in separate tablespaces.

Table 2.2  Suggested tablespaces to be created with the database
TEMPUsed for sorting and contains temporary segments
RBSStores additional rollback segments
TOOLSTables needed by the Oracle Server tools
APPS_DATAStores production data
APPS_IDXStore indexes associated with production data in APPS_DATA tablespace

Designing a Database Structure to Reduce Contention and Fragmentation

Separating groups of objects, such as tables with different fragmentation propensity, can minimize contention and fragmentation. You can use Table 2.3 as a guideline for separating objects.

Table 2.3  Fragmentation propensity
Segment Type:
Data dictionaryZero
Rollback segmentsMedium
Temporary segmentsHigh
Application dataLow

You can reduce disk contention by being familiar with the way in which data is accessed and by separating the data segments into groups based on their usage, such as separating

Database sizing issues should be considered to estimate the size of the tables and indexes.

Decide on the Database Character Set

After the database is created, you can't change the character set without recreating the database. If users will access the database by using a different character set, the database character set should be the same as or a superset of all the character sets that would be used. Oracle8 uses encoding schemes that can be commonly characterized as single-byte 7-bit, single-byte 8-bit, varying-width multi-byte, and fixed-width multi-byte. Refer to the Oracle8 Server reference guide for limitations on using these schemes.

For more information on Oracle's National Language Support (NLS) feature and character sets,

Start the Instance

Make sure that the following parameters are set properly in the environment. If the following parameters aren't set properly, your instance won't start or the wrong instance might start:

After the following environment variables are verified, you can connect to Server Manager as internal and STARTUP NOMOUNT.

Set the environment variables in UNIX
  1. Set the ORACLE_SID variable as follows for the sh shell (XXX is your SID):

  1. Set the variable as follows for the csh shell:

  1. Verify that ORACLE_SID has been set:

  1. Start up the instance in nomount state:
SVRMGR> Connect internal
SVRMGR> Startup nomount

Set the environment variables in Windows NT
  1. Use regedt32 to set the variables in the Registry's \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE hive. Or, from a DOS prompt, type
    where XXX is your SID name (maximum of four characters).

  1. Use the Services tool in the Windows Control Panel to ensure that the ORACLESERVICESID service is started.

Using Instance Manager on Windows NT
On Windows NT, you can use the ORADIM utility (Instance Manager) to create a new instance and service for your database.

Choosing the Method for Creating the Database

You have several options to create the database:

After the database is created, you can run catalog.sql and catproc.sql while connected as the SYS of "internal" account to create the data dictionary views.

After the database is created, the SYSTEM tablespace and SYSTEM rollback segment will exist. A second rollback segment must be created and activated in the SYSTEM tablespace before any other tablespace can be created in the database. To create a rollback segment, from the Server Manager prompt type

Svrmgr>Create rollback segment newsegment
Tablespace system
Storage (...);

Refer to the SQL Language manual for the complete syntax of the CREATE ROLLBACK SEGMENT command.

Using the Oracle Installer (ORAINST) to Create a Database

This menu-driven method is probably the easiest because it runs the necessary scripts for any selected product. You can use this method to create a seed database. The installation guide for your platform should have specific instructions for this purpose.

Oracle's installer isn't very flexible
Using the Oracle installer for database creation isn't as flexible as the preceding methods in terms of specifying parameters such as MAXDATAFILES. If this method is used, you'll have to create the other standard non-system tablespaces.

Using the CREATE DATABASE Command

You also can create a database by using the SQL command CREATE DATABASE:

    [LOGFILE filespec[, ...]]
    MAXLOGFILES integer
    DATAFILE filespec[, ...]
    MAXDATAFILES integer
    MAXINSTANCES integer
    CHARACTERSET charset

Table 2.4 lists the settings available with the CREATE DATABASE command.

Table 2.4  CREATE DATABASE settings
databaseThe name of the database to be created.
CONTROLFILE REUSESpecifies that existing control files specified by the CONTROL_FILES parameter can be reused. If REUSE is omitted and control files exist, you'll get an error.
LOGFILESpecifies one or more files to be used as redo log files. Each filespec specifies a redo log file group containing one or more redo log file members or copies. If you omit this parameter, Oracle will create two redo log file groups by default.
MAXLOGFILESSpecifies the maximum number of redo log file groups that can ever be created for this database.
MAXLOGMEMBERSSpecifies the maximum number of members or copies for a redo log file group.
MAXLOGHISTORYThis parameter is useful only if you're using the PARALLEL SERVER option and in parallel and ARCHIVELOG mode. It specifies the maximum number of archived redo log files for automatic media recovery.
DATAFILESpecifies one or more files to be used as data files.
MAXDATAFILESSpecifies the maximum number of data files that can ever be created for this database.
MAXINSTANCESSpecifies the maximum number of instances that can simultaneously have this parameter mounted and open.
ARCHIVELOG or NOARCHIVELOGEstablishes the mode for the redo log files groups. NOARCHIVELOG is the default mode.
EXCLUSIVEMounts the database in the exclusive mode after it's created. In this mode, only one instance can access the database.
CHARACTERSETSpecifies the character set the database uses to store the data. This parameter can't be changed after the database is created. The supported character sets and default value of this parameter are operating system dependent.

Oracle performs the following operations when executing the CREATE DATABASE command:

The data dictionary may not be created automatically
You need to run the SQL scripts to create the data dictionary (catalog.sql and catproc.sql) if these scripts aren't run from your database creation script.

The following example shows how to create a simple database:

create database test
    controlfile reuse
    logfile GROUP 1
'D:\log1btest.ora') size 500K reuse,
( 'C:\ORANT\DATABASE\log2atest.ora',
'D:\log2btest.ora' ) size 500K reuse
    datafile 'C:\ORANT\DATABASE\sys1test.ora'
    Âsize 10M reuse autoextend on
     next 10M maxsize 200M
    character set WE8ISO8859P1;

This command creates a database called TEST with one data file (sys1test.ora) that's 10MB in size and multiplexed redo log files with a size of 500KB each. The character set will be WE8ISO8859P1.

Creating a Database from the Seed Database

The following steps can be used to create a database called MARS, using the starter (seed) database ORCL. If you don't have the starter database, you can use the sample initialization file INITORCL.80 in the c:\orant\database directory.

Create a database in Windows NT with BUILD_ALL.sql
  1. Create a directory called MARS.
  3. Modify the DB_NAME, CONTROL_FILES, GLOBAL_NAMES, and DB_FILES parameters in the INITMARS.ORA file.
  4. Use the ORADIM80 command to create the service. For example, from a DOS prompt, type
C: > oradim80 -NEW -SID TEST -INTPWD password
     -PFILE c:\orant\database\inittest.ora

This command creates a new service called TEST, which is started automatically when Windows NT starts. INTPWD is the password for the "internal" account; the PFILE parameter provides the full pathname of initSID.ora.
When to create Oracle services
An Oracle serviceshould be created and started only if you want to create a database and don't have any other database on your system, or copy an existing database to a new database and retain the old data-base.

  1. Set ORACLE_SID to MARS:


  1. Copy the BUILD_DB.SQL script to c:\mars.
  2. Edit the BUILD_MARS.SQL script as follows:
  3. Use Control Panel's Services tools to verify that the service ORACLESERVICEMARS is started. If it's not started, start it.
  4. Start Server Manager and connect to the database as "internal":
C: > svrmgr30
C: > connect internal/password

  1. Start the database in the NOMOUNT state:
SVRMGR> STARTUP NOMOUNT PFILE=c:\mars\initmars.ora

  1. Turn on spooling to trap error messages and run BUILD_MARS.SQL:
SVRMGR> SPOOL build.log
    If there are errors while running BUILD_MARS.SQL, fix the errors and rerun the script for successful completion.

  1. Generate the data dictionary by running CATALOG.SQL:

  1. Run CATPROC.SQL to generate the objects used by PL/SQL:

  1. If you want additional features, run the appropriate scripts, such as CATREP8M.SQL for Advanced Replication.
  2. Turn off spooling and check the log for errors.

All the MAX parameters are set when the database is created. To determine what parameters your database has been created with, execute the following:

SVRMGR> Alter database backup controlfile to trace

This command will create an SQL script that contains several database commands:


To generate SQL statements for all the objects in the database, Export must query the data dictionary to find the relevant information about each object. Export uses the view definitions in CATEXP.SQL to get the information it needs. Run this script while connected as SYS or "internal." The views created by CATEXP.SQL are also used by the Import utility. Chapter 25, "Using SQL*Loader and Export/Import," discusses more about Oracle's Export and Import utilities.

CATALOG.SQL and CATEXP.SQL views don't depend on each other
You don't need to run CATALOG.SQL before running CATEXP.SQL, even though CATEXP.SQL is called from within CATALOG.SQL. This is because no view in CATEXP.SQL depends on views defined in CATALOG.SQL.

Create an identical copy of database but with no data
  1. Do a full database export with ROWS=N:
C: > exp system/manager full=y rows=n file=fullexp.dmp
    This will create a full database export (full=y) without any rows (rows=n).

  1. Run a full database import with ROWS=N:
C: > imp system/manager full=y rows=n file=fullexp.dmp
Creating a new database on the same machine
If the new database is to be created on the same machine as the old database, you need to pre-create the new tablespaces because the old data files are already in use.

Use Instance Manager to create a new database in Windows NT

  1. From the Start menu choose Oracle for Windows NT and then NT Instance Manager. This will start the Instance Manager and show you the status and startup mode of all the SIDs (see Figure 2.1).
    Figure 2.1 : The Instance Manager dialog box shows the available instances.
  2. Click the New button and supply the SID, internal password, and startup specifications for the new instance (see Figure 2.2).
    Figure 2.2 : Provide the specifications for the new instance.
  3. Click the Advanced button and choose appropriate database name, logfile, and data file parameters and a character set for the new database (see Figure 2.3).
    Figure 2.3 : Provide the specifications for the new database.

The Oracle Database Assistant can be used to create a database at any time.

Use Oracle Database Assistant to create a new database in Windows NT
  1. From the Start menu choose Programs, Oracle for Windows NT, Oracle Database Assistant.
  2. Select Create a Database and click Next.
  3. Choose the Typical or Custom option and click Next. The Custom option lets you to customize the parameters of the database that you're trying to create.
  4. Choose Finish.

In Windows NT, you can set the default SID by setting the Registry entry ORACLE_SID.

Updating ORACLE_SID in the Windows NT Registry
  1. From the DOS command prompt, type REGEDT32.

Don't modify the Registry unless you know what you're doing!
Be extremely careful when working with the Registry. Improperly set keys may prevent Windows NT from booting up.

  1. Choose the key \HKEY_LOCAL_MACHINE\
  2. From the Edit menu choose Add Value.
  3. In the Value Name text box, type ORACLE_SID.
  4. For the Data Type, choose REG_EXPAND_SZ.
  5. Click OK.
  6. Type your SID name in the String Editor text box and click OK.
  7. Exit the Registry.

Checking the Status of your Database

After the database is created, regularly check the status of the database by examining its data dictionary and the alert log.

Examining Basic Views

The data dictionary is one of the most important parts of the Oracle database. The data dictionary is a set of tables and views that you can use to look up valuable information about the database. You can use the data dictionary to obtain various types of information, including:

The catalog.sql and catproc.sql scripts can be used during or after database creation to create the commonly used data dictionary views and for PL/SQL support, respectively.

The data dictionary contains a set of base tables and associated set of views that can be placed in the following categories:

View Category
USER_xxx Views accessible by any user that provide information on objects owned by them
ALL_xxx Views accessible by any user that provide information on all objects accessible by them
DBA_xxx Views accessible by any user that provide information on any database object

Which data dictionary objects do I have?
All the data dictionary tables and views are owned by SYS. You can query the DICTIONARY table to obtain the list of all dictionary views.

The following examples show how to query the dictionary tables to obtain information about the database:

Select * from dba_rollback_segs;
Select * from dba_data_files;
Select * from dba_tablespaces;
Select * from dba_users;
Select * from v$database;
Select * from v$parameter;

Checking the Oracle Alert Log

When diagnosing a database problem, the first place to look for information and errors is the alert log (the name is operating system dependent). If this file isn't present, Oracle will automatically create it during database startup. This file can point you to the location of trace files, which can give a lot of insight into the problems encountered. It also contains additional information to indicate the status of the database and what's now happening in the database.

Locating trace files
The trace file would be located in the directory specified by BACKGROUND_DUMP_DEST, USER_DUMP_DEST, or CORE_DUMP_DEST, depending on the exact error and its cause.

For more information on the contents and usage of the alert log,

When the database is started, the following information is recorded in the alert log:

In general, the alert log records all important incidents of the database, including:

Each entry has a timestamp associated with it, and each non-error message has an entry marking its beginning and another entry marking its successful completion. You should frequently check this file for error messages for which the alert log will point to a trace file for more information.

The following is a sample alert log:

File header showing information about your system
Initialization parameters
Database in nomount state and CREATE DATABASE command.

LOGFILE 'E:\ORANT\database\logSJR1.ora' SIZE 200K,
    'E:\ORANT\database\logSJR2.ora' SIZE 200K
DATAFILE 'E:\ORANT\database\Sys1SJR.ora' SIZE 50M
Thu Jan 29 09:33:50 1998
Successful mount of redo thread 1.
Thread 1 opened at log sequence 1
 Current log# 1 seq# 1 mem# 0: E:\ORANT\DATABASE\LOGSJR1.ORA
Successful open of redo thread 1.
Thu Jan 29 09:33:50 1998
SMON: enabling cache recovery
Thu Jan 29 09:33:50 1998
create tablespace SYSTEM datafile
   'E:\ORANT\database\Sys1SJR.ora' SIZE 50M
  default storage (initial 10K next 10K) online

Thu Jan 29 09:34:10 1998
Completed: create tablespace SYSTEM datafile 'E:\ORANT\datab
Thu Jan 29 09:34:10 1998
create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)

Completed: create rollback segment SYSTEM tablespace SYSTEM

Thu Jan 29 09:34:14 1998
Thread 1 advanced to log sequence 2
 Current log# 2 seq# 2 mem# 0: E:\ORANT\DATABASE\LOGSJR2.ORA
Thread 1 cannot allocate new log, sequence 3
Checkpoint not complete
 Current log# 2 seq# 2 mem# 0: E:\ORANT\DATABASE\LOGSJR2.ORA
Thread 1 advanced to log sequence 3
 Current log# 1 seq# 3 mem# 0: E:\ORANT\DATABASE\LOGSJR1.ORA
Thread 1 advanced to log sequence 4
 Current log# 2 seq# 4 mem# 0: E:\ORANT\DATABASE\LOGSJR2.ORA

© Copyright, Macmillan Computer Publishing. All rights reserved.