Newsletters Archive - 2010

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


  Activating the Standby Database - (Oracle 10g, Physical Standby) — (16-December-2010)

The tasks involved in creating and managing a Data Guard configuration are 
fairly straightforward. Once the standby database is put into operation, you 
are happy, your customer is happy, management is happy - everyone is happy with 
the piece of mind that their data is being replicated to their disaster 
recovery site. But the time will eventually come where the inevitable will 
occur and the primary database becomes unavailable. You are now faced with 
failing over production activities to an available standby database. Other 
circumstances can also arise where scheduled maintenance needs to occur on the 
primary database and database operations need to be switched over to the 
standby database. In either case, the role of the primary database and the 
standby database will need to be changed. This is known as Role Transition and 
is the subject of the following article:

Activating the Standby Database - (Oracle 10g, Physical Standby)

It is assumed that a primary and one physical standby database is already 
configured in order to perform the role transition steps described in this 
guide. The examples used in this guide will make use of the Oracle Data Guard 
configuration described in the article:

Data Guard Configuration Example - (Oracle 10g, Physical Standby)

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Data Guard Configuration Example, Oracle 10g, Physical Standby — (10-December-2010)

Oracle Data Guard (known as Oracle Standby Database prior to Oracle9i), forms 
an extension to the Oracle RDBMS and provides organizations with high 
availability, data protection, and disaster recovery for enterprise databases. 
Oracle Data Guard provides the DBA with services for creating, maintaining, 
managing, and monitoring one or more standby databases. The functionality 
included with Oracle Data Guard enables enterprise data systems to survive both 
data corruption as well as major disasters.

The following article provides instructions for creating and configuring a 
physical standby database from a primary database using Oracle Database 10g 
Release 2 (10.2) operating in maximum performance protection mode:

Data Guard Configuration Example - (Oracle 10g, Physical Standby)

It should be noted that several different methods exist to create a physical 
standby database configuration and that this is just one of those ways. The 
methods outlined in this guide present a simple approach that should be easy to 
implement in most situations. In fact, if you break down the essential tasks 
required to build a standby database, you will see that it is essentially 
nothing more than taking a backup of the primary database, creating a standby 
controlfile, transferring the files to the standby host, mounting the standby 
database, putting the standby database in managed recovery mode (Redo Apply), 
and starting remote archiving from the primary database (Redo Transport). 
Obviously there are a number of smaller steps I am leaving out which will all 
be discussed in more depth throughout this guide.

All configuration parameters related to the Oracle instance and networking will 
be discussed as well as how to place the standby database in Managed Recovery 
Mode. 

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Using the Oracle ASM Cluster File System (Oracle ACFS) on Linux — (28-November-2010)

Introduced with Oracle ASM 11g release 2, Oracle ASM Cluster File System 
(Oracle ACFS) is a general purpose cluster file system implemented as part of 
Oracle Automatic Storage Management (Oracle ASM). Oracle ACFS extends the 
Oracle ASM architecture and is used to support many types of files which are 
typically maintained outside of the Oracle database. For example Oracle ACFS 
can be used to store BFILEs, database trace files, executables, report files 
and even general purpose files like image, text, video, and audio files. In 
addition, Oracle ACFS can be used as a shared file system for Oracle home 
binaries.

The features included with Oracle ACFS allow users to create, mount, and manage 
ACFS using familiar Linux commands. Oracle ACFS provides support for snapshots 
and the ability to dynamically resize existing file system online using Oracle 
ASM Dynamic Volume Manager (ADVM).

Oracle ACFS leverages Oracle ASM functionality that enables:

  * Oracle ACFS dynamic file system resizing
  * Maximized performance through direct access to Oracle ASM disk group storage
  * Balanced distribution of Oracle ACFS across Oracle ASM disk group storage for increased I/O parallelism
  * Data reliability through Oracle ASM mirroring protection mechanisms

My recent article on using the Oracle ASM Cluster File System provides a hands
on approach to creating and managing ACFS and ADVM on the Linux operating 
system using the following methods:

  * ASM Configuration Assistant (ASMCA)
  * Oracle Enterprise Manager (OEM)
  * Command Line / SQL / ASMCMD 

A full discussion on the different Oracle ACFS components will also be 
presented as well as snapshots, file system recovery, and management techniques.

