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:

Set environment variable by ‘.bash_profile’ file.
Create parameter file(pfile).
Create directory structure.
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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s