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
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
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=oraXE3. 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
Open a text editor, add the lines below and save it as
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
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 10Mautoextend ondefault temporary tablespace temp
tempfile 'C:\oraclexe\oradata\oraXE\temp.dbf'size 10Mautoextend 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.sqlsql>@?\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.
