Tag: db server

Oracle Server Installation

This document describe the installation process of Oracle Server 19c in a Linux Red Hat Advance Server 7.8 or CentOS 7.8 host.

This release of Oracle can be installed via RPM and so it’s much different – some how simpler – that it used to be.

Consider that, in this case, we choose as SID the code “EVE” and the server name is “dev.myisretail.eu”.

Steps to install

To performe Oracle Server installation the following steps must be performed:

  • wget https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
  • yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
  • download oracle-database-ee-19c-1.0-1.x86_64.rpm from oracle site
  • yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm

Create a database

A database can be created manually or via Oracle Database Assistant; since this tool is graphic we need to install the XWindows system.

To install the XWindows: yum groupinstall “X Window System” -y

To run Oracle Database Assistant it’s important to connect via ssh with the option “-X” or in putty with the flag “Enable X11 forwarding”: /opt/oracle/product/19c/dbhome_1/bin/dbca

Next
For specific parameters “Advance configuration” is the way to go. Since the specific part that we need is about datafile and are too many to be defined here: we choose typical. Password like “isretail” or too simples may rise a warning.
Complete.

Insert into .bash_profile of root and oracle user:

  • export TMP=/tmp
  • export TMPDIR=$TMP
  • export ORACLE_HOSTNAME=dev.myisretail.eu
  • export ORACLE_UNQNAME=EVE
  • export ORACLE_BASE=/opt/oracle
  • export ORACLE_HOME=$ORACLE_BASE/product/19c/dbhome_1
  • export ORACLE_SID=EVE
  • export PATH=/usr/sbin:$PATH
  • export PATH=$ORACLE_HOME/bin:$PATH
  • export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
  • export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
  • export NLS_LANG=american_america.utf8
  • export NLS_COMP=LINGUISTIC
  • export NLS_SORT=GENERIC_M_AI

Arrange configuration files:

listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = EVE)
(ORACLE_HOME =/opt/oracle/product/19c/dbhome_1 )
(PROGRAM = extproc)
)
(SID_DESC=
(GLOBAL_DBNAME=EVE)
(ORACLE_HOME=/opt/oracle/product/19c/dbhome_1 )
(SID_NAME=EVE)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.45.223)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tnsnames.ora

EVE=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev.myisretail.eu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = EVE)
)
)

Create tablespaces

In EVE it’s possible, via definition in action IG014 or “Parameters for tables creation”, choose specific settings for tables and indexed per table.

So if DBA decides, for example, that tables for entities should go in a specific tablespace:

To create the tablespaces that we need run the following statements:

CREATE TABLESPACE ADINDX DATAFILE ‘/opt/oracle/oradata/EVE/datafile/rad_indx01.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rad_indx02.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rad_indx03.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rad_indx04.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE ADTABLE DATAFILE ‘/opt/oracle/oradata/EVE/datafile/rad_table01.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rad_table02.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rad_table03.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rad_table04.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rad_table05.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rad_table06.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rad_table07.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 640M BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE MIINDX DATAFILE ‘/opt/oracle/oradata/EVE/datafile/rmi_indx01.dbf’ SIZE 21G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rmi_indx02.dbf’ SIZE 30G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE MITABLE DATAFILE ‘/opt/oracle/oradata/EVE/datafile/rmi_table01.dbf’ SIZE 13G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE TBS_I0011_LOB_16KB DATAFILE ‘/opt/oracle/oradata/EVE/datafile/tbs_i0011_16kb_lob_01.dbf’ SIZE 2048M AUTOEXTEND ON NEXT 128M MAXSIZE 32740M LOGGING ONLINE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE TTINDX DATAFILE ‘/opt/oracle/oradata/EVE/datafile/rtt_indx01.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rtt_indx02.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32M BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE TTTABLE DATAFILE ‘/opt/oracle/oradata/EVE/datafile/rtt_table01.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rtt_table02.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rtt_table03.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, ‘/opt/oracle/oradata/EVE/datafile/rtt_table04.dbf’ SIZE 32000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

Run Oracle configuration scripts

Important: the following scripts should never run in a database with running application and user logged in.

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Create EVE user

We choose the password “eve2020” but you can obviously decide for anything.

CREATE USER eve IDENTIFIED BY eve2020;

GRANT IMP_FULL_DATABASE TO eve;

ALTER USER eve QUOTA UNLIMITED ON USERS;
ALTER USER eve QUOTA UNLIMITED ON MITABLE;
ALTER USER eve QUOTA UNLIMITED ON ADINDX;
ALTER USER eve QUOTA UNLIMITED ON ADTABLE;
ALTER USER eve QUOTA UNLIMITED ON MIINDX;
ALTER USER eve QUOTA UNLIMITED ON MITABLE;
ALTER USER eve QUOTA UNLIMITED ON TBS_I0011_LOB_16KB;
ALTER USER eve QUOTA UNLIMITED ON TTINDX;
ALTER USER eve QUOTA UNLIMITED ON TTTABLE;