Creating an Oracle (XE) database manually on XP

There are two ways for creating an Oracle database, one is using Oracle Database Configuration Assistant and the other is manually, I'll provide a small step by step guide for the latter on windows XP.

This are the main steps:

1. Create the directory structure
2. Declare an oracle SID name.
3. Create a windows service and password file
4. Create the init.ora file
5. Start the instance in nomount mode
6. Use the Create database command
7. Create Data Dictionary


1. Create the directory structure

Under the admin directory (C:\oraclexe\app\oracle\admin\) we have to create the structure for the new database (this is called OFA Oracle Flexible Arquitecture), the directory is called oraXE which will be the name of the database, and the directories adump, bdump, cdump, dpdump, pfile, udump are created in it:


2. Declare an oracle SID name.

Using the this command we declare the variable for oracle SID. Since could be more than one oracle instance running, oracle uses the SID to difference them.

set ORACLE_SID=oraXE

3. Create a windows service and password file

Every instance on windows requires a windows service which can be created using oradim tool, the created service can be checked in the services list: type services.msc in the console or a link can be found in the control panel -> admin tools -> Services. Also a password file is created under database directory (ORACLE_HOME\database) with the SID embedded in the name (PWDoraXE.ora), this password file is used to authenticate the DBA before starting an instance.

oradim -new -sid %ORACLE_SID% -intpwd passwordhere -startmode M

Important : Remember to stop the default service for the "XE" database which is called "OracleServiceXE" and start the service just created "OracleServiceoraXE" for our new database in the services console.

4. Create the init.ora file
Open a text editor, add the lines below and save it as initoraXE.ora in C:\oraclexe\app\oracle\admin\oraXE\pfile\. This are just some basic initial parameters, the ones that are not specified are taken by default.

control_files = (C:\oraclexe\oradata\oraXE\control01.ctl,
C:\oraclexe\oradata\oraXE\control02.ctl,
C:\oraclexe\oradata\oraXE\control03.ctl)
undo_management = auto
db_name         = oraXE
db_block_size       = 8192

control_files : Here we define where will be our control files, those are used for store management information of the database like location of the datafiles, timestamp of the creation of the database,etc. In this case there are three files copies, its recommended to store each copy on different physical disk which is called multiplexing.

undo_management: this parameter is used for choosing between automatic undo management (AUTO) or manually undo management (MANUAL, this is the default) in the latter is needed to create , size and monitor closely undo (rollback) segments, it's recommended to be set to AUTO.

db_name : The database name must be the same used in the create statement.

db_block_size : The default or standard database block size, this is the mininal logical unit of storage in oracle this should be a multiple of the operating system block size.




5. Start the instance in nomount mode

Before you try to connect to start the instance check that you have the right ORACLE_SID:


echo %ORACLE_SID%
oraXE



You can now authenticate through the operating system (no login or password) to start up the database, like this:

sqlplus /nolog

sql>connect / as sysdba

sql>startup nomount pfile=C:\oraclexe\app\oracle\admin\oraXE\pfile\initoraXE.ora

ORACLE instance started.

Total System Global Area  113246208 bytes
Fixed Size                  1246556 bytes
Variable Size              58722980 bytes
Database Buffers           50331648 bytes
Redo Buffers                2945024 bytes

sqlplus /nolog : start the sqlplus client , the "/nolog" is to not be prompted for username/password.

connect / as sysdba : authenticate through the operating system with sysdba privileges. If you don't have the right ORACLE_SID ("oraXE") you may get an "ORA-12560: TNS:protocol adapter error".

startup nomount... : we must startup the db in nomount mode because at this point there a no database (physical files) to mount. The pfile that created in step 4 (C:\oraclexe\app\oracle\admin\oraXE\pfile\initoraXE.ora) must be specified otherwise it will look for the default location. If you get a ORA-01081 error is because probably the "XE" database is already started and you can't have two databases mounted at the same time, so first shutdown the running database using the shutdown command.


6. Use the Create database command

With this command we create the database:

sql> create database oraXE
logfile   group 1 ('C:\oraclexe\oradata\oraXE\redo1.log') size 10M,
group 2 ('C:\oraclexe\oradata\oraXE\redo2.log') size 10M,
group 3 ('C:\oraclexe\oradata\oraXE\redo3.log') size 10M
character set          WE8ISO8859P1
national character set utf8
datafile 'C:\oraclexe\oradata\oraXE\system.dbf'
size 50M
autoextend on
next 10M maxsize unlimited
extent management local
sysaux datafile 'C:\oraclexe\oradata\oraXE\sysaux.dbf'
size 10M
autoextend on
next 10M
maxsize unlimited
undo tablespace undo
datafile 'C:\oraclexe\oradata\oraXE\undo.dbf'
size 10M
autoextend on
default temporary tablespace temp
tempfile 'C:\oraclexe\oradata\oraXE\temp.dbf'
size 10M
autoextend on;

Database created.

7. Create Data Dictionary

Finally, the scripts for creating the data dictionary are under ORACLE_HOME\rdbms\admin.

Run:

sql> @?\rdbms\admin\catalog.sql
sql> @?\rdbms\admin\catproc.sql

? this will be replaced by the value of $ORACLE_HOME
catalog.sql creates the data dictionary views.
catproc.sql creates the dictionary items necessary for PL/SQL functionality.