René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Creating an Oracle 9i database from the command line only

This article is the successor to Creating an Oracle database on NT from the command line only. This article's successor is this one.
On the command line only refers to the fact, that no GUI tool is needed to create a database. For Unix users, the command line is their shell while for Windows users, the command line is the dos box. Windows users usually start the dos box with start->run and then enter cmd and press enter.
Creating a database consists basically of the following steps: All these steps are executed through the create database statement. The location of the control files is specified in the init.ora file.
First, a SID must be defined. This is in order to distinguish several Instances that run on the same machine. The SID's value is stored in the ORACLE_SID environment variable:
Setting ORACLE_HOME and PATH
ORACLE_HOME=/appl/oracle/product/9.2.0.2
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin
ORACLE_SID=adpdb
export ORACLE_SID
Windows Users will set the variable like so:
set ORACLE_SID=adpdb

Location of the control files

The location of the control files is specified in the init.ora file.
Here's a minimal init.ora (under $ORACLE_HOME/dbs if it is Unix, or %ORACLE_HOME%\database, if it is windows) just to demonstrate how the control files are found. Of course, you will add more init params into the init.ora file.
control_files = (/db2/adpdb/control01.ctl,
                 /redolog1/adpdb/control02.ctl,
                 /redolog2/adpdb/control03.ctl)


undo_management = auto


compatible = 9.2.0

db_name     = adpdb
The undo_management parameter is necessary if we want to use automatic undo management.
Although the above seems to be the bare required minimum, you probably also want do define background_dump_dest, core_dump_dest and user_dump_dest.

Starting the instance

On Windows like systems, starting the instance requires oradim:
oradim -new -sid %ORACLE_SID% -pfile c:\path\to\some\init.ora
On Unix like systems, there is no oradim required. On both, Windows and Unix like systems, it must be proceeded like this:
adpdb:/appl/oracle/product/9.2.0.2/dbs >sqlplus "/ as sysdba"


SQL*Plus: Release 9.2.0.2.0 - Production on Fri Jul 25 14:08:37 2003


Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to an idle instance.


SQL>startup nomount pfile=/path/to/the/init.ora
ORACLE instance started.


Total System Global Area  160925320 bytes
Fixed Size                   730760 bytes
Variable Size             109051904 bytes
Database Buffers           50331648 bytes
Redo Buffers                 811008 bytes
SQL>CREATE DATABASE adpdb
  LOGFILE group 1 ('/redolog1/adpdb/redolog1a.dbf','/redolog2/adpdb/redolog1b.dbf' ) SIZE 10M,
          group 2 ('/redolog1/adpdb/redolog2a.dbf','/redolog2/adpdb/redolog2b.dbf' ) SIZE 10M,
          group 3 ('/redolog1/adpdb/redolog3a.dbf','/redolog2/adpdb/redolog3b.dbf' ) SIZE 10M
  DATAFILE '/db1/adpdb/system.dbf' SIZE 200M
  CHARACTER SET WE8ISO8859P1
  national character set utf8
  EXTENT MANAGEMENT LOCAL
  undo tablespace ts_undo
    datafile '/db1/adpdb/undo.dbf'
    size 50M
  default temporary tablespace ts_temp
    tempfile '/db1/adpdb/temp01.dbf'
    size 50M autoextend on next 50M maxsize 300M;
If an ORA-01031: insufficient privileges is returned, that means most likely, that the current user is not in the dba group (on unix), or the ORA_DBA (windows).
If the init.ora file is not at its default location or has not been found with the pfile attribute, an ORA-01078: failure in processing system parameters and an LRM-00109: could not open parameter file '/appl/oracle/product/9.2.0.2/dbs/initadpdb.ora' error is issued.
The create database command also executes a file whose name is determined by the (hidden) init parameter _init_sql_file.
After the creation of the database, it can be mounted and opened for use.

Completing the work

Run
  • ?/rdbms/admin/catalog.sql
  • ?/rdbms/admin/catproc.sql and
  • ?/rdbms/admin/caths.sql
as sys.
catalog.sql calls, for example, catexp.sql which is a requirement for exp, or dbmsstdx.sql which is a requirement to create triggers.
The user system might also want to run ?/sqlplus/admin/pupbld.sql. pupbld.sql creates a table that allows to block someone from using sql plus.
The ? shortcut will be replaced with the value of $ORACLE_HOME by SQL PLUS.
These scripts do quite a bit of work:
Of course, tablespaces and users and tables and so on must be created according to the use of the database.

Setting up database to using java

Also call @?/javavm/install/initjvm if you want to enable the JServer option(?).

Oracle managed files

Refer also to DB_CREATE_ONLINE_LOG_DEST_n and DB_CREATE_FILE_DEST for Oracle-managed files.

Errors while creating database

If there is an error while the database is created, such as a ORA-01092: ORACLE instance terminated. Disconnection forced, the alert log should be consulted. This file most probably contains a more desriptive error message.
If the error occurs at a very early stage, there won't be an alert.log. In this case, the error will most probably be found in a trace file in udump directory.

Thanks

Thanks to Pawe Hajnas and Pierre van Tiggelen who each reported one error on this page.