How to create database manually in oracle without dbca

We need these resources

1. A sample pfile
2. A database creation sql script

You can create pfile from spfile if you already have a database created, using

SQL> create pfile from spfile;

Now you can check that a pfile has been created in $ORACLE_HOME/dbs with the name format such as initSID.ora

Make a copy of this pfile using your SID. In my case it is newdb. So I have copied in that location using

cp -p initprod.ora initnewdb.ora (we are using prod's pfile)

Or else you can use the sample pfile of mine. You can download it from here http://www.mediafire.com/download/34j4ekqx8jcohdh/initprod.ora

Also you can download the database creation sql script from here http://www.mediafire.com/view/379b73qmvv735wc/db_creation_script.sql

If you download the pfile then don't forget to place it inside $ORACLE_HOME/dbs and change the SID in the name initSID.ora to what you want. In my case it is newdb. So the name will be initnewdb.ora

You can place the database creation script anywhere like I have placed it on /home/oracle/Desktop

I have used PuTTY to connect to my host to give you a real industry feeling, you can do the same or you can use the terminal on your host directly. As you wish.

Go to your $ORACLE_HOME/dbs location such

>cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs

>Open the pfile initnewdb.ora and edit it using 
vim initnewdb.ora

>Type 

 :%s/prod/newdb/g to replace the old with new.

>Then :wq 

>Now open it using 

cat initnewdb.ora

Now find the directories that you have to create 

> Create those directories such as

mkdir -p /u01/app/oracle/admin/newdb/adump

mkdir -p /u02/data/newdb

mkdir -p /u02/fast_recovery_area/newdb


Basically you need create the adump location, the controlfiles location and the fast recovery area location. So create these according to your present mount point structure.

Now time edit the sql script 

go to the location where you have placed the database creation sql script. 

cd /home/oracle/Desktop

vim db_creation_script.sql

Replace the database name and locations with yours. When you are done use :wq to write and quit the vim editor.

Then open the oratab file using

vim /etc/oratab

And make an entry for your SID with home such as 

newdb:/u01/app/oracle/product/11.2.0/dbhome_1:N and then :wq

Now set the SID using . oraenv

Then open sqlplus '/as sysdba'

SQL> startup nomount

Then run the db_creation_script using 

SQL> @/home/oracle/Desktop/db_creation_script.sql

If we haven't done any mistake, we should see 

"Database created." in a while

Now we have run these two scripts one after another 

SQL> @/$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> @/$ORACLE_HOME/rdbms/admin/catproc.sql


The 2nd script takes too long. After both these get completed use this to verify

SQL> select open_mode from v$database;


You will get READ WRITE as output.


You can find the video here for this 

 That's all

Thanks
Subhajit