Monday , September 25 2017
Home / Oracle DBA / Database Creation

Database Creation

Database can be created in 4 ways:

  • Command line
  • DBCA(database configure assistant)
  • OMF database
  • ASM Database

1.Command line:

In command line we have to set the all parameters to create the database

Set environment file:

#su – oracle

$mkdir test (create directory for database)

$vi login.sql

Set sqlp”_user’@’_Connect_identifier>”

Define_editor = vi

:wq!

$vi bash_profile (edit the bash_profile and type below parameters)

export ORACLE_SID=test

export ORACLE_HOME=/u01/test/oracle/home

export PATH=$ORACLE_HOME/bin:$PATH

:wq! (save and exit)

$. .bash_profile (run the bash_profile)

$export ORACLE_SID=test (it is only temporary for session)

Create Parameter file:

Go to dbs location

$ vi $ORACLE_HOME/dbs/

$ls

$ cp init.ora inittest.ora (copy init.ora parameters into inittest.ora file)

$vi inittest.ora (edit the inittest.ora parameters)

db_name = test

shared_pool_size = 90m

control_files=/u01/test/control1.ctl,/u01/test/control2.ctl

undo_management = AUTO

compatible = 11.1.0.6.0

diagnostic_dest = /u01/test

undo_tablespace = UNDOTBS1

:wq!( save and exit from vi editor)

$Export ORACLE_SID=test

$vi create.sql (create a script for create database)

createdatabase test

logfilegroup 1 (‘/u01/test/redo1.log’) size 100M,

group 2 (‘/u01/test/redo2.log’) size 100M,

group 3 (‘/u01/test/redo3.log’) size 100M

datafile ‘/u01/test/system.dbf’ size 500M autoextend on next 10M maxsize unlimited extent management local

sysauxdatafile ‘/u01/test/sysaux.dbf’ size 100M autoextend on next 10M maxsize unlimited

default tablespace user_tbs datafile ‘ /u01/test/usertbs.dbf’ size 100m

undotablespace undotbs1 datafile ‘/u01/test/undotbs1.dbf’ size 100M

default temporarytablespace temp tempfile ‘/u01/test/temp01.dbf’ size 100M;

:wq!(save and exit from vi editor)

Start the instance:

$ sqlplus system/manager as sysdba

SQL>startupnomount (Start the database with nomount option)

 Note:In the no mount state oracle reads all initialization parameter values from p file( init test.ora)

Execute the script which we write above

SQL>@create.sql

SQL>host

After successful creation of the database we need to execute cata log.sql and catproc.sql scripts. These scripts update the data dictionary tables and views and pupbld.sql must be execute from system user. This scripts update users product profile.

Run catalog and cat proc:

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

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

connect system/manager

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

After successfully executed above scripts check the tablespaces and datafiles and database versions.

SQL>select * from v$tablespace;

SQL>select * from v$version;

SQL>select name from v$data file;

SQL>select name from v$temp file;

SQL>select name from v$control file;

SQL>set wrap off;

SQL>select * from v$log file;

2.DBCA(database configure assistant) TOOL:

This is a automatic process to create the database. Here no need to create and run the scripts. The dbca tool will take care of everything.

STEPS

#xhost + (enables the graphics mode)

#su – oracle

$dbca (just type dbca in the screen it will show some graphic environment then follow the below steps)

  • Select create database option
  • Click next
  • Select general purpose or transaction processing
  • Click next
  • Select global database name: test 1
  • SID:test1
  • Click next
  • Uncheck configure enterprise manager option
  • Click next
  • The same password all users
  • Sys
  • Sys
  • Click next
  • Select file system option
  • Click next
  • Use database file location from template
  • /u01
  • Click next
  • (√) specify flash recovery area
  • Click next
  • May/may not (√) sample schemas
  • Click next
  • Memory:select Typical-memory size (SGA&PGA)40%)
  • Sizing:block size = 8kb=8192byte
  • Processes=200
  • Connection mode:
  • (.)dedicated server mode
  • (.)shared server mode
  • Click next
  • Oracle recommends using the enhanced default security settings
  • (.)keep the enhanced 11g default security settings
  • ENTER
  • Disable (.) enable automatic maintenance task in lab
  • Click next
  • (√) create database
  • Finish
  • ok
Read More:
Disk Group Administration
RAC Administration
Openfiler Installation steps

Comments

comments

Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

Your email address will not be published. Required fields are marked *