Create oracle 10g database manually

It is assumed in this article that you have already installed oracle software.

The purpose of this article is to learn how we can manually create a oracle 10g database without the help of 'DBCA'(Database configuration assistant).

Lets get started.

We will create a database named pub.

Folders to create: -

/u01/app/oracle/admin/pub/bdump
/u01/app/oracle/admin/pub/cdump
/u01/app/oracle/admin/pub/udump
/u01/app/oracle/admin/pub/adump
/u01/app/oracle/admin/pub/pfile
/u01/app/oracle/admin/pub/dpump

Give the “pub” folder and its sub directories following permissions: -

Give file Permissions of 750 to the folder pub, --same for subdirectories.

Choose the owner of pub to “oracle” and group to “dba”, --same for the subdirectories.

Create two more folders, one in the oradata folder as “pub”
And second in the flash_recovery_area as “PUB”, change file permissions to 750 and again assign the owner of these folders as “oracle” and group to “dba”.

Create pfile in the pfile folder at the admin directory as initpub.ora

db_block_size=8192
db_file_multiblock_read_count=16
open_cursors=300
db_domain=""
db_name=pub
background_dump_dest=/u01/app/oracle/admin/pub/bdump
core_dump_dest=/u01/app/oracle/admin/pub/cdump
user_dump_dest=/u01/app/oracle/admin/pub/udump
control_files=("/u01/app/oracle/oradata/pub/control01.ctl",
              "/u01/app/oracle/oradata/pub/control02.ctl",
              "/u01/app/oracle/oradata/pub/control03.ctl")
db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
job_queue_processes=10
compatible=10.2.0.1.0
processes=150
sga_target=285212672
audit_file_dest=/u01/app/oracle/admin/pub/adump
remote_login_passwordfile=EXCLUSIVE
dispatchers="(PROTOCOL=TCP) (SERVICE=pubXDB)"
pga_aggregate_target=94371840
undo_management=AUTO
undo_tablespace=undotbs

Remember to grant file permission of 640 and choose owner & group as ‘oracle’ & ‘dba’ to this file.

Now open the command prompt, Switch to the oracle user.
su – oracle
export ORACLE_SID=pub ---- defines the name of the database to work with.
sqlplus ---- Gives access to oracle’s command prompt.
/ as sysdba ---- login as sys user

Startup the instance using the above created pfile.

STARTUP NOMOUNT
or
startup nomount pfile=’ /u01/app/oracle/admin/pub/pfile/initpub.ora’


Next in the sql prompt type the create database parameters and execute.

CREATE DATABASE pub
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY sys
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/pub/redo01.log') SIZE 100M,
        GROUP 2 ('/u01/app/oracle/oradata/pub/redo02.log') SIZE 100M,
        GROUP 3 ('/u01/app/oracle/oradata/pub/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/pub/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/pub/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TEMPORARY TABLESPACE temp1
   TEMPFILE '/u01/app/oracle/oradata/pub/temp01.dbf'
   SIZE 20M REUSE
UNDO TABLESPACE undotbs
   DATAFILE '/u01/app/oracle/oradata/pub/undotbs.dbf'
   SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Or you can make a script file with the above commands and execute at the sql command prompt as following: -

@’/u01/app/oracle/admin/pub/pfile/createdb.sql’

After this create additional tablespaces

-- create a user tablespace to be assigned as the default tablespace for users
CREATE TABLESPACE users LOGGING
  DATAFILE '/u01/app/oracle/oradata/pub/users01.dbf'
  SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING
  DATAFILE '/u01/app/oracle/oradata/pub/indx01.dbf'
  SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL;

Finally run the following scripts to build views, synonyms and pl/sql packages: -

CONNECT SYS/password AS SYSDBA
@’/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql’
@’/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql’

SHUTDOWN IMMEDIATE

CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/pub/pfile/initpub.ora';

STARTUP

-- From now on the instance will start using the server parameter file.

EXIT


Create password files.

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=sys entries=10

n specifies the maximum number of distinct DBAs and OPERs that can be stored in the password file.

The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix.

Adding Users to the password file
Users are added to the password file when they're granted the SYSDBA or sysoper privilege.

SYS@ora10> show user;
USER is "SYS"
SYS@ora10> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

SYS@ora10> grant SYSDBA to rene;

Grant succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
RENE                           TRUE  FALSE

SYS@ora10> grant SYSOPER to rene;

Grant succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
RENE                           TRUE  TRUE

SYS@ora10> revoke SYSDBA from rene;

Revoke succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
RENE                           FALSE TRUE

SYS@ora10> revoke SYSOPER from rene;

Revoke succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE