Oracle Database Best Practices

<- Articles

Overview

The practices described here ensure maximum efficiency for Oracle Database administrators. They also ensure optimum database reliability and performance. If implemented they can have a significant effect on your organization's bottom line. These are the ingredients of a successful Oracle environment. If you manage Oracle DBAs and\or Oracle databases make sure you understand these principles.

Keep Your Databases Simple and Standardized

Simple database architectures are easier to implement and more stable. SIMPLE = MATURE. Only use non-vanilla features of an Oracle database if there is a compelling business requirement.

Uncompromising Dedication to Core Tasks

In some environments projects can evolve into to an endless stream where sight is lost as to the most important database tasks. To ensure business\mission continuity your DBA resources need to attend to these core tasks before all others. Not allocating adequate resources to these is a mistake found in those environments where downtime and loss of revenue are acceptable. It is a good idea to review your database shop's workload routinely to make sure this foundation remains in place.

Core System DBA tasks:

• Database backups.
• Daily checks and routine monitoring of local and remote databases.
• Data synchronization technology required support (Data Guard, Streams etc.).
• Oracle database product patching.
• Disaster recovery testing.

Use Disaster Recovery as the Common Denominator

Disaster Recovery should be the first and last thing to be considered in any Oracle architecture. Any feature, method or decision needs to be weighed against what it will take to successfully recover your databases from a disaster. The simpler a database is, the easier it is to recover from a disaster.

Treat an Oracle Database as an OS, not an Application

Even if you have been programming for 20 years, when it comes to Oracle, you need to think of it as if it is an operating system, because in fact it is. Oracle is functionally an operating system for databases. This means that the actions you take in the administration of a database will produce nothing less than 100% uptime. Furthermore, all downtime should be planned and scheduled.

Do Not Base Any Decisions on Sales Information

Separate sales ideology from reality. Find out the truth about how any Oracle technology actually works. Do this by creating test cases that prove the technologies and methods will work in your environment. Also, by speaking to numerous and diverse industry experts you can determine if any given technology would be truly practical and sustainable in your environment.

Use New Hardware

The main reason complex Oracle features and methods are used is to compensate for inferior hardware. The man-hours spent supporting complex architectures and the database's lack of performance are monetary black holes for a company. To avoid this common pitfall, make sure that the systems which host your Oracle databases are the first priority when it comes to your system hardware upgrade schedule.

 Important Caveat
 No amount of hardware can overcome an application if it is written badly enough.

Ensure Your Disk I\O Performance is Adequate

Ideally Oracle requires 1ms read and 5ms average write times or better for production environments. If your SAN or RAID system cannot achieve this your clients will experience degraded performance and perhaps loss of service events. Make sure those that make decisions on Oracle database hardware purchases know this. Never assume your disk I/O is OK because it is new hardware. Security infrastructure, caching, disk controllers, encryption can all factor in in influencing this.

Ensure Your Backup Policy Meets Your Customer's RTO

Working with the customer, determine the Recovery Time Objectives (RTO) for all your databases. From this you can successfully architect a backup policy that will succeed. Concerning the architecture chosen, once again remember: SIMPLE = MATURE.

It is very wise to backup your database using more than one method, i.e. use both RMAN and routine exports as required. This not only gives you redundancy but optimum data restoration options. Disaster recover to fine grained schema object restoration without having to perform a full RMAN restore on another system just to extract the data you need.

Ensure Database Standards are Mandated from Above

"Standards" are simply practices in your environment that duplicate success. Adherence to standards and up-to-date documentation has a much greater impact on DBA efficiency than the use of bleeding edge or glamour tools. Standardization ensures that new DBAs will get up-to-speed fast, and that skilled DBAs will be more efficient. There is also far less to document! Standardization is not possible unless the IT manager or the equivalent sees the value and is committed to it.

Store Documentation in One Easy to Access Place

In some computer environments, documentation exists all over the physical and virtual landscape! When a new DBA comes on board, it seems like every common task must be reinvented, because there is no one place where all documentation can be found. You need to make creating and changing your documentation easy. To achieve this have all the documentation for your databases and their support in O-N-E (1) place.

Important Caveat

A corporate centralized location that has a slow connection and\or requires
a multitude of navigational efforts is as good as useless. 

Why? Documentation will be out of date if it exists at all.

One solution to such a toilsome environment is to maintain documentation 
locally and then periodically send a packaged update (.pdf etc.)
to the corporate site to keep the bean counters happy.

Hint: If the word "toilsome" can be used to describe your environments
documentation efforts management may not be committed to it being
up-to-date.