Using the Oracle ASM Cluster File System (Oracle ACFS) on Linux

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Oracle RAC 11g Release 2 running on CentOS using iSCSI — (08-November-2010)

Oracle RAC 11g release 2 allows DBA's to configure a clustered database 
solution with superior fault tolerance, load balancing, and scalability. 
However, DBA's who want to become more familiar with the features and benefits 
of database clustering will find the costs of configuring even a small RAC 
cluster costing in the range of US$10,000 to US$20,000. This cost would not 
even include the heart of a production RAC configuration, the shared storage. 
In most cases, this would be a Storage Area Network (SAN), which generally 
start at US$10,000.

Unfortunately, for many shops, the price of the hardware required for a typical 
RAC configuration exceeds most training budgets. For those who want to become 
familiar with Oracle RAC 11g without a major cash outlay, the following guide 
provides a low-cost alternative to configuring an Oracle RAC 11g release 2 
system using commercial off-the-shelf components and downloadable software at 
an estimated cost of US$2,800. 

This guide provides detailed instructions on how to build a two-node Oracle RAC 
system using low cost hardware and freely available software. A network storage 
server will be built using Openfiler 2.3 (Final) and iSCSI technology which 
will be used for all shared storage requirements. Powered by rPath Linux, 
Openfiler is a free browser-based network storage management utility that 
delivers file-based Network Attached Storage (NAS) and block-based Storage Area 
Networking (SAN) in a single framework. Openfiler supports CIFS, NFS, HTTP/DAV, 
FTP, however, I will only be making use of its iSCSI capabilities to implement 
an inexpensive SAN for the shared storage component required by Oracle RAC 11g.

The new article can be found at:

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

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Flushing the Oracle Data Buffer Cache During Performance Testing — (23-September-2010)

A common question among developers is how to flush the Oracle data buffer 
cache. During performance testing, it is important to replicate a real-world 
production environment as closely as possible; especially when it involves 
reducing physical I/O (disk reads). Developers need the ability to test the 
effects of changing execution paths without getting accidental benefits from 
the buffered data in the database buffer cache. Having the ability to flush the 
data buffer cache is essential throughout this phase of the SQL tuning process. 

The following note provides several methods used to flush the Oracle data 
buffer cache depending on the release of Oracle.

Flushing the Oracle Data Buffer Cache During Performance Testing

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Connect to SQL Server 2008 from Linux using Perl DBI — (19-September-2010)

If you are a system administrator or DBA, you know the importance of scripting. 
For many administrators, the choice is Perl. Given the fact that Perl is open 
source, available on almost all O/S platforms and extremely modular, many 
popular modules have been developed to extend the language. DBAs for example, 
have enjoyed the ease of scripting database functions using the "Database 
independent interface for Perl" or better known as DBI.

The DBI is a database access module for the Perl programming language. It 
defines a set of methods, variables, and conventions that provide a consistent 
database interface, independent of the actual database being used.

The following article describes how to access Microsoft SQL Server 2008 from a 
Linux server using the Perl DBD::Sybase driver module (this is the Sybase 
driver for the Perl DBI) combined with FreeTDS. FreeTDS is a set of libraries 
for Unix and Linux that allows your programs to natively talk to Microsoft SQL 
Server 2008 and Sybase databases.

Connect to SQL Server 2008 from Linux using Perl DBI

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Install AdventureWorks Sample Databases on SQL Server 2008 R2 — (19-September-2010)

To facilitate the new SQL Server articles and scripts on the iDevelopment.info
website, all of the examples are based on the AdventureWorks 2008R2 sample 
databases. These sample databases are part of the "AdventureWorks Community 
Sample Databases".

The following sample databases are available as part of the AdventureWorks 
Community Sample Databases for SQL Server 2008 R2:

    * AdventureWorks OLTP 2008R2
    * AdventureWorks Data Warehouse 2008R2
    * AdventureWorks LT 2008R2

Starting with SQL Server 2005, the sample databases are not installed by 
default due to security concerns. Users are now required to download and 
manually install the sample databases after successfully setting up SQL Server.

The following article presents the steps required to download and install all 
sample database found in the AdventureWorks Community Sample Databases package 
for SQL Server 2008 R2.

Install AdventureWorks Sample Databases - (SQL Server 2008 R2)

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Using UTL_HTTP and an Oracle Wallet to Establish a Secure URL Connection (SSL) — (13-September-2010)

