How To Create Oracle 11g Database Manually on Windows

Below steps will help you to create 11.2.0.1 oracle database manually on windows platform. These steps would remain same on all the Windows version such as XP, Vista etc. Before starting the creation of the database, ensure you have Oracle Database binaries installed.

1. Set up environment variables

C:\>set ORACLE_HOME=c:\oracle\product\11.2.0\dbhome_1
C:\>set PATH=%ORACLE_HOME%\bin;%PATH%
C:\>set ORACLE_SID=ORADB

2. Create required directories

C:\>mkdir c:\oracle\db\oradb\admin\adump  
C:\>mkdir c:\oracle\db\oradb\diag
C:\>mkdir c:\oracle\db\oradb\flash_recovery_area
C:\>mkdir c:\oracle\db\oradb\oradata

3. Create the parameter file

Create the parameter file named initORADB.ora under ORACLE_HOME/database directorydb_name='ORADB'
db_block_size=8192
memory_target=500m
processes=100
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
compatible ='11.2.0'
audit_trail ='db'
db_name='ORADB'
db_recovery_file_dest_size=5g
db_recovery_file_dest='c:\oracle\db\oradb\flash_recovery_area'
audit_file_dest='c:\oracle\db\oradb\admin\adump'
diagnostic_dest='c:\oracle\db\oradb\diag'
control_files = ('c:\oracle\db\oradb\oradata\control1.ctl', 'c:\oracle\db\oradb\oradata\control2.ctl', 'c:\oracle\db\oradb\oradata\control3.ctl')

4. Create a Windows service 

C:\>oradim -NEW -SID ORADB -STARTMODE manual
Instance created.

Check the status of service

C:\>sc query oracleserviceORADB
SERVICE_NAME: oracleserviceORADB
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
       (STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

5.Connect to instance and Start the instance with NOMOUNT mode by newly created pfile

C:\>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 23 07:39:54 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='c:\oracle\product\11.2.0\dbhome_1\dbs\initORADB.ora'
ORACLE instance started.
Total System Global Area  523108352 bytes
Fixed Size                  1375704 bytes
Variable Size             314573352 bytes
Database Buffers          201326592 bytes
Redo Buffers                5832704 bytes
Instance started.

6.Execute the CREATE DATABASE Command

CREATE DATABASE ORADB 
    USER sys IDENTIFIED BY sys
    USER system IDENTIFIED BY system
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 200
    MAXINSTANCES 1
    MAXLOGHISTORY 500
LOGFILE
GROUP 1 (
    'c:\oracle\db\oradb\oradata\redo01a.rdo',
    'c:\oracle\db\oradb\oradata\redo01b.rdo'
    ) SIZE 50M,
GROUP 2 (
    'c:\oracle\db\oradb\oradata\redo02a.rdo',
    'c:\oracle\db\oradb\oradata\redo02b.rdo'
    ) SIZE 50M,
GROUP 3 (
    'c:\oracle\db\oradb\oradata\redo03a.rdo',
    'c:\oracle\db\oradb\oradata\redo03b.rdo'
    ) SIZE 50M
DATAFILE 'c:\oracle\db\oradb\oradata\system01.dbf' SIZE 300M EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'c:\oracle\db\oradb\oradata\sysaux01.dbf' SIZE 200M
UNDO TABLESPACE UNDOTBS1 DATAFILE 'c:\oracle\db\oradb\oradata\undotbs01.dbf' SIZE 300M AUTOEXTEND OFF
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'c:\oracle\db\oradb\oradata\temp01.dbf' SIZE 200M REUSE AUTOEXTEND OFF
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET UTF8;

7. Create data dictionary objects 

Run the catalog.sql and catproc.sql scripts to create the dictionary objects.
SQL> @%ORACLE_HOME%\rdbms\admin\catalog.sql
SQL> @%ORACLE_HOME%\rdbms\admin\catproc.sql
SQL> connect system/system
SQL> @%ORACLE_HOME%\sqlplus\admin\pupbld.sql
SQL> conn / as sysdba

8. Create spfile from pfile, So that db will starts with spfile by default

SQL> Create spfile from pfile;
File created

9. Enable Archiving

To enable archiving shutdown the database and startup it in mount status. Enable the archiving and then open the database.
SQL> connect /as sysdba
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  523108352 bytes
Fixed Size                  1375704 bytes
Variable Size             314573352 bytes
Database Buffers          201326592 bytes
Redo Buffers                5832704 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     12
Next log sequence to archive   13
Current log sequence           13

10. Check for any invalid component or objects

SQL> select comp_id,version,status from dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ -------
CATALOG                        11.2.0.1.0                     VALID
CATPROC                        11.2.0.1.0                     VALID
2 rows selected.

SQL> select owner,object_name,object_type from all_objects where status='INVALID';
no rows selected

Note:If any inavlid objects are found then run ultrp.sql to validate objects

SQL> @%ORACLE_HOME%/rdbms/admin/utlrp.sql