Manual Creation of database in windows with oracle 10g
STEP: 1
Now open a command prompt and set oracle SID as
C:Documents and Settingsadministrator.SUVIN>set oracle_sid=samdb
STEP: 2
Start a windows service with internal password oradim –new –sid
intpwd is the syntax.
C:Documents and Settingsadministrator.SUVIN>oradim -new -sid samdb -intpwd samdb Instance created.
STEP: 3
Create a directory called mandb. In my case I created it in d: drive
D:samdbdata
D:samdbredo
D:samdbcontrol
D:samdbbdump
D:samdbcdump
D:samdbudump
D:samdbpfile
D:samdbspfile
D:samdbtemp
(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 initsamdb.ora in D:mandbpfile
Give the necessary parameter in the pfile.I gave thelease amount of parameters.
—-Contents of initmandb.ora—–
db_name=samdb
instance_name=samdb
db_block_size=8192
db_cache_size=25165824
background_dump_dest=D:samdbbdump
core_dump_dest=D:samdbcdump
user_dump_dest=D:samdbudump
control_files=(“D:samdbcontrolcontrol01.ctl”, “D:samdbcontrolCONTROL02.ctl”,
“D:samdbcontrolCONTROL03.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/samdb as sysdba then you should see that you
are connected to an idle instance
C:Documents and Settingsadministrator.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/samdb as sysdba
Connected to an idle instance.
SQL>
STEP: 6
Now start the instance in nomount mode as,
startup nomount pfile=D:samdbpfileinitmandb.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:samdbpfileinitsamdb.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:samdbredoredo01.log’) SIZE 50M,
GROUP 2(‘D:samdbredoredo02.log’) SIZE 50M,
GROUP 3(‘D:samdbredoredo03.log’) SIZE 50M
DATAFILE ‘D:samdbdatasystem01.dbf’ SIZE 200M autoextend on
SYSAUX DATAFILE ‘D:samdbdatasysaux01.dbf’ SIZE 200M
UNDO TABLESPACE UNDOTBS
DATAFILE ‘D:samdbdataUNDOTBS.dbf’ SIZE 100M
default temporary tablespace TEMP
tempfile ‘D:samdbtemptemp_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:samdbredoredo01.log’) SIZE 50M,
4 GROUP 2(‘D:samdbredoredo02.log’) SIZE 50M,
5 GROUP 3(‘D:samdbredoredo03.log’) SIZE 50M
6 DATAFILE ‘D:samdbdatasystem01.dbf’ SIZE 200M autoextend on
7 SYSAUX DATAFILE ‘D:samdbdatasysaux01.dbf’ SIZE 200M
8 UNDO TABLESPACE UNDOTBS
9 DATAFILE ‘D:samdbdataUNDOTBS.dbf’ SIZE 100M
10 default temporary tablespace TEMP
11 tempfile ‘D:samdbtemptemp_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:samdb 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/samdb as sysdba
check which database you are in by using the following command
show parameter service_name
STEP: 10
Now place the pfile initsamdb.ora in this lacation
D:Oracleproduct10.2.0db_1database
Then try to connect like this
SQL>conn sys/samdb 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:Oracleproduct10.2.0db_1database
C:Documents and Settingsadministrator.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/samdb 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:ORANTrdbmsadmincatalog.sql
Execute catproc.sql SQL>@d:ORANTrdbmsadmincatproc.sql
STEP: 12
Now you need to edit the following files
D:Oracleproduct10.2.0db_1NETWORKADMINtnsnames.ora
D:Oracleproduct10.2.0db_1NETWORKADMINlistener.ora
Here are the files that I used…you can change those accordingly
# TNSNAMES.ORA Network Configuration File: D:ORANTnetworkadmintnsnames.ora
# Generated by Oracle configuration tools.
SAMADHAN=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = samadhan)
)
)
samdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = inal075)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = samdb)
)
)
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:ORANTnetworkadminlistener.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 = samadhan)
(ORACLE_HOME = D:ORANT)
(SID_NAME = samadhan)
)
(SID_DESC =
(GLOBAL_DBNAME = samdb)
(ORACLE_HOME = D:samdb)
(SID_NAME = samdb)
)
)
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
Expert are always welcome for their valuable comment or suggestion for the above post.
samadhandba 