A collection of tutorials, code and tools to help you get better using Oracle
06 June 2011
These are notes about creating a database manually. The DBCA can also be used to do this much more easily.
First, create the directories you need for the datafiles. On a non production system, I like to keep all the files for a database under a single directory so it is easy to delete the database later, eg:
mkdir -p /mnt/raid/dborafiles/11gr2/datafiles mkdir -p /mnt/raid/dborafiles/11gr2/redo
For a production setup, each of these areas is probably a separate mount point on different disks etc.
This file should go into $ORACLE_HOME/dbs and be called initSID.ora:
control_files = (/mnt/raid/dborafiles/ora11gr2/datafiles/control01.ora, /mnt/raid/dborafiles/ora11gr2/datafiles/control02.ora, /mnt/raid/dborafiles/ora11gr2/datafiles/control03.ora) undo_management = auto db_name = ora11gr2 db_block_size = 8192 # 11G (oracle will create subdir diag and all the required subdirs) # This is a non-default location for the diag files. Normally they are created # under $ORACLE_BASE, but for non production setups I like to keep all the files # for a database instance under a single folder. diagnostic_dest = /mnt/raid/dborafiles/ora11gr2
$ sqlplus /nolog SQL11g> connect / as sysdba
$ create SPFILE from PFILE='/dboracle/product/11.2.0/dbhome_1/dbs/init11gr2.ora'
SQL11g> startup nomount
create database ora11gr2 logfile group 1 ('/mnt/raid/dborafiles/ora11gr2/redo/redo1.log') size 10M, group 2 ('/mnt/raid/dborafiles/ora11gr2/redo/redo2.log') size 10M, group 3 ('/mnt/raid/dborafiles/ora11gr2/redo/redo3.log') size 10M character set utf8 national character set utf8 datafile '/mnt/raid/dborafiles/ora11gr2/datafiles/system.dbf' size 50M autoextend on next 10M extent management local sysaux datafile '/mnt/raid/dborafiles/ora11gr2/datafiles/sysaux.dbf' size 10M autoextend on next 10M undo tablespace undo datafile '/mnt/raid/dborafiles/ora11gr2/datafiles/undo.dbf' size 10M autoextend on default temporary tablespace temp tempfile '/mnt/raid/dborafiles/ora11gr2/datafiles/temp.dbf' size 10M autoextend on
( TODO - unsure about setting max files sizes on these files )
SQL11G> @$ORACLE_HOME/rdbms/admin/catalog.sql SQL11G> @$ORACLE_HOME/rdbms/admin/catproc.sql
(not doing this doesn't cause any harm, but a warning is displayed when logging into SQLPLUS if it is not run)
The database is now basically ready to use, but there no users and no users tablespace. Note it is also NOT in archive log mode, so is certainly not production ready, but may be good enough for a non-backed up dev instance.
SQL>CREATE TABLESPACE users DATAFILE '/mnt/raid/dborafiles/ora11gr2/datafiles/users_01.dbf' SIZE 50M autoextend on maxsize 2048M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
SQL11G> create user sodonnel identified by sodonnel default tablespace users temporary tablespace temp; SQL11G> alter user sodonnel quota unlimited on users; SQL11G> grant connect, create procedure, create table, alter session to sodonnel;
Add a line to /etc/oratab to tell Oracle about the instance. This is used by the dbstart command, which will start all the database specified in this file:
To start all instances use dbstart and to stop use dbshut.
TODO - control script to autostart databases when the machine boots.
At this point, only people on the local machine can connect to the database, so the last step is to setup the listener. All you need to do here is add a file called listener.ora in $ORACLE_HOME/network/admin, and have it contain something like the following:
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) )
Creating a tnsnames.ora file at this point would be a good idea too. It also goes into $ORACLE_HOME/network/admin:
ora11gr2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = localhost)(Port = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ora11gr2) ) )