Infrastructure at your Service

Oracle Team

Oracle Cloud Services DBaaS and dbi services best practices

By Franck Pachot

.
dbi icons marine BP When we give our trainings (dbi services workshops) we signal our best practices with this logo. It’s our know-how, acquired through experience, consulting, service desk, exchange at events, beta testing, etc. that we share with our customers.

Let’s have a look at a few points that we recommend to change if you provision a database with DBaaS on the Cloud.

Swapiness

Do you want your database server to swap? Of course not. Most of the allocated memory is for cache, in order to avoid some physical i/o on datafiles or tempfiles. So you don’t want that memory to be read from disk. Swapping processes in advance can make sense on your laptop if, like me, you keep lot of applications open without using them frequently, but not for the database. With high swapiness the cold part of shared pool will be swapped out and have to be swapped in at AWR snapshot time.

cat /proc/sys/vm/swappiness
60

This is definitely something to change.

password expiration

Do you want your system passwords to expire? Probably not.
User passwords must expire and the end-user can change their password during the grace period.
But your system or application accounts that connect automatically must not expire, or you will have an outage.
Put them in external password store. Change them frequently. But don’t expire them automatically.

SQL> select * from dba_profiles where resource_name='PASSWORD_LIFE_TIME';
 
PROFILE
--------------------------------------------------------------------------------------------------------------------------------
RESOURCE_NAME RESOURCE
-------------------------------- --------
LIMIT COM
-------------------------------------------------------------------------------------------------------------------------------- ---
DEFAULT
PASSWORD_LIFE_TIME PASSWORD
180 NO
 
ORA_STIG_PROFILE
PASSWORD_LIFE_TIME PASSWORD
60
 
SQL> select username , profile from dba_users join dba_profiles using (profile) where username in ('SYS','SYSTEM','DBSNMP') and resource_name='PASSWORD_LIFE_TIME' and limit 'UNLIMITED';
 
USERNAME PROFILE
-------------------- --------------------
DBSNMP DEFAULT
SYSTEM DEFAULT
SYS DEFAULT

This is something we recommend to change.

Memory targets and max size


SQL> show parameter memory
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 6032M
memory_target big integer 6032M

We recommend to have a max size a bit higher than the target. Come to our Oracle DBA Essentials to know why 😉

Note that we recommend also to use HugePages when having large SGA, which is not there by default here.

Database file names

Just an example here:

SQL> select group#,member from v$logfile;
 
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
3 /u04/app/oracle/redo/redo03.log
2 /u04/app/oracle/redo/redo02.log
1 /u04/app/oracle/redo/redo01.log

This is the default from dbca. Please don’t name redo with ‘.log’ and don’t name tempfiles with ‘.tmp’. As consultants, we have seen a lot of environments over the years, and, believe it or not, it happens that we find in crontab something like: ‘find /u01 -name “*.log” -type f -mtime +1 -exec rm -f {} \’.

DMK

DBCA is very nice, but our quality exigence is a bit higher than the defaults it proposes. So we have integrated our best practices in our Database Management Kit that we give for free to our customers. ‘DMK Database Create’ integrates our best practices above the DBCA templates.

DMK is compatible with databases on the Cloud.

So what?

Having DBaaS provisioned by a few clicks is nice. And you will be able to apply PSU with a few clicks as well. But take care at a few defaults that you probably want to change.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team