MANUAL DATABASE CREATION

Oracle Version: 10.2.0.3
OS version: Solaris 5.10/Linux

To Create an oracle database using manual scripts without using dbca, we need to do the following steps.

Here the ORACLE_SID = SRITSTDB

Things we need before creating the database.

1) Proper Oracle HOME and the software should be installed in the $ORACLE_HOME
2) Folder structure in the admin directory which contains adump,bdump,cdump,and udump.
3) Init parameter file
4) DB Creation script

Step 1: The database software i.e ORACLE_HOME binaries should be installed. For installing ORACLE SOFTWARE please follow the steps described in "Oracle 10g Home Install" page.
On the database host do the following.

cd $ORACLE_HOME
ls -ltr -> Here it should list all the files which are installed during the software installation.

My Oracle home is located at /local/mnt/oracle/product/10.2.0

Step 2: Create the adump, bdump, cdump and udump directories.
We have to create the adump,bdump, cdump and udump folders in the admin directory.

Normally admin directory will be created in the directory structure. Look for admin directory for oracle on your host.

In my system my admin directory is located at /local/mnt/oracle/admin/SRITSTDB/

In this directory create adump, bdump,cdump and udump directories using mkdir command.


Step 3: Creating INIT parameter file.
On the database host do the following. The location of this file should be in $ORACLE_HOME/dbs/, because oracle will look into this folder by default for init parameter file.

cd $ORACLE_HOME
cd dbs

vi initSRITSTDB.ora

Enter the following entries in this file. All these may not be necessary. But some of them are must.

*.aq_tm_processes=0
*.audit_file_dest='/u01/app/oracle/admin/SRITSTDB/audit'
*.background_dump_dest='/opt/mis/oracle/admin/SRITSTDB/bdump'
*.COMPATIBLE='10.2.0.1'# 10.2
*.control_files='/u01/app/oracle/data/db1/data/SRITSTDB/SRITSTDB_control01.ctl',
'/u01/app/oracle/data/db2/data/SRITSTDB/SRITSTDB_control02.ctl',
'/u01/app/oracle/data/db1/data/SRITSTDB/SRITSTDB_control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/SRITSTDB/cdump'
*.db_block_size=8192
*.db_domain='WORLD'
*.db_file_multiblock_read_count=16
*.db_files=1024
*.db_name='SRITSTDB'
*.job_queue_processes=0
*.large_pool_size=150M# increase to 150M minimum req for 10.2, was 64M
*.log_archive_dest='/u01/app/oracle/admin/SRITSTDB/arch/SRITSTDB_'
*.nls_date_format='DD-MON-YYYY HH24:MI:SS'
*.NLS_LENGTH_SEMANTICS='CHAR'# turn on after upgrade
*.open_cursors=1800
*.pga_aggregate_target=500M
*.remote_os_authent=FALSE
*.sga_max_size=1000M
*.sga_target=512M
*.undo_management='AUTO'
*.undo_retention=28800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/uo1/app/oracle/admin/SRITSTDB/udump'
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

Make sure all the yellow shaded paths should exists.


Step 4: Create password file in the $ORACLE_HOME/dbs folder.
On the database host do the following.

cd $ORACLE_HOME
cd dbs

By Staying in "dbs" directory issue the following command. Here $ORACLE_SID is the name of the database which we are creating.

$ORACLE_HOME/bin/orapwd  file=orapw$ORACLE_SID password=srikanth entries=5


Step 5: DB creation scripts. In the next step I will show how to execute these scripts. In this step I will list all the scripts which are needed for the database creation.

We have to create the db creation scripts. The DB creation scripts will be as follows.

You can create these scripts in any folder you want in your machine.

Script 1: cr_db.sql file. --- This script accepts one parameter and the parameter will be the ORACLE DATABASE NAME. HERE the database name is SRITSTDB.

set time on timing on feed on
spool cr_db.log
startup nomount
CREATE DATABASE "&DBNAME"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
---
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
---
DATAFILE '/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._system1a.dbf' SIZE 1000M extent management local
SYSAUX DATAFILE '/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._sysaux1a.dbf' SIZE 1000M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._temp1a.dbf' SIZE 1000M
extent management local uniform size 1m
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._undotbs1a.dbf' SIZE 1000M
---
LOGFILE
GROUP 1 ('/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._log1a.dbf','/opt/mis/oracle/data/db2/data/&DBNAME/&DBNAME._log1b.dbf') SIZE 100M,
GROUP 2 ('/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._log2a.dbf','/opt/mis/oracle/data/db2/data/&DBNAME/&DBNAME._log2b.dbf') SIZE 100M,
GROUP 3 ('/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._log3a.dbf','/opt/mis/oracle/data/db3/data/&DBNAME/&DBNAME._log3b.dbf') SIZE 100M
---
USER SYS IDENTIFIED BY srikanth
USER SYSTEM IDENTIFIED BY srikanth
/

NOTE: MAKE SURE ALL THE SHADED PATHS SHOULD EXIST ON YOUR MACHINE. YOU CAN CHANGE THE THESE DIRECTORY PATHS WHAT EVER YOU WANT.


Script 2: @dictionary.sql

connect /as sysdba
spool dictionary.out
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catoctk.sql
@?/rdbms/admin/owminst.plb
@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql
@?/rdbms/admin/catclust.sql

connect SYSTEM/srikanth
spool sqlplus.log
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
spool off

spool off

Step 6: Creating a wrapper script to execute the above 2 scripts.

Make another wrapper script called "SRITSTDB.sql" with the following contents.

def DBNAME="SRITSTDB" @cr_db.sql @dictionary.sql
create tablespace users datafile '/opt/mis/oracle/data/db4/data/SRITSTDB/SRITSTDB_users1a.dbf' size 1000M;
alter database default tablespace users ;
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited ;
exit;

Step 7: Execution.

UNIX_PROMPT> sqlplus "/as sysdba"
@SRITSTDB.sql