• Arun Kumar

Manual Database Creation

It’s always a good ideas to create Oracle database using DBCA. But you must also know how to create database manually. It is one of the most frequent interview questions too.


Before you start, make sure Oracle software is installed on the server.

Create Pfile


Copy pfile from any other database server and rename it to new database SID.

other database pfile
initproddb.ora

change the file name to reflect new SID
initdevdb.ora

Open the initdevdb.ora file, find and replace all old SID (proddb) with new SID (devdb). Save and close the pfile.


Now create all the new directory path on your server as per the pfile.

mkdir <location_from_new_pfile>

Set environmental variables to connect new SID

export ORACLE_SID=devdb


Issue CREATE DATABASE


Make sure undo tablespace name is same in pfile and also create database statement.

STARTUP NOMOUNT;

CREATE DATABASE devdb
   USER SYS IDENTIFIED BY sys
   USER SYSTEM IDENTIFIED BY sys
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/devdb/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/devdb/redo02.log') SIZE 100M
   DATAFILE '/u01/app/oracle/oradata/devdb/system01.dbf' SIZE 325M 
   SYSAUX DATAFILE '/u01/app/oracle/oradata/devdb/sysaux01.dbf' SIZE 325M
   DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/devdb/temp01.dbf' SIZE 20M
   UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/devdb/undotbs01.dbf' SIZE 200M;


Post DB Creation Steps


Run post DB CREATE scripts

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql

Update /etc/oratab file with new database.

Recent Posts

See All

How to plan database creation

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files. Before you can simply go ahead and start

Oracle Transportable Tablespace

Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Rig

Grant Select on all tables in a schema

I encountered this situation where I wanted to grant SELECT on all the tables owned by one user to another user. There are two simple ways to achieve this: Generate SQL for each table In this method,

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback