Newsletters Archive - 2012

If you would like to know more about the Newsletter, please email me.

  Oracle Database Name Resolution with OpenLDAP — (21-August-2012)

Anyone who has ever worked with the Oracle Database is most likely familiar 
with the tnsnames.ora file. The tnsnames.ora file is a text file that contains 
client side definitions for net service names, sometimes call aliases, needed 
to log in to an Oracle instance. This provides local name resolution by mapping 
net service names to connect description information for Oracle instances; 
similar to how the local hosts file for an operating system maps machine names 
to IP addresses. The tnsnames.ora file is commonly found in its default 
location under the ORACLE_HOME/network/admin directory.

One of the advantages of using a local tnsnames.ora file is that it is easy to 
create and edit entries. This is especially true when there are only a few 
entries to maintain and the file only needs to be distributed to a small number 
of Oracle client machines. The primary disadvantage of local naming is when 
there are a large number of client machines on the network, say 100 or even a 
1000 clients, each required to have their own tnsnames.ora file. This problem 
gets compounded when frequent changes to the tnsnames.ora are required as a 
result of adding or moving database and the file has to be re-distributed to 
those clients.

A more efficient solution is to centralize the list of database names in a 
repository that every Oracle client can access. In the following article, I 
will demonstrate how to configure Oracle database naming in an LDAP directory. 
LDAP is for tnsnames.ora what DNS is for a local hosts file like /etc/hosts. 
Similar to how the local tnsnames.ora file works, when a client performs a 
lookup for a net server name in LDAP, it is given the appropriate connect 
descriptor information for the Oracle instance or service name.

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Oracle PL/SQL LDAP API Example — (16-August-2012)

The following guide provides PL/SQL source code that demonstrates how to use the 
Oracle PL/SQL LDAP API to search for People records in an LDAP directory. The 
LDAP directory is hosted on a Linux machine named 
configured with OpenLDAP Software release 2.3 and has been initialized with a 
base DN of dc=idevelopment,dc=info and organization units People, Group, and 
Hosts. The PL/SQL example searches for people based on a search filter. The 
attributes returned will be uid, cn, and loginShell.

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Configure System Authentication Using OpenLDAP on CentOS 5 — (15-August-2012)

When a user logs in to a Linux system, the username and password combination 
must be verified, or authenticated, as a valid and active user. A lot of times 
the information needed to authenticate the user is located on the local system 
through entries in the /etc/passwd and /etc/shadow files. Another option is to 
allow the system to defer user authentication to a user database on a remote 
system like, for example, an LDAP directory. This is a popular option as it 
allows administrators to centralize username and password information.

In the following guide, I will present the steps required to configure 
authentication for a Linux client through LDAP using Pluggable Authentication 
Modules (PAM) and Name Service Switch (NSS). The Linux client will be 
configured to access user information stored in an LDAP directory database such 
as username, UID number, GID number, home directory, login shell, and other 
user information that can be used to authenticate to the client system.

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Install and Configure OpenLDAP on CentOS 5 — (07-August-2012)

The Lightweight Directory Access Protocol (LDAP) is an application protocol 
used to access information in a directory over an IP network. Most information 
in a directory is organized with a hierarchical structure much like a file 
system. A directory can contain any type of data but most often consists of 
organizations, user accounts, address books, and other types of resources like 
files and devices on a network. You can even become adventurous and use LDAP 
directory naming to resolve SQL*Net service names for Oracle databases which 
allows storing net service names in a centralized LDAP-compliant directory 
server in place of a client-side tnsnames.ora file.

OpenLDAP Software is a free, open source implementation of the LDAP protocol 
developed by the OpenLDAP Project. Directory service is controlled by a 
stand-alone LDAP daemon (slapd) and the stand-alone LDAP update replication 
daemon (slurpd) when replication is configured. Propagating changes using 
replication falls outside the scope of this note and therefore will not 
include configuring the slurpd daemon.

