Oracle 11gR2 create database manually

Steps and guides to create an Oracle database instance

0. Prerequisite

$ cat setenv.ora

export ORACLE_BASE=/home/oracle/app
export ORACLE_HOME=${ORACLE_BASE}/product/11.2.0/db_1
export ORACLE_SID=art   #the new instance
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export LD_BIND_NOW=1

$ . ./setenv.ora

2. Create /home/oracle/app/product/11.2.0/db_1/dbs/initart.ora

$ cat /home/oracle/app/product/11.2.0/db_1/dbs/initart.ora

db_name='art'
memory_target=1G
processes = 150
audit_file_dest='/home/oracle/app/oradata/art/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/home/oracle/app/oradata/art/recovery'
db_recovery_file_dest_size=2G
diagnostic_dest='/home/oracle/app/oradata/art'
dispatchers='(PROTOCOL=TCP) (SERVICE=art)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS'
# You may want to ensure that control files are created on separate physical devices
control_files = (/home/oracle/app/oradata/art/control01.ctl, /home/oracle/app/oradata/art/control02.ctl)
compatible ='11.2.0'

3. Create createart.ora

$ cat createart.ora

CREATE DATABASE art
  CONTROLFILE REUSE
  CHARACTER SET UTF8
  NATIONAL CHARACTER SET UTF8
  DATAFILE '/home/oracle/app/oradata/art/system01.dbf' SIZE 2048M REUSE
  SYSAUX DATAFILE '/home/oracle/app/oradata/art/sysaux01.dbf' SIZE 512M REUSE
  UNDO TABLESPACE UNDOTBS
    DATAFILE '/home/oracle/app/oradata/art/undo.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 256M MAXSIZE 4096M
  DEFAULT TABLESPACE USERDATA
    DATAFILE '/home/oracle/app/oradata/art/userdata.dbf' SIZE 512M REUSE
  DEFAULT TEMPORARY TABLESPACE TEMP
    TEMPFILE '/home/oracle/app/oradata/art/temp.dbf' SIZE 512M REUSE
  LOGFILE
    GROUP 1 ('/home/oracle/app/oradata/art/redo01.log') SIZE 256M,
    GROUP 2 ('/home/oracle/app/oradata/art/redo02.log') SIZE 256M
;

  

4. Create createscott.ora

$ cat createscott.ora

CREATE USER scott IDENTIFIED BY tiger;
GRANT PUBLIC, CONNECT, RESOURCE TO scott;
GRANT UNLIMITED TABLESPACE TO scott;
GRANT CREATE SYNONYM, CREATE ANY SYNONYM TO scott;
GRANT CREATE TRIGGER, CREATE ANY TRIGGER TO scott;
GRANT CREATE VIEW, CREATE ANY VIEW TO scott;
GRANT CREATE SEQUENCE, CREATE ANY SEQUENCE TO scott;

5. make database directory

$ mkdir -p /home/oracle/app/oradata/art

$ mkdir -p /home/oracle/app/oradata/art/adump

$ mkdir -p /home/oracle/app/oradata/art/recovery

6. sqlplus /nolog

SQL> connect / as sysdba

Connected to an idle instance.

SQL> create spfile from pfile='/home/oracle/app/product/11.2.0/db_1/dbs/initart.ora';

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2220200 bytes

Variable Size             616566616 bytes

Database Buffers          444596224 bytes

Redo Buffers                5554176 bytes

SQL> @createDB.ora

Database created.

SQL> select status from v$instance;

STATUS

------------

OPEN

# if status != OPEN do

SQL> alter database open;

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> @scott.ora

SQL> connect system/manager

SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

7. update $ORACLE_HOME/network/admin/tnsnames.ora

art =

  (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 'your_host_name')(PORT = 1521))

      (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = art)

      )

  )

8. Restart Oracle