Manual Database Creation

Manual database creation is an important part of learning to work with Oracle. The first step in manual database creation is deciding on an appropriate SID or Oracle System Identifier (we used 'sampledb'). Next, an initialization parameter file should be created for the new database and stored in the 'dbs' directory or the 'database' directory within the Oracle Home Directory. This directory should already have a number of generic initialization files. Find the general purpose configuration Parameter file (init.ora) among the ones for the other templates, rename it to init<your_sid>.ora (e.g. initsampledb.ora) and save it in the same directory. You may modify the parameters within this file to configure the new database.

control_files   = (d:\oracle\databases\ora92\control01.ora,
d:\oracle\databases\ora92\control02.ora,
d:\oracle\databases\ora92\control03.ora)
undo_management = auto
db_name         = sampledb
db_block_size   = 8192

The minimal configuration file as above which is customized to the Oracle location on your system will do. The control file location and name are specified in the value of the control_files parameter. If the control_files parameter is not specified, Oracle will create a file with a default operating system-dependant-filename. The undo_management flag enables automatic undo management. The db_name parameter should be set to a text string of eight characters or less and correspond to the database's global name. The block_size specifies the size of each block.

Notes on some Database Configuration Parameters

The Archive redo logs are used for database recovery and standby database implementation. Running a database in archive log mode enables the archiving of redo log files before reuse. This means that all the redo information goes into log files on the hard disk. Data Block Sizing parameter DB_BLOCK_SIZE helps define the default database block size and determines the SORT_AREA_SIZE. The SORT_AREA_SIZE is an upper bound on the amount of memory used for sorting operations. The block size may only be set during database creation and cannot be changed thereafter.

The UDUMP, BDUMP, and CDUMP File Location parameters are used to specify locations for trace files. Database Storage parameters allow users to manage control file parameters (e.g. multiplex or not), tablespace options, datafile and undo segment related options (you may choose Automatic Undo Management rather than manage rollback segments manually), and redo log groups (number of groups, location etc.).

Now, SQL*plus should be started through the OEM console or the Oracle server console. Login as SYSDBA with the appropriate password and startup the instance in NOMOUNT mode (since you are not opening a database).

D:\oraclexe>sqlplus
 
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Dec 11 19:51:00 2006
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Enter user-name: SYS@XE as SYSDBA
Enter password:
 
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
 
SQL> startup nomount

Other options available for the startup command include: 'MOUNT' - this starts the instance and mounts the database but does not open the database; and 'OPEN' - this option mounts and opens the database. The 'recover' option fires up the instance without opening the database and starts the recovery from failure.

Finally, we are ready to issue the create database SQL command. The CREATE DATABASE command creates data files, control files, redo log files, the system tablespace along with the associated data file, and a system rollback segment, and the tables that underlie the data dictionary. This command also assigns a character set (language support) to the database, optionally sets the database time zone, and mounts and opens the database for use.

CREATE DATABASE [database name]
[CONTROLFILE REUSE]
[LOGFILE [GROUP integer] file specification]
[MAXLOGFILES integer]
[MAXLOGMEMBERS integer]
[MAXLOGHISTORY integer]
[MAXDATAFILES integer]
[MAXINSTANCES integer]
[ARCHIVELOG|NOARCHIVELOG]
[CHARACTER SET charset]
[NATIONAL CHARACTER SET charset]
[DATAFILE filespec [autoextend]]
[DEFAULT TEMPORARY TABLESPACE tablespace filespec]
[UNDO TABLESPACE tablespace DATAFILE filespec]
[SET TIME_ZONE [time_zone_region]];

In the above general format, parameters within square brackets should be replaced with their value. For example, to create a database named 'sampledb', the first line should be changed to 'CREATE DATABASE SAMPLEDB'.

• The [CONTROLFILE REUSE] directive allows the mentioned control files to be reused over and over again.
• LOG FILE specifies names, locations and groups for the database's log files.
• MAXLOGFILES is the upper bound of the redo log files
• MAXLOGMEMBERS is the upper bound of redo log file members within each log file group.
• MAXLOGHISTORY is the upper bound of archive redo logs
• AUTOEXTEND enables or disables automatic extension of the SYSTEM tablespace data files.
• The MAXINSTANCES parameter is optional. The default is 1.
• The DATAFILE option contains details about the files used for the system tablespace.
• DEFAULT TEMPORARY TABLESPACE is the default tablespace new users are assigned to upon creation.
• UNDO TABLESPACE creates and names the undo tablespace and the associated data files. This option is relevant only if the UNDO_MANAGEMENT variable is set to AUTO in the initialization parameter file.

create database sampledb controlfile reuse
Logfile
Group 1 ('/oradata/sampledb/log01a.log',
'/oradata/sampledb/log01b.log') size 20M,
Group 2 ('/oradata/sampledb/log02a.log',
'/oradata/sampledb/log02b.log') size 20M,
Group 3 ('/oradata/sampledb/log03a.log',
'/oradata/sampledb/log03b.log') size 20M,
Group 4 ('/oradata/sampledb/log04a.log',
'/oradata/sampledb/log04b.log') size 20M
Datafile '/oradata/sampledb/sampledb_system.dbf' 200M
AUTOEXTEND ON MAXSIZE 500M
Undo tablespace sampledb_undo1
Datafile '/oradata/sampledb/sampledb_undo1.dbf' 10M
Default temporary tablespace sampledbtemp tempfile
'/oradata/sampledb/sampledbtemp1.dbf' size 25M
EXTENT MANAGEMENT local
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 200;

Once the above command has been issued and executed (just select the entire command and hit F5 or click on the execute icon), the database is created. data files, control files, redo log files, the SYS and SYSTEM users, and all the internal data dictionary tables are created. You can view dynamic performance views, such as V$LOGFILE etc. by logging in as the SYSTEM or SYS user.

Next, the data dictionary views and procedures should be instituted by running the sql scripts in the rdbms/admin directory within the Oracle home directory. The catalog.sql script creates the data dictionary views and catproc.sql creates PL/SQL objects. Execute these files by typing '@ <file_path\file_name>' at the SQL prompt.

Use the following command to create an SPFILE, or use the server parameter file using the initialization parameter file you created at the start of the process. The command looks for the pfile in the default 'dbs' location with the name init<your_sid>.ora.

Create spfile from pfile;

You may now create new tablespaces, tables, users, schemas and add data. These procedures will be dealt with in detail in the following chapters.