René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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:
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 instanceoradim -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
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 filesErrors 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.
|