Create Database in oracle

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 = OraProdEurope

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.
Unix Path is: $ORACLE_HOME/dbs
Windows path is: %ORACLE_HOME%\database

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 mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 ('/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
DATAFILE '/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

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 '/oracle/oradata/mynewdb/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 '/oracle/oradata/mynewdb/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
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.

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.