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

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.