An article describing the steps necessary to establish a secure URL connection 
(SSL) using the UTL_HTTP Oracle PL/SQL package and an Oracle Wallet. In order 
to fully demonstrate the required steps, I will be establishing a secure URL 
connection to the web site https://www.centos.org/ from within PL/SQL.

In order to establish a connection to a secure URL from an Oracle database 
server, the following tasks will need to be performed:

    * Capture all required certificates from the SSL site
    * Create an Oracle Wallet that is accessible on the database server
    * Import the required certificate(s) of the SSL site into the Oracle Wallet
    * Use the UTL_HTTP.SET_WALLET PL/SQL procedure before attempting to access the secure URL

The full article can be found at:

Using UTL_HTTP and an Oracle Wallet to Establish a Secure URL Connection (SSL)

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Differences in df and du on Oracle Cluster File System (OCFS2) and Orphan Files — (18-August-2010)

Recently, it was noticed that the "df" and "du" commands were displaying 
different results from several nodes on two OCFS2 file systems. The cluster 
file systems were being used as shared storage for an Oracle RAC 10g 
configuration (Clusterware shared files and the Flash Recovery Area).

Researching this problem yielded a bug with the OCFS2 kernel driver (1.4.2-1) 
that was apparently leaving deleted files in the orphan directory (the 
//orphan_dir name space in OCFS2) after being deleted from the disk.

When an object (file and/or directory) is deleted from an OCFS2 cluster file 
system, the file system unlinks the object entry from the existing directory 
and links it as an entry against that cluster node's orphan directory (the 
//orphan_dir name space in OCFS2). When the object is eventually no longer used 
across the cluster, the file system frees it's inode including all disk space 
associated with it.

It was determined that orphan files on the OCFS2 cluster file system were 
responsible for the significant difference between the df and du commands. 
OCFS2 was leaving some deleted files in the orphan directory (the 
//orphan_dir name space in OCFS2) after being deleted.

The following article highlights the steps involved in troubleshooting disk 
space usage on an OCFS2 cluster file system and how to resolve the discrepancy 
between the df and du commands.

Differences in df and du on Oracle Cluster File System (OCFS2) and Orphan Files

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Upgrading OCFS2 - Version 1.4 — (18-August-2010)

An article describing the steps necessary to upgrade an installation of Oracle 
Cluster File System 2 (OCFS2) 1.4 on the Linux platform. More specifically, the 
OCFS2 kernel driver will be upgraded from version 1.4.4-1 to the latest release 
which at the time of this writing is 1.4.7-1.

In addition, the OCFS2 Tools and Console will be upgraded from version 1.4.3-1 
to version 1.4.4-1.

For the purpose of this article, a single OCFS2 file system is configured on a 
two node Oracle RAC 10g environment to support a shared file system required by 
Oracle Clusterware (the Oracle cluster registry file and the voting disk).

The full article can be found at:

Upgrading OCFS2 - 1.4

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Resolve Duplicate CRS Cluster Name in EM Grid Control - (Grid Control 10g) — (11-August-2010)

During the installation of Oracle Clusterware, you are asked to specify a name
for the cluster. If you're like me, you may opt to keep the CRS Cluster Name 
set to its default value of "crs". Even when configuring a second or third 
Oracle RAC within an organization, we many times simply leave the cluster name 
for the new clusters set to their default value of crs. Although I believe it 
is bad practice to configure multiple Oracle RAC clusters with the same CRS 
cluster name, it doesn't necessarily cause any conflicts given these clusters 
don't interact with each other. The clustered databases work independently from 
each other without incident. That is, until you register multiple clustered 
databases with the same CRS cluster name in Oracle Enterprise Manager Grid 
Control!

An issue can arise when multiple Oracle RAC clusters with the same CRS cluster 
name are registered as targets in EM Grid Control. While not causing a problem 
with the cluster itself, it does cause EM Grid Control to think both clusters 
are the same.

The following article will explain the steps used to change the EM cluster 
target name(s) in Grid Control from their default name of "crs" to avoid 
conflict with other cluster targets. In this article, a new EM cluster target 
name will be created in EM Grid Control for two Oracle RAC database 
environments that were registered in EM Grid Control with the same EM cluster 
target name.

Resolve Duplicate CRS Cluster Name in EM Grid Control - (Grid Control 10g)

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------