Standards in Database Conventions with EBS and 19c

OATUG just completed their Database Upgrade Week 2022 (if you are a member, the slides and recorded sessions are in The Knowledge Base.  If you are not a member, you should join.)

During the cloning panel, we were asked a couple of questions about standards which I thought would be a good idea to address here.  These are my opinions, so feel free to comment if you feel differently.

The first question was about locations on the filesystem as well as OS usernames (I am assuming Unix/Linux OS here).

OS Standardization:

• At this time, you should be using different systems for the database and applications tiers.
  • Licenses are based on processors for the database.  If these processors are also being used for the applications tier, you are writing a very large check to Oracle for processors that the database does not need.
    • Licensing tangent
      • If you are on-premise, licensing applies to the physical hardware.  If you use virtualization software, you may want to keep the hosts used for the database separate from any others. 
      • Your contract with Oracle is the only binding document on licenses.  Be very familiar with it as well as all documents that is references to be sure you understand your license issues.  Just like Sarbanes Oxley includes us as DBAs, the Oracle license agreement gives us responsibility to uphold the license.  This makes knowing these agreements critical in our role.
      • In the event of a licensing audit, make the corporate attorney the point person for all contact with Oracle.  It is very easy to waive rights in the audit by running scripts without approval from the attorney. 
        • Remember that EBS is licensed by named user and that covers runtime licenses for the apps tier components (once you customize, the runtime licenses may not cover you).   Audits love to ask for FND_USER and attempt to get you to buy licenses for all unexpired entries.  Since a single user can have an unlimited number of accounts, FND_USER is worthless for judging EBS licenses (in general employee count plus some number to cover consultants is a pretty good ceiling on the number of EBS licenses required)
        • We all add custom pieces that make the database be licensed directly.   Don’t try to get away with the EBS run time license of the database.
• Use username oracle with group dba (and probably oinstall)  for both database and applications binaries.  Historically applmgr was used for the applications, but that dates back to single system installs which were the norm through Oracle Applications 10.7 or so.  Since Oracle Applications was renamed E-Business Suite, using oracle on both tiers has become the standard.  Make sure the uids and gids are common on all systems.
  • Companies not using virtualization or minimizing CPUs in a cloud environment may do multiple environments on a single node.  In this case consider something like oradev, oratest, etc. to help keep separation between the environments.  You will want to keep a common group so the shared files like /etc/oratab can be read and written by all relevant usernames, but use separate groups for allowing os authentication, writing in oracle home and datafile locations, etc.
• Pick a naming standard for the filesystem.  There are multiple accepted standards for this.  It really does not matter what you pick as long as it is a standard across all your systems and that it does not introduce performance issues because of OS limitations.  Part of the goal with this should be to make it easy for someone else to quickly understand the layout in the event that something happens to you.
  • Examples for the database tier
    • /opt/oracle
      • /opt/oracle/SID/binaries
        • then specific homes, e.g. /opt/oracle/SID/binaries/19.0,0
        • /opt/oracle/binaries/SID could also be used
      • /opt/oracle/SID/logs
      • /opt/oracle/SID/flash_recovery_area
      • /opt/oracle/SID/datafiles
    • /
      • /oracle/
        • then specific homes, e.g. /oracle/SID/19.0,0 or /oracle/19.0.0/SID
      • /oradata/SID
        • datafiles and redo
      • /oraarch/SID
        • flash_recovery_area
    • /uXX
      • /u01/app/oracle
        • /u01/app/oracle/product/19.0.0
        • or /u01/app/oracle/product/19.0.0/dbhome1
      • Possibly, /u01/app/oracle/diag
        • put at top instead of in ORACLE_HOME
        • Same thing can apply to audit
      • /u02/flash_recovery_area
      • /u03/oradata/SID and so on
      • Assumption is each /uXX is a separate filesytem
    • hybrid
      • /opt/oracle or /oracle instead of /u01/app/oracle
        • If you always have the binaries in /u01/app/oracle it is really a fixed named anyway
      • /uXX for the the others
  • Pluggable datafiles can either be placed in a subdirectory of the CDB or in directories based on the name of the PDB (I suggest putting PDB$SEED under the CDB in any case).
    • /oradata/CDB_SID/PDB or
    • /oradata/CDB_SID, /oradata/PDB
  • If your datafiles are on the filesystem (as opposed to ASM), you need to pay attention to read and write limitations on your filesystem.
  • Separate the underlying luns and mountpoints so that in the event of a filesystem corruption, you do not lose all copies of control files, mirrored redo, datafiles, etc.

Database Naming Conventions

• Starting with 19c we have both a container and a pluggable database.  The limitation on the name is still 8 characters.
• Since we are limited to a single pluggable, the CDB and PDB names should be linked.  The standard that seems to be winning is to prefix the PDB name with a C.
  • This means that the name of the pluggable is limited to seven characters
    • For example PDB names of DEV, TEST, PROD, etc. with CDEV, CTEST, CPROD, etc. for the corresponding CDBs
  • A different standard prefix such as CDB would limit the length of pluggable names even more which could be an issue if you have multiple products with different environments.