Kamran Agayev's Oracle Blog

Oracle Certified Master

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

 manual_1

 

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

manual_2

 

 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

 manual_3 

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

 

manual_4 

 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!

 manual_5

66 Responses to “Create Database Manually – Step by Step instruction”

  1. Shyam Singh Says:

    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.

  2. Kamran Agayev A. Says:

    Yes. I assume that this code should create a new database in mac also
    Just try it. And let me know the result

  3. aditt Says:

    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

  4. Kamran Agayev A. Says:

    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’;

  5. aditt Says:

    o0o0oo0, ok ok, i’ll try it

    thanks :)

  6. aditt Says:

    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?

  7. Aijaz Khan Says:

    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.

  8. Kamran Agayev A. Says:

    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

  9. Chapter 2 – Creating an Oracle Database « Kamran Agayev’s Oracle Blog Says:

    […] Before starting this chapter, I’d like to direct you to my step by step guide on “Create database manually“ […]

  10. Yvon Julien Says:

    Hi Kamran,

    Have you ever done a db setup for Siebel? If so can you please send the step by step? Thanx

  11. Pablo Says:

    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….

  12. Kamran Agayev A. Says:

    Dear Pablo

    You can use the following link to recreate OEM
    http://www.idevelopment.info/data/Oracle/DBA_tips/Enterprise_Manager/OEM_3.shtml

  13. Rudra Says:

    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

  14. Kamran Agayev A. Says:

    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/

  15. Roberto Says:

    I think the default character set chosen (u7ascii) is not the best.

  16. Kamran Agayev A. Says:

    Sure Roberto. It’s just a little test on creating database manually. Default character set should be – WE8ISO8859P1

  17. Rodrigo Almeida Says:

    Kamran,

    Congratulations for your blog and tutorials! It’s very important for us.

    Regards,
    Rodrigo Almeida

  18. Kamran Agayev A. Says:

    Thanks Rodrigo for feedback

    Keep in touch!

  19. Mitch Says:

    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

  20. Kamran Agayev A. Says:

    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

  21. Mitch Says:

    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

  22. Kamran Agayev A. Says:

    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

  23. karthiga Says:

    how to run the sql commands please step by step details?

  24. Kamran Agayev A. Says:

    I’ve already shown all steps Karthiga

  25. ravi Says:

    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

  26. ravi Says:

    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

  27. Kamran Agayev A. Says:

    Just try my steps Ravi :)

  28. abhi Says:

    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……………..

  29. abhi Says:

    DEAR SIR
    SORRY TO DISTURB YOU..

    I HAVE MODIFIED THE VALUE OF SGA_TARGET =1677721600 TO SGA_TARGET=167772160

    YUPPEEEEEEE DONE

    LOT OF THNX…

  30. didik prasetyo Says:

    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

  31. Kamran Agayev A. Says:

    You got “created file already exists”
    Go to that folder, delete already existed controlfiles and create database again

  32. didik prasetyo Says:

    how to create control01.ctl

    Thanks a lot ^_^

  33. didik prasetyo Says:

    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

  34. Kamran Agayev A. Says:

    You need to create parameter file from spfile, delete deprecated parameters and recreate spfile again

  35. Monalisa Says:

    Excellent Work..

    Thanks a lot :)

  36. Raja Says:

    Hi Kamran,

    Can I run this script on Windows?

  37. Akbar Jalaluddin. Says:

    Salam Brother.

    This is really helped me alot. I really appreciate your help.

    Thanks,

    Akbar

  38. Kamran Agayev A. Says:

    Sure, Just change the file names

  39. praveen alluri Says:

    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

  40. Kamran Agayev A. Says:

    Check the following link:
    http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch9.htm#sthref1610

  41. praveen alluri Says:

    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.

  42. Kamran Agayev A. Says:

    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

  43. Praveen Alluri Says:

    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.

  44. Kamran Agayev A. Says:

    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)

  45. praveen alluri Says:

    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

  46. Kamran Agayev A. Says:

    tnsping the TNS entry and see if you get the same error again. If so, change the tns entry and specify the correct one

  47. Ivan Says:

    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…

  48. Kamran Agayev A. Says:

    Hi Ivan
    There’s no relation between sending an email and using sql (or Oracle)

  49. Aaron Says:

    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??

  50. Kamran Agayev A. Says:

    This is only a general view for the newbies to see how it’s possible to create a database manually

  51. kalyani Says:

    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

  52. Kamran Agayev A. Says:

    -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$

  53. Amar Says:

    Hi Kamran,

    Thanks for the post, it’s very helpful

  54. Sridhar Tempalle Says:

    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?

  55. haseena Says:

    Hi sir.what is the meaning of replication?

  56. Kamran Agayev A. Says:

    Check the following link:
    http://bit.ly/SPbhTR

  57. Avinash Says:

    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

  58. Kamran Agayev A. Says:

    Please post the alert.log file output

  59. Ganesh Says:

    How to create the service, ur not create the service

  60. Kamran Agayev A. Says:

    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

  61. Syed Says:

    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

  62. Kamran Agayev A. Says:

    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

  63. Muhammad Ayub Says:

    hi Sir,
    help me how to look the encrypted password oracle10g

  64. Kamran Agayev A. Says:

    Hi Muhammad
    Sorry, but there’s no way for this

  65. Roche Says:

    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

  66. Kamran Agayev A. Says:

    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

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>