General recommendations
Please follow these general recommendations for every Oracle Database installation
Parameter NLS_CHARACTERSET must be AL32UTF8.
The application server uses only dedicated server process and have 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). Example: for 3 censhare Servers, ther number of Oracle processes should be set to 500.
Operating System Timezone should be 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 distribuetd environments with few hundred concurrent censhare users. Key factor for the SGA sizing is the number of censhare assets, features and relations.
Censhare v5 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. sourceIP->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 firewall or listener (validnode_checking) rules on 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 doesn't violate the purchased License. TODO: extend this section with few examples for ASFU licenses provided by censhare.
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 eventual database corruptions 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 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 carefull when installing Oracle product 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 the VMware's interpretation about the Oracle Support position:
For more information, see this Understanding Oracle Certification, Support And Licensing
. See also these white papers:For more information, see this FAQ,
For more information, see this Use Cases
,For more information, see this High Availability
.Other types of Virtualization and Partitioning
Solaris zones: censhare IT have long experiance in SaaS and On-premiss installations. Docket containers: Oracle support 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 for list of
For more information, see this Supported Virtualization and Partitioning Technologies
Censhare's recommendation
For AWS installations, customers should use RDS.
For small on-premises installations, RHEL 7 on a physical machine is a safe choice. Whenever 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 beween 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 have high RPO/RTO expectations. In this case Oracle Database planning, installation and support have to be performend 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 a confirmation about license and certification
Before you install any Oracle Product, get a written confirmation from the customer, project manager or a 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
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
Install required OS packages
Enable the optional RPMS 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
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
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
Firewall
systemctl stop firewalld systemctl disable firewalld TODO: put here an option to only open ports instead of disabling the whole firewall
Setup ORACLE_BASE directory
mkdir -p /u01/app/oracle/product/12.1.0.2/db_1 chown -R oracle:oinstall /u01 chmod -R 775 /u01
Setup 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
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:
.. 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/
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
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
Setup some settings
sqlplus -s / as sysdba < $ORACLE_HOME/network/admin/tnsnames.ora
Backup 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.
Apply the latest Patch Set 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/
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
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