Chapter 2. Maintaining Oracle Standards

As a DBA you will be expected to draft and control different standards, as they pertain to an Oracle database. Some of the best standards are the ones that have a wide adoption rate, and which are easily understood and well-documented. Standards are intended to grease the various gears of a team so that they work together with less friction. Standards will also make a DBA's life easier in the long run, safer for all those involved, and more efficient, because time isn't spent reinventing or rewriting a process that wasn't based on a standard in the first place.

This chapter outlines several standards for the major tasks that most DBAs are expected to perform: installation, configuration, and the maintenance of Oracle software. Expect the standards that you adopt to change gradually over time as technology improves and to reflect changes within your organization.

Adapting to constant change

You can have more than one ORACLE_HOME for every node, primarily for migration projects that will be spread over an extended period of time. There is an inherent risk in running multiple ORACLE_HOMES, in that you may mistakenly use the wrong ones. It is my personal recommendation that the ongoing risk is worth it when using the Step-Ordered Approach to Migrating, because it reduces the overall downtime (see Chapter 7).

An ORACLE_HOME consists of installed binary files along with Oracle-supplied scripts. The database comprises memory structures, background processes, control files, parameter files, data files, and temporary and undo files. There are other types of ORACLE_HOME(s) that contain ORACLE installed software, as each of them is only an environmental variable pointing to a specific disk location. Other types will have other names like: CRS_HOME or AGENT_HOME that may be identified as an ORACLE_HOME in the Oracle documentation. This means you can have multiple...

Oracle's Optimal Flexible Architecture (OFA)

In the following section, you will find a small summary of the OFA standard(s) that were written by Cary Millsap. His article titled Oracle for Open Systems was first published in 1995. It is still used today and is widely adopted on Unix systems by DBAs, no matter the database size. This standard that has been expanded and revised to embrace the newer hardware technologies, and the Automatic Diagnostic Repository found in 11g is in the installation guide of every operating system.

Note

You can find the original 1995 version at the following location:

http://method-r.com/downloads/doc_details/13-the-ofa-standardoracle-for-open-systems-cary-millsap.

1. Name the Unix mount points with this pattern /mountpoint+numbered string and start numbering with a left-padded zero to keep the list in numerical order. For example, /u01, /u02, /u03.

2. The Oracle operating system account that owns ORACLE_HOME with a home directory of /mountpoint/directory/user. For example...

Automating day-to-day tasks

Options to automate the DBA tasks mentioned in the previous chapter include some of the following:

• DBMS_SCHEDULER: Oracle-supplied PL/SQL package that comes preinstalled

• OS scheduling commands: Certain tasks such as monitoring file space usage and removing old trace/log files are often scheduled using something like Unix cron or the Windows at command. The best options include tasks that need to run no matter the database state—down, up, or mounted.

• OEM Grid Control Intelligent Agent: Requires installation and configuring to run OS type commands. A very useful Enterprise-wide monitoring tool but quite an investment in time to configure and maintain for multiple servers.

Let's look at an example task and apply it to each of the three types of automation mentioned above: Monitoring the Database Alert Log.

DBMS_SCHEDULER

DBMS_SCHEDULER, along with the power of PL/SQL, provides a mechanism for automating some of the daily tasks mentioned earlier in this chapter. Using...

11g Diagnosability Framework

The Oracle 11g Database includes a full suite of diagnostic tools and advisors that are at least partially integrated with My Oracle Support (MOS), automatically uploading metrics via the Oracle Configuration Manager. This new diagnosability infrastructure includes the monitoring of the RDBMS, Automatic Storage Management (ASM) , Oracle Call Interface (OCI), SQL*Net, and Oracle Application Server 11.1 products.

This new version of Oracle introduces the Automatic Diagnostic Repository (ADR), which is a flat file structure containing all alert logs, core files, trace files, and incident information. The ADR_BASE = $ORACLE_BASE, which is controlled by the database diagnostic_dest parameter, replaces background_dump_dest , core_dump_dest , and user_dump_dest . The entire ADR repository can be moved to a different location than the initial install, at the same time resetting $ORACLE_BASE. Just removing or moving the directories will not disable the ADR. The TNS components...

