Oracle Best Practices
Oracle Database Tips by Donald Burleson
Also see my notes on
server room standards.
Following Oracle best practices is a critical
management goal for many reasons:
Contrary to an
Oracle "best practice", an Oracle Worst Practices are actions (or
non-actions) that cause poor performance and create excessive
management overhead, essentially a non-standard approach to Oracle
Areas of Oracle best practices
many areas of Oracle best practices, covering architecture best
practices, DBA best practices and Oracle developer best
Oracle Best Practice: Instance
There are many
compelling benefits to having multiple instances on a large
scalable server (Oracle
instance consolidation), all part of the
of mainframe computing:
Oracle Best Practice: Proper change
control - It
is a best practice to fully test all production changes,, first
in a TEST instance, and later in a QA instance, testing all
possible scenarios before going into production.
best practice to ensure optimal SQL execution in production is to provide an
adequate database for your developers. Remember, you can
export your production schema statistics to make your development
system look like production.
Practice: Enough testing instances - Many
Oracle shops keep fours environments, DEV for development, TEST for
unit testing, QA for pre-production testing, and PROD for
production. Any less
and you may risk unintended side effects.
Practice: Performance tracking - With STATSPACK
(free) and AWR in Oracle10g, there is no excuse for not tracking
your database performance. STATSPACK and AWR provide a great
historical performance record and set the foundation for DBA
predictive modeling. See my book
Oracle Tuning: The Definitive Reference for details on Oracle
monitoring best practices.
Practice: Security management infrastructure- Oracle offers
a host of access control mechanisms (grants, roles, VPD) yet it is
amazing how many shops have giant security holes. See the book "Oracle
Privacy Security Auditing" for Oracle security best practices.
Practice: Standardized external environments
is the worst of worst practices where every database uses different
shells, different aliases and non-standard file locations.
Oracle best practice - audit
changes to initialization parameters.
Tracking & auditing changes to your init.ora parameters
Rampant author Brian Peasland offers these Oracle best practices:
- Maintain your database on current releases.
Today that means you should be on either 22.214.171.124 or 126.96.36.199. If
the former, you should already have a plan in place to get to
188.8.131.52 before free extended support for 184.108.40.206 expires early
next year. Too many DBAs let their version lag behind for a
variety of reasons. They later find upgrades are more painful than
if they had remained current. Plus they often end up engineering
solutions that the new version provides as a new feature.
If you're on 12c, you could probably add lots of new features that
can make your life easier.
- Start using Unified Auditing.
- Use IDENTITY columns or sequence NEXTVAL as default column
values to populate that synthetic key column much faster than the
old trigger selecting NEXTVAL.
- In Memory columnstore.
- Get the Diagnostics Pack and Tuning Pack for your
environments. They are not cheap but they sure do make
your life a lot easier. Many people who won't buy these because of
the cost do not realize the they end up paying for themselves in
about two years (your mileage may vary).
- Learn how to
create a testbed with Virtual Box on your
workstation or laptop. There is no excuse any more for
not having a testbed at your disposal. Even in many corporate
environments with private cloud (or similar) implementations and
Oracle VM or VMWare dominating in the enterprise, it may not be
easy to standup an Oracle database just for a small
proof-of-concept project. Too many times, it becomes political
infighting within the IT department. But if you do this on your
workstation, you'll be off and running and have total control of
- Get involved with social media. Follow blogs
of Oracle experts, follow them on Twitter. Participate in the OTN
and/or MOSC communities. There will be no better way to grow your
career then when you are interacting with other Oracle
professionals on a daily basis.
- Learn another database system. When I started
this career, it typically meant another RDBMS like SQL Server,
DB2, etc. Now, it can also mean MongoDB, Hadoop, and others that
are not relational in nature. When learning these other
systems, try to figure out how each is different an how each is
the same. This will help solidify your overall database knowledge.
For example, from a database theory perspective, we know that
relational databases generate transaction logs. But how each does
- If you're still using SQL*Plus daily, take a look at SQL
Developer and how much of a timesaver it can be. And get a copy of
SQLcl as well. SQL*Plus is still great and should still be in the
DBAs arsenal though.
These are just a few of the top Oracle best practices.