Kamran Agayev’s Oracle Blog

Kamran Agayev’s Oracle Blog

Create Database Manually – Step by Step instruction

Posted by Kamran Agayev A. on May 31, 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

Advertisements

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

  1. 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. 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 said

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

    o0o0oo0, ok ok, i’ll try it

    thanks 🙂

  6. aditt said

    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 said

    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. 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. […] Before starting this chapter, I’d like to direct you to my step by step guide on “Create database manually“ […]

  10. Yvon Julien said

    Hi Kamran,

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

  11. Pablo said

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

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

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

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

  17. Kamran,

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

    Regards,
    Rodrigo Almeida

  18. Thanks Rodrigo for feedback

    Keep in touch!

  19. Mitch said

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

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

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

  24. I’ve already shown all steps Karthiga

  25. ravi said

    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 said

    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. Just try my steps Ravi 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: