Installing Oracle XE, ORDS and Apex on CentOS - Part Three: Additional configuration

This is part three in a series of blog posts about how to install Oracle 11g Express Edition (XE) with Oracle Application Express (Apex) on a CentOS Linux server, with Apex served by Oracle REST Data Services (ORDS) running on top of Tomcat and Apache.

By now, you should have a working installation of CentoOS with Oracle XE, Apex, ORDS, TomCat and Apache. There are still some additional configuration and optimization we should do, which we'll take a look at in this third installment of the blog post series.

This part is a "grab bag" of various tips. None of these steps are really required for a working installation, but most of them are strongly recommended for better security and performance.

Disabling root login

So far we have been using the root user to login to the server and perform administrative tasks. For various reasons it is better to disable logins for the root user and instead create an alternative user that can elevate its privileges to root level only when necessary.

This is explained in detail in this article.

Cleaning up Oracle XE

Let's clean up and disable/remove stuff we don't need from Oracle XE:

	-- do some cleanup after Oracle XE installation
	-- run as SYS
	-- disable XDB server
	-- assumes we will use ORDS or other web listener instead
	exec dbms_xdb.sethttpport(0);
	exec dbms_xdb.setftpport(0);
	-- anonymous user is not needed when we don't use XDB
	alter user anonymous account lock;
	-- drop demo schemas
	drop user hr cascade;

view raw xe_post_install_cleanup.sql hosted with ❤ by GitHub

Optimizing Oracle XE

Let's change some default session, process and memory settings in Oracle XE:

	-- run as SYS
	sqlplus /nolog
	connect sys as sysdba
	-- show current values
	show parameters sessions;
	show parameters processes;
	show parameters memory;
	-- adjust values
	alter system set sessions=250 scope=spfile;
	alter system set processes=200 scope=spfile;
	-- note: make sure there is enough shared memory space (tmpfs) before increasing database memory parameters
	-- see "Scaling up and down the server" for separate script to adjust tmpfs size
	alter system set memory_target=1024M scope=spfile;
	alter system set memory_max_target=1024M scope=spfile;
	-- restart database
	shutdown immediate;
	startup;

view raw xe_post_install_optimize.sql hosted with ❤ by GitHub

Setting up Oracle XE network ACLs

If you plan to do any network calls from the database, for example to invoke web services, you need to explictly open the database Network Access Control List (ACL) to enable outgoing traffic to certain hosts/IP addresses and ports.

This is explained in detail in this article. Here is a sample script that can be used as a starting point:

	-- to be run as user SYS
	-- to avoid ORA-30992 and ORA-01858 due to invalid date format when calling create_acl
	alter session set nls_language = AMERICAN;
	alter session set nls_territory = AMERICA;
	BEGIN
	DBMS_NETWORK_ACL_ADMIN.create_acl (
	acl => 'apex.xml',
	description => 'Access Control List for APEX',
	principal => 'APEX_050000',
	is_grant => TRUE,
	privilege => 'connect',
	start_date => SYSTIMESTAMP,
	end_date => NULL);
	COMMIT;
	END;
	/
	-- for outgoing mail via local mail server
	BEGIN
	DBMS_NETWORK_ACL_ADMIN.assign_acl (
	acl => 'apex.xml',
	host => 'localhost',
	lower_port => 25,
	upper_port => 25);
	COMMIT;
	END;
	/
	-- for integration to PayPal (also requires Oracle Wallet with SSL certificate)
	BEGIN
	DBMS_NETWORK_ACL_ADMIN.assign_acl (
	acl => 'apex.xml',
	host => '*.paypal.com',
	lower_port => 443,
	upper_port => 443);
	COMMIT;
	END;
	/
	-- for integration with Amazon S3 (use port 443 if using SSL)
	BEGIN
	DBMS_NETWORK_ACL_ADMIN.assign_acl (
	acl => 'apex.xml',
	host => '*.amazonaws.com',
	lower_port => 80,
	upper_port => 80);
	COMMIT;
	END;
	/
	/*
	-- add another user/schema to already existing ACL
	BEGIN
	DBMS_NETWORK_ACL_ADMIN.add_privilege (
	acl => 'apex.xml',
	principal => 'YOUR_SCHEMA_NAME',
	is_grant => TRUE,
	privilege => 'connect',
	position => NULL,
	start_date => NULL,
	end_date => NULL);
	COMMIT;
	END;
	/
	*/
	-- to verify settings:
	select host, lower_port, upper_port, acl
	from dba_network_acls;
	select *
	from dba_network_acl_privileges;

view raw oracle_network_acl_config.sql hosted with ❤ by GitHub

Closing XDB port (8080) in the firewall

Remember to close port 8080 in the firewall, as it is not be needed in the default software stack we have set up. Revisit the firewall.sh script we created earlier and make sure that the line that opens port 8080 has been commented out or deleted from the firewall script. Re-run the script if necessary to make sure the change takes effect.

Optimizing ORDS

The default connection pool settings in the ORDS configuration are too small. You'll have to experiment to see what the best settings are for your workload, but the following seem to work well. Edit the /u01/ords/config/ords/conf/apex.xml file and put in the below JDBC settings. Restart Tomcat for the changes to take effect.

	<entry key="jdbc.InitialLimit">10</entry>
	<entry key="jdbc.MinLimit">10</entry>
	<entry key="jdbc.MaxLimit">60</entry>

view raw ords_jdbc_config.xml hosted with ❤ by GitHub

Cleaning up Tomcat

Let's remove the sample apps from the default Tomcat install, as we won't be needing any of it:

	# Remove Tomcat default apps/pages
	cd /usr/share/tomcat7/latest/webapps
	rm -rf docs/*
	rmdir docs
	rm -rf examples/*
	rmdir examples
	rm -rf host-manager/*
	rmdir host-manager
	rm -rf manager/*
	rmdir manager
	rm -rf ROOT/*
	# now put your own content in a ROOT/index.html file
	# see http://wiki.apache.org/tomcat/HowTo#How_do_I_override_the_default_home_page_loaded_by_Tomcat.3F

view raw tomcat_post_install_cleanup.sh hosted with ❤ by GitHub

Cleaning up Apache

Let's remove the default Apache home page and replace it with our own. Do this by creating an index.html page on your local computer and then copy it to the correct folder on the server:

	# copy files from client to server
	# adjust SSH port number, file paths, server/host name as appropriate
	cd /users/yourname/projectname/website
	scp -P 22 index.html root@server-name-or-ip:/var/www/html/

view raw apache_copy_homepage.sh hosted with ❤ by GitHub

Optimizing Apache

Well, not so much optimizing Apache itself, but optimizing delivery of website content by making sure that the contents is being compressed and cached, which will speed up performance.

Actually, we already did this as part of the Apache installation (in part two of this blog post series). If you look back at the apex.conf file we created, it included configuration for the modules mod_deflate (for compression) and mod_expires (for caching).

You can verify that compression is enabled by looking at the headers returned from the server via Chrome's network tab (look for the Content-Encoding header):

Also, if you enable a two-line display of each request, Chrome will show the uncompressed file size as well as the compressed file size, so you can see how much bandwidth was saved.

You can verify that caching is enabled (for static files such as images and Javascript) by looking at the Expires header, which means that the browser should keep a copy of this file and not download it again until after it has expired:

I usually put an expiration of 7 days on static files. This means that typical business applications that are used daily or several times per week will always have a copy of the static files already downloaded, which speeds up things considerably. And if you do change the files (as part of an application upgrade), then it will take a maximum of one week before all your users have the latest files. (Tip: Users can always force a new download of cached files by holding down the Shift key and reloading the page in the browser.)

Chrome shows files retrieved from the cache as "(from cache)" and no time (0 ms) is spent downloading the file.

Adding robots.txt

Add a robots.txt file to the root of your website to avoid search engines hitting your website and wasting its resources and bandwidth. This assumes that you mostly have private (internal business) Apex applications that require logins, and since the search engine will not be able to login, we might as well tell the search engines to not even bother trying.


Here is a sample robots.txt that advises search engines to avoid certain folders. Note that the robots.txt file in no way blocks access, so don't try to use this to "secure" content.

	User-agent: *
	Disallow: /pls/
	Disallow: /apex/
	Disallow: /ords/
	Disallow: /web/
	Sitemap: http://www.mysite.example/sitemap.xml

view raw robots.txt hosted with ❤ by GitHub

If, on the other hand, you have applications with public pages that you would like to have indexed by search engines, then by all means change or skip the robots.txt file altogether. (Or look into the use of sitemaps to direct the search engine towards the important pages of your website.)

Setting up SSL certificates

Any website that requires authentication should be running via HTTPS to encrypt the communication between the client and the server. This means you need to install an SSL certificate.

For development environments, you could use a self-signed certificate, which is free. The disadvantage is that browsers will complain (some more than others) about the self-signed certificate being unsafe because it is "untrusted", but as long as you know who installed the certificate (you!), it's fine and you can ignore the warnings.

Installing mod_ssl and configuring a self-signed certificate is explained in this article. Here is a short version:

	# install mod_ssl and set up self-signed certificate on Apache on CentOS
	# install mod_ssl
	yum install mod_ssl -y
	# directory to store keys and certificates
	mkdir /etc/httpd/ssl
	# create server key and self-signed certificate
	# you will be prompted for certificate information
	# for "Common Name", enter your domain name, or if you don't have one yet, your site's IP address
	openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout /etc/httpd/ssl/apache.key -out /etc/httpd/ssl/apache.crt
	# open the SSL configuration file
	nano /etc/httpd/conf.d/ssl.conf
	# make the changes (see separate file for details)
	# restart apache
	service httpd restart

view raw apache_ssl_install.sh hosted with ❤ by GitHub

Modify the default configuration to disable weak/broken ciphers and protocols, and also to set up the port forwarding of the SSL port (443) to Tomcat/ORDS:

	# use the domain name or IP address
	ServerName example.com:443
	# point to the files created when generating the certificate
	SSLEngine on
	SSLCertificateFile /etc/httpd/ssl/apache.crt
	SSLCertificateKeyFile /etc/httpd/ssl/apache.key
	# disable broken/weak protocols and ciphers
	# see https://cipherli.st/ for an updated list
	SSLProtocol all -SSLv2 -SSLv3
	SSLCipherSuite "EECDH+ECDSA+AESGCM EECDH+aRSA+AESGCM EECDH+ECDSA+SHA384 EECDH+ECDSA+SHA256 EECDH+aRSA+SHA384 EECDH+aRSA+SHA256 EECDH+aRSA+RC4 EECDH EDH+aRSA !RC4 !aNULL !eNULL !LOW !3DES !MD5 !EXP !PSK !SRP !DSS"
	# forward dynamic (ORDS) requests to Tomcat, same as is done for http on port 80
	<VirtualHost _default_:443>
	ProxyRequests Off
	ProxyPreserveHost On
	<Proxy *>
	Order deny,allow
	Allow from all
	</Proxy>
	ProxyPass /ords ajp://localhost:8009/ords
	ProxyPassReverse /ords ajp://localhost:8009/ords
	</VirtualHost>

view raw apache_ssl_conf hosted with ❤ by GitHub

For production environments you need an SSL certificate issued by a Certificate Authority (CA). There are many CAs and lots of companies offering SSL certificates. I won't go into the details of buying a certificate here, but pick a cheap one -- there is really not much technical difference between the cheap certificates and the very expensive ones.

After you have set up the SSL certificate on your website, you should test that it works correctly by running the SSL test at ssllabs.com. You should aim for nothing less than a "Grade A" result. If you are using a self-signed certificate, the test will complain about the certificate not being trusted, but you still get the SSL settings of your site verified.

Setting up backups

There are two parts to this: Backing up the server itself, and backing up the database. The latter is the most important to me (as the operating system can always be reinstalled quite quickly, as this series of blog posts show, but the database of course contains the real value -- your data!).

A cloud server provider such as DigitalOcean offers some built-in mechanisms for backups and snapshots, as explained here.

Also, you could use the rsync command to set up your own backup of the OS files.

For backing up the database (schemas), see this blog post I wrote a couple of years ago about using PL/SQL to do Data Pump exports and transfer the files to Amazon's Simple Storage Service (S3).

Monitoring server uptime

Having spent all this effort to set up your website, you want to make sure it is up and available to your users. You should set up one or more automated uptime monitors, using a free service such as Pingdom or UptimeRobot. These services will hit your server from various locations around the world at regular intervals, and send you an email or SMS if the site is unavailable. They will also keep statistics of the average response times and the uptime percentage, which is useful to analyse your server performance over time.

Scaling up (or down) the server

At some point you may want to scale up your server by adding more CPUs, disk space, memory and/or bandwidth. If you are using a cloud service such as DigitalOcean, this is very easy to do. Simply shut down the server, select a new server size from the control panel, and restart the server. This is explained here, and the process will be similar for other virtual server environments.

Note that if you scale down the server and thereby reduce the amount of available memory, you might get an ORA-00845: MEMORY_TARGET not supported on this system error from Oracle and you might need to adjust the operating system memory settings as described in this article. The short version is as follows:

	# adjust tmpfs on CentOS as appropriate for Oracle XE
	# see https://oracle-base.com/articles/11g/automatic-memory-management-11gr1
	# check the available space
	df -k
	# adjust available space
	umount tmpfs
	mount -t tmpfs shmfs -o size=1024M /dev/shm
	# make this change permanent
	nano /etc/fstab
	# make sure the "tmpfs" line has the following values
	# tmpfs /dev/shm tmpfs size=1024M 0 0

view raw centos_tmpfs.sh hosted with ❤ by GitHub

Keeping the server updated

You should keep your server up-to-date with the latest OS patches. You can manually run yum list updates to see what's available, and yum update to download and install all relevant operating system updates that are available. (On DigitalOcean, you will get a warning that says "grubby fatal error: unable to find a suitable template" that has to do with the way Linux kernels are managed in DigitalOcean. You can ignore the warning. If you need to update the kernel, this is done via the server control panel, as described here.)

To set up automatic updates, install the package yum-cron and configure it (described in more detail here).

	# see http://linuxaria.com/pills/enabling-automatic-updates-in-centos-6-and-red-hat-6
	# install yum-cron
	yum install yum-cron -y
	# if desired, set up admin email for notifications
	# look for the MAILTO option
	nano /etc/sysconfig/yum-cron
	# enable automatic updates
	/etc/init.d/yum-cron start
	# make sure the auto-updater starts at boot time
	chckconfig yum-cron on

view raw centos_yum_cron.sh hosted with ❤ by GitHub

Next steps

 
That concludes this collection of additional configuration. The next (and final) blog post in this series will deal with performance testing the new server to verify that it can handle an appropriate number of users.