Create Database Manually – Step by Step instruction
Posted by Kamran Agayev A. on May 31st, 2009
Today, I’ll show you how we can create a Database without Database Configuration Assistant (DBCA). We’re going to create the database manually, using CREATE DATABASE statement. Sometimes, we are not able to use GUI in order to create a database. For this, we should know syntax of database creation from SQL*Plus. To create database manually, follow below steps:
1. Firstly, export Environment Variables. To export EV automatically for every session, do below changes to /home/oracle/.bashrc file:
export ORACLE_SID=kamran
export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
2. Create parameter file and modify it by setting minimum required parameters:
*.db_name=kamran
*.db_block_size=8192
*.sga_target=1677721600
*.undo_management=’AUTO’
*.control_files = (‘/home/oracle/oracle/product/10.2.0/control01.ctl’)
*.user_dump_dest=’/home/oracle/oracle/product/10.2.0/udump’
*.background_dump_dest=’/home/oracle/oracle/product/10.2.0/bdump’
*.core_dump_dest=’/home/oracle/oracle/product/10.2.0/cdump’
After creation of this parameter file, create below folders in /home/oracle/oracle/product/10.2.0/ directory. Three of them are dump folders (needed for trace files and alert.log file). We’re going to keep Control Files and DataFiles in oradata folder.
– oradata
– udump
– bdump
– cdump
3. Create Server parameter file (SPFILE) using this parameter file and STARTUP the instance in NOMOUNT mode.
CREATE SPFILE FROM PFILE=’/home/oracle/oracle/product/10.2.0/init.ora’;
STARTUP NOMOUNT
Now our instance started, SGA allocated and background processes started
4. To create a new database, use the CREATE DATABASE statement. As a result, below files will be created:
– Redo Log files
– system.dbf and sysaux.dbf (files for SYSTEM tablespace)
– undo01.dbf file (for UNDO tablespace)
– temp_tbs.dbf file (for TEMPORARY tablespace)
– users.dbf (for DEFAULT PERMANENT tablespace)
//######## Database Creation Code #######
CREATE DATABASE kamran
USER SYS IDENTIFIED BY kamran
USER SYSTEM IDENTIFIED BY kamran
LOGFILE GROUP 1 (‘/home/oracle/oracle/product/10.2.0/oradata/redo01.log’) SIZE 50 m,
GROUP 2 (‘/home/oracle/oracle/product/10.2.0/oradata/redo02.log’) SIZE 50 m,
GROUP 3 (‘/home/oracle/oracle/product/10.2.0/oradata/redo03.log’) SIZE 50 m
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET us7ascii
NATIONAL CHARACTER SET al16utf16
DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/system01.dbf’ SIZE 325 m REUSE EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/sysaux.dbf’ SIZE 400 m REUSE
DEFAULT TABLESPACE tbs_1 DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/users.dbf’ SIZE 200m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE ‘/home/oracle/oracle/product/10.2.0/oradata/temp_tbs.dbf’ SIZE 20m REUSE
undo TABLESPACE undotbs DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/undo01.dbf’ SIZE 200m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
5. Run the scripts necessary to build views, synonyms, and PL/SQL packages
CONNECT / AS SYSDBA
SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
6. Shutdown the instance and startup the database. Your database is ready for use!
June 10th, 2009 at 5:26 pm
Hi Kamran,
Can you tell me your database creation article will help us to create database in MAc os also.
Now I want to try this manual on mac also.
Since, I am not a dba but was developer but now a days in new company such assignments are given to me and I play with it.
June 10th, 2009 at 5:29 pm
Yes. I assume that this code should create a new database in mac also
Just try it. And let me know the result
July 13th, 2009 at 8:04 pm
hi Sir,
its me again hehe
for point no 2, can you tell me where to create the parameter file, or how, coz i used to use DBCA to create database, so i’m not really understand how to create it without DBCA.
thanks
July 14th, 2009 at 8:46 am
Hi Aditt
Create parameter file wherever you want. Let’s assume that you’ve created that file in /tmp directory. Then, while starting the instance you should run :
SQL>startup nomount pfile=’/tmp/pfile.ora’;
July 14th, 2009 at 1:14 pm
o0o0oo0, ok ok, i’ll try it
thanks
July 16th, 2009 at 11:31 pm
i tried it, and when executing SQL>starup nomount pfile=’/home/oracle/init.ora’; i got this error :
LRM-0123: invalid character 128 found in the input file
ORA-01078: failure in processing system parameters
i dont know, maybe there’s something i’m missing?
October 25th, 2009 at 10:22 am
Thanks alot for showing how to create a paramater file
Can you tell me how i can get the default init.ora file if in case it is deleted from my root.
i dont have any copy of parameter file i want the default parameter file with all its parameters.
Thanks in advance.
October 30th, 2009 at 10:31 am
Hi Aijaz
You can create default parameter file as I’ve shown in this article. Just create a text file, add the parameters to that file and start the database
November 17th, 2009 at 6:23 pm
[…] Before starting this chapter, I’d like to direct you to my step by step guide on “Create database manually“ […]
June 11th, 2010 at 10:55 pm
Hi Kamran,
Have you ever done a db setup for Siebel? If so can you please send the step by step? Thanx
July 19th, 2010 at 9:01 pm
Hello, after the creation of the database, how do you configurate OEM DB Console? because you create datafile SYSAUX where allocate objects of sysman and dbsnmp….
July 25th, 2010 at 2:26 pm
Dear Pablo
You can use the following link to recreate OEM
http://www.idevelopment.info/data/Oracle/DBA_tips/Enterprise_Manager/OEM_3.shtml
August 8th, 2010 at 6:49 pm
AFTER CREATING THE DATABASE USING THIS COMMAND IN XP , I CAN’T CONFIGURE FOR ORACLE ENTERPRISE MANAGER. BUT WHEN I DID IT WITH DBCA , OEM IS CREATED AUTOMATICALLY. WOULD U PLEASE GIVE ME THE SOLUTION, ======DEAR EXPERT
August 9th, 2010 at 9:22 am
Dear Rudra
You can refer to the following link to create OEM
http://blog.mclaughlinsoftware.com/oracle-architecture-configuration/changing-windows-hostname-and-oracle-enterprise-manager/
October 1st, 2010 at 8:27 pm
I think the default character set chosen (u7ascii) is not the best.
October 5th, 2010 at 11:30 am
Sure Roberto. It’s just a little test on creating database manually. Default character set should be – WE8ISO8859P1
October 23rd, 2010 at 4:35 am
Kamran,
Congratulations for your blog and tutorials! It’s very important for us.
Regards,
Rodrigo Almeida
October 25th, 2010 at 2:01 pm
Thanks Rodrigo for feedback
Keep in touch!
November 4th, 2010 at 11:24 am
Hello Kamram,
Thanks to your post it is very helpful for a newbie like me. Quick question on the control files you used, did you manually create the ctl file or had copied an existing ctl file from a different database?
Regards,
Mitch
November 4th, 2010 at 3:20 pm
Hi Mitch
I’m sorry I’ve mixed your question with another one, so the answer was different
Actually, control files were created by CREATE DATABASE command. If you’re creating a database using CREATE DATABASE command, you have to identify controlfiles and Oracle will create it by itself. However, you can create controlfile using CREATE CONTROLFILE command as well
Good Luck
November 5th, 2010 at 7:12 am
Hello Kamran,
Thank you for the response. I have tried to create a database manually, you are right it automatically creates a ctl file inside my flash recovery directory.
But what if I will copy an existing ctl file from a different db then specify it in the init.ora file to create a new db but now with a controlfile reuse clause, will this erase the pervious content of the ctl file? Can I still call this a manual db creation or did I do a clone?
Regards,
Mitch
November 5th, 2010 at 9:37 am
Dear Mitch
You can’t use the controlfile of the different database. Control files must be unique because it keeps the database name, archived redo log sequence, checkpoint values inside. So using different controlfile will through an error
November 24th, 2010 at 4:04 pm
how to run the sql commands please step by step details?
November 24th, 2010 at 6:57 pm
I’ve already shown all steps Karthiga
May 21st, 2011 at 5:04 pm
i ned some help how to create spfile and make modfication to create database.i install oracle 10g in linux and
there is bydefault 1 database how to create another database i want to create db02 database.
1 dtabase that is created by dbca while installtion is in /u01/app/oracle directtory please step by step tell me
May 21st, 2011 at 5:39 pm
suppose i have already one database wth name dba01 it is system generated so before going to create the manually database creation what i have to do…… can i create in this folder or what exactly we need to do .
please reply and tell me.
ravikumar
May 27th, 2011 at 12:20 am
Just try my steps Ravi
October 25th, 2011 at 2:00 pm
dear sir
at step 3 for the command
STARTUP NOMOUNT
gives error as
ORA-27102:NOT ENOUGH MEMORY..
I HAVE ORACLE 10G ON SOLARIS IN VMPLAYER..
NOW WAT TO DO SIR……………..
October 25th, 2011 at 2:09 pm
DEAR SIR
SORRY TO DISTURB YOU..
I HAVE MODIFIED THE VALUE OF SGA_TARGET =1677721600 TO SGA_TARGET=167772160
YUPPEEEEEEE DONE
LOT OF THNX…
November 1st, 2011 at 8:33 am
im tried by i get error with error
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: ‘/opt/oracle/product/11.2.0/oradata/control01.ctl’
ORA-27038: created file already exists
how to solution
Thanks a lot
November 1st, 2011 at 9:20 am
You got “created file already exists”
Go to that folder, delete already existed controlfiles and create database again
November 2nd, 2011 at 8:45 am
how to create control01.ctl
Thanks a lot ^_^
November 21st, 2011 at 6:43 am
after i create spfile and i startup nomount i have problem
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1670221824 bytes
Fixed Size 2213936 bytes
Variable Size 419432400 bytes
Database Buffers 1241513984 bytes
Redo Buffers 7061504 bytes
how solution for this problem
thanks a lot
November 21st, 2011 at 3:27 pm
You need to create parameter file from spfile, delete deprecated parameters and recreate spfile again
January 29th, 2012 at 2:27 pm
Excellent Work..
Thanks a lot
February 16th, 2012 at 10:40 pm
Hi Kamran,
Can I run this script on Windows?
February 16th, 2012 at 10:58 pm
Salam Brother.
This is really helped me alot. I really appreciate your help.
Thanks,
Akbar
February 17th, 2012 at 5:30 am
Sure, Just change the file names
February 22nd, 2012 at 7:14 pm
Hi Kamran,
Good post.. can u pls tell whether it is necessary to execute pupbld.sql at the end after executing catlog.sql and catproc.sql. i ahve tried executing and getting erors. Pls advise me on this. Thanks
February 23rd, 2012 at 6:05 am
Check the following link:
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch9.htm#sthref1610
February 22nd, 2012 at 7:27 pm
Hi Kamran,
i am getting the following error if i execute pupbld.sql. pls help me on this.
SYSTEM@sales 22-FEB-12> @pupbld.sql
Synonym dropped.
CREATE TABLE SQLPLUS_PRODUCT_PROFILE AS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
DROP TABLE PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-01430: column being added already exists in table
CREATE TABLE SQLPLUS_PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
DROP TABLE PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
View dropped.
View created.
Grant succeeded.
Synonym dropped.
Synonym created.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
Synonym created.
Synonym dropped.
Synonym created.
February 23rd, 2012 at 5:57 am
So what’s the question? You’ve already run it once, and thus getting above errors. At the end of the script it seems you got all objects created
February 23rd, 2012 at 6:12 am
Hi Kamran,
Yes objects are created.. But why is it showing some errors before. Could u pls enlighten me what are those errors related to.
February 23rd, 2012 at 9:43 am
You were getting those errors because you’ve already installed it and while creating new objects which are already installed, you got those errors (warnings)
February 23rd, 2012 at 6:55 pm
Hello kamaran,
Thanks for your swift response.. I am trying to configure RMAN in catalog mode and i am encountering the following error.
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=2074197942)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database: ORA-12162: TNS:net service name is incorrectly specified
I have changed the tns entries but still it is showing the same error. could you please assist me on this.
Thanks,
Praveen
February 24th, 2012 at 6:53 am
tnsping the TNS entry and see if you get the same error again. If so, change the tns entry and specify the correct one
March 10th, 2012 at 1:11 am
HI… my name is Ivan.. i would like you to help me with something… i am new in www design but i want to make something…
i already made a web page with test, and when people finish the test they click on send and i get their test on my mail.
question:
Now i would like to make the test when people click on send, they get the evaluation of the test on their mail, and i still get their test on mine. Please help me, i think now i need a sql base. Until now, all i did is form on my web site and php code for it…
March 21st, 2012 at 6:51 pm
Hi Ivan
There’s no relation between sending an email and using sql (or Oracle)
June 27th, 2012 at 3:15 pm
this is good stuff, but i think its not recommended by Oracle in production environment, widely used in 9i. can get the EM in 10g / 11g after using this procedure? do you get functionality of sysaux tbs??
June 28th, 2012 at 4:08 am
This is only a general view for the newbies to see how it’s possible to create a database manually
January 29th, 2013 at 4:52 am
Hi Kamran please help me below error.
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file:
‘/zfssa/ex01db/backup1/OEM/oradata/control/control01.ctl’
ORA-27054: NFS file system where the file is created or resides is not mounted
with correct options
Additional information: 3
January 29th, 2013 at 3:37 pm
-bash-3.2$ oerr ORA 27054
27054, 00000, “NFS file system where the file is created or resides is not mounted with correct options”
// *Cause: The file was on an NFS partition and either reading the mount tab
// file failed or the partition wass not mounted with the correct
// mount option.
// *Action: Make sure mount tab file has read access for Oracle user and
// the NFS partition where the file resides is mounted correctly.
// For the list of mount options to use refer to your platform
// specific documentation.
-bash-3.2$
August 5th, 2013 at 10:47 am
Hi Kamran,
Thanks for the post, it’s very helpful
October 10th, 2013 at 12:49 pm
Hi I have a db in /u01 and creating recovery catalog in /u02. I follwed ur steps for recovery catalog database in /u02 and db creation succeeded. Now when i echo the SID or ORACLE_HOME, it reflects the values of the db in /u01 which is shutdown. Moreover rdbms folder has not been created alongside the dbs folder in /u02.
1. How should i control these vales while installing on the same system though its not recommended?
2. What should i do now? Since I dont have rdbms folder to run additional scripts, should i copy those from db in /u01. Would that be ok here since m using it as recovery catalog for db in /u01?
January 11th, 2014 at 11:17 am
Hi sir.what is the meaning of replication?
June 17th, 2014 at 4:03 am
Check the following link:
http://bit.ly/SPbhTR
February 7th, 2014 at 5:20 am
I have created database successfully but when I startup database then its Mounted but not Open.
the error is
Ora-1092 Oracle instance terminated. Disconnection forced
June 13th, 2014 at 4:30 am
Please post the alert.log file output
February 10th, 2014 at 5:43 am
How to create the service, ur not create the service
June 17th, 2014 at 3:55 am
Hi Ganesh
You should create a service if you create a database on Windows OS.
Check this link please:
http://docs.oracle.com/cd/E16655_01/win.121/e10714/create.htm
April 30th, 2014 at 5:50 pm
Kamran, I am wondering if you can help me out. I tried to follow all the steps mentioned above on how to create a DB manually. This is where I am getting stuck
sqlplus /nolog
SQL>
but when I do sqlplus / as sysdba. it prompt me for the username and pwd. When I put in the username and pwd I get this error:
ORA-12560: TNS:protocol adapter error
June 13th, 2014 at 4:03 am
Hi Syed
In which stage you stuck? Have you set environment variable correctly? Are you running this command from Windows? If so, check the following post:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:431775600346873836
June 3rd, 2014 at 11:55 am
hi Sir,
help me how to look the encrypted password oracle10g
June 11th, 2014 at 8:27 am
Hi Muhammad
Sorry, but there’s no way for this
June 8th, 2014 at 4:15 pm
Hi Kamran,
In your CREATE DATABASE command you mentioned the below statements:
USER SYS IDENTIFIED BY kamran
USER SYSTEM IDENTIFIED BY kamran
If if ignore this statements, Oracle is creating the database.Is the Oracle will create this 2 users in the database ?
If Oracle creates the SYS and SYSTEM users, what is the password for the users.
Please Help.
Thank you
June 11th, 2014 at 8:21 am
Hi Roche
Check the documentation:
If you do not specify these clauses, then Oracle Database creates default passwords change_on_install for user SYS and manager for user SYSTEM. You can subsequently change these passwords using the ALTER USER statement. You can also use ALTER USER to add password management attributes after database creation.
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5004.htm#SQLRF53849