The following guide presents the steps required to install and configure 
OpenLDAP Software on the CentOS 5 platform. This tutorial will also work for 
Red Hat Enterprise Linux 5 and Oracle Linux 5.

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Secure Database Passwords in an Oracle Wallet — (26-July-2012)

The practice of writing scripts to automate routine database tasks is 
commonplace. This can include database backups, ETL jobs, or any type of batch 
processing that requires database access without user interaction. These 
scripts are typically held on the filesystem which depend on OS file 
permissions to protect the security credentials needed to log in to the 
database. The challenge has been how to adequately hide or obfuscate the 
username and password and not expose them in clear text and causing a potential 
security breach. A widely used practice has been to rely on OS Authentication, 
but starting with Oracle Database 10g Release 2, a more simplified and scalable 
solution would be to use a Secure External Password Store. This approach 
provides a secure method to store database credentials and reduces risk to 
security policies because the usernames and passwords no longer need to be 
exposed in clear text. This also avoids the need for the DBA or other security 
administrators to share passwords with developers and other non administrator 
users needing access to the database.

The secure external password store uses a client-side Oracle Wallet to store 
one or more user name/password combinations. The wallet is encrypted using the 
3DES algorithm so the contents of the wallet are not readable. If the wallet is 
ever compromised, the database password for the user can be changed and a new 
wallet can be generated thus rendering the previous wallet unusable.

The following guide demonstrates how to configure and make use of an Oracle Secure External Password Store:

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Oracle OS Authentication — (16-July-2012)

The most common method for logging in to an Oracle database is by username and 
password. A database user is created by using the CREATE USER statement as 

CREATE USER username IDENTIFIED BY password;

This creates a database user, associated with a user schema, who can access the 
database and be authenticated by using a password with the CONNECT command as 

CONNECT username@database_service_name
Enter Password: password

The following guide provides an alternative to the username / password method 
by using OS Authentication. OS authentication allows Oracle to delegate control 
of user authentication for the database to the operating system. Although OS 
authentication has been widely used over the years, there are potential 
security risks to be aware of before implementing it in a production 
environment. In fact, when using Oracle Database 10g Release 2 or higher, a 
better and more secure approach uses a client-side Oracle Wallet which is a 
password store external to the database used to store Oracle login credentials.

OS authentication is a method which identifies users by the credentials 
supplied by the OS and then uses that information to allow authentication to 
the database without a password. These credentials can be the username and 
password supplied to the OS or digital certificates on the user's computer. A 
password is not required for a database connection because it is assumed the OS 
has already taken care of authenticating the user. One thing to keep in mind 
though is that database connections relying on OS authentication are only be as 
secure as the underlying OS.

This guide presents instructions on how to configure Oracle for OS 
authentication along with several examples on how to use it.

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Oracle Database Backup to Amazon Simple Storage Service (Amazon S3) on Linux — (09-July-2012)

The traditional method of relying on tapes for off-site backups has been a 
widely accepted best practice for many organizations. Today, however, new disk 
economics and the compelling price point offered by Cloud storage vendors has 
presented a new opportunity to make off-site backups more reliable, accessible, 
and secure. With the introduction of Oracle Secure Backup Cloud Module for 
Amazon S3, it is now possible to perform Oracle database backups to Amazon 
Simple Storage Service (Amazon S3) for off-site storage purposes. This new 
strategy also offers the ability to stream backups directly to the Cloud. This 
is especially appealing when performing a backup through a low latency network 
connection like Amazon EC2 instances (DBs within Amazon Cloud) or from your 
on-premise network to Amazon VPC using Amazon AWS Direct Connect service (DBs 
not within Amazon Cloud).

The following guide demonstrates how to install and configure the Oracle Secure 
Backup Cloud Module for Amazon S3 to backup an Oracle database to the Cloud 
using the direct streaming method. This can be performed from an Amazon EC2 
instance (off-premise) or from an Oracle database server running in your own 
data center (on-premise).

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Associate Elastic IP on Instance Startup - (Linux) — (03-July-2012)

