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

   logfile

   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