Oracle Database 10g: The Top 20 Features for DBAs

Oracle Database 10g: The Top 20 Features for DBAs

Join Oracle Magazine's 2003 "DBA of the Year" Arup Nanda over the next 20 weeks as he presents his list of the top Oracle Database 10g features for database administrators

Follow along! Download Oracle Database 10g



Week 8
Automatic Storage Management


Finally, DBAs can free themselves from the mundane yet common tasks of adding, shifting, and removing storage disks at no additional cost

You just received a brand-new server and storage subsystem for a new Oracle database. Aside from operating system configuration, what is your most important before you can create the database? Obviously, it's creating the storage system layout—or more specifically, choosing a level of protection and then building the necessary Redundant Array of Inexpensive Disks (RAID) sets.

Setting up storage takes a significant amount of time during most database installations. Zeroing on a specific disk configuration from among the multiple possibilities requires careful planning and analysis, and, most important, intimate knowledge of storage technology, volume managers, and filesystems. The design tasks at this stage can be loosely described as follows (note that this list is merely representative; tasks will vary by configuration):
Confirm that storage is recognized at the OS level and determine the level of redundancy protection that might already be provided (hardware RAID).
Assemble and build logical volume groups and determine if striping or mirroring is also necessary.
Build a file system on the logical volumes created by the logical volume manager.
Set the ownership and privileges so that the Oracle process can open, read, and write to the devices.
Create a database on that filesystem while taking care to create special files such as redo logs, temporary tablespaces, and undo tablespaces in non-RAID locations, if possible.
In most shops, the majority of these steps are executed by someone with lots of knowledge about the storage system. That "someone" is usually not the DBA.

Notice, however, that all these tasks—striping, mirroring, logical filesystem building—are done to serve only one type of server, our Oracle Database. So, wouldn't it make sense for Oracle to offer some techniques of its own to simplify or enhance the process?

Oracle Database 10g does exactly that. A new and exciting feature, Automatic Storage Management (ASM), lets DBAs execute many of the above tasks completely within the Oracle framework. Using ASM you can transform a bunch of disks to a highly scalable (and the stress is on the word scalable) and performant filesystem/volume manager using nothing more than what comes with Oracle Database 10g software at no extra cost. And, no, you don't need to be an expert in disk, volume managers, or file system management.

In this installment, you will learn enough about ASM basics to start using it in real-world applications. As you might guess, this powerful feature warrants a comprehensive discussion that would go far beyond our current word count, so if you want to learn more, I've listed some excellent sources of information at the conclusion.

What is ASM?

Let's say that you have 10 disks to be used in the database. With ASM, you don't have to create anything on the OS side; the feature will group a set of physical disks to a logical entity known as a diskgroup. A diskgroup is analogous to a striped (and optionally mirrored) filesystem, with important differences: it's not a general-purpose filesystem for storing user files and it's not buffered. Because of the latter, a diskgroup offers the advantage of direct access to this space as a raw device yet provides the convenience and flexibility of a filesystem.

Logical volume managers typically use a function, such as hashing to map the logical address of the blocks to the physical blocks. This computation uses CPU cycles. Furthermore, when a new disk (or RAID-5 set of disks) is added, this typical striping function requires each bit of the entire data set to be relocated.

In contrast, ASM uses a special Oracle Instance to address the mapping of the file extents to the physical disk blocks. This design, in addition to being fast in locating the file extents, helps while adding or removing disks because the locations of file extents need not be coordinated. This special ASM instance is similar to other filesystems in that it must be running for ASM to work and can't be modified by the user. One ASM instance can service a number of Oracle databases instances on the same server.

This special instance is just that: an instance, not a database where users can create objects. All the metadata about the disks are stored in the diskgroups themselves, making them as self-describing as possible.

So in a nutshell, what are the advantages of ASM?
Disk Addition—Adding a disk becomes very easy. No downtime is required and file extents are redistributed automatically.
I/O Distribution—I/O is spread over all the available disks automatically, without manual intervention, reducing chances of a hot spot.
Stripe Width—Striping can be fine grained as in Redo Log Files (128K for faster transfer rate) and coarse for datafiles (1MB for transfer of a large number of blocks at one time).
Buffering—The ASM filesystem is not buffered, making it direct I/O capable by design.
Kernelized Asynch I/O—There is no special setup necessary to enable kernelized asynchronous I/O, without using raw or third-party filesystems such as Veritas Quick I/O.
Mirroring—Software mirroring can be set up easily, if hardware mirroring is not available.
Creating an ASM-enabled Database, Step by Step

