Oracle: Create database manually (in windows/linux)

Let us check a demonstration of HOW TO CREATE AN ORACLE DATABASE MANUALLY:

Note: Demonstration is on oracle 10G version. but the same will do for other versions also...almost....

step 1: let us create the location for our database:
My database name will be 'DEMO' and the location is 'd:\demo'
under d:\demo folder, i will create folder named......

d:\demo\data (will store all of my datafiles)
d:\demo\pfile (will be my pfile location)
d:\demo\adump (will be my audit_dump_dest location)
d:\demo\bdump (will be my background_dump_dest location)
d:\demo\cdump (will be my core_dump_dest location)
d:\demo\udump (will be my user_dump_dest location)
if you want you can have another folder for flash recovery area (Oracle 10G onwards)

step 2: now , let us copy a sample pfile from the existing running database and place it into "d:\demo\pfile" folder.

CHECK THE SAMPLE PFILE

In the sample file do the required changes mentioned in "BLUE" color text and save the file.

Note: remember the value of undo_tablespace parameter in pfile,as we need to provide the same name for undo_tablespace when we will create the database.

step 3: now we need to create the password file. we will use the orapwd utility for this.
you will find this utility in $ORACLE_HOME\bin directory.
so, lets proceed...

c:\ershad>orapwd file=E:\oracle\product\10.2.0\db_1\database\pwddemo.ora password=tiger entries=5

here we have created a password file named pwddemo.ora under my $oracle_home. The password is tiger and maximum 5 user will be allowed to login as sysdba.

What is a password file
Create password file with ORAPWD utility

step 4: update your listener.ora and tnsnames.ora with only the BLUE colored text here:
(copy & paste)
==>in tnsnames.ora file:

# tnsnames.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

tiger =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.12.242)(PORT = 1521))
)
(CONNECT_DATA =
(server=dedicated)
(SERVICE_NAME = tiger)
)
)

demo=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = YOUR HOSTNAME/IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =demo)
)
)

==>in listener.ora file:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba2)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = e:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = tiger)
(ORACLE_HOME =e:\oracle\product\10.2.0\db_1)
(SID_NAME = tiger)
)
(SID_DESC =
(GLOBAL_DBNAME = demo)
(ORACLE_HOME =e:\oracle\product\10.2.0\db_1)
(SID_NAME = demo)
)
)


step 5: you need to create the service:

C:\ershad>oradim -new -sid demo -pfile d:\demo\pfile\initdemo.ora -startmode A

step 6: connect to the database:
C:\ershad>set oracle_sid=demo

C:\ershad>sqlplus / as sysdba

sql> select status from v$instance;

STATUS
------------
STARTED

after connecting to database, if it shows "connect to an idle instance" , then you need to manually start up the database using the following:
sql>startup nomount pfile=d:\demo\pfile\initdemo.ora

step 7: run the create database script :
just copy the following into a file and run the file in sql prompt as follows...............

content of create_database_script file:

create database demo
logfile group 1 ('d:\demo\data\redo1.log') size 100M,
group 2 ('d:\demo\data\redo2.log') size 100M,
group 3 ('d:\demo\data\redo3.log') size 100M
datafile 'd:\demo\data\system.dbf' size 100M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile 'd:\demo\data\sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited
undo tablespace undodemo datafile 'd:\demo\data\undotbs1.dbf' size 100M
default temporary tablespace temptiger tempfile 'd:\demo\data\temp01.dbf' size 100M;

so now,
sql>@d:\create_database_script
Database created.

Now, You need to run two scripts to create the dynamic views and enable pl/sql packages
put the database in oepn mode and run the following scripts:
location of scripts are '$ORACLE_HOME\RDBMS\ADMIN'


sql> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catalog.sql
sql> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql

Your manually created database is ready to use now.

HOW TO CREATE ORACLE DATABASE MANUALLY IN LINUX

perform all the steps previous except step 5. as in unix/linux ,no need to create the database service.

thanks,
have a nice day...........................