Creating an Oracle Database manually

Most of the time, we need to create the database manually. Below are the steps to create a database manually in few minutes.

1) Create an init<SID>.ora file under $ORACLE_HOME/dbs with the following parameters.

audit_file_dest='/u01/app/oracle/admin/cust/adump'

background_dump_dest='/u01/app/oracle/admin/cust/bdump'

core_dump_dest='/u01/app/oracle/admin/cust/cdump'

control_files='/u01/app/oracle/oradata/cust/control01.ctl','/u01/app/oracle/oradata/cust/control02.ctl','/u01/app/oracle/oradata/cust/control03.ctl'

db_block_size=8192

db_name='cust'

job_queue_processes=10

processes=200

pga_aggregate_target=80M

sga_target=300M

sga_max_size=300M

remote_login_passwordfile='EXCLUSIVE'

sessions=150

undo_management='AUTO'

undo_tablespace='UNDOTBS01'

user_dump_dest='/u01/app/oracle/admin/cust/udump'

2) Create password file using ORAPWD utility.

Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>

orapwd file=orapwcust password=xxxxxx entries=20 force=<y/n>

Note:- There are no spaces around the equal-to (=) character.

          Force is used to overwrite and existing file.

3) Create directories

mkdir –p /u01/app/oracle/admin/cust/adump

mkdir –p /u01/app/oracle/admin/cust/bdump

mkdir –p /u01/app/oracle/admin/cust/cdump

mkdir –p /u01/app/oracle/admin/cust/ddump

mkdir –p /u01/app/oracle/oradata/cust

mkdir –p /u01/app/oracle/oradata/cust/arch

3) Create database manually

REUSE has been used with CONTROLFILE,DATAFILE and ONLINE REDOLOG files so that if the create database statement fails, there is no need to cleanup the files that got created. The files would be overwritten.

CREATE DATABASE rishi controlfile reuse

USER SYS IDENTIFIED BY rishi

USER SYSTEM IDENTIFIED BY rishi

LOGFILE GROUP 1 ('/u02/oradata/rishi/redoa/redo01a.log','/u02/oradata/rishi/redob/redo01b.log') SIZE 50M reuse,

GROUP 2 ('/u02/oradata/rishi/redoa/redo02a.log','/u02/oradata/rishi/redob/redo02b.log') SIZE 50M reuse,

GROUP 3 ('/u02/oradata/rishi/redoa/redo03a.log','/u02/oradata/rishi/redob/redo03b.log') SIZE 50M reuse

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 400

MAXINSTANCES 1

CHARACTER SET US7ASCII

NATIONAL CHARACTER SET AL16UTF16

DATAFILE '/u02/oradata/rishi/system/system01.dbf' SIZE 325M REUSE EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/u02/oradata/rishi/system/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TABLESPACE users datafile '/u02/oradata/rishi/data/users01.dbf' size 20M reuse

DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u02/oradata/rishi/temp/temp01.dbf' SIZE 200M REUSE

UNDO TABLESPACE undotbs  DATAFILE '/u02/oradata/rishi/undo/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

4) Post-db creations scripts

Connect as SYS

SQL> @?/rdbms/admin/catalog.sql

SQL> @? /rdbms/admin/catproc.sql

SQL> @? /rdbms/admin/catblock.sql;

SQL> @?/sqlplus/admin/plustrce.sql

SQL> Connect SYSTEM/XXX

SQL> @?/sqlplus/admin/pupbld.sql

5) Create SPFILE from PFILE

SQL> Create spfile from pfile;

SQL> startup force

5) Enable Archivelog

SQL> Alter system set log_archive_dest_1=’location=/u01/app/oracle/oradata/cust/arch’ scope=spfile;

SQL> Alter system set log_archive_format=’cust_%s_%t_%r.arc’ scope=spfile;

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> alter system switch logfile; – To test archiving