Chapter 2

Creating a Database

• Prerequisites for Creating a Database
• Choosing Initialization Parameters for your Database
• Getting Ready to Create a Database
  • Organizing the Database Contents
  • Designing a Database Structure to Reduce Contention and Fragmentation
  • Decide on the Database Character Set
  • Start the Instance
• Choosing the Method for Creating the Database
  • Using the Oracle Installer (ORAINST) to Create a Database
  • Using the CREATE DATABASE Command
• Creating a Database from the Seed Database
• Checking the Status of your Database
  • Examining Basic Views
  • Checking the Oracle Alert Log

• Create a new database
• Create an Oracle service for Windows NT
• Run optional data dictionary scripts
• Understand the initialization parameters
• Use the alert log

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:

• Database sizing. Proper sizing of the database files can help you choose your initialization parameters. It will result in an optimally tuned database to start with.
• Changing passwords for SYS and SYSTEM. These passwords will be used to log onto the database and perform administrative tasks. This can be done after database creation. The passwords of the SYS and the SYSTEM users should be changed as soon as possible. Use the ALTER USER command to change these passwords. It's recommended that you create a new account with SYSDBA privileges and use that account to create and own the database.

• MTS configuration. The multithreaded server (MTS) pools connections and doesn't allocate a single thread per connection. As a result, it avoids stack overflow and memory allocation errors that would occur from a dedicated connection per thread. A multithreaded server configuration allows many user threads to share very few server threads. The user threads connect to a dispatcher process, which routes client requests to the next available server thread, thereby supporting more users. (You can configure MTS after database creation.)
• Setting the environment variables. Setting the ORACLE_SID, ORACLE_HOME, and path variables to the correct values will allow you to start the correct instance.

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.

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.

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.

The following is a sample init.ora file:

db_name = SJR
db_files = 1020
control_files = (E:\ORANT\database\ctl1SJR.ora,
   E:\ORANT\database\ctl2SJR.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 = 8.0.3.0.0
sort_area_size = 65536
log_checkpoint_timeout = 0
remote_login_passwordfile = shared
max_dump_file_size = 10240

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
   $ORACLE_HOME\database\initSID.ora.
2. Edit the initSID.ora by changing the following parameters:

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:

• Runs the catalog.sql script, which will create the data dictionary
• Creates an additional rollback segment, r0, in SYSTEM
• Creates the tablespaces rbs, temporary, tools, and users
• Creates additional rollback segments r01, r02, r03, and r04 in rbs
• Drops the rollback segment r0 in SYSTEM
• Changes temporary tablespaces for SYS and SYSTEM
• Runs catdbsyn.sql as SYSTEM to create private synonyms for DBA-only dictionary views

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.

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

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.

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

• Segments with different backup needs
• Segments with different security needs
• Segments belonging to different projects
• Large segments from smaller segments
• Rollback segments from other segments
• Temporary segments from other segments
• Data segments from index segments

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.

SEE ALSO
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:

• ORACLE_SID. This parameter is used by Oracle to determine the instance to which the user will connect. If ORACLE_SID isn't set properly and the CREATE DATABASE statement is run, you can wipe out your existing database and all its data.
• ORACLE_HOME. This parameter shows the full pathname of the Oracle system home directory.
• PATH. It should include $ORACLE_HOME.
• ORA_NLS. This is the path to the language object files. If ORA_NLS isn't set and the database is started with languages and character sets other than the database defaults, they won't be recognized.

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):

ORACLE_SID XXX; export ORACLE_SID

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

setenv ORACLE_SID XXX

3. Verify that ORACLE_SID has been set:

Echo $ORACLE_SID

4. Start up the instance in nomount state:

$svrmgrl
SVRMGR> Connect internal
SVRMGR> Startup nomount

1. Use regedt32 to set the variables in the Registry's \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE hive. Or, from a DOS prompt, type

C: > set ORACLE_SID=XXX

where XXX is your SID name (maximum of four characters).

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

Choosing the Method for Creating the Database

You have several options to create the database:

• You can use the Oracle installer to create a database-this is probably the easiest method of database creation because it allows the creation of a seed database, which you can use as a template to create new databases. Check your installation guide for platform-specific instructions for creating the seed database which has a fixed platform-specific schema. This method is discussed in the following section.
• You can modify the create database scripts provided with Oracle as desired to create a database with your own schema. The name and location of these scripts varies with the operating system. On Windows 95/NT, the BUILDALL.SQL and BUILD_DB.SQL scripts can be used as a starting point for database creation. On UNIX systems, the crdbSID.sql is a similar script for database creation. By using this method, you can copy these mentioned scripts, make necessary changes to create the database, and then run the scripts to create the database. You can specify parameters such as MAXDATAFILES and specify multiple SYSTEM tablespace database files by using this method.
• Manually create the database by executing the CREATE DATABASE command. Refer to the Oracle SQL reference manual for the complete syntax. This method allows for more flexibility by allowing you to specify MAXDATAFILES on parameters or multiple SYSTEM tablespace data files.But, there's also more possibility of syntax errors.

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.

