Creating an ORACLE 11g Database with the CREATE DATABASE Statement

Creation of database

There are 3 types to create the database:

 1.Creating database Manually.
 2.Creating database using OSMPF.
 3.Creating database using DBCA command

Creating a Database with the CREATE DATABASE Statement:

Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts.
If you have existing scripts for creating your database, consider editing those scripts to take advantage of new Oracle Database features.
The instructions in this section apply to single-instance installations only. Refer to the Oracle Real Application Clusters (Oracle RAC) installation guide for your platform for instructions for creating an Oracle RAC database.

Note:

Single-instance does not mean that only one Oracle instance can reside on a single host computer. In fact, multiple Oracle instances (and their associated databases) can run on a single host computer. A single-instance database is a database that is accessed by only one Oracle instance, as opposed to an Oracle RAC database, which is accessed concurrently by multiple Oracle instances on multiple nodes. See Oracle Real Application Clusters Administration and Deployment Guide for more information on Oracle RAC.

 Creation of database:

Steps to create the database manually

STEP1 : 

Edit the .bash_profile
 
    $vi .bash_profile

       ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME
       ORACLE_BASE=/maggy/app/oracle; export ORACLE_BASE
       ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
       ORACLE_SID=test; export ORACLE_SID
       ORACLE_TERM=xterm; export ORACLE_TERM
       PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH; export PATH


    :wq  (save and quit)
 
   check the oracle_home,oracle_sid,oracle_base
   $echo $ORACLE_HOME
   $echo $ORACLE_SID
   $echo $ORACLE_BASE

STEP 2: 

 cp init.ora init$ORACLE_SID.ora

STEP 3:

Edit the init$ORACLE_SID.ora file
 
    $cd $ORACLE_HOME/dbs
 dbs$vi init$ORACLE_SID.ora
     db_name='client'
     diagnostic_dest=/disk1/oradata/client
     undo_tablespace='UNDOTBS'
     undo_management=auto
     control_files =/disk1/oradata/client/cont.ctl

    :wq   (Save and Quit)

STEP 4:

  login to sql ideal instance
   $sqlplus / as sysdba

   sys>>startup nomount
   

STEP 5:

 Create the database script

   $vi dbcreate.sql

   create database "client"
   datafile '/disk1/oradata/client/system.dbf' size 100m autoextend on
   sysaux datafile '/disk1/oradata/client/sysaux.dbf' size 100m autoextend on
   undo tablespace undotbs datafile '/disk1/oradata/client/undotbs.dbf' size 100m autoextend on
   default tablespace userdata datafile '/disk1/oradata/client/userdata.dbf' size 100m autoextend on
   default temporary tablespace temp '/disk1/oradata/client/temp.dbf' size 100m autoextend on
   logfile
   group 1('/disk1/oradata/client/redo1.log' size 5m),
   group 2('/disk2/oradata/client/redo2.log' size 5m)
   controlfile reuse;

   :wq     (save and quit)


STEP 6: 

Create data dictionary views

   $vi postdb.sql

   @$ORACLE_HOME/rdms/admin/catalog.sql
   @$ORACLE_HOME/rdms/admin/catproc.sql
   connect system/manager
   @$ORACLE_HOME/sqlplus/admin/pupbld.sql

   :wq     (save and quit)


STEP 7:

sqlplus / as sysdba
 
  sys>>startup nomount
  sys>>@dbcreate.sql
 
  check the status of the database

  To check the status of the database

  sys>>select status from v$instance;
       status--open

  To check the database name
  sys>>select name from v$database;
  
  To check the patch of the datafile

  sys>>select name from v$datafile;
 
  To check the path of the redolog file

  sys>>select member from v$logfile;


  sys>>@postdb.sql

  sys>> show parameter user_dump_dest

  sys>>show parameter background_dump_dest



  for more information check the data dictionary views

  v$SGA
  v$INSTANCE
  v$DATABASE
  v$PROCESS
  v$SYSAUX_OCCUPANTS