Amazon Web Services (AWS) provides the ability to associate (map) a static IP 
address with a running EC2 instance through the use of an Elastic IP addresses. 
An Elastic IP address is a static IP address which you can allocate and assign 
to any one of your running EC2 instances either through the AWS Management 
Console or using the Amazon EC2 API Tools. By default, your account is limited 
to 5 Elastic IP addresses per region and only one Elastic IP address can be 
assigned to an instance. On a normal startup, every instance will come with a 
private IP address and a dynamic Internet routable public IP address. When an 
Elastic IP address is associated with an instance, it replaces the dynamic 
public IP address. Because an Elastic IP address is associated with your 
account and not permanently with a particular instance, you are able to quickly 
and efficiently mask instance or Availability Zone failures by associating the 
Elastic IP address to any surviving instance under your account. Associating an 
Elastic IP address to an instance is a manual process that can take several 
minutes from when you instruct Amazon to associate the IP address to fully 
propagating through their system of network devices.

With all of this flexibility and ease of use, there is one feature to be 
cognitive of. When an instance is stopped and then restarted, the Elastic IP no 
longer gets associated with the instance. When the instance is started again, 
the default action is to assign another dynamic IP address (and new hostname) 
to the public IP address instead of the previously associated Elastic IP 

In the following guide, I present one approach that can be used to 
automatically associate an Elastic IP with an instance on startup using an EC2 
startup script and user data. The example in this guide will be performed on an 
Amazon EBS-backed instance running CentOS 6. In addition to associating the 
Elastic IP address, I will also be setting the hostname.

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Install Amazon EC2 Tools (Linux) — (30-June-2012)

The following guide presents an example of the steps on how to install the Amazon 
API Tools and the Amazon AMI Tools on the Linux (CentOS 6) platform.

The Amazon API Tools and the Amazon AMI Tools are packages of command-line 
scripts to the AWS web service used to manage and bundle instances. Although 
there are many other Developer Tools provided by Amazon and the AWS development 
community to help developers create and manage applications built on AWS, these 
two are the most commonly used to manage EC2 instances.

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Remote Desktop to an Amazon EC2 Instance — (27-June-2012)

In most cases, the Linux servers I setup as Amazon EC2 instances are used to 
host the Oracle database software and only require use of the operating system 
Command-Line Interface (CLI). This is beneficial because I only need register 
an Amazon Machine Image (AMI) with a Minimal or Basic Server OS installation 
and can add only those required Linux packages needed to support the database. 
However, there are situations where I need to access a graphical desktop in 
order to install or run certain Graphical User Interface (GUI) applications.

The guide mentioned in this newsletter provides the steps needed to add the 
GNOME Desktop to a Red Hat Enterprise Linux 6.2 AMI (or CentOS) where the OS 
was installed without the X Window System. Although there are several options 
to enable a remote desktop on an EC2 instance, I prefer to use NX Free Edition 

FreeNX is a program which allows users to run remote X11 sessions from clients 
running on Windows, Linux, Mac OS X and Solaris platforms to servers running, 
at present, on Linux or Solaris.

This guide assumes you have an Amazon Web Services account and know how to 
create new EC2 instances from an AMI, key-pairs and security groups within the 
AWS Management Console.

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Build a Custom Amazon EC2 Machine Image (CentOS 6.2) — (24-June-2012)

There is no shortage when it comes to finding an available Amazon Elastic 
Compute Cloud (EC2) Machine Image (AMI). Often times, however, finding an image 
from the community AMIs that meets your particular needs can be a challenge. In 
many cases the image is bloated, provides too much customization, performs 
poorly, or lacks any type of reasonable documentation. Not to mention the 
inherent security concerns associated with some 3rd party AMIs.

