Tuesday, September 30, 2014

Manually create Database script in oracle

Manually create Database script in oracle

Prerequisites for creating database:

Step 1: setting environment variable by editing the '.bash_profile'

]$ vi .bash_profile
# User specific environment and startup programs
export ORACLE_SID=orcl
export ORACLE_HOME=/oraeng/app/oracle/product/11.2.0 (specify the your existing oracle home )
export PATH=$ORACLE_HOME/bin:$PATH;


Step 2: create parameter file (pfile) by editing the parameter file

[oracle@redhat ~]$ cd /oraeng/app/oracle/product/11.2.0/dbhome_2/dbs/
[oracle@redhat dbs]$ cp init.ora init$ORACLE_SID.ora
[oracle@redhat dbs]$ vi init$ORACLE_SID.ora (edit the following parameters)
db_name=primary
shared_pool_size = 100Mb
control_files =/disk1/oradata/primary/control.ctl
diagnostic_dest=/disk1/oradata/primary
undo_tablespace=undotbs1
undo_management=auto
compatible = 11.2.0

Step 3: create directory structure

]$ mkdir -p /disk1/oradata/primary
  
Step 4: create database creation script and run at SQL prompt .

1.[oracle@redhat ~]$ vi createdb.sql 
(use the following script, change the directory structure according)

2.create database primary
datafile '/disk1/oracle/primary/system.dbf' size 100m autoextend on
sysaux datafile '/disk1/oracle/primary/sysaux.dbf' size 100m autoextend on
undo tablespace undotbs1 datafile '/disk1/oracle/primary/undotbs1.dbf' size 100m autoextend on
default tablespace users datafile '/disk1/oracle/primary/users.dbf' size 200m autoextend on
default temporary tablespace temp tempfile '/disk1/oracle/primary/temp.dbf' size 100m autoextend on
logfile
group 1 ('/disk1/oracle/primary/redo1.log') size 10m,
group 2 ('/disk1/oracle/primary/redo2.log') size 10m;

3.[oracle@redhat ~]$ sqlplus / as sysdba

SYS> startup nomount

SYS>@createdb.sql

Database created.

Step 5: Postdbcreat.sql

[oracle@redhat ~]$ vi postdbcreate.sql
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
conn system/manager
@$ORACLE_HOME/sqlplus/admin/pupbld.sql
 
save and quit.
 
run the script.

SQL> @postdbcreate.sql

 database creation is complete.

No comments:

Post a Comment

Thanks for giving comments!!