Create Database in Oracle 11g

After you have install the binaries using Oracle Universal Installer you need to create the database. And you can do that in two ways:
Using the Database Configuration Assistant (DBCA)
With the SQL “create database” statement
Using “DBCA” :

The Database Configuration Assistant (DBCA) an Oracle supplied tool that enables you to create an Oracle database, configure database options for an existing Oracle database, delete an Oracle database, or manage database templates.
command prompt ( start->run->cmd->dbca).

Using SQL “create database” statement:

Step 1: Decide on Your Instance Identifier (SID)
There can be more than one Oracle instance on a single machine. In order to be able to distinguish these instances, Oracle uses a SID (System Identifier) which is a string.
The SID can be set through the ORACLE_SID environment
Example:

>set ORACLE_SID = TEST

Step 2: Establish the Database Administrator Authentication Method

Step 3: Create the initialization parameter file
When an Oracle instance starts up, it requires either an initialization paramter file (init.ora) or an SPFILE. SPFILES have binary content and must be created from init.ora files. Therefore, the init.ora file (which is an ordinary text file) is created first.
Create the init.ora file and put it in below path.
$ORACLE_HOME/dbs
 
Step 4: Connect to the Instance
$ SQLPLUS /nolog
SQL>CONNECT SYS/password AS SYSDBA
Connected to an idle instance.

SQL*Plus tells us that we're connected to an idle instance. That means that it is not yet started. So, let's start the instance. We have to start the instance without mounting (nomount) as there is no database we could mount at the moment.

Step 5: Start the Instance.
SQL> STARTUP NOMOUNT

This created the SGA (System Global Area) and the background processes

Step 6: Issue the CREATE DATABASE Statement
create database test
datafile '/disk1/oradata/test/system.dbf' size 170m
sysaux datafile '/disk1/oradata/test/sysaux.dbf' size 60m
undo tablespace undotbs
datafile '/disk1/oradata/test/undotbs.dbf' size 30m
default tablespace userdata
datafile '/disk1/oradata/test/userdata.dbf' size 40m
default temporary tablespace temp
tempfile '/disk1/oradata/test/temp.dbf' size 30m
logfile
group 1 ('/disk1/oradata/test/redo1.log') size 4m,
group 2 ('/disk1/oradata/test/redo2.log') size 4m
controlfile reuse;

If something goes wrong with the creation, Oracle will write an error into the alert.log. The alert log is normaly found in the directory that is specified with the background_dump_dest. If this parameter was not specified (as is the case in our minimal init.ora), the alert.log will be written into %ORACLE_HOME%/RDMBS/trace.
If an ORA-01031: insufficient privileges is returned, that means most likely, that the current user is not in the dba group (on unix), or the ORA_DBA (windows).
If the init.ora file is not at its default location or has not been found with the pfile attribute, an ORA-01078: failure in processing system parameters and an LRM-00109: could not open parameter file '/appl/oracle/product/9.2.0.2/dbs/initadpdb.ora' error is issued.
The create database command also executes a file whose name is determined by the (hidden) init parameter _init_sql_file (which seems to default to sql.bsq)
After the creation of the database, it can be mounted and opened for use.

Step 7: Create Additional Tablespaces

CONNECT SYS/password AS SYSDBA
-- create a user tablespace to be assigned as the default tablespace for users
CREATE TABLESPACE users LOGGING
DATAFILE '/disk1/oradata/test/users01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING
DATAFILE '/disk1/oradata/test/indx01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
EXIT

Step 8: Run Scripts to Build Data Dictionary Views
CONNECT SYS/password AS SYSDBA
(In order to complete the db creation, the following scripts must be run as sys )

@/oracle/rdbms/admin/catalog.sql
@/oracle/rdbms/admin/catproc.sql
@/oracle/sqlplus/admin/pupbld.sql
EXIT

CATALOG.SQL
Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL
Runs all scripts required for or used with PL/SQL.
PUPBLD.SQL
The user system might also want to run /sqlplus/admin/pupbld.sql.
pupbld.sql creates a table that allows to block someone from using sql plus.