ORACLE 11G DATABASE CREATION

                                             ORACLE 11G DATABASE CREATION

Database can be created in multiple ways: -

                        1. Command Line

                        2. DBCA (Database Configuration Assistant)

                       

CREATION OF DATABASE IN COMMAND LINE:

1)    Set Environment file:

  #su – oracle

  $vi .bash_profile

          export ORACLE_HOME=/u01/app/oracle/PRODuct/11.2.0/db_1

           export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib,/usr/ucblib,/usr/openwin/lib

export CLASS_PATH=$ORACLE_HOME/jlib

press esc :wq

Execute bash profile

home/oracle]$. .bash_profile

Set instance name

EXPORT ORACLE_SID=orcl

                                                     OR

$vi /etc/oratab:

Orcl: /u01/app/oracle/PRODuct/11.2.0/db_1:N

Prees esc :wq

$. oraenv
ORACLE_SID[] : orcl

2)    Create Parameter file:

$cd $ORACLE_HOME/dbs

$ vi initorcl.ora

      

*.compatible='11.2.0.0.0'

*.control_files='/u01/oradata/orcl/controlfile01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.diagnostic_dest='/u01/admin'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.sga_target=836763648

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

Press esc :wq!

--create necessary directories to save oradata

$mkdir -p /u01/oradata/orcl

$mkdir -p /u01/admin

3)    Create the database:

$cd

$vi dbcr8.sql

create database orcl

datafile '/u01/oradata/orcl/system01.dbf' size 300m

sysaux  datafile '/u01/oradata/orcl/sysaux01.dbf' size 200m

undo tablespace undotbs1

        datafile '/u01/oradata/orcl/undotbs01.dbf' size 100m

default temporary tablespace tempx

        tempfile '/u01/oradata/orcl/tempx01.dbf' size 100m

default tablespace userdata

        datafile '/u01/oradata/orcl/userdata01.dbf' size 100m

logfile

group 1 '/u01/oradata/orcl/redo1a.log' size 20m,

group 2 '/u01/oradata/orcl/redo2a.log' size 20m,

group 3 '/u01/oradata/orcl/redo3a.log' size 20m

controlfile reuse

character set utf8;

Start Instance:

Home/oracle]$sqlplus / as sysdba

 Sql>startup nomount

SQL> @dbcr8.sql

Database created.

4) Run catalog and catproc:

After Successful creation of the database we need to execute catalog.sql and catproc.sql.These two scripts updates the data dictionary tables and views. And pupbld.sql must be executed from Systemuser.This script updates users product information.

$sqlplus / as sysdba

SQL>@?/rdbms/admin/catalog.sql

SQL>@?/rdbms/admin/catproc.sql

SQL>connect system/manager

SQL>@?/sqlplus/admin/pupbld.sql

Check the datafile and logiles created:-

SQL> select * from v$version;

BANNER

--------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> select name from v$datafile;

SQL> select name from v$controlfile;

   SQL> select GROUP#,MEMBER from v$logfile;

  

Data Dictionary Static & Dynamic Tables and views :

Data dictionary contains structure of the database.It is also  called metadata or repository of the database

Data dictionary tables or views divided into two categories.

            1.  Dynamic tables/views      2.  Static tables/views

1.Dynamic Tables/Views:

These tables/views are used in the mount state and open state.

 Ex:- v$database, v$tablespace,……..etc.

 sys>select name from v$datafile;

2. Static tables/views :-

These tables/views can be used in the open state only

Ex: dba_tablespaces, dba_segments etc.

SQL> select FILE_NAME from dba_data_files;

it cannot work in mount state.

Background Process:

$ps-ef|grep ora_

The following background process are displayed

Smon,pmon,dbwr,lgwr,arc,ckpt,reco etc..

Types of datafiles:-

a) DATA FILE:  Physical storage area of the database is called as Datafile.

The following datafiles must be available in every database.

1) System Datafile    2) Sysaux datafile    3) Users datafile

4) Temporary Datafile 5) Undo datafile

1) System Datafile:

                  This is the main datafile in the database and it contains data dictionary. This data dictionary contains data about data.(or) Meta data (or) Structure of the database. It is also called repository of the database.

System datafile contains the base tables.

Base table: A table which contains the metadata is known as basetable.

All the basetables will be end with ‘$’ symbol

Ex. of basetables à Tab$,view$,priv$....

System datafiles contains metadata in the form of basetables.

2) Sysaux Datafile:

                  Sysaux datafile contains performance related tables workload repository tables. This datafile minimize the load on the system datafile.

3) Users Datafile:

  This is the datafile and contains user data.

4) Temporary Datafile :

                  Every database must require atleast one temporary datafile.It is usefull to perform users temporary calicaulation.For example sorting the records in ascending order and descending order.

5) Undo Datafile:

                         This undo datafile is available from oracle 9i onwords. It contains uncommitted transaction information. Rollback command is used to recover uncommitted transaction information.