Environmental variables and scripting

Unix scripting will be one of the most powerful tools in your arsenal and only with constant use will you become proficient. Your standards for scripting need to produce code that is robust, modular, consistent, and scalable. This book won't cover everything you need to know about scripting. There are many excellent lessons available from your operating system vendor. See the following documents for more information specific to how Oracle products behave in a Unix environment:

• Note: 131207.1 Subject: How to Set Unix Environment Variables

• Note: 1011994.6 Subject: How do Applications Act in Different Unix Shells.

• How to Integrate the Shell, SQL*Plus Scripts and PL/SQL in any Permutation? [ID 400195.1] (Excellent document)

Usually cron is used for scheduling in Unix and the AT command with Windows.

For Oracle utilities that run outside the database (or which must only run when the database is down), Unix shell scripting is best used. A well-written script...

Reducing operating system differences with common tools

When your enterprise environment contains several different operating systems and database versions, it is best to have tools that work across scopes. We have already mentioned scripting as well as using DBMS_SCHEDULER and PL/SQL. There are also several software packages that offer GUI interfaces for real-time monitoring capability.

Oracle has a product called Grid Control (GC); it is the Enterprise Manager for the Enterprise. You can install a standalone version of Enterprise Manager in each database (called Database Control) or use GC with a centralized repository for all of your Oracle products. Grid Control is a free product from Oracle, the Management Packs have an additional license cost. Most customers purchase the DIAGNOSTICS & TUNING to take advantage of the extensive performance tuning features found in the Automatic Workload Repository, and not just for the OEM Interface.

The trade-off is a superb enterprise-wide tool,...

Configuration management, release management, and change control

One of the largest changes to Oracle is the recent acquisition of several other software lines and technologies. Oracle has combined all of these technologies and customers under a single support site called My Oracle Support at http://support.oracle.com, effective from Fall 2009. Along the way, Oracle also completely redesigned the interface, making it flash-based in order to provide a personalized GUI.

To take full advantage of the personalization features, you will need to install a free utility on each node and each ORACLE_HOME you would like to monitor. The following paragraphs outline several reasons for use and suggestions for getting started. Please review and discuss with the management the security implications of uploading critical information to a support website before proceeding.

Configuration management

Are you the only Oracle DBA in your company? How do you provide disaster recovery and redundancy for personnel...

Where, when, and who to call for help

Okay, now that you know the major responsibilities as a DBA, when should you ask for help? When would you know something is wrong? One issue that every DBA dreads is when end users complain that the database is slow. It doesn't matter if you are sitting in your office and the database seems to be running perfectly well. Every script you run reveals no issues or distinguishable slowness. So what are those end users talking about? If the end users have an issue, then you now have a problem. And that particular problem is called response time; this subject will be explored further in Chapter 8.

Components (at several levels of the technology stack) that slow things down for the database can include the network, operating system, application servers, and of course hardware-specific problems. Start an SQL trace to help come up with an error message that will give more clues to the problem. Please don't assume it is someone else's problem.

If you encounter corruption...

Summary

We looked at many things related to implementing standards across the enterprise in this chapter. Let's take a moment to run through them.

Multiple ORACLE_HOME(s) and multiple databases on multiple servers can be kept under control by maintaining consistent standards across the enterprise. These standards include both OFA and the methods used to automate DBA tasks. Don't let small mistakes keep you squirreled away in your office manually fixing problems that can be avoided in the first place.

Start off by writing your own scripts. Feel free to search on My Oracle Support or the Internet when you need some inspiration. But don't just implement what someone else has written; change it slightly to make it yours. Keep working on scripting as and when you have time.

This chapter contained an introduction to all of the latest manageability features found in 11g Diagnosability Framework. Several of the newer features are an additional licensed option, which are available as a link on the OEM...