CREATE ORACLE 11G R2 DATABASE MANUALLY USING COMMAND

ORACLE 11G R2 DATABASE MANUALLY USING COMMANDS:

Prerequisites for database creation:

Sufficient primary memory should be available to start the oracle instance.

Sufficient disk space must be available on the computer.

Steps for database creation:                 

1. Set environment variable by '.bash_profile' file.
2. Create parameter file(pfile).
3. Create directory structure.
4. Create database creation script and run at SQL prompt.

    Post database creation steps:

• Create and run 'postdb.sql' at SQL prompt

STEP 1:   Set environment variable by '.bash_profile'

[oracle@bhanu  ~] $ vi .bash_profile

#

#

PATH=$PATH:$HOME/bin

export  ORACLE_SID=bhanu

export  ORACLE_HOME=/oraeng/app/oracle/product/11.2.0/db_1

export   PATH=$ORACLE_HOME/bin:$PATH:.

export PATH

~

~

~

:wq

NOTE:  To edit in vi editor click "i". After editing to save the file click "esc" then ":wq".

Here "w" is to save the file and "q" is to quit the file. Both combine "wq" represents save and quit.

[oracle@bhanu  ~] $ .  .bash_profile

NOTE: To execute the file we use "dot space <filename>". We run this file to set all the environment variables for that user.

To check whether the environment variables are exported or not use the below commands

[oracle@bhanu  ~] $ echo $ORACLE_HOME

/oraeng/app/oracle/product/11.2.0/db_1

[oracle@bhanu  ~] $ echo $ORACLE_SID

bhanu

STEP 2:  create parameter file (pfile)

[oracle@bhanu  ~] $ cd $ORACLE_HOME/dbs

[oracle@bhanu  dbs] $ pwd  

/oraeng/app/oracle/product/11.2.0/db_1/dbs

[oracle@bhanu  dbs] $ ls

init.ora

[oracle@bhanu  dbs] $ cp init.ora init$ORACLE_SID.ora

[oracle@bhanu  dbs] $ vi init$ORACLE_SID.ora

#

#

#

db_name=bhanudb

control_files=/disk1/oradata/bhanu/control.ctl

diagnostic_dest=/disk1/oradata/bhanu

undo_management=auto

undo_tablespace=undotbs

compatible=11.1.0

~

~

:wq

STEP 3: Create directory structure.

[oracle@bhanu  dbs] $ mkdir -p /disk1/oradata/bhanu

[oracle@bhanu  dbs] $ cd

STEP 4:  Create database creation script and run at SQL prompt.

[oracle@bhanu  ~] $ vi cr8db.sql

create database bhanudb

datafile '/disk1/oradata/bhanu/system.dbf' size 200m autoextend on

sysaux datafile '/disk1/oradata/bhanu/sysaux.dbf' size 100m autoextend on

undo tablespace undotbs

datafile '/disk1/oradata/bhanu/undotbs.dbf' size 40m

default tablespace userdata

datafile '/disk1/oradata/bhanu/userdata.dbf' size 40m

default temporary tablespace temp

tempfile '/disk1/oradata/bhanu/temp.dbf' size 30m

logfile

group 1('/disk1/oradata/bhanu/redo1.log') size 5m,

group 2('/disk2/oradata/bhanu/redo2.log') size 5m

controlfile reuse;

:wq

NOTE: This 'cr8db.sql' is to create database.

[oracle@bhanu  ~] $  sqlplus / as sysdba

SQL> startup mount

SQL> @cr8db.sql

Database Created

SQL> shut immediate

SQL> exit

NOTE: This command creates brand new database. In which users can be created and they can login and create tables etc. But the environment in not user friendly because there are no views or synonyms and PL*SQL packages or profile so these will be created by running another script.

[oracle@bhanu  ~] $ vi postdb.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

connect system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

~

~

:wq

[oracle@bhanu  ~] $  sqlplus / as sysdba

SQL> startup

SQL> @pupbld.sql

this script runs for some time by the end it shows   "SQL>---END-OF-PUPBLD.SQL----"

NOTE:  

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.

HERE BY CREATION OF ORACLE 11G R2 DATABASE MANUALLY USING COMMANDS HAS BEEN DONE.