Tales From A Lazy Fat DBA

Below provided are Steps to create a Database manually 9Without using any GUI tool – DBCA)

Manual Creation Of Database
================================

Create Directories in ORACLE_BASE to install physical files.

[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/adump
[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/bdump
[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/cdump
[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/udump

[oracle@localhost root]$ mkdir -p /u01/app/oracle/oradata/doom

Create parameter file for new database
[oracle@prashant2 dbs]$ cp initarcl.ora initdoom.ora

Note: Use Find and Replace command in VI in order to replace all old SID terms from the param file.
ex: :%s/OLD/NEW/g
Capital ZZ to save all changes made.

create Password File using ORAPWD Utility.
[oracle@prashant2 dbs]$ orapwd file=orapwdoom password=oracle90 entries=19

Export newly created SID
[oracle@prashant2 dbs]$ export ORACLE_SID=doom

[oracle@prashant2 dbs]$ echo $ORACLE_SID
doom

Create a script/text file to create Database (Contains Steps to create all data-files, control files, System files, redo log files etc).
Something like:

create database doom
datafile ‘/u01/app/oracle/admin/doom/system.dbf’ size 250M
sysaux datafile ‘/u01/app/oracle/admin/doom/sysaux.dbf’ size 100m
undo tablespace undotbs1 datafile ‘/u01/app/oracle/admin/doom/undo.dbf’ size 100m
default temporary tablespace temp tempfile ‘/u01/app/oracle/admin/doom/tmp.dbf’ size 100m
logfile group 1 ‘/u01/app/oracle/admin/doom/log1.ora’ size 50m,group 2 ‘/u01/app/oracle/admin/doom/log2.ora’ size 50m;
Startup Database in ‘NOMOUNT’ Mode.
As we already created Parameter file for the Database but yet to create controlfiles so it’s not possibel to start DB in MOUNT mode.
Starting DB will allow us to run scripts from any locations.

[oracle@prashant2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Aug 6 15:24:59 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size              96470488 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
SQL> @dbcreate.sql

Database created.

SQL>

N.E.X.T is to load two sql scripts (files) which helps database to create all default tablespaces, views. rows, comments, indexes, packages, procedures, synonyms, libraries, grants and commits columns and other objects and create entries in data dictionary.

catalog.sql – create data dictionary views.
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql

Grant succeeded.

Commit complete.

View created.

catproc.sql – run all sql scripts for the procedural option
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql

@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql
DOC>######################################################################
DOC>######################################################################
DOC>The following PL/SQL block will cause an ORA-20000 error and
DOC>terminate the current SQLPLUS session if the user is not SYS.
DOC>Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

Once Created start Database and check Status of the Instance using:

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
——— ———-
DOOM      READ WRITE

1 row selected.

SQL>

Thanks for Reading.
Prash’ant’ Dixit