0 Database creation on linux

Database creation

Database can created in three different ways in oracle
1)DBCA(GUI interface creation)
2)OMF(Oracle Managed Files)
3)Manual Creation

Oracle supports some tools to create a database.But, here we are manually creating the database.
 Generally the home directory for oracle software is ORACLE_HOME.Different oracle versions are stored in this oracle_home directory

Manual Database creation:

Steps to create database manually

Step 1:  Set the environmental variables

  • environmental variables are nothing but a executable file
  • go to .bash_profile directory and set some environmental variables in that directory as follows
     export ORACLE_HOME=  <path where software is located>
     export  ORACLE_SID    =  <sid name>
     export    PATH                 =$ORACLE_HOME/bin:$PATH:.
         :wq!-------------save file
  • set the above environmental variables in .bash_profile directory and execute the file as follows
    $ . .bash_profile---------->to execute .bash_profile
    $echo $ORACLE_HOME------>to display contents in ORACLE_HOME

Step 2: Creating pfile(parameter file)  

$ cd ORACLE_HOME/dbs----->move to dbs directory where it is located in ORACLE_HOME
$ ls------>list all file in that directory
$ cp init.ora  init$ORACLE_SID.ora-------->copy init.ora file to init<SID NAME>.ora
$vi init<SID NAME>.ora--------------->open copied file and make changes as follows

  db_name=<SID NAME>

  control_file=/disk1/oradata/test_database/control.ctl------->create this directory path before editing this file
 diagnostic_dest=/disk1/oradata/test_database------->create directory path before editing this file

:wq!-------- >save file

Step 3: Script for creating database
  • Make a folder with name createdatabse.sql in /home location
  • Now enter into that folder and write script as follows
     create database <SID NAME>
    datafile '/disk1/oradata/test_database/system.dbf ' size 170m autoextended on
    sysaux datafile '/disk1/oradata/test_database/sysaux.dbf ' size 70m autoextended on
    undo tablespace  <Tablespace name>
   datafile  '/disk1/oradata/test_database/<tablespace name>.dbf ' size 30m
   default tablespace <Tablespace name>
   datafile  '/disk1/oradata/test_database/<tablespace name>.dbf ' size 40m
   default temporary tablespace<Tablespace name>
   tempfile '/disk1/oradata/test_database/<tablespace name>.dbf ' size 30m
   group 1( '/disk1/oradata/test_database/redo1.log') size 4m,
   group 2( '/disk1/oradata/test_database/redo2.log') size 4m
   controlfile reuse;

:wq!------------->save the file

Step 4 : Script creating for base tables
  • Make a folder with name basetables.sql in /home location
  • Now enter into that folder and write script as follows
@$<path where oracle software is located>/rdbms/admin/catalog.sql----->(this is to create base tables upon that basetables it will create views upon the view it will create sysnonyms and grant some privilages for the user)
@$<path where oracle software is located>ORACLE_HOME/rdbms/admin/catproc.sql------>(this is to create packages and PL/SQL procedures)
conn system/manager------>to connect as admin user
@$<path where oracle software is located>ORACLE_HOME/sqlplus/admin/pupbld.sql ------->(to start sql*plus to all users)

Step 5 : connect to oracle database as follows
$ sqlplus / as sysdba-----> to connect to oracle database
sys> startup nomount  ---- - > to connect database in nomount state
sys> @createdatabase.sql ------ > run the script to create database
sys> @createbasetables.sql ------> run the script to create base tables
sys> select instance_name,status from v$instance;------>check the status after creating the databse


Oracle DBA Tutorial Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates