General recommendations

Please follow these general recommendations for every Oracle Database installation

  • Parameter NLS_CHARACTERSET must be AL32UTF8.

  • The application server uses only a dedicated server process and has its own connection pool mechanism. No dispatchers.

  • Parameter PROCESSES must be extended from default 300 to (number of censhare Servers * censhare parameter max-open JDBC connections + number of background Oracle processes). For example: for 3 censhare Servers, the number of Oracle processes should be set to 500.

  • Operating System Timezone should be the same as the master censhare server.

  • At least 5x 200MB redo log groups. For mission-critical environments, the groups should be multiplexed.

  • For maximum performance, the data files should be on SSD and the instance should be configured with ASSM on HugePages.

  • SGA can be sized between 1GB for small DEV servers to 60 GB for globally distributed environments with a few hundred concurrent censhare users. The key factor for the SGA sizing is the number of censhare assets, features, and relations.

  • censhare needs only one tablespace, which we usually name CORPUS. Sizing estimates, between 20 and 100 GB growth per year.

  • grant connect, resource, select_catalog_role on the application user named "corpus"

  • Strict separation on the login to PROD from DEV schemas (aka. source IP->DBschema pairs). For example: If PROD and DEV schemas are in the same database, then a login trigger should allow login to the PROD schema only from the IP on the PROD servers. if DEV and CONS schemas are in different databases, then a firewall or listener (validnode_checking) rules on the PROD database should allow login only from the IP address on the PROD censhare servers.

  • Monitoring with thresholds, alerts, and usage charts on Tablespace and FRA usage, Listener, and Database availability. Eg. Icinga, Enterprise Manager, etc.

Licensing

It's very important that the installed database edition, options, packs, and features don't violate the purchased license. 

Certification of the operating system and platform

It's very important that the database software is installed on a certified operating system and platform, otherwise, you may not get support and be responsible for possible database corruption and data loss.

Here is a snip from the certification requirements for Linux as of January 2018.

## Oracle Database 12.1.0.2
 * Oracle Linux 7.2 with UEK 4: 4.1.12-61.1.19.el7uek.x86_64 or later
 * RHEL 7 with 3.10.0-123.el7.x86_64 or later
 * SLES 12 with 3.12.49-11-default or later

## Oracle Database 11.2.0.4
 * Oracle Linux 7.0 with the UEK 3: 3.8.13-35.3.1.el7uek.x86_64 or later
 * RHEL 7 with 3.10.0-123.el7.x86_64 or later
 * SLES 11

IMPORTANT: For the actual state of the detailed information, always refer to Oracle Note 1304727.1

Oracle database in a VMware environment

Many customers are using VMware infrastructure in their datacenters. You should be very careful when installing Oracle products in a VMware VM.

Official statement from Oracle Global Customer Support

This is the position for Oracle Products Running on VMWare Virtualized Environments (Doc ID 249212.1 as of January 2018)

Support Status for VMware Virtualized Environments Oracle has not certified any of its products on VMware virtualized environments. Oracle Support will assist customers running Oracle products on VMware in the following manner: Oracle will only provide support for issues that either are known to occur on the native OS or can be demonstrated not to be as a result of running on VMware. If a problem is a known Oracle issue, Oracle support will recommend the appropriate solution on the native OS. If that solution does not work in the VMware virtualized environment, the customer will be referred to VMware for support. When the customer can demonstrate that the Oracle solution does not work when running on the native OS, Oracle will resume support, including logging a bug with Oracle Development for investigation if required. If the problem is determined not to be a known Oracle issue, we will refer the customer to VMware for support. When the customer can demonstrate that the issue occurs when running on the native OS, Oracle will resume support, including logging a bug with Oracle Development for investigation if required. NOTE: Oracle has not certified any of its products on VMware. For Oracle RAC, Oracle will only accept Service Requests as described in this note on Oracle RAC 11.2.0.2 and later releases.

VMware's interpretation and guidelines

See this white paper for VMware's interpretation about the Oracle Support position:

Understanding Oracle Certification, Support, and Licensing

See also these white papers:

FAQ

Use Cases

High Availability

Other types of Virtualization and Partitioning

Solaris zones: censhare IT has long experience in SaaS and on-premiss installations. Docker containers: Oracle supports Oracle Database (single instance) running in Docker containers running on Oracle Linux 7 with UEK4 or Red Hat Enterprise Linux 7. For detailed information see Oracle Notes 2216342.1 and 1921163.1. See this page for a list of Supported Virtualization and Partitioning Technologies.

censhare recommendation

For AWS installations, customers should use RDS. For small on-premises installations, RHEL 7 on a physical machine is a safe choice. Whenever a customer needs Load Balancing and Server Failover within the same datacenter, he could use the classical solution for two physical servers (one for censhare and one for Oracle) and in addition buy s2s for data synchronization between the servers. In this case, both servers have to be licensed. For globally distributed business-critical censhare installations with hundreds of users, the business owner usually has high RPO/RTO expectations. In this case, Oracle Database planning, installation, and support have to be performed by the customer's own DBA team or external consulting company.

Install the software and create a database

These are the general steps for installing Oracle Database 12.1.0.2 Standard Edition 2 on RHEL 7.

Get confirmation about license and certification

Before you install any Oracle product, get a written confirmation from the customer, project manager, or colleague:

1. I am sure that customer XXXX have a license for Oracle Database XXXXX Edition.   
2. I am sure the underlying hardware and operating system is certified to run version X.X.X.X 
   and doesn't violates the licensing policy.

Setup kernel parameters

# vi /etc/sysctl.d/98-oracle.conf
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500


..... then activate the changes like this:
sysctl -p
CODE

Setup resource limits

# vi /etc/security/limits.d/oracle-rdbms-server-12cR1-preinstall.conf
oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768
oracle   hard   memlock    134217728
oracle   soft   memlock    134217728
CODE

Install required OS packages

Enable the optional RPM repository needed for some of the packages below.

yum-config-manager --enable rhel-7-server-optional-rpms

Then install the packages:

yum install xauth \
binutils \
compat-libstdc++-33 compat-libstdc++-33.i686 \
gcc \
gcc-c++ \
glibc glibc.i686 \
glibc-devel glibc-devel.i686 \
ksh \
libgcc libgcc.i686 \
libstdc++ libstdc++.i686 \
libstdc++-devel libstdc++-devel.i686 \
libaio libaio.i686 \
libaio-devel libaio-devel.i686 \
libXext libXext.i686 \
libXtst libXtst.i686 \
libX11 libX11.i686 \
libXau libXau.i686 \
libxcb libxcb.i686 \
libXi libXi.i686 \
make \
unixODBC unixODBC-devel \
zlib-devel zlib-devel.i686 \
compat-libcap1.i686 compat-libcap1.x86_64
CODE

Create Oracle user and groups

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
useradd -u 54321 -g oinstall -G dba,oper oracle
passwd oracle
CODE

SELinux

##### Change a parameter in file /etc/selinux/config like this
SELINUX=permissive

... then execute:
setenforce Permissive

TODO: put here an option to setup rules instead of setting it to permisive
CODE

Firewall

systemctl stop firewalld
systemctl disable firewalld

TODO: put here an option to only open ports instead of disabling the whole firewall
CODE

Set up ORACLE_BASE directory

mkdir -p /u01/app/oracle/product/12.1.0.2/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01
CODE

Set up environment variables

# vi /home/oracle/.bash_profile
export ORACLE_HOSTNAME=[ .........PUT HERE YOUR SERVER'S FQDN............]
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_UNQNAME=corpus
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_SID=corpus
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export EDITOR=vim
CODE

Download the software

You can download the software from https://edelivery.oracle.com
If you have access to the file server, copy the software like this:
CODE 
.. from your Mac:
scp /Volumes/vol_censhare_it_data_nfs_iso/Oracle_Database/Standard_Edition_2_12.1.0.2/
     Linux_x64_V77388-01_*of2.zip \ oracle@DBSERVER:/u01/app/oracle/software/
CODE

Install the database software

-------- AS USER oracle
cd /u01/app/oracle/software/
unzip Linux_x64_V77388-01_1of2.zip
unzip Linux_x64_V77388-01_2of2.zip

cd database
export DISTRIB=$(pwd)
./runInstaller -silent \
-responseFile $DISTRIB/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en \
ORACLE_HOME=$ORACLE_HOME \
ORACLE_BASE=$ORACLE_BASE \
oracle.install.db.InstallEdition=SE2 \
oracle.install.db.isCustomInstall=false \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
oracle.install.db.BACKUPDBA_GROUP=dba \
oracle.install.db.DGDBA_GROUP=dba \
oracle.install.db.KMDBA_GROUP=dba \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true

.... wait for the installer to complete


-------- AS USER root
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/12.1.0.2/db_1/root.sh
CODE

Create the database

-------- from now on, all commands have to be executed as AS USER oracle

ln -s $ORACLE_HOME ~/orahome 
ln -s $ORACLE_BASE/oradata ~/orahome/oradata 

dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname corpus -sid corpus -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-nationalCharacterSet AL16UTF16 \
-sysPassword $RANDOM$(date +%S) \
-systemPassword $RANDOM$(date +%S) \
-emConfiguration NONE \
-datafileDestination $ORACLE_BASE/oradata \
-createAsContainerDatabase false \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-storageType FS \
-redoLogFileSize 200 \
-ignorePreReqs
CODE

Set some options

sqlplus -s / as sysdba < $ORACLE_HOME/network/admin/tnsnames.ora

Back up the configuration

copy the lates backup_rman.sh script from GIT and create this cron job
0 21 * * * /home/oracle/bin/backup_rman.sh >/dev/null 2>&1

touch /var/log/backup_rman.log as root and change the owner to oracle
Ensure that /rmanbackup/ is on a storage or volume different than the database files.
Check the log file on the next day and setup active monitoring 
Schedule annual database recovery tests.
CODE

Apply the latest patch update

Critical patch updates are collections of security fixes for Oracle products. They are released on the Tuesday closest to the 17th day of January, April, July, and October.

Download the PSU and OPatch utility

You can download the latest PSU from https://support.oracle.com - see Oracle Note 756671.1 for the latest available patch. if you have access to the file server, you can copy the PSU like this:

scp /Volumes/vol_censhare_it_data_nfs_iso/Oracle_Database/Standard_Edition_2_12.1.0.2/
*Linux-x86-64.zip \
oracle@DBSERVER:/u01/app/oracle/software/
CODE

Update the OPatch utility

cd $ORACLE_BASE/software/
cp -r $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.$(date '+%Y-%m-%d_%H%M%S')
unzip -oq -d $ORACLE_HOME p6880880_122010_Linux-x86-64.zip
opatch lsinv
CODE

Apply the PSU

sqlplus / as sysdba
shutdown immediate
exit

lsnrctl stop

ps -ef | grep oracle # ensure that there are no other oracle processes

cd $ORACLE_BASE/software/
unzip p26713565_121020_Linux-x86-64.zip
cd 26713565
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
opatch apply

lsnrctl start
sqlplus / as sysdba
startup
exit

cd $ORACLE_HOME/OPatch
./datapatch -verbose
CODE