Oracle Database Creation
Oracle Tips by Burleson Consulting
To create a database, the CREATE command is
run under SVRMGR or with 8i and 9i in SQL*Plus. (The command sqlplus/nolog
should be used to avoid annoying prompts for username and password.)
The Remote DBA must connect to the Oracle SYS user (or INTERNAL user
pre-8i) via the command:
CONNECT SYS AS SYSRemote DBA
The instance is started in an unmounted condition. This is
accomplished with the following command.
STARTUP NOMOUNT PFILE=filename
where PFILE=filename refers to the database
initialization file (INIT.ORA) you will be using; unless it is located
in the directory you are currently in, a path must also be provided.
If no PFILE is specified the SPFILE or the default PFILE will be used
if no SPFILE is available.
The database is created. The format would be:
CREATE DATABASE name
LOGFILE GROUP n (filespec)
DATAFILE (filespec) autoextend_clause
SET STANDBY DATABASE clause
AUTOEXTEND ON|OFF NEXT n K|M MAXSIZE
'full path file name|logical|system link name'
SIZE n K|M REUSE
SIZE n [K|M]
DEFAULT TEMPORARY TABLESPACE tablespace [TEMPFILE]
UNDO TABLESPACE tablespace [DATAFILE
In this code:
DATABASE name. The name of the database, a
maximum of eight characters long.
File specifications for data files. Are of
the format: ‘filename’ SIZE integer K or M REUSE. K is for kilobytes,
M is for megabytes. REUSE specifies that if the file already exists,
reuse it. The AUTOEXTEND option is new with later versions of Oracle7
and all of Oracle8 and is used to allow your data files to
automatically extend as needed. (Note: Be very careful with this
command, as it can use up a great deal of disk space rather rapidly if a
mistake is made during table builds or inserts.) File specifications
for log files depend on the operating system.
MAXLOGFILES, MAXDATAFILES, and MAXINSTANCES.
Set hard limits for the database; these should be set to the maximum
you ever expect.
MAXLOGMEMBERS and MAXLOGHISTORY. Hard
EXTENT MANAGEMENT. Determines whether the
extents in the SYSTEM tablespace are managed via the data dictionary
(DICTIONARY) or locally via a bitmap in the tablespace (LOCAL). In
addition, the extents can be AUTOALLOCATED, to enable the system to
manage them as to size; or the UNIFORM clause, with or without a size
specification, can be used to force all extents to a uniform size. For
versions earlier than 9i, you should not make the system tablespace
anything other than dictionary-managed.
CHARACTER_SET and NATIONAL_CHARACTER_SET.
For Oracle8, Oracle8i, and Oracle9i, determines the character set that
data will be stored in. This value is operating system-dependent.
ARCHIVELOG and NOARCHIVELOG. If you need
archive logging, set ARCHIVELOG; if you don’t need it right away, set
NOARCHIVELOG. I suggest using NOARCHIVELOG to avoid creation of
multiple archive logs during initial database creation; you won’t
recover from a failed build, you will just rebuild. This is one thing
to check if the build seems to stall during later steps (running
catproc.sql, for example): the archive log location may have filled.
This is checked using the alert log stored in the location specified
Databases are created in EXCLUSIVE
mode, and are either EXCLUSIVE or PARALLEL. A database must be altered
to PARALLEL mode after creation if you intend to use the oracle
parallel or RAC options.
The CHARACTER_SET is used for normal
data. Character specifications AF16UTF16, JA16SJISFIXED, JA16EUCFIXED,
and JA16DBCSFIXED can be used only as the NATIONAL_CHARACTER_SET. The
NATIONAL_CHARACTER_SET specifies the national character set used to
store data in columns specifically defined as NCHAR, NCLOB, or
NVARCHAR2. The NATIONAL_CHARACTER_SET is usually specified as
AF16UTF16 or UTF8 (formally known as UNICODE). You cannot change the
national character set after creating the database. If not specified,
the national character set defaults to the database character set.
There are nearly 300 character sets supported. For a complete list,
consult the Oracle9i Globalization Support Guide Release 1 (9.0.1)
Part Number A90236-01,June 2001, Oracle Corporation.
You must verify that the registry on NT
is set with the same character set as the database, or data problems
and performance degradation will occur. This applies to all client
workstations, including Windows95, 98, NT, as well as Windows2000
The following clauses are new in
The default_tablespace_clause. Specifies
the default temporary tablespace for all users where one is not
specified in the CREATE USER command. In previous releases, the
default temporary tablespace was SYSTEM. The SYSTEM tablespace cannot
be specified as the DEFAULT TEMPORARY tablespace. The default
temporary tablespace must use the database blocksize that was
specified for the database at creation.
The UNDO_tablespace_clause. Specifies that
an UNDO tablespace will be used instead of a ROLLBACK segment
tablespace. An UNDO tablespace is automatically managed by Oracle. If
this clause is left off of an Oracle9i (it can only be specified in
Oracle9i and later) CREATE DATABASE command, a default tablespace
The set_timezone_clause. Allows
specification of the default time zone for the database. The default
time zone is specified either as a displacement from ZULU (Universal
Time-Coordinated (UTC) formally Greenwich mean time) of -12:00 to
+14:00 or by specifying a region, there are 616 possible regions in an
Oracle9i 9.0.1 database. You can find out what regions are available
from the V$TIMEZONE_NAMES view. All TIMESTAMP WITH LOCAL TIME ZONE
data is normalized to the time zone of the database when data is
stored on disk. An invalid zone will default to UTC.
An example database creation script is
shown in Source 2.1.
Source 2.1 Example database creation script
from the Database Creation Assistant (DBCA).
connect SYS/change_on_install as SYSRemote DBA
set echo on
startup nomount pfile="/var/oracle/OraHome2/admin/galinux2/scripts/init.ora";
CREATE DATABASE galinux2
SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS" DATAFILE '/var/oracle/OraHome2/oradata/galinux2/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/var/oracle/OraHome2/oradata/galinux2/redo01.log')
GROUP 2 ('/var/oracle/OraHome2/oradata/galinux2/redo02.log')
GROUP 3 ('/var/oracle/OraHome2/oradata/galinux2/redo03.log')
What the Oracle kernel does when given
a CREATE DATABASE command is easy, first the system creates control,
redo log, and database files. Next, the system creates the SYSTEM
rollback segment in the SYSTEM tablespace, creates and loads data
dictionary tables, and mounts and opens the database.
On virtually all platforms you will have a Java-based tool
called the Database Configuration Assistant (on UNIX and Linux DBCA in
the $ORACLE_HOME/bin directory). This tool will help you create Oracle
databases. On NT and W2K, this should be named Database Configuration
Assistant and will be in the menu tree - Start -- Programs -- Oracle
-- Oracle_home -- Database Administration -- Database Configuration
Code Depot for Full Scripts
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.