Create a database manually on windows in 11g?

-- 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

Advertisements

What are the command line options that can be passed to runInstaller?

Here are some of the options that can be passed to runInstaller
-slient – Slient mode
-debug – To get debug information
-noclusterEnabled – Disabled so no cluster nodes specified so it doesn’t install cluster aware
To turn on debugging when running installer one can pass the following flags -J-DTRACING.ENABLED=true -J-DTRACING.LEVEL=2