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

8. Restart Oracle