Create Oracle 9i database in windows manually

Create Oracle 9i database in windows manually

“dbtest” is the database that i am going to create.
create one folder called dbtest (Example: c:\oracle\dbtest)

1. Open the console(Run –> cmd)
2. Set oracle SID as ‘set oracle_sid=dbtest’
3. If dbtest windows service is not created then execute this
c:\>oradim -new -sid dbtest -intpwd dbpassword
4. Create a directory called dbtest. In this case I created it in c:\oracle\dbtest
( you can change the location according to yours )
5. Create your initdbtest.ora that I saved it in c:\oracle\Ora91\database.
This is the default location that oracle will check for pfile. Rather you can save it any where but u should mention the file

during startup; startup pfile=path

Following is the static parameter file that I have used in my database creation
Note: All paths that i had given depends on my configuration. Make sure paths which you had given is correct
(This is the simple way to create a parameter File. You can add whichever parameters you want)

##########################################
db_name=dbtest
instance_name=dbtest
control_files=c:\oracle\dbtest\control\c.ctl
background_dump_dest=c:\oracle\dbtest\bdump
core_dump_dest=c:\oracle\dbtest\cdump
user_dump_dest=c:\oracle\dbtest\udump
db_block_size=4096
db_cache_size=33554432
compatible=9.0.1.1.1
undo_management=AUTO
undo_retention=1
undo_tablespace=UNDOTBS
pga_aggregate_target=25165824
sort_area_size=524288
############################################

6. Open Another command prompt
7. Execute this command set oracle_sid=dbtest
8. Execute this command “sqlplus/nolog” to get the sql prompt
9. Conn sys as sysdba ;
SQL> conn sys as sysdba
10. Now Start the instance in nomount mode
SQL> startup nomount
Why are you starting in nomount mode?
Bcoz still we are not created control files. An instance should be started in NOMOUNT mode only
during the database creation and recreation of control files
11. Create a sql script file to create a database.
(Here i m giving you an example )
######################################
create database dbtest
LOGFILE GROUP 1(‘c:/oracle/dbtest/redo/log1.log’) SIZE 100M,
GROUP 2(‘c:/oracle/dbtest/redo/log2.log’) SIZE 100M
DATAFILE ‘c:/oracle/dbtest/data/data.dbf’ SIZE 325M
MAXLOGFILES 3
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
######################################

12. Run the script in sql prompt
SQL>@ “path of the sql file”
once completed this script you can see the control files, redo log file, Alert log file and .dbf files
files are created in c:\oracle\dbtest\…….

13. Now you can shutdown the database
SQL>SHUT
and restart it
c:\>set oracle_sid=dbtest
If dbtest windows service is not yet started start it manually or execute this
c:\>oradim -startup -sid dbtest -intpwd dbpassword
(Note: Here we don’t need to start the database in nomount mode because we have already created control files)
SQL>conn sys as sysdba
password: sys(sys password)
( or you can simply change the mount state rather restarting the database
SQL>alter database open)
14. Execute catalog.sql
SQL>@c:\ora91\rdbms\admin\catalog.sql
15. Execute catproc.sql
SQL>@d:\ora91\rdbms\admin\catproc.sql

Note: if the password file is corrupted you can recreate
the password file, make sure to delete the existing password file.
c:\>orapwd file=d:\ora91\database\PWDdbtest.ora password=dbpassw