Eliminate Careers in Complexity

A "career in complexity" is another way of describing how a technologist uses overly complex or exotic practices without sound business requirements. In some cases it is nothing more than resume building. Questionable practices guarantee consistent negative phenomena. In the presence of such practices, new more impressionable DBAs may feel that this is what being a DBA is all about. In advanced cases an entire IT Department's reputation can be adversely affected.

Question Authority

Oracle is constantly changing. Just because it worked a certain way in an earlier version does not mean it works that way now. As Tom Kyte says, "Things change, expect that. Everything can (and should) be proven. Make sure you know what you are talking about. Use the scientific method or you risk losing credibility."

Dedicated Database Server

To build your Oracle database on a rock solid foundation, make sure its dedicated solely to the database. This means not installing other Oracle software (like OAS) or having the server perform any other roles (application server, file server, jumpstart server etc.). This is also a security compliance requirement.

Use Modern OFA (Optimal Flexible Architecture)

Ensure the database file system layout you use is standardized on all your database systems. In Oracle this is done via OFA.

Optimally at this point you should use Oracle Managed Files (OMF). Among all its other benefits it enforces OFA. If you cannot use OMF a good example of manually implementing OFA is below:

-- SAN\RAID Example

c:\oradata\DB01\arc    Archived Redo Logs 1 
o:\oradata\DB01\arc    Archived Redo Logs 2 

c:\oradata\DB01\ctl    Control File 1
o:\oradata\DB01\ctl    Control File 2

c:\oradata\DB01\fra    Fast recovery area.

c:\oradata\DB01\rdo    Redo Log Files 1
o:\oradata\DB01\rdo    Redo Log Files 2

c:\oradata\DB01\dbf    Core system tablespace datafiles (SYSTEM, SYSAUX, TEMP, UNDO ...).
o:\oradata\DB01\dbf    User data tablespace datafiles.

r:\exports\DB01        DataPump export files.
r:\rman\DB01           RMAN backup files.

-- JBOD (just a bunch of disks) Example

c:\oradata\DB01\arc    Archived Redo Logs 1
c:\oradata\DB01\ctl    Control File 1
c:\oradata\DB01\dbf    Core System Tablespace Datafiles (SYSTEM, SYSAUX, TEMP, UNDO ...)
c:\oradata\DB01\fra    Fast recovery area.
c:\oradata\DB01\rdo    Redo Log Files 1

d:\oradata\DB01\arc    Archived Redo Logs 2
d:\oradata\DB01\ctl    Control File 2
d:\oradata\DB01\rdo    Redo Log Files 2

f:\oradata\DB01\dbf    User data tablespace datafiles.
g:\oradata\DB01\dbf    User data tablespace datafiles.
h:\oradata\DB01\dbf    User data tablespace datafiles.
...

r:\rman\DB01           RMAN backup files.
x:\exports\DB01        DataPump export files.

The JBOD method is becoming less and less common all the time.

ASM Sidebar

For non-RAC databases on most modern SAN systems there is little to be gained in using ASM except added complexity. However, ASM implemented in a JBOD environment can be very effective.

According to Donald K. Burleson, "The advent of RAID 0+1 has made Oracle-level striping obsolete since RAID 0+1 stripes at the block level, dealing out the table blocks, one block per disk, across each disk device. RAID 0+1 is also a far better striping alternative since it distributes the load evenly across all of the disk devices, and the load will rise and fall evenly across all of the disks. This relieves the Oracle administrator of the burden of manually striping Oracle tables across disks."

Exceptions would be if your RAID/SAN consistently experiences writes that take longer than 5ms and reads that take longer than 1ms. Some SAN vendors advise the use of ASM with Oracle to get around their hardware short comings. These tend to be SAN/NAS systems designed for historical data storage, i.e. not designed for exhaustive real-time file access (like an Oracle database).

ASM Misconceptions

There are some misconceptions with modern ASM. One huge one is mirroring is required.

Otherwise you can rely on your hardware SAN for mirroring. This both simplifies your ASM architecture and provides the optimal performance to ASM as a RAID type solution.

Here is what the 19c Oracle docs say on this:
Oracle ASM mirroring runs on the database server and Oracle recommends to off load this processing to the storage hardware RAID controller by using external redundancy .
Source of above here

Another misconception is that ASM mirroring will help you survive all disk issues. Nope! Do not rely on ASM exclusively for this. I have seen instances first hand where a vendor changed OS level properties and ALL the ASM disks were instantly corrupted requiring a complete RAC re-installation. Luckily I had a recent full database export and RMAN backup. ;-)

<- Articles