Here's a concrete example of how you would create an ASM-enabled database:

1. Set up an ASM Instance

You create an ASM instance via the Database Creation Assistant by specifying the following initialization parameter:

INSTANCE_TYPE = ASM

You should start the instance up when the server is booted, and it should be one of the last things stopped when the server is shut down.

By default the value of this parameter is RDBMS, for regular databases.

2. Set up a Disk Group

After starting the ASM instance, create a disk group with the available disks.

CREATE DISKGROUP dskgrp1
EXTERNAL REDUNDANCY
DISK
'/dev/d1',
'/dev/d2',
'/dev/d3',
'/dev/d4',
... and so on for all the specific disks ...
;

In the above command, we have instructed the database to create a diskgroup named dksgrp1 with the physical disks named /dev/d1, /dev/d2, and so on. Instead of giving disks separately, you can also specify disk names in wildcards in the DISK clause as follows.

DISK '/dev/d*'

In the above command, we have specified a clause EXTERNAL REDUNDANCY, which indicates that the failure of a disk will bring down the diskgroup. This is usually the case when the redundancy is provided by the hardware, such as mirroring. If there is no hardware based redundancy, the ASM can be set up to create a special set of disks called failgroup in the diskgroup to provide that redundancy.

CREATE DISKGROUP dskgrp1
NORMAL REDUNDANCY
FAILGROUP failgrp1 DISK
'/dev/d1',
'/dev/d2',
FAILGROUP failgrp2 DISK
'/dev/d3',
'/dev/d4';

Although it may appear as such, d3 and d4 are not mirrors of d1 and d2. Rather, ASM uses all the disks to create a fault-tolerant system. For instance, a file on the diskgroup might be created in d1 with a copy maintained on d4. A second file may be created on d3 with copy on d2, and so on. Failure of a specific disk allows a copy on another disk so that the operation can continue. For example, you could lose the controller for both disks d1 and d2 and ASM would mirror copies of the extents across the failure group to maintain data integrity.

3. Create Tablespace

Now create a tablespace in the main database using a datafile in the ASM-enabled storage.

CREATE TABLESPACE USER_DATA DATAFILE '+dskgrp1/user_data_01'
SIZE 1024M
/

That's it! The setup process is complete.

Note how the diskgroup is used as a virtual filesystem. This approach is useful not only in data files, but in other types of Oracle files as well. For instance, you could create online redo log files as

LOGFILE GROUP 1 (
'+dskgrp1/redo/group_1.258.3',
'+dskgrp2/redo/group_1.258.3'
) SIZE 50M,
...
Further Resources

As mentioned earlier, this article is not designed to offer all that is to know about the ASM feature and make you an expert, simply due to the sheer volume of information associated. However, don't despair; there is plenty of help available here on Oracle Technology Network:

"Storage on Automatic," by Lannes Morris-Murphy, is an excellent introductory article on ASM.

ASMLib, a library of the ASM features for Linux, extends ASM functionality. This page also links to technical references and source code for the library modules.

Chapter 12 of the Oracle Database Administrator's Guide 10g Release 1 (10.1) fully explains the concepts behind ASM.


Even archived log destinations can also be set to a diskgroup. Pretty much everything related to Oracle Database can be created in an ASM-based diskgroup. For example, backup is another great use of ASM. You can set up a bunch of inexpensive disks to create the recovery area of a database, which can be used by RMAN to create backup datafiles and archived log files. (In the next installment about RMAN in Oracle Database 10g, you'll learn in detail how to use that capability to your advantage.)

Please bear in mind however that ASM supports files created by and read by the Oracle Database only; it is not a replacement for a general-purpose filesystem and cannot store binaries or flat files.

Maintenance

Let's examine some typical tasks needed to maintain the diskgroups. From time to time, you may have to add additional disks into the diskgroup dskgrp1 to accommodate growing demand. You would issue:

alter diskgroup dskgrp1 add disk '/dev/d5';

To find out what disks are in what diskgroup, you would issue:

select * from v$asm_disk;

This command shows all the disks managed by the ASM instance for all the client databases. Of these disks, you may decide to remove a disk with:

alter diskgroup dskgrp1 drop disk diskb23;

Conclusion

The introduction of ASM provides a significant value in making it much easier to manage files in an Oracle database. Using this bundled feature, you can easily create a very scalable and performant storage solution from a set of disks. Any dynamic database environment requires the addition, shifting, and removal of disks, and ASM provides the necessary toolset to free the DBA from those mundane tasks.