Blog

How to Create a Oracle Database – DBA Tutorial

Steps to Create Database

STEP 1: 

Login as a oracle user and move to dbs directory, it is available in oracle_home  location

$cd $oracle_home/dbs

STEP 2:

Create init.ora (ex: initcricket.ora) file i.e., parameter file and keep intIalization parameters which are required to create the database.

For example, here my sid (system identifier) is cricket.

$vi initcricket.ora (press enter here)

Db_name= cricket

Db_files=80

Db_block_size=8192

Db_cache_size = 500m

Shared_pool_size = 300m

Control_files=(/u01/ cricket /control01.ctl,/u01/ cricket /control02.ctl)

Undo_management = auto

Undo_tablespace = undotbs1

Diagnostic_dest=/u01/ cricket /diag

Compatible =11.1.0.6.0

:wq (save  parameter file & quite the file)

Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training.

STEP 3:

Create a directory structures, what you kept in the init .ora file.

For above example i used /u01/cricket directory structure, so we need to create it.

$mkdir -p /u01/cricket

STEP 4:

Now export the oracle_sid value as cricket by using following command.

$export oracle_sid=cricket

]

STEP 5:

Now connect to the sql as sysdba user by using following command.

$sqlplus / as sysdba

STEP 6: 

In startup nomount stage only it is possible to create the database,

So now we move to nomount state by using the following command.

Sql> startup nomount

Checkout Oracle DBA Interview Questions

STEP 7:

For database creation we need to fire the below sql statement.

Sql>create database cricket

Datafile ‘/u01/cricket/system.dbf’ size 1024m extent management local

Sysaux datafile ‘/u01/cricket/sysaux.dbf’ size 700m

Default tablespace user_tbs

Datafile ‘/u01/cricket/user01.dbf’ size 500m

Undo tablespace undotbs1

Datafile ‘/u01/cricket/undo01.dbf’ size 500m

Default temporary tablespace temptbs

Tempfile ‘/u01/cricket/temp01.dbf’ size 500m

Logfile

Group 1 ‘/u01/cricket/redolog01.log’ size 512m,

Group 2 ‘/u01/cricket/redolog02.log’ size 512m,

Group 3 ‘/u01/cricket/redolog03.log’ size 512m;

It will take few minits for  to create the database.

STEP 8:

 After database creation we need to run the two scripts at sql prompt.

These scripts create the data dictionary under system tablespace.

Sql>@$oracle_home/rdbms/admin/catalog.sql

It will take few minits. After this one we run second script.

Sql>@$oracle_home/rdbms/admin/catproc.sql

It will take few minits.

STEP 9:

Now we login as a system user and run one more script.

Sql>conn system/manager

Sql>@$oracle_home/sqlplus/admin/pupbld.sql

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

 

Related Articles:

How To Create TableSpaces - Oracle DBA

Oracle DBA Tutorial

Oracle Transportable Tablespaces - DBA


RELATED COURSES

Get Updates on Tech posts, Interview & Certification questions and training schedules