Manual Database Creation in Oracle 10g

Manual Creation of database in windows with oracle 10g

STEP: 1

Now open a command prompt and set oracle SID as set oracle_sid=mandb

--------------------------------------------------------------------------------
C:\Documents and Settings\administrator.SUVIN>set oracle_sid=mandb
--------------------------------------------------------------------------------

STEP: 2

Start a windows service with internal password oradim –new –sid
intpwd is the syntax.

--------------------------------------------------------------------------------
C:\Documents and Settings\administrator.SUVIN>oradim -new -sid mandb -intpwd mandb
Instance created.
--------------------------------------------------------------------------------

STEP: 3

Create a directory called mandb. In my case I created it in d:\ drive
D:\mandb\data
D:\mandb\redo
D:\mandb\control
D:\mandb\bdump
D:\mandb\cdump
D:\mandb\udump
D:\mandb\pfile
D:\mandb\spfile
D:\mandb\temp

(Note: all my parameter files and .sql file that are going to discuss following are based on my
location, you can change the location according to yours)

STEP: 4

Now create a pfile for the database namely initmandb.ora in D:\mandb\pfile
Give the necessary parameter in the pfile.I gave thelease amount of parameters.

----Contents of initmandb.ora-----

db_name=mandb
instance_name=mandb

db_block_size=8192
db_cache_size=25165824

background_dump_dest=D:\mandb\bdump
core_dump_dest=D:\mandb\cdump
user_dump_dest=D:\mandb\udump

control_files=("D:\mandb\control\control01.ctl", "D:\mandb\control\CONTROL02.ctl",
"D:\mandb\control\CONTROL03.ctl")

shared_pool_size=50331648

undo_management=AUTO

STEP: 5

Now type following in your current command prompt sqlplus/nolog and in sql
prompt type conn sys/mandb as sysdba then you should see that you
are connected to an idle instance

--------------------------------------------------------------------------------
C:\Documents and Settings\administrator.SUVIN>sqlplus/nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 1 15:18:36 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys/mandb as sysdba
Connected to an idle instance.
SQL>
--------------------------------------------------------------------------------

STEP: 6

Now start the instance in nomount mode as,

startup nomount pfile=D:\mandb\pfile\initmandb.ora

why are you starting the database in nomount mode ?
The reason is still we are not created control files. “An instance would be started in
the NOMOUNT stage only during database creation or the re-creation of control files.

------------------------------------------------------------------------------------
SQL> startup nomount pfile=D:\mandb\pfile\initmandb.ora
ORACLE instance started.

Total System Global Area 109051904 bytes
Fixed Size 1247540 bytes
Variable Size 75499212 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
SQL>

------------------------------------------------------------------------------------

STEP: 7

Now create the database by the following command...

CREATE DATABASE mandb
LOGFILE
GROUP 1('D:\mandb\redo\redo01.log') SIZE 50M,
GROUP 2('D:\mandb\redo\redo02.log') SIZE 50M,
GROUP 3('D:\mandb\redo\redo03.log') SIZE 50M
DATAFILE 'D:\mandb\data\system01.dbf' SIZE 200M autoextend on
SYSAUX DATAFILE 'D:\mandb\data\sysaux01.dbf' SIZE 200M
UNDO TABLESPACE UNDOTBS
DATAFILE 'D:\mandb\data\UNDOTBS.dbf' SIZE 100M
default temporary tablespace TEMP
tempfile 'D:\mandb\temp\temp_01_db01.dbf' SIZE 50M
extent management local uniform size 128k
;

Run the script in the SQL prompt
--------------------------------------------------------------------------------------
SQL> CREATE DATABASE mandb
2 LOGFILE
3 GROUP 1('D:\mandb\redo\redo01.log') SIZE 50M,
4 GROUP 2('D:\mandb\redo\redo02.log') SIZE 50M,
5 GROUP 3('D:\mandb\redo\redo03.log') SIZE 50M
6 DATAFILE 'D:\mandb\data\system01.dbf' SIZE 200M autoextend on
7 SYSAUX DATAFILE 'D:\mandb\data\sysaux01.dbf' SIZE 200M
8 UNDO TABLESPACE UNDOTBS
9 DATAFILE 'D:\mandb\data\UNDOTBS.dbf' SIZE 100M
10 default temporary tablespace TEMP
11 tempfile 'D:\mandb\temp\temp_01_db01.dbf' SIZE 50M
12 extent management local uniform size 128k
13 ;

Database created.

SQL>

once you run this you can see the control files, redo log file, Alert log file, .dbf files and .trc
(Background Trace files & User Trace files) files are created in D:\mandb\ folder.

--------------------------------------------------------------------------------------

STEP: 8

Now you can shutdown the database using shutdown command.

---------------------------------
Database dismounted.
ORACLE instance shut down.
SQL>
---------------------------------

Once the database shutdown reboot your PC

STEP: 9

Connect again as sysdba to default database

---------------------------------
sqlplus/nolog,
SQL>conn sys/mandb as sysdba
---------------------------------

check which database you are in by using the following command

show parameter service_name

STEP: 10

Now place the pfile initmandb.ora in this lacation
D:\Oracle\product\10.2.0\db_1\database\

Then try to connect like this

SQL>conn sys/mandb as sysdba

and startup your database.

You dont have to mention your pfile in th startup command since you have placed it in the
default pfile location

D:\Oracle\product\10.2.0\db_1\database\

------------------------------------------------------------------

C:\Documents and Settings\administrator.SUVIN>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 1 15:48:15 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sys/mandb as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 109051904 bytes
Fixed Size 1247540 bytes
Variable Size 75499212 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL>

------------------------------------------------------------------

STEP: 11

Execute catalog.sql SQL>@d:\ORANT\rdbms\admin\catalog.sql
Execute catproc.sql SQL>@d:\ORANT\rdbms\admin\catproc.sql

STEP: 12



Now you need to edit the following files

D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora

Here are the files that I used…you can change those accordingly
# TNSNAMES.ORA Network Configuration File: D:\ORANT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
SURANGA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = suranga)
)
)
mandb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = inal075)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mandb)
)
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)
6
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)



# LISTENER.ORA Network Configuration File: D:\ORANT\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\ORANT)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = suranga)
(ORACLE_HOME = D:\ORANT)
(SID_NAME = suranga)
)
(SID_DESC =
(GLOBAL_DBNAME = mandb)
(ORACLE_HOME = D:\mandb)
(SID_NAME = mandb)
)
)
Now start configuring tns Service Name in Client System from where you need to
access the Oracle 10g Enterprise Server
Open Net Manager
Start->Programs->Oracle10gHome->Configuration and Migration Tools->Net Manager

Create a User in mandb Database and test the connection in Net Manager
by changing the login.