Database Creation on Linux ( Manual)

My Environment

OS : RHEL 5.7

OS User: Oracle

Oracle DB Version : 11.1.0.7 (Already Installed)

ORACLE_HOME : /u01/CHARAN/oracle/11gR1/orahome

Location for Control Files, Redo LogFiles, Database Files : /u01/CHARAN/oracle/11gR1/oradata

Step 1 : Create Environment File

Create an environment file with the below variables in your home location and source the environment file

[oracle@dba01 ~]$ cat db_CHARAN.env

export ORACLE_HOME=/u01/CHARAN/oracle/11gR1/orahome

export LD_LIBRARY_PATH=/u01/CHARAN/oracle/11gR1/orahome/lib

export PATH=/u01/CHARAN/oracle/11gR1/orahome/bin:$PATH

export ORACLE_SID=CHARAN

. db_CHARAN.env

Step 2 : Create a parameter file

cd $ORACLE_HOME/dbs

vi initCHARAN.ora

db_name='CHARAN'

memory_target=1G

processes = 150

audit_trail ='db'

db_block_size=8192

diagnostic_dest='/u01/CHARAN/oracle/11gR1/orabase'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

control_files = (/u01/CHARAN/oracle/11gR1/oradata/control01.ctl)

compatible ='11.1.0'

Step 3: DB Creation Script and Directory Structure

create a script dbcreate.sql in your home directory

[oracle@dba01 ~]$ cat dbcreate.sql

CREATE DATABASE CHARAN

DATAFILE '/u01/CHARAN/oracle/11gR1/oradata/system01.dbf' SIZE 200m AUTOEXTEND ON

SYSAUX DATAFILE '/u01/CHARAN/oracle/11gR1/oradata/sysaux01.dbf' SIZE 200m AUTOEXTEND ON

UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/CHARAN/oracle/11gR1/oradata/undo01.dbf' SIZE 100m

DEFAULT TABLESPACE USERDATA DATAFILE '/u01/CHARAN/oracle/11gR1/oradata/userdata01.dbf' SIZE 100m

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/CHARAN/oracle/11gR1/oradata/temp01.dbf' SIZE 50m

LOGFILE

GROUP 1 ('/u01/CHARAN/oracle/11gR1/oradata/log01.log') SIZE 4m,

GROUP 2 ('/u01/CHARAN/oracle/11gR1/oradata/log02.log') SIZE 4m;

Create the directory structure for database files and diagnostic files

Step 4: Start the instance and run dbcreate script and post creation scripts

sqlplus / as sysdba

SQL> startup nomount

SQL> @dbcreate.sql

Now the database is created and open.

- Oracle creates the files specified in dbcreate.sql script.

- Oracle runs sql.bsq script located in $ORACLE_HOME/rdbms/admin which creates all base tables (ex TAB$) in system tablespace. These base tables will be administered by Oracle itself, any manual updates to base tables will corrupt the integrity of database.

- Base tables can be queried at this stage but data dictinary views are not yet created (ex: DBA_TABLES). To create data dictionary views, we have to run catalog.sql script located in $ORACLE_HOME/rdbms/admin

This will create all data dictionary views. Now you should be able to query data dictionary views (ex: dba_tables)

Like wise to create all database default procedures and packages, we need to run catproc.sql and create profiles we need to run pupbld.sql. pupbld.sql should be run as system user not as sys user.

Note: Once the Oracle instance is started (startup nomount) it will create an alert log file in diagnostic_dest location

Even if the alert log file is deleted, it will be created automatically during next startup of instance.

That's it!!!

Regards

Charan