1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to create database manually in oracle 10g in windows?

Discussion in 'Server Administration and Options' started by kimipatel, Sep 18, 2012.

  1. kimipatel

    kimipatel Active Member

    Messages:
    53
    Likes Received:
    1
    Trophy Points:
    140
    Hi,

    We all know that database creation is so easy by installer(automatic). But how to create database by manually?
    To more understand we have to try this way also. Any one have know or idea or steps to create a database.
    By using command prompt or else. If any one have demo?

    Thanks.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is the .sql file used to create a database named SMEDLEY on a Windows server:

    Code (SQL):
    SET verify off
    ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
    ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
    host C:\oradb\product\11.2.0\dbhome_1\bin\orapwd.exe file=C:\oradb\product\11.2.0\dbhome_1\DATABASE\PWDsmedley.ora force=y
    @C:\oradb\admin\smedley\scripts\CreateDB.SQL
    @C:\oradb\admin\smedley\scripts\CreateDBFiles.SQL
    @C:\oradb\admin\smedley\scripts\CreateDBCatalog.SQL
    @C:\oradb\admin\smedley\scripts\JServer.SQL
    @C:\oradb\admin\smedley\scripts\xdb_protocol.SQL
    @C:\oradb\admin\smedley\scripts\ordinst.SQL
    @C:\oradb\admin\smedley\scripts\interMedia.SQL
    @C:\oradb\admin\smedley\scripts\apex.SQL
    @C:\oradb\admin\smedley\scripts\lockAccount.SQL
    @C:\oradb\admin\smedley\scripts\postDBCreation.SQL
     
    It's called by a .bat file:

    OLD_UMASK=`umask`
    umask 0027
    mkdir C:\oradb\admin\smedley\adump
    mkdir C:\oradb\admin\smedley\dpdump
    mkdir C:\oradb\admin\smedley\pfile
    mkdir C:\oradb\cfgtoollogs\dbca\smedley
    mkdir C:\oradb\flash_recovery_area
    mkdir C:\oradb\flash_recovery_area\smedley
    mkdir C:\oradb\oradata\smedley
    mkdir C:\oradb\product\11.2.0\dbhome_1\database
    umask ${OLD_UMASK}
    set ORACLE_SID=smedley
    set PATH=%ORACLE_HOME%\bin;%PATH%
    C:\oradb\product\11.2.0\dbhome_1\bin\oradim.exe -new -sid SMEDLEY -startmode manual -spfile
    C:\oradb\product\11.2.0\dbhome_1\bin\oradim.exe -edit -sid SMEDLEY -startmode auto -srvcstart system
    C:\oradb\product\11.2.0\dbhome_1\bin\sqlplus /nolog @C:\oradb\admin\smedley\scripts\smedley.sql

    Of course all of this could be had by simply checking the box in DBCA to create the scripts before creating the database. You can also simply create the scripts and not create a database through DBCA and run the generated .bat file (and associated scripts) at another time.
     
  3. kimipatel

    kimipatel Active Member

    Messages:
    53
    Likes Received:
    1
    Trophy Points:
    140
    Hi,

    Sorry but i do not understanding what you trying to tell me. This all are sql command? For creating manually database i should run this command? Where should i run this?
    Can you explain me step by step?

    Thanks.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, this is NOT 'SQL command'. Apparently you have no idea how to create a database outside of the DBCA. I will explain, minimally, the contents I posted earlier and will attempt to provide an actual SQL script to create a database.

    The .BAT file (the second listing) is run from the Windows command prompt; it starts the process of creating the database by creating ALL of the necessary directories the database will need for tracing, logging, etc. The .BAT file calls a MASTER SQL SCRIPT (the FIRST listing in my original post) which calls all OTHER SQL scripts to create the database and all necessary data dictionary components. ALL of these scripts will be generated by DBCA if you check the box to create scripts, found on the last window before DBCA starts creating the database. You can also inform DBCA to NOT create the database and only create the scripts (by unchecking the 'Create database' checkbox) so these can be run later.

    The actual CREATE DATABASE command would look like this:

    Code (SQL):
    SET VERIFY OFF
    CONNECT "SYS"/"&&sysPassword" AS SYSDBA
    SET echo ON
    spool C:\oradb\admin\smedley\scripts\CreateDB.log append
    startup nomount pfile="C:\oradb\admin\smedley\scripts\init.ora";
    CREATE DATABASE "smedley"
    MAXINSTANCES 8
    MAXLOGHISTORY 1
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    DATAFILE 'C:\oradb\oradata\smedley\system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL
    SYSAUX DATAFILE 'C:\oradb\oradata\smedley\sysaux01.dbf' SIZE 600M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
    SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\oradb\oradata\smedley\temp01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
    SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE 'C:\oradb\oradata\smedley\undotbs01.dbf' SIZE 800M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
    CHARACTER SET AL32UTF8
    NATIONAL CHARACTER SET AL16UTF16
    LOGFILE GROUP 1 ('C:\oradb\oradata\smedley\redo01.log') SIZE 51200K,
    GROUP 2 ('C:\oradb\oradata\smedley\redo02.log') SIZE 51200K,
    GROUP 3 ('C:\oradb\oradata\smedley\redo03.log') SIZE 51200K
    USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
    spool off
     
    This is not a job to be taken lightly nor one to be undertaken by someone who has no knowledge of Oracle -- I would strongly suggest that you

    1) Run DBCA to set various parameters you would want for your database
    2) Tell it to create scripts and the database
    3) After your database is created go and look at ALL of the scripts DBCA created and learn what they do, one by one

    There are too many things that can go wrong when creating a database 'manually' if you don't know what you are doing. I suggest, strongly, that you do NOT attempt this and let DBCA do the work as you clearly don't know enough about Oracle to be successful in creating a database from the command line. I do not say this to be mean, but to give you the truth.