In the article described at the end of the newsletter, I will demonstrate how 
to create your own instance store-backed (a.k.a. S3-backed) and EBS-backed 
Amazon EC2 image of CentOS 6.2 (64-bit) with its own kernel. Creating your own 
AMI allows you to make the most of Amazon EC2 and provides better control over 
performance, security, and reproducibility. Your AMIs become the basic unit of 
deployment which allow you to rapidly boot new custom instances as you need 

There are two methods to prepare your own custom Amazon EC2 instances for 
Linux/UNIX systems:

  1.) From an Existing AMI

      Involves launching an existing public AMI and modifying it according to 
      your requirements.

  2.) Create a New AMI from Scratch on Your Own Machine (loopback method)

      Involves building a fresh installation either on a stand-alone machine or 
      on an empty file system mounted by loopback.

Although preparing a new AMI from an existing one is often the easiest method, 
this guide will document the procedures to create a new AMI from scratch using 
a fresh OS install of CentOS 6.2 (64-bit) on an empty file system mounted by 

Build a Custom Amazon EC2 Machine Image (CentOS 6.2)

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Oracle Database Rolling Upgrade Using Data Guard SQL Apply - (10g to 11g) — (01-June-2012)

An Oracle rolling database upgrade eliminates lengthy downtime associated with 
the conventional database upgrade and can also provide an efficient fail back 
method in case of an emergency during the upgrade. A rolling upgrade also 
eliminates application downtime due to recompilation (timings depend on the 
number of invalid objects). The only database downtime required by the entire 
rolling upgrade process is the time it takes to perform a Data Guard switchover 
and the time for applications and services to be re-activated which can occur 
in a matter of a minute or two. 

A rolling upgrade can be performed using either SQL Apply or the Transient 
Logical Standby method. The two rolling upgrade methods are fairly similar 
where the primary difference is the state of the standby database before and 
after completing the upgrade. Using the SQL Apply method, the upgrade starts 
with one node as a logical standby and concludes with one node continuing to be 
a logical standby while with the transient logical standby, one node will start 
as a physical standby, temporarily convert to a logical standby, and will 
revert back to a physical standby after the upgrade. 

