How to create an Oracle 10g database manually on windows

Ok Guys, This is  how I create an Oracle 10g database manually on Windows :

*******************************************************************

set ORACLE_SID=NEW1
set PATH=%ORACLE_HOME%\BIN;%PATH%

mkdir F:\oracle\admin\%ORACLE_SID%\bdump
mkdir F:\oracle\admin\%ORACLE_SID%\cdump
mkdir F:\oracle\admin\%ORACLE_SID%\adump
mkdir F:\oracle\admin\%ORACLE_SID%\udump
mkdir F:\oracle\admin\%ORACLE_SID%\pfile
mkdir F:\oracle\oradata\%ORACLE_SID%
mkdir F:\oracle\arch\%ORACLE_SID%

REM copy C:\oracle\product\10.2.0\db_1\database\initorcl.ora  C:\oracle\product\10.2.0\db_1\database\init%ORACLE_SID%.ora
REM You need to copy pfile from sample or any other pfile and make changes in some init parameters like :
REM udump,bdump,cdump,adump,control_files,db_name,db_block_size
REM

In your pfile (init%ORACLE_SID%.ora), make basic modifications like below :

new1.__db_cache_size=188743680
new1.__java_pool_size=4194304
new1.__large_pool_size=4194304
new1.__shared_pool_size=88080384
new1.__streams_pool_size=0
*.audit_file_dest='F:\oracle\admin\NEW1\adump'
*.background_dump_dest='F:\oracle\admin\NEW1\bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\oradata\NEW1\control01.ctl','F:\oracle\oradata\NEW1\control02.ctl','F:\oracle\oradata\NEW1\control03.ctl'
*.core_dump_dest='F:\oracle\admin\NEW1\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='NEW1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=NEW1XDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=96468992
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=290455552
*.undo_management='AUTO'
*.undo_tablespace='UNDO'
*.user_dump_dest='F:\oracle\admin\NEW1\udump'

oradim -new -sid %ORACLE_SID% -intpwd sys -startmode M -PFILE C:\oracle\product\10.2.0\db_1\database\init%ORACLE_SID%.ora

sqlplus / as sysdba @F:\oracle\admin\NEW1\cr8db.sql

And in  cr8db.sql, I have actual database creation script :

startup nomount pfile='C:\oracle\product\10.2.0\db_1\database\init%ORACLE_SID%.ora';

create spfile from pfile='C:\oracle\product\10.2.0\db_1\database\init%ORACLE_SID%.ora';

shut

startup nomount

-- You need to edit the database creation script to locate data files as per business requirement

create database NEW1
  logfile   group 1 ('F:\oracle\oradata\%ORACLE_SID%\redo1.log') size 10M,
            group 2 ('F:\oracle\oradata\%ORACLE_SID%\redo2.log') size 10M,
            group 3 ('F:\oracle\oradata\%ORACLE_SID%\redo3.log') size 10M
  character set          WE8ISO8859P1
  national character set utf8
  datafile 'F:\oracle\oradata\%ORACLE_SID%\system.dbf'
            size 500M
            autoextend on
            next 10M maxsize unlimited
            extent management local
  sysaux datafile 'F:\oracle\oradata\%ORACLE_SID%\sysaux.dbf'
            size 400M
            autoextend on
            next 10M
            maxsize unlimited
  undo tablespace undo
            datafile 'F:\oracle\oradata\%ORACLE_SID%\undo.dbf'
            size 50M
  default temporary tablespace temp
            tempfile 'F:\oracle\oradata\%ORACLE_SID%\temp.dbf'
            size 10M;


connect /as sysdba




spool dictionary.out

@%ORACLE_HOME%\rdbms\admin\catalog.sql
@%ORACLE_HOME%\rdbms\admin\catblock.sql
@%ORACLE_HOME%\rdbms\admin\catproc.sql
@%ORACLE_HOME%\rdbms\admin\catoctk.sql
@%ORACLE_HOME%\rdbms\admin\owminst.plb
@%ORACLE_HOME%\javavm\install\initjvm.sql
@%ORACLE_HOME%\xdk\admin\initxml.sql
@%ORACLE_HOME%\xdk\admin\xmlja.sql
@%ORACLE_HOME%\rdbms\admin\catjava.sql
@%ORACLE_HOME%\rdbms\admin\catexf.sql

connect SYSTEM/manager
spool sqlplus.log@%ORACLE_HOME%\sqlplus\admin\pupbld.sql

@%ORACLE_HOME%\sqlplus\admin\help\hlpbld.sql helpus.sql
spool off ;
spool off;



conn / as sysdba

shut immediate

startup

****************************************************************************


Here we are, the database is created and ready for you.


Thanks,
Shekhar

My Oracle DBA Blog

Hi Guys,

I will be sharing my Oracle DBA experience and knowledge here.
But whatever contents or my experience notes I am mentioning here are purely related to my own experience only, in case if you would like to test these on your systems then it will be whole and sole on your own responsibility only.

Thanks,
Shekhar