Remote DBA Plans
Remote DBA Service

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

1. The Remote DBA must connect to the Oracle SYS user (or INTERNAL user pre-8i) via the command:

CONNECT SYS AS SYSRemote DBA

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

3. The database is created. The format would be:

CREATE DATABASE name
     CONTROLFILE REUSE
     LOGFILE GROUP n (filespec)
     MAXLOGFILES     n
     MAXLOGMEMBERS   n
     MAXLOGHISTORY   n
     MAXDATAFILES    n
     MAXINSTANCES    n
     ARCHIVELOG|NOARCHIVELOG
     CHARACTER_SET charset
     NATIONAL_CHARACTER_SET charset
     DATAFILE (filespec) autoextend_clause
     Extent_management_clause
      Default_temp_tablespace_clause
      UNDO_tablespace_clause
      SET STANDBY DATABASE clause
      Set_time_zone_clause

autoextend_clause:

AUTOEXTEND ON|OFF NEXT n K|M MAXSIZE n|UNLIMITED K|M

Filespec:

'full path file name|logical|system link name' SIZE n K|M REUSE

extent_management_clause:

EXTENT MANAGEMENT

              DIRECTORY
              LOCAL
                AUTOALLOCATE
                UNIFORM
                      SIZE n [K|M]

Default_temp_tablespace_clause:

DEFAULT TEMPORARY TABLESPACE tablespace [TEMPFILE]

Temp_tablespace_extent_clause

UNDO_tablespace_clause:

UNDO TABLESPACE tablespace [DATAFILE file_space_clauses]

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

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

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

The following clauses are new in Oracle9i:

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

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
spool /var/oracle/OraHome2/assistants/dbca/logs/CreateDB.log

startup nomount pfile="/var/oracle/OraHome2/admin/galinux2/scripts/init.ora";
CREATE DATABASE galinux2
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE '/var/oracle/OraHome2/oradata/galinux2/system01.dbf' 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') SIZE 100M,
GROUP 2 ('/var/oracle/OraHome2/oradata/galinux2/redo02.log') SIZE 100M,
GROUP 3 ('/var/oracle/OraHome2/oradata/galinux2/redo03.log') SIZE 100M;
spool off
exit;

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

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