steps for database creation manually in oracle

->in this example i assume database name is demo

-----------------------------------------------------

Step 1: Specify an Instance Identifier (SID)

Decide on a unique Oracle system identifier (SID) for your instance, open a command window, and set the ORACLE_SID environment variable.

$export ORACLE_SID=demo

Step 2: Ensure That the Required Environment Variables Are Set

For example, on most platforms, ORACLE_SID and ORACLE_HOME must be set. In addition, it is advisable to set the PATH variable to include theORACLE_HOME/bin directory

$export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

$export PATH=$PATH:$ORACLE_HOME/bin

Step 3: Choose a Database Administrator Authentication Method

·         With a password file

·         With operating system authentication

To be authenticated with a password file, create the password file

$orapwd FILE=orapworcl password=****** ENTRIES=30

 To be authenticated with operating system authentication, ensure that you log in to the host computer with a user account that is a member of the appropriate operating system user group. On the UNIX and Linux platforms, for example, this is typically the dba user group.

Step 4: Create the Initialization Parameter File

If you create the initialization parameter file manually, ensure that it contains at least the parameters listed in . All other parameters not listed have default values.

Parameter Name	Mandatory	Notes
DB_NAME	Yes	Database identifier. Must correspond to the value used in the CREATE DATABASE statement. Maximum 8 characters.
CONTROL_FILES	No	Strongly recommended. If not provided, then the database instance creates one control file in the same location as the initialization parameter file. Providing this parameter enables you to multiplex control files. Ex: CONTROL_FILES = (/u01/oracle/demo/control01.ctl,                  /u02/oracle/demo/control02.ctl,                  /u03/oracle/demo/control03.ctl)
MEMORY_TARGET	No	Sets the total amount of memory used by the instance and enables automatic memory management. You can choose other initialization parameters instead of this one for more manual control of memory usage. See "Configuring Memory Manually".

Example:

*.audit_file_dest='/u01/app/oracle/admin/demo/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/demo/control01.ctl','/u01/app/oracle/flash_recovery_area/demo/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='demo'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=856686592

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.resource_limit=TRUE

*.undo_tablespace='UNDOTBS1'

Step 5: (Windows Only) Create an Instance

On the Windows platform, before you can connect to an instance, you must manually create it if it does not already exist. The ORADIM command creates an Oracle instance by creating a new Windows service.

>oradim -NEW -SID sid -STARTMODE MANUAL -PFILE pfile

Step 6: Connect to the Instance

To authenticate with a password file, enter the following commands, and then enter the SYS password when prompted:

$ sqlplus /nolog

SQL> CONNECT SYS AS SYSDBA

To authenticate with operating system authentication, enter the following commands:

$ sqlplus /nolog

SQL> CONNECT / AS SYSDBA

Step 7: Create a Server Parameter File

SQL>CREATE SPFILE FROM PFILE;

Step 8: Start the Instance

SQL> STARTUP NOMOUNT

Step 9: Issue the CREATE DATABASE Statement

CREATE DATABASE demo

   USER SYS IDENTIFIED BY oracle

   USER SYSTEM IDENTIFIED BY oracle

   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/demo/redo1a.log') SIZE 64M REUSE,

           GROUP 2 ('/u01/app/oracle/oradata/demo/redo2a.log') SIZE 64M REUSE

   MAXLOGFILES 32

   MAXLOGMEMBERS 3

   MAXLOGHISTORY 1

   MAXDATAFILES 200

   CHARACTER SET AL32UTF8

   NATIONAL CHARACTER SET AL16UTF16

   EXTENT MANAGEMENT LOCAL

   DATAFILE '/u01/app/oracle/oradata/demo/system01.dbf' SIZE 325M REUSE

   SYSAUX

   DATAFILE '/u01/app/oracle/oradata/demo/sysaux01.dbf' SIZE 128M REUSE

   DEFAULT TABLESPACE users

   DATAFILE '/u01/app/oracle/oradata/demo/users01.dbf'  SIZE 32M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

   DEFAULT TEMPORARY TABLESPACE tempts1

   TEMPFILE '/u01/app/oracle/oradata/demo/temp01.dbf' SIZE 32M REUSE

   UNDO TABLESPACE undotbs1

   DATAFILE '/u01/app/oracle/oradata/demo/undotbs01.dbf' SIZE 128M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Step 11: Run Scripts to Build Data Dictionary Views

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/sqlplus/admin/pupbld.sql

Script	Description
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	Required for SQL*Plus. Enables SQL*Plus to disable commands by user.