-- Step 1) set up environment variables needed (ORACLE_HOME, ORACLE_SID and add ORACLE_HOME\bin to PATH)
set ORACLE_HOME=e:\oracle\11g\product\11.2.0.1
set PATH=%ORACLE_HOME%\bin;%PATH%
set ORACLE_SID=TESTDB

-- Step 2) Sample pfile (parameter file) for the instance and place it in %ORCALE_HOME%\dbs
db_name='TESTDB'
memory_target=500m
processes=150
audit_file_dest='E:\oracle\DB\TESTDB\admin\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
diagnostic_dest=E:\oracle\DB\TESTDB\diagnostic_dest
dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ("E:\oracle\DB\TESTDB\oradata\control1.ora", "E:\oracle\DB\TESTDB\oradata\control2.ora")
compatible ='11.1.0'

-- Step 3) Create directories mentioned in the spfile
E:\>mkdir E:\oracle\DB\TESTDB\admin\adump
E:\>mkdir E:\oracle\DB\TESTDB\diagnostic_dest
E:\>mkdir E:\oracle\DB\TESTDB\oradata

-- Step 4) Creation of service for TESTDB and set startup mode to be manual
E:\>oradim -NEW -SID TESTDB -STARTMODE manual
Instance created.

-- Step 5) Start SQL*PLus to create database
E:\> sqlplus "/nolog"
SQL> connect sys/oracle as sysdba
Connected

-- Step 6) Start instance in nomount with the pfile
SQL> startup nomount pfile="E:\oracle\11g\product\11.2.0.1\dbs\initTESTDB.ora"

ORACLE instance started.

Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 314574544 bytes
Database Buffers 197132288 bytes
Redo Buffers 8052736 bytes

-- Step 7) Create database
SQL>
CREATE DATABASE TESTDB
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
DATAFILE 'E:\oracle\DB\TESTDB\oradata\SYSTEM01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1000M
SYSAUX DATAFILE 'E:\oracle\DB\TESTDB\oradata\SYSAUX01.DAT' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
DEFAULT TABLESPACE USERS DATAFILE 'E:\oracle\DB\TESTDB\oradata\USERS01.DBF' SIZE 50M AUTOEXTEND ON MAXSIZE 200M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'E:\oracle\DB\TESTDB\oradata\TEMP01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M
UNDO TABLESPACE "UNDOTBS1" DATAFILE 'E:\oracle\DB\TESTDB\oradata\UNDOTBS01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('E:\oracle\DB\TESTDB\oradata\REDO01a.LOG', 'E:\oracle\DB\TESTDB\oradata\REDO01b.LOG') SIZE 100M,
GROUP 2 ('E:\oracle\DB\TESTDB\oradata\REDO02a.LOG', 'E:\oracle\DB\TESTDB\oradata\REDO02b.LOG') SIZE 100M,
GROUP 3 ('E:\oracle\DB\TESTDB\oradata\REDO03a.LOG', 'E:\oracle\DB\TESTDB\oradata\REDO03b.LOG') SIZE 100M
EXTENT MANAGEMENT LOCAL
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 254
MAXINSTANCES 1;

-- Step 8) Create catalog data dictionary.
SQL> @?/rdbms/admin/catalog.sql

-- Step 9) Create all structures required for PL/SQL
SQL> @?/rdbms/admin/catproc.sql

-- Step 10) Create spfile from pfile and restart instance
SQL> create spfile from pfile='E:\oracle\11g\product\11.2.0.1\dbs\initTESTDB.ora';
File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 314574544 bytes
Database Buffers 197132288 bytes
Redo Buffers 8052736 bytes
Database mounted.
Database opened.
SQL> shutdown immediate;
Database closed.
Database dismounted.
SQL> show parameter spfile;

NAME TYPE VALUE
———————————— ———– ——————————
spfile string E:\ORACLE\11G\PRODUCT\11.2.0.1\DATABASE\SPFILETESTDB.ORA

-- Step 11) How to stop/start the services and instance
-- Show service status
E:\>sc query OracleServiceTESTDB
SERVICE_NAME: OracleServiceTESTDB
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

-- Stop service, the status of the service changes to STOP_PENDING
E:\>sc stop OracleServiceTESTDB
SERVICE_NAME: OracleServiceTESTDB
TYPE : 10 WIN32_OWN_PROCESS
STATE : 3 STOP_PENDING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x1
WAIT_HINT : 0x15f90

-- Check service again after waiting for a bit
E:\>sc query OracleServiceTESTDB
SERVICE_NAME: OracleServiceTESTDB
TYPE : 10 WIN32_OWN_PROCESS
STATE : 1 STOPPED
(NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN))
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

-- Start service, the status will change to START_PENDING
E:\>sc start OracleServiceTESTDB
SERVICE_NAME: OracleServiceTESTDB
TYPE : 10 WIN32_OWN_PROCESS
STATE : 2 START_PENDING
(NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN))
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x7d0
PID : 3784
FLAGS :

-- Recheck status of the service
E:\>sc query OracleServiceTESTDB
SERVICE_NAME: OracleServiceTESTDB
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

-- Re-login to start the database, if you don't relogin, you will get the ORA-03113: end-of-file on communication channel error message
E:\>sqlplus "/nolog"
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 7 10:01:21 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 314574544 bytes
Database Buffers 197132288 bytes
Redo Buffers 8052736 bytes