Using the CREATE DATABASE Command

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

CREATE DATABASE database
    [CONTROLFILE [REUSE]]
    [LOGFILE filespec[, ...]]
    MAXLOGFILES integer
    MAXLOGMEMBERS integer
    MAXLOGHISTORY integer
    DATAFILE filespec[, ...]
    MAXDATAFILES integer
    MAXINSTANCES integer
        ARCHIVELOG|NOARCHIVELOG
    EXCLUSIVE
    CHARACTERSET charset

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

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

• Creates the data files as specified (if previously existing data files are specified, their data is erased)
• Creates and initializes the specified control files
• Creates and initializes the redo logs as specified
• Creates the SYSTEM tablespace and the SYSTEM rollback segment
• Creates the data dictionary
• Creates the SYS and SYSTEM users
• Specifies the character set for the database
• Mounts and opens the database

The following example shows how to create a simple database:

create database test
    controlfile reuse
    logfile GROUP 1
('C:\ORANT\DATABASE\log1atest.ora',
'D:\log1btest.ora') size 500K reuse,
GROUP 2
( '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.
2. Copy C:\ORANT\DATABASE\INITORCL.ORA to C:\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
     -STARTMODE AUTO
     -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.

5. Set ORACLE_SID to MARS:

C: > Set ORACLE_SID=MARS

6. Copy the BUILD_DB.SQL script to c:\mars.
7. Edit the BUILD_MARS.SQL script as follows:
   • Set PFILE to the full pathname for INITMARS.ORA.
   • Change CREATE DATABASE ORACLE to CREATE DATABASE MARS.
   • Change the data files and log filenames to the appropriate names.
   • Modify the location of the Oracle home directory to point to C:\MARS.
8. Use Control Panel's Services tools to verify that the service ORACLESERVICEMARS is started. If it's not started, start it.
9. Start Server Manager and connect to the database as "internal":

C: > svrmgr30
C: > connect internal/password

10. Start the database in the NOMOUNT state:

SVRMGR> STARTUP NOMOUNT PFILE=c:\mars\initmars.ora

11. Turn on spooling to trap error messages and run BUILD_MARS.SQL:

SVRMGR> SPOOL build.log
SVRMGR> @BUILD_MARS.SQL

If there are errors while running BUILD_MARS.SQL, fix the errors and rerun the script for successful completion.

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

SVRMGR> @%RDBMS80%\ADMIN\CATALOG.SQL

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

SVRMGR> @%RDBMS80%\ADMIN\CATPROC.SQL

14. If you want additional features, run the appropriate scripts, such as CATREP8M.SQL for Advanced Replication.
15. 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:

CREATE CONTROLFILE REUSE DATABASE "SJR" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 254
    MAXINSTANCES 1
    MAXLOGHISTORY 899
LOGFILE
  GROUP 1 'E:\ORANT\DATABASE\LOGSJR1.ORA'  SIZE 200K,
  GROUP 2 'E:\ORANT\DATABASE\LOGSJR2.ORA'  SIZE 200K
DATAFILE
  'E:\ORANT\DATABASE\SYS1SJR.ORA',
  'E:\ORANT\DATABASE\RBS1SJR.ORA',
  'E:\ORANT\DATABASE\USR1SJR.ORA',
  'E:\ORANT\DATABASE\TMP1SJR.ORA',
  'E:\ORANT\DATABASE\INDX1SJR.ORA'
;

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.

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

2. Run a full database import with ROWS=N:

C: > imp system/manager full=y rows=n file=fullexp.dmp

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.

2. Choose the key \HKEY_LOCAL_MACHINE\
   SOFTWARE\ORACLE\HOMEID.
3. From the Edit menu choose Add Value.
4. In the Value Name text box, type ORACLE_SID.
5. For the Data Type, choose REG_EXPAND_SZ.
6. Click OK.
7. Type your SID name in the String Editor text box and click OK.
8. 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:

• General database structure
• Information about schema objects
• Integrity constraints
• Database users
• Privileges and roles
• Space allocated to database objects

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:

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

• To identify all the rollback segments in the current database and their status, use

Select * from dba_rollback_segs;

• To identify all the data files in the current database and their status, use

Select * from dba_data_files;

• To identify all the tablespaces in the current database and their status, use

Select * from dba_tablespaces;

• To identify all the users belonging to this database, use

Select * from dba_users;

• To find out if the database is in the ARCHIVELOG mode, use

Select * from v$database;

• To identify all the parameter values in use for the data-base, use

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.

SEE ALSO
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:

• All the init.ora parameters
• Informational messages indicating that the background processes have been started
• The thread used by the instance
• The log sequence that the LGWR is now writing to

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

• Database startups
• Database shutdowns
• Rollback segment creations
• Tablespace creations
• ALTER statements issued
• Log switches
• Error messages

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
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'E:\ORANT\database\Sys1SJR.ora' SIZE 50M
MAXDATAFILES 254
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET WE8ISO8859P1
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.