Oracle Database 11g – Underground Advice for Database Administrators — Save 50%
A real-world DBA survival guide for Oracle 11g database implementations with this Oracle book and eBook
This article by April Sims, author of the book Oracle Database 11g – Underground Advice for Database Administrators, gives an introduction to all of the latest manageability features found in Oracle 11g.
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.
Are you the only Oracle DBA in your company? How do you provide disaster recovery and redundancy for personnel in that situation?
MOS has a tool that provides an Automatic Document Repository (my words) called Oracle Configuration Manager (OCM). The real purpose of this tool is to manage all of your configurations (different systems, servers, databases, application servers) when dealing with Oracle support.
It is automatic in the sense that if you are out of the office, temporarily or permanently, the system configurations are available for viewing by anyone with the same Oracle Customer Support Identifier (CSI) number . The information is also available to Oracle support personnel. The repository is located on My Oracle Support. The systems are for you to choose, whether you want to only include production and/or non-production systems.
What information does OCM collect and upload? It contains extensive hardware details, software installs (not just Oracle products), databases, and Oracle application servers. There is enough information to help in recreating your site if there is a complete disaster. The GUI interface allows managers and other IT personnel to see how nodes and applications are related and how they fit into your architectural framework. The information can only be updated by the upload process.
Using OCM in disconnected mode with masking
There is sensitive information being collected from the OCM tool. If you are employed by an organization that doesn't allow you to reveal such information or allow direct access by the servers to the Internet, there are steps to improve the security of this upload process. This section is highly recommended to be reviewed before enabling OCM. You must know what types of information are there and how that information is used before enabling uploading capabilities to a support website.
To disable the collection of IP and MAC addresses, you add the following entries to the $ORACLE_HOME/ccr/config/collector.properties file.
To disable the collection of network addresses, add the following entry:
To disable the collection of the MAC address, add the following entry:
The OCM collector collects the schema usernames for databases configured for configuration collections. The collection of this information is filtered or masked when ccr.metric.oracle_database.db_users.username is assigned the value of 'mask' in the $ORACLE_HOME/ccr/config/collector.properties file. The default behavior of the collector is to not mask this data.
MOS customers may request deletion of their configuration information by logging a Service Request (SR) indicating the specific configuration information and scope of the deletion request.
Disconnected mode is carried out with something called Oracle Support Hub, which is installed at your site. This hub is configured as a local secure site for direct uploads from your nodes, which the hub can then upload to MOS through the Internet. This protects each of your nodes from any type of direct Internet access.
Finally, there is a way to do a manual upload of a single node using the method outlined in the MOS document 763142.1: How to upload the collection file ocmconfig.jar to My Oracle Support for Oracle Configuration Manager (OCM) running in Disconnected Mode. This is probably the safest method to use for OCM. Run it for a specific purpose with appropriate masking built-in and then request the information to be deleted by entering a SR request.
These tips came from these locations as well as the OCM licensing agreement found on MOS:
The Oracle Support Hub can by found on the OCM Companion Distribution Disk at: http://www.oracle.com/technology/ documentation/ocm.html.
Each node with an installed OCM collector can be automated to upload any changes on a daily basis or interval of your choice. OCM is now an optional part of any of the 10.2.0.4+ Oracle Product GUI installs. The OCM collector is also found by logging into MOS and selecting the collector tab. It is recommended to use at least the 3.2 version for ease of installation across the enterprise.
Be aware! The collector install actually creates the Unix cron entry to automatically schedule the uploads.
Mass deployment utility
The OCM collector utility has been out for over a year, but a recent enhancement makes installation easier with a mass deployment utility. On the MOS collector tab, find Configuration Manager Repeater & Mass Deployment Tools and the OCM Companion Distribution Guide.
The template file required to install the collector on multiple servers is in csv format, which you may find difficult to edit using vi or vim. The template doesn't have an initial entry and the length is wider than the average session window. Once the first entry is filed out (try using desktop spreadsheet software), editing this file with a command-line tool is easier. It has a secure password feature so that no password is stored in clear text. You can enter a password at the prompt or allow the password utility to encrypt the open text passwords in the template file during the install run.
Running the utility runs very quickly from a single node that has SSH access to all entries in the template. It auto detects if OCM was already installed and bypasses any of those entries. You may encounter an issue where the required JAVA version is higher than what is installed. Other prerequisites include SSH on Linux or CYGWIN for Windows.
A downside is that all configuration information is available to everyone with the same CSI number. In a small IT shop, this isn't a problem as long as MOS access is maintained properly when personnel changes. Providing granular group access within a CSI number to your uploaded configurations is a highly anticipated feature.
As a DBA you must be consistent in the different aspects of administration. This takes dedication to keep all of your installed Oracle products up-to-date on critical patches. Most DBAs keep up-to-date with production down issues that require a patch install. But what about the quarterly security fixes? The operating systems that your system admin is in charge of will probably be patched more regularly than Oracle. Why is that the case? It seems to take an inordinate amount of effort to accomplish what appears to be a small task.
Newer versions of Oracle are associated with major enhancements—as shown by the differences between versions 11.1 and 11.2. Patch sets contain at least all the cumulative bug fixes for a particular version of Oracle and an occasional enhancement as shown in the version difference between 184.108.40.206 and 220.127.116.11. Oracle will stop supporting certain versions, indicating which is the most stable version (labeling it as the terminal release). For example, the terminal release of Oracle 10.1.x is 10.1.0.5, as that was the last patch set released. See the following document on MOS for further information on releases—Oracle Server (RDBMS) Releases Support Status Summary [Doc ID: 161818.1].
In addition to applying patch sets on a regular basis (usually an annual event) to keep current with bug fixes, there are other types of patches released on a regular basis. Consider these to be post-patch set patches. There is some confusing information from MOS, with two different methods of patching on a quarterly basis (Jan, April, July, Oct.)—Patch Set Updates and Critical Patch Updates. CPUs only contain security bug fixes. The newer method of patching—PSU—includes not only the security fixes but other major bugs. These are tested as a single unit and contain bug fixes that have been applied in customers' production environments.
See the following for help in identifying a database version in relationship to PSUs:
MOS Doc ID 850471.1
1st digit-Major release number
2nd digit-Maintenance release
3rd digit-Application server release
4th digit-Release component specific
5th digit-Platform specific release
First PSU for Oracle Database Version-10.2.0.4.1
Second PSU for Oracle Database Version-10.2.0.4.2
While either PSUs or CPUs can be applied to a new or existing system, Oracle recommends that you stick to one type. If you have applied CPUs in the past and want to continue—that is one path. If you have applied CPUs in the past and now want to apply a PSU, you must now only apply PSUs from this point to prevent conflicts. Switching back and forth will cause problems and ongoing issues with further installs, and it requires significant effort to start down this path. You may need a merge patch when migrating from a current CPU environment, called a Merge Request on MOS.
Important information on differences between CPUs and PSUs can be found in the following locations. If there is a document number, then that is found on the MOS support site:
Doc 864316.1 Application of PSU can be automated through Deployment Procedures
Doc 854428.1 Intro to Patch Set Updates
Doc 756388.1 Recommended Patches
Upgrade Companions 466181.1, 601807.1
Error Correction Policy 209768.1
Now to make things even more complicated for someone new to Oracle; let's discuss recommended patches. These are released between the quarterly PSUs and CPUs with common issues for targeted configurations . The following are targeted configurations:
- Generic—General database use
- Real Application Clusters and CRS—For running multiple instances on a single database with accompanying Oracle Clusterware software
- DataGuard (and/or Streams)—Oracle Redo Apply technology for moving data to a standby database or another read/write database
- Exadata—Vendor-specific HP hardware storage solution for Oracle
- Ebusiness Suite Certification—Oracle's version of Business Applications, which runs on an Oracle Database
Recommended patches are tested as a single combined unit, reducing some of the risk involved with multiple patches. They are meant to stabilize production environments, hopefully saving time and cost with known issues starting with Oracle Database Release 10.2.0.3—see Doc ID: 756671.1.
eBook Price: $29.99
Book Price: $49.99
DBA issues with patching
Here is a list of the most frequently experienced issues with the patching process for Oracle products:
- What patches should be applied?
- No advance notification if there is a conflict.
- Patch conflicts that cause rollbacks.
- Merge requests for patch conflicts.
- Troubleshooting patch application failure.
- Conflicting and resolving differences in CPUs and PSUs.
- Should I apply the recommended patches?
- Am I the first person to deploy this patch?
- Documenting versions and patches across all systems.
Oracle recently added a new functionality to their support website called Patch Plans. It requires the 3.2+ version of the MOS Configuration Manager Collector (which is the collector component of OCM). It was just mentioned a few sections earlier and is available for download from the My Oracle Support website. Once the collector is installed and your system configuration information is uploaded to MOS, you can create a patch plan.
The following screenshot contains my list of patch recommendations for all of the ORACLE_HOME(s) that have a collector installed and configured for uploading to Metalink.
There is an alternate login page for non-flash users— http://supporthtml.oracle.com, but this will not include any of the personalization features such as Patch Plans or uploaded configuration details.
There is one patch plan associated with the patch number 8534378, as shown by the folder icon. I purposely moved the sliding components of this dashboard so that the sensitive information is covered. You can also right-click to select a patch to add to a new plan or existing plan, download, or suppress this patch. The existence of a patch plan is indicated by the folder icon next to a certain patch, as seen in the very first entry of the list of patch recommendations. If you don't have any configurations, you will not see a list of patch recommendations.
The next screenshot is the full screen of a selected patch 8534378, which is the CPU for July 2009. Notice on the right the Coming Soon! banner, which is reserved for the community functionality that wasn't available at the time this article was written. This area of MOS will include end users' feedback on this particular patch and will also list the number of times it was downloaded over the past few months.
Applying a patch without integrating MOS with OCM
Here are generic instructions for applying a patch without MOS and OCM:
- Download the patch, PSU, CPU, or patch set.
- Create a new ORACLE_HOME for testing if alternating between different ORACLE_HOMES. Otherwise, all Oracle software running in the home to be patched will have to be shut down.
- Install if there aren't any conflicts. If there is a conflict, then the patch is rolled back.
- Check if any key patches are also rolled back.
- Submit a merge request on MOS for key patches.
- Download the merge request to start the patch process again.
Using the new patch plan functionality with OCM installed and uploaded to MOS
- Patch Plan automatically checks for missing prerequisites. This functionality has not been shown to be consistent across the board yet.
- It checks any conflicts before downloading.
- It request a merge if required before downloading.
- It will also check recommended patches for your configuration.
The following documents from MOS contain the manual methods for determining whether a patch will conflict using the Oracle supplied opatch utility. If your organization doesn't want to install or configure the OCM collector for security reasons, then the following is a viable option for determining patch conflicts.
Refer to How to find whether the one of the patches will conflict or not? [Doc ID: 458485.1]. Here are a couple of examples of how to use this command-line utility outlined in the document:
opatch prereq CheckConflictAmongPatchesWithDetail -phBaseDir $ORACLE_
opatch prereq CheckConflictAgainstOHWithDetail –phBaseDir $ORACLE_
To keep from constantly writing the same pieces of code over and over again and reconciling differences between servers, a common storage area for DBA code needs to be established. It is most likely that your programming staff already has access to a code repository. The two largest open source versions are CVS (Open Source Version Control) and Subversion (SVN). They are both equally efficient and powerful tools with slightly different features. If version control software is already deployed in your enterprise, then ask for a repository for database administration and limit the access to that repository.
Do not store passwords in a repository that means 'currently in use' or 'previously used'. Find an enterprise-wide password utility such as KeePass that will store encrypted passwords for all of the servers and databases. Integrate SSH PUTTY with KeePass so that it launches a terminal window without having to type the password. See the following blog for additional information on other password utilities like KeePass: http://princessleia.com/journal/?p=1235.
One way to use version control software is to check out the repository on each node and use the command-line version on that node. While you can check out code on your desktop, you will still have to shuttle any code changes via SSH or SFTP between your desktop and the servers, which adds another step to the process. There are also issues when editing the files with a Windows utility; it inserts the carriage return character that has to be removed to run on a Unix box. The Unix utility that removes special Windows characters is called dos2unix.
dos2unix removes any links (symbolic or hard) that a file currently has. This will affect how the file is referenced in the $PATH, so the links must be recreated. See the Unix help pages for ln, which is done by issuing a man ln. This command opens up the OS documentation for the ln command on Unix.
There is a way to eliminate the extra shuttling of code while keeping the GUI interface by using the open source ECLIPSE on the server. It is most closely associated with Java programming as an Independent Development Environment (IDE). There are additional plugins, software extensions, and code additions for almost any programming you would need to accomplish. Check out the Data Tools Platform Project for downloads specific to database development.
Version Control Software will take some time to learn to use safely. It can easily overwrite or remove critical pieces of code. So work closely with the code repository system admin to make sure there are backups! A safer way to use the repository might be to check it out in a temporary staging area and migrate the code safely to a protected code tree.
In this article, we learnt about configuration, release and change management with Oracle.
If you have read this article you may be interested to view :
- Oracle's RDBMS SQL Command Dump Block
- Oracle: When to use Log Miner
- Oracle: RDBMS Log Miner Utility, FRA, and AUM
- Oracle: Environmental Variables and Scripting
eBook Price: $29.99
Book Price: $49.99
About the Author :
April Sims is currently the Database Administrator at Southern Utah University and an Oracle Certified Professional: 8i, 9i, and 10g with a master's degree in Business Administration from the University of Texas at Dallas. Involved as a volunteer with the Independent Oracle Users Group for over 7 years, April is currently a Contributing Editor for the IOUG "SELECT" Journal. April is an annual presenter at Oracle OpenWorld, IOUG COLLABORATE, and numerous regional Oracle-related conferences.