STEP BY STEP MANUAL DATABASE CREATION IN LINUX ORACLE 11G

 

STEP BY STEP MANUAL DATABASE CREATION IN LINUX ORACLE 11G

Create the database manually, using CREATE DATABASE statement. Sometimes as a DBA we are not able to use GUI to create a database in this case, we should know syntax of database creation from SQL*Plus.

Step By Step process to create database manually:

Step 1:- Specify an Instance

export ORACLE_SID=test
export ORACLE_HOME=u01/app/oracle/product/11.2.0/dbhome_1/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

Step 2:-  Create required directories as per your location (I have created Database Directory : /u01/app/oracle/oradata/test)

-pfile

-data

-control

-log

-back

-user

-trace

-archive

-flash_recovery_area (Fast_Recovery_Area)
Step 3:- Create parameter file and modify it by setting minimum required parameters: Save the file (/u01/app/oracle/oradata/test/pfile/inittest.ora)
db_name=’test’

memory_target=500m

processes = 150

audit_file_dest=’/u01/app/oracle/oradata/test/back’audit_trail=’db’

db_block_size=8192

db_domain=”’

db_recovery_file_dest=’/u01/app/oracle/oradata/test/flash_recovery_area’

db_recovery_file_dest_size=2G

diagnostic_dest=’/u01/app/oracle/oradata/test/trace’

dispatchers='(PROTOCOL=TCP) (SERVICE=test)’

open_cursors=300

remote_login_passwordfile=’EXCLUSIVE’

undo_tablespace=’UNDOTBS1′

# You may want to ensure that control files are created on separate physical# devices

control_files = (/u01/app/oracle/oradata/test/control/control1.ctl, /u01/app/oracle/oradata/test/control/control2.ctl)

compatible =’11.2.0′
Step 4:- Create the Password file
$ orapwd file=$ORACLE_HOME/dbs/orapwtest password=test entries=10
Step 5:- Create Server parameter file (SPFILE) using this parameter file and STARTUP the instance in NOMOUNT mode.

$ sqlplus /nolog

SQL> CONNECT SYS AS SYSDBA

SQL> startup nomount pfile=’/u01/app/oracle/oradata/test/pfile/initTEST.ora’;
OR (Use default location to start database with help of pfile. Depend where you have created pfile. If path is not given it will look on DBS location (Default))

SQL> startup nomount pfile=’/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initTEST.ora’;

SQL> CREATE SPFILE FROM PFILE=’/u01/app/oracle/oradata/test/pfile/initTEST.ora’;

Now our instance started, SGA allocated and background processes started
Step 6: CREATE DATABASE (My Directory Locatin:/u01/app/oracle/oradata/test/data)

CREATE DATABASE test
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/test/log/redo1.log’ SIZE 10M,
GROUP 2 ‘/u01/app/oracle/oradata/test/log/redo2.log’ SIZE 10M,
GROUP 3 ‘/u01/app/oracle/oradata/test/log/redo3.log’ SIZE 10M
DATAFILE
‘/u01/app/oracle/oradata/test/data/system.dbf’ size 100M REUSE
sysaux datafile ‘/u01/app/oracle/oradata/test/data/sysaux.dbf’ size 100m
undo tablespace UNDOTBS1
datafile ‘/u01/app/oracle/oradata/test/data/undo1.dbf’ size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE ‘/u01/app/oracle/oradata/test/data/temp01.dbf’
SIZE 100M REUSE
CHARACTER SET AL32UTF8
;

Step 7:- Run Scripts to Build Data Dictionary Views

Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:
@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/sqlplus/admin/pupbld.sql

SQL> EXIT

Congratulation your database is created successfully.

Interview Question.
What is the use of CATALOG.SQL scripts ?

CATALOG.SQL: Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.

What is the use of CATPROC.SQL ?

CATPROC.SQL: Runs all scripts required for or used with PL/SQL.

What is the use of PUPBLD.SQL ?

PUPBLD.SQL Required for SQL*Plus. Enables SQL*Plus to disable commands by user.

SQL> show parameter spfile;

NAME TYPE VALUE
———————————— ———– —————————————————————————————-
spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletest.ora

SQL> select name from v$database;

NAME
————–
TEST

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/test/control/control1.ctl
/u01/app/oracle/oradata/test/control/control2.ctl

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/test/data/system.dbf
/u01/app/oracle/oradata/test/data/sysaux.dbf
/u01/app/oracle/oradata/test/data/undo1.dbf

SQL> select name from v$tempfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/test/data/temp01.dbf

SQL> select member from v$logfile;

MEMBER
——————————————————————————–
/u01/app/oracle/oradata/test/log/redo1.log
/u01/app/oracle/oradata/test/log/redo2.log
/u01/app/oracle/oradata/test/log/redo3.log