In the following guide, a rolling database migration using Data Guard SQL Apply 
will be deployed to migrate from Oracle Database 10g Release 2 ( to 
Oracle Database 11g Release 2 ( This same guide can also be used to 
perform rolling database upgrades using Data Guard SQL Apply. For example, 
upgrading Oracle Database 11g Release 2 ( to (

Although the example in this guide demonstrates a rolling database migration,
the word upgrade will be used interchangeably with migration. 

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Remove a Node from an Existing Oracle RAC 11g R2 Cluster on Linux - (RHEL 5) — (07-May-2012)

Although not as exciting as building an Oracle RAC or adding a new node and 
instance to a cluster database; removing a node from a clustered environment is 
just as important to understand for a DBA managing Oracle RAC. While it is true 
that most of the attention in a cluster database environment is focused on 
extending the database tier to support increased demand, the exact opposite is 
just as likely to be encountered where the DBA needs to remove a node from an 
existing Oracle RAC. One scenario may be a node failure or that an 
underutilized server in the database cluster could be better served in another 
business unit. In either case, a node can be removed from the cluster while the 
remaining nodes continue to service ongoing requests.

The following guide is an extension to two of my articles: "Building an 
Inexpensive Oracle RAC 11g R2 on Linux - (RHEL 5)" and "Add a Node to an 
Existing Oracle RAC 11g R2 Cluster on Linux - (RHEL 5)".

Remove a Node from an Existing Oracle RAC 11g R2 Cluster on Linux - (RHEL 5)

The original articles that describe the existing Oracle RAC and adding a new 
node can be found at:

Building an Inexpensive Oracle RAC 11g R2 on Linux - (RHEL 5)

Add a Node to an Existing Oracle RAC 11g R2 Cluster on Linux - (RHEL 5)

Contained in this new article are the steps required to remove a single node 
from an existing three-node Oracle RAC 11g Release 2 ( environment 
on the CentOS 5 Linux platform. The node being removed is the third node I 
added in the second article. Although this article was written and tested on 
CentOS 5 Linux, it should work unchanged with Red Hat Enterprise Linux 5 or 
Oracle Linux 5.

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Add a Node to an Existing Oracle RAC 11g R2 Cluster on Linux - (RHEL 5.5) — (27-April-2012)

As your organization grows, so too does your need for more application and 
database resources to support the company's IT systems. Oracle RAC 11g provides 
a scalable framework which allows DBA's to effortlessly extend the database 
tier to support this increased demand. As the number of users and transactions 
increase, additional Oracle instances can be added to the Oracle database 
cluster to distribute the extra load.

The following guide is an extension to my article "Building an Inexpensive 
Oracle RAC 11g R2 on Linux - (RHEL 5.5)":

Add a Node to an Existing Oracle RAC 11g R2 Cluster on Linux - (RHEL 5.5)

The original article that describes the existing Oracle RAC can be found at:

Contained in this new guide are the steps required to add a single node to an 
already running and configured two-node Oracle RAC 11g Release 2 for Linux 
x86_64 environment on the CentOS 5.5 for x86_64 platform. All shared disk 
storage for Oracle RAC is based on iSCSI using Openfiler release 2.3 x86_64 
running on a separate node (known in this article as the Network Storage 
Server). Although this article was written and tested on CentOS 5 Linux, it 
should work unchanged with Red Hat Enterprise Linux 5 or Oracle Linux 5. 

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Install Oracle Database 11g R2 on Linux using Oracle ASM - (OL5) — (17-January-2012)

The article described in this newsletter is a comprehensive guide for 
installing Oracle Database 11g Release 2 (11.2) on the Oracle Linux 5 (OL5)
operating environment using Oracle Automatic Storage Management 
(Oracle ASM) and Oracle Restart.

Oracle ASM provides a virtualization layer between the database and storage so 
that multiple disks can be treated as a single disk group and disks can be 
dynamically added or removed while keeping databases online. The example 
database created in this guide will use Oracle ASM for all physical database 
file storage (data files, control files, online redo log files, Fast Recovery 

In previous releases, Oracle ASM was installed as part of the Oracle Database 
installation. With Oracle Database 11g Release 2, Oracle ASM is part of an 
Oracle Grid Infrastructure installation, either for a cluster, or for a 
standalone server. This guide demonstrates how to create a single instance 
(non-RAC) database using Oracle ASM and therefore will require Oracle Database 
11g Release 2 Grid Infrastructure for a Standalone Server to be installed 
before the Oracle Database software.

Oracle Restart is another component of Oracle Grid Infrastructure for a 
Standalone Server that will be used in this guide.

Starting with Oracle Database 11g Release 2, the dbstart and dbshut scripts 
that were used to automate database startup and shutdown in previous Oracle 
versions are deprecated. Oracle now recommends to configure Oracle Database 
with the Oracle Restart feature to automatically restart the database, the 
listener, Oracle Automatic Storage Management (Oracle ASM), and other Oracle 
components after a hardware or software failure or when the database host 
computer restarts. 

The full version of this article can be found at the following location.

Jeffrey M. Hunter, OCP
Sr. Database Administrator

  Install Oracle Database 11g R2 on Linux - (RHEL 6) — (02-January-2012)

The following article provides a comprehensive overview and describes the steps 
necessary to install Oracle Database 11g Release 2 (11.2) on the Red Hat 
Enterprise Linux 6 (RHEL6) operating environment. Installing the Oracle 
Database software is not a difficult task by any means; however, critical 
prerequisite tasks and key decisions will be discussed during the setup and 
configuration in this guide that will have a great impact on your installation.

This guide will include instructions for installing and configuring the Linux 
operating system, installing the Oracle Database software, creating an example 
database, managing Oracle Enterprise Manager Database Control, automatically 
starting and stopping the instance through reboots, and further enhancing the 
Oracle environment by installing custom DBA management scripts.

Jeffrey M. Hunter, OCP
Sr. Database Administrator