|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
Creating a database consists basically of the following steps:
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/22.214.171.124 export ORACLE_HOME PATH=$PATH:$ORACLE_HOME/bin
ORACLE_SID=adpdb export ORACLE_SID
Windows Users will set the variable like so:
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
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
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/126.96.36.199/dbs >sqlplus "/ as sysdba" SQL*Plus: Release 188.8.131.52.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/184.108.40.206/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
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
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 to Pawe Hajnas and Pierre van Tiggelen who each reported one error on this page.